-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Except he's not looking for the identity, but the count. Setting
nocount on kinda kills that...
> We ran into this once before and according to Allaire when
> CFQUERY evaluates
> a multi statement argument it waits for the SQL server to respond
> and specifically looks for the text 'X Rows Returned' Once
> CFQUERY sees this
> statement it stops evaluating any additional information
> returned. A work
> around would be to use a NOCOUNT and also wrap your queries in SQL
> TRANSACTION. So some example syntax would be:
>
> SET NOCOUNT ON (Turns off Record Count Dialog)
> BEGIN TRANSACTION (Begins a Transaction)
> INSERT INTO SomeTable (SomeField) VALUES ('SomeValue')
> SELECT @@IDENTITY AS NewID
> COMMIT TRANSACTION (ends a Transaction)
> SET NOCOUNT OFF (Important as this is a system state and
> would remain set)
The transactions aren't necessary here.
This should be:
SET NOCOUNT ON (Turns off Record Count Dialog)
INSERT INTO SomeTable (SomeField) VALUES ('SomeValue')
SELECT @@IDENTITY AS NewID
SET NOCOUNT OFF (Important as this is a system state and
would
That works to get the identity.
As for getting the count, that's a little nastier...
Maybe something like:
<cfquery datasource="#DSNOperaDW#" name="AddInvoices">
SELECT Count(CSVTemp.Inv_ID) as RowsAffected,
CSVTemp.DIR
FROM Images_INVPROJECT_CSVTempTable as CSVTemp
WHERE (Doc_Type = 'I')
SET NOCOUNT ON
INSERT INTO [Images_Invoice_Index]
(Invoice_ID, DirPath)
SELECT CSVTemp.Inv_ID,
CSVTemp.DIR
FROM Images_INVPROJECT_CSVTempTable as CSVTemp
WHERE (Doc_Type = 'I')
SET NOCOUNT OFF
</cfquery>
Potential problems there are:
1) You're running the SELECT twice, so that's a performance hit
2) If someone runs another query that changes
Images_INVPROJECT_CSVTempTable in between the two queries, you might
be in trouble. I'm not sure whether wrapping that in a transaction
would help or not, but you could try it...
If your database will be accessed only by ColdFusion pages, then
wrapping all cfquerys that change Images_INVPROJECT_CSVTempTable in a
CFLOCK might help. Just speculating, tho...
>
> Hope that helps
Ditto...
Best regards,
Zac Bedell
> ----- Original Message -----
> From: "Rif Kiamil" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Friday, December 01, 2000 6:31 AM
> Subject: CF Does not like MSSQL7 @@ROWCOUNT
>
>
> > Dear All
> >
> > I have the flowing CF Code
> >
> > --- START -
> >
> > <cfparam name="AddInvoices.RowsAffected" default="NOTSET">
> >
> > <cfquery datasource="#DSNOperaDW#" name="AddInvoices">
> > INSERT INTO [Images_Invoice_Index]
> > (Invoice_ID, DirPath)
> >
> > SELECT CSVTemp.Inv_ID,
> > CSVTemp.DIR
> >
> > FROM Images_INVPROJECT_CSVTempTable as CSVTemp
> >
> > WHERE (Doc_Type = 'I')
> >
> > SELECT @@ROWCOUNT as RowsAffected
> > </cfquery>
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>
iQA/AwUBOif3GavhLS1aWPxeEQKh9gCfYlpiJiF0+pp2+5HYeupf1uilD/YAoLSK
9BqUFskiw1VfaprSsmjvw0wx
=6oHo
-----END PGP SIGNATURE-----
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
