Synch Stored Proceedure Error

Brian Donahue

Hi Martin,

Thanks for posting. It does look as if the linked server called OPERA
isn't available. When you create a stored procedure, SQL Server checks to
see that the linked server name exists. If it doesn't, you could get thrown
an error. Since Linked Servers are out of the scope of a database and fall
under the realm of management, you will probably need to create the linked
server manually on the server that you're running the migration against.

Please let me know if this works!

Regrads,

Brian Donahue
Red Gate Technical Support

"martin brawley" <martinb@bms-software.com> wrote in message
news:p$hUfvshEHA.1512@server53...
> I get the following error trying to synch 2 databases. Any ideas.
>
> The following error message was returned from the SQL Server:
> [7391] The operation could not be performed because the OLE DB provider
> 'MSDASQL' was unable to begin a distributed transaction.
> The following SQL command caused the error:
> CREATE PROCEDURE dbo.Accounts_Opera_SSALE_Append
> AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> Delete From ssale
> INSERT INTO
> dbo.ssale (ss_acode, ss_adesc,
> ss_nominal, ss_vatcode,
> ss_qtyind, ss_costind)
> SELECT
> LTRIM(RTRIM(ss_acode)), LTRIM(RTRIM(ss_adesc)),
> LTRIM(RTRIM(ss_nominal)), LTRIM(RTRIM(ss_vatcode)),
> LTRIM(RTRIM(ss_qtyind)), LTRIM(RTRIM(ss_costind))
> FROM OPENQUERY(OPERA, 'Select * From z_ssale')
>
>
> The following messages were returned from the SQL Server:
> [5701] Changed database context to 'ProgressDataSQL_ETL'.
> [5703] Changed language setting to us_english.
> [0] Creating [dbo].[Accounts_Opera_SSALE_Append]
>
>

Brian Donahue

Hi Martin,



Here's another thing I had ran into recently: there is a problem
with the way that SQL Compare sets up the transactions because the ODBC
drivers don't support the serialization level that SQL does. We need to have
the line ' SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' in all of the
scripts that SQL Compare runs, but this is what causes script errors on
linked servers.



The only workaround at this point is to save the script to a file,
change SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to read SET TRANSACTION
ISOLATION LEVEL READ COMMITTED and save the script. Hopefully this doesn't
cause other problems when you run the script.



Regards,



Brian Donahue

Technical Support Engineer

Red Gate Software Ltd.

+44 (0)870 160 0037 ext 21

US and CAN 1-866-RED GATE

mailto:brian.donahue@red-gate.com



"martin brawley" <martinb@bms-software.com> wrote in message
news:p$hUfvshEHA.1512@server53...
> I get the following error trying to synch 2 databases. Any ideas.
>
> The following error message was returned from the SQL Server:
> [7391] The operation could not be performed because the OLE DB provider
> 'MSDASQL' was unable to begin a distributed transaction.
> The following SQL command caused the error:
> CREATE PROCEDURE dbo.Accounts_Opera_SSALE_Append
> AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> Delete From ssale
> INSERT INTO
> dbo.ssale (ss_acode, ss_adesc,
> ss_nominal, ss_vatcode,
> ss_qtyind, ss_costind)
> SELECT
> LTRIM(RTRIM(ss_acode)), LTRIM(RTRIM(ss_adesc)),
> LTRIM(RTRIM(ss_nominal)), LTRIM(RTRIM(ss_vatcode)),
> LTRIM(RTRIM(ss_qtyind)), LTRIM(RTRIM(ss_costind))
> FROM OPENQUERY(OPERA, 'Select * From z_ssale')
>
>
> The following messages were returned from the SQL Server:
> [5701] Changed database context to 'ProgressDataSQL_ETL'.
> [5703] Changed language setting to us_english.
> [0] Creating [dbo].[Accounts_Opera_SSALE_Append]
>
>