Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl

trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for

ddl_database_level_events
as
--select

eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post

Time'
from

@.data.nodes('/EVENT_INSTANCE')

as EventsTable(Col)
go

Thank you in advance,

Bishoy

Your XPATH expression for the value method is incorrect. You can do one of the following:

select
Col.value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

-- or

select
Col.value('(./EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(./PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

See Books Online EVENTDATA topics for more examples. And check out the XQuery documentation also.

sql

Question about querying xml returned by eventdata() function in ddl trigger

Hi All,

I wanted to query the xml returned by the eventdata() function in a ddl trigger to view it in result set.

I made that code but it returned null, any help please?

createtrigger DatabaseEvents
ondatabase
for ddl_database_level_events
as
--select eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @.data xml
select @.data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)')as'Event Type'
,Col.value('(/PostTime/text())[1]','datetime')as'Post Time'
from @.data.nodes('/EVENT_INSTANCE')as EventsTable(Col)
go

Thank you in advance,

Bishoy

The path expressions in the value methods need to be relative path expressions from the node...

Try:

select
Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go

Question about querying database with select statement in SQL Server 2005

In the sql server 2005, why do I need to perform querying as followed
"SELECT * FROM Productiion.Product"
Why do i need to stick the prefix Production to the table named
"product" in Adventurework sample database?
Any idea would be appreciate.
Optimus wrote:
> In the sql server 2005, why do I need to perform querying as followed
> "SELECT * FROM Productiion.Product"
> Why do i need to stick the prefix Production to the table named
> "product" in Adventurework sample database?
> Any idea would be appreciate.
SQL Server 2005 uses the concept of schemas. That is, objects are owned
by schemas, and not users as they have been in the past. The separation
is beneficial. See "User-Schema Separation" in the SQL 2005 BOL for more
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Question about querying database with select statement in SQL Server 2005

In the sql server 2005, why do I need to perform querying as followed
"SELECT * FROM Productiion.Product"
Why do i need to stick the prefix Production to the table named
"product" in Adventurework sample database?
Any idea would be appreciate.Optimus wrote:
> In the sql server 2005, why do I need to perform querying as followed
> "SELECT * FROM Productiion.Product"
> Why do i need to stick the prefix Production to the table named
> "product" in Adventurework sample database?
> Any idea would be appreciate.
SQL Server 2005 uses the concept of schemas. That is, objects are owned
by schemas, and not users as they have been in the past. The separation
is beneficial. See "User-Schema Separation" in the SQL 2005 BOL for more
information.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Question about querying database with select statement in SQL Server 2005

In the sql server 2005, why do I need to perform querying as followed
"SELECT * FROM Productiion.Product"
Why do i need to stick the prefix Production to the table named
"product" in Adventurework sample database?
Any idea would be appreciate.Optimus wrote:
> In the sql server 2005, why do I need to perform querying as followed
> "SELECT * FROM Productiion.Product"
> Why do i need to stick the prefix Production to the table named
> "product" in Adventurework sample database?
> Any idea would be appreciate.
SQL Server 2005 uses the concept of schemas. That is, objects are owned
by schemas, and not users as they have been in the past. The separation
is beneficial. See "User-Schema Separation" in the SQL 2005 BOL for more
information.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>

Question about Querying by "for xml auto" and retriving xml by

Thanks very much for reply. Actually, I trid a same page (same result of
query) on two boxes. It worked for one but failed on another. Is it possible
that different system handles NULL value differently?
Ruopian
"Jay Nathan" wrote:

> I don't know what line that this is failing on, but if I had to guess, I
> think it would be in this area:
>
> cart.Attributes(0).Value.ToString & "</cart>"
> You probably have some NULL values and therefore aren't bringing back the
> attribute value that you are trying to retrive here:
> "cart.Attributes(0).Value". I would check the results of the query first.
> Jay Nathan
> http://www.jaynathan.com/blog
>
>
> "Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
> news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> several
> and
> auto"
> result.
> cart.Attributes(0).Value.ToString
>
>Are both of the boxes connecting to the same data source (not to be too
simple minded, here...)? If so, you could have a NULL in one db and not the
other. The functionality of the DOM shouldn't be any different from machine
to machine.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:2A522413-E03D-48FD-A14A-DF1255FFBB12@.microsoft.com...
> Thanks very much for reply. Actually, I trid a same page (same result of
> query) on two boxes. It worked for one but failed on another. Is it
possible
> that different system handles NULL value differently?
> Ruopian
> "Jay Nathan" wrote:
>
the
first.
an
2003,
pwd=pwd;
xml|||yes, they are connecting to the same database. Actually, they are two server
s
of a cluster. Can you explaine why I could have a NULL in one server and not
the other by querying the same db? Thanks a lot!
Ruopian
"Jay Nathan" wrote:

> Are both of the boxes connecting to the same data source (not to be too
> simple minded, here...)? If so, you could have a NULL in one db and not th
e
> other. The functionality of the DOM shouldn't be any different from machin
e
> to machine.
> --
> Jay Nathan
> http://www.jaynathan.com/blog
>
> "Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
> news:2A522413-E03D-48FD-A14A-DF1255FFBB12@.microsoft.com...
> possible
> the
> first.
> an
> 2003,
> pwd=pwd;
> xml
>
>|||Actually, if you were hitting the same db with the same query, then you
shouldn't have different values. Have you tried to run the queries by
returning a regular resultset, not in XML format? On either machine? My gut
is that you have a NULL value coming back somewhere, you just need to find
it, or handle it gracefully as you are looping through the DOM.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:4002B56E-9884-488F-B055-8F8B7D377892@.microsoft.com...
> yes, they are connecting to the same database. Actually, they are two
servers
> of a cluster. Can you explaine why I could have a NULL in one server and
not
> the other by querying the same db? Thanks a lot!
> Ruopian
> "Jay Nathan" wrote:
>
the
machine
of
guess, I
back
crashes
of
for
the

Question about querying across servers

Hi,
We have 8 sites running each one a SQL Server.
Clients can connect to any of these sites to get information.
Now we want to have an 'extra site'. Clients that connect to this site
will be able to get information from the previous 8 sites. I mean, this
extra site, will contain all the data of the other 8 sites.
Ex.
Site 1 has a table called 'Persons' with a record called 'John'
Site 3 has a table called 'Persons' with a record 'Peter'
If I query for all persons on site 1, I get 'John'
However, If I query for all persons in that extra site, I get 'John' and
'Peter'
1. What is the best way to do this? I have been looking at linked
servers but it's not clear to me... I'm not sure if this is a solution.
2. Should I transfer all the data phisically from Site1->Extra Site,
Site2->Extra site... ?
3. On the other hand, how do I distinguish primary keys? Let's suppose that
primary key for 'John' on site 1 is 1000, and 'Peter' on site 3 is also
1000. Could this cause some problems?
Thanks a lot.Will the New sites will be added on a periodic basis , or the developer is
adding settings for new sites ?
U can use linked servers for this process than replication
first register the link server , then in queries use the
linkserver..dbname.table for querying
thanks
"Star" wrote:

> Hi,
> We have 8 sites running each one a SQL Server.
> Clients can connect to any of these sites to get information.
> Now we want to have an 'extra site'. Clients that connect to this site
> will be able to get information from the previous 8 sites. I mean, this
> extra site, will contain all the data of the other 8 sites.
> Ex.
> Site 1 has a table called 'Persons' with a record called 'John'
> Site 3 has a table called 'Persons' with a record 'Peter'
> If I query for all persons on site 1, I get 'John'
> However, If I query for all persons in that extra site, I get 'John' and
> 'Peter'
> 1. What is the best way to do this? I have been looking at linked
> servers but it's not clear to me... I'm not sure if this is a solution.
> 2. Should I transfer all the data phisically from Site1->Extra Site,
> Site2->Extra site... ?
> 3. On the other hand, how do I distinguish primary keys? Let's suppose tha
t
> primary key for 'John' on site 1 is 1000, and 'Peter' on site 3 is also
> 1000. Could this cause some problems?
> Thanks a lot.
>

Question about encryption and password protection

Howdy!

Couple quick questions:

1) How much does setting encryption cost in terms of speed of querying and cpu usage?
2) If encryption is not set, is the password "encrypted" when its sent with the initialization routine?

Thanks in advance!

1: Most agree on a 10 - 20 % performance hit (on devices), it is probably not noticeable on a desktop.

2: If a password is specifed, you are always using encryption

Password-or-Pwd-or-database password-or-ssceatabase password

The database password, which can be up to 40 characters in length. If not specified, the default value is no password. This property is required if you enable encryption on the database. If you specify a password, encryption is automatically enabled on the database.

|||Thanks for the reply!