quote:

---

Originally posted by SamC

Step 1: Margarita mix...

---

quote:

---

SQL Server 2000 finally solved the problem of taking database backups from servers with a different character set / sort order and restoring them, without getting a collation error. Before SQL Server 2000, backups from one server could only be restored on a different server if the same character set and sort order where used by both servers. With SQL Server 2000, you are able to set the collating sequence at the server, database, and even the column level. Now that you can have multiple collating sequences on a single database server, there are a few things you should understand about mixing collations.

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable99]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[myTable99]
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

SET NOCOUNT ON
DECLARE @Error int, @RowCount int, @cmd varchar(8000), @pwd varchar(30), @uid varchar(30), @db sysname, @fn sysname

SELECT    @db = db_name()
	, @uid = 'sa'
	, @pwd = 'p99edward'
	, @fn = 'c:\myTable99_'+REPLACE(@@SERVERNAME,'\','_')+'_'+@db+'.txt'

INSERT INTO myTable99(Col1) EXEC sp_helpsort

SELECT @Error = @@ERROR, @RowCount = @RowCount

IF @Error <> 0
  BEGIN
	SELECT 'Error Occured executing sp_helpsort.  Error='+CONVERT(varchar(5),@Error)
	GOTO l_Error
  END
IF @RowCount = 0
  BEGIN
	SELECT 'No Rows returned from sp_helpsort'
	GOTO l_Error
  END

SELECT @cmd = 'bcp  ' + @db + '.dbo.myTable99 out ' + @fn +' -c -S' + @@SERVERNAME + ' -U'+@uid+' -P'+@pwd 
SELECT @cmd

EXEC master..xp_cmdshell @cmd

SELECT * FROM myTable99
SELECT 'Successful'
GOTO l_Exit

l_Error:
SELECT 'Unsuccessful'

l_Exit:
SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO