| Author |
Topic |
|
padhma.p
Yak Posting Veteran
91 Posts |
 Posted - 2009-08-03 : 09:50:45
|
| Hello,Good Morning.Here is my question.I have MS SQL 2005.I have access to a stored procedure located on a different SQL Instance and a different DB. I can ONLY execute the procedure to get the data. I do not have any other permission to modify the procedure.I have a local DB on my machine. Is it possible for me to pull the data from the output of the this procedure directly direct it into a table in my local DB.Currently, I am saving the output to a .csv file and doing a bulk insert into my local DB. It is kind of manual process. I want to know without making any changes to the stored procedure, is it anyway possible for me to write a SQL script on my local DB, to pull data from output of the stored procedure.Please advice. This is urgent.I appreciate you input.Thank you,Padhma |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-03 : 10:08:09
|
| One way could be to add a "linked server" to your local server configured to access the server with the SP. Then locally create a table with the same structure as the output of the SP. Then from your local server, insert into your table the results of the remote procedure call:insert into <yourNewTable>exec <remoteServer>.<datbase>..<SP>Be One with the OptimizerTG |
 |
|
|
padhma.p
Yak Posting Veteran
91 Posts |
Posted - 2009-08-03 : 10:23:26
|
| Thank you for your reply. If I do this, then I need a login access to be provided to me by the people who are managing the other server and the database on which SP procedure resides right? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-03 : 10:54:05
|
quote:
Originally posted by padhma.p
Thank you for your reply. If I do this, then I need a login access to be provided to me by the people who are managing the other server and the database on which SP procedure resides right?
You said you already have access to execute the SP, right? So you should be able to connect with those credentials.Be One with the OptimizerTG |
|
|
|
padhma.p
Yak Posting Veteran
91 Posts |
Posted - 2009-08-03 : 14:05:08
|
| Thank you. I have been getting authentication error andI tried to link the server and it says connection succeeded but when i execute the SP, it will give me this error."The transaction manager has disabled its support for remote/network transactions.". I have read many SQL articles and none worked. Any advise here please.Also,will cursors help to answer my topic question?Please advise. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-03 : 14:14:55
|
| I think this error usually means the DTS (distributed transaction coordinator) service needs to be enabled. That is probably on the remote box which you don't have permission to....so:It may be much simpler if you can request from the DBA of the remote box either that table (from Brett's suggestion) DTS'd to your box or perhaps even a backup of the database given to you. Then you can do whatever you want.Be One with the OptimizerTG |
|
|
|
padhma.p
Yak Posting Veteran
91 Posts |
Posted - 2009-08-03 : 15:26:09
|
| Thank you. The access issue wont work, because other who have linked their server to the server B(ther server i am trying to connect) are doing just fine. I tried to comapre my Server Link option, setting with them. They seem to match, but i am the only one who cannot link to server B. The onky difference I saw in others seting in they have their RPC option in linked setting turned to FALSE. when i try to turn it to False, i get RPC not turned on error.So I am not sure what to do about this. |
|
|
|
padhma.p
Yak Posting Veteran
91 Posts |
|
|
padhma.p
Yak Posting Veteran
91 Posts |
Posted - 2009-08-04 : 08:59:20
|
| TG, as you suggested it was a DTC issue. Now the connection is all set.I have another question. When I try to do insert into <table>exec SPshould the table i create in my local DB match the data types and column names of the original source table?Please advise. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-04 : 09:51:34
|
| another method is this http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-04 : 10:35:53
|
| Yeah, that is good too but openrowset has its own configuration issues. You can read more details in Books Online topic: OPENROWSET.>>should the table i create in my local DB match the data types and column names of the original source table?Your target table needs to match the SP output. That may or may not be what the source table structure is.Be One with the OptimizerTG |
|
|
|
padhma.p
Yak Posting Veteran
91 Posts |
Posted - 2009-08-04 : 17:02:34
|
| Thank you TG. |
|
|
|
|
Output of a Procedure into a another table