HI, yes you could. If it's on a different server, you will need to create a linked server to it and then write to that SQL table. Here are the instructions (so to speak) for setting up linked server,etc. (a gentleman posted this on the forum post a while back but I saved the document so here it is again):
Select Distinct CentreName as QuestionOption, CentreName as QuestionOptionValue FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=pro3;User ID=removed;Password=removed').abcuniforms.dbo.staff Where CentreState = '$(ABCState)' And Deleted = 0 And Password Like '%childcare.com.au%' Order by CentreName ASC
I am thankful for sites like this that really try to help. I have been struggling to call a stored proc from a linked server when the calling server is SQL 2005 and the linked server is SQL 2000. After a lot of reading and experimenting I found the solution, which I have laid out in simple steps below. Good Luck!
– Add a linked server
EXEC sp_addlinkedserver
@server=’BOB’,
@srvproduct=”,
@provider=’SQLOLEDB’,
@datasrc=’SERVER’,
@catalog = ‘Northwind’
– get a list of linked servers
EXEC sp_linkedservers
– add linked server login (this user and password has already been created on linked server)
EXEC sp_addlinkedsrvlogin ‘BOB’, ‘false’, NULL, ‘userlogin’, ‘password’
EXEC sp_serveroption @server=’BOB’, @optname = ‘rpc’, @optvalue=’true’
EXEC sp_serveroption @server=’BOB’, @optname=’rpc out’, @optvalue=’true’
–see all linked servers and status
EXEC sp_helpserver
– perform query against linked server
SELECT * FROM OPENQUERY (BOB, ‘SELECT * FROM customers’)
– execute stored proc on Linked Server
declare @CustomerID char(50)
EXEC BOB.Northwind.dbo.CustOrderHist ‘ALFKI’
– drop linked server logins (must be done prior to droping linked server)
EXEC sp_droplinkedsrvlogin ‘BOB’, NULL
– drop linked server
EXEC sp_dropserver ‘BOB’
-- David
|