I upgraded and now my stored procedures don't work

Question

I upgraded to v22, and followed the instructions to set up a new linked server. Querying it works fine, but I can’t use the stored procedures. I get something like this every time:

--- Starting SF_Replicate for account V5.1.8
11:34:36: Parameters: salesforce2022 account Version: V5.1.8
11:34:36: DBAmpAZ 5.1.8.0 Copyright c 2021 CData Software, Inc.
11:34:36: Using options specified in Options parameter: soap
11:34:40: Retrying
11:34:40: Retrying
11:34:40: Retrying
11:34:40: Error: Unable to retrieve settings from Registry.
11:34:40: System.Data.SqlClient.SqlException (0x80131904): The OLE DB provider "MSOLEDBSQL" for linked server "salesforce2022" does not contain the table "sys_sfsession". The table either does not exist or the current user does not have permissions on tha
t table.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParamet
erEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolea
n inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at DBAmpAZ.DBAmpSettings.GetSysSFSession(SqlConnection currentConnection, DBAmpRegistry registry, TableOptions tableOptions)
ClientConnectionId:119f92c7-563b-4f68-b3c0-c215710791f8
Error Number:7314,State:1,Class:16
11:34:40: DBAmpAZ Operation FAILED.
11:34:42: Error: Replicate program was unsuccessful.
11:34:42: Error: Command string is C:\"Program Files"\DBAmp\DBAmpAZ.exe FullCopy "account" "DL-7KVJRL3" "salesforce_backups" "salesforce2022" "soap"
--- Ending SF_Replicate. Operation FAILED.
Msg 50000, Level 16, State 1, Procedure sf_replicate, Line 308 oBatch Start Line 0]
--- Ending SF_Replicate. Operation FAILED.

Page 1 / 1

The key error there is “System.Data.SqlClient.SqlException (0x80131904): The OLE DB provider "MSOLEDBSQL" for linked server "salesforce2022" does not contain the table "sys_sfsession". The table either does not exist or the current user does not have permissions on that table.” but the key clue is this line: “Starting SF_Replicate for account V5.1.8“ That indicates that your stored procedures are still the v5.1.8 versions, which are incompatible with the new version and the new version’s linked servers.

What you need to do is update the stored procedures. You can find the file “Create DBAmp SPROCS.sql” in the SQL folder of the CData DBAmp installation directory (make sure it’s the CData DBAmp/v22 installation directory, not your old version’s installation folder directory!). That script will create all the DBAmp stored procedures in whatever database you run it in, so be sure to run it in every database you would like to use with your upgraded version!

There is one extra step if you did not install DBAmp in the default installation directory. If that was the case, you will need to find all instances where @ProgDir is set in the Create DBAmp SPROCS.sql script and make sure it is set to the bin folder of your non-default installation directory. Once you make that edit, then you can go ahead and run it in all the databases you need them to be in.

Reply

Dani Moran · Accepted Answer

The key error there is “System.Data.SqlClient.SqlException (0x80131904): The OLE DB provider "MSOLEDBSQL" for linked server "salesforce2022" does not contain the table "sys_sfsession". The table either does not exist or the current user does not have permissions on that table.” but the key clue is this line: “Starting SF_Replicate for account V5.1.8“ That indicates that your stored procedures are still the v5.1.8 versions, which are incompatible with the new version and the new version’s linked servers.

What you need to do is update the stored procedures. You can find the file “Create DBAmp SPROCS.sql” in the SQL folder of the CData DBAmp installation directory (make sure it’s the CData DBAmp/v22 installation directory, not your old version’s installation folder directory!). That script will create all the DBAmp stored procedures in whatever database you run it in, so be sure to run it in every database you would like to use with your upgraded version!

There is one extra step if you did not install DBAmp in the default installation directory. If that was the case, you will need to find all instances where @ProgDir is set in the Create DBAmp SPROCS.sql script and make sure it is set to the bin folder of your non-default installation directory. Once you make that edit, then you can go ahead and run it in all the databases you need them to be in.

Reply

Connect With Us

Connect With Us

Reply

Sign up

CData Community

Scanning file for viruses.

This file cannot be downloaded

Connect With Us

Connect With Us