SQL 2008 T-SQL

Question

SQL 2008 T-SQL

Ron McCullough

SSC Guru

Points: 63877
More actions
August 24, 2009 at 8:36 pm

#214293

Comments posted to this topic are about the item SQL 2008 T-SQL
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Aluminum Ten Centuries Points: 1152 More actions · Answer 1

Ummm.... I had an error when processing this sql. That is the option I picked before I answered and decided to check it after I was wrong.

kramaswamy SSCoach Points: 18181 More actions · Answer 2

did you try it in SSMS 2008? doens't work in 2005, those features are part of the 2008 version.

Aluminum Ten Centuries Points: 1152 More actions · Answer 3

Aluminum

Ten Centuries

Points: 1152

August 25, 2009 at 7:45 am

#1044176

Did not. Thanks for the post.

Michael Poppers SSCrazy Points: 2151 More actions · Answer 4

I thought [font="Courier New"]@msg *= 3[/font] would not work -- my bad :-).

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 5

I thought this wouldn't work because I assumed 2005 worked the same as 2008. I was surprised to see that I was wrong. I wanted to see it work in 2008 but when I ran it my server returned this error.

@i @Msg Date

----------- ----- --------

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'Date'. The GetBytes function can only be used on columns of type Text, NText, or Image.

What does this mean?

-Kevin

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 6

DECLARE @i AS INT = 100,@msg AS VARCHAR(5)= '11',@Date AS DATE=GETDATE()

will generate error "Cannot assign a default value to a local variable."

Bhavesh
.NET and SQL Server Blog

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 7

bhavster27 (8/25/2009)
DECLARE @i AS INT = 100,@msg AS VARCHAR(5)= '11',@Date AS DATE=GETDATE()
will generate error "Cannot assign a default value to a local variable."

Not for SQL 2008

KevinC. SSCommitted Points: 1570 More actions · Answer 8

KevinC.

SSCommitted

Points: 1570

August 25, 2009 at 10:55 am

#1044360

Good question!

john.moreno Default port Points: 1485 More actions · Answer 9

On my SQL 2008 server (using the Master database so no question of compatibility level) this fails with the previously mentioneed "GetBytes function" error.

use master

DECLARE @Date AS DATE=GETDATE()

SELECT @Date as '@Date'

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column '@Date'. The GetBytes function can only be used on columns of type Text, NText, or Image.

If you explicitly cast the DATE to CHAR or VARCHAR then it works (also if you change the type of date to DATETIME). I'm not sure why this is so, as the conversion page says that there is an implicit conversion from Date to char and varchar AND no conversion possible to Text, NText or Image.

So, I got the right answer for the wrong reason, but it was graded wrong for an unknown reason. Sounds fair to me.

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 10

john.moreno (8/25/2009)
On my SQL 2008 server (using the Master database so no question of compatibility level) this fails with the previously mentioneed "GetBytes function" error.

I found that the reason I got the GetBytes error was because I was connecting to the SQL 2008 server with MSSMS 2005.

john.moreno Default port Points: 1485 More actions · Answer 11

kevin.l.williams (8/25/2009)
I found that the reason I got the GetBytes error was because I was connecting to the SQL 2008 server with MSSMS 2005.

I thought I was connected with the MSSMS 2008, but checking shows that you are right, I was using 2005.

anil-372043 SSC Rookie Points: 29 More actions · Answer 12

the statement below is same for sql server 2005

DECLARE @i AS INT

declare @msg AS VARCHAR(5)

declare @Date AS DATEtime

set @i =100;

set @msg ='11';

set @Date =GETDATE()

SET @i =@i +25;

SET @i =@i *10;

SET @i =@i / 1000;

SET @msg =@msg*3;

SELECT @i AS '@i', @msg AS '@Msg', @Date AS 'Date'

and will give same answer as in 2008....

@i @Msg Date

----------- ----- -----------------------

1 33 2009-08-26 09:45:49.150

anil-372043 SSC Rookie Points: 29 More actions · Answer 13

the statement below is same for sql server 2005

DECLARE @i AS INT

declare @msg AS VARCHAR(5)

declare @Date AS DATEtime

set @i =100;

set @msg ='11';

set @Date =GETDATE()

SET @i =@i +25;

SET @i =@i *10;

SET @i =@i / 1000;

SET @msg =@msg*3;

SELECT @i AS '@i', @msg AS '@Msg', @Date AS 'Date'

and will give same answer as in 2008....

@i @Msg Date

----------- ----- -----------------------

1 33 2009-08-26 09:45:49.150

anil-372043 SSC Rookie Points: 29 More actions · Answer 14

the statement below is same for sql server 2005

DECLARE @i AS INT

declare @msg AS VARCHAR(5)

declare @Date AS DATEtime

set @i =100;

set @msg ='11';

set @Date =GETDATE()

SET @i =@i +25;

SET @i =@i *10;

SET @i =@i / 1000;

SET @msg =@msg*3;

SELECT @i AS '@i', @msg AS '@Msg', @Date AS 'Date'

and will give same answer as in 2008....

@i @Msg Date

----------- ----- -----------------------

1 33 2009-08-26 09:45:49.150