Capture collation of remote database

• win

  SSCommitted

  Points: 1569

  More actions

  October 2, 2007 at 2:42 am

  #118870

  I can call call sp_helpsort in a dynamic string from the remote server, but I cannot store the output string in a local table.

  This works:

  set @nstmt='exec ['+@srvinstance+'].['+@database+'].dbo.sp_helpsort'

  exec(@nstmt)

  This does not work:

  set @nstmt='insert into aaa_test exec ['+@srvinstance+'].['+@database+'].dbo.sp_helpsort'

  exec(@nstmt)

  Table aaa_test has 1 field with varchar(500). According to BOL is should work with remote servers.

  I get this error message:

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

  OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

  Msg 7391, Level 16, State 1, Line 1

  The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

  Does anybody know of a working solution for SS2K?

  Thanks,

  Win

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  October 2, 2007 at 3:52 am

  #738052

  Try to remove brackets from the script.

  _____________
  Code for TallyGenerator

• win

  SSCommitted

  Points: 1569

  More actions

  October 2, 2007 at 4:00 am

  #738053

  Tried and failed. The string can evaluate to something like this:

  BLACKADDER\SS2KCS.1Day.dbo.sp_helpsort

  Cheers,

  Win

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  October 2, 2007 at 4:29 am

  #738058

  Then use double quotes instead of brackets..

  _____________
  Code for TallyGenerator

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 2, 2007 at 4:00 pm

  #738407

  http://support.microsoft.com/kb/306212

  
  * Noel