I setup the DTS package and worked out the bugs to it. I turned on the Schedule for it, removed the schedule step, and ran it manually from the Jobs icon. It worked just fine, no errors.
I then tried to run the Job from a remote client from SEM, the entire job ran without any errors, but I had no indication of when the job was done so I set about on turning on the Email Notification by following the instructions in KB: 263556. I followed all the steps and when I logged back in under Admin, the test emails were sent without errors.
But, when I went to run the Job from the server that worked before the email setup, all I get is this error:
>>[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from PMCBWIP to [AFACTORY].[dbo].[PMCBWIP] Step DTSRun: Package execution com. The step failed.<<
I can't run the package OR the job from the client anymore after setting up the email. On the client I get the 'SQL server does not exist or access denied' error trying to run the package.
The ODBC is connecting to another server with our ERP/MRP software on it. I checked the ODBC connection by running an Excel query on the SQL server and I get no errors, the data populates correctly. Using Access to create a link to the data works also (Importing the data also works). The DTS package runs without errors also, the table is populated correctly when run on the server.
After several searchs I found KB: 269074. I tried to follow this, but I can't figure out what account needs to be used and where.
Even newly created jobs (after setting up email) don't work, and as soon as they get to the ODBC portion (I have the table info deleted first, that works fine) it errors out.
What do I need to check? I'm new to SQL so please be specific, Thanks!
Tyson
I then tried to run the Job from a remote client from SEM, the entire job ran without any errors, but I had no indication of when the job was done so I set about on turning on the Email Notification by following the instructions in KB: 263556. I followed all the steps and when I logged back in under Admin, the test emails were sent without errors.
But, when I went to run the Job from the server that worked before the email setup, all I get is this error:
>>[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from PMCBWIP to [AFACTORY].[dbo].[PMCBWIP] Step DTSRun: Package execution com. The step failed.<<
I can't run the package OR the job from the client anymore after setting up the email. On the client I get the 'SQL server does not exist or access denied' error trying to run the package.
The ODBC is connecting to another server with our ERP/MRP software on it. I checked the ODBC connection by running an Excel query on the SQL server and I get no errors, the data populates correctly. Using Access to create a link to the data works also (Importing the data also works). The DTS package runs without errors also, the table is populated correctly when run on the server.
After several searchs I found KB: 269074. I tried to follow this, but I can't figure out what account needs to be used and where.
Even newly created jobs (after setting up email) don't work, and as soon as they get to the ODBC portion (I have the table info deleted first, that works fine) it errors out.
What do I need to check? I'm new to SQL so please be specific, Thanks!
Tyson