asked
Data source name not found and no default driver specified
Hello all you kind peoples (the extent of my sucking up) I inherited a SQL 2000 environment (stop laughing) that is causing me some amount of consternation. It is a VM Server 2003 SP1 with SQL Server 2000 SP4 on it. It's sister VM in the QA environment is set up exactly like this one except for the SP; However when running a scheduled job on this particular machine the job fails. Upon checking the DTS package logs I find: Package Error Error Source : Microsoft OLE DB Provider for ODBC Drivers Error Description : The number of failing rows exceeds the maximum specified. Error Code: 0 Error Source= Microsoft OLE DB Provider for ODBC Drivers Error Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error on Line 35 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified I upgraded the OS to Server 2003 SP2 so that it matched the other server where this worked and validated the account being used was an admin and then followed this process: 1.I changed the authentication to Windows and set the default database to OPACBILLING – Connection Test Passed – Job failed Executed as user: AFR\SVC_SQL_Admin_QA. ...: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_7 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_5 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_5 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnProgress: DTSStep_DTSDataPumpTask_3; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_3; 1318 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1318 DTSRun OnFinish: DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = 0 (0) Er... Process Exit Code 1. The step failed. 2.I left the authentication as Windows but removed the default database - Connection Test Passed – Job failed 3.I changed the authentication back to SQL and put in the sa password and left the default database at OPACBILLING - Connection Test Passed – Job failed 4.I left the authentication as SQL and keyed in the sa password but removed the default database - Connection Test Passed – Job failed 5.I executed the DTS Package directly and it failed on the Load Property_Mortgagee Table: The number of failing rows exceeds the maximum specified. Error Code: 0 Error Source= Microsoft OLE DB Provider for ODBC Drivers Error Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error on Line 35 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 6.I opened the DTS package and executed each transformation step – it failed on the Load Property_Mortgagee Table. HOWEVER; I can preview it which smells like a write permissions issue 7.Ensured that the service account was indeed a system /server admin and then explicitly added it to each database as “dbo” – and the job failed 8.Repeated Steps 5 & 6 after the changes I made in step 7 with the same failure I need a lumberjack to clear some of the trees out of my way so I can see what I might be missing. I haven't had to play with SQL 2000 in quite a while so it is possible it is staring me right in the face. Luckily this is not a production environment, but it is a certification VM so I still need to get it this working. **BTW** - ALL other jobs appear to be working just fine. Any assistance provided would be greatly appreciated.
No need to apologise. Some of us still have SQL2000SP3a. ;-)
Is this the only job that uses that particular ODBC connection?
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Bugmesh, I can feel your frustration. We don't have any SQL 2000 versions around anymore so it's been a while for me. Throwing some things out there; When did this start? What was the last thing changed on the system prior to the issue starting? Have you tried deleting the job and recreating? What about looking at the DTS package and reviewing the code? Check the ODBC setup and then try creating a new data source with a different name to test against? Are other jobs using the same data source? Good luck.
Thanks for the input I have reviewed the SQL in the transformation and compared it to that of the DEV and PROD environments and they are identical. I actually dropped the DTS package and replaced it with one from the other VM's with no change. I actually tried the ODBC in several different configurations with no change. I haven't dropped/recreated the single datasource from the package, so I suppose I can give that a go. I appreciate the support. I will advise of the outcome
We have pretty much run the circuit on this one. Our last step will be to CLONE a VM where we know everything is functioning and change it's name to the correct name and IP (after removing the current server from the network). We "assume" this will work, but................ I appreciate the input folks
Must be a driver issue. Try reinstalling the driver.I had a similar kind of issue loading foxpro table to sql server. I had to reinstall both the odbc and oledb driver. Sometimes restarting sql server agent may also solve.
18 People are following this question.
