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

ADO error: incorrect syntax near keyword

Hello,
I'm trying to change a stored procedure to add another parameter
(qaType) and take one of two execution paths based on its value (=1 or <>
1). When I try to save the changes using VS2003.NET, I keep getting this
error:
ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
spotting it, but I'm sure you guys will see it in a flash. Thanks!
*--Code begins here --*
ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
@.qaID int, @.qaType int
AS
IF (@.qaType <> 1)
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
(dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
ELSE
BEGIN
SELECT
dd.QADetailDetailID,
dd.QADetailID,
dd.DetailedFR,
dd.UnitQty,
dd.QtyPerUnit,
(dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
dd.MockUps,
dd.Spares,
dd.FabFallout,
dd.GFE,
dd.ITA,
dd.Other,
(((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
dd.Comments,
dd.ITAAmountPlanned,
dd.ITAAmountActual
FROM
tblQADetailDetail dd,
tblQADetail d
WHERE
dd.QADetailID = d.QADetailID AND
d.QAID = @.qaID
END
________________________________________
____________________________________
___
Posted Via Uncensored-News.Com - Accounts Starting At $6.95 - http://www.uncensore
d-news.com
<><><><><><><> The Worlds Uncensored News Source <><><><><><><><>> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
It looks like you have an extra open parenthesis. Try
((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
Hope this helps.
Dan Guzman
SQL Server MVP
"Spurious Logic" <spurs> wrote in message
news:420955b0$1_2@.news4.uncensored-news.com...
> Hello,
> I'm trying to change a stored procedure to add another parameter
> (qaType) and take one of two execution paths based on its value (=1 or <>
> 1). When I try to save the changes using VS2003.NET, I keep getting this
> error:
> ADO error: Incorrect syntax near the keyword 'AS'. I'm having trouble
> spotting it, but I'm sure you guys will see it in a flash. Thanks!
> *--Code begins here --*
> ALTER PROCEDURE dbo.frmQASELECTQADetailDetails
> @.qaID int, @.qaType int
> AS
> IF (@.qaType <> 1)
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + dd.MockUps + dd.Spares + dd.FabFallout) -
> (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
> ELSE
> BEGIN
> SELECT
> dd.QADetailDetailID,
> dd.QADetailID,
> dd.DetailedFR,
> dd.UnitQty,
> dd.QtyPerUnit,
> (dd.UnitQty * dd.QtyPerUnit) AS QtyReq,
> dd.MockUps,
> dd.Spares,
> dd.FabFallout,
> dd.GFE,
> dd.ITA,
> dd.Other,
> (((dd.UnitQty * dd.QtyPerUnit) + ((dd.MockUps + dd.Spares +
> dd.FabFallout)*dd.QtyPerUnit) - (dd.ITA + dd.GFE) + dd.Other) AS SubTotal,
> dd.Comments,
> dd.ITAAmountPlanned,
> dd.ITAAmountActual
> FROM
> tblQADetailDetail dd,
> tblQADetail d
> WHERE
> dd.QADetailID = d.QADetailID AND
> d.QAID = @.qaID
> END
>
> ________________________________________
__________________________________
_____
> Posted Via Uncensored-News.Com - Accounts Starting At $6.95 -
> http://www.uncensored-news.com
> <><><><><><><> The Worlds Uncensored News Source
> <><><><><><><><>
>

ADO error (syntax error or access violation)

I made a union all between two query's in I've built all of the queries in
query analyzer without any problem but in reportingssvcs I get a ADO error
(syntax error or access violation) Does any one know why?Can you give an example and repro steps for Northwind or AdventureWorks?
Send to me directly and I will try it.
--
| From: "Marco van de Kraats" <marcovdkNoSpam999@.dbs.nl>
| Subject: ADO error (syntax error or access violation)
| Date: Thu, 26 May 2005 13:56:40 +0200
| Lines: 6
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <O6Hr7neYFHA.2956@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 62.58.60.27
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:44797
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I made a union all between two query's in I've built all of the queries
in
| query analyzer without any problem but in reportingssvcs I get a ADO
error
| (syntax error or access violation) Does any one know why?
|
|
|
||||I am having the same problem where I get an "ADO error: syntax error or
access violation" when I try to run my query. Here is my query:
SELECT Table1.Name,
(SELECT Table2.Billings
WHERE Table2.Date >= CONVERT(datetime,
@.StartDate) AND Table2.Date <= CONVERT(datetime, @.EndDate))
AS Billings
FROM Table2 RIGHT OUTER JOIN
Table1 ON Table2.Name = Table1.Name
GROUP BY Table1.Name, Table2.Date, Table2.Billings
""Brad Syputa - MS"" wrote:
> Can you give an example and repro steps for Northwind or AdventureWorks?
> Send to me directly and I will try it.
> --
> | From: "Marco van de Kraats" <marcovdkNoSpam999@.dbs.nl>
> | Subject: ADO error (syntax error or access violation)
> | Date: Thu, 26 May 2005 13:56:40 +0200
> | Lines: 6
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <O6Hr7neYFHA.2956@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 62.58.60.27
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:44797
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I made a union all between two query's in I've built all of the queries
> in
> | query analyzer without any problem but in reportingssvcs I get a ADO
> error
> | (syntax error or access violation) Does any one know why?
> |
> |
> |
> |
>|||I found out that:
- if you use a parameter in the second query the error occurd if the quey is
complex(no example)
- if you use a parameter in both first and the second query the error occurd
if the quey is complex(no example)
- if you use a parameter in the first query or second and you wil use a
UNION ALL statement a error occurd
See the example's
On northwind WITH error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = @.orderID)
UNION ALL
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
On northwind WITH error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = @.orderID)
UNION ALL
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
WHERE (EmployeeID = @.orderID)
On northwind WITH error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = 1)
UNION ALL
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
WHERE (EmployeeID = @.orderID)
On northwind NO error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = @.orderID)
UNION
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
On northwind NO error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = 1)
UNION ALL
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
On northwind NO error
SELECT OrderDate, OrderID, 0 AS bel
FROM Orders
WHERE (OrderID = 1)
UNION
SELECT 2, 0 AS fiets, EmployeeID AS bel
FROM Employees
WHERE (EmployeeID = @.orderID)
I found out that if you use a parameter in the second query the error occurd
So for example
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:LVe3MZiZFHA.3928@.TK2MSFTNGXA01.phx.gbl...
> Can you give an example and repro steps for Northwind or AdventureWorks?
> Send to me directly and I will try it.
> --
> | From: "Marco van de Kraats" <marcovdkNoSpam999@.dbs.nl>
> | Subject: ADO error (syntax error or access violation)
> | Date: Thu, 26 May 2005 13:56:40 +0200
> | Lines: 6
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <O6Hr7neYFHA.2956@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 62.58.60.27
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:44797
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I made a union all between two query's in I've built all of the queries
> in
> | query analyzer without any problem but in reportingssvcs I get a ADO
> error
> | (syntax error or access violation) Does any one know why?
> |
> |
> |
> |
>

ADO 2.7 with sql server using oracle syntax

Hi,

I've been tasked with investigating how we can migrate from oracle to sql server. I have successfully converted a typical schema with 97% success using SSMA and have a query regarding ADO and oracle syntax.

I have heard that the SQL server provider can interpret oracle syntax into its own syntax, but i am unable to find any reference/examples on the web. Is this possible? and if so could any kind soul please post some links.

Thanks in advance,
Michael

The SQL Server provider (SQLOLEDB or SQLNCLI for SQL Server 2005) does not directly interpret Oracle SQL syntax if that's your question but you can get at Oracle data from SQL Server in a linked server scenario or by using the OPENROWSET T-SQL command. See the following links for more information

OPENROWSET: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Linked Servers: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

Regards,

Uwa.