Help on ANSI

Hi all!!!!!!
My requirement is to write DDL and DML scripts which are ANSI complient, so that it can run on MS SQL server 7.0 & 2000, Oracle 8i & 9i, DB@. and Sybase.
So could U plz tell me where can I find ANSI Standards for this.Originally posted by sameer_mehta198
Hi all!!!!!!

My requirement is to write DDL and DML scripts which are ANSI complient, so that it can run on MS SQL server 7.0 & 2000, Oracle 8i & 9i, DB@. and Sybase.

So could U plz tell me where can I find ANSI Standards for this.

How about:

http://webstore.ansi.org/ansidocstore/default.asp

a little google will do ya...

Help needed on DDL Triggers in SQL server 2005

Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-LPerhaps you need
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME)
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Please post the DDL for DDLEVENTLOG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to create DDL trigger as below
ALTER TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
/*POSTTIME,
SPID,
SERVERNAME,
LOGINNAME,
DATABASENAME,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
TARGETOBJNAME,
TARGETOBJTYPE,
TSQLSTATEMENT,
FULLDATA*/
)
VALUES
(
CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
/*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
@.EventData*/
)
But when i execute this its throwing an error...
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Am i doing some thing here?
Please help
Thanks
-L|||Seems some of the XML and Xquery stuff is case sensitive. Below executed wit
h no errors on my
machine:
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.EventData XML
SET @.EventData = EVENTDATA()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
@.EventData.query('data(//EventType)')
--CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Learner" <pradev@.gmail.com> wrote in message
news:1139846190.438357.222750@.g43g2000cwa.googlegroups.com...
> Hello,
> I am trying to create DDL trigger as below
>
> ALTER TRIGGER DDLTRIGGER ON DATABASE
> FOR DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.EventData XML
> SET @.EventData = EVENTDATA()
> INSERT DDLEVENTLOG
> (EVENTTYPE
> /*POSTTIME,
> SPID,
> SERVERNAME,
> LOGINNAME,
> DATABASENAME,
> SCHEMANAME,
> OBJECTNAME,
> OBJECTTYPE,
> TARGETOBJNAME,
> TARGETOBJTYPE,
> TSQLSTATEMENT,
> FULLDATA*/
> )
> VALUES
> (
> CONVERT (NVARCHAR(100),@.EventData.Query('DATA(//EventType)'))
> /*CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//PostTime)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SPID)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SERVERNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//LOGINNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//DATABASENAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//SCHEMANAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//OBJECTTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJNAME)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//TARGETOBJTYPE)')),
> CONVERT (NVARCHAR(100),@.EVENTDATA.QUERY('DATA(//COMMANDTEXT)')),
> @.EventData*/
> )
> But when i execute this its throwing an error...
>
> Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
> "Query" is not a valid function, property, or field.
> Am i doing some thing here?
> Please help
> Thanks
> -L
>|||Uri,
Thanks for the quick reply . But :( no luck yet.
After changing the whole line looks like this
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)')) as
sysname)
but it said
Msg 1035, Level 15, State 10, Procedure DDLTRIGGER, Line 23
Incorrect syntax near 'cast', expected 'AS'.
Am i doing it right here?
Thanks
-L|||Thank you for the quick reply. But i still get the same error on my
machine.
if i run your sql as is i got this
Msg 257, Level 16, State 3, Procedure DDLTRIGGER, Line 6
Implicit conversion from data type xml to nvarchar is not allowed. Use
the CONVERT function to run this query.
and later i uncommented the Convert line and commented out the first
line then i got the same Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Do i need to setup any kind of option on my machine?
Thanks
-L|||You've misplaced the closing parentheses:
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
ML
http://milambda.blogspot.com/|||Looks like all of you have no problem running the above sql... But i
still get the same thig.
Here is the entire sql that i am trying to run
CREATE TRIGGER DDLTRIGGER ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventData XML
SET @.eventData = EventData()
INSERT DDLEVENTLOG
(EVENTTYPE
)
VALUES
(
CONVERT (NVARCHAR(100), cast(@.EventData.Query('Data(//EventType)') as
sysname))
)
Some thing wrong with it?
I still get the Query error
Msg 227, Level 15, State 1, Procedure DDLTRIGGER, Line 6
"Query" is not a valid function, property, or field.
Thanks
-L|||FWIW, I use @.EventData.Value, not @.EventData.Query. E.g. these work great
for me in a DDL auditing trigger:
@.eventdata.value('(/EVENT_INSTANCE/SPID)[1]','int')
@.eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
@.eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)')|||I got it solved the line, CONVERT
(NVARCHAR(100),@.EventData.query('data(//EventType)')) works!
It doesn't recognise @.EventData.Query but it does @.EventData.query..
I thank all of you for looking into my problem.
How ever i need one more help with dropping a trigger
when i run this sql, drop trigger ddltrigger
i got this message. Is it some kind of security issue? what property do
i need to set inorder to drop it?
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'ddltrigger', because it does not exist or you
do not have permission.
Thanks
-L

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>