| Author |
 Topic  |
|
|
rgoehring
Starting Member
USA
3 Posts |
 Posted - 08/11/2012 : 13:15:43
|
Hi, All! I am currently running SQL Server 2008 and using SQLCMD to execute some basic scripts on my local machine to update a db on a server. The goal is to insert records from an Excel file into a temp table. The connection to the server and DB works fine. And the execution of CREATE works fine. But the code below fails. It works fine when executed on my machine against a local DB. So, it is related to the remote DB. Based on googling this problem, I believe we must install the Microsoft.ACE.OLEDB driver and Office 2007 Data Connectivity Components on the server. But the IT team said no. I asked for clarification and just waiting for more info. Perhaps this approach creates a security concern? I've only been working with SQL Server for a month. Anyone have any thoughts?
INSERT INTO [Database].[dbo].[tmpInvoiceHistory] ([InvoiceID], [InvoiceDate], [Amount], [ProfileID])
SELECT AI.[InvoiceID], I.[InvoiceDate], I.[Amount], I.[ProfileID]
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\ClientName\Import.xlsx;HDR=YES;IMEX=1;','SELECT * FROM [InvoiceHistory$];') AS I;
Msg 7403, Level 16, State 1, Server WLIDB16, Line 5
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/12/2012 : 01:45:19
|
I can't see the nature of the data in the EXcel spreadsehht but If your IT dept said no, there are a couple of other options:
1) Export Excel to a text file , then complete the Import. Therefore using different drives
2)Use Powershell on your laptop - which I'm assuming has the Excel drivers (or more likely to be alowed by IT dept) . That way you can connect to the spreadsheet and INSERT into the database
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
 |
|
|
rgoehring
Starting Member
USA
3 Posts |
Posted - 08/12/2012 : 09:52:03
|
Hi! Thanks so much for the replies and suggestions! In terms of the downloads of the MS Access Engine and 2007 Office Components, please note that I installed these on my laptop. Would these normally be required on the server side? I wasn't sure. Also, the idea about exporting to a text file and using a different driver is pretty smart, so I'm gonna check it out.
Richard Goehring |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 08/12/2012 : 11:36:44
|
quote:
Originally posted by rgoehring
Hi! Thanks so much for the replies and suggestions! In terms of the downloads of the MS Access Engine and 2007 Office Components, please note that I installed these on my laptop. Would these normally be required on the server side? I wasn't sure. Also, the idea about exporting to a text file and using a different driver is pretty smart, so I'm gonna check it out.
Richard Goehring
i think you should install it on server as OPENROWSET is executed from server
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/13/2012 : 01:34:29
|
If they won't let you intsall the drivers on a Production server , what about requesting they allow install on a lower environment?
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/13/2012 : 01:38:41
|
quote:
In terms of the downloads of the MS Access Engine and 2007 Office Components, please note that I installed these on my laptop. Would these normally be required on the server side? I wasn't sure
If you are executing the SQL server code on the SQL server Instance (regardless of where you are connecting from) and requiring usage of the drivers , then yes, you'll need them installed on the server
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
rgoehring
Starting Member
USA
3 Posts |
Posted - 08/17/2012 : 13:20:51
|
Hi, All! Once again, thanks for the replies!! The IT team had a rough release earlier this week, so I gave them some time to recover but now I'm going to request the drivers that are needed. Will let you know how it goes! LOL
Richard Goehring |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48025 Posts |
Posted - 08/17/2012 : 13:23:02
|
cool...will wait for response...
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/17/2012 : 15:39:46
|
Remember , if you are installing customised (non standard build ) drivers to document for DR or server rebuild purposes.
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
| |
Topic |
|
Insert to Remote DB from Excel File
