Using ADO & Stored Procedures in C/SIDE

2

Comments

  • krikikriki Posts: 7,917Member, Moderator
    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
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • kwalker0412kwalker0412 Posts: 11Member
    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 Posts: 3,412Member
    ADO ... you gotta love it :)

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • ara3nara3n Posts: 9,147Member
    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
    
  • WaldoWaldo Posts: 3,412Member
    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 Posts: 9,147Member
    Done.
  • triplechocotriplechoco Posts: 1Member
    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 Posts: 487Member
    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 Posts: 100Member
    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 Posts: 3,412Member
    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 Posts: 100Member
    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 Posts: 143Member
    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 Posts: 108Member
    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.
    NAV and T-SQL Developer.
    Have performance issues?
    Contact me.
  • jr_macarilayjr_macarilay Posts: 29Member
    is there a way to use Windows Authentication with this?

    i tried 'Integrated Security=TRUE' but im getting an error
  • julkifli33julkifli33 Posts: 937Member
    can we use this to automate customer master from database 1 to another databases?
    thanks
  • mohana_cse06mohana_cse06 Posts: 4,906Member
    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 Posts: 937Member
    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 Posts: 7,917Member, Moderator
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • kmkaotkmkaot Posts: 125Member
    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();
    NavDataTypeError.jpg
    40K
  • krikikriki Posts: 7,917Member, Moderator
    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
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • EwanEwan Posts: 54Member
    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 Posts: 54Member
    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 Posts: 7,917Member, Moderator
    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
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

  • EwanEwan Posts: 54Member
    Thanks

    forgot about checking for null field values :oops:
  • nitinagrawal003nitinagrawal003 Posts: 2Member
    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 Posts: 2Member
    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 Posts: 4,906Member
    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 Posts: 54Member
    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 Posts: 1,470Member
    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 Posts: 51Member
    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
    ADOError.jpg
    27K
2
Sign In or Register to comment.