Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2007-06-15 : 11:57:07
|
| Dear all, a few days, I had posted a thread asking for help with trigger because the one I wrote didn't work for me and I didn't much help with the trigger.So, I switched to stored proc. This is what I am trying to do.I have a sql server db on one server but want to export data from that db to an MS Access db on another server.So, I was instructed to link the databases.So, I performed a linkedServer routine, tested it and everything seems to be working fine.Now, I want to schedule a script to export data from sql server on one server to MS Access on another server.I wrote a very simple stored proc to do this but when I tried to schedule jobs to run this export, I got the following error message:Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.I don't know what it is or how to resolve it. Your assistance is greatly appreciated.When I exec the stored proc manually, it works but when I do it through the scheduled job, I get the error above.Thanks in advance |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-15 : 15:26:33
|
| Sql agent service account needs permission on access file. Why don't use dts by the way? |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2007-06-15 : 17:24:33
|
| Thank you very much for your response.I wanted to but sql the job needs to be scheduled to run 5 times a day, 7am, 12noon, 4pm, 8pm and 12 mid night.dts won't let me do that. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-15 : 23:02:42
|
| Yes, you can run dts package as sql job. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2007-06-16 : 08:37:02
|
| If you would be kind enough to assist me with the dts package,I would greatly appreciate.Below is my code. I have tested it manually and it seems to work.How do I put this code in a dts package and execute it?CREATE PROCEDURE TransferCalAS DELETE FROM OPENQUERY(CalExtract,'SELECT * FROM extrCal') INSERT INTO OPENQUERY (CalExtract, 'SELECT ExtractDate,Jitials,File,caseNum,HDate,Type,JudgeName,CRoom,O,Att FROM ExtrCal') SELECT ExtractDate,Jitials,File,caseNum,HDate,Type,JudgeName,CRoom,O,Att FROM ExtrCal thank you very much. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-16 : 17:44:28
|
| You can find dts details in boos online, just use data export wizard in your case. |
|
|
|
|
|
|
|
|
Exporting data using stored proc