Re: SQL - connecting servers - importing tables
I hesitate to start...
1. I want to import lists to the SQL database so that I can reference things within queries - batch edit records - etc.
Our DTB is on the network and wont allow me to use a query of the form;
SELECT *
INTO mg_personnel
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=F:\PERSONNEL.xls;Extended Properties=Excel 8.0')...[SHEET1$]
2. I set up a seperate SQL database locally and use this for temporary table imports (and then realise that I can use this for the Snapshots - which is good).
Nevertheless I still want to build queries that bind the core tables with the temporary ones, i.e. across the two servers.
Ive been looking at 'linking servers' and Transact statements - but am very unclear about the solution (or the syntax) at the moment.
Any clear pointers how to acheive a solution - by solving either 1 or 2 or providing a No.3?
I have tried our IT guys but it is a 'little beyond their remit, and I probably shouldn't be playing with such things anyway'
Nevertheless, I still suspect the failure of No.1 is down to firewalls or permissions or something rather than the query itself which works beautifully on local instances.
Crumbs of comfort gratefully nibbled.
M
Tullie House Museum