UPDATE STATISTICS using datastage

[satyakunta]

I am trying to do an UPDATE STATISTICS calling a Stored Procedure in ODBC using DataStage. This has to be done after my sequence loads all the tables.The Stored Procedure runs fine updating all the Tables in the SQL Server Query Analyzer, well it is throwing a warning message when i run the DataStage job which calls the sp. I tried updating only one table which works, but that does not serve the purpose, i need to perform update statistics on all tables in DB

Any suggestions would be greatly appreciated.

This the warning message:

[SQL statement:{call "DEV"."dbo"."sp_UpdTable"(?)}
SQLSTATE=01000, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Updating dbo.Contact_Address
SQLSTATE=37000, DBMS.CODE=226
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS statement not allowed within multi-statement transaction.
SQLSTATE=01000, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Updating dbo.Customer_HIN
SQLSTATE=37000, DBMS.CODE=226
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS statement not allowed within multi-statement transaction.
][/code]

[I_Server_Whale]

Hi SatyaKunta,

As a second alternative, Since you have to run this stored procedure after your sequence loads all the tables, you can execute the stored procedure in the after-job subroutine of the sequence job by selecting "ExecDOS". Here pass the windows command for executing a Stored procedure.

I did this at one of the sites. But I don't recall what the command was for executing the stored procedure through DOS.

Do a search for that command on SQL SERVER help. I'm sure you will find it.

If you can't find it. Let me know. I'll see if I can dig into my memory and notes and get an answer.

Thanks,
Naveen.

[satyakunta]

Naveen,

Thanks for the suggestion, i tried lookig for help in sql server but could not find any.

Satya

[kwwilliams]

Have you tried changing your stored procedure so that it is not a multistatement transaction?

It's been quite a while ago, but I did see an error like yours:

[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS statement not allowed within multi-statement transaction.

I fixed it by putting a go in between each update statistics call. That made them not be multi-statement transactions and alleviated my problem. The problem I believe stems from ODBC versus using the SQL Server native products. If you google your issue, you will find that other software vendors have encountered this problem. PeopleSoft and some others were out there. I have heard that there is now a SQL Server Native stage if you are running on a Windows 2003 box. You could check with support.