Help with Linked Servers

(4 posts) (2 voices)
  1. ajdjackson, Member

    Hi

    I'm having a bit of play at the moment and have hit an issue that I hope you can help me with.

    On the same server there are several VMs with MS SQL Server applications running.

    I have created a new VM and and have installed mydbr with MS SQL Server.

    I have managed to link one of the other VM's SQL Servers and the test connection is OK.

    I have created a simple mydbr report to test the ability to read data form a table.

    create procedure sp_DBR_Capita_Test as begin

    select top 10 * from [PH-HV-CAPITA\TRUTIMEV14].[Intelligent].dbo.Employees;

    end go

    However when I run this report I get the following error:

    Could not execute the report. There was an error in the report.
    SQLSTATE: 42000, code: 7405
    Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

    I'm not sure where I need to set these setting?

    Any help would be most appreciated.

    Jake

  2. myDBR Team, Key Master

    You can override the default connection level parameters in user/defaults.php. If you are using sqlsrv-driver use setting:

    $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET ARITHABORT OFF;SET ANSI_NULLS ON;';

    and if you are using the older FreeTDS drivers (mssql) the setting is:

    $mydbr_defaults['db_connection']['sql_server_init'] = 'SET ANSI_WARNINGS ON;SET ARITHABORT OFF;SET ANSI_NULLS ON;';

    --
    myDBR Team

  3. ajdjackson, Member

    Cheers

    That worked a treat.

    Now scratching my head converting some mysql sps to mssql sps :(

    Can't believe there is no on duplicate key update :(

    Thanks

    Jake

  4. myDBR Team, Key Master

    The SQL Server equivalent to "on duplicate key update" is the MERGE-command.

    --
    myDBR Team


Reply

You must log in to post.