ODBC error(for one user only)

JPalatianos · Post by **JPalatianos** » Mon Oct 06, 2008 11:21 am

Hi,
We have a job that one of the users is executin and gets the error below. When I execute, no errors at all. The SQL server connection (via ODBC) is identiacal for both of us using a process id(uid/pwd combo) defined in job parameters. Any ideas what may cause this?
Thanks - - John

SELECT src_id,drvr_id,univl_plan_id,seq_num,qty,wght,plan_id,cost_objct_id,cost_objct_grp_id,p_l_prodt_id,last_acty_oper_id,scenario_cd,per_cd FROM cfsstg_plan_cost_objct_stage_2
SQLSTATE=S0022, DBMS.CODE=207
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'scenario_cd'.
SQLSTATE=S0022, DBMS.CODE=207
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'per_cd'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

JPalatianos · Post by **JPalatianos** » Mon Oct 06, 2008 1:20 pm

Looks like we found the problem. The user who was having the problem created the same table(minus the 2 new columns that had teh errors) in the database with his id as the owner. I had him update the job to refernce dbo.table name and it seems like everything is fine now.
I'll also make sure to have him delete his table.

chulett · Post by **chulett** » Mon Oct 06, 2008 1:31 pm

So... you were not in fact logging into the database as the same user?

JPalatianos · Post by **JPalatianos** » Mon Oct 06, 2008 1:35 pm

The weird thing is that we were. Not sure why his execution was choosing the table created by his id even though the ODBC was using a process id for both of us.

Developers execution:
Project:CostFoundation (NJROS1BVA0301)
Job name:cfsstg_plan_cost_objct_stage_2
Event #:922
Timestamp:10/6/2008 1:01:16 PM
Event type:Control
User:PRUDENTIAL\X030953
Message:
Starting Job cfsstg_plan_cost_objct_stage_2.
$cfs_uid = Z8TDSTG [$PROJDEF]
$cfs_pwd = ********
$cfs_dsn = CostFoundation [$PROJDEF]
scenario = ACTUAL
period = 2008-MAR

My execution:
Project:CostFoundation (NJROS1BVA0301)
Job name:cfsstg_plan_cost_objct_stage_2
Event #:901
Timestamp:10/6/2008 12:36:27 PM
Event type:Control
User:PRUDENTIAL\X090842
Message:
Starting Job cfsstg_plan_cost_objct_stage_2.
$cfs_uid = Z8TDSTG [$PROJDEF]
$cfs_pwd = ********
$cfs_dsn = CostFoundation [$PROJDEF]
scenario = ACTUAL
period = 2008-MAR

chulett · Post by **chulett** » Mon Oct 06, 2008 3:00 pm

That does seem odd to me too, but then I don't work with SQL Server so aren't aware of the finer points of its use.