Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-10-30 : 21:24:43
|
| I am posting this for information after having had a headache trying to figure out what went wrong in some queries:When using ADO and triggers, all triggers must have a SET NOCOUNT ON if they run any action (if they don't, why have them?). Otherwise, if the returned count happens to be 0, you will get a very uninformative ADO error.Always remember: TRIGGERS ON TABLES MODIFIED BY ADO MUST HAVE A SET NOCOUNT ON!Sarah Berger MCSD |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-10-31 : 02:47:30
|
| I would like to expand that to include stored procedures. Some applications using ADO or OLE DB will behave very strangely when NOCOUNT is off. A good example is Crystal Reports v7, which gets an application error if NOCOUNT is off when accessing SQL2000 procs using OLE DB. |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-10-31 : 11:26:23
|
| Ayup. What will happen in an SP without a NOCOUNT ON is that when ADO will hit the first statement that returns a rowcount e.g. SET @Var, the procedure will exit, and return a count of rows affected. So if e.g. you have a rs.open ProcedureName,conn,,,adCmdStoredProc type of code, instead of getting the data you are expecting you will get a 1 row, 1 column recordset with a rowcount in it.Sarah Berger MCSD |
|
|
|
sherrer
64 Posts |
Posted - 2002-10-31 : 15:48:32
|
| Although if you use the ADO method .NextRecordset you will get the data you are looking for... but the recordset is read only, forward only when returning multiple recordsets to ADO and it has to be a client side cursor. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-01 : 15:33:12
|
| You cannot get a forward only cursor client side. Client side cursors use the Cursor Library, which only supports static cursors.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
Info: ADO and Triggers