SQL Server Update Stored Procedure

Keywords: 	  SQL Server Update Stored procedure 

---

Question:

I have a scripe that works well (all but the Update). I'm not getting an error message, the no data is added in the update.

Using :

oConn = "Provider=SQLOLEDB;Data Source=Server1;Initial Catalog=AutoInqS;Integrated Security=SSPI;"
 

It runs select statements OK.

[ sqlB1 = "SELECT * FROM InqSrc WHERE (((SesVar)<>'0'))" ]
rsB1 = RoConn.Execute(sqlB1)

It runs Insert Stored Procedures OK.

[ cSQL = "insert_BasicInquiryReturn_1 @SesVar_1 = '%th1w%', @TagNo_2 = '%th2w%', @Uid = '%th4w%'"
RoConn.Execute(cSQL)]

But I can not get it to run an Update Stored Procedure.

svM = strcat("update_BasicInquiryReturn_ZZ @SV = '%th1w%',", ", " , " @St = '%Rs%'", ", " , " @In = '%Inv%'", ", " , " @TLP = '%TLP%'", ", " , " @DT = '%Dtp%'", ", " , " @DN = '%Dec%'")
RoConn.Execute(svM)

Using Message, it is filling the variables correctly.

I tried an ADODB Command object also with the same Non Results.

I am obviously overlooking something.

Answer:

There are 2 possibilities:

1. You need to put in code to check the 'state' of the Connection if you are immediately following the INSERT with UPDATE. If the INSERT is still 'executing' Winbatch will issue the UPDATE but it will go to dead air.

2. The UPDATE takes longer than the CommandTimeOut for the Connection - big 'gotcha' here, if you set the CommandTimeOut to 0 ( supposedly never times out ) for a Connection, you are hosed- its a well known but little advertised ADO bug.

In that Case , re-issue the Stored Proc as a Command Object, and set the Command.CommandTimeout to unlimited. This happened to me when using AS400 Data and I posted it earlier.