Friday, 22 September 2006

SQL 2000 > 2005 Linked Server Error

" A severe error occurred on the current command. The results, if any, should be discarded. "

This occurs passing a NVARCHAR to sp_executesql on sql 2000 when linking to a 2005 instance.

Sql 2000 could not cope with the output returned by sql 2005 hence although the steps ran perfectly on their own.

declare @chvDataBaseName varchar(100)
declare @sql nvarchar(2000)
set @chvDataBaseName = 'northwind'

-- backup on local server

set @sql = N' BACKUP DATABASE ' + @chvDataBaseName
set @sql = @sql + ' TO DISK = N''d:\tempmigrationfolder\'
set @sql = @sql + @chvDataBaseName + '.BAK'' WITH NOFORMAT, INIT,'
set @sql = @sql + ' NAME = N''Full Database Backup'','
set @sql = @sql + ' SKIP, NOREWIND, NOUNLOAD, STATS = 10'
print @sql
execute linked2005server.master.sp_executesql @sql

It is confirmed as a bug. I simply reversed my plan and ran the script from sql2005 performing the remote call on the sql 2000 databases.

No comments: