|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sp_provider_types_rowset and some other stored procedures keep being executedexecuted We recently experienced a very interesting problem. When connecting to SQL Server 2000 (SP4) using ADO, after the recordset is retrieved, we can see the following stored procedures kept being executed every we submit an update query. This is what we saw in the tracing file: ------------------------------------------------------------------------- .... [submit an update query and maybe some other querys] exec sp_provider_types_rowset NULL, NULL exec [dbname]..sp_tables_rowset;2 NULL, NULL exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL [submit an update query and maybe some other querys] exec sp_provider_types_rowset NULL, NULL exec [dbname]..sp_tables_rowset;2 NULL, NULL exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL .... ------------------------------------------------------------------------ What may be causing those stored procedured to be exectued all the time? There is little docmentation on those stored procedures anywhere...:( dragooon (dragooon@newsgroups.nospam) writes:
Show quote > sp_provider_types_rowset and some other stored procedures keep being ADO performs a lot of queries behind your back, and is not always that> executed > > We recently experienced a very interesting problem. When connecting to SQL > Server 2000 (SP4) using ADO, after the recordset is retrieved, we can see > the following stored procedures kept being executed every we submit an > update query. This is what we saw in the tracing file: > > ------------------------------------------------------------------------- > ... > [submit an update query and maybe some other querys] > exec sp_provider_types_rowset NULL, NULL > exec [dbname]..sp_tables_rowset;2 NULL, NULL > exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL > exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL > [submit an update query and maybe some other querys] > exec sp_provider_types_rowset NULL, NULL > exec [dbname]..sp_tables_rowset;2 NULL, NULL > exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL > exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL > ... > ------------------------------------------------------------------------ > > What may be causing those stored procedured to be exectued all the time? > There is little docmentation on those stored procedures anywhere...:( effective. Personally, I much prefer using stored procedures for updates. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland - Thanks for your reply. Unfortunately it is not that easy to just
switch everything to stored procedures (otherwise what's the point to use ADO?). Right now what I am looking for is, why are those stored procedures executed? what is triggering those stored procedures at the background? By know more the background activities, I hope we can fix this by maybe changing some properties or queries. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98176F8A1C26FYazorman@127.0.0.1... > dragooon (dragooon@newsgroups.nospam) writes: >> sp_provider_types_rowset and some other stored procedures keep being >> executed >> >> We recently experienced a very interesting problem. When connecting to >> SQL >> Server 2000 (SP4) using ADO, after the recordset is retrieved, we can >> see >> the following stored procedures kept being executed every we submit an >> update query. This is what we saw in the tracing file: >> >> ------------------------------------------------------------------------- >> ... >> [submit an update query and maybe some other querys] >> exec sp_provider_types_rowset NULL, NULL >> exec [dbname]..sp_tables_rowset;2 NULL, NULL >> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >> [submit an update query and maybe some other querys] >> exec sp_provider_types_rowset NULL, NULL >> exec [dbname]..sp_tables_rowset;2 NULL, NULL >> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >> ... >> ------------------------------------------------------------------------ >> >> What may be causing those stored procedured to be exectued all the time? >> There is little docmentation on those stored procedures anywhere...:( > > ADO performs a lot of queries behind your back, and is not always that > effective. > > Personally, I much prefer using stored procedures for updates. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx The functions you are referring to obtain database metadata. IIRC they are
used when you use ADO client-side Recordsets to perform updates; ADO is using these to query the database metadata and construct a SQL update statement. They may used elsewhere in ADO, perhaps even when querying data, depending on the options selected. What does your ADO code look like? Cheers, Bob Beauchemin http://www.SQLskills.com/blogs/bobb Show quote "dragooon" <dragooon@newsgroups.nospam> wrote in message news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... > Erland - Thanks for your reply. Unfortunately it is not that easy to just > switch everything to stored procedures (otherwise what's the point to use > ADO?). Right now what I am looking for is, why are those stored procedures > executed? what is triggering those stored procedures at the background? By > know more the background activities, I hope we can fix this by maybe > changing some properties or queries. > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns98176F8A1C26FYazorman@127.0.0.1... >> dragooon (dragooon@newsgroups.nospam) writes: >>> sp_provider_types_rowset and some other stored procedures keep being >>> executed >>> >>> We recently experienced a very interesting problem. When connecting to >>> SQL >>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we can >>> see >>> the following stored procedures kept being executed every we submit an >>> update query. This is what we saw in the tracing file: >>> >>> ------------------------------------------------------------------------- >>> ... >>> [submit an update query and maybe some other querys] >>> exec sp_provider_types_rowset NULL, NULL >>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>> [submit an update query and maybe some other querys] >>> exec sp_provider_types_rowset NULL, NULL >>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>> ... >>> ------------------------------------------------------------------------ >>> >>> What may be causing those stored procedured to be exectued all the time? >>> There is little docmentation on those stored procedures anywhere...:( >> >> ADO performs a lot of queries behind your back, and is not always that >> effective. >> >> Personally, I much prefer using stored procedures for updates. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > I have two Ado recordsets in the program as follows:
For recordset 1: .CursorLocation = adUseClient .CursorType = adOpenDynamic .LockType = adLockOptimistic .Properties("Initial Fetch Size") = 10 .Properties("Update Criteria") = ADODB.adCriteriaKey .CacheSize = 10 .Open , , , , adAsyncFetch For recordset 2: .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open Any idea? I don't think any of the above properties should trigger those weird stored procedures... Thanks! Show quote "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message news:uXCgqekuGHA.4460@TK2MSFTNGP04.phx.gbl... > The functions you are referring to obtain database metadata. IIRC they are > used when you use ADO client-side Recordsets to perform updates; ADO is > using these to query the database metadata and construct a SQL update > statement. They may used elsewhere in ADO, perhaps even when querying > data, depending on the options selected. > > What does your ADO code look like? > > Cheers, > Bob Beauchemin > http://www.SQLskills.com/blogs/bobb > > > > "dragooon" <dragooon@newsgroups.nospam> wrote in message > news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... >> Erland - Thanks for your reply. Unfortunately it is not that easy to just >> switch everything to stored procedures (otherwise what's the point to use >> ADO?). Right now what I am looking for is, why are those stored >> procedures executed? what is triggering those stored procedures at the >> background? By know more the background activities, I hope we can fix >> this by maybe changing some properties or queries. >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns98176F8A1C26FYazorman@127.0.0.1... >>> dragooon (dragooon@newsgroups.nospam) writes: >>>> sp_provider_types_rowset and some other stored procedures keep being >>>> executed >>>> >>>> We recently experienced a very interesting problem. When connecting to >>>> SQL >>>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we can >>>> see >>>> the following stored procedures kept being executed every we submit an >>>> update query. This is what we saw in the tracing file: >>>> >>>> ------------------------------------------------------------------------- >>>> ... >>>> [submit an update query and maybe some other querys] >>>> exec sp_provider_types_rowset NULL, NULL >>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>> [submit an update query and maybe some other querys] >>>> exec sp_provider_types_rowset NULL, NULL >>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>> ... >>>> ------------------------------------------------------------------------ >>>> >>>> What may be causing those stored procedured to be exectued all the >>>> time? >>>> There is little docmentation on those stored procedures anywhere...:( >>> >>> ADO performs a lot of queries behind your back, and is not always that >>> effective. >>> >>> Personally, I much prefer using stored procedures for updates. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> > > Hmmm... not so weird. ADO is a generalized client API that doesn't assume
(ie by having it hardcoded in the provider) what database or version of the database its working with. These stored procedures retrieve the metadata ADO and OLE DB (ADO is a library layered over OLE DB) needs. You can almost do a one-to-one correspondence. sp_provider_types_rowset and sp_columns_rowset are used to populate OLE DB interfaces IColumnsInfo and IColumnsRowset which is used to expose Properties in the ADO Field object. It has things like data type and column size. So if you say: rs.Fields.Field(1) = 300 it knows of the field data type is int, decimal, or float. Or varchar. ;-) And, for example, if you use aliases in your SQL query (e.g. authorlastname vs au_lname in the database, ADO has to keep track of the original name to construct UPDATE statements. And ensure the database schema hasn't changed since you did the SELECT. sp_tables_rowsets gets catalog, schema, and table name, useful for creating UPDATE statements. It has a "(schema) last updated" column also. You indicated it should create UPDATE statements: .Properties("Update Criteria") = ADODB.adCriteriaKey They could cache some of this, but need to ensure its up-to-date, as the client-side recordset doesn't lock data or metadata in the DBMS itself. If the DBA issues: ALTER TABLE foo ALTER COLUMN... in between your SELECT and your UPDATE, ADO needs to "know", because it doesn't want to generate statements that fail. Cheers, Bob Beauchemin http://www.SQLskills.com/blogs/bobb Show quote "dragooon" <dragooon@newsgroups.nospam> wrote in message news:eay67mnuGHA.4544@TK2MSFTNGP04.phx.gbl... >I have two Ado recordsets in the program as follows: > > For recordset 1: > > .CursorLocation = adUseClient > .CursorType = adOpenDynamic > .LockType = adLockOptimistic > .Properties("Initial Fetch Size") = 10 > .Properties("Update Criteria") = ADODB.adCriteriaKey > .CacheSize = 10 > .Open , , , , adAsyncFetch > > For recordset 2: > > .CursorLocation = adUseClient > .CursorType = adOpenKeyset > .LockType = adLockOptimistic > .Open > > Any idea? I don't think any of the above properties should trigger those > weird stored procedures... > > Thanks! > > "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message > news:uXCgqekuGHA.4460@TK2MSFTNGP04.phx.gbl... >> The functions you are referring to obtain database metadata. IIRC they >> are used when you use ADO client-side Recordsets to perform updates; ADO >> is using these to query the database metadata and construct a SQL update >> statement. They may used elsewhere in ADO, perhaps even when querying >> data, depending on the options selected. >> >> What does your ADO code look like? >> >> Cheers, >> Bob Beauchemin >> http://www.SQLskills.com/blogs/bobb >> >> >> >> "dragooon" <dragooon@newsgroups.nospam> wrote in message >> news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... >>> Erland - Thanks for your reply. Unfortunately it is not that easy to >>> just switch everything to stored procedures (otherwise what's the point >>> to use ADO?). Right now what I am looking for is, why are those stored >>> procedures executed? what is triggering those stored procedures at the >>> background? By know more the background activities, I hope we can fix >>> this by maybe changing some properties or queries. >>> >>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>> news:Xns98176F8A1C26FYazorman@127.0.0.1... >>>> dragooon (dragooon@newsgroups.nospam) writes: >>>>> sp_provider_types_rowset and some other stored procedures keep being >>>>> executed >>>>> >>>>> We recently experienced a very interesting problem. When connecting to >>>>> SQL >>>>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we can >>>>> see >>>>> the following stored procedures kept being executed every we submit an >>>>> update query. This is what we saw in the tracing file: >>>>> >>>>> ------------------------------------------------------------------------- >>>>> ... >>>>> [submit an update query and maybe some other querys] >>>>> exec sp_provider_types_rowset NULL, NULL >>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> [submit an update query and maybe some other querys] >>>>> exec sp_provider_types_rowset NULL, NULL >>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> ... >>>>> ------------------------------------------------------------------------ >>>>> >>>>> What may be causing those stored procedured to be exectued all the >>>>> time? >>>>> There is little docmentation on those stored procedures anywhere...:( >>>> >>>> ADO performs a lot of queries behind your back, and is not always that >>>> effective. >>>> >>>> Personally, I much prefer using stored procedures for updates. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>> >>>> Books Online for SQL Server 2005 at >>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>> Books Online for SQL Server 2000 at >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >>> >> >> > > Hi Bob -
Thanks a lot for the explanation...the weird thing is, I have two versions of the same application, both uses the same recordsets (with properties("Update Criteria")=Adodb.adCriteriaKey). The only difference is that they were compiled with different dlls (maybe different Ado version too). The old version has this problem while the new one does not. So it seems like I can not blame the properties("Update Criteria")=Adodb.adCriteriaKey? Anyway, thanks for your help, I will take it out and give it a try. Jesse Chen Show quote "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message news:elXlG1nuGHA.1272@TK2MSFTNGP05.phx.gbl... > Hmmm... not so weird. ADO is a generalized client API that doesn't assume > (ie by having it hardcoded in the provider) what database or version of > the database its working with. These stored procedures retrieve the > metadata ADO and OLE DB (ADO is a library layered over OLE DB) needs. You > can almost do a one-to-one correspondence. > > sp_provider_types_rowset and sp_columns_rowset are used to populate OLE DB > interfaces IColumnsInfo and IColumnsRowset which is used to expose > Properties in the ADO Field object. It has things like data type and > column size. > So if you say: rs.Fields.Field(1) = 300 > it knows of the field data type is int, decimal, or float. Or varchar. ;-) > > And, for example, if you use aliases in your SQL query (e.g. > authorlastname vs au_lname in the database, ADO has to keep track of the > original name to construct UPDATE statements. And ensure the database > schema hasn't changed since you did the SELECT. > > sp_tables_rowsets gets catalog, schema, and table name, useful for > creating UPDATE statements. It has a "(schema) last updated" column also. > You indicated it should create UPDATE statements: > .Properties("Update Criteria") = ADODB.adCriteriaKey > > They could cache some of this, but need to ensure its up-to-date, as the > client-side recordset doesn't lock data or metadata in the DBMS itself. If > the DBA issues: > ALTER TABLE foo ALTER COLUMN... in between your SELECT and your UPDATE, > ADO needs to "know", because it doesn't want to generate statements that > fail. > > Cheers, > Bob Beauchemin > http://www.SQLskills.com/blogs/bobb > > > > > "dragooon" <dragooon@newsgroups.nospam> wrote in message > news:eay67mnuGHA.4544@TK2MSFTNGP04.phx.gbl... >>I have two Ado recordsets in the program as follows: >> >> For recordset 1: >> >> .CursorLocation = adUseClient >> .CursorType = adOpenDynamic >> .LockType = adLockOptimistic >> .Properties("Initial Fetch Size") = 10 >> .Properties("Update Criteria") = ADODB.adCriteriaKey >> .CacheSize = 10 >> .Open , , , , adAsyncFetch >> >> For recordset 2: >> >> .CursorLocation = adUseClient >> .CursorType = adOpenKeyset >> .LockType = adLockOptimistic >> .Open >> >> Any idea? I don't think any of the above properties should trigger those >> weird stored procedures... >> >> Thanks! >> >> "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message >> news:uXCgqekuGHA.4460@TK2MSFTNGP04.phx.gbl... >>> The functions you are referring to obtain database metadata. IIRC they >>> are used when you use ADO client-side Recordsets to perform updates; ADO >>> is using these to query the database metadata and construct a SQL update >>> statement. They may used elsewhere in ADO, perhaps even when querying >>> data, depending on the options selected. >>> >>> What does your ADO code look like? >>> >>> Cheers, >>> Bob Beauchemin >>> http://www.SQLskills.com/blogs/bobb >>> >>> >>> >>> "dragooon" <dragooon@newsgroups.nospam> wrote in message >>> news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... >>>> Erland - Thanks for your reply. Unfortunately it is not that easy to >>>> just switch everything to stored procedures (otherwise what's the point >>>> to use ADO?). Right now what I am looking for is, why are those stored >>>> procedures executed? what is triggering those stored procedures at the >>>> background? By know more the background activities, I hope we can fix >>>> this by maybe changing some properties or queries. >>>> >>>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>> news:Xns98176F8A1C26FYazorman@127.0.0.1... >>>>> dragooon (dragooon@newsgroups.nospam) writes: >>>>>> sp_provider_types_rowset and some other stored procedures keep being >>>>>> executed >>>>>> >>>>>> We recently experienced a very interesting problem. When connecting >>>>>> to SQL >>>>>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we >>>>>> can see >>>>>> the following stored procedures kept being executed every we submit >>>>>> an >>>>>> update query. This is what we saw in the tracing file: >>>>>> >>>>>> ------------------------------------------------------------------------- >>>>>> ... >>>>>> [submit an update query and maybe some other querys] >>>>>> exec sp_provider_types_rowset NULL, NULL >>>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>> [submit an update query and maybe some other querys] >>>>>> exec sp_provider_types_rowset NULL, NULL >>>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>> ... >>>>>> ------------------------------------------------------------------------ >>>>>> >>>>>> What may be causing those stored procedured to be exectued all the >>>>>> time? >>>>>> There is little docmentation on those stored procedures anywhere...:( >>>>> >>>>> ADO performs a lot of queries behind your back, and is not always that >>>>> effective. >>>>> >>>>> Personally, I much prefer using stored procedures for updates. >>>>> >>>>> >>>>> -- >>>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>> >>>>> Books Online for SQL Server 2005 at >>>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>>> Books Online for SQL Server 2000 at >>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>> >>>> >>> >>> >> >> > > Hi Jesse,
Agreed, on the fact that providers might work differently in different versions. ADO ships with MDAC which (until SQL Server 2005) always shipped the latest version of SQLOLEDB (OLE DB provider for SQL Server) and others along with MDAC. I'm not sure you can "stifle" execution of those procedures completely, if that's how they provide metadata "in the large". If you're not doing updates using the disconnected rowset/recordset, then so don't need that property on the connect string. Cheers, Bob Beauchemin http://www.SQLskills.com/blogs/bobb Show quote "dragooon" <dragooon@newsgroups.nospam> wrote in message news:u9UYvOvuGHA.1436@TK2MSFTNGP02.phx.gbl... > Hi Bob - > > Thanks a lot for the explanation...the weird thing is, I have two versions > of the same application, both uses the same recordsets (with > properties("Update Criteria")=Adodb.adCriteriaKey). The only difference is > that they were compiled with different dlls (maybe different Ado version > too). The old version has this problem while the new one does not. So it > seems like I can not blame the properties("Update > Criteria")=Adodb.adCriteriaKey? > > Anyway, thanks for your help, I will take it out and give it a try. > > Jesse Chen > > > "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message > news:elXlG1nuGHA.1272@TK2MSFTNGP05.phx.gbl... >> Hmmm... not so weird. ADO is a generalized client API that doesn't assume >> (ie by having it hardcoded in the provider) what database or version of >> the database its working with. These stored procedures retrieve the >> metadata ADO and OLE DB (ADO is a library layered over OLE DB) needs. You >> can almost do a one-to-one correspondence. >> >> sp_provider_types_rowset and sp_columns_rowset are used to populate OLE >> DB interfaces IColumnsInfo and IColumnsRowset which is used to expose >> Properties in the ADO Field object. It has things like data type and >> column size. >> So if you say: rs.Fields.Field(1) = 300 >> it knows of the field data type is int, decimal, or float. Or varchar. >> ;-) >> >> And, for example, if you use aliases in your SQL query (e.g. >> authorlastname vs au_lname in the database, ADO has to keep track of the >> original name to construct UPDATE statements. And ensure the database >> schema hasn't changed since you did the SELECT. >> >> sp_tables_rowsets gets catalog, schema, and table name, useful for >> creating UPDATE statements. It has a "(schema) last updated" column also. >> You indicated it should create UPDATE statements: >> .Properties("Update Criteria") = ADODB.adCriteriaKey >> >> They could cache some of this, but need to ensure its up-to-date, as the >> client-side recordset doesn't lock data or metadata in the DBMS itself. >> If the DBA issues: >> ALTER TABLE foo ALTER COLUMN... in between your SELECT and your UPDATE, >> ADO needs to "know", because it doesn't want to generate statements that >> fail. >> >> Cheers, >> Bob Beauchemin >> http://www.SQLskills.com/blogs/bobb >> >> >> >> >> "dragooon" <dragooon@newsgroups.nospam> wrote in message >> news:eay67mnuGHA.4544@TK2MSFTNGP04.phx.gbl... >>>I have two Ado recordsets in the program as follows: >>> >>> For recordset 1: >>> >>> .CursorLocation = adUseClient >>> .CursorType = adOpenDynamic >>> .LockType = adLockOptimistic >>> .Properties("Initial Fetch Size") = 10 >>> .Properties("Update Criteria") = ADODB.adCriteriaKey >>> .CacheSize = 10 >>> .Open , , , , adAsyncFetch >>> >>> For recordset 2: >>> >>> .CursorLocation = adUseClient >>> .CursorType = adOpenKeyset >>> .LockType = adLockOptimistic >>> .Open >>> >>> Any idea? I don't think any of the above properties should trigger those >>> weird stored procedures... >>> >>> Thanks! >>> >>> "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message >>> news:uXCgqekuGHA.4460@TK2MSFTNGP04.phx.gbl... >>>> The functions you are referring to obtain database metadata. IIRC they >>>> are used when you use ADO client-side Recordsets to perform updates; >>>> ADO is using these to query the database metadata and construct a SQL >>>> update statement. They may used elsewhere in ADO, perhaps even when >>>> querying data, depending on the options selected. >>>> >>>> What does your ADO code look like? >>>> >>>> Cheers, >>>> Bob Beauchemin >>>> http://www.SQLskills.com/blogs/bobb >>>> >>>> >>>> >>>> "dragooon" <dragooon@newsgroups.nospam> wrote in message >>>> news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... >>>>> Erland - Thanks for your reply. Unfortunately it is not that easy to >>>>> just switch everything to stored procedures (otherwise what's the >>>>> point to use ADO?). Right now what I am looking for is, why are those >>>>> stored procedures executed? what is triggering those stored procedures >>>>> at the background? By know more the background activities, I hope we >>>>> can fix this by maybe changing some properties or queries. >>>>> >>>>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>>> news:Xns98176F8A1C26FYazorman@127.0.0.1... >>>>>> dragooon (dragooon@newsgroups.nospam) writes: >>>>>>> sp_provider_types_rowset and some other stored procedures keep being >>>>>>> executed >>>>>>> >>>>>>> We recently experienced a very interesting problem. When connecting >>>>>>> to SQL >>>>>>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we >>>>>>> can see >>>>>>> the following stored procedures kept being executed every we submit >>>>>>> an >>>>>>> update query. This is what we saw in the tracing file: >>>>>>> >>>>>>> ------------------------------------------------------------------------- >>>>>>> ... >>>>>>> [submit an update query and maybe some other querys] >>>>>>> exec sp_provider_types_rowset NULL, NULL >>>>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>>> [submit an update query and maybe some other querys] >>>>>>> exec sp_provider_types_rowset NULL, NULL >>>>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>>>> ... >>>>>>> ------------------------------------------------------------------------ >>>>>>> >>>>>>> What may be causing those stored procedured to be exectued all the >>>>>>> time? >>>>>>> There is little docmentation on those stored procedures >>>>>>> anywhere...:( >>>>>> >>>>>> ADO performs a lot of queries behind your back, and is not always >>>>>> that >>>>>> effective. >>>>>> >>>>>> Personally, I much prefer using stored procedures for updates. >>>>>> >>>>>> >>>>>> -- >>>>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>>> >>>>>> Books Online for SQL Server 2005 at >>>>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>>>> Books Online for SQL Server 2000 at >>>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > One more comment on this. All databases provide metadata on all rowsets by
default without executing any weird procedures. SQL Server provides one "describe packet" per column (allows query analyzer to name the columns, for example). But the information is sparse in comparison to what ADO needs; describe packets do not, for example, distinguish whether or not a column is a foreign key, nor the (table) source or every column in a 4-table join, as two examples that come to mind. You need these if the client API will create UPDATE statements. Cheers, Bob Show quote "dragooon" <dragooon@newsgroups.nospam> wrote in message news:eay67mnuGHA.4544@TK2MSFTNGP04.phx.gbl... >I have two Ado recordsets in the program as follows: > > For recordset 1: > > .CursorLocation = adUseClient > .CursorType = adOpenDynamic > .LockType = adLockOptimistic > .Properties("Initial Fetch Size") = 10 > .Properties("Update Criteria") = ADODB.adCriteriaKey > .CacheSize = 10 > .Open , , , , adAsyncFetch > > For recordset 2: > > .CursorLocation = adUseClient > .CursorType = adOpenKeyset > .LockType = adLockOptimistic > .Open > > Any idea? I don't think any of the above properties should trigger those > weird stored procedures... > > Thanks! > > "Bob Beauchemin" <bobb_no_spam@SQLskills.com> wrote in message > news:uXCgqekuGHA.4460@TK2MSFTNGP04.phx.gbl... >> The functions you are referring to obtain database metadata. IIRC they >> are used when you use ADO client-side Recordsets to perform updates; ADO >> is using these to query the database metadata and construct a SQL update >> statement. They may used elsewhere in ADO, perhaps even when querying >> data, depending on the options selected. >> >> What does your ADO code look like? >> >> Cheers, >> Bob Beauchemin >> http://www.SQLskills.com/blogs/bobb >> >> >> >> "dragooon" <dragooon@newsgroups.nospam> wrote in message >> news:u39tQljuGHA.1852@TK2MSFTNGP04.phx.gbl... >>> Erland - Thanks for your reply. Unfortunately it is not that easy to >>> just switch everything to stored procedures (otherwise what's the point >>> to use ADO?). Right now what I am looking for is, why are those stored >>> procedures executed? what is triggering those stored procedures at the >>> background? By know more the background activities, I hope we can fix >>> this by maybe changing some properties or queries. >>> >>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>> news:Xns98176F8A1C26FYazorman@127.0.0.1... >>>> dragooon (dragooon@newsgroups.nospam) writes: >>>>> sp_provider_types_rowset and some other stored procedures keep being >>>>> executed >>>>> >>>>> We recently experienced a very interesting problem. When connecting to >>>>> SQL >>>>> Server 2000 (SP4) using ADO, after the recordset is retrieved, we can >>>>> see >>>>> the following stored procedures kept being executed every we submit an >>>>> update query. This is what we saw in the tracing file: >>>>> >>>>> ------------------------------------------------------------------------- >>>>> ... >>>>> [submit an update query and maybe some other querys] >>>>> exec sp_provider_types_rowset NULL, NULL >>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> [submit an update query and maybe some other querys] >>>>> exec sp_provider_types_rowset NULL, NULL >>>>> exec [dbname]..sp_tables_rowset;2 NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> exec [dbname]..sp_columns_rowset N'tablename', NULL, NULL >>>>> ... >>>>> ------------------------------------------------------------------------ >>>>> >>>>> What may be causing those stored procedured to be exectued all the >>>>> time? >>>>> There is little docmentation on those stored procedures anywhere...:( >>>> >>>> ADO performs a lot of queries behind your back, and is not always that >>>> effective. >>>> >>>> Personally, I much prefer using stored procedures for updates. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>> >>>> Books Online for SQL Server 2005 at >>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>> Books Online for SQL Server 2000 at >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >>> >> >> > > Hi dragooon,
This is a quick note to check whether or not this issue has been fixed. I would like to know the issue status and whether or not you need further research. Sincerely yours, Charles Wang Microsoft Online Community Support  |
|||||||||||||||||||||||
Other interesting topics