Newbie Question - Shrinking DB & Physical Database Size

Hi! I'm new to SQL Server 2K and have a very basic question. Will the
"Shrink Database" function reduce the physical size of the database
file (as seen in Windows) after records have been deleted?

The physical database size is currently 1.2 GB but even after deleting
all the records, its still 1.2 GB.

Thanks in advance for any assistance.

Jason"Jaosn S" <jason_sweet@.earthlink.net> wrote in message
news:8976695f.0312151650.5dd48fea@.posting.google.c om...
> Hi! I'm new to SQL Server 2K and have a very basic question. Will the
> "Shrink Database" function reduce the physical size of the database
> file (as seen in Windows) after records have been deleted?

"maybe"

It's not exactly analogous to say Access where there is a "compact" command.

And generally you do not want to keep changing the size of your DB. (it can
cause fragmentation at the disk level).

> The physical database size is currently 1.2 GB but even after deleting
> all the records, its still 1.2 GB.

In this case, you should be able to shrink it.

> Thanks in advance for any assistance.
> Jasonsql

newbie question - DDDW in RS

hi,
I'm a RS newbie and trying to convert powerbuilder report to RS.
Is there a dropdown datawindow (DDDW) function in RS?
I mean can I use DDDW in a column(eg. department id) to retrieve code name
(eg department )
from a code table.
I have so many code id columns to join code table and I don't wanna use join
in SQL.
TIAReport parameters can be based on a query.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steffi" <steffibev@.hotmail.com> wrote in message
news:O34bWe1RFHA.3716@.TK2MSFTNGP14.phx.gbl...
> hi,
> I'm a RS newbie and trying to convert powerbuilder report to RS.
> Is there a dropdown datawindow (DDDW) function in RS?
> I mean can I use DDDW in a column(eg. department id) to retrieve code
name
> (eg department )
> from a code table.
> I have so many code id columns to join code table and I don't wanna use
join
> in SQL.
> TIA
>
>

Newbie Datediff help please

I am new to SQL and I am having problems getting the result I need from a
datediff function.
What I am trying to do is check the difference between to dates for which a
customer rented a product and if they only rented for one day bring them up
in the result set.
For example if you have the following:
DateRented 4/26/08
DateReturned 4/26/08
I want the value to come up as 1 instead of 0 is there any way to perform
this function in this manner?
Thanks in advance for any help
NealHi Neal
For this one example, you can add 1 to the datediff
SELECT datediff(dd, datereturned, daterented) + 1
Datediff counts the number of boundaries of the date unit passed as the
first argument. Since these two dates do not cross a day boundary, the
result is 0.
But you need to think about the general case before we can help with a
complete solution. Does the time of day matter?
If DateRented is April 26 and DateReturned is April 28, do you want to
return 3?
You should also consider using a completely unambiguous date format.
4/26/08 might be obvious because there is no 26th month, but many people
will interpret 5/1/08 as January 1st.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Neal Ostrander" <NealOstrander@.discussions.microsoft.com> wrote in message
news:192562E3-DA26-48C6-BA28-FD7DAFB10DF7@.microsoft.com...
>I am new to SQL and I am having problems getting the result I need from a
> datediff function.
> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
> For example if you have the following:
> DateRented 4/26/08
> DateReturned 4/26/08
> I want the value to come up as 1 instead of 0 is there any way to perform
> this function in this manner?
> Thanks in advance for any help
> Neal|||> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
If the data contains date only (midnight time), try:
WHERE
DATEADD(day, 1, DATEDIFF(day, DateRented, DateReturned)) = 1
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Neal Ostrander" <NealOstrander@.discussions.microsoft.com> wrote in message
news:192562E3-DA26-48C6-BA28-FD7DAFB10DF7@.microsoft.com...
>I am new to SQL and I am having problems getting the result I need from a
> datediff function.
> What I am trying to do is check the difference between to dates for which
> a
> customer rented a product and if they only rented for one day bring them
> up
> in the result set.
> For example if you have the following:
> DateRented 4/26/08
> DateReturned 4/26/08
> I want the value to come up as 1 instead of 0 is there any way to perform
> this function in this manner?
> Thanks in advance for any help
> Neal|||The code depends on what you want when the date returned is one day
later than the date rented. I will assume that too should be 1.
SELECT CASE WHEN DATEDIFF(day,DateRented,DateReturned) = 0
THEN 1
ELSE DATEDIFF(day,DateRented,DateReturned)
END
FROM Whatever
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 18:04:40 -0700, Neal Ostrander
<NealOstrander@.discussions.microsoft.com> wrote:
>I am new to SQL and I am having problems getting the result I need from a
>datediff function.
>What I am trying to do is check the difference between to dates for which a
>customer rented a product and if they only rented for one day bring them up
>in the result set.
>For example if you have the following:
>DateRented 4/26/08
>DateReturned 4/26/08
>I want the value to come up as 1 instead of 0 is there any way to perform
>this function in this manner?
>Thanks in advance for any help
>Neal

Newbie ? Please Help!

I'm a little new at this and need some help. My assignment is to modify the following function and do the following steps:
1.If ab_Table.a_col = xy_Table.a_col, then dosomething.
??How do I populate xy_Table
??How does ab_Table get populated in the code below
??How do I compare the ab_Table.a_col to xy_Table.a_col to see if they match
??Where should the code go in the function

/*This is what already exists, I need to know where to put my code.*/
tbl_rc_ab_Table Number :=0

TYPE ab_Table_rectab IS TABLE OF ab_Table %ROWTYPE
INDEX BY BINARY INTEGER

tbl_ab_Table_rectab ab_Table_rectab;

FUNCTION fCreate(p_arg IN OUT...)
n NUMBER :=0
IF tbl_ab_Table_rectab.COUNT > 0 THEN
FOR n in 1..tbl_ab_Table_rectab.COUNT LOOP
IF tbl_ab_Table_rectab.cola = p_arg THEN
tbl_ab_Table_rectab.colb :=1
END IF;
END LOOP;
tbl_rc_ab_Table = tbl_ab_Table_rectab.COUNT + 1;
tbl_ab_Table_rectab(tbl_rc_ab_Table).col_c :=0
END IF:Answers to your questions:
Both #1 and #2 - populate a physical table with the INSERT statement.
#3 - Use the equivalence operator to test variables of same type.
#4 - Well, the "record group" you declare is only initialized (but no
values are being populated in it), the arg val ("p_arg) is of both IN OUT
(only IN is needed) and all this loop is doing right now is making
"colb" equal to a value of 1 if "cola" equals the "p_arg".

First, I think you'll need to place values into this RG (using a Cursor
from the table). Once the RG has values, now you'll be able to
get something out of it with your FOR loop.

newbee question on function

Hi,
I created some functions in SQL server 2000 server. Every time I use the
functions I created I have to prefix them with dbo., say dbo.myFunction. Is
there a way to get around it?
TIANope. Qualifying scalar UFD with owner is mandatory. In fact, owner qualifyi
ng in general is a very
good thing to do, so you should get into the habit of always doing it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny Ni" <dnd@.yahoo.com> wrote in message news:%23KV4rzSaFHA.2128@.TK2MSFTNGP14.phx.gbl...

> Hi,
> I created some functions in SQL server 2000 server. Every time I use the
> functions I created I have to prefix them with dbo., say dbo.myFunction. I
s
> there a way to get around it?
> TIA
>

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.