CFSTOREDPROC with OLEDB datasource

[ketankshah]

I have a stored proc. in MSSQL7 which is working fine when I use ODBC DSN. But since I use OLEDB DSN in this site, while using it the CFSTOREDPROC tag gives following error :

-------------------------------

Error Diagnostic Information
OLEDB Error Code = 8180

Statement(s) could not be prepared.

The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (1:1) to (8:3).

Date/Time: 03/16/01 17:10:35
Browser: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
Remote Address: 192.168.0.3
-------------------------------

While using ODBC DSN I used the following tag.
<cfstoredproc
procedure=&quot;AddDeliverable&quot;
datasource=&quot;backdata1&quot;
returncode=&quot;Yes&quot;>

While using OLEDB DSN I used the following tag.

<cfstoredproc
procedure=&quot;AddDeliverable&quot;
datasource=&quot;backdata2&quot;
provider=&quot;SQLOLEDB&quot;
providerdsn=&quot;backdata2&quot;
returncode=&quot;Yes&quot;>

Is this syntax is correct? My OLEDB datasource name is backdata2 and ODBC DSN is backdata1.

How to execute SP by using OLEDB DSN ?

Please help.

 

[ketankshah]

Is anyone there to help my out regarding my above problem ?

It is very urgent. Pleeeeease.

Ketan

 

[CFProgrammer]

I am not sure if the syntax is correct. Just copy the existing SP to a new one. I would make sure that CF has verified my OLE connection. If it has, then you are good.

>:O> CFPROGRAMMER

 

[nicfoster]

To begin with, one of the reasons for using SQLOLEDB instead of ODBC is that you do not need a DSN for the datasource. This is part of the SQLOLEDB connection string established in CFAdministator.

Did you create the SQLOLEDB entry in the CF Administrator for backdata2? All you need in your cfstoredproc is the procedure name and the datasource name. Even the return code is optional. I think if you recode your cfstoredproc, it should work.

Nic

 

[ketankshah]

Yes, I have already created backdata2 as an SQLOLEDB entry in the CF Administrator. My CFQUERYs are working fine with this 'backdata2' but not the SP.

I have tried with the following syntax.
cfstoredproc
procedure=&quot;AddDeliverable&quot;
datasource=&quot;backdata2&quot;
returncode=&quot;Yes&quot;>

But still it is giving me the same error.