No answer from answers.microsoft.com on this problem yet...

Question

No answer from answers.microsoft.com on this problem yet...

Tiiinygecko

SSC Enthusiast

Points: 124
More actions
June 24, 2019 at 6:59 pm

#3657072

I posted the below in the answers.microsoft.com forums, but after three days have yet to receive a reply. Perhaps I posted to the wrong forum there:
***********************************************************************
Below is from answers.microsoft.com
***********************************************************************
looked at this thread...
https://answers.microsoft.com/en-us/education_ms/forum/all/excel-2016-what-is-the-connection-string-for/75eeba94-bb05-4c86-8195-185d07e7ea49

So I tried using the Excel 2007 connection string:
EXEC sp_addlinkedserver
@server = 'ExcelLinkSrv2',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'Z:\Developer\Bruce\Stock_Project\StockSample3smaller.xlsx',
@provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';

However, as soon as I try to query it (Select * from ExcelLinkSrv2...[Stocksample2$]
or even list the tables in the linked server, the entire SQL Server crashes.

The above thread said to use the Excel 2007 connection string. I have done so. It doesn't work.
SQL Server version: 2012
Excel version: 2016

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

My first thought is that the Z:\ drive is a user mapped drive, which the server has no access to. Can you use the fully qualified name (\\server\share\folder\filename)?

Thom A SSC Guru Points: 99126 More actions · Answer 2

crow1969 wrote:

My first thought is that the Z:\ drive is a user mapped drive, which the server has no access to. Can you use the fully qualified name (\\server\share\folder\filename)?

And when you use the fully qualified name, does the service account the SQL Server is running under have access to that share?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

Is the linked server itself correct defined? Test this from SQL with stored procedure

exec sp_testlinkedserver 'ExcelLinkSrv2';

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Tiiinygecko SSC Enthusiast Points: 124 More actions · Answer 4

SSC Guru: Thanks for your reply 🙂 I moved the Excel spreadsheet to C:\Testing\StockSample3smaller.xlsx, and tried:

EXEC sp_addlinkedserver

@server = 'ExcelLinkSrv2',

@srvproduct = 'Excel',

@provider = 'Microsoft.ACE.OLEDB.12.0',

@datasrc = 'C:\Testing\StockSample3smaller.xlsx',

@provstr = 'Excel 12.0 Xml; HDR=YES; IMEX=1';

then...

(Select * from ExcelLinkSrv2...[Stocksample2$]

But it still crashes 🙁

Hanshi: Thanks for your response 🙂

I will try exec sp_testlinkedserver 'ExcelLinkSrv2' and let you know what it says 🙂

Edit:

I tried the above command and received this response:

"Msg 214, Level 16, State 90, Procedure sp_testlinkedserver, Line 1

Procedure expects parameter '@servername' of type 'sysname'."

...Is there an additional parameter I need?

Also, should I add a "GO" to the end of my sp_addlinkedserver command before trying to run sp_testlinkedserver?

Sorry for the newbishness of my last question...

This reply was modified 4 years, 10 months ago by Tiiinygecko.
This reply was modified 4 years, 10 months ago by Tiiinygecko. Reason: Additional information

crow1969 Hall of Fame Points: 3001 More actions · Answer 5

If the server is crashing, you should (hopefully) be able to get some sort of error from the errorlog. When you run the following:

xp_readerrorlog 1

Do the final few lines look like errors? If so, they may provide a lead. If not, the crash may be unexpected. If possible, please post the last few lines of the output from the above command (properly anonymized as necessary, naturally).

Tiiinygecko SSC Enthusiast Points: 124 More actions · Answer 6

Crow1969: Thankyou for your reply 🙂

The last *four* lines of the error log say:

2019-06-21 10:25:47.270 spid66 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.

2019-06-21 10:25:47.280 spid66 Using 'xpsqlbot.dll' version '2011.110.2100' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.

2019-06-21 10:25:50.110 spid67 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.

2019-06-21 10:25:50.220 spid67 Using 'xpstar.dll' version '2011.110.2100' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

Hanshi: I googled the error that I received when running exec sp_testlinkedserver 'ExcelLinkSrv2' and it said that it means the linkserver is not populated...except that the Excel spreadsheet I'm linking to is in fact populated, with ten columns and 200 rows.