October 2, 2007 at 2:42 am
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
October 2, 2007 at 3:52 am
Try to remove brackets from the script.
_____________
Code for TallyGenerator
October 2, 2007 at 4:00 am
Tried and failed. The string can evaluate to something like this:
BLACKADDER\SS2KCS.1Day.dbo.sp_helpsort
Cheers,
Win
October 2, 2007 at 4:29 am
Then use double quotes instead of brackets..
_____________
Code for TallyGenerator
October 2, 2007 at 4:00 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply