Question Re: Need to retrieve number of rows affected by SQL query in Product Lifecycle Management Q&A

Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 07:31:32 GMT

Hi,

I am executing some update queries in SQL in a xMII transaction. Iam using a SQL Query template for the same and I need to find out how many records were affected by the last executed statement. I have tried doing SELECT @@ROWCOUNT in a second SQL template that executes after the UPDATE SQL Query Template. However, the results are intermittent, sometimes it returns 1 row affected as expected but sometimes returns 0 rows. Can anyone help on this.

Thanks

Re: Need to retrieve number of rows affected by SQL query

former_member201407 — Thu, 08 Jul 2010 07:38:24 GMT

You can try using TRIGGER in another table where number of Audits will be inserted on time of execution of Update of the desired table

Regards

Som

Re: Need to retrieve number of rows affected by SQL query

sidnooradarsh — Thu, 08 Jul 2010 07:38:42 GMT

Hello,

Instead of using separate Query template for @@ROWCOUNT.

Put it as the last statement in the same Query after the update statement.

Regards,

Adarsh

Re: Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 08:04:30 GMT

@Som,

I do not have any control over the database end, hence cannot create new tables/triggers.

@Adarsh,

I tried adding the @@Rowcount query at the end of the update statement.

E.g. I modified my query from Update mytable set col1='Some value' where col2=1234

Update mytable set col1='Some value' where col2=1234

Update mytable set col1='Some value' where col2=1234 ; Select @@RowCount

However this doesnt return any useful message. It simply returns Command Query Successful and not the count of rows affected.

Re: Need to retrieve number of rows affected by SQL query

former_member201407 — Thu, 08 Jul 2010 08:08:50 GMT

You don't need to have control over DB end or go into the same, instead you can create table/trigger from MII end using the mode = Command in SQL Query.

Regards

Som

Re: Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 08:23:14 GMT

Actually, my point is I do not have authorisation to modify table structures or create new ones. I understand this option might work for me, but it involves table/triggger creation. Is there any other way to resolve this?

Re: Need to retrieve number of rows affected by SQL query

sidnooradarsh — Thu, 08 Jul 2010 09:13:06 GMT

Anamika,

1) As per sql best practices use of trigger is not recommend unless you don't have any other option because usually triggers consume more resources and IO since it involves internal monitoring.

2) @@ROWCOUNT should go in same SQL session where you are performing update to identify rows affected else as you said earlier your results will not be accurate

3) The other alternative is to create a Stored Procedure but it again boils down to your authorization issue. This would be easier and better if you have authorization to create SP's. But nevertheless give it a try.

4) Lets forget about SP say that you have an Update Statement like

Update Table1 set column2 = 'abc' where Column2 = 'xyz'

In this case it is evident that you know what you are passing into the Where clause and to what table hence its obvious that you also know how many rows will be affected by using query,

 Select count(*) from Table1 where Column2 = 'abc'

The @@ROWCOUNT is powerful within t-sql with SP, cursors etc..

Hope this helps!!

Regards,

Adarsh

Re: Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 09:35:29 GMT

Thanks Adarsh,

Since @@ROWCOUNT doesnt work for me, Iam now using SELECT COUNT(col1) ... approach.. This has resolved the problem iam facing. But its still a mystery for me why @@ROWCOUNT doesnt work sometimes..

Re: Need to retrieve number of rows affected by SQL query

sidnooradarsh — Thu, 08 Jul 2010 09:47:00 GMT

Glad your problem got solved.

@@ROWCOUNT will not work because when you put @@ROWCOUNT in different Query Template the @@ROWCOUNT doesn't know which table was updated/inserted and it will show you the last/latest affected operation which could be anything other than your update statement hence its inaccurate.

But if you place your @@ROWCOUNT immediately after any update/insert then it is nothing but putting all statements within a single transaction or single user session which should definitely give you results.

Do a small test in sql analyzer you will get to know

Good Luck!!

Regards,

Adarsh

Re: Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 10:30:17 GMT

Thanks..I know it works in SQL analyzer..but bad part is XMII doesnt support such things..

Re: Need to retrieve number of rows affected by SQL query

jcgood25 — Thu, 08 Jul 2010 12:41:38 GMT

Try using an alias for the column:

SELECT @@ROWCOUNT AS NumRows.....

Re: Need to retrieve number of rows affected by SQL query

Former Member — Thu, 08 Jul 2010 12:50:26 GMT

U can try using OUTPUT clause along with your update query within Query template itself.

You can refer this link for more details on OUTPUT clause

http://technet.microsoft.com/en-us/library/ms177564.aspx

Your query mode should be set to Query and not Command.

Regards,

Shalaka