RE: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-15 Thread Scott Thornton 
I have seen similar behaviour, I think it was with CF 8.

Using the Set nocount on option seemed to help.

cfquery datasource=ds name=qInsert
set nocount on
INSERT
INTO TableName (Col1)
VALUES ('foobar')
SELECT @@IDENTITY AS 'NewID'
Set nocount off
/cfquery

PS: SCOPE_IDENTITY() is a better choice. You never know when your table may 
have an underlying insert trigger….


From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of 
Paul Kukiel
Sent: Friday, 13 August 2010 5:27 PM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] CF8 and MSSQL 2005/2008

Steve don't use @@identity you can achieve this result with CF alone:

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_Into_a-16522.html

and with CF9 its more generic:

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html

Paul.
On Fri, Aug 13, 2010 at 5:19 PM, Steve Onnis 
st...@cfcentral.com.aumailto:st...@cfcentral.com.au wrote:
Is there an updated driver for SQL Server?  I have come across this issue where 
if i run this query:-

cfquery datasource=ds name=qInsert
INSERT
INTO TableName (Col1)
VALUES ('foobar')
SELECT @@IDENTITY AS 'NewID'
/cfquery

Under CF8 it errors saying qInsert is undefined but under CF9 it runs fine.

Steve
--
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to 
cfaussie@googlegroups.commailto:cfaussie@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.commailto:cfaussie%2bunsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.



--
Paul Kukiel
--
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

Re: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-15 Thread Paul Kukiel 
I agree.  Where we are not using result attribute on older code we use 
scope_identity() without issues on CF8 and we have never changed drivers.


Paul.

On 16/08/2010 8:02 AM, Scott Thornton wrote:


I have seen similar behaviour, I think it was with CF 8.

Using the Set nocount on option seemed to help.

cfquery datasource=ds name=qInsert

set nocount on

INSERT

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

Set nocount off

/cfquery

PS: SCOPE_IDENTITY() is a better choice. You never know when your 
table may have an underlying insert trigger….


*From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] 
*On Behalf Of *Paul Kukiel

*Sent:* Friday, 13 August 2010 5:27 PM
*To:* cfaussie@googlegroups.com
*Subject:* Re: [cfaussie] CF8 and MSSQL 2005/2008

Steve don't use @@identity you can achieve this result with CF alone:

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_Into_a-16522.html

and with CF9 its more generic:

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html

Paul.

On Fri, Aug 13, 2010 at 5:19 PM, Steve Onnis st...@cfcentral.com.au 
mailto:st...@cfcentral.com.au wrote:


Is there an updated driver for SQL Server?  I have come across this 
issue where if i run this query:-


cfquery datasource=ds name=qInsert

INSERT

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

/cfquery

Under CF8 it errors saying *_qInsert_* is undefined but under CF9 it 
runs fine.


Steve

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com 
mailto:cfaussie@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com 
mailto:cfaussie%2bunsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.





--
Paul Kukiel

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.


--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.


--
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

Re: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-15 Thread Paul Kukiel 
We only use 2005 and 2008.  Both methods work for me I'll assume 8.01 
and all updates have been applied?


Paul.

On 16/08/2010 12:54 PM, Steve Onnis wrote:
what version of SQL though? I havent had any issues with it either 
till i started using SQL2005

the SET NOCOUNT ON/OFF seems to work


*From:* Paul Kukiel [mailto:kuki...@gmail.com]
*Sent:* Monday, 16 August 2010 8:42 AM
*To:* cfaussie@googlegroups.com
*Subject:* Re: [cfaussie] CF8 and MSSQL 2005/2008

I agree.  Where we are not using result attribute on older code we use 
scope_identity() without issues on CF8 and we have never changed drivers.


Paul.

On 16/08/2010 8:02 AM, Scott Thornton wrote:


I have seen similar behaviour, I think it was with CF 8.

Using the Set nocount on option seemed to help.

cfquery datasource=ds name=qInsert

set nocount on

INSERT

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

Set nocount off

/cfquery

PS: SCOPE_IDENTITY() is a better choice. You never know when your 
table may have an underlying insert trigger


*From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] 
*On Behalf Of *Paul Kukiel

*Sent:* Friday, 13 August 2010 5:27 PM
*To:* cfaussie@googlegroups.com
*Subject:* Re: [cfaussie] CF8 and MSSQL 2005/2008

Steve don't use @@identity you can achieve this result with CF alone:

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_Into_a-16522.html

and with CF9 its more generic:

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html

Paul.

On Fri, Aug 13, 2010 at 5:19 PM, Steve Onnis st...@cfcentral.com.au 
mailto:st...@cfcentral.com.au wrote:


Is there an updated driver for SQL Server?  I have come across this 
issue where if i run this query:-


cfquery datasource=ds name=qInsert

INSERT

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

/cfquery

Under CF8 it errors saying *_qInsert_* is undefined but under CF9 it 
runs fine.


Steve

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.
To post to this group, send email to cfaussie@googlegroups.com 
mailto:cfaussie@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com 
mailto:cfaussie%2bunsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.





--
Paul Kukiel

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.


--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

--
You received this message because you are subscribed to the Google 
Groups cfaussie group.

To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.


--
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

RE: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-15 Thread Andrew Scott 
Steve,

 

As far as I know this setting is not on my default, and as the setting means
it will not return the number of rows affected.

 

 

