Re: Stored Proc Help
Robert Rawlins - Think Blue wrote:
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1
GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1
create procedure usp_Something AS
BULK INSERT YourStagingTable
FROM 'c:\YourInputFile.txt'
INSERT INTO TableWithMacAddresses
SELECT MacAddress, Port
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)
You really want a DISTINCT here.
INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable
GO
INSERT INTO TableWithLoggingData (x, y, z)
SELECT TWMA.PK, YST.y, YST.z
FROM YourStagingTable YST INNER JOIN TableWithMacAddresses TWMA ON
YST.MacAddress = TWMA.MacAddress
Jochem
~|
Create robust enterprise, web RIAs.
Upgrade integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275997
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored Proc Help
Thanks for that Jochem,
What would you suggest as a Staging Table? Does SQL Server have a specific
function for creating this temporary table? Or do I need to build and drop
one myself?
What would you suggest?
Thanks,
Rob
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: 23 April 2007 09:34
To: CF-Talk
Subject: Re: Stored Proc Help
Robert Rawlins - Think Blue wrote:
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1
GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1
create procedure usp_Something AS
BULK INSERT YourStagingTable
FROM 'c:\YourInputFile.txt'
INSERT INTO TableWithMacAddresses
SELECT MacAddress, Port
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)
You really want a DISTINCT here.
INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable
GO
INSERT INTO TableWithLoggingData (x, y, z)
SELECT TWMA.PK, YST.y, YST.z
FROM YourStagingTable YST INNER JOIN TableWithMacAddresses TWMA ON
YST.MacAddress = TWMA.MacAddress
Jochem
~|
ColdFusion MX7 and Flex 2
Build sales marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275998
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored Proc Help
Also guys,
When trying to switch out that bulk insert with a parameter I get the
following error when running the SQL that should create the procedure.
BULK INSERT DeviceStaging
FROM @location
Msg 102, Level 15, State 1, Procedure usp_CommitDeviceLog, Line 4
Incorrect syntax near '@location'.
Any ideas?
Thanks,
Rob
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: 23 April 2007 09:34
To: CF-Talk
Subject: Re: Stored Proc Help
Robert Rawlins - Think Blue wrote:
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1
GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1
create procedure usp_Something AS
BULK INSERT YourStagingTable
FROM 'c:\YourInputFile.txt'
INSERT INTO TableWithMacAddresses
SELECT MacAddress, Port
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)
You really want a DISTINCT here.
INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable
GO
INSERT INTO TableWithLoggingData (x, y, z)
SELECT TWMA.PK, YST.y, YST.z
FROM YourStagingTable YST INNER JOIN TableWithMacAddresses TWMA ON
YST.MacAddress = TWMA.MacAddress
Jochem
~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276001
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored Proc Help
Robert Rawlins - Think Blue wrote: What would you suggest as a Staging Table? Does SQL Server have a specific function for creating this temporary table? Or do I need to build and drop one myself? What would you suggest? I prefer to make data loading / transformation tables permanent tables in their own schema. That way they are always accessible for schema inspection and I can create a log table in the same schema too. And if something goes wrong halfway the data will still be accessible, while temporary table are generally not accessible after the transaction has been aborted. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276005 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc/CF help!!
How are you calling the proc? If you use cfstoredproc Then use this at the bottom: cfprocresult name=resultset_name_1 resultset=1 cfprocresult name= resultset_name_1 resultset=2 ~Brad -Original Message- From: Tim Heald [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 11:07 AM To: CF-Talk Subject: Stored Proc/CF help!! Ok, Sorry for the OT-ish question, but I was gone for so long that I am having some crazy problems writing this SQL Server Stored proc. I have something like this: Create procedure get_stuff(@status int) As Select @status = status From table Where things = 1 If @status = 1 begin select cdata, cdate from table where cdata = 1 end End if How would I return the second query to CF? I know that's not the greatest example, and it prolly won't even run, but it's close. I can answer questions if need be. TIA Tim Heald [EMAIL PROTECTED] ColdFusion Developer Signal Solutions, Inc Work : 202-224-1224 Cell : 703-765-0618 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228873 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc/CF help!!
They are both still undefined. This is killing me because the procedure compiled just fine, it doesn't have an IN variables, is there something I need to do to specify that they are OUT result sets? -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:27 PM To: CF-Talk Subject: FW: Stored Proc/CF help!! Sorry, correction: cfprocresult name=resultset_name_1 resultset=1 cfprocresult name= resultset_name_2 resultset=2 I mistyped. The result sets would need unique names. (but you probably figured that out anyway...) ~Brad ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228877 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc/CF help!!
Wait a minute-- I just looked at your code again. You really only have one resultset. The first select is just setting the value of your @status variable. I don't know what database you are using, but in SQL server 2000 your proc would look like this: CREATE PROCEDURE dbo.get_stuff @status AS varchar(255) OUTPUT AS Notice the output after the parameter declaration. Then your cfstoredprocparam needs to look like this: cfstoredproc procedure= get_stuff datasource=#request.dsn_write# returncode=Yes cfprocparam cfsqltype=CF_SQL_INTEGER dbvarname=@status type=InOut variable=name_of_variable_for_status cfprocresult name=name_of_variable_for_result_set resultset=1 /cfstoredproc Notice the type=InOut In theory this would work if your database is like mine. :) ~Brad -Original Message- From: Tim Heald [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 11:30 AM To: CF-Talk Subject: RE: Stored Proc/CF help!! They are both still undefined. This is killing me because the procedure compiled just fine, it doesn't have an IN variables, is there something I need to do to specify that they are OUT result sets? -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:27 PM To: CF-Talk Subject: FW: Stored Proc/CF help!! Sorry, correction: cfprocresult name=resultset_name_1 resultset=1 cfprocresult name= resultset_name_2 resultset=2 I mistyped. The result sets would need unique names. (but you probably figured that out anyway...) ~Brad ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228880 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc/CF help!!
Hey thanks man. -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:40 PM To: CF-Talk Subject: RE: Stored Proc/CF help!! Wait a minute-- I just looked at your code again. You really only have one resultset. The first select is just setting the value of your @status variable. I don't know what database you are using, but in SQL server 2000 your proc would look like this: CREATE PROCEDURE dbo.get_stuff @status AS varchar(255) OUTPUT AS Notice the output after the parameter declaration. Then your cfstoredprocparam needs to look like this: cfstoredproc procedure= get_stuff datasource=#request.dsn_write# returncode=Yes cfprocparam cfsqltype=CF_SQL_INTEGER dbvarname=@status type=InOut variable=name_of_variable_for_status cfprocresult name=name_of_variable_for_result_set resultset=1 /cfstoredproc Notice the type=InOut In theory this would work if your database is like mine. :) ~Brad -Original Message- From: Tim Heald [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 11:30 AM To: CF-Talk Subject: RE: Stored Proc/CF help!! They are both still undefined. This is killing me because the procedure compiled just fine, it doesn't have an IN variables, is there something I need to do to specify that they are OUT result sets? -Original Message- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 12:27 PM To: CF-Talk Subject: FW: Stored Proc/CF help!! Sorry, correction: cfprocresult name=resultset_name_1 resultset=1 cfprocresult name= resultset_name_2 resultset=2 I mistyped. The result sets would need unique names. (but you probably figured that out anyway...) ~Brad ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228885 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
Indeed, it is not good practice to use this DB. Ho hum. -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: 05 December 2005 19:36 To: CF-Talk Subject: RE: Stored Proc At 08:11 AM 12/5/2005, Robertson-Ravo, Neil (RX) wrote: That said, though, claiming it's detrimental to performance is wildly false. Erm, no it is true... check BOL. Naming it with sp_ can reduce reduce performance. When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name. From BOL. Exactly. As I said, I forgot about mentioning that this was to be created in the master database, otherwise you'd *not* want to use the prefix. -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226147 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
Mathematical operation like this could be detrimental to SQL's performance - you would have to run test on thisI reckon I this case a CFC would be better placed for memory intensive operations. -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: 02 December 2005 22:57 To: CF-Talk Subject: Stored Proc Hi guys. As they say in CF good practices. Make your database do the work for you. I have a CFC with this function below. I need to make it into a stored procedure. The function is: cffunction name=LatLonDist returntype=numeric access=public cfargument name=lat1 type=numeric required=true cfargument name=lon1 type=numeric required=true cfargument name=lat2 type=numeric required=true cfargument name=lon2 type=numeric required=true cfargument name=units type=string required=true cfset var myResult = 0 CFIF (arguments.lat1 GT 90 OR arguments.lat1 LT -90 OR arguments.lon1 GT 180 OR arguments.lon1 LT -180 OR arguments.lat2 GT 90 OR arguments.lat2 LT -90 OR arguments.lon2 GT 280 OR arguments.lon2 LT -280) cfset myResult = 0 cfelse cfset lat1 = arguments.lat1 * pi()/180 cfset lon1 = arguments.lon1 * pi()/180 cfset lat2 = arguments.lat2 * pi()/180 cfset lon2 = arguments.lon2 * pi()/180 cfset UnitConverter = 1.150779448 cfif arguments.units eq 'nm' cfset UnitConverter = 1.0 /cfif cfif arguments.units eq 'km' cfset UnitConverter = 1.852 /cfif cfset distance = 2*asin(sqr((sin((arguments.lat1-arguments.lat2)/2))^2 + cos(arguments.lat1)*cos(arguments.lat2)*(sin(( arguments.lon1-arguments.lon2)/2))^2)) cfif arguments.units neq 'radians' cfset distance = UnitConverter * 60 * distance * 180/pi() /cfif cfset myResult=distance /CFIF cfreturn myResult /cffunction Thanks, K ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226060 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
NOTE: You should avoid using sp_ as the prefix for user stored procedures - it can be detrimental to performance. -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: 03 December 2005 16:15 To: CF-Talk Subject: Re: Stored Proc Here's a small stored procedure that does the difference from two sets of latitude/longitude. create procedure dbo.sp_latLongDist @lat1 numeric, @long1 numeric, @lat2 numeric, @long2 numeric, @units char(2) as set nocount on ; declare @mult numeric ; if @units = 'km' set @mult = 1.852 else set @mult = 1.0 ; select sqrt(power((69.1*(@[EMAIL PROTECTED])),2)+power((69.1*(@[EMAIL PROTECTED])*cos(@lat1/57. 3)),2))[EMAIL PROTECTED] as distance go And example usage: exec dbo.sp_latLongDist 34.09011, -118.4065, 42.34892, -71.04651, 'nm' ; -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226061 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
At 03:51 AM 12/5/2005, you wrote: NOTE: You should avoid using sp_ as the prefix for user stored procedures - it can be detrimental to performance. The purpose of that, in this case, is use in any database without having to specifically create the procedure in each when created in the master database. I'd forgotten about mentioning that. You'd also need to grant permissions to it, a la: grant execute on master..sp_latLongDist to public ; If you're not the administrator of the database server, though, yeah - you should probably not prefix it and probably can't create it in the master database. That said, though, claiming it's detrimental to performance is wildly false. -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: 03 December 2005 16:15 To: CF-Talk Subject: Re: Stored Proc Here's a small stored procedure that does the difference from two sets of latitude/longitude. create procedure dbo.sp_latLongDist @lat1 numeric, @long1 numeric, @lat2 numeric, @long2 numeric, @units char(2) as set nocount on ; declare @mult numeric ; if @units = 'km' set @mult = 1.852 else set @mult = 1.0 ; select sqrt(power((69.1*(@[EMAIL PROTECTED])),2)+power((69.1*(@[EMAIL PROTECTED])*cos(@lat1/57. 3)),2))[EMAIL PROTECTED] as distance go And example usage: exec dbo.sp_latLongDist 34.09011, -118.4065, 42.34892, -71.04651, 'nm' ; -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226062 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
That said, though, claiming it's detrimental to performance is wildly false. Erm, no it is true... check BOL. Naming it with sp_ can reduce reduce performance. When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name. From BOL. Stored procedures with the prefix sp_ are first looked up in master. If a user-defined stored procedure has the same name as a system-supplied stored procedure residing in master, SQL Server always finds the system-supplied stored procedure. Expect different results as compared to earlier versions of SQL Server. Expect a difference in behavior when calling user-defined stored procedures with the sp_ prefix. Either explicitly qualify the name of the user-defined stored procedure, or rename the user-defined stored procedure. Also Google user stored procedures + SP_ -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: 05 December 2005 14:10 To: CF-Talk Subject: RE: Stored Proc At 03:51 AM 12/5/2005, you wrote: NOTE: You should avoid using sp_ as the prefix for user stored procedures - it can be detrimental to performance. The purpose of that, in this case, is use in any database without having to specifically create the procedure in each when created in the master database. I'd forgotten about mentioning that. You'd also need to grant permissions to it, a la: grant execute on master..sp_latLongDist to public ; If you're not the administrator of the database server, though, yeah - you should probably not prefix it and probably can't create it in the master database. That said, though, claiming it's detrimental to performance is wildly false. -Original Message- From: Phillip Beazley [mailto:[EMAIL PROTECTED] Sent: 03 December 2005 16:15 To: CF-Talk Subject: Re: Stored Proc This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226064 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
At 08:11 AM 12/5/2005, Robertson-Ravo, Neil (RX) wrote: That said, though, claiming it's detrimental to performance is wildly false. Erm, no it is true... check BOL. Naming it with sp_ can reduce reduce performance. When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name. From BOL. Exactly. As I said, I forgot about mentioning that this was to be created in the master database, otherwise you'd *not* want to use the prefix. -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226083 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Here's a small stored procedure that does the difference from two sets of latitude/longitude. create procedure dbo.sp_latLongDist @lat1 numeric, @long1 numeric, @lat2 numeric, @long2 numeric, @units char(2) as set nocount on ; declare @mult numeric ; if @units = 'km' set @mult = 1.852 else set @mult = 1.0 ; select sqrt(power((69.1*(@[EMAIL PROTECTED])),2)+power((69.1*(@[EMAIL PROTECTED])*cos(@lat1/57.3)),2))[EMAIL PROTECTED] as distance go And example usage: exec dbo.sp_latLongDist 34.09011, -118.4065, 42.34892, -71.04651, 'nm' ; -- Phillip Beazley Onvix -- Website Hosting, Development E-commerce Visit http://www.onvix.com/ or call 727-578-9600. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226018 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Ken, I have worked on apps that had similar needs for determinig distance based on lat a long. My solutoin invovled creating a UDF in SQL Server to return teh distance between 2 points using a formula similar to what you are using. Then I just SELECT the TOP 1 and sort based on that distance DESC. -- Scott Stroz Boyzoid.com ___ Some days you are the dog, Some days you are the tree. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226042 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
As they say in CF good practices. Make your database do the work for you. That's news to me. Why take a good cfc that is working just fine and turn it into a stored proc? Assuming that is the way you are going to go, regardless of my ramblings, what DB are you running? Stored procedures are different in every DB... --- [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A2 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225981 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
I know where you are coming from, Munson. However, believe me, I have good reason to do that. I have a table db_postalcodes of all US zipcodes and their latitutde and longitudes. I am building a store locator app. My logic is to take the zipcode entered by the user, fetch the latitutde and logitude for it, compare it with the zipcodes of our store locations. My CFC calculates the distance between 2 given zipcodes. Then I present the least distance to the user. So far, I have code that looks like this: (It is very taxing on the database): cfobject component=Resources name=ObjRes cfset userzip=86403 !--- zip entered by user--- cfquery name=GetUserZip datasource=#request.datasource# select latitude, longitude from db_postalcodes where ZIPCode='#userzip#' /cfquery cfset lat1=#GetUserZip.latitude# cfset lon1=#GetUserZip.longitude# cfquery name=GetQDZips datasource=#request.datasource# select db_users.user_id, db_users.user_postal AS QDZip from db_users where db_users.user_postal is not null AND db_users.country_id=218 AND user_id IN (Select user_ID from db_quikdrop where inactive IS NULL) /cfquery cfset distlist= cfloop query=GetQDZips cfquery name=GetQDZip datasource=#request.datasource# select latitude, longitude from db_postalcodes where ZIPCode = '#Left( GetQDZips.QDZip,5)#' /cfquery cfset lat2=#GetQDZip.latitude# cfset lon2=#GetQDZip.longitude# cfif IsNumeric(lat2) AND IsNumeric(lon2) cfset distance=ObjRes.LatLonDist(lat1,lon1,lat2,lon2,'nm') cfset distlist= ListAppend(distlist,distance) /cfif /cfloop cfset distlist=listsort(distlist,numeric) cfoutput#distlist#/cfoutput I would appreciate ways to make this code more efficient. Thanks, K On 12/2/05, Munson, Jacob [EMAIL PROTECTED] wrote: As they say in CF good practices. Make your database do the work for you. That's news to me. Why take a good cfc that is working just fine and turn it into a stored proc? Assuming that is the way you are going to go, regardless of my ramblings, what DB are you running? Stored procedures are different in every DB... --- [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A2 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225982 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc
One thing I would do to speed things up a little is use cfqueryparam. Not only is this good to make sure the user isn't performing a sql injection attack, but cfqueryparam will send a compiled query to the DB, which can speed things up (otherwise the DB has to compile every query, including all the ones in your loop). You'll have to play around to see if this speeds things up or not. -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Friday, December 02, 2005 4:33 PM To: CF-Talk Subject: Re: Stored Proc I know where you are coming from, Munson. However, believe me, I have good reason to do that. I have a table db_postalcodes of all US zipcodes and their latitutde and longitudes. I am building a store locator app. My logic is to take the zipcode entered by the user, fetch the latitutde and logitude for it, compare it with the zipcodes of our store locations. My CFC calculates the distance between 2 given zipcodes. Then I present the least distance to the user. So far, I have code that looks like this: (It is very taxing on the database): cfobject component=Resources name=ObjRes cfset userzip=86403 !--- zip entered by user--- cfquery name=GetUserZip datasource=#request.datasource# select latitude, longitude from db_postalcodes where ZIPCode='#userzip#' /cfquery cfset lat1=#GetUserZip.latitude# cfset lon1=#GetUserZip.longitude# cfquery name=GetQDZips datasource=#request.datasource# select db_users.user_id, db_users.user_postal AS QDZip from db_users where db_users.user_postal is not null AND db_users.country_id=218 AND user_id IN (Select user_ID from db_quikdrop where inactive IS NULL) /cfquery cfset distlist= cfloop query=GetQDZips cfquery name=GetQDZip datasource=#request.datasource# select latitude, longitude from db_postalcodes where ZIPCode = '#Left( GetQDZips.QDZip,5)#' /cfquery cfset lat2=#GetQDZip.latitude# cfset lon2=#GetQDZip.longitude# cfif IsNumeric(lat2) AND IsNumeric(lon2) cfset distance=ObjRes.LatLonDist(lat1,lon1,lat2,lon2,'nm') cfset distlist= ListAppend(distlist,distance) /cfif /cfloop cfset distlist=listsort(distlist,numeric) cfoutput#distlist#/cfoutput I would appreciate ways to make this code more efficient. Thanks, K On 12/2/05, Munson, Jacob [EMAIL PROTECTED] wrote: As they say in CF good practices. Make your database do the work for you. That's news to me. Why take a good cfc that is working just fine and turn it into a stored proc? Assuming that is the way you are going to go, regardless of my ramblings, what DB are you running? Stored procedures are different in every DB... --- [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A2 ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225983 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
I do this type of search on a site of mine, and it just uses SQL to do it all. I pass in a zip to it and the sql does all the work, not cf. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225991 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
First, I get all the long/lat info from the zip table with a query named passedzip. No cfqueryparams here so don't say anything bout it. And I did this two years ago before I got decent. lol SELECT * FROM tblzipcodes WHERE zipcode=#FORM.passedzipcode# This runs the query to display the items found within the passed radius from the form. Just drop out all the crap fields. SELECT tblChorusGroups.ChorusID, tblChorusGroups.ChorusName, tblChorusGroups.ChorusCity, tblChorusGroups.ChorusState, tblChorusGroups.ChorusZip, tblChorusTypes.ChorusTypeCategory, tblChorusTypes.ChorusID, tblzipcodes.zipcode, tblzipcodes.longitude, tblzipcodes.latitude, ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) AS distance FROM tblChorusGroups, tblChorusTypes, tblzipcodes WHERE tblChorusGroups.ChorusID = tblChorusTypes.ChorusID AND tblChorusGroups.ChorusZip = tblzipcodes.zipcode AND (latitude = #passedzip.latitude# - (#FORM.passedradius#/111)) AND (latitude = #passedzip.latitude# + (#FORM.passedradius#/111)) AND (longitude = #passedzip.longitude# - (#FORM.passedradius#/111)) AND (longitude = #passedzip.longitude# + (#FORM.passedradius#/111)) cfif IsDefined(grouptype) AND #FORM.grouptype# NEQ AND tblChorusGroups.ChorusGroupType = '#FORM.grouptype#'/cfif cfif IsDefined(groupcategory) AND #FORM.groupcategory# NEQ AND tblChorusTypes.ChorusTypeCategory = '#FORM.groupcategory#'/cfif GROUP BY tblChorusGroups.ChorusName ORDER BY distance Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225994 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Thanks Will. Can you please share your code with me? Or give me some hints how to do it? Regards, K On 12/2/05, Will Tomlinson [EMAIL PROTECTED] wrote: I do this type of search on a site of mine, and it just uses SQL to do it all. I pass in a zip to it and the sql does all the work, not cf. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225996 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Thanks Will. Can you tell if the formula that you used to calculate the distance between 2 latitutdes and longitudes is the flat-earth formula or the spherical earth formula? I am talking about: ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) Thanks again, K On 12/2/05, Will Tomlinson [EMAIL PROTECTED] wrote: First, I get all the long/lat info from the zip table with a query named passedzip. No cfqueryparams here so don't say anything bout it. And I did this two years ago before I got decent. lol SELECT * FROM tblzipcodes WHERE zipcode=#FORM.passedzipcode# This runs the query to display the items found within the passed radius from the form. Just drop out all the crap fields. SELECT tblChorusGroups.ChorusID, tblChorusGroups.ChorusName, tblChorusGroups.ChorusCity, tblChorusGroups.ChorusState, tblChorusGroups.ChorusZip, tblChorusTypes.ChorusTypeCategory, tblChorusTypes.ChorusID, tblzipcodes.zipcode, tblzipcodes.longitude, tblzipcodes.latitude, ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) AS distance FROM tblChorusGroups, tblChorusTypes, tblzipcodes WHERE tblChorusGroups.ChorusID = tblChorusTypes.ChorusID AND tblChorusGroups.ChorusZip = tblzipcodes.zipcode AND (latitude = #passedzip.latitude# - (#FORM.passedradius#/111)) AND (latitude = #passedzip.latitude# + (#FORM.passedradius#/111)) AND (longitude = #passedzip.longitude# - (#FORM.passedradius#/111)) AND (longitude = #passedzip.longitude# + (#FORM.passedradius#/111)) cfif IsDefined(grouptype) AND #FORM.grouptype# NEQ AND tblChorusGroups.ChorusGroupType = '#FORM.grouptype#'/cfif cfif IsDefined(groupcategory) AND #FORM.groupcategory# NEQ AND tblChorusTypes.ChorusTypeCategory = '#FORM.groupcategory#'/cfif GROUP BY tblChorusGroups.ChorusName ORDER BY distance Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225997 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
lol, this otta be a good one lol ~Dave the disruptor~ good sites - make money getting rid of ie :) http://explorerdestroyer.com/ http://www.killbillsbrowser.com/ From: Ken [EMAIL PROTECTED] Sent: Friday, December 02, 2005 10:04 PM To: CF-Talk cf-talk@houseoffusion.com Subject: Re: Stored Proc Thanks Will. Can you please share your code with me? Or give me some hints how to do it? Regards, K On 12/2/05, Will Tomlinson wrote: I do this type of search on a site of mine, and it just uses SQL to do it all. I pass in a zip to it and the sql does all the work, not cf. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225999 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Thanks Will. Can you tell if the formula that you used to calculate the distance between 2 latitutdes and longitudes is the flat-earth formula or the spherical earth formula? I am talking about: ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) I don't know what it is. I think I found it through google a while back. I DO know that it seems to give fairly accurate results. And it also displays the distance in the results, which is nice. Will ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226000 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
Thanks Will. I just got this formula. But I need to convert this into SQL syntax: 2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2)) The POWER function of SQL is confusing. This formula uses the curved earth formula. - K On 12/2/05, Will Tomlinson [EMAIL PROTECTED] wrote: Thanks Will. Can you tell if the formula that you used to calculate the distance between 2 latitutdes and longitudes is the flat-earth formula or the spherical earth formula? I am talking about: ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) I don't know what it is. I think I found it through google a while back. I DO know that it seems to give fairly accurate results. And it also displays the distance in the results, which is nice. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226003 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc
he got it from easycfm.com did u look there yet? Or you can scan the archives on here as there are quite a few code snips people have shown over the last few years. ~Dave the disruptor~ good sites - make money getting rid of ie :) http://explorerdestroyer.com/ http://www.killbillsbrowser.com/ From: Ken [EMAIL PROTECTED] Sent: Friday, December 02, 2005 11:05 PM To: CF-Talk cf-talk@houseoffusion.com Subject: Re: Stored Proc Thanks Will. I just got this formula. But I need to convert this into SQL syntax: 2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2)) The POWER function of SQL is confusing. This formula uses the curved earth formula. - K On 12/2/05, Will Tomlinson wrote: Thanks Will. Can you tell if the formula that you used to calculate the distance between 2 latitutdes and longitudes is the flat-earth formula or the spherical earth formula? I am talking about: ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958 * 3963) I don't know what it is. I think I found it through google a while back. I DO know that it seems to give fairly accurate results. And it also displays the distance in the results, which is nice. Will ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226004 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout [mailto:[EMAIL PROTECTED] Sent: 21 January 2005 00:13 To: CF-Talk Subject: RE: Stored Proc Question I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191323 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Good point. But you can't use query caching without placing in application or session scope and still use cfqueryparam -Original Message- From: Robertson-Ravo, Neil (RX) In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191341 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Caching with CFQUERY? To be honest I rarely use CFQUERY now apart from QoQ so I am a tad rusty on its ins and outs ;-) -Original Message- From: Andy Ousterhout [mailto:[EMAIL PROTECTED] Sent: 21 January 2005 13:51 To: CF-Talk Subject: RE: Stored Proc Question Good point. But you can't use query caching without placing in application or session scope and still use cfqueryparam -Original Message- From: Robertson-Ravo, Neil (RX) In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191342 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: I thought that even with simple queries that Stored Procs where much faster? That is not my experience. YMMV Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191345 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Has anyone tested simple cached queries with CFQUERY against the performance of sp_executesql and SQL statement strings? Functionally it's exactly the same as cfquery in that variables are replaced with values... however, the DB does the work and the CF server simply issues directives and receives results. The gotcha here is that it's (afaik) a SQL Server only solution... and that sux. Well, at least it does if it's any better. This is really interesting, because on my last major I completely converted to stored procs and views because I had some conditional datasets (i.e. is this user a staffmember or a client? find out, return the right data) that I was able to accomplish with an sproc whereas I'd have had to have CF issue at least 2, possibly 3-5 cfquery routines. Also, with sprocs and views I was able to completely abstract the application from the specifics of the DB schema... which I found to be very cool. I've always heard CF is an application server, compartmentalize functionality and let the DB do what it does best and let CF handle the smallest amount of data it needs to do the job. Has this changed, or is it just much less concrete than I've been thinking of it in the past? On Fri, 21 Jan 2005 15:42:15 +0100, Jochem van Dieten [EMAIL PROTECTED] wrote: Andy Ousterhout wrote: I thought that even with simple queries that Stored Procs where much faster? That is not my experience. YMMV Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: Lets say that I need to look up an invoice by 2 different mechanisms: Internal reference/Key 2 strings Will the execution plan for a stored proc for the Key be different enough from one for the strings to justify creating 2 stored procs? Right now I've got a single stored proc that does both. The execution plans will be different enough to justify creating 2 execution plans. This requires 2 queries, but depending on your database you might be able to put multiple queries in one stored procedure. Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191294 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? create proc spreadInvoice @InvoiceNumber int, /*Provide either Invoice Number or both */ @PeachTreeInvoice char(21), /* Peachtree Invoice Number and */ @PeachtreeKey char(21) /* Peachtree Key */ as /* Procedure to read Customer information by either CustomerKey or PeachtreeKey */ SELECT FROM tabInvoicedItems LEFT OUTER JOIN tabOrderedItems ON tabInvoicedItems.Line = tabOrderedItems.Line AND tabInvoicedItems.OrderNumber = tabOrderedItems.OrderNumber LEFT OUTER JOIN tabItems ON tabInvoicedItems.Item_FK = tabItems.Item_PK LEFT OUTER JOIN tabItemTypes ON tabItems.ItemType_FK = tabItemTypes.ItemType_PK LEFT OUTER JOIN tabCustomers INNER JOIN tabOrders ON tabCustomers.Customer_PK = tabOrders.Customer_FK ON tabOrderedItems.OrderNumber = tabOrders.OrderNumber RIGHT OUTER JOIN tabPeople tabPeople_1 RIGHT OUTER JOIN tabInvoices LEFT OUTER JOIN tabPeople ON tabInvoices.EnteredBy_FK = tabPeople.Person_PK ON tabPeople_1.Person_PK = tabInvoices.ChangedBy_FK ON tabInvoicedItems.InvoiceNumber = tabInvoices.InvoiceNumber WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) ORDER BYtabInvoices.InvoiceNumber, tabInvoicedItems.Line; GO -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Lets say that I need to look up an invoice by 2 different mechanisms: Internal reference/Key 2 strings Will the execution plan for a stored proc for the Key be different enough from one for the strings to justify creating 2 stored procs? Right now I've got a single stored proc that does both. The execution plans will be different enough to justify creating 2 execution plans. This requires 2 queries, but depending on your database you might be able to put multiple queries in one stored procedure. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191303 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191307 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191309 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: stored proc - Value cannot be converted to the requested type
From: Russ [mailto:[EMAIL PROTECTED] We have this stored procedure. It works fine if I comment out @imageHeader declaration, but once I put it in, and pass it in, I get an error from CF saying [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type. Are you using MX? If so, the dbvarname attribute is no longer used. Positional notation is used. Anyway, it sounds like your cfsqltype is not matching up with the type of data you are passing in the value attribute. Try outputting the values before running the storedproc to ensure the proper data is being passed in. Also, I'd use Trim() and Val() around the appropriate variables... Mike ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183815 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: stored proc - Value cannot be converted to the requested type
We are using MX. I kind of figured that positional notation is used, so I
ordered everything properly. Here is what the debugging looks like.
The SQL is { (param 1) = call uspNodeManagerUpdateScreen( (param 2) , (param
3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) ,
(param 10) )}
And here is what really gets passed in:
(param 1) = [type='OUT', sqltype='CF_SQL_INTEGER'] , (param 2) = [type='IN',
class='java.lang.Integer', value='7', sqltype='cf_sql_integer'] , (param 3)
= [type='IN', class='java.lang.Integer', value='164',
sqltype='cf_sql_integer'] , (param 4) = [type='IN',
class='java.lang.Integer', value='6426', sqltype='cf_sql_integer'] , (param
5) = [type='IN', class='java.lang.String', value='Home',
sqltype='cf_sql_varchar'] , (param 6) = [type='IN',
class='java.lang.String', value='globalDefault', sqltype='cf_sql_varchar'] ,
(param 7) = [type='IN', class='java.lang.Integer', value='211',
sqltype='cf_sql_integer'] , (param 8) = [type='IN',
class='java.lang.Integer', value='0', sqltype='cf_sql_integer'] , (param 9)
= [type='IN', class='java.lang.String', value='test',
sqltype='cf_sql_varchar'] , (param 10) = [type='OUT',
sqltype='cf_sql_integer']
Russ
-Original Message-
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 09, 2004 5:46 PM
To: CF-Talk
Subject: RE: stored proc - Value cannot be converted to the requested type
From: Russ [mailto:[EMAIL PROTECTED]
We have this stored procedure. It works fine if I comment out @imageHeader
declaration, but once I put it in, and pass it in, I get an error from CF
saying
[Macromedia][SQLServer JDBC Driver]Value can not be converted to requested
type.
Are you using MX? If so, the dbvarname attribute is no longer used.
Positional notation is used. Anyway, it sounds like your cfsqltype is not
matching up with the type of data you are passing in the value attribute.
Try outputting the values before running the storedproc to ensure the proper
data is being passed in. Also, I'd use Trim() and Val() around the
appropriate variables...
Mike
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183816
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Help Needed Please
I think the problem is with the context of the executed dynamic sql string. If you try something like this... DECLARE @max INT, @tableName VARCHAR(20), @sql VARCHAR(100) SET @tableName = 'tblQuestions' SET @sql = 'DECLARE @max INT SELECT @max = MAX(QuestionID) FROM ' + @tableName EXEC(@sql) ... you'll see there's no error about @max not being declared because it is declared within the context of the SELECT @max statement. I think! This doesn't give you the result you're after and I can't think of a way around this, there might be a way to get the result from one context to the current on but I'm not sure how. Let us know if you figure out a way. Ade -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 01 November 2004 22:38 To: CF-Talk Subject: Stored Proc Help Needed Please Ok I am buildin on an issue I solved a few weeks back about dynamically generating the table name varible and using it in the FROM clause of an SQL statement. I am now trying to use this method but also set a varible from the select statement. I keep getting an error telling me I have to declare the varible I am trying to set (@MaxLoopCounter) but it is in my declaration statement. Can eanyone assist me with this or give me another approach? Here is the stored proc. SQL2000. CREATE PROCEDURE Validate_Member_Counts @month1 varchar(10), @month2 varchar(10) AS DECLARE @pid1 int, @pid2 int, @expdate1 datetime, @joindate1 datetime, @expdate2 datetime, @joindate2 datetime, @status varchar(10), @counttype varchar(15), @accttype varchar(1), @loopid int, @MaxLoopCounter int, @SQLvarchar(8000), @TABLE_NAME1sysname, @TABLE_NAME2sysname --Create a holding table for results IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DAILY_MBR_COUNTS]') AND OBJECTPROPERTY(id, N'IsTable') = 1) CREATE TABLE [DAILY_MBR_COUNTS] ( personidint, accttypevarchar(1), status varchar(10), joindatedatetime, expdate datetime, counttype varchar(15) ) ELSE TRUNCATE TABLE DAILY_MBR_COUNTS; SELECT @TABLE_NAME1 = @month1+'persondem'; SELECT @TABLE_NAME2 = @month2+'persondem' SET @loopid = 1; SELECT @SQL =' SELECT @MaxLoopCounter = Max(Loopid) FROM ' + @TABLE_NAME1 EXEC(@SQL) WHILE @LoopId = @MaxLoopCounter BEGIN -- Do your magic here SELECT @SQL ='@pid1 = personid, @expdate1,@joindate1 FROM ' + @TABLE_NAME1 +'WHERE loopid = @loopid'; EXEC(@SQL) SET @LoopId = @LoopID + 1 END GO ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183404 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: stored proc error with MX
John,
Try CFSQLTYPE=CF_SQL_TIMESTAMP
Steve
-Original Message-
From: John mccosker [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 9:57 AM
To: CF-Talk
Subject: stored proc error with MX
hi,
is there some reason why I can't add a date object via a stored proc in MX
6.1.
example,
cfset offRoadStart = CreateDateTime(Year(now()),
month(now()),
day(now()),
hour(now()),
minute(now()),
00)
then,
!---parameter passed with others, this is the only one giving me the
problem---
CFPROCPARAM DBVARNAME=@OFFROADSTART VALUE=#offRoadStart#
CFSQLTYPE=CF_SQL_DATE TYPE=IN
I get this error,
{Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server
Driver]Optional feature not implemented
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented
I had a look at JavaCast thinking I was'nt passing in a proper date object,
even though I am, but javaCast has not data type support for date objects.
any ideas,
john.
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: stored proc error with MX
John,
Try CFSQLTYPE=CF_SQL_TIMESTAMP
Steve
-Original Message-
From: John mccosker [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 9:57 AM
To: CF-Talk
Subject: stored proc error with MX
hi,
is there some reason why I can't add a date object via a stored proc in MX
6.1.
example,
cfset offRoadStart = CreateDateTime(Year(now()),
month(now()),
day(now()),
hour(now()),
minute(now()),
00)
then,
!---parameter passed with others, this is the only one giving me the
problem---
CFPROCPARAM DBVARNAME=@OFFROADSTART VALUE=#offRoadStart#
CFSQLTYPE=CF_SQL_DATE TYPE=IN
I get this error,
{Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server
Driver]Optional feature not implemented
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented
I had a look at JavaCast thinking I was'nt passing in a proper date object,
even though I am, but javaCast has not data type support for date objects.
any ideas,
john.
_
Thanks Steve,
that worked for me.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Stored Proc Question Help Please
You need to write a cursor, it sounds like. Take a look in the books online to get a sense of how it works. M -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
The syntax I have attached actually uses two temp tables and then unions them at the end, but does use a cursor to loop with. Keep in mind this is SQL 6.5, and some of the functionality might be depricated or changed: create table #temp_origin_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) create table #temp_dest_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) Declare @company_id int --Declare @this_date datetime --Declare @that_date datetime --Select @this_date = '05/23/03' --Select @that_date = '05/24/03' Insert into #temp_origin_records (summary_id,service_type,type,company_id,company_name,sco re,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created,es.probation from evaluatio n_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where es.company_id 0 and es.date_created = @this_date AND date_created @that_date and es.status = 'R' --and service_type = 1 Declare this _cursor CURSOR FOR Select company_id from #temp_origin_records Open this_cursor Fetch Next from this_cursor into @company_id While (@@fetch_status = 0) Begin Insert into #temp_dest_records (summary_id,service_type,type,company_id,company _name,score,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created ,es.probation from evaluation_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where summary_id = (select max(summary_id) from evaluation_summary where date_created @this_date and company_i d = @company_id)-- and service_type = 1 Fetch Next from this_cursor into @company_id End close this_cursor deallocate this_cursor select * from #temp_origin_records union all select * from #temp_dest_records order by company_id, summar y_id desc drop table #temp_origin_records drop table #temp_dest_records -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
Although a trigger would be better. Much more efficient in this case. WG -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 15:48 To: CF-Talk Subject: RE: Stored Proc Question Help Please You need to write a cursor, it sounds like. Take a look in the books online to get a sense of how it works. M -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
What's the relationship between the two tables? Ade -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 15:29 To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE dbo.sp_getId
AS
BEGIN
declare @intError int
if object_id('tempdb..#myTempTable') is not null drop #myTemptable
/* Create Table */
create table #myTempTable
(
customerId int,
blah1 int,
blah2 varchar(200
)
declare @cid int
declare cidCursor cursor local static
for
select customerId from customerIdTable
open cidCursor
fetch cidCursor into @cid
while @@fetch_status=0
begin
insert into #myTempTable
select customerId, blah1, blah2
from myOtherTableorSql
where customerId = @cid
fetch next from aidCursor into @cid
end
close cidCursor
deallocate cidCursor
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ô¿ô Tony
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 7:29 AM
To: CF-Talk
Subject: Stored Proc Question Help Please
This is a very simple question.
I need to do this in SQL Server from a job not a CF page.
I have a table where I store customer IDs. I want to pull the individual IDs and loop
each ID out of the table and run it against another query and write that output of the
query to a tmpTable. How do I do this and if you could help it would be most
appreciated.
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
Thanks I will see what I can do with this. I appreciate you time and your help. -Original Message- From: John Stanley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 9:53 AM To: CF-Talk Subject: RE: Stored Proc Question Help Please The syntax I have attached actually uses two temp tables and then unions them at the end, but does use a cursor to loop with. Keep in mind this is SQL 6.5, and some of the functionality might be depricated or changed: create table #temp_origin_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) create table #temp_dest_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) Declare @company_id int --Declare @this_date datetime --Declare @that_date datetime --Select @this_date = '05/23/03' --Select @that_date = '05/24/03' Insert into #temp_origin_records (summary_id,service_type,type,company_id,company_name,sco re,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created,es.probation from evaluatio n_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where es.company_id 0 and es.date_created = @this_date AND date_created @that_date and es.status = 'R' --and service_type = 1 Declare this _cursor CURSOR FOR Select company_id from #temp_origin_records Open this_cursor Fetch Next from this_cursor into @company_id While (@@fetch_status = 0) Begin Insert into #temp_dest_records (summary_id,service_type,type,company_id,company _name,score,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created ,es.probation from evaluation_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where summary_id = (select max(summary_id) from evaluation_summary where date_created @this_date and company_i d = @company_id)-- and service_type = 1 Fetch Next from this_cursor into @company_id End close this_cursor deallocate this_cursor select * from #temp_origin_records union all select * from #temp_dest_records order by company_id, summar y_id desc drop table #temp_origin_records drop table #temp_dest_records -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored Proc Question Help Please
Eric Creese wrote: This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. Can't you just do: INSERT INTO temptable (field [, field]) SELECT field [, field] FROM othertable WHERE id IN (SELECT id FROM customertable) Why do you want to loop? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
I guess I could try this -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:07 AM To: CF-Talk Subject: Re: Stored Proc Question Help Please Eric Creese wrote: This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. Can't you just do: INSERT INTO temptable (field [, field]) SELECT field [, field] FROM othertable WHERE id IN (SELECT id FROM customertable) Why do you want to loop? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please-SOLVED
Thanks for everyones input! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc list input
I ran into this some time ago. The following function will take your list and a 1 character delimiter and return a table of the parsed results. Originally when I did this we were on SQL7 so I do have a SP version if you want it, but I prefer the function. CREATE FUNCTION f_reuParseList (@tcList VARCHAR(8000), @tcDelimiter CHAR(1)) RETURNS @ParsedList TABLE (ListID int IDENTITY, ListItem varchar(8000)) AS BEGIN DECLARE @lcListWork varchar(8000), @lnCommaPos int, @lcItem varchar(8000) SET @lcListWork = @tcList WHILE LEN(@lcListWork) 0 BEGIN SET @lnCommaPos = CHARINDEX(@tcDelimiter, @lcListWork) IF @lnCommaPos 0 BEGIN SET @lcItem = SUBSTRING(@lcListWork, 1, @lnCommaPos - 1) SET @lcListWork = SUBSTRING(@lcListWork, @lnCommaPos + 1, LEN(@lcListWork) - @lnCommaPos) END ELSE BEGIN SET @lcItem = @lcListWork SET @lcListWork = '' END INSERT INTO @ParsedList VALUES (@lcItem) END RETURN END GO __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 1:52 PM To: CF-Talk Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Stored Proc list input
I think cfprocparam has a list attribute. Ade -Original Message- From: Neil H. [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 18:21 To: CF-Talk Subject: Stored Proc list input I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Stored Proc list input
Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Stored Proc list input
Would you happen to have any more details? Nothing I have found mentions it. Neil - Original Message - From: Adrian Lynch [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:25 PM Subject: RE: Stored Proc list input I think cfprocparam has a list attribute. Ade -Original Message- From: Neil H. [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 18:21 To: CF-Talk Subject: Stored Proc list input I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Stored Proc list input
Impossible that there is no way to do this, at least you would think so?! Neil - Original Message - From: S. Isaac Dealey [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:52 PM Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Stored Proc list input
Server I have yet to find a way to pass a list to a stored procedure I found this a couple of days back; haven't tried it yet. Matthew P. Smith Web Developer, Object Oriented Naval Education Training Professional Development Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 12:52 PM To: CF-Talk Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored Proc list input
Neil, There are a few ways to deal with this. Here's one option. http://accesshelp.net/content/Report.asp?REPORT=4PARAM_ID=46 Chris -Original Message- From: Neil H. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 2:07 PM To: CF-Talk Subject: Re: Stored Proc list input Impossible that there is no way to do this, at least you would think so?! Neil - Original Message - From: S. Isaac Dealey [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:52 PM Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Stored Proc list input
Another option is to write a UDF as shown in this example. http://www.sqlteam.com/item.asp?ItemID=11499 Chris -Original Message- From: Lomvardias Christopher Sent: Tuesday, November 19, 2002 2:20 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: Stored Proc list input Neil, There are a few ways to deal with this. Here's one option. http://accesshelp.net/content/Report.asp?REPORT=4PARAM_ID=46 Chris -Original Message- From: Neil H. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 2:07 PM To: CF-Talk Subject: Re: Stored Proc list input Impossible that there is no way to do this, at least you would think so?! Neil - Original Message - From: S. Isaac Dealey [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:52 PM Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Stored Proc list input
Yea, it's something I've struggled with for a while -- I guess I've never really understood why afaik no one's ever introduced native support for lists in the rdbms as a parameter for a stored procedure. Impossible that there is no way to do this, at least you would think so?! Neil - Original Message - From: S. Isaac Dealey [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:52 PM Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Stored Proc list input
Server I have yet to find a way to pass a list to a stored procedure I found this a couple of days back; haven't tried it yet. Found what? I'd love to see a url or such if you've got one. :) Matthew P. Smith Web Developer, Object Oriented Naval Education Training Professional Development Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -Original Message- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 12:52 PM To: CF-Talk Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored Proc list input
The reason I've tried to avoid doing this in the past is because ms sql server loops are slow ( funny that, considering how quickly it deals with record sets )... not to mention it's a lot more code in the sp where there aren't any native list functions -- or you have to build your own sql server udf's for ms sql 2000... just my 2c... Neil, There are a few ways to deal with this. Here's one option. http://accesshelp.net/content/Report.asp?REPORT=4PARAM_ID=46 Chris -Original Message- From: Neil H. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 19, 2002 2:07 PM To: CF-Talk Subject: Re: Stored Proc list input Impossible that there is no way to do this, at least you would think so?! Neil - Original Message - From: S. Isaac Dealey [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, November 19, 2002 1:52 PM Subject: Re: Stored Proc list input Likely you're going to run into a larger issue with your database not being able to interpret the @PassVariable value as an integer... at least with MS SQL Server I have yet to find a way to pass a list to a stored procedure without passing it as a varchar and then using exec (@mysqlstatement) or exec sp_executesql @mysqlstatement which somewhat defeats the purpose of the storedprocedure being pre-processed. I need to pass a string such as 1,2 in to a stored procedure. The variable is then used like this: Where MYVariable IN (@PassVariable) Now the problem is that MYVariable is an INT and I am passing in the @PassVariable as a string. I get this error: Syntax error converting the varchar value '1,2' to a column of data type int. I need to figure some way around this. Any ideas? Thanks, Neil S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Stored proc help
Stick this code at the top of your SP: SET DATEFORMAT DMY -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: 07 November 2002 19:29 To: CF-Talk Subject: RE: Stored proc help Sent it once but here it is again. It has the recommended changes and runs without errors but returns nothing? cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_TIMESTAMP dbvarname=startdate value=#CreateODBCDateTime(10/01/2000)# null=No CFprocresult name=Qtest /CFSTOREDPROC the username has full privileges to the db. and I have gone into the tables and verified that there are records with a start date after the one entered. and if the storedproc is run from the SQL server it returns records? -Original Message- From: Bryan Stevenson [mailto:bryan;electricedgesystems.com] Sent: Thursday, November 07, 2002 11:29 AM To: CF-Talk Subject: Re: Stored proc help How about we get to see some code instead of playing 20 questions ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder Director www.cfug-vancouverisland.com - Original Message - From: Bruce, Rodney S HQISEC/Veridian IT Services [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 9:49 AM Subject: RE: Stored proc help No params are dates -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Thursday, November 07, 2002 10:26 AM To: CF-Talk Subject: RE: Stored proc help Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: Stored proc help
Thanks That seems to have done it -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Friday, November 08, 2002 3:19 AM To: CF-Talk Subject: RE: Stored proc help Stick this code at the top of your SP: SET DATEFORMAT DMY -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: 07 November 2002 19:29 To: CF-Talk Subject: RE: Stored proc help Sent it once but here it is again. It has the recommended changes and runs without errors but returns nothing? cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_TIMESTAMP dbvarname=startdate value=#CreateODBCDateTime(10/01/2000)# null=No CFprocresult name=Qtest /CFSTOREDPROC the username has full privileges to the db. and I have gone into the tables and verified that there are records with a start date after the one entered. and if the storedproc is run from the SQL server it returns records? -Original Message- From: Bryan Stevenson [mailto:bryan;electricedgesystems.com] Sent: Thursday, November 07, 2002 11:29 AM To: CF-Talk Subject: Re: Stored proc help How about we get to see some code instead of playing 20 questions ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder Director www.cfug-vancouverisland.com - Original Message - From: Bruce, Rodney S HQISEC/Veridian IT Services [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 9:49 AM Subject: RE: Stored proc help No params are dates -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Thursday, November 07, 2002 10:26 AM To: CF-Talk Subject: RE: Stored proc help Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Stored proc help
make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Stored proc help
I added the CreateODBCDateTime() but there is no option for SQLDATESTAMP for type, only CF_SQL_DATE for cfsqltype. type is IN, out IN/OUT. My code: cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_DATE dbvarname=startdate value=CreateODBCDateTime(1/1/2000) null=No CFprocresult name=Qtest /CFSTOREDPROC Thanks Rodney -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: Thursday, November 07, 2002 10:07 AM To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Stored proc help
Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney This usually means you're using cfsqltype=cf_sql_date ... change that to cfsqltype=cf_sql_timestamp and you should be good to go. :) S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Stored proc help
Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored proc help
now I get: ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification SQL = sp_psactualsbyweek -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Thursday, November 07, 2002 10:22 AM To: CF-Talk Subject: Re: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney This usually means you're using cfsqltype=cf_sql_date ... change that to cfsqltype=cf_sql_timestamp and you should be good to go. :) S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored proc help
No params are dates -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Thursday, November 07, 2002 10:26 AM To: CF-Talk Subject: RE: Stored proc help Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored proc help
Ive found better results using CF_SQL_TIMESTAMP in conjunction with createodbcdate and createodbcdatetime HTH -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 7, 2002 17:23 To: CF-Talk Subject: RE: Stored proc help I added the CreateODBCDateTime() but there is no option for SQLDATESTAMP for type, only CF_SQL_DATE for cfsqltype. type is IN, out IN/OUT. My code: cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_DATE dbvarname=startdate value=CreateODBCDateTime(1/1/2000) null=No CFprocresult name=Qtest /CFSTOREDPROC Thanks Rodney -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: Thursday, November 07, 2002 10:07 AM To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Stored proc help
How about we get to see some code instead of playing 20 questions ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder Director www.cfug-vancouverisland.com - Original Message - From: Bruce, Rodney S HQISEC/Veridian IT Services [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 9:49 AM Subject: RE: Stored proc help No params are dates -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Thursday, November 07, 2002 10:26 AM To: CF-Talk Subject: RE: Stored proc help Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Stored proc help
Thanks for all the replies It runs now without any errors but nothing is being return and I know that there are records that are after the date I am entering. -Original Message- From: Mike Townend [mailto:mike;cfnews.co.uk] Sent: Thursday, November 07, 2002 11:12 AM To: CF-Talk Subject: RE: Stored proc help Ive found better results using CF_SQL_TIMESTAMP in conjunction with createodbcdate and createodbcdatetime HTH -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 7, 2002 17:23 To: CF-Talk Subject: RE: Stored proc help I added the CreateODBCDateTime() but there is no option for SQLDATESTAMP for type, only CF_SQL_DATE for cfsqltype. type is IN, out IN/OUT. My code: cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_DATE dbvarname=startdate value=CreateODBCDateTime(1/1/2000) null=No CFprocresult name=Qtest /CFSTOREDPROC Thanks Rodney -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: Thursday, November 07, 2002 10:07 AM To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Stored proc help
Sent it once but here it is again. It has the recommended changes and runs without errors but returns nothing? cfstoredproc procedure=sp_mySP datasource=SQLDB username=x password=xxx cfprocparam type=In cfsqltype=CF_SQL_TIMESTAMP dbvarname=startdate value=#CreateODBCDateTime(10/01/2000)# null=No CFprocresult name=Qtest /CFSTOREDPROC the username has full privileges to the db. and I have gone into the tables and verified that there are records with a start date after the one entered. and if the storedproc is run from the SQL server it returns records? -Original Message- From: Bryan Stevenson [mailto:bryan;electricedgesystems.com] Sent: Thursday, November 07, 2002 11:29 AM To: CF-Talk Subject: Re: Stored proc help How about we get to see some code instead of playing 20 questions ;-) Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Vancouver Island ColdFusion Users Group Founder Director www.cfug-vancouverisland.com - Original Message - From: Bruce, Rodney S HQISEC/Veridian IT Services [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, November 07, 2002 9:49 AM Subject: RE: Stored proc help No params are dates -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Thursday, November 07, 2002 10:26 AM To: CF-Talk Subject: RE: Stored proc help Is one of your parameters type text or ntext? -Original Message- From: Weaver, Anthony [mailto:AWeaver;Limitedbrands.com] Sent: 07 November 2002 17:07 To: CF-Talk Subject: RE: Stored proc help make sure that your date CFPROCPARAMS use the SQLDATESTAMP type and use CreateODBCDateTime(your date). i get this often on date formats -Original Message- From: Bruce, Rodney S HQISEC/Veridian IT Services [mailto:Rodney.Bruce;HQISEC.ARMY.MIL] Sent: Thursday, November 07, 2002 12:04 PM To: CF-Talk Subject: Stored proc help Hello all I am trying to call a storedproc from an SQL 7 db. and I get this error. Does anyone know what it means. ODBC Error Code = S1C00 (Driver not capable) [Microsoft][ODBC SQL Server Driver]Optional feature not implemented I have tested the connection through CF admin and it tests ok. If I leave out a param. I get the error the param is missing. SO I think the connection to the db if ok. Thanks Rodney ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Stored Proc wizard
Man I love this list! Thanks Phillip Broussard Tracker Marine Group 417-873-5957 - Original Message - From: Adam Churvis [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Saturday, July 27, 2002 7:36 AM Subject: Re: Stored Proc wizard That would be ours, at www.ProductivityEnhancement.com. Enjoy! :) Respectfully, Adam Phillip Churvis Advanced Intensive ColdFusion MX Training http://www.ColdFusionTraining.com E-mail: [EMAIL PROTECTED] Phone: 770-446-8866 - Original Message - From: Phillip B [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, July 26, 2002 4:33 PM Subject: Stored Proc wizard I think I saw something recently about a stored procedure wizard. I look in the archives but I didn't see what I was looking for. Some one posted a link to the site it could be down loaded from. Anyone know what I am talking about and can point me in the right direction? Thank you. Thank you very much. Phillip Broussard Tracker Marine Group 417-873-5957 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Proc wizard
That would be ours, at www.ProductivityEnhancement.com. Enjoy! :) Respectfully, Adam Phillip Churvis Advanced Intensive ColdFusion MX Training http://www.ColdFusionTraining.com E-mail: [EMAIL PROTECTED] Phone: 770-446-8866 - Original Message - From: Phillip B [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, July 26, 2002 4:33 PM Subject: Stored Proc wizard I think I saw something recently about a stored procedure wizard. I look in the archives but I didn't see what I was looking for. Some one posted a link to the site it could be down loaded from. Anyone know what I am talking about and can point me in the right direction? Thank you. Thank you very much. Phillip Broussard Tracker Marine Group 417-873-5957 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Proc wizard
Only thing I know of is at http://www.aloha-webdesign.com Douglas Brown Email: [EMAIL PROTECTED] - Original Message - From: Phillip B [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, July 26, 2002 1:33 PM Subject: Stored Proc wizard I think I saw something recently about a stored procedure wizard. I look in the archives but I didn't see what I was looking for. Some one posted a link to the site it could be down loaded from. Anyone know what I am talking about and can point me in the right direction? Thank you. Thank you very much. Phillip Broussard Tracker Marine Group 417-873-5957 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
i just ran a test on my system and it ran fine. iam using SQL 7.0. i created
a view from a table i have in a database and then did a insert/select and it
worked fine. i did forget to do one thing in the code i gave you. i forgot
to explicitly define the columns to insert into. here is the re-written
code.
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
-- you have to put the column names
insert into #tempTable
(rr, memmonth, memyear, intcost1, extcost1)
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
See if it works now.
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:57 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
I ran it in query analyzer and views cannot pull data from temp tables was
the error given
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:46 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
shouldn't give you an error. can you post it?
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:47 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Also my other problem is that I have to pull data from that temp table via a
view so when I try a select from #temptable on a dynamic view it gives me an
error
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
RE: Stored Proc Problems
Did this help you?
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 8:27 AM
To: CF-Talk
Subject: RE: Stored Proc Problems
i just ran a test on my system and it ran fine. iam using SQL 7.0. i created
a view from a table i have in a database and then did a insert/select and it
worked fine. i did forget to do one thing in the code i gave you. i forgot
to explicitly define the columns to insert into. here is the re-written
code.
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
-- you have to put the column names
insert into #tempTable
(rr, memmonth, memyear, intcost1, extcost1)
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
See if it works now.
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:57 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
I ran it in query analyzer and views cannot pull data from temp tables was
the error given
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:46 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
shouldn't give you an error. can you post it?
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:47 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Also my other problem is that I have to pull data from that temp table via a
view so when I try a select from #temptable on a dynamic view it gives me an
error
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc
RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
Will this work if 14 people hit the site at once?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
Also my other problem is that I have to pull data from that temp table via a
view so when I try a select from #temptable on a dynamic view it gives me an
error
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
probably better than a cursor. crusors are evil nasty hoes beasts from hell
;). seriously, i have done test inserting 1,000,000 records from one tabl
einto another. using a cursor ate all my ram and brought the server to it's
knees. And it took FOREVER to get done. Insert/select, 5 secs, no problem,
no memory issue.
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:39 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Will this work if 14 people hit the site at once?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
shouldn't give you an error. can you post it?
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:47 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Also my other problem is that I have to pull data from that temp table via a
view so when I try a select from #temptable on a dynamic view it gives me an
error
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Problems
I ran it in query analyzer and views cannot pull data from temp tables was
the error given
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:46 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
shouldn't give you an error. can you post it?
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:47 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Also my other problem is that I have to pull data from that temp table via a
view so when I try a select from #temptable on a dynamic view it gives me an
error
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Proc Problems
Only if you lock the code or have an individual login for each user. If
they're using the same account, then they will also share the same temp
tables.
Marlon
- Original Message -
From: Joshua Tipton [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 15, 2002 2:38 PM
Subject: RE: Stored Proc Problems
Will this work if 14 people hit the site at once?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:27 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
not to impose, but since your looking to see if the table exists and then
dropping it, why don't you just use a temp table for the stored procdure.
Also, i don't think you will need to use a cursor in this stored procedure
if you use the insert/select combination.
Rewritten
=
CREATE PROCEDURE sp_missingcursor
as
CREATE TABLE #TempTable (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
insert into #tempTable
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09',
'10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
SELECT * FROM #TempTable
DROP TABLE #TempTable
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 3:22 PM
To: CF-Talk
Subject: RE: Stored Proc Problems
Actually it is not the insert statements. SQL is using @cfid literally
instead of as a variable. Does anyone know how to do this?
Joshua Tipton
-Original Message-
From: Joshua Tipton [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 15, 2002 2:58 PM
To: CF-Talk
Subject: Stored Proc Problems
I am receiving an error that column name or number of supplied values does
not match table definition. Something is up with my insert statements or
stored procs do not like dynamic table names.
Please help I know this is OT
CREATE PROCEDURE sp_missingcursor
@cfid nvarchar(50)
as
if exists (select * from sysobjects where id = object_id(N'[dbo].[@cfid]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@cfid]
CREATE TABLE [dbo].[@cfid] (
[rr] [char] (2) NULL ,
[memmonth] [varchar] (20) NULL ,
[memyear] [char] (1) NULL ,
[intcost1] [numeric](18, 0) NULL ,
[extcost1] [numeric](18, 0) NULL
) ON [PRIMARY]
set nocount on
declare @rr char(2), @memmonth nvarchar(20), @memyear char(1), @intcost1
numeric, @extcost1 numeric
declare dispo_cursor CURSOR for
SELECT region, memomonth, memoyear,
SUM(CASE WHEN SLIC NOT LIKE '88%' THEN cost ELSE 0 END) AS INTCOST,
SUM(CASE WHEN SLIC LIKE '88%' THEN cost ELSE 0 END) AS exTCOST
FROM dbo.CTMCORPAPP
WHERE region in ('00', '02', '03', '04', '05', '06', '07', '08', '09',
'10',
'22') and [desc] Like '%Missing%'
GROUP BY memoyear, memomonth, region
open dispo_cursor
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
while @@fetch_status = 0
begin
fetch next from dispo_cursor into @rr, @memmonth, @memyear, @intcost1,
@extcost1
insert into [dbo].[@cfid]
select @rr, @memmonth, @memyear, @intcost1, @extcost1
end
close dispo_cursor
deallocate dispo_cursor
__
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored proc query
John, Try: cfprocresult name=q_getCustomerVehicles cfprocresult name=q_getAndPopulate2ndPane resultset=2 HTH, Joseph DeVore VeloxWeb Technologies -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 3:09 AM To: CF-Talk Subject: Stored proc query Hi all, I was just wondering, the CFPROCRESULT tag returns the recordset from the procedure. Can you have more than one of these, if you have more than one query within your procedure. For instance, I call like so, CFSTOREDPROC PROCEDURE=p_Get_Customer_Vehicles DATASOURCE=#REQUEST.dsn# USERNAME=#REQUEST.user# PASSWORD=#REQUEST.key# CFPROCRESULT NAME=q_getCustomerVehicles CFPROCPARAM DBVARNAME=@customerID VALUE=#session.customerid# CFSQLTYPE=CF_SQL_INTEGER TYPE=IN CFPROCPARAM DBVARNAME=@CFID VALUE=#CLIENT.CFID# CFSQLTYPE=CF_SQL_CHAR TYPE=IN CFPROCPARAM DBVARNAME=@CFTOKEN VALUE=#CLIENT.CFTOKEN# CFSQLTYPE=CF_SQL_CHAR TYPE=IN /CFSTOREDPROC Then this is my procedure, simple like, CREATE PROCEDURE p_Get_Customer_Vehicles ( @customerID INT, @cfid INT, @cftoken INT ) AS SELECT customerID, vehicleid, callsign, registration FROMdbo.vehicles WHERE customerid = @customerID AND vehicleid NOT IN (SELECT vehicleid FROMdbo.vehicleSelection WHERE customerid = @customerID AND cfid = @cfid AND cftoken = @cftoken) SELECT * FROMdbo.vehicleSelection WHERE customerid=@customerID AND cfid=@cfid AND cftoken=@cftoken as you can see it has two selects, so in the stored proc call at the top, if I wanted to add another CFPROCRESULT how would I make sure CFPROCRESULT NAME=q_getCustomerVehicles was referencing my first select and the second CFPROCRESULT, CFPROCRESULT NAME=q_getAndPopulate2ndPane was referencing my second select. Thanks for any advice and help Jmc . . __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored proc query
Yip, that was it, Thank You Joseph. . . -Original Message- From: Joseph DeVore [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 11:20 AM To: CF-Talk Subject: RE: Stored proc query John, Try: cfprocresult name=q_getCustomerVehicles cfprocresult name=q_getAndPopulate2ndPane resultset=2 HTH, Joseph DeVore VeloxWeb Technologies -Original Message- From: John McCosker [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 3:09 AM To: CF-Talk Subject: Stored proc query Hi all, I was just wondering, the CFPROCRESULT tag returns the recordset from the procedure. Can you have more than one of these, if you have more than one query within your procedure. For instance, I call like so, CFSTOREDPROC PROCEDURE=p_Get_Customer_Vehicles DATASOURCE=#REQUEST.dsn# USERNAME=#REQUEST.user# PASSWORD=#REQUEST.key# CFPROCRESULT NAME=q_getCustomerVehicles CFPROCPARAM DBVARNAME=@customerID VALUE=#session.customerid# CFSQLTYPE=CF_SQL_INTEGER TYPE=IN CFPROCPARAM DBVARNAME=@CFID VALUE=#CLIENT.CFID# CFSQLTYPE=CF_SQL_CHAR TYPE=IN CFPROCPARAM DBVARNAME=@CFTOKEN VALUE=#CLIENT.CFTOKEN# CFSQLTYPE=CF_SQL_CHAR TYPE=IN /CFSTOREDPROC Then this is my procedure, simple like, CREATE PROCEDURE p_Get_Customer_Vehicles ( @customerID INT, @cfid INT, @cftoken INT ) AS SELECT customerID, vehicleid, callsign, registration FROMdbo.vehicles WHERE customerid = @customerID AND vehicleid NOT IN (SELECT vehicleid FROMdbo.vehicleSelection WHERE customerid = @customerID AND cfid = @cfid AND cftoken = @cftoken) SELECT * FROMdbo.vehicleSelection WHERE customerid=@customerID AND cfid=@cfid AND cftoken=@cftoken as you can see it has two selects, so in the stored proc call at the top, if I wanted to add another CFPROCRESULT how would I make sure CFPROCRESULT NAME=q_getCustomerVehicles was referencing my first select and the second CFPROCRESULT, CFPROCRESULT NAME=q_getAndPopulate2ndPane was referencing my second select. Thanks for any advice and help Jmc . . __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. You should be able to use @@ROWCOUNT for this. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Proc Question
If your running a SELECT statement you can include COUNT(*) as an extra column. If you are running another SQL statement which does not return a recordset (INSERT, DELETE, UPDATE) you can check the @@ROWCOUNT something like: IF @@ROWCOUNT = 0 YOUR SECOND QUERY Cheers, Bill In a message dated 8/13/01 12:51:21 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
First Query here IF @@rowcount = 0 BEGIN -- Second query here END -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: John Barleycorn [mailto:[EMAIL PROTECTED]] Sent: 13 August 2001 17:49 To: CF-Talk Subject: Stored Proc Question Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
@@Rowcount is overwritten as soon as you execute the next query. So, it might be wise to do something like the following: declare @MyRowCount int 'Your first query here' set @MyRowCount = @@RowCount if @MyRowCount = 0 begin 'Your second query here' end This is especially useful if you need to refer to the rowcount in more than one place. My $.25 worth... Shawn Grover -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, August 13, 2001 10:56 AM To: CF-Talk Subject: RE: Stored Proc Question First Query here IF @@rowcount = 0 BEGIN -- Second query here END -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: John Barleycorn [mailto:[EMAIL PROTECTED]] Sent: 13 August 2001 17:49 To: CF-Talk Subject: Stored Proc Question Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: stored proc trouble
A couple of things could be going wrong 1) nvarchar . is used for Unicode text. Do you need Unicode text ? 2) when declaring a nchar or nvarchar when n is not specified in a data definition or variable declaration statement, the default length is 1. 3) their is no need for quotes in your WHERE clause 4) You might want to upgrade your JOIN syntax ...some types of Joins in SQL 7 and newer can product "Unpredictable Results" when you put the join clause in the WHERE clause. And, personally I find that my queries are a bit clearer when I use the ANSI join syntax Your statement might read : CREATE PROCEDURE CONSULTINFO @user_n varchar (50), @pass_w varchar (50), @user_id int AS SELECT u.cons_id, u.user_n, u.pass_w, c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type FROMreg_users u ON c.cons_id = u.cons_id INNER JOIN consultants c WHERE u.pass_w = @pass_w AND u.user_n=@user_n Hope this helps. -eric Common sense is genius dressed in its working clothes. -- Ralph Waldo Emerson Eric Barr Zeff Design (p) 212.714.6390 (f) 212.580.7181 -Original Message- From: C Frederic Valone [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 21, 2001 11:58 AM To: CF-Talk Subject: stored proc trouble I have created a stored proc in SQL 7 and for some reason cannot get it to "work" using the input params I need. here is the proc: CREATE PROCEDURE CONSULTINFO @user_n nvarchar, @pass_w nvarchar, @user_id int AS select u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type from reg_users u, consultants c where u.pass_w = "@pass_w" and u.user_n="@user_n" in the above I added the user_id field after for checking purposes. If I run the proc using the user_id as the in param the proc returns a row correctly. (I have also tried using single quotes in the where clause and no quotes, neither helped) I have checked the values of the user_n and pass_w and they are correct ( a cfquery using the same values returns the right info) here is the cf code: cfquery name="TEST" datasource="jobs_db" dbtype="ODBC" SELECT u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type FROM dbo.CONSULTANTS C, dbo.REG_USERS U WHERE (U.USER_N='#user_n#' and U.PASS_W='#pass_w#') and (C.CONS_ID=U.CONS_ID) /cfquery !--- CFSTOREDPROC tag --- CFSTOREDPROC PROCEDURE="consultinfo" DATASOURCE="jobs_db" DEBUG !--- CFPROCRESULT tags --- CFPROCRESULT NAME = cons !--- CFPROCPARAM tags --- CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR" VALUE="#user_n#"DBVARNAME=@user_n CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR" VALUE="#pass_w#"DBVARNAME=@pass_w !--- this line was added for testing using the user_id and it then returned the information I needed --- !---CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_INTEGER" VALUE="1"DBVARNAME=@user_id!--- !--- Close the CFSTOREDPROC tag --- /CFSTOREDPROC h3The Results Information test/h3 CFOUTPUT#cons.recordcount# br#cons.USER_N# /CFOUTPUT I have also tried hard-coding the values of @used_n and @pass_w in both the cf and the stored proc with no better results. Any ideas on what I am doing wrong? thanks, Frederic -- / \__ Frederic Valone (@\___ Webmaster / O American Kennel Club /(_/ /_/ U ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: stored proc trouble
Thanks Eric I'll try that when I get home. Eric Barr wrote: A couple of things could be going wrong 1) nvarchar . is used for Unicode text. Do you need Unicode text ? 2) when declaring a nchar or nvarchar when n is not specified in a data definition or variable declaration statement, the default length is 1. 3) their is no need for quotes in your WHERE clause 4) You might want to upgrade your JOIN syntax ...some types of Joins in SQL 7 and newer can product "Unpredictable Results" when you put the join clause in the WHERE clause. And, personally I find that my queries are a bit clearer when I use the ANSI join syntax Your statement might read : CREATE PROCEDURE CONSULTINFO @user_n varchar (50), @pass_w varchar (50), @user_id int AS SELECT u.cons_id, u.user_n, u.pass_w, c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type FROMreg_users u ON c.cons_id = u.cons_id INNER JOIN consultants c WHERE u.pass_w = @pass_w AND u.user_n=@user_n Hope this helps. -eric Common sense is genius dressed in its working clothes. -- Ralph Waldo Emerson Eric Barr Zeff Design (p) 212.714.6390 (f) 212.580.7181 -Original Message- From: C Frederic Valone [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 21, 2001 11:58 AM To: CF-Talk Subject: stored proc trouble I have created a stored proc in SQL 7 and for some reason cannot get it to "work" using the input params I need. here is the proc: CREATE PROCEDURE CONSULTINFO @user_n nvarchar, @pass_w nvarchar, @user_id int AS select u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type from reg_users u, consultants c where u.pass_w = "@pass_w" and u.user_n="@user_n" in the above I added the user_id field after for checking purposes. If I run the proc using the user_id as the in param the proc returns a row correctly. (I have also tried using single quotes in the where clause and no quotes, neither helped) I have checked the values of the user_n and pass_w and they are correct ( a cfquery using the same values returns the right info) here is the cf code: cfquery name="TEST" datasource="jobs_db" dbtype="ODBC" SELECT u.cons_id, u.user_n, u.pass_w,c.first_n, c.last_n, c.phone, c.email, c.city, c.state_id, c.level_id, c.resume, c.enterdate, c.availability, c.type FROM dbo.CONSULTANTS C, dbo.REG_USERS U WHERE (U.USER_N='#user_n#' and U.PASS_W='#pass_w#') and (C.CONS_ID=U.CONS_ID) /cfquery !--- CFSTOREDPROC tag --- CFSTOREDPROC PROCEDURE="consultinfo" DATASOURCE="jobs_db" DEBUG !--- CFPROCRESULT tags --- CFPROCRESULT NAME = cons !--- CFPROCPARAM tags --- CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR" VALUE="#user_n#"DBVARNAME=@user_n CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_VARCHAR" VALUE="#pass_w#"DBVARNAME=@pass_w !--- this line was added for testing using the user_id and it then returned the information I needed --- !---CFPROCPARAM TYPE="IN" cfsqltype="CF_SQL_INTEGER" VALUE="1"DBVARNAME=@user_id!--- !--- Close the CFSTOREDPROC tag --- /CFSTOREDPROC h3The Results Information test/h3 CFOUTPUT#cons.recordcount# br#cons.USER_N# /CFOUTPUT I have also tried hard-coding the values of @used_n and @pass_w in both the cf and the stored proc with no better results. Any ideas on what I am doing wrong? thanks, Frederic -- / \__ Frederic Valone (@\___ Webmaster / O American Kennel Club /(_/ /_/ U ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
