Using ADO & Stored Procedures in C/SIDE

2

Comments

  • krikikriki Member, Moderator Posts: 9,094
    My error.
    It should be:
    IADOCommand.CommandText := 'SELECT TOP 1 NULL FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';
    

    (there was a '1' too many).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • kwalker0412kwalker0412 Member Posts: 11
    That worked in so far as it compiled and ran.
    I am still having troube with the RecordCount variable.
    But if I check the EOF, I get the result I need, so I am good! \:D/

    And I had no trouble INSERTing into the SQL table when I needed to.

    Thanks for all your help. =D>
    Kelly
  • WaldoWaldo Member Posts: 3,412
    ADO ... you gotta love it :)

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • ara3nara3n Member Posts: 9,255
    I just wanted to add some additional suggestion.

    When using recordset variable. Add it as local otherwise you will get an error for duplicate Variable.

    Second. When running a sql statement that returns a record set, for Decimal values etc, you will get an error
    This message is for C/AL programmers:
    This data type is not supported by C/SIDE. You can access data of any of the following data types: VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL"


    You can change you sql statement and use cast function
    Select Amount 
    from Customer
    
    to
    Select cast (Amount as float)
    from Customer
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • WaldoWaldo Member Posts: 3,412
    Thanks, Rashed. This is valuable info ... .
    May be you can add them to my blog as well?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • ara3nara3n Member Posts: 9,255
    Done.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • triplechocotriplechoco Member Posts: 1
    very interesting post! thanks a lot for sharing it with us..


    commission de surendettement - commission de surendettement, vous pouvez demander un dossier de surendettement.
  • jversusjjversusj Member Posts: 489
    That worked in so far as it compiled and ran.
    I am still having troube with the RecordCount variable.
    But if I check the EOF, I get the result I need, so I am good! \:D/

    And I had no trouble INSERTing into the SQL table when I needed to.

    Thanks for all your help. =D>
    Kelly

    i had this same problem. i added 1 line to your code and it appears like the count works.
    ADORecordSet.ActiveConnection := VarActiveConnection;
    ADORecordSet.CursorType       := 3;   //this sets the cursor to static, according to devguru, the count only works for static cursor
    ADORecordSet.Open(ADOCommand);
    
    kind of fell into this...
  • DRBDRB Member Posts: 105
    Hi,
    Can we use Windows Authentication also in this:

    lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password);

    In the above code, what should be changed for using Windows Authentication?
    -Dhan Raj Bansal
    Linkedin Profile: http://in.linkedin.com/in/dhanrajbansal
  • WaldoWaldo Member Posts: 3,412
    Check out www.connectionstrings.com ..

    May be this is a good alternative connectionstring for you:
    Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • DRBDRB Member Posts: 105
    Thanks Waldo, that worked.

    Now I am getting two other errors:
    1.
    Microsoft Dynamics NAV
    This message is for C/AL programmers:

    The call to member ConnectionString failed. ADODB.Connection returned the following message:
    Operation is not allowed when the object is open.

    OK

    2.cannot change the activeconnection property of a recordset object which has a command object as its source.


    Following is the code that I am using:

    IF ISCLEAR(lADOConnection) THEN BEGIN
    CREATE(lADOConnection);
    lADOConnection.ConnectionString:=GetConnectionString('ServerName', 'DatabaseName', TRUE);
    lADOConnection.Open;
    END;
    IF ISCLEAR(lADOCommand) THEN
    CREATE(lADOCommand);

    lvarActiveConnection := lADOConnection;
    lADOCommand.ActiveConnection := lvarActiveConnection;

    lADOCommand.CommandText :=SQLString(SelectionType);
    lADOCommand.CommandType := 1;
    lADOCommand.CommandTimeout := 0;
    lADOCommand.Execute;


    IF ISCLEAR(lADORecordset) THEN
    CREATE(lADORecordset);

    lADORecordset.ActiveConnection := lvarActiveConnection;
    lADORecordset.Open(lADOCommand);
    I:=0;
    WHILE NOT lADORecordset.EOF DO BEGIN
    I+=1;
    IF I=1 THEN
    InsertCaptionIntoExcel(SelectionType);
    InsertDataIntoExcel(SelectionType);
    lADORecordset.MoveNext;
    END;
    EndOfLoop;
    lADOConnection.Close;
    CLEAR(lADOConnection);

    Any help will be greatly appreciated.
    -Dhan Raj Bansal
    Linkedin Profile: http://in.linkedin.com/in/dhanrajbansal
  • mabl4367mabl4367 Member Posts: 143
    Hi Waldo!

    I tested your examples and everything works very well.

    Now that I try to use this in my own way I run into som trouble.

    I wanted to declare lADORecordSet as a global variable in a report that is going to get som data from another database using an sp.

    The problem is that as soon as i declare any global variable of type Automation, Microsoft ActiveX Data Objects 2.8 Library'.Recordset, I can no longer compile the report without geting an error message telling me that the variable is declared more then once.

    I have tried creating a new report with nothing in it except for this global variable and I get the same error.

    I also tried exporting this report in txt-format. When I open up the file in a text editor the variable is decladerd only once. Although there are some event triggers declared that seams to have something to do with the variable.
    OBJECT Report 51086 TEST SQL SP CALL 2
    {
      OBJECT-PROPERTIES
      {
        Date=11-08-18;
        Time=09:17:50;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
      }
      DATAITEMS
      {
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=9020;
          Height=3410;
        }
        CONTROLS
        {
        }
      }
      CODE
      {
        VAR
          lADORecordSet@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";
    
        EVENT lADORecordSet@1000000000::WillChangeField@9(cFields@1000000003 : Integer;Fields@1000000002 : Variant;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::FieldChangeComplete@10(cFields@1000000004 : Integer;Fields@1000000003 : Variant;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::WillChangeRecord@11(adReason@1000000003 : Integer;cRecords@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::RecordChangeComplete@12(adReason@1000000004 : Integer;cRecords@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::WillChangeRecordset@13(adReason@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::RecordsetChangeComplete@14(adReason@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::WillMove@15(adReason@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::MoveComplete@16(adReason@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::EndOfRecordset@17(VAR fMoreData@1000000002 : Boolean;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::FetchProgress@18(Progress@1000000003 : Integer;MaxProgress@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    
        EVENT lADORecordSet@1000000000::FetchComplete@19(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
        BEGIN
        END;
    

    Can anyone shed some light on this problem?
  • rmv_RUrmv_RU Member Posts: 119
    ara3n wrote:
    I just wanted to add some additional suggestion.

    You can change you sql statement and use cast function
    Select Amount 
    from Customer
    
    to
    Select cast (Amount as float)
    from Customer
    
    Because result of calculation is unpredictable I never use float datatype.
    Compare:
    declare @f1 float, @f2 float, @f3 float
    
    set @f1=12345678912345692
    set @f2=1
    set @f3=1
    
    select @f1, @f1+@f2+@f3, @f2+@f3+@f1
    
    Using money datatype is better way.
    Looking for part-time work.
    Nav, T-SQL.
  • jr_macarilayjr_macarilay Member Posts: 29
    is there a way to use Windows Authentication with this?

    i tried 'Integrated Security=TRUE' but im getting an error
  • julkifli33julkifli33 Member Posts: 1,073
    can we use this to automate customer master from database 1 to another databases?
    thanks
  • mohana_cse06mohana_cse06 Member Posts: 5,503
    is there a way to use Windows Authentication with this?

    i tried 'Integrated Security=TRUE' but im getting an error

    Use
    ConnectionString := 'Data Source='+ServerName+';'
    + 'Initial Catalog='+NavDb+';'
    + 'Trusted_Connection=True;';
  • julkifli33julkifli33 Member Posts: 1,073
    hi all...
    now i can execute from nav to SP
    i want to execute insert,update (and maybe delete row) to several databases
    so... how do i send the parameter (example : customer no, customer name, etc) become parameter in my storeprocedure?
    thanks
  • krikikriki Member, Moderator Posts: 9,094
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • kmkaotkmkaot Member Posts: 261
    I am able to get the records from ADO Connection.
    but unable to see the values or Store values in my NAV table because data type conversion.

    Please this code and adivce.
    SQLString:=' SELECT     PName, DeptName, PNumber,AttDate, MIN(CAST(RsTime AS Datetime)) AS InTime,'+
               'MAX(CAST(RsTime AS Datetime)) AS OutTime,'+
               'DATEDIFF(minute, MIN(CAST(RsTime AS Datetime)),MAX(CAST(RsTime AS Datetime))) AS Total'+
               ' FROM  dbo.TMP_ATTENDANCE_DAILY2'+
               ' where attDate >='  +SingleQuote+FORMAT(firstdate,0,'<Year4>-<Month,2>-<Day,2>')+SingleQuote+
               ' AND '+' AttDate <='+SingleQuote+FORMAT(Lastdate,0,'<Year4>-<Month,2>-<Day,2>') +SingleQuote+
               ' AND PName='+SingleQuote+kmtext+SingleQuote+''+
               ' GROUP BY PName,AttDate, DeptName, PNumber ORDER BY AttDATE';
    



    VARSQLString:=SQLString;
    MESSAGE(SQLString);
    ADORecSet:=ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
    ADORecSet.MoveFirst;

    REPEAT

    KinTime:= ADORecSet.Fields.Item('InTime').Value;
    kOutTme:= ADORecSet.Fields.Item('OutTime').Value;

    Myvar := Myvar +1;
    AttendanceData.INIT;
    AttendanceData.EntryNum:= vlastno;
    AttendanceData.PName:= ADORecSet.Fields.Item('pName').Value;
    AttendanceData.DeptName:= ADORecSet.Fields.Item('DeptName').Value ;
    AttendanceData.PNumber:= ADORecSet.Fields.Item('PNumber').Value;
    AttendanceData.Date:= ADORecSet.Fields.Item('attDate').Value ;
    IF KinTime <> 0D THEN
    BEGIN
    AttendanceData.Intime:= CREATEDATETIME(KinTime,0T);
    AttendanceData.Outtime:= CREATEDATETIME(kOutTme,0T);
    AttendanceData.total:= ADORecSet.Fields.Item('Total').Value ;
    END;
    AttendanceData.INSERT;
    vlastno:=vlastno+1;
    ADORecSet.MoveNext();
    UNTIL ADORecSet.EOF();
  • krikikriki Member, Moderator Posts: 9,094
    You should first check out which field it is that generates the error. Probably an integer or date or datetime field.

    If it is total, try to CAST it as integer.
    If it is a date/datetime, CAST it to a VARCHAR (in a format that you define like "YYMMDD HHMMSSNNN" so you are always sure you get that format), receive it in a text-variable in NAV and then convert it backup to date/datetime.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • EwanEwan Member Posts: 54
    Hi Am getting a strange compilation error when I try this

    My code is as below for the recordset. As soon as I try to save the report that calls the stored proc I get

    The C/AL obect was not saved in compiledform. Compile it from the Object Designer and run it again
    Internal Compiler error.Error Code= 00000019.

    If I comment out the line below it compiles and saves OK

    NewDocNo := Format(ADOrecordset.Fields.Item('DocumentNo').Value);

    Any Ideas?

    Ewan
     ADOconn.Open(ConnectionString);
    
    IF ISCLEAR(ADOcommand) THEN CREATE(ADOcommand);
    
     varConn := ADOconn;
    
     ADOcommand.ActiveConnection := varConn;
     ADOcommand.CommandText :='Interco';
     ADOcommand.CommandType := 4;
     ADOcommand.CommandTimeout := 0;
    
     ADOparameter:=ADOcommand.CreateParameter('@date',135,1,0,InterCoDate);
     ADOcommand.Parameters.Append(ADOparameter);
     ADOcommand.Execute;
    
     MESSAGE('Get Records');
    
     IF ISCLEAR(ADOrecordset) THEN
     CREATE(ADOrecordset);
    
     ADOrecordset.ActiveConnection := varConn;
     ADOrecordset.Open(ADOcommand);
     CurrDocNo :='';
     WHILE NOT ADOrecordset.EOF DO BEGIN
     NewDocNo := Format(ADOrecordset.Fields.Item('DocumentNo').Value);
     IF (NewDocNo <> CurrDocNo) THEN
     BEGIN
     CurrDocNo := NewDocNo ;
     MESSAGE(NewDocNo);
     END;
     ADOrecordset.MoveNext;
     END;
    
  • EwanEwan Member Posts: 54
    Resolved this in fact I am surprised anyone got this to work at all as you cannot access the Record set directly you have to access it by using the ADO stream object

    for further info see this post

    http://navitips.blogspot.co.uk/2012/05/ ... -data.html =D>

    Regards
  • krikikriki Member, Moderator Posts: 9,094
    try something like this:

    Name DataType Subtype Length
    ADOStream Automation 'Microsoft ActiveX Data Objects 6.0 Library'.Stream



    ADOGetFieldValue(ItxtFieldName : Text[1024]) OtxtValue : Text[1024]
    // ADOGetFieldValue
    // Gets the value of a field in current record
    // IMPORTANT : a record must be available at the moment by a ADOFind or ADONext
    // PARAMETERS:
    // ItxtFieldName : the name of the field to get
    // RETURN-VALUE : the value as text. All types of values will be returned as text

    IF FORMAT(ADORecSet.Fields.Item(ItxtFieldName).ActualSize) = FORMAT(0) THEN // NULL-value
    EXIT('');

    OtxtValue := '';

    ADOStream.Open;
    ADOStream.WriteText(ADORecSet.Fields.Item(ItxtFieldName).Value);
    ADOStream.Position:= 0;
    OtxtValue := ADOStream.ReadText;
    ADOStream.Close;

    EXIT;
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • EwanEwan Member Posts: 54
    Thanks

    forgot about checking for null field values :oops:
  • nitinagrawal003nitinagrawal003 Member Posts: 2
    Dear Waldo,
    Thank you very much for sharing this valuable information..
    Now I am able to see the result in message when I run report in C/AL.
    I need to know how do I print the result on report?
  • nitinagrawal003nitinagrawal003 Member Posts: 2
    Dear Waldo,

    Thank you very much for sharing valuable info.

    Now I am able to get message with required information.

    Please let me know how I will be able to print the data on the nav report of which SQL returned.

    Thanks,
    Nitin
  • mohana_cse06mohana_cse06 Member Posts: 5,503
    After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.
    WHILE NOT lADORecordset.EOF DO BEGIN  
    
    ptmpGlobalInventoryBuffer.INIT; 
    ptmpGlobalInventoryBuffer."Item No" := lADORecordset.Fields.Item('ItemNo').Value; 
    ptmpGlobalInventoryBuffer."Company Name" := lADORecordset.Fields.Item('CompanyName').Value; 
    ptmpGlobalInventoryBuffer."Location Code" := lADORecordset.Fields.Item('LocationCode').Value; 
    ptmpGlobalInventoryBuffer."Location Name" := lADORecordset.Fields.Item('LocationName').Value; 
    ptmpGlobalInventoryBuffer."Main Location" := lADORecordset.Fields.Item('MainLocation').Value; 
    ptmpGlobalInventoryBuffer.INSERT; 
    
    lADORecordset.MoveNext; 
    
    END;
    

    You need to take a integer dataitem and repeat the temp table and show the data in sections..
  • EwanEwan Member Posts: 54
    Hi

    Is it possible to run the ado command asynchronously?

    The reason being I could then set up a progress bar using a timer that updates until the stored procedure has finished executing.

    Anyone out there attempted to do this?
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    I made a bit of a framework for this, using stored procs with parameters, really clean, I use it for reading in an external webshop, works well in Classic: http://navitips.blogspot.co.at/2012/05/ ... -data.html

    So you end up with code like this:
    InitAll;
    
    ConnString := 'Provider=SQLOLEDB;Data Source=NAVISION;' +
             'Initial Catalog=NAV_INPUT;User ID=NAVREADER;Password=wonttellya;';
    
    SPName := 'sp_readtest1';
    
    ParamValues[1] := '3';
    
    RunADOStoredProcRetRows;
    IF NOT ADORecSet.EOF THEN
    REPEAT
      MESSAGE(GetFieldStr('field1')+'|' + GetFieldStr('field2'));
      ADORecSet.MoveNext;
    UNTIL ADORecSet.EOF;
    
    CloseAll;
    

    Really clean right?

    Now would someone PLEASE upgrade it to 2013 with .NET please? I have no clue about ADO.NET...
  • matthewtaingmatthewtaing Member Posts: 52
    Hey guys,

    Encountered an issue using ADO where my stored procedure is getting data from a linked server and it keeps dropping connection after idling for some time and gives an error message that says Login failed for user 'NT authority\anonymous logon' when I try to run my codeunit.

    If I go back to SQL management studio and and run the Test connection then my codeunit works again. The following is the t-sql that I run to create the linked server.
    EXEC master.dbo.sp_addlinkedserver @server = N'linkedservername\linkedserver_SQL', @srvproduct=N'SQL Server'
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername\linkedserver_SQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
    
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername\linkedserver_SQL',@useself=N'True',@locallogin=N'domain\username',@rmtuser=NULL,@rmtpassword=NULL
    
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'rpc out', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'use remote collation', @optvalue=N'true'
    

    The following is the connection string I used for my ADOConnection.
      ConnectionString:='Provider=SQLOLEDB;'
        + 'Data Source='+ServerName+';'
        + 'Initial Catalog='+DBName+';'
        + 'Integrated Security=SSPI;'
        + 'Trusted_Connection=True;';
    
    Appreciate it if any of you found the solution to this issue.

    Thanks
Sign In or Register to comment.