executing a DDL statement

[radub]

Hello,

I have the D6 with SDAC trial version on XP SP3. I have the following SQL statement:
"disable trigger AutoInc_CUSTOMCODE on CUSTOMCODE"
which I put it in an TMSQuery and I called ExecSQL procedure.

At run time I receive an error from MSSQL because the statement was suffixed with the word "exec", although in a DML statement this word doesn't appear.

Any suggestion?

Thank you.

[Challenger]

SDAC also does not insert this word. Please give a more detailed desciption of you problem and specify the text of the exception.

[radub]

These are the last 4 statements from SQL PROFILER:

select sFk.NAME from sys.FOREIGN_KEYS sFk join sys.TABLES st on st.OBJECT_ID=sFk.PARENT_OBJECT_ID and st.NAME='CUSTOMCODE'
go
alter table CUSTOMCODE nocheck constraint FK_CUSTOMCO_R_CODEUSE_APPUSER
go
select stg.NAME from sys.TRIGGERS stg join sys.TABLES st on st.OBJECT_ID=stg.PARENT_ID and st.NAME='CUSTOMCODE'
go
exec disable trigger AutoInc_CUSTOMCODE on CUSTOMCODE
go

and the error is "Incorrect syntax near the keyword 'trigger'." and appears before the last statement.

The statement is "disable trigger AutoInc_CUSTOMCODE on CUSTOMCODE" and obviously, I have put a breakpoint before issuing executing the statement to recheck the text, and it is without the word exec.

But SDAC does put "exec" word in front of some statements as is seen below (also from SQL PROFILER):

exec [sf_elbi].[sys].sp_tables_info_90_rowset N'USERACCESS',N'dbo',NULL
go
exec [sf_elbi].[sys].sp_columns_90_rowset N'USERACCESS',N'dbo',NULL
go
exec [sf_elbi].[sys].sp_indexes_90_rowset N'USERACCESS',NULL,N'dbo'
go
exec [sf_elbi].[sys].sp_check_constbytable_rowset N'USERACCESS',N'dbo',NULL,NULL
go



Thank you

[Challenger]

We have reproduced this problem. SDAC does not insert this word. It is inserted by OLEDB provider. There is no problem with Native Client. Now we are investigating this problem. As soon as we get results, we will let you know.

[radub]

Thank you!
I have changed the value of Provider from prSQL to prNativeClient and it worked. Although I don't quite understand the meaning of this flag, I hope no other problems will occur at runt time.

[rept]

I'm having the exact same problem. I update a database through the script component and on some installation I'm getting: SQL execute error: EMSError Error: Incorrect syntax near the keyword 'TRIGGER'. Statement: ENABLE TRIGGER ALL ON dbo.ModLink

Setting it for all the clients to native isn't an option.

[azyk]

We couldn't reproduce the problem. Please send a script reproducing the problem (including the scripts for creating database objects involved in the above script) to andreyz*devart*com .

[rept]

Sorry that's impossible. This is a script containing thousands of lines. The relevant part however is very simple, it's just a:

ENABLE TRIGGER ALL ON dbo.Invoices

command.

On 95% of our installations it works, but on some it doesn't. I haven't been able to figure out if it's related to a certain SQL server, but I suppose it has to do with a certain OLE DB installation.

[azyk]

To solve the problem, try using SQL Native Client provider. For this, in the connection options set the TMSConnection.Options.Provider option to 'prNativeClient'.