I think you need to provide more information if anyone is to provide assistance:
Are you getting any error messages, or just nothing gets returned?
Are the datasources configured exactly the same on both the remote server and on your local machine (including the user account used for the database connection)?
Can you provide the code you are using (specifically the CFSTOREDPROC part)?
I don't use CFSTOREDPROC, so I'm not sure if anything changed between CF9 and CF10 - if there are new attributes or behavior added in CF10, are you utilizing them (they probably would not work in CF9)?
I use CFDUMP to see there is no data return from CFDUMP.
I use MS SQL server 2008 and the date tpye are integer and datetime.
I use cfprocparam cf_sql_integer, cf_sql_date and cf_sql_time.
I tried to use contant to my stored procedure like
MyID = 6, MyDate = '2013-07-04' and MyTime = '15:04:00' in the stored procedure which means no matter which parameter pass from cfm file, the stored procedure always use the contstant then cfdump returns data and my form works.
It seems that CF 9 CFSTORDPROC does not pass same date time format to SQL server 2008 like CF 10.
I pass paramter like following:
If it is true are there any work around to pass correct date time data type format to MS SQL server stored procedures from cfprocparam.
Your help and information is great appreciated,
<cfstoredproc procedure="MySP" datasource="MyDataSource">
<cfprocparam value = "#form.MyNumber#" CFSQLTYPE = "cf_sql_integer">
<cfprocparam value = "#form.MyDate#" CFSQLTYPE = "cf_sql_date">
<cfprocparam value = "#form.MyTime#" CFSQLTYPE = "cf_sql_time">
<cfprocresult name="SP1" resultset="1">
I tried to use CFOUT, and CFDUMP, but there is no data return.
I use CFOUT for form.MyNumber, form.MyDate and form.MyTime. All paramters have value.
Here is cfstoredproc code, there is no error message, just does not return data.
I'm wondering if the date and time strings are getting messed up by being passed as URL parameters. Maybe use the URLEncode function in your link to preserve the slashes and colons? To confirm that those are the issues, what if you dump the URL scope on your page. Do the variables in URL.MyDate and URL.MyTime match what you set them to in the originating page?
1 person found this helpful
Thanks for the message and help,
I found the problem, cf_sql_time translate to 1970-01-01 15:14:00 which my data are 1900-01-01 15:14:00.
cf_sql_time does not pass pure time to SQL server. I need modify the data to 1970-01-01 15:14:00 then it resturns data.