InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 14:45:39 GMT20http://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspx[quote][b]PaulB-TheOneAndOnly (3/11/2011)[/b][hr][quote][b]LightningFingers (3/11/2011)[/b][hr]One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.[/quote]Out of curiosity, may I ask what kind of issues?Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.[/quote]As Paul says, those techniques don't change your collation.So a restored database's collation will be consistent with its source, the database's default collation should also be the same.However, if the server uses a different collation to your database, then there is a small inconvenience you're likely to run into:Whenever you create a temp table, it will go to tempdb, and use temdb's collation as its default.If you then try compare data between the temp tables and your regular tables, you will get collation errors.To resolve this, create your temp tables specifying a collation clause COLLATE DATABASE DEFAULT for each column. This will create temp tables with the same default as your current database.If this doesn't work, then you may already have inconsistent collation in your database; either due to changing the database defalt at some stage or explicitly specifying collation on some tables.Resolve this by simply explicitly specifying the exact same collation when you create the temp tables.There is one other thing worth knowing: you can explicitly indicate which collation to use on comparison expressions. E.g. Table1.Col1 = Table2.Col1 COLLATE XXXXE.g. You might do this to force a case-sensitive comparison on case insensitive columns. But be warned, the technique can prevent the use of indexes, because indexes are stored in collation order.Thu, 08 Nov 2012 15:59:20 GMTcraig 81366http://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspx[quote][b]LightningFingers (3/11/2011)[/b][hr]I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this.[/quote]It appears like you are in between a rock and a hard place.You may want to check document below "How to transfer a database from one collation to another collation in SQL Server"; it includes a nice explanation about what "collation" is about - hope this helps.[url]http://support.microsoft.com/kb/325335[/url]Fri, 11 Mar 2011 12:05:03 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspx[quote][b]PaulB-TheOneAndOnly (3/11/2011)[/b][hr]Out of curiosity, may I ask what kind of issues?Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.[/quote]I don't completely understand collations, to be honest. And unfortunately we don't have a DBA where I work to help guides us through things like this. The problems began when the developers all received new workstations. The local SQL Server install used a different collation than our staging server. I don't specifically remember what the issues were, just error messages when running stored procedures. Some tables had a specific collation applied to them that was different than the database collation. Its all quite messed up to be honest. But we found that as long as we use scripts to generate the database we didn't encounter the issues.I wrote the following script to modify the collation for each table in a database, but didn't have the confidence in its implications to ever run it anywhere but on my local DB.[code="sql"]DECLARE @DB_Collation nvarchar(50)SET @DB_Collation = CAST(DATABASEPROPERTYEX('TargetDbName', 'Collation') as nvarchar)-- Display the rows before the updatedSELECT *FROM INFORMATION_SCHEMA.COLUMNS cWHERE ( COLLATION_NAME IS NOT NULL AND COLLATION_NAME != @DB_Collation AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE'))DECLARE dynamicSqlCursor CURSOR FOR SELECT 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(10) + 'ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + CASE WHEN DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar' THEN '(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH as varchar) END + ')' END + char(10) + 'COLLATE ' + @DB_Collation + char(10) + CASE IS_NULLABLE WHEN 'NO' THEN 'NOT NULL' WHEN 'YES' THEN 'NULL' END + char(10) FROM INFORMATION_SCHEMA.COLUMNS c WHERE ( TABLE_SCHEMA != 'dbo' AND -- Avoid system tables COLLATION_NAME IS NOT NULL AND -- Null collation will fall back on database collation anyways COLLATION_NAME != @DB_Collation AND -- This is our true target: old collation values EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE') -- Alter tables only )DECLARE @SQL nvarchar(MAX)OPEN dynamicSqlCursorFETCH NEXT FROM dynamicSqlCursorINTO @SQLWHILE @@FETCH_STATUS = 0BEGIN PRINT @SQL -- Execute the query EXEC sp_executeSql @SQL FETCH NEXT FROM dynamicSqlCursor INTO @SQLENDCLOSE dynamicSqlCursorDEALLOCATE dynamicSqlCursor-- Verify the results. The following query should return 0 rows.SELECT *FROM INFORMATION_SCHEMA.COLUMNS cWHERE ( COLLATION_NAME IS NOT NULL AND COLLATION_NAME != 'SQL_Latin1_General_CP1_CI_AS' AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = c.TABLE_NAME AND TABLE_TYPE = 'BASE TABLE'))[/code]Fri, 11 Mar 2011 11:29:24 GMTLightningFingershttp://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspx[quote][b]LightningFingers (3/11/2011)[/b][hr]One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same.[/quote]Out of curiosity, may I ask what kind of issues?Backup/Restore and Detach/Attach methods are not supposed to change collation on target system e.g. collation remains the same as the one in source system.Fri, 11 Mar 2011 11:14:55 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspxIf your clients are using SQL Server 2005, I think, and later, you could use the .net framework and SMO to create an exe that restores the backup or runs the scripts that they can run from the commandline.I am an application developer, and we use Advanced Installer (http://www.advancedinstaller.com/) to install our ASP.net applications that are all front ends to a SQL Server 2005 or later database. The MSI that it creates executes the SQL scripts against the SQL Server. It works pretty slick, so this would be another option. You can use this in two manners: 1) Generate scripts for your database, including data, and have the MSI execute the scripts, or 2) Have the MSI restore your database. I have done the option 1 with success, but knowing the functionality of Advanced Installer, option 2 is doable.You may also want to make sure that you are compacting your backups to help with size.Hope this helps,JimFri, 11 Mar 2011 09:57:56 GMTJim Macehttp://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspxYou might want to start to look at tools like Visual Studio Team System or Red Gate SQL Source Control as a mechanism for implementing deployment processes that allow you to move things in an incremental manner.If you just have to move the whole database every time, I'd go with backup and restore. It's probably the safest and most efficient. As far as collation errors, supply some documentation as to which collation should be used. That one is just a bit outside your control.Fri, 11 Mar 2011 09:29:05 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1076892-391-1.aspxWe currently use SQL 2008's "Generate Scripts" feature to package our data into a series of scripts. We then have a batch file that uses sqlcmd to run each script against the target database.I am looking for alternatives because this method has become quite cumbersome as the database continues to grow and grow and grow!One possibility seems to be to create a .bak file for the database. But we've run into issues with collation when moving .bak files between servers that aren't configured the same. Not sure how to fix that either :unsure:Any help is appreciated, thanks.Fri, 11 Mar 2011 08:01:19 GMTLightningFingers