Error with linked Server
-
/*
Hello
I've got a problem linking a excel spreadsheet. The slighty sanitized code is below, and runs well on my local server,
but fails when I run it on our production environment. The output including the error I get is:
------------------------------------------------------------------------------------------------------------------------------------------------------
\\server1\Bill Run\BillAccounts_20050611.xls
(1 row(s) affected)
There
Server: Msg 7399, Level 16, State 1, Line 28OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Anyone seen this and know what I need to do to make it work on my production server?
*/
SET NoCount ON
declare @year char(4), @month char(2), @day char(2), @fileString varchar(150), @fileOK int
set @year = Year(GetDate())
set @month = Right('00' + cast(month(GetDate()) as varchar),2)
set @day = Right('00' + cast(day(GetDate()) as varchar),2)
--testing only
set @year = '2005'
set @month = '06'
set @day = '11'
set @fileString = '\\server1\Bill Run\' + @year + @month + @day + '\BillAccounts_' + @year + @month + @day + '.xls'
select @fileString
exec master..xp_fileExist @fileString, @fileOK output
if @fileOK = 1
print 'There'
else
begin
print 'Not there'
return
end
EXEC sp_addlinkedserver @server = 'billNotes'
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @fileString
, @provstr = 'Excel 8.0'
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin 'billNotes', 'false'
GO
declare @year char(4), @month char(2), @day char(2), @billCycle int, @fileString varchar(150), @fileOK int
set @year = Year(GetDate())
set @month = Right('00' + cast(month(GetDate()) as varchar),2)
set @day = Right('00' + cast(day(GetDate()) as varchar),2)
select @year
select @month
select @day
--testing only
set @year = '2005'
set @month = '06'
set @day = '11'
if @day = '01'
set @billCycle = 5
if @day = '04'
set @billCycle = 1
if @day = '11'
set @billCycle = 2
if @day = '18'
set @billCycle = 3
if @day = '25'
set @billCycle = 4
Select a.AccountID
from
Reach_Roms.dbo.Account_t a
inner join
billNotes...sheet1$ n on a.AccountNumber = n.ACCOUNT_NUM
where n.Bill_Cycle = @billCycle
and n.BILLING_STATUS = 'XQ'
and n.ACC_STAT = 'live'
and n.NEXT_BILL_DTM = @year + '-' + @month + '-' + @day
GO
EXEC sp_dropserver 'billNotes', 'droplogins'
GO
--sp_helpserver
-
Try this:
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_droplinkedsrvlogin 'billNotes', NULL
This removes the default mapping that is created by sp_addlinkedserver.
You have to remove all credentials associated with the linked server (for Excel and Accessm unless you have tightened up your access db security) so only run sp_droplinkedsrvlogin but not sp_addlinkedsrvlogin
-
I tried, but now I get
Server: Msg 7416, Level 16, State 1, Line 37
Access to the remote server is denied because no login-mapping exists.
I'm running it from my PC at the mo', but would love to solve it so I could schedule the thing to go automatically...Dave J
-
Make Sure that the MSSQLServer and SQLServerAgent service accounts have permissions to access @fileString path.
You can check out this script for more info:
http://www.sqlservercentral.com/scripts/contributions/763.asp
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply