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