• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Executing Remote Stored Procedures

Guest
Apr 23, 2012 Apr 23, 2012

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

TOPICS
Database access

Views

3.1K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 23, 2012 Apr 23, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 30, 2012 Apr 30, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 08, 2015 Jun 08, 2015

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 09, 2015 Jun 09, 2015

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 11, 2015 Jun 11, 2015

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jun 12, 2015 Jun 12, 2015

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation