Hello,

Does anybody know, MSSQL 2005 T SQL language, are variables or keywords case
sensitive or not?

Regards

No, keywords and variables are no case sensitive (but CLR method names are).

--
Plamen Ratchev
http://www.SQLStudio.com

What is wrong with this code
Let's look at @sec variable
You can see upper case S in the @Sec variable in the body of function
And lower case s at @sec input variable
When I tried to compile this I got error

Msg 137, Level 15, State 1, Procedure test, Line 8

Must declare the scalar variable "@Sec".

Msg 137, Level 15, State 2, Procedure test, Line 10

Must declare the scalar variable "@Sec".



After changing @sec into @Sec everything works fine

I don't get it?!
Can you explain this

drop function [dbo].[test]

Go

create function [dbo].[test](@sec integer)

returns varchar(20)

as

begin

declare @str varchar(16)

Set @Sec = 100

Set @str = Convert(varchar(16), @Sec)

return @str

end





"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote

My statement was incorrect about variables. Since they follow the rules for identifiers, they can be case sensitive
depending on the collation. Here is from BOL:

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such
as logins and database names, are assigned the default collation of the instance. Identifiers of objects in a database,
such as tables, views, and column names, are assigned the default collation of the database. For example, two tables
with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be
created in a database that has case-insensitive collation.

--
Plamen Ratchev
http://www.SQLStudio.com

Thank you very much.

Now I have problem.

I have database A and database B

I made full backup of db A (example function "test" works properly >
Insensitive collation)
And restore that backup in db B.

In database B example "test" function doesn't work properly!

I thought if I make full backup of db A and restore it in db B, db B must
have exact collation like db A?!

I suppose there isn't easy way to covert database collation?

Thank you in advance.

"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote

m (miroslavsi (AT) gmail (DOT) com) writes:
Are the databases on the same server or on different ones? If the
servers are different, they may have different server collation.

When you say "restore that backup in db B", this in accurate. More correctly
is to say "restore that back *as* db B". Because, when you restore a backup
into an existing database, everything in it is flattened.

If you have further questions, please describe more closely what
"doesn't work properly means".

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for your answer.

Databases are on different servers, and they have different server
collation.

"Doesn't work properly" means that given example function
cannot be compiled in B database (has error, see posts before) while in
database A compiles correctly (there are no errors)

So we determined that is becouse different server collation.

Question is what to do to avoid compilation errors in db B, but without
changing sql function code, becouse code is encrypted.

Thanks in advance,

Regards

I restored backup of db A into db
"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

m (miroslavsi (AT) gmail (DOT) com) writes:
I'm afraid that you're stuck. That is, you either need to change the
code, or change the server collation. Or give up the restore entirely.

The recommendation I can give is to use a case-sensitive collation in
development, and also stick to lowercase identifiers consistently to
avoid problems like these.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thank you very much.

Regards

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

I will go one stepo further. Get a "Pretty Printer" that will format
the source code for you into a consistent format.I have
recommendations in my book SQL PROGRAMMING STYLE based on the research
we did starting in the 1970's on code readabilty for DoD. Good
formatting makes a 8-12% difference in maintenance time.