Copy link to clipboard
Copied
I am trying to execute a stored procedure through ColdFusion. The stored procedure being executed attempts to insert local data into a remote server table through a linked server.
Attempting the stored procedure call from ColdFusion results in an error:
[Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 253.
However, if the stored procedure is exected within SQL Server Management Studio, the call works correctly, no error.
I am using the same credentials within Management Studio as within ColdFusion (via Datasources).
Copy link to clipboard
Copied
Are you able to run any Stored Procs from that database using ColdFusion?
Copy link to clipboard
Copied
Yes, I can execute other stored procedures that simply return result sets on the local database with no issue.
Copy link to clipboard
Copied
I have encountered the same issue.
1) Created a linked server in MSSQL 2008 in datasource xyz
EXEC sp_addlinkedserver N'192.168.1.103', N'SQL Server';
2) Created a synonym to the stored proc on the linked server
CREATE SYNONYM [dbo].[RPC_sspQuery] FOR [192.168.1.103].table_name.dbo.ssp_Query
3) When calling RPC_sspQuery, I get The DBMS returned an unspecified error. The command code was 253.
<cfstoredproc PROCEDURE="RPC_sspQuery" DATASOURCE="xyz">
</cfstoredproc>
What might I be doing wrongly?
Directly executing RPC_sspQuery from xyz works in sql.
Putting RPC_sqlQuery in a local stored proc and calling it (the local stored proc) via <cfstoredproc> works.
Changing the <cfstoredproc> to XYZ datasource and calling ssp_Query works.
All works except calling the remote stored procedure through cfstoredproc.
Please help.
Copy link to clipboard
Copied
Out of curiosity, is there a reason you can't call the stored procedure directly from ColdFusion to the "linked server" rather than passing through your server "xyz"?
I'm wondering too if there might be an authentication issue going on. Since you appear to have created the linked server connection without specifying a login (this can be done using sp_addlinkedsrvlogin), it may not be passing the credentials through that you want.
-Carl V.
Copy link to clipboard
Copied
Hi Carl,
I can, but would prefer not to due to maintenance/deployment considerations.
The current application has multiple database servers (staging/prod/train/demo) and the remote stored proc call is to interface with another CF application that also has the same multiple database server setup.
I've checked the linked server setup in SQL (with and without username/password), the connection currently works. Username & passwords are same in both db servers. Also, passing in username="username" and password="password" in <cfstoredproc> stlll gives the same error. Using CF9.0.1.
I've also tried with CF11 and get the same results. Below are some additional tests and results. The failed proc call error as attached.
1) Calling the synonym stored proc through CFQUERY works
2) Calling the synonym stored proc through CFSTOREDPROC fails
3) Calling another sysnonym stored proc through CFSTOREDPROC succeeds only if the synonym is located on the same server as the datasource.
spy(Thread-36)(2015/06/11 14:52:51.903)>> Connection[8].getMetaData() spy(Thread-36)(2015/06/11 14:52:51.904)>> OK (DatabaseMetaData[23]) spy(Thread-36)(2015/06/11 14:52:51.904)>> DatabaseMetaData[23].getURL() spy(Thread-36)(2015/06/11 14:52:51.904)>> OK (jdbc:macromedia_direct:sqlserver://localhost:1433;CATALOGOPTIONS=2;CONNECTIONRETRYDELAY=1;BULKLOADBATCHSIZE=1000;DATABASENAME=merimen_ic21;MAXPOOLEDSTATEMENTS=100;PROGRAMID=;ENABLECANCELTIMEOUT=false;TRUSTSTOREPASSWORD=;VALIDATESERVERCERTIFICATE=true;CODEPAGEOVERRIDE=;DATETIMEINPUTPARAMETERTYPE=auto;CONNECTIONRETRYCOUNT=5;ENABLEBULKLOAD=false;BATCHPERFORMANCEWORKAROUND=false;INITIALIZATIONSTRING=;HOSTPROCESS=0;FAILOVERPRECONNECT=false;USESERVERSIDEUPDATABLECURSORS=false;ALWAYSREPORTTRIGGERRESULTS=false;RESULTSETMETADATAOPTIONS=0;CLIENTUSER=;QUERYTIMEOUT=0;FAILOVERGRANULARITY=nonAtomic;HOSTNAMEINCERTIFICATE=;STRINGINPUTPARAMETERTYPE=nvarchar;SNAPSHOTSERIALIZABLE=false;APPLICATIONNAME=;JAVADOUBLETOSTRING=false;SENDSTRINGPARAMETERSASUNICODE=false;LOADLIBRARYPATH=;IMPORTSTATEMENTPOOL=;PROGRAMNAME=;DATETIMEOUTPUTPARAMETERTYPE=auto;NETADDRESS=000000000000;APPLICATIONINTENT=readwrite;ALTERNATESERVERS=;DESCRIBEINPUTPARAMETERS=noDescribe;EMBEDDED=true;STRINGOUTPUTPARAMETERTYPE=nvarchar;WSID=;SPYATTRIBUTES=(log=(file)c:\log.txt; linelimit=80;logTName=yes;timestamp=yes);BULKLOADOPTIONS=2;TRUNCATEFRACTIONALSECONDS=true;ENCRYPTIONMETHOD=NoEncryption;ACCOUNTINGINFO=;CONVERTNULL=1;TRUSTSTORE=;JDBCBEHAVIOR=1;FAILOVERMODE=connect;AUTHENTICATIONMETHOD=auto;LOGINTIMEOUT=0;FETCHTWFSASTIME=false;LONGDATACACHESIZE=2048;LOADBALANCING=false;TRANSACTIONMODE=implicit;DESCRIBEOUTPUTPARAMETERS=noDescribe;SUPPRESSCONNECTIONWARNINGS=false;WORKAROUNDS=0;INSENSITIVERESULTSETBUFFERSIZE=2048;PACKETSIZE=-1;CLIENTHOSTNAME=;DEFAULTSCALE=4;XMLDESCRIBETYPE=;FETCHTSWTZASTIMESTAMP=false;SELECTMETHOD=direct) spy(Thread-36)(2015/06/11 14:52:51.904)>> DatabaseMetaData[23].getDriverName() spy(Thread-36)(2015/06/11 14:52:51.904)>> OK (SQLServer) spy(Thread-36)(2015/06/11 14:52:51.904)>> DatabaseMetaData[23].getDriverVersion() spy(Thread-36)(2015/06/11 14:52:51.906)>> OK (5.1.1.000048 (F000075.U000035)) spy(Thread-36)(2015/06/11 14:52:51.906)>> DatabaseMetaData[23].getDatabaseProductName() spy(Thread-36)(2015/06/11 14:52:51.907)>> OK (Microsoft SQL Server) spy(Thread-36)(2015/06/11 14:52:51.908)>> DatabaseMetaData[23].getDatabaseProductVersion() spy(Thread-36)(2015/06/11 14:52:51.908)>> OK (Microsoft SQL Server 2008 R2 - 10.50.4033.0) spy(Thread-36)(2015/06/11 14:52:51.908)>> Connection Options : spy(Thread-36)(2015/06/11 14:52:51.908)>> CATALOGOPTIONS=2 spy(Thread-36)(2015/06/11 14:52:51.908)>> CONNECTIONRETRYDELAY=1 spy(Thread-36)(2015/06/11 14:52:51.908)>> BULKLOADBATCHSIZE=1000 spy(Thread-36)(2015/06/11 14:52:51.908)>> DATABASENAME=merimen_ic21 spy(Thread-36)(2015/06/11 14:52:51.908)>> MAXPOOLEDSTATEMENTS=100 spy(Thread-36)(2015/06/11 14:52:51.908)>> PROGRAMID= spy(Thread-36)(2015/06/11 14:52:51.908)>> ENABLECANCELTIMEOUT=false spy(Thread-36)(2015/06/11 14:52:51.908)>> TRUSTSTOREPASSWORD= spy(Thread-36)(2015/06/11 14:52:51.908)>> VALIDATESERVERCERTIFICATE=true spy(Thread-36)(2015/06/11 14:52:51.908)>> CODEPAGEOVERRIDE= spy(Thread-36)(2015/06/11 14:52:51.908)>> DATETIMEINPUTPARAMETERTYPE=auto spy(Thread-36)(2015/06/11 14:52:51.908)>> CONNECTIONRETRYCOUNT=5 spy(Thread-36)(2015/06/11 14:52:51.908)>> ENABLEBULKLOAD=false spy(Thread-36)(2015/06/11 14:52:51.908)>> BATCHPERFORMANCEWORKAROUND=false spy(Thread-36)(2015/06/11 14:52:51.908)>> INITIALIZATIONSTRING= spy(Thread-36)(2015/06/11 14:52:51.908)>> HOSTPROCESS=0 spy(Thread-36)(2015/06/11 14:52:51.908)>> FAILOVERPRECONNECT=false spy(Thread-36)(2015/06/11 14:52:51.908)>> USESERVERSIDEUPDATABLECURSORS=false spy(Thread-36)(2015/06/11 14:52:51.908)>> ALWAYSREPORTTRIGGERRESULTS=false spy(Thread-36)(2015/06/11 14:52:51.908)>> RESULTSETMETADATAOPTIONS=0 spy(Thread-36)(2015/06/11 14:52:51.908)>> CLIENTUSER= spy(Thread-36)(2015/06/11 14:52:51.908)>> QUERYTIMEOUT=0 spy(Thread-36)(2015/06/11 14:52:51.908)>> FAILOVERGRANULARITY=nonAtomic spy(Thread-36)(2015/06/11 14:52:51.908)>> HOSTNAMEINCERTIFICATE= spy(Thread-36)(2015/06/11 14:52:51.908)>> STRINGINPUTPARAMETERTYPE=nvarchar spy(Thread-36)(2015/06/11 14:52:51.908)>> SNAPSHOTSERIALIZABLE=false spy(Thread-36)(2015/06/11 14:52:51.908)>> APPLICATIONNAME= spy(Thread-36)(2015/06/11 14:52:51.908)>> JAVADOUBLETOSTRING=false spy(Thread-36)(2015/06/11 14:52:51.908)>> SENDSTRINGPARAMETERSASUNICODE=false spy(Thread-36)(2015/06/11 14:52:51.908)>> LOADLIBRARYPATH= spy(Thread-36)(2015/06/11 14:52:51.908)>> IMPORTSTATEMENTPOOL= spy(Thread-36)(2015/06/11 14:52:51.908)>> PROGRAMNAME= spy(Thread-36)(2015/06/11 14:52:51.908)>> DATETIMEOUTPUTPARAMETERTYPE=auto spy(Thread-36)(2015/06/11 14:52:51.909)>> NETADDRESS=000000000000 spy(Thread-36)(2015/06/11 14:52:51.909)>> APPLICATIONINTENT=readwrite spy(Thread-36)(2015/06/11 14:52:51.909)>> ALTERNATESERVERS= spy(Thread-36)(2015/06/11 14:52:51.909)>> DESCRIBEINPUTPARAMETERS=noDescribe spy(Thread-36)(2015/06/11 14:52:51.909)>> EMBEDDED=true spy(Thread-36)(2015/06/11 14:52:51.909)>> STRINGOUTPUTPARAMETERTYPE=nvarchar spy(Thread-36)(2015/06/11 14:52:51.909)>> WSID= spy(Thread-36)(2015/06/11 14:52:51.909)>> SPYATTRIBUTES=(log=(file)c:\log.txt spy(Thread-36)(2015/06/11 14:52:51.909)>> linelimit=80 spy(Thread-36)(2015/06/11 14:52:51.909)>> logTName=yes spy(Thread-36)(2015/06/11 14:52:51.909)>> timestamp=yes) spy(Thread-36)(2015/06/11 14:52:51.909)>> BULKLOADOPTIONS=2 spy(Thread-36)(2015/06/11 14:52:51.909)>> TRUNCATEFRACTIONALSECONDS=true spy(Thread-36)(2015/06/11 14:52:51.909)>> ENCRYPTIONMETHOD=NoEncryption spy(Thread-36)(2015/06/11 14:52:51.909)>> ACCOUNTINGINFO= spy(Thread-36)(2015/06/11 14:52:51.909)>> CONVERTNULL=1 spy(Thread-36)(2015/06/11 14:52:51.909)>> TRUSTSTORE= spy(Thread-36)(2015/06/11 14:52:51.909)>> JDBCBEHAVIOR=1 spy(Thread-36)(2015/06/11 14:52:51.909)>> FAILOVERMODE=connect spy(Thread-36)(2015/06/11 14:52:51.909)>> AUTHENTICATIONMETHOD=auto spy(Thread-36)(2015/06/11 14:52:51.909)>> LOGINTIMEOUT=0 spy(Thread-36)(2015/06/11 14:52:51.909)>> FETCHTWFSASTIME=false spy(Thread-36)(2015/06/11 14:52:51.909)>> LONGDATACACHESIZE=2048 spy(Thread-36)(2015/06/11 14:52:51.909)>> LOADBALANCING=false spy(Thread-36)(2015/06/11 14:52:51.909)>> TRANSACTIONMODE=implicit spy(Thread-36)(2015/06/11 14:52:51.909)>> DESCRIBEOUTPUTPARAMETERS=noDescribe spy(Thread-36)(2015/06/11 14:52:51.909)>> SUPPRESSCONNECTIONWARNINGS=false spy(Thread-36)(2015/06/11 14:52:51.909)>> WORKAROUNDS=0 spy(Thread-36)(2015/06/11 14:52:51.909)>> INSENSITIVERESULTSETBUFFERSIZE=2048 spy(Thread-36)(2015/06/11 14:52:51.909)>> PACKETSIZE=-1 spy(Thread-36)(2015/06/11 14:52:51.909)>> CLIENTHOSTNAME= spy(Thread-36)(2015/06/11 14:52:51.909)>> DEFAULTSCALE=4 spy(Thread-36)(2015/06/11 14:52:51.909)>> XMLDESCRIBETYPE= spy(Thread-36)(2015/06/11 14:52:51.909)>> FETCHTSWTZASTIMESTAMP=false spy(Thread-36)(2015/06/11 14:52:51.909)>> SELECTMETHOD=direct spy(Thread-36)(2015/06/11 14:52:51.909)>> Driver Name = SQLServer spy(Thread-36)(2015/06/11 14:52:51.909)>> Driver Version = 5.1.1.000048 (F000075.U000035) spy(Thread-36)(2015/06/11 14:52:51.909)>> Database Name = Microsoft SQL Server spy(Thread-36)(2015/06/11 14:52:51.909)>> Database Version = Microsoft SQL Server 2008 R2 - 10.50.4033.0 spy(Thread-36)(2015/06/11 14:52:51.910)>> Connection[8].setTransactionIsolation(int level) spy(Thread-36)(2015/06/11 14:52:51.910)>> level = 2 spy(Thread-36)(2015/06/11 14:52:51.910)>> OK spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> Connection[8].getMetaData() spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> OK (DatabaseMetaData[24]) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> DatabaseMetaData[24].getJDBCMajorVersion() spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> OK (4) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> Connection[8].isReadOnly() spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> OK (false) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> Connection[8].getAutoCommit() spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> OK (true) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> Connection[8].prepareCall(String sql) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.911)>> sql = {call dsn_XYZ(?)} spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> OK (CallableStatement[4]) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> CallableStatement[4].setMaxRows(int max) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> max = 0 spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> OK spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> CallableStatement[4].setMaxFieldSize(int max) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> max = 64000 spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> OK spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> CallableStatement[4].setObject(int parameterIndex, Object x, int targetSqlType) spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> parameterIndex = 1 spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> x = 1 spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> targetSqlType = 4 spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> OK spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.912)>> CallableStatement[4].execute() spy(http-bio-8500-exec-2)(2015/06/11 14:52:51.914)>> java.sql.SQLNonTransientConnectionException: [macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224. ErrorCode=0 SQLState=08006 java.sql.SQLNonTransientConnectionException: [macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224. at macromedia.jdbc.sqlserverbase.ddcw.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddcw.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddcv.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddcv.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.c(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddq.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.dda3.m(Unknown Source) at macromedia.jdbc.sqlserverbase.dde7.e(Unknown Source) at macromedia.jdbc.sqlserverbase.dde7.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddd2.a(Unknown Source) at macromedia.jdbc.sqlserverbase.dde7.v(Unknown Source) at macromedia.jdbc.sqlserverbase.dde7.r(Unknown Source) at macromedia.jdbc.sqlserverbase.ddd2.execute(Unknown Source) at macromedia.jdbc.sqlserverbase.ddd6.execute(Unknown Source) at macromedia.jdbcspysqlserver.SpyPreparedStatement.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101) at coldfusion.sql.Executive.executeCall(Executive.java:1075) at coldfusion.sql.Executive.executeCall(Executive.java:948) at coldfusion.sql.Executive.executeCall(Executive.java:898) at coldfusion.sql.SqlImpl.executeCall(SqlImpl.java:528) at coldfusion.tagext.sql.StoredProcTag.executeQuery(StoredProcTag.java:340) at coldfusion.tagext.sql.StoredProcTag.doEndTag(StoredProcTag.java:288) at cfremoteRPC2ecfm212503075.runPage(F:\Development\remoteRPC.cfm:24) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:246) at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:734) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:570) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:45) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:487) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:42) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:141) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:58) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:219) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at coldfusion.inspect.weinre.MobileDeviceDomInspectionFilter.doFilter(MobileDeviceDomInspectionFilter.java:121) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:422) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:313) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)
Regards,
Lisa
Copy link to clipboard
Copied
I've checked the linked server setup in SQL (with and without username/password), the connection currently works. Username & passwords are same in both db servers. Also, passing in username="username" and password="password" in <cfstoredproc> stlll gives the same error. Using CF9.0.1.
You will definitely have to specify username/password in the Linked Server if you want the connection to work for ColdFusion, as ColdFusion's credentials most likely won't carry across the link.
It's possible you've found a bug in the implementation of <cfstoredproc> since calling the procedure through <cfquery> works. So my suggestions are:
-Carl V.