ADO.NET OleDb SET ROWCOUNT or DBPROP_MAXROWS

I am writing a data access tool that needs to be non provider specific. I have used System.Data.OleDb to access a variety of data source types (MSSQLServer, MSAccess, MSExcel, CSV, Oracle, XML). This works beautifully. Thankyou Microsoft for giving us ADO.NET2.0.

However, I now need to restrict the number of rows returned from a SELECT statement. Is there any way I can I achieve this in a non provider specific fashion?

So far searches have yielded DBPROP_MAXROWS however I can find no way of setting the DBProperties using ADO.NET2.0. Is this possible?

Documentation for DBPROP_MAXROWS states that it uses "SET ROWCOUNT n" as part of the command text. I tried this and it works but not for the JET4 provider (this is a problem since I use JET4 to access .mdb, .xls and .csv files)!

Any tips here would be greatly appreciated. Even if somebody were to tell me that it's just not possible, at least that would put me out of my misery :)

Thanks.

Because of the way JET accesses data, bringing all of the data to the client before processing the criteria, I don't think that there is a way to make that work.|||

Thanks for the reply Arnie,

since posting I have done some more research and discovered that using TOP works for Jet. This is good for me as it also works for SQL Server. The approach I have taken is as follows:

int maxRows = 100;

string someQry = "SELECT * FROM table1";

string topQry = "SELECT TOP " + maxRows.ToString() + " * FROM (" + someQry + ") as topSubQry";

However it doesn't work for XML files. To get data from XML files I use a connection string of "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6;" and then the full path to the XML file as the command text. I am guessing there will just be no way to limit the rows returned when using this provider.

ADO puts EXEC in front of my SQL statements?

Hi there,
I'm tracking down a syntax error and have an odd problem.
I'm using the SQL Trace tool to view SQL arriving at the server.
Now, there's two problems...
SQL Server keeps on putting EXEC in front of my statements -
eg:
ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
is changed to
exec ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
And the second problem is that any cursor/dataset is created like:
declare @.p1 int
set @.p1=2
exec sp_prepexec @.p1 output,NULL,N'<my statement>'
select @.p1
Does anyone have an idea why?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comBecause you are embedding dynamic SQL. Best practice is to put your
code into stored procs and call the procs from ADO.
David Portas
SQL Server MVP
--|||Hello David,

> Because you are embedding dynamic SQL. Best practice is to put your
> code into stored procs and call the procs from ADO.
In this case, not an option :-)
Funny, ad-hoc queries in SQL Server Management Studio or in the
Query Analyzer don't "suffer" from this problem.
Why is that?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Query Analyzer uses ODBC, not ADO, that's why :)
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
> Hello David,
>
> In this case, not an option :-)
> Funny, ad-hoc queries in SQL Server Management Studio or in the
> Query Analyzer don't "suffer" from this problem.
> Why is that?
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>|||
> Query Analyzer uses ODBC, not ADO, that's why :)
Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
Ah well...
Anyway, how can I get rid of this EXEC stuff then?
With regards,
Martijn Tonies

> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
SQL
>|||Just to make sure that we are talking about the same thing: you are
using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
or something else ?
Razvan|||> Just to make sure that we are talking about the same thing: you are
> using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
> or something else ?
No ADO.NET, just plain old ADO.
SQL 2000 and 2005, for that matter.
Haven't tried SQL 7 on this particular thingy yet...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||I'm not an ADO expert, but IIRC there is a method you can call on the
connection (or command) that just sends the SQL to the server with adding
the EXEC stuff. Which one that is you'll have to look up in the ADO
documentation or ask on an ado newsgroup. It might be the CommandType. You
can try setting the CommandType to adCmdText.
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:OnYpjrayFHA.460@.TK2MSFTNGP15.phx.gbl...
>
> Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
> Ah well...
> Anyway, how can I get rid of this EXEC stuff then?
>
> --
> With regards,
> Martijn Tonies
>
> SQL
>|||
> I'm not an ADO expert, but IIRC there is a method you can call on the
> connection (or command) that just sends the SQL to the server with adding
> the EXEC stuff. Which one that is you'll have to look up in the ADO
> documentation or ask on an ado newsgroup. It might be the CommandType. You
> can try setting the CommandType to adCmdText.
It's already adCmdText :-/
I'll have a look at the docs then ... couldn't find it before ...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Hello, Martijn
In SQL Server 2000, "ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE"
is not a valid statement. Try a "ALTER TABLE YourTable ENABLE TRIGGER
SomeNormalTrigger" and it should work. If ADO doesn't recognize your
CommandText as being a valid SQL statement, it tries to add an "exec "
or a "select * from " guessing that it could be a procedure name or a
table/view name.
In SQL Server 2005, that statement should work, but it seems that there
is a bug in the "Microsoft OLEDB Provider for SQL Server" (or it is not
updated for SQL Server 2005). Try using the "SQL Native Client" OLEDB
provider (i.e. "Provider=SQLNCLI.1", instead of "Provider=SQLOLEDB.1"
in the connection string). It worked on my system (using SQL Server
2005 September CTP).
Razvan

Administrating roles on several databases and cubes ?

Anyone know of somekind of tool, where you easy can administrate userroles on several databases and cubes ?

No. I would appreciate a product that could do that. Roles are related to solutions/databases not across solutions/databases.

When Active Directoryn was introduced in Windows 2000 my naive idea was that each application would build its security repository around AD. This is not the case nore for MS-products.

Regards

Thomas Ivarsson

admin tool for MSDE?

What admin tools are developers using for MSDE applications? If SQL Server
Enterprise Manager is unavailable and the Web Access tool is just too
clunky, what else is popular?
Thanks,
Bill
Cincinnati,OH USA
My favourite (if EM is not applicable) is QALite which is a really cool tool
for scripting and other things, just try this.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
> What admin tools are developers using for MSDE applications? If SQL Server
> Enterprise Manager is unavailable and the Web Access tool is just too
> clunky, what else is popular?
> Thanks,
> Bill
> Cincinnati,OH USA
>
|||Hi,
DBARTISAN from embarcadero is also really good for administering SQL Server.
http://www.embarcadero.com/products/dbartisan/
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OLBhrfQSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> My favourite (if EM is not applicable) is QALite which is a really cool
> tool for scripting and other things, just try this.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
> news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
>

admin tool for MSDE?

What admin tools are developers using for MSDE applications? If SQL Server
Enterprise Manager is unavailable and the Web Access tool is just too
clunky, what else is popular?
Thanks,
Bill
Cincinnati,OH USAMy favourite (if EM is not applicable) is QALite which is a really cool tool
for scripting and other things, just try this.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
> What admin tools are developers using for MSDE applications? If SQL Server
> Enterprise Manager is unavailable and the Web Access tool is just too
> clunky, what else is popular?
> Thanks,
> Bill
> Cincinnati,OH USA
>|||Hi,
DBARTISAN from embarcadero is also really good for administering SQL Server.
http://www.embarcadero.com/products/dbartisan/
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OLBhrfQSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> My favourite (if EM is not applicable) is QALite which is a really cool
> tool for scripting and other things, just try this.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
> news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
>

admin tool for MSDE?

What admin tools are developers using for MSDE applications? If SQL Server
Enterprise Manager is unavailable and the Web Access tool is just too
clunky, what else is popular?
Thanks,
Bill
Cincinnati,OH USAMy favourite (if EM is not applicable) is QALite which is a really cool tool
for scripting and other things, just try this.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
> What admin tools are developers using for MSDE applications? If SQL Server
> Enterprise Manager is unavailable and the Web Access tool is just too
> clunky, what else is popular?
> Thanks,
> Bill
> Cincinnati,OH USA
>|||Hi,
DBARTISAN from embarcadero is also really good for administering SQL Server.
http://www.embarcadero.com/products/dbartisan/
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OLBhrfQSFHA.1268@.TK2MSFTNGP14.phx.gbl...
> My favourite (if EM is not applicable) is QALite which is a really cool
> tool for scripting and other things, just try this.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bill" <usenet@.spoofed.com> schrieb im Newsbeitrag
> news:7dc07$426bf7cd$d8c4ff3d$14512@.FUSE.NET...
>> What admin tools are developers using for MSDE applications? If SQL
>> Server
>> Enterprise Manager is unavailable and the Web Access tool is just too
>> clunky, what else is popular?
>> Thanks,
>> Bill
>> Cincinnati,OH USA
>>
>