August 2, 2011 at 10:29 am
I need to create a stored proc in my db. Stored proc is pulling data from the different server. But I don't have linked server setup for this server. Is there anyway to create stored procedure without setting up a linked server?
Currently when I try to create this sp, I am getting the following error.
Msg 7202, Level 11, State 2, Line 2
Could not find server '100-P-T-001' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
August 2, 2011 at 10:38 am
August 2, 2011 at 10:41 am
So, do I need to create sp with OPENROWSET?
August 2, 2011 at 10:43 am
Well yes, unless you preffer to add the linked server.
Option A would have been SSIS if you don't mind the learning curve.
August 2, 2011 at 10:55 am
I am familiar with ssis. How ca I accomplish this through ssis?
August 2, 2011 at 11:01 am
No idea what you want to do in the process.
The only thing I can tell you is to have 2 connections (1 local, 1 remote).
SSIS is a tool, might be the one to use depending on your needs.
What do you need to do?
August 2, 2011 at 12:25 pm
No. I can't do that. This some body else's process. I just need to be able to create that stored proc. How about creating this under the service account?
August 2, 2011 at 12:27 pm
Then it's back to openrowset, openquery or linked server.
Nothing else will work. So get <something> setup depending on the business rules and be done with it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply