executing a DDL statement

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

executing a DDL statement

Post by radub » Sat 10 Jul 2010 18:56

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
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Mon 12 Jul 2010 12:45

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

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Post by radub » Mon 12 Jul 2010 13:13

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
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Wed 14 Jul 2010 15:34

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
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Post by radub » Wed 14 Jul 2010 16:06

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
Posts: 20
Joined: Mon 30 Oct 2006 09:15
Location: Belgium

Re: executing a DDL statement

Post by rept » Mon 29 Dec 2014 09:48

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: executing a DDL statement

Post by azyk » Mon 29 Dec 2014 13:19

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
Posts: 20
Joined: Mon 30 Oct 2006 09:15
Location: Belgium

Re: executing a DDL statement

Post by rept » Tue 30 Dec 2014 09:40

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: executing a DDL statement

Post by azyk » Tue 30 Dec 2014 12:52

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

Post Reply