Synch Stored Proceedure Error

Brian DonahueBrian Donahue Posts: 6,590 New member
edited August 31, 2004 11:09AM in SQL Compare Previous Versions
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" <[email protected]> wrote in message
news:[email protected]
> 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]
>
>

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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:[email protected]



    "martin brawley" <[email protected]> wrote in message
    news:[email protected]
    > 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]
    >
    >
This discussion has been closed.