Default dates to UTC in SQL Server 2000

Hi all,

I hope this is the correct forum. I have SQL Server 2000 installed on a server located in the USA. The local time zone on the server is EST. Therefore getdate() returns EST time. I now realise that I'd like to be saving all my timestamps in UTC. While I could modify all my stored procedures to use getutcdate() rather than getdate() I wonder if there is a database level setting for time zone that will cause getdate() to return UTC date?

Any ideas?

Des

Unfortunately, there is not a database or server lever 'switch' that would allow us to shift time zones.

However, it would be fairly quick to search syscomments for getdate() to determine a list of procedures needing alteration.

You would also want to change any table definitions where column level defaults are set to getdate().

Or, you could set the OS Server time for GMT timezone and turn automatic DST off -but that would effect all other non database logging on the server. Perhaps not a good idea...

|||

Thanks Arnie. I thought as much but it's always good to double check.

Regards,

Des

default date in sql2k

Having a table with two datetime columns(date_ & time_. One to store the date and the other to store the time (my client's design).

When I insert a date alone to the date_ column the time defaults to 12:00:00 AM (as expected).
But I have a problem when inserting / updating the time in the time_ column. When i insert the time from my asp application / query analyzer the date defaults to 1900-1-1(expected). When i insert the time from enterprise manager the date defaults to 1899-12-30.

Can anybody explain me why the date defaults to 1899-12-30 in enterprise manager

thanksThe following article explains this in detail:

article (http://www.databasejournal.com/features/mssql/article.php/1494281)

If you need further discussion, let me know and I will give you my 2 cents as to what is occurring.sql

default column value

as current time..
how can i set that in mssql 2005 ?Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
CREATE TABLE test_defaults
(keycol smallint,
process_id smallint DEFAULT @.@.SPID, --Preferred default definition
date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char1 char(3),
char2 char(3) DEFAULT 'xyz') --Preferred default definition;
GO
HTH. Ryan
"gary" <admin@.newsgroup.com.hk> wrote in message
news:eHFDHarEGHA.3100@.tk2msftngp13.phx.gbl...
> as current time..
> how can i set that in mssql 2005 ?
>|||Note that naming constraints is considered good practice. Sooner or later yo
u will want to change
some defaults, and if you don't know the name, you have to look up the auto-
generated name in the
system tables. This makes implementation of such scripts a mess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uwM3JhrEGHA.916@.TK2MSFTNGP10.phx.gbl...
> Here's the example in 2005 Books Online. The date_ins GETDATE() is the one
you need.
> CREATE TABLE test_defaults
> (keycol smallint,
> process_id smallint DEFAULT @.@.SPID, --Preferred default definition
> date_ins datetime DEFAULT getdate(), --Preferred default definition
> mathcol smallint DEFAULT 10 * 2, --Preferred default definition
> char1 char(3),
> char2 char(3) DEFAULT 'xyz') --Preferred default definition;
> GO
>
> --
> HTH. Ryan
>
> "gary" <admin@.newsgroup.com.hk> wrote in message news:eHFDHarEGHA.3100@.tk2
msftngp13.phx.gbl...
>

Default "Auto Close" to no for entire server?

I was having this problem:
http://groups.google.com/group/micr...78?dmode=source
with it taking an inordinate amount of time to enumerate the databases
on my local SQL Server instance, so I went through all the local
databases and unchecked "Auto Close" on the "Options" tab of the
Database properties in Enterprise Manager.

This cleared up the problem nicely. In EM or through SQL-DMO, the
databases on the local server enumerate quite quickly now. However, any
new databases that are created have "Auto Close" checked, so eventually
the original problem will resurface.

Is there some way to tell SQL Server to create any new databases with
"Auto Close" set to No?When you create a new DB, SQL Server make a copy of database "model".
So make sure that the option auto close is set as you like in "model",
before create a new DB.

Beowulf wrote:

Quote:

Originally Posted by

I was having this problem:
http://groups.google.com/group/micr...78?dmode=source
>
with it taking an inordinate amount of time to enumerate the databases
on my local SQL Server instance, so I went through all the local
databases and unchecked "Auto Close" on the "Options" tab of the
Database properties in Enterprise Manager.
>
This cleared up the problem nicely. In EM or through SQL-DMO, the
databases on the local server enumerate quite quickly now. However, any
new databases that are created have "Auto Close" checked, so eventually
the original problem will resurface.
>
Is there some way to tell SQL Server to create any new databases with
"Auto Close" set to No?

|||francois.bourdages@.harfan.com wrote:

Quote:

Originally Posted by

Beowulf wrote:

Quote:

Originally Posted by

>I was having this problem:
>http://groups.google.com/group/micr...78?dmode=source
>>
>with it taking an inordinate amount of time to enumerate the databases
>on my local SQL Server instance, so I went through all the local
>databases and unchecked "Auto Close" on the "Options" tab of the
>Database properties in Enterprise Manager.
>>
>This cleared up the problem nicely. In EM or through SQL-DMO, the
>databases on the local server enumerate quite quickly now. However, any
>new databases that are created have "Auto Close" checked, so eventually
>the original problem will resurface.
>>
>Is there some way to tell SQL Server to create any new databases with
>"Auto Close" set to No?

>
When you create a new DB, SQL Server make a copy of database "model".
So make sure that the option auto close is set as you like in "model",
before create a new DB.

"Auto Close" is set to No in model, but if I create a new database in
Enterprise Manager, then the new database has "Auto Close" checked. Is
this a bug in SQL Server 2000?|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

"Auto Close" is set to No in model, but if I create a new database in
Enterprise Manager, then the new database has "Auto Close" checked. Is
this a bug in SQL Server 2000?

What happens if you create a database from Query Analyzer, using the
CREATE DATABASE statement?

What edition of SQL 2000 do you have?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>"Auto Close" is set to No in model, but if I create a new database in
>Enterprise Manager, then the new database has "Auto Close" checked. Is
>this a bug in SQL Server 2000?

>
What happens if you create a database from Query Analyzer, using the
CREATE DATABASE statement?

The result is the same as when right-clicking and choosing New Database
in EM.

The Options listed for model:
[ ] - Restrict access
(*) Members of db_owner, dbcreator, or sysadmin
( ) Single user
[ ] - Read-only
--
Recovery Model: Full
--
[ ] - ANSI NULL default
[ ] - Recursive triggers
[x] - Auto update statistics
[x] - Torn page detection
[ ] - Allow cross-database ownership chaining
[ ] - Auto close
[ ] - Auto shrink
[x] - Auto create statistics
[ ] - Use quoted identifiers
--
Compatibility Level: Database compat level 80
--
[ ] - List this database in Active Directory

Quote:

Originally Posted by

What edition of SQL 2000 do you have?

SELECT @.@.VERSION

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

The result is the same as when right-clicking and choosing New Database
in EM.
>...
SELECT @.@.VERSION
>
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.

I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.

I would try:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

and also restart SQL Server.

Yes, I'm clutching at straws.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:
>

Quote:

Originally Posted by

>>The result is the same as when right-clicking and choosing New Database
>>in EM.
>>...
>>SELECT @.@.VERSION
>>
>>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
>>23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
>>Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

>
>
Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.
>
I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.
>
I would try:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
and also restart SQL Server.
>
Yes, I'm clutching at straws.

Thanks for the suggestions. I'll try that out and report back.|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>The result is the same as when right-clicking and choosing New Database
>in EM.
>...
>SELECT @.@.VERSION
>>
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
>23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
>Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

>
Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.
>
I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.
>
I would try:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
and also restart SQL Server.
>
Yes, I'm clutching at straws.

Ok, here's what I did:

1. Re-confirmed in EM that model has Auto Close unchecked
2. Ran this SQL batch in Query Analyzer:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

CREATE DATABASE stercus1000

- Result: stercus1000 had Auto Close checked

3. Stopped the MS SQL Server service (and dependent services)
4. Started the MS SQL Server service (and dependent services)
5. Ran this SQL batch in Query Analyzer:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

CREATE DATABASE stercus2000

- Result: stercus2000 had Auto Close checked

I'm stumped.|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

3. Stopped the MS SQL Server service (and dependent services)
4. Started the MS SQL Server service (and dependent services)
5. Ran this SQL batch in Query Analyzer:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
CREATE DATABASE stercus2000
>
- Result: stercus2000 had Auto Close checked
>
I'm stumped.

And you think I am not? :-)

The only I can suggest is a Post-It note which says "Don't forget to
set newly created databases to AUTO_CLOSE OFF".

I have no idea of what is going on. Maybe Personal Edition always sets
AUTO_CLOSE ON, despite that Books Online said that this applies to
MSDE. (I don't have any instance of Personal Edition installed to test.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>3. Stopped the MS SQL Server service (and dependent services)
>4. Started the MS SQL Server service (and dependent services)
>5. Ran this SQL batch in Query Analyzer:
>>
> ALTER DATABASE model SET AUTO_CLOSE ON
> go
> ALTER DATABASE model SET AUTO_CLOSE OFF
>>
> CREATE DATABASE stercus2000
>>
>- Result: stercus2000 had Auto Close checked
>>
>I'm stumped.

>
And you think I am not? :-)
>
The only I can suggest is a Post-It note which says "Don't forget to
set newly created databases to AUTO_CLOSE OFF".
>
I have no idea of what is going on. Maybe Personal Edition always sets
AUTO_CLOSE ON, despite that Books Online said that this applies to
MSDE. (I don't have any instance of Personal Edition installed to test.)

Thanks for the suggestions, Erland. I'll go dig out my pad of Post-Its.

Decrypting WITH Encryption User Functions...

...it's possible without any third party application?

I need to recover some encrypted user functions but the sources have been
lost long time ago, someone can help me?
--

Lav."Laverio" <darthmoul@.despammed.com> wrote in message
news:br7rlk$2l8$1@.newsreader.mailgate.org...
> ...it's possible without any third party application?
> I need to recover some encrypted user functions but the sources have been
> lost long time ago, someone can help me?

Actually in the past I think I've heard of folks watching it go through the
Profiler.

> --
> Lav.|||http://www.securiteam.com/tools/6J00S003GU.html

--
David Portas
----
Please reply only to the newsgroup
--

"Laverio" <darthmoul@.despammed.com> wrote in message
news:br7rlk$2l8$1@.newsreader.mailgate.org...
> ...it's possible without any third party application?
> I need to recover some encrypted user functions but the sources have been
> lost long time ago, someone can help me?
> --
> Lav.sql

DECODE?

I have to run a query to give a column a value based on a time range. Can I
use DECODE?

select decode(trans_date, trans_date>='01-Jul-2002' and
trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
from. . .
where. . .Sherman H. (shung@.earthlink.net) writes:
> I have to run a query to give a column a value based on a time range.
> Can I use DECODE?
> select decode(trans_date, trans_date>='01-Jul-2002' and
> trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
> from. . .
> where. . .

Maybe in some other DBMS, but there is no such function in SQL Server.

I don't know what decode is supposed to achieve, but it seems that
the CASE expression might to the task:

SELECT CASE WHEN transdate BETWEEN '20020701' AND '20030630'
THEN 'Fiscal2002'
...
END

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

declare variables

New to stored procedures. Is it necessary to place a default value into a
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
can someone tell me whats going on?
XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>
|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't th
e same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on s
electivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which
is a good idea. I just
want t point out that none of your examples will actually expose parameter s
niffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/pr...r />
comp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

DecimalPlaces Property

I've got a SQL table that I created through the Access 2K2 interface that is
suddenly giving me a warning message each time I attempt to open the table
in design mode or datasheet mode which says:
"The setting for the DecimalPlaces property must be from 0 through 15, or 25
5 for Auto (default)."
I have checked, rechecked, and checked again the format for every item in my
table, and none of them have a DecimalPlaces property outside of this range
. In fact, only three smallmoney fields have any DecimalPlaces setting at al
l - each is 0. The rest of
the numeric fields are all specified as General Number, and the Decimal Plac
es property is blank (and inaccessible). I have been completely unable to fi
nd any information on this on SQL Server BOL, the KB or these newsgroups. Ha
s anyone else encountered t
his behavior? Any idea why it may be occurring, and what I can do to stop it
? BTW, this is a new table with no data, and the warning message only recent
ly started appearing - it didn't always do so.
TIA,
Terry Roberts>
> I've got a SQL table that I created through the Access 2K2
> interface that is suddenly giving me a warning message each time
> I attempt to open the table in design mode or datasheet mode
> which says:
> "The setting for the DecimalPlaces property must be from 0
> through 15, or 255 for Auto (default)."
--
Hi Terry,
Try deleting the linked table in Access and relink. See if the problem goes
away.
If the problem persists, post your question in one of the Microsoft Access
forums and someone might be able to help.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||Eric,
Thanks for the advice - unfortunately, it didn't solve the problem. I'll try
a post to the Access newsgroup.
Terry

DecimalPlaces Property

I've got a SQL table that I created through the Access 2K2 interface that is suddenly giving me a warning message each time I attempt to open the table in design mode or datasheet mode which says:
"The setting for the DecimalPlaces property must be from 0 through 15, or 255 for Auto (default)."
I have checked, rechecked, and checked again the format for every item in my table, and none of them have a DecimalPlaces property outside of this range. In fact, only three smallmoney fields have any DecimalPlaces setting at all - each is 0. The rest of
the numeric fields are all specified as General Number, and the Decimal Places property is blank (and inaccessible). I have been completely unable to find any information on this on SQL Server BOL, the KB or these newsgroups. Has anyone else encountered t
his behavior? Any idea why it may be occurring, and what I can do to stop it? BTW, this is a new table with no data, and the warning message only recently started appearing - it didn't always do so.
TIA,
Terry Roberts
>
> I've got a SQL table that I created through the Access 2K2
> interface that is suddenly giving me a warning message each time
> I attempt to open the table in design mode or datasheet mode
> which says:
> "The setting for the DecimalPlaces property must be from 0
> through 15, or 255 for Auto (default)."
Hi Terry,
Try deleting the linked table in Access and relink. See if the problem goes
away.
If the problem persists, post your question in one of the Microsoft Access
forums and someone might be able to help.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Eric,
Thanks for the advice - unfortunately, it didn't solve the problem. I'll try a post to the Access newsgroup.
Terry

DecimalPlaces Property

I've got a SQL table that I created through the Access 2K2 interface that is suddenly giving me a warning message each time I attempt to open the table in design mode or datasheet mode which says
"The setting for the DecimalPlaces property must be from 0 through 15, or 255 for Auto (default).
I have checked, rechecked, and checked again the format for every item in my table, and none of them have a DecimalPlaces property outside of this range. In fact, only three smallmoney fields have any DecimalPlaces setting at all - each is 0. The rest of the numeric fields are all specified as General Number, and the Decimal Places property is blank (and inaccessible). I have been completely unable to find any information on this on SQL Server BOL, the KB or these newsgroups. Has anyone else encountered this behavior? Any idea why it may be occurring, and what I can do to stop it? BTW, this is a new table with no data, and the warning message only recently started appearing - it didn't always do so
TIA
Terry Roberts>
> I've got a SQL table that I created through the Access 2K2
> interface that is suddenly giving me a warning message each time
> I attempt to open the table in design mode or datasheet mode
> which says:
> "The setting for the DecimalPlaces property must be from 0
> through 15, or 255 for Auto (default)."
--
Hi Terry,
Try deleting the linked table in Access and relink. See if the problem goes
away.
If the problem persists, post your question in one of the Microsoft Access
forums and someone might be able to help.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||Eric
Thanks for the advice - unfortunately, it didn't solve the problem. I'll try a post to the Access newsgroup
Terry

Debugging or a parody on it?

Hi, I am new here.

I tried to debug stored procedures in the Visual Studio and you know… it was a real shock. Like going in time 10 or 20 years back.

The stored procedures I deal with contain a lot of @.tab and #tab, but it appears that I can not evaluate the values of these objects! The only type of object I can evaluate is a variable. But SQL is designed for the table data, right? So the most important type of data is not accessible!

How can I understand, what records have been affected by Update or inserted by Insert? Using prints or debug selects? Is it an era of FORTRAN or ALGOL?

To make it worse, to enter a debugger, I need to provide values of all parameters. It might be simple for the outermost procedure, but for inner stored procs, and where some parameters are some short-living identity values of some records, created in a transaction, it is very difficult to prepare all conditions to make a correct run.

So my question is, may be I had overlooked something? Is it a real debugger or just a joke from Microsoft? Sorry, but I am really angry after wasting a lot of time last week on it.

If you want to trace changes on the record level it might be better to enable auditing in SQL Server and examine the log files with Profiler. Here is one article about enabling auditing in SQL 2000:

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

|||

Thank you. But agree with me, it is far from simplicity of the debugging process we have with C# code.

Ok, I'll google if there any 3rd party tools.

Debugging in SSIS - Immediate and Command windows

Hi everyone,
I am having a difficult time debugging a package that I'm working on. I read in BOL that the immediate window should be an option during debugging but I can't find it anywhere(nor can I enable it), and I was planning on using it to access an oledb source property that is using an expression. Is the command window the same as immediate? I didn't orignally think so but I'm not sure. What is the syntax to use for this once I find it?
Thanks,
Adrian

You can't access runtime values of an oledb source property but you can see what data going downstream using visualizers.

Command window isn't supported.

Can you give me a ref where in BOL you found about immediate window?

|||Nick, thanks for responding, I didn't see that anyone had responed until now.
I found it here:
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/54a458cc-9f4f-4b48-8cf2-db2e0fa7756c.htm

Adrian

Debugging DataFlow components

I'm trying to do design time debugging of a dataflow component that I developed. I'm clearly doing something wrong, but I can't figure out what so I'm looking for suggestions if anyone has had a similar issue.

The reason I think I doing the steps correctly is that I can debug other components. However, that set seems to be limited to those developed in C#. My component is developed in VB. Shouldn't matter, I wouldn't think.

As a test I tried to debug other VB components from the SQL Server Samples and I have the same problem with them. Last one I tried was ChangeCaseVB. Though I just tried ChangeCaseVS and it didn't stop for me either...

The problem I have is when I drop the controls onto the design surface in the second instance of VS, the first instance of VS, which has the code for the component, does not hit any of the breakpoints, such as ProvideComponentProperties, which I know should be hit, and are hit when I debug a C# component.

To recap, I have an instance of VS open with the code for the component, developed in VB.

I compile the component.

I copy the component to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

I gacutil the component and then it shows in C:\WINDOWS\assembly

I add the component to the toolbox in my second instance of VS where I am designing the SSIS package which will host the component

I attach my first VS instance to my second VS instance

I drop the component on the designer in the second VS instance.

At this point I expect the first VS instance to hit a breakpoint, it does not.

I have also tried the method of setting the external program and command line arguements in the component's VS for running dtexec.exe directly.

Any suggestions?

Debugging VB or C# is exactly the same, no issues there. I cannot see anything wrong with what you describe, so some general pointers.

Make sure the component has been compiled, GAC'd and copied to the PipelineCOmponenst folder, before you open the second instance of VS that hosts the SSIS project. This will ensure you use the correct version of the assembly, when loaded by the SSIS project, that matches the code instance of VS.

Make sure your code project is set to a Debug build, rather than Release.

Be aware that some properties are design-time, so attach to VS as you have, but others are run-time, in which case, the extrenal program and dtexec is the best way. The design-time hosts (VS) and runtime host (ideally DTEXEC) are different so to debug different methods you need to be attached to the correct host. There is VS run-time host as well, more on that here-

Building, Deploying, and Debugging Custom Objects
(http://msdn2.microsoft.com/en-us/library/b03685bc-5398-4c3f-901a-1219c1098fbe.aspx)

|||

Thank you for the response and "reality check". I have been able to get debugging working now. I'd like to report that I had only one issue, but as I was testing with a variety projects trying to figure this out, they all seeminlgly had different problems, causing myself some extra grief. I still see the same issue from time to time, but overall it's going much better. Thanks for the help.

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
Paul
Paul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionH
andler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptio
nHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debug Error - Object variable or With block variable not set -

I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.

~~~~~~~~~~~

Error:

System.NullReferenceException was unhandled by user code
Message="Object variable or With block variable not set."
Source="Microsoft.VisualBasic"

~~~~~~~~~~~~

My Code

Dim DBConnAs SqlConnection

Dim DBAddAsNew SqlCommand

Dim strConnectAsString = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString

DBConn =New SqlConnection(strConnect)

DBAdd.CommandText ="INSERT INTO D12_MIS (" _

&"CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _

&") values (" _

&"'" & Replace(vbCSJ.Text,"'","''") _

&"', " _

&"'" & Replace(tmp1Date,"'","''") _

&"', " _

&"'" & Replace(tmpRecordLock,"'","''") _

&"', " _

&"'" & Replace(CheckedCreator,"'","''") _

&"', " _

&"'" & Replace(CheckedReviewer,"'","''") _

&"', " _

&"'" & Replace(vb2_1,"'","''") _

&"', " _

&"'" & Replace(tmp2Date,"'","''") _

&"', " _

&"'" & Replace(tmp3Date,"'","''") _

&"', " _

&"'" & Replace(tmp4Date,"'","''") _

&"', " _

&"'" & Replace(vb2_5,"'","''") _

&"', " _

&"'" & Replace(vb3_1a,"'","''") _

&"', " _

&"'" & Replace(vb3_1b,"'","''") _

&"', " _

&"'" & Replace(vb3_2a,"'","''") _

&"', " _

&"'" & Replace(vb3_2b,"'","''") _

&"', " _

&"'" & Replace(vb3_3a,"'","''") _

&"', " _

&"'" & Replace(vb3_3b,"'","''") _

&"')"

DBAdd.Connection = DBConn

DBAdd.Connection.Open()

DBAdd.ExecuteNonQuery()

DBAdd.Connection.Close()

Has nothing to do with NULL values, it's a null reference. In better VB-speak, you've accessed a method or property on an object that you haven't initialized.

For example:
Dim conn as sqlconnection

conn.open

Notice, conn was never set to a sqlconnection, hence, it's nothing (Nothing is vb-speak for C#/C++'s null).

My guess is that you don't actually have your web.config set up correctly. More specifically, you don't have a connection string named "ProtoCostConnectionString" in the connectionstrings section. Note, that is NOT the same as the appsettings section.

Also, you really should use a parameterized query, and if you are going to be concatenating more than a few lines of strings, you'd be much better off using the stringbuilder class if you absolutely MUST continue using string concatenation as how you build your SQL commands.

|||Thanks, I will rewrite this procedure and will use "parameterized query" instead of the string variable.