2

The SQL Data Import Wizard doesn't allow me to change the column types in the "Edit Mappings" option...so I started going down the road of "write a query" for the data transfer.

I got some examples online & enabled 'ad hoc queries' on my machine. However, I keep getting the following exception:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Msg 7303, Level 16, State 1, Line 8 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

  • I looked at various posting on this message, but their description & solution makes me think they are for a different scenario or problem altogether
  • There is no actual "linked server"...both the server & file are local

Q: Did I enable the wrong attribute option?
Q: Is my query wrong?

QUERY:

SELECT * FROM OPENROWSET('MSDASQL',
                         'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
                          UID=admin;
                          UserCommitSync=Yes;
                          Threads=3;
                          SafeTransactions=0;
                          ReadOnly=1;
                          PageTimeout=5;
                          MaxScanRows=8;
                          MaxBufferSize=2048;
                          FIL=excel 12.0;
                          DriverId=1046;
                          DefaultDir=C:\Temp;
                          DBQ=C:\Temp\DataMigration.xls', 
                         'SELECT * FROM [AK$]')

SQL to RECONFIGURE SERVER:
This worked fine...just thought I would include it anyway.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

UPDATES:

Unfortunately, I cannot choose "Edit SQL" either
If there is a way to "enable" this option...that would be fine with me. enter image description here

Prisoner ZERO
  • 13,848
  • 21
  • 92
  • 137
  • Does that linked server actually exist? (Server Objects>Linked Servers). It looks like you tried to use the Provider (driver) as the actual linked server. You need to create the linked server first. Do you need to keep this as an Excel source? It's so much easier to work with flat files. – Jacob H Jul 12 '18 at 15:01
  • @JacobH Yeah...sorry. It is what it is. – Prisoner ZERO Jul 12 '18 at 15:02
  • Ok so Linked Server is for querying across servers so you've got an XY problem here for sure. "Solving" this with a linked server is most likely going to cause more problems, if you do get it working. Highly recommend saving the Excel as tab delimited and use Flat File as the source if you can. – Jacob H Jul 12 '18 at 15:04
  • If you MUST use Excel. I believe the solution here is a format file. – Jacob H Jul 12 '18 at 15:05

0 Answers0