1 Reply by Matt_tullie (edited by Matt_tullie 12-05-2008 08:05:26)

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

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

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: SQL - connecting servers - importing tables

Matt, when you say it won't allow you to run that query, what is the error it throws back?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: SQL - connecting servers - importing tables

Matt, if your SQL Server is on the network then it is highly likely that it will be configured to only be able to see local disks. So you might have to plead with your IT guys to get your Excel file placed temporarily on the database server C drive, or go with your local database import then linked server idea.

Have you managed to create a linked server yet? If not, let us know what database management tool you are using and I'll try and sort out some instructions. If you have one, then the syntax is to use a fully qualified table name. This is of the form server.database.owner.tablename. Unless you have specified the owner, its most likely that this will be dbo (= database owner). So, as an example if you have got data in a local table called ImportTest and a linked server created called MainServer and you want to move the data into a new table called DestImport in the  TempImports database on that server, here is a query that should work if run against the local database:
SELECT *
INTO MainServer.TempImports.dbo.DestImport
FROM ImportTest

Good luck, and let us know how you get on.

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: SQL - connecting servers - importing tables

Don't want to hijack this thread or anything, but out of interest John, how does Recorder pick up external data and pipe it in when using the import wizard? It looks like it creates a temp Access database, but then what? Is it creating a linked server?

Matt: I can confirm that our server cannot see the same drives our workstations can see, and so we have to be careful when using drive letters. It's possible to put the import file into a local folder, share it, then get to it on the server via Network Places, but I'm not sure that SQL Server can see UNC paths, so you might need to copy it to the server first as John suggested.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

5 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: SQL - connecting servers - importing tables

Hi Charles,

The principle behind the import is that the first step is to create an MS Access database containing all the records in the import, but in the same structure as the Recorder database. This applies to any import format. Once it is in that format we can treat all the imports the same.

A linked server wouldn't work for network versions as the SQL Server instance can't see your local disk, and the Access file can't be placed on the server disks either. So the second step is to create tables in the Access database file which are linked to the SQL Server tables (same principle as the Get External Data option). Once we have these linked tables, we are free first check for duplicates, then to issue commands to Access to dump all the data across into the SQL Server database.

Kind Regards

John van Breda
Biodiverse IT

johnvanbreda's Website

6 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: SQL - connecting servers - importing tables

John, thanks, that explains things nicely.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

7 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: SQL - connecting servers - importing tables

Charles,

For reference - Error message below.

OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='SHEET1$'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'SHEET1$'.  The table either does not exist or the current user does not have permissions on that table.

John,

Matt, if your SQL Server is on the network then it is highly likely that it will be configured to only be able to see local disks. So you might have to plead with your IT guys to get your Excel file placed temporarily on the database server C drive, or go with your local database import then linked server idea.

That would certainly sound like it would explain it.

I suspect the long term solution will have to be linking servers - so will attempt to give this some 'thought time'. 

Im using SSMSE.
I have begun to prod the linking process - setting up server objects on both databases - but it is not very obvious that anything has actually been defined. Online resources are only so useful.

But as I say - I will give it some time and let you know what I have been able to screw up.

Thanks all.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

8 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: SQL - connecting servers - importing tables

If you've got the full version of SQL Server 2005, you might want to have a look at SSIS, which comes with it:

http://msdn.microsoft.com/en-us/library/ms141026.aspx

That'll allow you to bulk import data. It sounds horribly complex, but it has wizards and suchlike to make things easier.

Another option would be to create a local Access database (or ADP) link to the SQL Server, then copy/paste or query the data across like that. This is pretty much what Recorder does as explained by John.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

9 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: SQL - connecting servers - importing tables

Just solved this little niggle of an issue (a 3am 'light bulb' moment) - as a consequence of your note re: the server looking for a local host - I just put the full server path into the query and it did it fine.

One hurdle overcome.

Still to tackle linked server queries.

With thanks.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

10 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: SQL - connecting servers - importing tables

Would be interested to see your final query, if you're able to post it here?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

11 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: SQL - connecting servers - importing tables

Just substitute the directory address as below. (aAfter saving the file to a directory it can easily find). Not sure how one would configure an address of a particular computer - but possible Im sure.

SELECT *
INTO mg_personnel
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=\\carl07\Recording\PERSONNEL.xls;Extended Properties=Excel 8.0')...[SHEET1$]

MAtt

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website