MSSQL and ODBC problem.

Oxbow · 18 Aug 2006 at 10:13

Hi guru types.

I have a job on SQL that connects via ODBC to a machine. When the user runs the job manually everything works fine, but when I get a SQL schedule to run it, it hangs.

I think the job is waiting for a password to be input. I have edited the ODBC but I cannot see how to put the password in to avoid the system from asking each time (which is what I think it is doing, although there is no password actually assigned to the job).

Any ideas?

TIA

happytechie · 18 Aug 2006 at 10:32

Oxbow said:
Hi guru types.

I have a job on SQL that connects via ODBC to a machine. When the user runs the job manually everything works fine, but when I get a SQL schedule to run it, it hangs.

I think the job is waiting for a password to be input. I have edited the ODBC but I cannot see how to put the password in to avoid the system from asking each time (which is what I think it is doing, although there is no password actually assigned to the job).

Any ideas?

TIA

you can encode a password in a connection string. Are you using windows authentication or seperate SQLServer usernames/passwords.

Oxbow · 18 Aug 2006 at 10:53

happytechie said:
you can encode a password in a connection string. Are you using windows authentication or seperate SQLServer usernames/passwords.

Its actually AS400, not SQL, although the job runs on SQL it seems to hang when it trys to access AS400.

Does this make sense?

happytechie · 18 Aug 2006 at 11:48

that makes perfect sense I think...

where does it need a password the logon to AS400 or does the scheduled job need a logon to run?

the remote table link in SQL Server that is actually ODBC to the AS400 database should still have a connection string property with it iirc.

HT

Oxbow · 18 Aug 2006 at 13:37

Thanks for this so far.

Right, there is a ODBC connection with the AS400 user and password. In DTS it is used to connect to the database and extract the data to be loaded on SQL

Does that clarify it?

oneilldo · 18 Aug 2006 at 18:05

in SQL Server the SQL Server agent and SQl Server are seperate services so check to see if its a seperate service the runs the scheduled job and ensure the logon is the same for each