IDBInitialize::Initialize returned 0x80004005 err creating link server

[diddydustin]

Hi guys,

I am trying to run the following code:

-- local variables
DECLARE @Worksheet AS varchar(255)
DECLARE @SQLString AS varchar(255)
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

DECLARE @intQueID as int
SET @intQueID = 121
DECLARE @strFileName AS varchar(255)
SET @strFileName = '\\devweb\upload\AmazonQue\test_7_7_2005_9_32_12.xls'

-- local variable values
SET @Worksheet = 'Sheet1$'
BEGIN

-- Build insert string
--SET @SQLstring = 'INSERT ' + 'tblQueItem (chrISBN, intQueID, chrRecordStatus) SELECT ISBN, ' + @intQueID + ', "A" FROM ' + @ExcelSource + '...' + @Worksheet
--T @SQLstring = 'INSERT INTO tblQueItem (chrISBN, intQueID, chrRecordStatus) SELECT ISBN, ' + cast(@intQueID as varchar(128)) + ', "A" FROM ExcelSource...' + @Worksheet
SET @SQLstring = 'INSERT INTO tblQueItem
SELECT e.ISBN, ' + cast(@intQueID as varchar(128)) + ', "A"
FROM ExcelSource...' + @Worksheet + ' e
LEFT OUTER JOIN tblQueItem q
ON q.chrISBN = e.ISBN
WHERE isnull(q.chrISBN,"x") <> e.ISBN
GROUP BY e.ISBN'

END

-- Create the link
--EXEC sp_addlinkedserver @server = 'ExcelSource'
-- , @srvproduct = 'Microsoft Excel Workbook'
-- , @provider = 'Microsoft.Jet.OLEDB.4.0'
-- , @datasrc = @strFileName
-- , @provstr = 'Excel 5.0'

-- Set parameter values
SET @server = 'ExcelSource'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = @strFileName
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', null, null, null
-- Load from the link to the table
EXEC (@SQLString)

-- Break the link
EXEC sp_dropserver 'ExcelSource', 'droplogins'


When I try to run the code, I get the following error:


(1 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I've checked various pages on Help and Support from Microsoft but it appears I cannot solve this problem. The version of SQL Server and the Jet database we are using is current. I believe all permissions are set correctly. I cannot seem to pinpoint why we are getting this error.

Can anyone point me in a direction I could go looking? I am stuck.

Thanks
Dustin

It seems I cannot get this to work no matter what I try. I've looked at

 

[micha123]

Do you have an error with remarks here...?

-- Build insert string
--->
--SET @SQLstring = 'INSERT ' + 'tblQueItem (chrISBN, intQueID, chrRecordStatus) SELECT ISBN, ' + @intQueID + ', "A" FROM ' + @ExcelSource + '...' + @Worksheet
--->
--T @SQLstring = 'INSERT INTO tblQueItem (chrISBN, intQueID, chrRecordStatus) SELECT ISBN, ' + cast

M.

 

[diddydustin]

No, sorry that is commented out. It appears on two lines because of the limits on my post. However, in the code those are commented out. What is running is:

-- Build insert string

SET @SQLstring = 'INSERT INTO tblQueItem
SELECT e.ISBN, ' + cast(@intQueID as varchar(128)) + ', "A"
FROM ExcelSource...' + @Worksheet + ' e
LEFT OUTER JOIN tblQueItem q
ON q.chrISBN = e.ISBN
WHERE isnull(q.chrISBN,"x") <> e.ISBN
GROUP BY e.ISBN'

There are no errors from this, we can run this statement fine.

??

 

[micha123]

can you please write a print command before the
EXEC (@SQLString)
like this:

PRINT SQLString

and send me the results?

M.

 

[diddydustin]

(1 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)

INSERT INTO tblQueItem
SELECT e.ISBN, 121, "A"
FROM ExcelSource...Sheet1$ e
LEFT OUTER JOIN tblQueItem q
ON q.chrISBN = e.ISBN
WHERE isnull(q.chrISBN,"x") <> e.ISBN
GROUP BY e.ISBN
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

 

[micha123]

What happens if you just issue a
select * from ExcelSource...Sheet1$ e
?

 

[diddydustin]

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Same error. I believe the problem is in creating the linked server, because when I try to access the linked server through enterprise manager -> security -> linked servers, i get the same error.

 

[micha123]

What happens when you are in the command prompt and you do:
dir \\devweb\upload\AmazonQue\test_7_7_2005_9_32_12.xls?

 

[diddydustin]

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\Documents and Settings\dlyons>dir \\devweb\upload\AmazonQue\test_7_7_2005_9_
2_12.xls
Volume in drive \\devweb\upload has no label.
Volume Serial Number is 0251-237B

Directory of \\devweb\upload\AmazonQue

07/07/2005 09:32a 63,488 test_7_7_2005_9_32_12.xls
1 File(s) 63,488 bytes
0 Dir(s) 77,872,074,752 bytes free

C:\Documents and Settings\dlyons>

 

[diddydustin]

Can you think of anything else it might be?

Dustin

 

[micha123]

I will try it later on a computer with SQL SERVER 2000 (here I don't have one...) and I will let you know.


M.

 

[diddydustin]

Sorry needed to bump this thread because we still can't figure out this issue! Does anyone have an idea what could be going on?

Thanks
Dustin