Add Linked Server

• sqlfriends@sql.com

  SSCommitted

  Points: 1504

  More actions

  February 5, 2010 at 6:32 am

  #220271

  Dear All;

  EXEC sp_addlinkedserver N'Revaluation',

  @srvproduct = N'',

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

  @datasrc = N'e:\faijur\rv2.xls',

  @provstr = N'Excel 8.0;'

  GO

  EXEC sp_addlinkedsrvlogin 'Revaluation', 'false'

  Select * From OpenQuery (Revaluation,'select * From [sheet1$]')

  /*------------------------

  Select * From OpenQuery (Revaluation,'select * From [sheet1$]')

  ------------------------*/

  Msg 7399, Level 16, State 1, Line 2

  The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Revaluation" reported an error. The provider did not give any information about the error.

  Msg 7303, Level 16, State 1, Line 2

  Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Revaluation".

  I Have To run the following Qry But Through the following Error ;

  the Following path is correct ; any one help ;

  Thanks;

  A.Faijur

• Lowell

  SSC Guru

  Points: 323546

  More actions

  February 5, 2010 at 6:40 am

  #1114379

  they linked server syntax you used is a bit different from what I typically use; i'm posting it here for reference;

  also, just a confirmation, your server is not 64 bit is it? It must be 32 bit to use the Jet Drivers.

  see if this works for you

  --#################################################################################################

  --Linked server Syntax for Excel

  --#################################################################################################

  DECLARE @server sysname,

  @srvproduct nvarchar(256),

  @provider nvarchar(256),

  @datasrc nvarchar(100),

  @location nvarchar(100),

  @provstr nvarchar(100),

  @catalog sysname,

  @sql varchar(1000)

  --add an excel spreadsheet as a linked server.

  SET @server = 'Revaluation'

  SET @srvproduct = 'Jet 4.0'

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

  set @provstr = 'Excel 8.0;'

  SET @datasrc ='e:\faijur\rv2.xls'

  EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

  EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!