CFSTOREDPROC does not return data

[iccsi]

I have CF10 Developer version on my machine and CF 9 is remote server.

I have CFSTOREDPROC works on CF10 local developer version, but CFSTOREDPROC does not return data on CF9 Remote server.

Both CF9 and CF10 connect to the same database server and running exactly same code nad stored procedures, because it is same source code I just use Dreamweave to put 2 different web server.

I would like to know any thing I can check between local and remote server using CF9 and CF10 makes CFSTOREDPROC does not return data.

Your help and information is great appreciated,

Regards,

Iccsi,

[Carl Von Stetten]

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)?

-Carl V.

[iccsi]

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:

MyServer.cfm?MyNumber=6&MyDate=07/04/2013&MyTime=15:04:00

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,

Regards,

Iccsi,

[iccsi]

<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">

</cfstoredproc>

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.

Regards,

Iccsi,

[Carl Von Stetten]

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?

-Carl V.

[iccsi]

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.

Regards,

Iccsi,