Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-25 : 21:23:34
|
| Jonah writes "I have a SQL2000(sp2) database (ServerA) and a SQL7(sp2) database(ServerB)From the SQL2000 database I want to call a remote stored procedure on ServerB and store the result set in a table on ServerB.E,ginsert TableAexec ServerB...sp_GetStuffIf I try this by making serverB a remote server,I get the following error message Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'sa'. If I make ServerB a Linked server, I get the error Server: Msg 8501, Level 16, State 1, Line 1MSDTC on server '' is unavailable.Server: Msg 7391, Level 16, State 1, Line 1The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.Now the MSDTC on ServerB won't start - It returns error 3221229574 to which the solution seems to be to reinstall MSDTC which looks like a very messy job with registry hacks and also the threat of reformatting the hard drive So I don't want to do this if possibleI really don't want a distibuted transaction anyway so I tried to stop the transaction being promoted using SET REMOTE_PROC_TRANSACTIONS OFFBut this has no effect Any ideas would be appreciated" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-07-26 : 06:11:25
|
| The remote server option should work I think.You need to add the remote login to the remote server (sa in this case) and map it to a login there and give it access to the database and user.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Calling remote proc