Anonymous

Question

Executing Remote Stored Procedures

Forum|Forum|13 years ago
April 23, 2012
2 replies
3440 views

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

This topic has been closed for replies.

yui8979

Inspiring

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>

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.

Carl Von Stetten

Legend

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.

yui8979

Inspiring

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

Dan_Bracuk

Inspiring

Are you able to run any Stored Procs from that database using ColdFusion?

Anonymous

Yes, I can execute other stored procedures that simply return result sets on the local database with no issue.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded