#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0

    Question Discussion: TADOQuery Error While Executing The SQL Second Time


    Hi All,

    I am getting a strange behavior by TADOQuery in Delphi7 component on Windows XP and Windows7 operating system. Following issue works fine on Windows7 oprating system, but it gives an error on XP operating system.

    Let me explain the schenario, I have one TADOConnection component and two TADOQuery components. The TADOConnection component is having the value for "ConnectionString" property as follows:

    ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User ID; Data Source = My Data Source';

    I have already applied the role/granted permission to the User ID/Password which I am login to the application.

    I have connected TADOConnection component to the TADOQuery components using "Connection" property.

    When I execute the first sql then it works fine without any error, but when I execute the second sql that time I am getting following error on Windows XP oprating system.

    [Microsoft] [SQL Server Native Client 10.0] [SQL Server] The SELECT permission was denied on the object 'My Table Name', database 'My Database Name', schema 'dbo'


    The same code if I execute on Windows7 oprating system it works fine without any error.


    Could anybody put focus what could be missing or what could be the issue ?

    ------------------------------------------------------------------------------------------------------------------------

    I have added "Persist Security Info = False;" to a connection string as follows:

    ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Persist Security Info = False; Password = My Password; User ID = My User ID; Data Source = My Data Source';

    Now, whenever I execute the sql using TADOQuery component, I need to write following statement every time, even if I have already set the "Connection" property of TADOQuery at the beginning, still I need to set this property whenever I execute the sql.

    ADOQuery1.Connection := ADOConnection1;

    But as I said, On Windows7 there is no need to change the "ConnectionString" property of TADOConnection component and no need to set the "Connection" property of any TADOQuery component.


    Could anybody tell what exactly has to be done ?

    If your application is too hugh and lot of places coding changes are required, would anybody agree with that ? And what about testing efforts ? At all the places testing has to be done, right ?

    So, I am still in search of proper solution. The question is still unanswered.


    If anybody is having any clue or hint or proper solution that would be highly appreciated.


    Thanks In Advance.



    With Best Regards.

    Vishal
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    It seems that somewhere in your T-SQL you have put exclusive lock to a table and the lock had not released properly at the end of your query. It's possible ADO Driver in Win7 release this lock when it detected this problem while in XP it does not.

    Perhaps you should try to find out which part of your T-SQL that caused the lock.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi Luthfi,

    Thank you for the reply. But even if I write simple sql like:

    Select * from MyTableName;

    Then also the same issue happens.



    With Best Regards.

    Vishal
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    If with such simple SQL you get this problem, then the fault is somewhere else in your program. Not with ADO Driver. You should check events generated by your sql, such as the TADOQuery's OnBeforeOpen, OnAfterOpen, etc. And dont forget to check events of your TADOConnection, such as OnExecuteComplete, OnWillExecute, etc.

    You should trace to see what happened when you run the SQL at the first time.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi Luthfi,

    There are no events are written for TADOConnection component or TADOQuery components in the application.


    With Best Regards.

    Vishal
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    Maybe you could send me the sample project and table structure of the table. I have XP professional under VMWare with SQL Server 2000 and 2008 R2. So I can check it out myself.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi Luthfi again,

    I am working on Delphi7 and SQL Server 2005, 2008.
    As you asked table structre of the table, so you could consider any table you have, not an issue, coz simple "SELECT" statement as shown in the following example is giving an error.

    So, just have one TADOConnection component and two or more TADOQuery components. And make the all TADOQuery components connection with one and only one TADOConnection component as shown below:

    Code:
    ADOConnection1.Connected := False;
    ADOConnection1.LoginPrompt := False;
    ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User ID; Data Source = My Data Source';
    ADOConnection1.Connected := True;
    I am doing a very simple stuff, since the huge project is entirely depending opon BDE strategy, so I need to replace TADOQuery component with TADOQuery, so I am going with TADOQuery and TADOConnection components.

    Let me explain you what simple steps I am doing are as below:

    I have "SQL Setrver Native Client 10.0" driver which shows in ODBC DSN dialog box.

    1. As the application starts launching I am connecting all the TADOQuery components to a single TADOConnection component. "ConnectionString" as you know I have already provided in my first thread itself.

    Making connection as shown below, coz TADOConnection and some TADOQuery components are kept at design time:

    Code:
    for iCount := 0 to ComponentCount - 1 do
    begin
      if Components[iCount] is TADOQuery then
      begin
        TADOQuery(Components[iCount]).Connection := ADOConnection1;
      end;
    end;
    2. Applying role/permission to user who is logging in to the application with his provided password.

    3. Then I take any of the TADOQuery and without writing following statement I try to execute simple sql as below:

    Code:
    ADOQuery1.Connection := ADOConnection;
    SQL execution written is as follows:

    Code:
    ADOQuery1.Close;//Some places this statement is not written
    ADOQuery1.Clear;
    ADOQuery1.SQL.Add('Select * from MyTableName');
    ADOQuery1.Open;//OR ADOQuery1.Active := True;
    4. The time I execute the last statement I get the error as I mentioned in the first thread.


    These are the simple stuff I am doing.

    Please take this small piece code and kindly see whether you could get the succes.

    One doubt from my side, is there anything missing in the TADOConnection component's "ConnectionString" property ?



    Thanking You for all your kind help.

    Expecting To Hear From You And With Best Regards.

    Vishal
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi,

    I have kept one TADOConnection component and 2 TADOQuery components and one TButton component at design time.

    I have written the code at two places:

    1. FormShow event:

    Here I am making database connection to TADOConnection component and after that assigning all TADOQuery components to it.

    2. Button click event:

    Here I am first allpying the role to a perticular use and the tring to execute the sql, where it is failing to execute the sql.


    Code is as follows:

    --------------------------------------------------------------

    procedure TForm1.FormShow(Sender: TObject);
    var
    iCount : Integer;
    begin

    ADOConnection1.Connected := False;


    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

    ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;User ID=My User ID'
    + ';Password=My password;Data Source=My data Source';

    OR

    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

    ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Initial Catalog=My Database Name;Data Source= My data Source';

    OR

    //SQL execution doesn't work with TADOQuery, following connection string is done using TADOConnection component, even if connection is successful with TADOConnection component

    //but while applying the role there is no error.

    ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Data Source=My data Source';



    Only following connection string works for all query components, i.e. there is not error while executing the sql with any query component, but when I provide "Data Source=My data Source" it is not connecting at all using TADOConnection component.


    ADOConnection1.ConnectionString := 'Provider=SQLNCLI10.1;Persist Security Info=False;User ID=My User ID;Password=My password;'
    + 'Initial Catalog=My Database Name;Data Source= 3.204.35.38,51722';



    ADOConnection1.Connected := True;

    ADOConnection1.BeginTrans;
    for iCount := 0 to ComponentCount - 1 do
    begin
    if Components[iCount] is TADOQuery then
    begin
    TADOQuery(Components[iCount]).Connection := ADOConnection1;
    end;
    end;
    ADOConnection1.CommitTrans;
    end;


    --------------------------------------------------------------


    procedure TForm1.Button1Click(Sender: TObject);
    begin

    \\"test123" is the password

    ADOQuery3.Close;
    ADOQuery3.SQL.Clear;
    ADOQuery3.SQL.Add('EXEC sp_setapprole ''APP_USER_APP'', ''test123''');
    ADOQuery3.ExecSQL;

    ADOQuery1.Close;
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('Select * from Table1');
    ADOQuery1.Open; \\Getting error in this statement

    ADOQuery2.Close;
    ADOQuery2.SQL.Clear;
    ADOQuery2.SQL.Add('Select * from Table1');
    ADOQuery2.Open; \\Getting error in this statement
    end;
    --------------------------------------------------------------



    With Best Regards.

    Vishal
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    151
    Rep Power
    3
    After I re-read your posts, I think you should try to use the following connection string (the connection string is considering you are using SQL Server native client).

    "Provider=SQLNCLI10.1; DataTypeCompatibility=80;Password=My Password; User ID=My User ID; Data Source=My Data Source"

    if it does not work, perhaps you have more recent SQL Server Native Client driver. Use the following instead.

    "Provider=SQLNCLI11; DataTypeCompatibility=80;Password=My Password; User ID=My User ID; Data Source=My Data Source"

    Let me know the result.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi Luthfi again,

    I tried with the connection string you provided, but it is giving an error while making connection using "TADOConnection.Connected := True;" statement as below:

    Named pipes provider : Could not open a connection to a SQL server [53].

    I tried on SQL server 2008.

    With Best Regards.

    Vishal
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi,

    If I execute the following 2 lines of code before executing sql using any TADOQuery component then that time there is no error, could I get any clue what is the reason, when I EXECUTE FOLLOWING @ LINES OF CODE< WHY I don't get "SELECT permission..." error.

    ADoConnection1.Connected := False;
    ADoConnection1.Connected := True;

    -----------------------------------------------------------------
    Code:
    procedure TForm1.Button1Click(Sender: TObject); 
    begin 
    
    \\"test123" is the password 
    
    ADOQuery3.Close; 
    ADOQuery3.SQL.Clear; 
    ADOQuery3.SQL.Add('EXEC sp_setapprole ''APP_USER_APP'', ''test123'''); 
    ADOQuery3.ExecSQL; 
    
    
    ADoConnection1.Connected := False;
    ADoConnection1.Connected := True;
    ADOQuery1.Close; 
    ADOQuery1.SQL.Clear; 
    ADOQuery1.SQL.Add('Select * from Table1'); 
    ADOQuery1.Open; \\No error in this statement 
    
    
    ADoConnection1.Connected := False;
    ADoConnection1.Connected := True;
    ADOQuery2.Close; 
    ADOQuery2.SQL.Clear; 
    ADOQuery2.SQL.Add('Select * from Table1'); 
    ADOQuery2.Open; \\No error in this statement 
    end;
    -----------------------------------------------------------------


    Thanking You And With Best Regards.

    Vishal
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I am not at all familiar with TADOQuery, I use other database access components, so this
    is just speculation.

    Is there any chance that even with the SELECT statement you are in a transaction and you need to end it
    with a commit or similar to clear the way for the second query?
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi,

    No, for time being I am trying with sample project where I am just trying execute "Select * from MyTable Name", there also, I am getting the error when I take second TADOQuery component and try to execute same above statement.


    With Best Regards.

    Vishal
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    9
    Rep Power
    0
    Hi All,

    I have got the starnge solutions for the strange problem. Till now it is working perfectly.


    As I said, the SQL execution would not give error untill I use any second TADOQuery component. But the moment I use any other TADOQuery rather than first one, I get an

    error like, "SELECT permision was denied on object <'Table Name'>......".

    I have found two solutions:

    I am using DSN as Datya Source, so even if I use SQL Sever or Oracle as a database, so there is no need to make much changes.


    Solution1:

    I have just assigned the TADOQuery No. 1 to any other TaDOQuery which I am going to use for SQL execution as below:


    Code:
    ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=True;'
                                     + 'User ID= My User ID;Password= My Password;'
                                     + 'Data Source= My DSN Data Source Name';
    
    ADOConnection1.Connected := True;
    
    ADOQuery1.Connection := ADOConnection1;
    
    ADOQuery2.Close;
    
    ADOQuery2 := ADOQuery1;  //Assigning ADOQuery1 to ADOQuery2
    
    ADOQuery2.SQL.Clear;
    ADOQuery2.SQL.Add('Select * from Table1');
    ADOQuery2.Open; // OR ADOQuery2.Active := True;

    In above sample code, there is no effect whether I use ADOQuery1 for execution for any time or not, i.e. for the first time or never, everything works fine.

    So, whenever I need to use any other ADOQuery rather than ADOQuery1, I just assign the ADOQuery1 to the respective ADOQuery component before SQL execution i.e. before

    assigning SQL to the respective TADOQuery component as below.


    Code:
    ADOQuery_N := ADOQuery1;

    Solution 2:

    I have used TClientDataSet and TDataSetProvider component with TADOQuery component as follows:

    Code:
    ADOQuery1.SQL.Clear;
    ADOQuery1.SQL.Add('Select * from Table1');
    
    DataSetProvider2.DataSet := ADOQuery1;  //As It Is Required, Assigning ADOQuery1 to TDataSetProvider2
    
    ClientDataSet2.SetProvider(DataSetProvider2);
    ClientDataSet2.Active := True;
    
    ClientDataSet2.First;
    
    for iCount := 0 to ClientDataSet2.RecordCount - 1 do
    begin
      ADOQuery1.Close;
      ADOQuery1.SQL.Clear;
    
      ADOQuery1.SQL.Add('Select * from Table2 where Column_Name = ' + QuotedStr(ClientDataSet2.FieldByName('Column_N_Name').AsString));
    
      DataSetProvider3.DataSet := ADOQuery1;  //As It Is Required, Assigning ADOQuery1 to TDataSetProvider3
    
      ClientDataSet3.SetProvider(DataSetProvider3);
      ClientDataSet3.Active := True;
    
      //Workout with ClientDataset3 in your code, then clear the data in it as shown below. 
      //So, in the loop we could workout freshly with ClientDataSet3.
    
      ClientDataSet3.DisableControls;
      ClientDataSet3.EmptyDataSet;
      ClientDataSet3.EnableControls;
    
      ClientDataSet2.Next;
    end;

    Kindly Let me know, if there is anything to say from your side.



    With Best Regards.

    Vishal

IMN logo majestic logo threadwatch logo seochat tools logo