May 5, 2022 at 10:33 am
Hi there
We have a Server where the collation Sequence has been set to Latin1_General_CS_AS (Case Sensitivity)
In this server , we have a database for which the Colllation Sequence has been set to Latin1_General_CI_AS (Case Insensitive)
Now Ive been trying to update the database in question from a project in Visual Studio but its failling because there is a difference in case between the case on the variable declaration and when the variable is used, for example:
DECLARE @SQL NVARCHAR(4000)
SET @sql = 'SELECT @FromDate = MIN(ReadingDateTime) FROM ' + Quotename(@DownloadTableName,'[]')
-- Produces an error because @sql has not been declared
My question therefore is does the Server Collation Sequence overide that of the database when publishing from Visual Studio Database Project?
May 5, 2022 at 11:16 am
Normally the collation order is column > database > server. Maybe it's connecting to the wrong database?
May 5, 2022 at 2:07 pm
the query above would give an error with variable @FromDate as it is not declare WITHIN the @SQL
if you are doing a exec @sql then this would fail - different error - should be exec (@SQL)
if indeed it is saying that @SQL is not defined and if your script has your code exactly as supplied (without a GO) between the declare and the statement then MAYBE your server collation was set with a Case Sensitive collation - not normally advisable but can happen.
see [urlhttps://docs.microsoft.com/en-us/sql/t-sql/statements/collations?redirectedfrom=MSDN&view=sql-server-ver15[/url]
Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database. The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply