6 Replies Latest reply: Jun 12, 2015 10:21 AM by Carl Von Stetten RSS

    Executing Remote Stored Procedures

    HiTopp Community Member

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

        • 1. Re: Executing Remote Stored Procedures
          Dan Bracuk Community Member

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

          • 2. Re: Executing Remote Stored Procedures
            HiTopp Community Member

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

            • 3. Re: Executing Remote Stored Procedures
              yui8979

              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.

              • 4. Re: Executing Remote Stored Procedures
                Carl Von Stetten Community Member

                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.

                • 5. Re: Executing Remote Stored Procedures
                  yui8979 Community Member

                  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

                  • 6. Re: Executing Remote Stored Procedures
                    Carl Von Stetten Community Member

                    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:

                    1. Use <cfquery> if possible as a workaround.
                    2. File a bug report so the <cfstoredproc> issue can be tested by Adobe and fixed if need be.  Be sure to provide specific information on the setup of the SQL Server databases, linked server relationship, and any supporting sample code (pared back to the bare minimum required to reproduce the issue).

                    -Carl V.