[vishualsoft]

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

[Luthfi]

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.

[vishualsoft]

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

[Luthfi]

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.

[vishualsoft]

Hi Luthfi,

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


With Best Regards.

Vishal

[Luthfi]

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.

[vishualsoft]

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:

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:

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:

ADOQuery1.Connection := ADOConnection;

SQL execution written is as follows:

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

[vishualsoft]

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

[Luthfi]

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.

[vishualsoft]

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

[vishualsoft]

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;

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

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

[clivew]

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?

[vishualsoft]

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

[vishualsoft]

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:

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.

ADOQuery_N := ADOQuery1;

Solution 2:

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

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