Error While retrieving data in Query Analyzer from Excel File

Question

Error While retrieving data in Query Analyzer from Excel File

anita-978619

Valued Member

Points: 71
More actions
January 2, 2009 at 11:54 pm

#133467

Hello All,
Thanx in advance....
can anyone help me with this problem.
I had added the linked server excel and trying to retrieve the data thru query
SELECT * FROM EXCEL...Stock$
the same process when i did on my local server it worked fine after adding linked server by i dont know whats wrong with the server.
When i try to do it on my replication server it is not working fine its giving me this error....
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Please anyone help urgently

Viewing 4 posts - 1 through 3 (of 3 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

All your queries on how to play with excel and sql server (including linked servers) should be answered in this excellent article by Phil Factor and Robyn Page.

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

Pradeep Singh

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 2

anita (1/2/2009)
Hello All,
Thanx in advance....
can anyone help me with this problem.
I had added the linked server excel and trying to retrieve the data thru query
SELECT * FROM EXCEL...Stock$
the same process when i did on my local server it worked fine after adding linked server by i dont know whats wrong with the server.
When i try to do it on my replication server it is not working fine its giving me this error....
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Please anyone help urgently

------------------------------try this , u will sucess-----------------------

EXEC sp_addlinkedserver 'ExcelShare',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'\\MyServer\MyShare\Spreadsheets\DistExcl.xls', --- your excel sheet path

NULL,

'Excel 5.0' -- ur execl version

SELECT * FROM OPENQUERY(ExcelShare, 'SELECT * FROM [Sheet1$]') -- replace ur sheetname with sheet1$

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Anita

When do you get the error message - when you attempt to create the linked server, or when you attempt to run a query against it?

Normally I use a stored procedure to set up the server link and test that it works:

CREATE PROCEDURE [dbo].[YourProcName]

@ServerName VARCHAR(30),

@TabName VARCHAR(30),

@xlsFileName VARCHAR(200)

AS

DECLARE @Retval int, @MsgString VARCHAR(400)

SET NOCOUNT ON

-- If the link already exists, then drop it first...

IF OBJECT_ID('tempdb..#Worksheets1') IS NOT NULL

DROP TABLE #Worksheets1

CREATE TABLE #Worksheets1 (srvname varchar(20), providerstring VARCHAR(50))

INSERT INTO #Worksheets1

EXEC('SELECT srvname, providerstring FROM master.dbo.sysservers WHERE srvname = ''' + @ServerName + ''' AND providerstring = ''Excel 8.0;''')

IF (SELECT COUNT(srvname) FROM #Worksheets1 WHERE srvname = @ServerName) > 0

EXEC sp_dropserver @ServerName, 'droplogins'

DROP TABLE #Worksheets1

-- Attempt to create the link...

EXEC sp_addlinkedserver @ServerName,

@srvproduct = '',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@datasrc = @xlsFileName,

@provstr = 'Excel 8.0;'

EXEC sp_addlinkedsrvlogin @ServerName, 'false'

-- Validate the linked server by checking that the tab is available...

IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL

DROP TABLE #Worksheets

CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))

INSERT INTO #Worksheets

EXEC sp_tables_ex @ServerName

IF (SELECT COUNT(TABLE_NAME) FROM #Worksheets WHERE TABLE_NAME = @TabName) = 0

BEGIN

SET @MsgString = 'Tab [' + @TabName + '] or Excel file [' + @xlsFileName + '] not found, linked server [' + @ServerName + '] not created.'

SET @Retval = 1

END

ELSE

BEGIN

SET @MsgString = 'Linked server [' + @ServerName + '] created to file [' +@xlsFileName + '], tab [' + @TabName + '].'

SET @Retval = 0

END

RAISERROR (@MsgString, 0, 1) WITH NOWAIT

RETURN @Retval

Problems with the SQL Server are typically highlighted by a failure of the statement EXEC sp_tables_ex @ServerName

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden