How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

How to get the value of script of various SQLDMO(version 8.0) objects

Hello,
I am working on a project where I want to access and modify some objects of Microsoft SQL 2000 database.
Would someone help me how to do this? I need following informations;
- Script of the checks of a table
- Script of the Indexes of a table
- Script of Key object(Primary or Forign key)
- Script of Permissions of View and Stored procedure

The following code returns no result;

MyTabelClass mytabelClass = new MyTabelClass();

mytabelClass.Naam = table.Name;

mytabelClass.ChecksScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

mytabelClass.IndexesScript = table.Script(SQLDMO_SCRIPT_TYPE.SQLDMOScript_Indexes,string.Empty,string.Empty,SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default);

Thanks in advance.
With regards,

Your script is incorrect. Instead of accessing an existing object, you create a new one :

MyTabelClass mytabelClass = new MyTabelClass(); create a new instance.

See this sample:

Dim svr As New SQLServer
svr.Name = "."
svr.LoginSecure = True
svr.Connect

Dim t As Table

Set t = svr.Databases("pubs").Tables("authors")

MsgBox t.Script(SQLDMOScript_DRI_Checks, "", "", SQLDMOScript2_Default)
MsgBox t.Script(SQLDMOScript_Indexes, "", "", SQLDMOScript2_Default)

Groeten/Greetings

how to get the rowNumber in a matrix?

I am working with reporting services recently.

when using a matrix, I want to get the row number to dynamically set the row color.

but i can not get it by using the rownumber function,

can anyone help me with it?

thank you very much.

The row number doesn't really help you in a matrix because you are always dealing with grouped data in a matrix. You have to follow the approach discussed in the following blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

How to get the result of an Exec (@Sql) into a temp table.

Hi all.
I am working on some crosstab logic and needs to get my result into a
temporay table for further use and joins later in the prosedyre. It is
dynamic crosstabs so I don't know the number of columns on beforehand. After
my logic I can get a result that looks nice in QueryAnalyzer usning the Exec
command.
EXEC (@.sql)
What I want i for that result to get into a ad hock created temp table for
further use and joins. Just like
INSERT Col1 INTO #tmpTable FROM Tablename
Looks like the Exec command runs in another "space" so I can't reache the
#tmpTable even if my @.sql is correct with the INTO clause. If I print the
SQL, copies it and runs it it works fine ofcause.
Any ideas
thanx all
geirTry creating your temp table outside the Exec statement.
create table #mytemp
(
a int,
b int
)
Exec('insert ... into #mytemp')
select * from #mytemp
"Geir Holme" <geir@.multicase.no> wrote in message
news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand.
After
> my logic I can get a result that looks nice in QueryAnalyzer usning the
Exec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
> Any ideas
> thanx all
> geir
>|||Hi Jonny.
This works fine as long as you know the number of columns AND the name of
the columns. Since I am inserting a dynamic crosstab i don't know the name
of the columns and the number of columns. That's the big issue here.
Thank you for your interest so far. Mabe you have some more ideas?
regards
geir
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
> Try creating your temp table outside the Exec statement.
> create table #mytemp
> (
> a int,
> b int
> )
> Exec('insert ... into #mytemp')
> select * from #mytemp
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> After
> Exec
>|||Perhaps create a physical table in tempdb and then drop it when not needed.
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
the
the
>|||If you are doing a dynamic query like this, you will have to create a
dynamic temporary table. Consider building a permanent table in tempdb,
using a guid for the table name.
declare @.tableName varchar(40)
set @.tableName = newid()
declare @.query varchar(1000)
set @.query = 'create table tempdb..[' + @.tableName + '] ( column1
varchar(10))'
exec (@.query)
exec ('insert into tempdb..[' + @.tablename + '] values (''hello'') ')
exec ('select * from tempdb..[' + @.tablename + ']')
exec ('drop table tempdb..[' + @.tablename + ']')
Ugly, but it will work. You could also use select into instead of creating
the table. The most important thing is to use a permanent table. The guid
name of the table will ensure no name clashes.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
>|||Geir Holme wrote:
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand. Aft
er
> my logic I can get a result that looks nice in QueryAnalyzer usning the Ex
ec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps, instead of a procedure you'd like to try a function?
use Northwind
go
create FUNCTION udf_getOrders()
returns table
as
return(select top 100 * from orders)
go
select *
into #t
from dbo.udf_getOrders()
go
select * from #t
go
drop table #t
drop function dbo.udf_getOrders
go
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhv1KoechKqOuFEgEQLZcwCgyqhNQjMg+vPO
HFdAUtIi/AFH/5AAoJcq
LOei0tjH80SMhUbTd+uGEPfh
=EhCr
--END PGP SIGNATURE--

How to get the description ?

Hi,
I have a SQL that is working fine:

SELECT DISTINCT T1.ATCkod FROM ATC_tot T1
JOIN ATC_tot T2 ON T2.ATCkod = T1.ATCkod and T2.Typ_lakemedel LIKE '%' + @.Kod2 + '%'
JOIN ATC_tot T3 on T3.ATCkod = T1.ATCkod AND T3.Typ_lakemedel LIKE '%' + @.Kod3 + '%'
WHERE T1.Typ_lakemedel = @.Kod
Now I need to have a description together with the ATCkod, tablename ATC columnnameATCdesc. ATC_tot.ATCkod =ATC.ATCkod. I have tried Inner join without success. Any good suggestions ...?

Note that if you do not specify a JOIN type, INNER is the default. I prefer to specify the JOIN type for clarity.
Doesn't this work?

SELECTDISTINCT
T1.ATCkod,
ATC.ATCdesc
FROM
ATC_tot T1
INNER JOIN
ATC_tot T2 ON T2.ATCkod = T1.ATCkod and T2.Typ_lakemedel LIKE '%' + @.Kod2 + '%'
INNER JOIN
ATC_tot T3 on T3.ATCkod = T1.ATCkod AND T3.Typ_lakemedel LIKE '%' + @.Kod3 + '%'
INNER JOIN
ATCkod ATC ON T1.ATCkod = ATC.ATCkod
WHERE
T1.Typ_lakemedel = @.Kod

sql

How to get the client IP Address in T-SQL ?

Hi,

My problem is -
I have a trigger for auditing the changes(insert/update/delete) in the database table.That is done and is working fine. But I need to have the client's IP address from where the changes are done. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL.

As I have checked many forums, I got that there is extended stored procedure in master database named xp_cmdshell which has xplog70.dll and when we execute this stored procedure with 'ipconfig' we can get the IP Address. But I do not need that in master database. I need that in my database say myDB.

So how to proceed further. I don't know whether to create extended SP which contains DLL or is there any other option.

Pls help
Thanks in advanceHave you tried calling the stored procedure* from within your "myDB"? :)|||See this the stored procedure code and call,

create Procedure sp_get_ip_address (@.ip varchar(40) out)
as
begin
Declare @.ipLine varchar(200)
Declare @.pos int
set nocount on
set @.ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @.ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@.ipLine,'***') != '***')
begin
set @.pos = CharIndex (':',@.ipLine,1);
set @.ip = rtrim(ltrim(substring (@.ipLine ,
@.pos + 1 ,
len (@.ipLine) - @.pos)))
end
drop table #temp
set nocount off
end
go

declare @.ip varchar(40)
exec sp_get_ip_address @.ip out
print @.ip

But this is in the master Database.If I do this same thing in my database say myDB it gives error that it do not have extended SP|||Try

Exec dbo.sp_get_ip_address @.ip out|||If we exec this stored procedure in the trigger, How to store the output in the table or in some variable. Since I have tried this way - Insert #temp exec master..xp_cmdshell 'ipconfig' which gives error saying that -' insert and execute statments cannot be nested'. And if I first create the table with a column and then write - select * into #temp from exec sp_get_my_ip_address @.ip out, it gives error as incorrect syntax near exec|||Let's just confirm the DBMS that we're using...
I'm guessing SQL Server 2000, but please correct me if I'm wrong.

I'll then move the thread to the appropriate topic.|||I am using SQL Server 2005|||This works in 2000 and 2005 and might be of some use to you - let me know how you get on.

DECLARE @.host varchar(255)
SET @.host = host_name()

CREATE TABLE #Results (
Results varchar(255)
)

DECLARE @.cmd varchar(260)
SET @.cmd = 'ping ' + @.host

INSERT INTO #Results
EXEC master..xp_cmdshell @.cmd

SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
, host_name() As [host_name()]
FROM #Results
WHERE Results LIKE 'Pinging%'

DROP TABLE #Results|||Now my problem is - if the client machine do not have host name assigned in that case how can we ping and get the IP ?|||You can't...
Back to your method it is then...

Try this

--DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
DROP TABLE myTable
END

--Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1 char(1)
, changed_by_ip char(15)
, changed_by_host char(15)
, datetime_changed datetime
)
GO

--Create trgger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON myTable
FOR insert, update
AS
DECLARE @.ipLine varchar(255)
DECLARE @.pos int
DECLARE @.ip char(15)

--temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)

--Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'

--find the line which contains the IP and assign it to a variable
SET @.ipLine = (
SELECT ipLine
FROM #ip
WHERE ipLine LIKE '%IP Address%'
)

--If the IP is known
IF Coalesce(@.ipLine, '***') <> '***' BEGIN
--Find the index of the colon from the END of the string
SET @.pos = CharIndex(':', Reverse(@.ipLine), 1) - 1
--Trim the IP off the end of the string
SET @.ip = Right(@.ipLine, @.pos)
--Remove any trailing or leading white space
SET @.ip = RTrim(LTrim(@.ip))
END

--Drop the temp table
DROP TABLE #ip

--Update the audit fields based on the value being updated
UPDATE myTable
SET changed_by_ip = @.ip
, datetime_changed = GetDate()
, changed_by_host = host_name()
WHERE id IN (SELECT id FROM inserted)
GO

--Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')
--Display initial values
SELECT * FROM myTable

--Update one of the fields
UPDATE myTable
SET field1 = 'b'
WHERE id = 2
--Display changed values.
SELECT * FROM myTable

--Notice the change in datetime_changed where id = 2
GO

--And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable

This works on my install of 2000 and 2005.|||Let me see if I understand what the original poster (neetu bhagtani) was looking for, because this line of reasoning doesn't sound correct to me.

You have at least a SQL 2005 server, and a web server in a data center. You have clients that connect to the web server using HTTP, but those clients do not log in to the SQL Server directly.

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.

-PatP|||See as I told you before I get an error at this line -

INSERT #ip EXEC master..xp_cmdshell 'ipconfig' (as per the code given by you)

Insert #temp exec master..xp_cmdshell 'ipconfig' (and as per the code written by me)

which gives error saying that -' insert and execute statments cannot be nested'.

Also, I would like to tell that I was working on testing environment means on local server but not on live server since it this works on testing server and only we can upload and test on live server. Live Server is SQL Server 2003

Now when I uploaded your pinging version of Stored Procedure I get an error telling that master DB owner is someone else and when I give '[dbo].master' for executing the xp_cmdshell it gives another error saying that server is not the sysservers list use 'sp_addlinkedserver' Stored Procedure to add the server in the sysservers list

Pls help|||Yes, as described below is very much true

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.|||So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)|||Some users will access the application on web server from UK, some from US, and some from India. So all will connect to same web server on which the application resides but will have different IP addresses of their machines.|||You do realize that the IIS logs keep all of the information you are after. Right?|||You should be handling this at the application level and/or analyzing logs as MCrowley suggests. It is not physically possible to do at the database level per Pat's assertion.

Also, if you enabled the ability to fire xp_cmdshell under the privileges that your web app uses, I suggest you disable it right now.|||But how to exactly do that ? how to get it from IIS log ?|||Let's go back to the original requirement for a second. You need to get the webserver client's IP address, so you can put that into an audit trail for any updates. Is this about what you need?|||See I am inserting the data in my Audit table in the trigger while insert/update/delete and I have the log file created by IIS which has all IP Addresses. My problem is how to get the IP from log file and insert in the table since insert in the table is done in the trigger while insert/update/delete|||This sounds like it should be done by passing the variable from the client-side.
For example, if you're users are accessing the databse through a web front end then pick up the value from the client's workstation and pass it on submit of a query as one of the values.

Make sense?|||Why would this need to be done by a trigger? Why can't the webserver pass this information to you? Are there users accessing this data by methods other than the webserver?|||Hi there,

Does it have to be IP address? will hostname do? with a lookup table for IP address or something.

If you want the hostname then do this:

select hostname from sysprocesses
where spid = @.@.spid|||True, but in an IIS implementation both the web server's address and hostname ought to be constants. The original poster wants the IP address of the client, which is not available to the SQL Server.

-PatP

How to get the beta sp 2

I am working through the issue of email not working through subscription,
which seems to be an issue for many after scouring this forum and the net
over the past few days. I have a 2003 Server running with RS, SP1 installed
but I keep getting the error that the server saying ... "Failure sending
mail: The Report Server has encountered a configuration error; more details
in the log files" and the lof files show nothing other than the error that
has been mentioned here several times ...
ReportingServicesService!library!1678!01/07/2005-07:33:07:: i INFO: Call to
RenderFirst( '/Sutter Connect/SC EDI/EDI Utilization Vendor Report' )
ReportingServicesService!library!1678!01/07/2005-07:33:07:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
I have read through many KB articles, including 842423 and I can get it to
send just the link as mentioned in that article. I have tried the fixes and
installed the SP1 to no avail. I have reinstalled RS three times, using
different accounts (Local Admin, two different service domain accounts) and
nothing fixes the problem. I have the same issue on a test machine running
2000 Server. So I cannot get this feature to work.
Can anyone help? Does anyone know how to get the beta SP2? I am willing to
try anything. Thanks in advance.
~Sharihttp://support.microsoft.com/kb/842440 contains information about how you
can request access to SQL Server 2000 Reporting Services Beta 2.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:C319A846-B349-4E06-9C67-F00843B53717@.microsoft.com...
>I am working through the issue of email not working through subscription,
> which seems to be an issue for many after scouring this forum and the net
> over the past few days. I have a 2003 Server running with RS, SP1
> installed
> but I keep getting the error that the server saying ... "Failure sending
> mail: The Report Server has encountered a configuration error; more
> details
> in the log files" and the lof files show nothing other than the error that
> has been mentioned here several times ...
> ReportingServicesService!library!1678!01/07/2005-07:33:07:: i INFO: Call
> to
> RenderFirst( '/Sutter Connect/SC EDI/EDI Utilization Vendor Report' )
> ReportingServicesService!library!1678!01/07/2005-07:33:07:: e ERROR:
> Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in
> the
> log files, AuthzInitializeContextFromSid: Win32 error: 1355;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in
> the
> log files
> I have read through many KB articles, including 842423 and I can get it to
> send just the link as mentioned in that article. I have tried the fixes
> and
> installed the SP1 to no avail. I have reinstalled RS three times, using
> different accounts (Local Admin, two different service domain accounts)
> and
> nothing fixes the problem. I have the same issue on a test machine running
> 2000 Server. So I cannot get this feature to work.
> Can anyone help? Does anyone know how to get the beta SP2? I am willing to
> try anything. Thanks in advance.
> ~Shari|||Thank you Stephen for your very quick response. I submitted a request to that
link esterday and am waiting. Is it just a waiting game? Is there any other
method of getting this SP asap?
Thanks,
Shari
"Stephen Dybing [MSFT]" wrote:
> http://support.microsoft.com/kb/842440 contains information about how you
> can request access to SQL Server 2000 Reporting Services Beta 2.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please reply to the newsgroups only, thanks.
> "Shari" <Shari@.discussions.microsoft.com> wrote in message
> news:C319A846-B349-4E06-9C67-F00843B53717@.microsoft.com...
> >I am working through the issue of email not working through subscription,
> > which seems to be an issue for many after scouring this forum and the net
> > over the past few days. I have a 2003 Server running with RS, SP1
> > installed
> > but I keep getting the error that the server saying ... "Failure sending
> > mail: The Report Server has encountered a configuration error; more
> > details
> > in the log files" and the lof files show nothing other than the error that
> > has been mentioned here several times ...
> >
> > ReportingServicesService!library!1678!01/07/2005-07:33:07:: i INFO: Call
> > to
> > RenderFirst( '/Sutter Connect/SC EDI/EDI Utilization Vendor Report' )
> > ReportingServicesService!library!1678!01/07/2005-07:33:07:: e ERROR:
> > Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> > The Report Server has encountered a configuration error; more details in
> > the
> > log files, AuthzInitializeContextFromSid: Win32 error: 1355;
> > Info:
> > Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> > The Report Server has encountered a configuration error; more details in
> > the
> > log files
> >
> > I have read through many KB articles, including 842423 and I can get it to
> > send just the link as mentioned in that article. I have tried the fixes
> > and
> > installed the SP1 to no avail. I have reinstalled RS three times, using
> > different accounts (Local Admin, two different service domain accounts)
> > and
> > nothing fixes the problem. I have the same issue on a test machine running
> > 2000 Server. So I cannot get this feature to work.
> >
> > Can anyone help? Does anyone know how to get the beta SP2? I am willing to
> > try anything. Thanks in advance.
> >
> > ~Shari
>
>|||I do not know what the process is behind that link, and it might very well
be manual, so you should just give it a couple more days. Sorry, there isn't
any other way of getting it that I'm aware of.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:FB15ED4B-14B9-4886-944C-EB368F658B25@.microsoft.com...
> Thank you Stephen for your very quick response. I submitted a request to
> that
> link esterday and am waiting. Is it just a waiting game? Is there any
> other
> method of getting this SP asap?
> Thanks,
> Shari
>
> "Stephen Dybing [MSFT]" wrote:
>> http://support.microsoft.com/kb/842440 contains information about how you
>> can request access to SQL Server 2000 Reporting Services Beta 2.
>> --
>> Sincerely,
>> Stephen Dybing
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Please reply to the newsgroups only, thanks.
>> "Shari" <Shari@.discussions.microsoft.com> wrote in message
>> news:C319A846-B349-4E06-9C67-F00843B53717@.microsoft.com...
>> >I am working through the issue of email not working through
>> >subscription,
>> > which seems to be an issue for many after scouring this forum and the
>> > net
>> > over the past few days. I have a 2003 Server running with RS, SP1
>> > installed
>> > but I keep getting the error that the server saying ... "Failure
>> > sending
>> > mail: The Report Server has encountered a configuration error; more
>> > details
>> > in the log files" and the lof files show nothing other than the error
>> > that
>> > has been mentioned here several times ...
>> >
>> > ReportingServicesService!library!1678!01/07/2005-07:33:07:: i INFO:
>> > Call
>> > to
>> > RenderFirst( '/Sutter Connect/SC EDI/EDI Utilization Vendor Report' )
>> > ReportingServicesService!library!1678!01/07/2005-07:33:07:: e ERROR:
>> > Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
>> > The Report Server has encountered a configuration error; more details
>> > in
>> > the
>> > log files, AuthzInitializeContextFromSid: Win32 error: 1355;
>> > Info:
>> > Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
>> > The Report Server has encountered a configuration error; more details
>> > in
>> > the
>> > log files
>> >
>> > I have read through many KB articles, including 842423 and I can get it
>> > to
>> > send just the link as mentioned in that article. I have tried the fixes
>> > and
>> > installed the SP1 to no avail. I have reinstalled RS three times, using
>> > different accounts (Local Admin, two different service domain accounts)
>> > and
>> > nothing fixes the problem. I have the same issue on a test machine
>> > running
>> > 2000 Server. So I cannot get this feature to work.
>> >
>> > Can anyone help? Does anyone know how to get the beta SP2? I am willing
>> > to
>> > try anything. Thanks in advance.
>> >
>> > ~Shari
>>|||Did you get sp2 and did it solve the problem? (I'm having same trouble with RS SP1 on 2003 Server.)
--
Message posted via http://www.sqlmonster.com|||I worked this incident with Microsoft tried windows 2003 sp1 to no avail and then found an answer. They should be posting the new information in their kb and they are considering changes to the software to prevent this behavior.
To sum it up, the account that the report services is running as is having trouble checking some authorization attributes of the user that created the subscription. In my case my user account is in a different domain than the user account the sql reporting services is running as and my domain did not trust the domain of the sql reporting service account. So when the job tried to run that service account was not allowed to check an attribute in my domain for the account of the user that created the subscription. When the check fails it prevents the job from running and does not produce a very friendly error message. In my case I was able to create the 2 way trust relationship and it worked immediately.
MS's reason for doing this initially was so if an employee sets up a subscription to email himself corporate data and he quits or gets fired, they wanted to make sure his subscription stopped working once his account is disabled. I gave feedback that if an admin sets up production subscriptions and then leaves the company, you don't want your production systems to stop operating. They are reviewing the strategy and are considering not failing the job but rather just log warnings in the event log.
--
Message posted via http://www.sqlmonster.com|||More info: It seems the same process did not work for a Windows 2000
server, it seems the magic solution in our case was:
This worked:
Server 2003 SP1 (RC1)
Reporting Services SP1
RS Service domain trusted by subscription creator domain
This did not:
Server 2000 SP4
Reporting Services SP1
RS Service domain trusted by subscription creator domain
--
Message posted via http://www.sqlmonster.com

How to get SQL Express working remotely?

Hello all,

I am having trouble getting SQL Express 2005 on Windows Server 2003

working remotely. I have set it up for TCP connections and all,

and I can connect to the server fine, but I am unable to login. How do

I go about creating accounts for the db that will allow me to access it

remotely?

Mike's blog has a great explanation of what needs to be done to enable remote connections. Here's the link:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Thanks,
Sam Lester (MSFT)

How to get rid of dbo in field names?

Hello,
I just converted a website database from MS Access to SQL 2000, and many of
the pages are not working because SQL now wants fields to be ref'd as
"dbo.<<fieldname>>"... I've fixed this before, but cannot remember how... If
I cannot fix it through the database / connection, I'll have hundreds of
lines of code to modify.
Any suggestions?
Thanks in advance!!!!
--Jon
What are the login credentials of your application? Is it qualifying objects
with a different owner name? If so, s_changeobjectowner might help you.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"code" <code@.community.nospam> wrote in message
news:3BD82441-93C0-4CC9-A466-DD1E76291669@.microsoft.com...
> Hello,
> I just converted a website database from MS Access to SQL 2000, and many
> of
> the pages are not working because SQL now wants fields to be ref'd as
> "dbo.<<fieldname>>"... I've fixed this before, but cannot remember how...
> If
> I cannot fix it through the database / connection, I'll have hundreds of
> lines of code to modify.
> Any suggestions?
> Thanks in advance!!!!
> --Jon

How to get relation between two feilds of same table

Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.

My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.

there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.

In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.

I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)

How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.

Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.

Priyadarshini

You might want to ask this in a C# forum.

How to get output of sql command in columns

Hi,
I am working with Informix db in Digital Unix.
When I try to give any select commands and try to retrieve more than 5 columns in the same sql command, the output comes in rows instead of columns.
Is there a way to force it to come in columns?

i just use a simple format,
select column1 ,column2 ,column3 ,column4 ,column5 from tableyou should be getting 5 columns per record in the DB

column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5
column1 ,column2 ,column3 ,column4 ,column5

how do you want the layout and why?

How to get ODBC working with MSDE

Hi,
yes thanks .. but I did figure that out by myself.
SQLServer is running, I am able to osql on it ...
but ODBC refuses connectivity.
Maybe I just forgot something else.

>please have a look at
>http://support.microsoft.com/default...&Product=sql2k
You could checkout if your network protocols are on or not. OSQL may be
using shared memory connection here so it may be working properly.
KB article http://support.microsoft.com/?id=827204

How to get list of EventClasses in MSSQLServer2000

Hi,
I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
In MSSQL 2005 I am using built in system table like sys.trace_events I am
able to see list of events. When executing the following query.
SELECT * FROM sys.trace_events;
Is there any equal lent table available on MSSQLServer 2000. I tried a
lot, I am unable to get. Please any body help on this issue.
Thanks & Regards
-SomaSekharHi
I'm afraid you cannot get it in SQL Server 2000
sp_trace_setevent stored procedure has a parameter EventId and there is a
list of event numbers (See BOL)
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
> Hi,
> I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
> In MSSQL 2005 I am using built in system table like sys.trace_events I am
> able to see list of events. When executing the following query.
> SELECT * FROM sys.trace_events;
> Is there any equal lent table available on MSSQLServer 2000. I tried a
> lot, I am unable to get. Please any body help on this issue.
> Thanks & Regards
> -SomaSekhar|||Hi Uri,
What is the better solution for this. How to get list of EventId's and
EventClass name on SQL Server 2000. Because i am using
fn_trace_gettrable('trace file name', default), it's giving in EventClass as
a EventID. Insted of EventID i need EventClass name. How achive this in SQL
Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
sys.trace_events i am comparing both event ID's i am getiting EventClass
name. Please check this query,
--This will work on MS SQL Server 2005
SELECT TextData,trace_event_id,category_id,name
FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
EventID.trace_event_id
NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
give Ur trace file location"
What is the equallent Query in MS SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:

> Hi
> I'm afraid you cannot get it in SQL Server 2000
> sp_trace_setevent stored procedure has a parameter EventId and there is
a
> list of event numbers (See BOL)
>
>
> "SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
> news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
>
>|||Hi
Taken fro Vyas's web site
CREATE TABLE [dbo].[Events] (
[EventClass] [smallint] NOT NULL ,
[EventName] [varchar] (50) NOT NULL ,
[EventDescription] [varchar] (300) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
[dbo].[Events]([EventClass]) ON [PRIMARY]
GO
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(44,'SP:Stmt
S
tarting','SQL
statement inside a stored procedure is starting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(45,'SP:Stmt
C
ompleted','SQL
statement inside a stored procedure has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(46,'Object:
C
reated','Indicates
that an object has been created, such as for CREATE INDEX, CREATE TABLE, and
CREATE DATABASE statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(47,'Object:
D
eleted','Indicates
that an object has been deleted, such as in DROP INDEX and DROP TABLE
statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(48,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(49,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
TRANSACTION statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(51,'Scan:St
a
rted','Indicates
when a table or index scan has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(52,'Scan:St
o
pped','Indicates
when a table or index scan has stopped.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(53,'CursorO
p
en','Indicates
when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(54,'Transac
t
ion
Log','Tracks when transactions are written to the transaction log.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
Warning','Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a buffer
partition has reverted to an alternate plan. This can occur because of
recursion depth, data skew, trace flags, or bit counting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(56,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(57,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(58,'Auto Upd
ate
Stats','Indicates an automatic updating of index statistics has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(59,'Lock:De
a
dlock
Chain','Produced for each of the events leading up to the deadlock.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(60,'Lock:Es
c
alation','Indicates
that a finer-grained lock has been converted to a coarser-grained lock (for
example, a row lock escalated or converted to a page lock).')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
Errors','Indicates that an OLE DB error has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(62,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(63,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(64,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(65,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(66,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(67,'Executi
o
n
Warnings','Indicates any warnings that occurred during the execution of a
SQL Server statement or stored procedure.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(68,'Executi
o
n
Plan','Displays the plan tree of the Transact-SQL statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
Warnings','Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(70,'CursorP
r
epare','Indicates
when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(71,'Prepare
SQL','ODBC,
OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
for use.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(72,'Exec Pre
pared
SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(73,'Unprepa
r
e
SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
Transact-SQL statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(74,'CursorE
x
ecute','A
cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(75,'CursorR
e
compile','A
cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
recompiled either directly or due to a schema change.Triggered for ANSI and
non-ANSI cursors.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(76,'CursorI
m
plicitConversion','A
cursor on a Transact-SQL statement is converted by SQL Server from one type
to another.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(77,'CursorU
n
prepare','A
prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC,
OLE DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(78,'CursorC
l
ose','A
cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is closed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(79,'Missing
Column
Statistics','Column statistics that could have been useful for the optimizer
are not available.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(80,'Missing
Join
Predicate','Query that has no join predicate is being executed. This could
result in a long-running query.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(81,'Server M
emory
Change','Microsoft SQL Server memory usage has increased or decreased by
either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever
is greater.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(82,'User Con
figurable
(0)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(83,'User Con
figurable
(1)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(84,'User Con
figurable
(2)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(85,'User Con
figurable
(3)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(86,'User Con
figurable
(4)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(87,'User Con
figurable
(5)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(88,'User Con
figurable
(6)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(89,'User Con
figurable
(7)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(90,'User Con
figurable
(8)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(91,'User Con
figurable
(9)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(92,'Data Fil
e Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(93,'Log File
Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(94,'Data Fil
e Auto
Shrink','Indicates that a data file was shrunk automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(95,'Log File
Auto
Shrink','Indicates that a log file was shrunk automatically by the server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(96,'Show Pla
n
Text','Displays the query plan tree of the SQL statement from the query
optimizer.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(97,'Show Pla
n
ALL','Displays the query plan with full compile-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(98,'Show Pla
n
Statistics','Displays the query plan with full run-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(99,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(100,'RPC Out
put
Parameter','Produces output values of the parameters for every RPC.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(101,'Reserv
e
d','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(102,'Audit S
tatement
GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(103,'Audit O
bject
GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(104,'Audit A
dd/Drop
Login','Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(105,'Audit L
ogin
GDR','Occurs when a Microsoft Windows® login right is added or removed;
for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(106,'Audit L
ogin Change
Property','Occurs when a property of a login, except passwords, is modified;
for sp_defaultdb and sp_defaultlanguage.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(107,'Audit L
ogin Change
Password','Occurs when a SQL Server login password is changed.Passwords are
not recorded.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(108,'Audit A
dd Login to
Server Role','Occurs when a login is added or removed from a fixed server
role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(109,'Audit A
dd DB
User','Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(110,'Audit A
dd Member to
DB','Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
sp_changegroup.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(111,'Audit A
dd/Drop
Role','Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(112,'App Rol
e Pass
Change','Occurs when a password of an application role is changed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(113,'Audit S
tatement
Permission','Occurs when a statement permission (such as CREATE TABLE) is
used.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(114,'Audit O
bject
Permission','Occurs when an object permission (such as SELECT) is used, both
successfully or unsuccessfully.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(116,'Audit D
BCC','Occurs
when DBCC commands are issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(117,'Audit C
hange
Audit','Occurs when audit trace modifications are made.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(118,'Audit O
bject
Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
are issued.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(0,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(1,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(2,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(3,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(4,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(5,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(6,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(7,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(8,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(9,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(10,'RPC:Com
p
leted','Occurs
when a remote procedure call (RPC) has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(11,'RPC:Sta
r
ting','Occurs
when an RPC has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(12,'SQL:Bat
c
hCompleted','Occurs
when a Transact-SQL batch has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(13,'SQL:Bat
c
hStarting','Occurs
when a Transact-SQL batch has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(14,'Login',
'
Occurs when
a user successfully logs in to SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(15,'Logout'
,
'Occurs when
a user logs out of SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(16,'Attenti
o
n','Occurs
when attention events, such as client-interrupt requests or broken client
connections, happen.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(17,'Existin
g
Connection','Detects
all activity by users connected to SQL Server before the trace started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(18,'Service
C
ontrol','Occurs
when the SQL Server service state is modified.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(19,'DTCTran
s
action','Tracks
Microsoft Distributed Transaction Coordinator (MS DTC) coordinated
transactions between two or more databases.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(20,'Login
Failed','Indicates that a login attempt to SQL Server from a client
failed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(21,'EventLo
g
','Indicates
that events have been logged in the Microsoft Windows NT® application
log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(22,'ErrorLo
g
','Indicates
that error events have been logged in the SQL Server error log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(23,'Lock:Re
l
eased','Indicates
that a lock on a resource, such as a page, has been released.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(24,'Lock:Ac
q
uired','Indicates
acquisition of a lock on a resource, such as a data page.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(25,'Lock:De
a
dlock','Indicates
that two concurrent transactions have deadlocked each other by trying to
obtain incompatible locks on resources the other transaction owns.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(26,'Lock:Ca
n
cel','Indicates
that the acquisition of a lock on a resource has been canceled (for example,
due to a deadlock).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(27,'Lock:Ti
m
eout','Indicates
that a request for a lock on a resource, such as a page, has timed out due
to another transaction holding a blocking lock on the required resource.
Time-out is determined by the @.@.LOCK_TIMEOUT function, and can be set with
the SET LOCK_TIMEOUT statement.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(28,'DOP Even
t','Occurs
before a SELECT, INSERT, or UPDATE statement is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(29,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(30,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(31,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(32,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(33,'Excepti
o
n','Indicates
that an exception has occurred in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(34,'SP:Cach
e
Miss','Indicates
when a stored procedure is not found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(35,'SP:Cach
e
Insert','Indicates
when an item is inserted into the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(36,'SP:Cach
e
Remove','Indicates
when an item is removed from the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(37,'SP:Reco
m
pile','Indicates
that a stored procedure was recompiled.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(38,'SP:Cach
e
Hit','Indicates
when a stored procedure is found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(39,'SP:Exec
C
ontextHit','Indicates
when the execution version of a stored procedure has been found in the
procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(40,'SQL:Stm
t
Starting','Occurs
when the Transact-SQL statement has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(41,'SQL:Stm
t
Completed','Occurs
when the Transact-SQL statement has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(42,'SP:Star
t
ing','Indicates
when the stored procedure has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(43,'SP:Comp
l
eted','Indicates
when the stored procedure has completed.')
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:1F3FC9E1-32F3-4B9E-A2A4-1C3FF2B30588@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> What is the better solution for this. How to get list of EventId's and
> EventClass name on SQL Server 2000. Because i am using
> fn_trace_gettrable('trace file name', default), it's giving in EventClass
> as
> a EventID. Insted of EventID i need EventClass name. How achive this in
> SQL
> Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
> sys.trace_events i am comparing both event ID's i am getiting EventClass
> name. Please check this query,
> --This will work on MS SQL Server 2005
> SELECT TextData,trace_event_id,category_id,name
> FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
> INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
> EventID.trace_event_id
> NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
> give Ur trace file location"
> What is the equallent Query in MS SQL Server 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>|||Hi Uri,
Thank You very much. I will try this, but i want to know one thig with
out using this procedure any system tables is available on SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:

> Hi
> Taken fro Vyas's web site
> CREATE TABLE [dbo].[Events] (
> [EventClass] [smallint] NOT NULL ,
> [EventName] [varchar] (50) NOT NULL ,
> [EventDescription] [varchar] (300) NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
> [dbo].[Events]([EventClass]) ON [PRIMARY]
> GO
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(44,'SP:Stm
tStarting','SQL
> statement inside a stored procedure is starting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(45,'SP:Stm
tCompleted','SQL
> statement inside a stored procedure has completed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(46,'Object
:Created','Indicates
> that an object has been created, such as for CREATE INDEX, CREATE TABLE, a
nd
> CREATE DATABASE statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(47,'Object
:Deleted','Indicates
> that an object has been deleted, such as in DROP INDEX and DROP TABLE
> statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(48,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(49,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
> Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
> TRANSACTION statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(51,'Scan:S
tarted','Indicates
> when a table or index scan has started.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(52,'Scan:S
topped','Indicates
> when a table or index scan has stopped.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(53,'Cursor
Open','Indicates
> when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(54,'Transa
ction
> Log','Tracks when transactions are written to the transaction log.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
> Warning','Indicates that a hashing operation (for example, hash join, hash
> aggregate, hash union, and hash distinct) that is not processing on a buff
er
> partition has reverted to an alternate plan. This can occur because of
> recursion depth, data skew, trace flags, or bit counting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(56,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(57,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(58,'Auto U
pdate
> Stats','Indicates an automatic updating of index statistics has occurred.'
)
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(59,'Lock:D
eadlock
> Chain','Produced for each of the events leading up to the deadlock.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(60,'Lock:E
scalation','Indicates
> that a finer-grained lock has been converted to a coarser-grained lock (fo
r
> example, a row lock escalated or converted to a page lock).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
> Errors','Indicates that an OLE DB error has occurred.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(62,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(63,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(64,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(65,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(66,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(67,'Execut
ion
> Warnings','Indicates any warnings that occurred during the execution of a
> SQL Server statement or stored procedure.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(68,'Execut
ion
> Plan','Displays the plan tree of the Transact-SQL statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
> Warnings','Indicates sort operations that do not fit into memory. Does not
> include sort operations involving the creating of indexes; only sort
> operations within a query (such as an ORDER BY clause used in a SELECT
> statement).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(70,'Cursor
Prepare','Indicates
> when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
> DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(71,'Prepar
e SQL','ODBC,
> OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
> for use.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(72,'Exec P
repared
> SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
> statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(73,'Unprep
are
> SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
> Transact-SQL statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(74,'Cursor
Execute','A
> cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(75,'Cursor
Recompile','A
> cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
> recompiled either directly or due to a schema change.Triggered for ANSI an
d
> non-ANSI cursors.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(76,'Cursor
ImplicitConversion','A
> cursor on a Transact-SQL statement is converted by SQL Server from one typ
e
> to another.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(77,'Cursor
Unprepare','A
> prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODB
C,
> OLE DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(78,'Cursor
Close','A
> cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is closed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(79,'Missin
g Column
> Statistics','Column statistics that could have been useful for the optimiz
er
> are not available.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(80,'Missin
g Join
> Predicate','Query that has no join predicate is being executed. This could
> result in a long-running query.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(81,'Server
Memory
> Change','Microsoft SQL Server memory usage has increased or decreased by
> either 1 megabyte (MB) or 5 percent of the maximum server memory, whicheve
r
> is greater.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(82,'User C
onfigurable
> (0)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(83,'User C
onfigurable
> (1)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(84,'User C
onfigurable
> (2)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(85,'User C
onfigurable
> (3)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(86,'User C
onfigurable
> (4)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(87,'User C
onfigurable
> (5)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(88,'User C
onfigurable
> (6)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(89,'User C
onfigurable
> (7)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(90,'User C
onfigurable
> (8)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(91,'User C
onfigurable
> (9)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(92,'Data F
ile Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(93,'Log Fi
le Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(94,'Data F
ile Auto
> Shrink','Indicates that a data file was shrunk automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(95,'Log Fi
le Auto
> Shrink','Indicates that a log file was shrunk automatically by the server.
')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(96,'Show P
lan
> Text','Displays the query plan tree of the SQL statement from the query
> optimizer.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(97,'Show P
lan
> ALL','Displays the query plan with full compile-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(98,'Show P
lan
> Statistics','Displays the query plan with full run-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(99,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(100,'RPC O
utput
> Parameter','Produces output values of the parameters for every RPC.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(101,'Reser
ved','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(102,'Audit
Statement
> GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission i
s
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(103,'Audit
Object
> GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(104,'Audit
Add/Drop
> Login','Occurs when a SQL Server login is added or removed; for sp_addlogi
n
> and sp_droplogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(105,'Audit
Login
> GDR','Occurs when a Microsoft Windows? login right is added or removed;
> for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(106,'Audit
Login Change
> Property','Occurs when a property of a login, except passwords, is modifie
d;
> for sp_defaultdb and sp_defaultlanguage.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(107,'Audit
Login Change
> Password','Occurs when a SQL Server login password is changed.Passwords ar
e
> not recorded.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(108,'Audit
Add Login to
> Server Role','Occurs when a login is added or removed from a fixed server
> role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(109,'Audit
Add DB
> User','Occurs when a login is added or removed as a database user (Windows
> or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
> sp_adduser, and sp_dropuser.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(110,'Audit
Add Member to
> DB','Occurs when a login is added or removed as a database user (fixed or
> user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
> sp_changegroup.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(111,'Audit
Add/Drop
> Role','Occurs when a login is added or removed as a database user to a
> database; for sp_addrole and sp_droprole.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(112,'App R
ole Pass
> Change','Occurs when a password of an application role is changed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(113,'Audit
Statement
> Permission','Occurs when a statement permission (such as CREATE TABLE) is
> used.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(114,'Audit
Object
> Permission','Occurs when an object permission (such as SELECT) is used, bo
th
> successfully or unsuccessfully.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
> Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(116,'Audit
DBCC','Occurs
> when DBCC commands are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(117,'Audit
Change
> Audit','Occurs when audit trace modifications are made.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(118,'Audit
Object
> Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
> are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(0,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(1,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(2,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(3,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(4,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(5,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(6,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(7,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(8,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(9,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(10,'RPC:Co
mpleted','Occurs
> when a remote procedure call (RPC) has completed.')
> INSERT INTO [events]|||Soma
I'm not aware of it
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:695531DD-50FE-4A48-9578-B070055C0DD3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> Thank You very much. I will try this, but i want to know one thig with
> out using this procedure any system tables is available on SQL Server
> 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>

How to get hierarchical xml based of multiple tables xml columns

Hi,
I am currently working in SQL Server 2005.
we have three tables all these tables have an xml type columns.The XML in
these XML columns are related to each other.
EX.
Table "PLY" contains Rows As
<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>
<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>
Table "GRP" Contains Rows As
<dsgrp>
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
<grp_inst_info inst_ref_id="dca02503-8186-3480-a9d3-16aeedb7fa23" />
<grp_inst_info inst_ref_id="aba02503-8186-4380-a9d3-16aeedb7fa23" />
</grp>
</dsgrp>
<dsgrp>
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<grp_inst_info inst_ref_id="d7a02503-8186-4380-a9d3-16aeedb7fa08" />
</grp>
</dsgrp>
Table "INSTANCE" Contains Rows As
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
i want to get a hierachical relational xml out of these TABLES columns XML.
In the Following XML FORMAT
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<grp id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" slug="grp4">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
<instance id="dca02503-8186-3480-a9d3-16aeedb7fa23" slug="inst4" />
<instance id="aba02503-8186-4380-a9d3-16aeedb7fa23" slug="inst3" />
</grp>
</ply>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<grp id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="grp3">
<instance id="d7a02503-8186-4380-a9d3-16aeedb7fa08" slug="inst1" />
</grp>
</ply>
How can i achieve this using FLWOR Expression or any other way.
Thanks,
CarolI have done this for one table
try this
create table ply (ply_col xml)
insert into ply values ('<dsplaylist>
<ply id="f277f633-fa5d-4d98-8a30-d8d857d65343" slug="ply1">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
</ply>
</dsplaylist>')
insert into ply values ('<dsplaylist>
<ply id="c9835a5e-5dd0-47cd-8d14-a59ae00abda6" slug="ply2">
<ply_grp_info grp_ref_id="5de7cf11-54b4-43fc-8ae8-3885f2cd58fe" />
<ply_grp_info grp_ref_id="3de9cf11-34C8-53fc-6ae8-3335f2cd58fe" />
</ply>
</dsplaylist>')
This is the query
with CTE_PLY as
(
SELECT T1.ply_id.query('.') as ply_id
FROM ply
CROSS APPLY ply_col.nodes('/dsplaylist/ply') as T1(ply_id)
)
select ply_id.value('/ply[1]/@.id[1]','varchar(100)') as [id],
T2.ply_GRP.value('@.grp_ref_id','varchar(100)') as ply_grp_ref_id
FROM CTE_PLY
CROSS APPLY PLY_ID.nodes('/ply/ply_grp_info') as T2(ply_GRP)
If you can apply the same logic on all three tables then its a normal join
query :)
Hope this helps..