Regards,

Andrew Scott

 http://www.andyscott.id.au/ http://www.andyscott.id.au/

 

 

 

From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Steve Onnis
Sent: Monday, 16 August 2010 12:54 PM
To: cfaussie@googlegroups.com
Subject: RE: [cfaussie] CF8 and MSSQL 2005/2008

 

what version of SQL though? I havent had any issues with it either till i
started using SQL2005

 

the SET NOCOUNT ON/OFF seems to work

 

  _  

From: Paul Kukiel [mailto:kuki...@gmail.com] 
Sent: Monday, 16 August 2010 8:42 AM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] CF8 and MSSQL 2005/2008

I agree.  Where we are not using result attribute on older code we use
scope_identity() without issues on CF8 and we have never changed drivers.

Paul.

On 16/08/2010 8:02 AM, Scott Thornton wrote: 

I have seen similar behaviour, I think it was with CF 8.

Using the Set nocount on option seemed to help.

cfquery datasource=ds name=qInsert

set nocount on

INSERT 

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

Set nocount off

/cfquery

PS: SCOPE_IDENTITY() is a better choice. You never know when your table may
have an underlying insert trigger..

From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf
Of Paul Kukiel
Sent: Friday, 13 August 2010 5:27 PM
To: cfaussie@googlegroups.com
Subject: Re: [cfaussie] CF8 and MSSQL 2005/2008

Steve don't use @@identity you can achieve this result with CF alone:

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_I
nto_a-16522.html

and with CF9 its more generic:

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_r
ecord-16593.html

Paul.

On Fri, Aug 13, 2010 at 5:19 PM, Steve Onnis st...@cfcentral.com.au wrote:

Is there an updated driver for SQL Server?  I have come across this issue
where if i run this query:-

cfquery datasource=ds name=qInsert

INSERT 

INTO TableName (Col1)

VALUES ('foobar')

SELECT @@IDENTITY AS 'NewID'

/cfquery

Under CF8 it errors saying qInsert is undefined but under CF9 it runs fine.

Steve

-- 
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com
mailto:cfaussie%2bunsubscr...@googlegroups.com .
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.




-- 
Paul Kukiel

-- 
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.

-- 
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.

-- 
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.

-- 
You received this message because you are subscribed to the Google Groups
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

[cfaussie] CF8 and MSSQL 2005/2008

2010-08-13 Thread Steve Onnis 
Is there an updated driver for SQL Server?  I have come across this issue
where if i run this query:-
 
cfquery datasource=ds name=qInsert
INSERT 
INTO TableName (Col1)
VALUES ('foobar')
SELECT @@IDENTITY AS 'NewID'
/cfquery
 
Under CF8 it errors saying qInsert is undefined but under CF9 it runs fine.
 
Steve

-- 
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

Re: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-13 Thread Paul Kukiel 
Steve don't use @@identity you can achieve this result with CF alone:

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_Into_a-16522.html

http://cookbooks.adobe.com/post_Getting_the_ID_of_the_Last_Record_Inserted_Into_a-16522.htmland
with CF9 its more generic:

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html

http://cookbooks.adobe.com/post_Generated_key___Get_the_ID_of_the_inserted_record-16593.html
Paul.

On Fri, Aug 13, 2010 at 5:19 PM, Steve Onnis st...@cfcentral.com.au wrote:

  Is there an updated driver for SQL Server?  I have come across this issue
 where if i run this query:-

 cfquery datasource=ds name=qInsert
 INSERT
 INTO TableName (Col1)
 VALUES ('foobar')
 SELECT @@IDENTITY AS 'NewID'
 /cfquery

 Under CF8 it errors saying *qInsert* is undefined but under CF9 it runs
 fine.

 Steve

 --
 You received this message because you are subscribed to the Google Groups
 cfaussie group.
 To post to this group, send email to cfaus...@googlegroups.com.
 To unsubscribe from this group, send email to
 cfaussie+unsubscr...@googlegroups.comcfaussie%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/cfaussie?hl=en.




-- 
Paul Kukiel

-- 
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.

Re: [cfaussie] CF8 and MSSQL 2005/2008

2010-08-13 Thread Kai Koenig 
Yeah, I'm very sure there's been an update on the JDBC drivers from CF 8 to CF 
9.


 Is there an updated driver for SQL Server?  I have come across this issue 
 where if i run this query:-
  
 cfquery datasource=ds name=qInsert
 INSERT
 INTO TableName (Col1)
 VALUES ('foobar')
 SELECT @@IDENTITY AS 'NewID'
 /cfquery
  
 Under CF8 it errors saying qInsert is undefined but under CF9 it runs fine.
  
 Steve
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 cfaussie group.
 To post to this group, send email to cfaus...@googlegroups.com.
 To unsubscribe from this group, send email to 
 cfaussie+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/cfaussie?hl=en.


--
Kai Koenig - Ventego Creative Ltd
ph: +64 4 476 6781 - mob: +64 21 928 365 /  +61 450 132 117
web: http://www.ventego-creative.co.nz
blog: http://www.bloginblack.de
twitter: http://www.twitter.com/agentK
--







-- 
You received this message because you are subscribed to the Google Groups 
cfaussie group.
To post to this group, send email to cfaus...@googlegroups.com.
To unsubscribe from this group, send email to 
cfaussie+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/cfaussie?hl=en.