Capture collation of remote database

  • 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

  • Try to remove brackets from the script.

    _____________
    Code for TallyGenerator

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

    BLACKADDER\SS2KCS.1Day.dbo.sp_helpsort

    Cheers,

    Win

  • Then use double quotes instead of brackets..

    _____________
    Code for TallyGenerator

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


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply