Error in the script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Error in the script

Author

Topic

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-01-09 : 14:44:11

In the process of upgrading sql 2000 to sql 2005.

Steps Followed:

Moved all databases from sql server 2000 to new sql server 2005 by backup and restore method.
Databases are still in compatibility 80 in new server.

While testing some reports I found this issue.

When I run this script in sql server 2000 database I don't get any error.
declare
@begckindt datetime

SET @begckindt = (SELECT TOP 1 selecttext FROM logtable WITH(NOLOCK) WHERE logid = 11111 AND sequence = 3)

When I run the same script in sql server 2005 environment, I get error

"Conversion failed when converting datetime from character string"

I do not want to make any changes to the script,
since we have lot of reports using similar kind of script.

Can anyone please suggest any solution.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-09 : 15:19:25

I believe your language in sql server 2005 is not the same as in 2000.
Make a test like this:


set language english
declare @begckindt datetime
SET @begckindt = (select '01/22/2008')

or this

set language german
declare @begckindt datetime
SET @begckindt = (select '01/22/2008') --throws that error for this kind of date

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-01-09 : 15:30:26

Thanks for your time.
ok I ran the script you provided, it is throwing error if I set language to german, whereas after setting the language to english
I can run the declare @begckindt datetime
SET @begckindt = (select '01/22/2008') with out any problem.
When I ran the script which I had problem as posted here I am getting same error.So I think this language set did not fix my problem.
BTW my new server is 64 bit , if that makes any difference.

Thanks again

quote:
Originally posted by webfred

I believe your language in sql server 2005 is not the same as in 2000.
Make a test like this:

set language english
declare @begckindt datetime
SET @begckindt = (select '01/22/2008')

or this

set language german
declare @begckindt datetime
SET @begckindt = (select '01/22/2008') --throws that error for this kind of date

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-09 : 15:35:10

In place of you I would act like this:

SELECT TOP 1 selecttext FROM logtable WITH(NOLOCK) WHERE logid = 11111 AND sequence = 3

to see what comes out and if there is a problem in the data.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources