adding info from excel to sql server

[davetherave2]

Hi am trying to add some info from an excel spreadsheet into an sql server 2005 database and have seen that this is possible by using something similar to this:

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)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

now this works fine and creates the linked server that I want. But I just can't seem to get the next bit working which is to get the info from my sheet to the server.

Whenever I try to run any of the commands from this ms link which I got:

http://support.microsoft.com/kb/306397/EN-US/

and this one:

http://support.microsoft.com/kb/321686

I just get an error message saying that it can't find the linked server. So can someone point me the right direction to getting this working.

Many thanks in advance for any help given.

 

[davetherave2]

well I have now sorted this first problem but I now have another issue. I have tested this bit of code:


select * from XLTEST_SP...[projects$]

on two different sql servers one using sql 2k5 and one using sql express edition and it works with the excel linked server.

but when I try to run it on the main sql server i get this error message.


OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure <my procedure>, Line 15
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLTEST_SP".


Now I have had a quick google and nothing seems to work from what I have tested.

I have the latest version of the jet drivers and have tried to use both the domain and local admin accounts for the sql server but nothing seems to work.

Just wondering if anyone had a possible solution.

Ideally I want to copy over my stored procedure to this server as it is the live server.

Thanks in advance.