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 - 2003-02-25 : 07:37:00
|
| Xnera writes "I have to transfer data to a third party software. The means to do this is via thier stored procedures on a MS SQL 2000 server. I have created a linked server from my MS SQL 2000 server to thiers. (Both boxes are running Windows 2000 server) Using the link, I call a series of stored procedure as per thier API to do the data transfers.My problem is one of the APIs requires me to create about 5 temp tables prior to calling the first stored procedure, thier stored procedure uses them. However, I have been unable to figure out how to do this via a linked server. I have tried a few different ways, first just trying to create them directly over there:create table [SageTest].test_app.dbo.#aTempTest( SomeColumn int NULL)gives me:The object name 'SageTest.test_app.dbo.' contains more than the maximum number of prefixes. The maximum is 2.create table [SageTest].test_app.#aTempTest( SomeColumn int NULL)gives me:Database name 'SageTest' ignored, referencing object in tempdb.and creates the temp table in the local dbI also tried creating a stored procedure on the the linked server that creates the temp tables, then calling that before calling my first stored procedure in the API and that also does not work, even if I call the sps locally, the second sp doesn't recognize the temp tables (Something to do with scope?)I have more examples if you need them on what I have tried. But I am out of ideas now and don't know what to do. I've searched the web, asked around and no one has any ideas, so I thought I'd try here." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-25 : 08:56:25
|
| create table has to be on the local server/database.You would have to send the command to the remote server but as you have found the temp table is deleted as soon as the command completes as it will be in a different batch.You will have to create the temp table and action all your commands on the remote server. This will have to be in a single batch to retain the temp table. You could use an osql script file for this.==========================================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. |
 |
|
|
|
|
|
Linked Servers, Temp tables and sprocs