2

i have a problem in my delphi application with tadodataset the simple question after switching from delphi xe2 to xe9 and sql2014 i see in profiler that all of my queries started with SET NO_BROWSETABLE ON this cause recompile stored procedures and functions in sql server can some one tell me how can i disable this option and sample code

ADODataSet1.Close; ADODataSet1.CommandText := 'Select * from mytable'; ADODataSet1.Open;

and result in profiler : SET NO_BROWSETABLE ON Select * from mytable

dawood karimy
  • 179
  • 2
  • 13
  • What makes you think that this is anything to do with Delphi? Afaik, the exact Sql sent to the server is determined by the Ado/MDac layers "below" your Delphi code. Try using the profiler to observe what is generated when you save a change to a row's data - often the MDac layer will create a temporary, parameterized stored proc to do it, withiut your app being involved. – MartynA Feb 25 '16 at 09:02
  • look into answer to this question: http://stackoverflow.com/questions/29430665/strange-ado-behavior-generating-unwanted-no-browsetable-set-fmtonly-queries-in - different providers issue – Ivan Starostin Feb 25 '16 at 11:06

1 Answers1

2

Your question seems to imply that the SET NO_BROWSETABLE ON has only started since changing to a more recent Delphi version.

If that is what you mean, I am not sure your observation is accurate, at least I cannot reproduce any difference in behaviour between an app compiled with the current Delphi version, XE10 Seattle and Delphi 7 from 15 years go regarding NO_BROWSETABLE.

I have an instance of Sql Server 2014 running on this machine, which is Windows 10 Pro 64-bit. If I compile and run a minimal Delphi Seattle project that does a simple select * from sometable using a TAdoConnection and a TAdoQuery, Sql Server's Profiler shows the MDac layer sending a SET NO_BROWSETABLE ON, like you. However, if I compile and run exactly the same project in Delphi 7, I get exactly the same statements shown in the profiler - there is no difference whatsoever if the app is compiled in D7 or Seattle.

This is with the cursor location set to clUseClient. The SET NO_BROWSETABLE ON doesn't occur if I use clUseServer. It also doesn't occur in a minimal DBExpress application using a TSqlConnection and TSqlQuery so maybe that uses a server-side cursor as well.

See here for more info about NO_BROWSETABLE:

https://support.microsoft.com/en-us/kb/885146

I'm using the Microsoft OLE DB Provider for Sql Server. Although this is said to be deprecated by MS, I've always had far less trouble with it than with their provider for ODBC or that "native client" one.

You also might want to take a look at this SO q:

Strange ADO behavior generating unwanted NO_BROWSETABLE / set fmtonly queries in VB6

Btw you mention "xe9". Do you mean XE8 or Delphi Seattle or what?

Community
  • 1
  • 1
MartynA
  • 30,454
  • 4
  • 32
  • 73
  • thanks for your attention first and then i changed the locktype to itreadonly and i saw that it hide and my problem is fixed i can't test it with other delphi versions but i have my older app version that compiled with delphi xe2 and i dont see SET NO_BROWSETABLE in profiler and my main question is why my sql procedure and function are recompiling when is see SET NO_BROWSETABLE – dawood karimy Feb 28 '16 at 20:38
  • "my main question is why my sql procedure and function are recompiling" Well, I think that's a different q than you originally asked and therefore if you want an answer, you should post it as a new q. I think you should include in your new q whatever evidence you think you have that it is that "SET NO_BROWSETABLE ON" causes the recompilation you think is happening. How do you know that recompilation is occurring at all? – MartynA Feb 28 '16 at 20:46
  • SELECT DEST.TEXT FROM sys.[dm_exec_connections] SDEC CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST WHERE SDEC.[most_recent_session_id] = 393 i often see a create function and create procedure in query history of my sql server and after little search i understand that other people have my same problem to – dawood karimy Mar 02 '16 at 05:25