December 7, 2007 at 12:55 pm
Environment:
Windows Server 2003 SP2
SQL Server 2005 SP2
Ad Hoc Remote Queries is turned on
OLE Automation is turned on.
msjet40.dll version 4.00.9505.0 and is registered
I am able to create a linked server on my development machine successfully using the code block 1 below: I can also connect using openrowset (see code block 2) on my development machine or Opendatasource (code block 3). However, when I either 1) connect to the production server from my machine or 2) remote into the production server, I continue to receive the following error.
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I have made sure that the file exists in the directory I'm pointing to. I've made sure that I have permissions to that directory (I even gave 'everyone' full rights as a test). I've made sure that Ad Hoc Queries Option is turned on in surface area configuration. I'm thinking it has to be a setting I've missed somewhere but for the life of me, I can't find it. I've been chasing this for a couple of days now and would greatly appreciate any assistance.
Code Block 1:
SET NOCOUNT ON;
EXEC master.dbo.sp_addlinkedserver @server=@LinkName, @srvproduct=N'Excel', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=@DataPath, @provstr=N'Excel 8.0'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@LinkName, @optname=N'use remote collation', @optvalue=N'true'
Code Block 2:
SELECT * INTO ##myTempSPECSTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\20071204-160123.XLS;HDR=YES',
'SELECT * FROM [Sheet1$]')
Select * from ##myTempSPECSTable
Code Block 3:
SELECT *
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\20071204-160123.XLS;
Extended Properties=''Excel 8.0''')...[Sheet1$]
December 7, 2007 at 4:50 pm
try debug the location u r trying to access with xp_cmdshell dir command....
master..xp-xmdshell 'dir '
maybe u need to map a network location before accessing it...if u r not using windows authentication....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 9, 2007 at 9:40 am
Thank you for your suggestions. I have tried using Windows Authentication (remoted into the server) and I've tried mixed authentication (connecting from my dev machine). Neither works. I've tried sharing the directory and using UNC path (with share and permission rights set to everyone). I've tried checking the permissions on the file itself. I'm truely stumped. I have not tried checking the dir with xpcmdshell... I'll try that in the morning. Keep in mind that this all worked prior to installing sql server sp2 and the 2 critical updates on windows server 2003.
December 10, 2007 at 8:13 am
Windows Authentication
master..xp_cmdshell 'dir C:\MES\Interface\SPECS\Archive\'
Volume in drive C has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of C:\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,982,476,800 bytes free
NULL
master..xp_cmdshell 'dir \\s02devmes\MES\Interface\SPECS\Archive\'
Volume in drive \\s02devmes\MES has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of \\s02devmes\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,982,427,648 bytes free
NULL
Mixed Authentication
master..xp_cmdshell 'dir C:\MES\Interface\SPECS\Archive\'
Volume in drive C has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of C:\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,978,470,400 bytes free
NULL
master..xp_cmdshell 'dir \\s02devmes\MES\Interface\SPECS\Archive\'
Volume in drive \\s02devmes\MES has no label.
Volume Serial Number is 6C7B-3152
NULL
Directory of \\s02devmes\MES\Interface\SPECS\Archive
NULL
12/10/2007 08:13 AM .
12/10/2007 08:13 AM ..
08/24/2007 12:37 PM 18,944 P09_CF_SPECS.xls
12/07/2007 08:58 AM 28,672 SPEC-CMF-P09-CF-20071204-160123.XLS
2 File(s) 47,616 bytes
2 Dir(s) 15,978,470,400 bytes free
NULL
I'm beginning to think that Microsoft has hosed something since I've posted this on this forum as well as on experts-exchange and no one has a solution/suggestion/anything...
I know I can do this with a dtx package. Hardcoded path/filename works.
December 10, 2007 at 11:22 am
it seems to be more of a basic issue rather than microsoft bug....
one idea is basic file is not available/accesible on the target or its a authentication issue...
all i can guess is its a path issue...try to see if u delete file from query analyzer and test with some sample code with sample excel file available on internet to create linked server....
right click on linked server and in properties check what is the authentication method selected...try changing it to other options and see
there was something like NTLM authentication setting is there when u do gpedit.msc which does clears network access as NTLM security or something else... have a look at this link...http://support.microsoft.com/kb/823659
give it a try and see....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply