December 8, 2013 at 11:49 am
Here is my Problem:
1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:\Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]
All this works fine. I can even put it in to an After Insert Trigger on another table and it works.
My problem is that I need this to work in a scheduled job.
F:\Data is just a folder with files in it.
This info is from a Restaurant POS system and I need to update it every night.
I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.
Anyone have an answer to this. I'm out of trys.
John
December 8, 2013 at 12:23 pm
Who owns the job?
CEWII
December 8, 2013 at 12:52 pm
I've tried everyone and no one.
There is no security on the file side of the linked server so I don't know how to set up the security on the Job.
December 8, 2013 at 1:00 pm
12/08/2013 14:53:04,Daily Data Upload,Error,1,WORKPC\PCAFE,Daily Data Upload,Customer Data,,Executed as user: NT SERVICE\SQLAgent$PCAFE.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PTCAFE". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PTCAFE" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412).
The step failed.,00:00:00,16,7412,,,,0 12/08/2013 14:49:18,Daily Data Upload,Error,,WORKPC\PCAFE,Daily Data Upload,,,The job failed.
Here is what I am getting with no owner.
December 9, 2013 at 6:30 am
hornak.john (12/8/2013)
I've tried everyone and no one.There is no security on the file side of the linked server so I don't know how to set up the security on the Job.
my first knee jerk reaction was that the JET driver cannot be working in the first place, assuming that you are using SQL2014CTE, which is only 64 bit; but you already said it was working, so my next guess is:
from your two posts, you can see that the user context whent he job was running is NT SERVICE\SQLAgent$PCAFE
THAT user, apparently does not have access to the F:\Data , or at least the proxy account user (if it is set up?) doesn't have access to that drive; is that a mapped drive ont he network, or a local drive to the server?
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply