I am using the following code to find if a use exits in the table:
declare @.UserName char(30)
set @.UserName = (select fullname from udf_GetUserName(@.UserId))
SELECT @.@.ROWCOUNT
but that always returns 1. How do I solve that?
ThanksYou are getting the rowcount value for the SET variable statement, which
obviously always equal to 1. If you'd like to know how many full name values
exist for a given identifier, you might consider using COUNT(*) like:
SELECT COUNT(*) FROM tbl_valued_udf ;
or avoid the assignment and do:
SELECT col FROM tbl_valued_udf ;
SELECT @.@.ROWCOUNT ;
Alternatively to check the existence, you could do:
IF EXISTS ( SELECT * FROM tbl_valued_udf )
Anith|||You solve it by asking the correct query. :)
What you are asking SQL to return is the number of rows affected by the
previous statement. Since the previous SELECT always returns a single row,
you get a rowcount of 1. What you really should do is select the fullname
from the underlying user table (or from an abstraction such as a view or
procedure) where the userID = @.userID (supplied parameter). If you get an
empty result set, then the user doesn't exist.
It looks like someone is trying to write an abstraction layer for
programmers so they won't have to learn any SQL. That leads to some really
bad code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A494674C-147F-4009-8C40-99C163F6568D@.microsoft.com...
>I am using the following code to find if a use exits in the table:
> declare @.UserName char(30)
> set @.UserName = (select fullname from udf_GetUserName(@.UserId))
> SELECT @.@.ROWCOUNT
> but that always returns 1. How do I solve that?
> Thanks|||Why not just test IF IS NULL(@.UserName) instead?
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 12:47:01 -0700, Markgoldin
<Markgoldin@.discussions.microsoft.com> wrote:
>I am using the following code to find if a use exits in the table:
>declare @.UserName char(30)
>set @.UserName = (select fullname from udf_GetUserName(@.UserId))
>SELECT @.@.ROWCOUNT
>but that always returns 1. How do I solve that?
>Thanks
Showing posts with label returned. Show all posts
Showing posts with label returned. Show all posts
Wednesday, March 28, 2012
Numbering Query Results
Is there a way to have a column in a query result that is an "autonumber"?
Say I have 10 records returned, I want them to be numbered 1 - 10. I'm sure
there's a way using a stored procedure or something, I just can't think of a
way.
Thanks in advance.
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemSee:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"chuckdfoster" wrote:
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm su
re
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Thanks...
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:O7e$7waSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm
sure
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
Say I have 10 records returned, I want them to be numbered 1 - 10. I'm sure
there's a way using a stored procedure or something, I just can't think of a
way.
Thanks in advance.
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemSee:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"chuckdfoster" wrote:
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm su
re
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Thanks...
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:O7e$7waSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm
sure
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
Monday, March 26, 2012
numbering a query
Is there a quick way to number a query?
ie I want to number the records returned by a recordset consecutively.
This seems like it should be simple but I haven't figured out how to do it
yet.
Any help is appreciated!
TIA
Carter"me" <me@.work.com> wrote in message
news:10hn03h10dtja5a@.corp.supernews.com...
> Is there a quick way to number a query?
> ie I want to number the records returned by a recordset consecutively.
> This seems like it should be simple but I haven't figured out how to do it
> yet.
> Any help is appreciated!
> TIA
> Carter
http://www.aspfaq.com/show.asp?id=2427
Simon|||Thanks much I'll take a look
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411b9a40$1_3@.news.bluewin.ch...
> "me" <me@.work.com> wrote in message
> news:10hn03h10dtja5a@.corp.supernews.com...
> > Is there a quick way to number a query?
> > ie I want to number the records returned by a recordset consecutively.
> > This seems like it should be simple but I haven't figured out how to do
it
> > yet.
> > Any help is appreciated!
> > TIA
> > Carter
> http://www.aspfaq.com/show.asp?id=2427
> Simon
Friday, March 23, 2012
Number of Rows returned by sp
Is it possible to get the number of Rows returned by a stored procedure in a profiler ?
if yes, what parameters shud I be looking for?
THanks
HarshalDo you mean like:
USE Northwind
GO
CREATE PROC mySproc99 @.rs int OUTPUT
AS
SELECT * FROM Orders
SELECT @.rs = @.@.ROWCOUNT
GO
DECLARE @.rs int
EXEC mySproc99 @.rs OUTPUT
SELECT @.rs
GO
DROP PROC mySproc99
GO|||no.
the sps are not to be changed.
its like
create procedure test
as
begin
select * from table
end
suppose this is the existing sp which is being used by a application
I want to put the profiler to get the number of rows which are being selected by this sp.
since its the prod db I cant change the sp.|||You may want to look into sp_trace_generateevent and related topics, but I think you'd still need to alter your procedures.|||From books online...
Use the SP:StmtCompleted event, and trace the Integer Data.
It is a bit difficult to find in BOL, so I bookmarked it, for myself. Try searching on "Monitoring with SQL Profiler Event Categories" The quoted string, will get you the desired result|||I can find the topic, and I can find a table that shows that the integer counter returns something for a stored procedure's StmtCompleted event, but darned if I can find anywhere that it explicitly says what that integer is!
Good sleuthing!
-PatP|||Pat: There should be two links on the page. Following "Stored Procedures Data Columns" should bring you to a short description of all the data elements.|||Hey thank you guys !!
I got the required data by importing the required data to my box and altered the proc and ran it. :cool: Ok I agree this is not a professional way to do things but it was a show stopper bug in the system so had to find a way.
I'll check the profiler events that rdjabarov and MCrowley has suggested.
Thanks again.
Regards,
Harshal.
if yes, what parameters shud I be looking for?
THanks
HarshalDo you mean like:
USE Northwind
GO
CREATE PROC mySproc99 @.rs int OUTPUT
AS
SELECT * FROM Orders
SELECT @.rs = @.@.ROWCOUNT
GO
DECLARE @.rs int
EXEC mySproc99 @.rs OUTPUT
SELECT @.rs
GO
DROP PROC mySproc99
GO|||no.
the sps are not to be changed.
its like
create procedure test
as
begin
select * from table
end
suppose this is the existing sp which is being used by a application
I want to put the profiler to get the number of rows which are being selected by this sp.
since its the prod db I cant change the sp.|||You may want to look into sp_trace_generateevent and related topics, but I think you'd still need to alter your procedures.|||From books online...
Use the SP:StmtCompleted event, and trace the Integer Data.
It is a bit difficult to find in BOL, so I bookmarked it, for myself. Try searching on "Monitoring with SQL Profiler Event Categories" The quoted string, will get you the desired result|||I can find the topic, and I can find a table that shows that the integer counter returns something for a stored procedure's StmtCompleted event, but darned if I can find anywhere that it explicitly says what that integer is!
Good sleuthing!
-PatP|||Pat: There should be two links on the page. Following "Stored Procedures Data Columns" should bring you to a short description of all the data elements.|||Hey thank you guys !!
I got the required data by importing the required data to my box and altered the proc and ran it. :cool: Ok I agree this is not a professional way to do things but it was a show stopper bug in the system so had to find a way.
I'll check the profiler events that rdjabarov and MCrowley has suggested.
Thanks again.
Regards,
Harshal.
Number of row returned by ExecuteReader
when I execute the line:
reader = comm.ExecuteReader();
Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned.
For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form.
Here is my code snippet with further explanation of what I am trying to do:
int Inumber = 0;foreach (string itemin menuHeaders){
string title = menuHeaders[Inumber];sp.Value = menuHeaders[Inumber];
Inumber++;
conn.Open();
reader = comm.ExecuteReader(CommandBehavior.CloseConnection);//Get the culture property of the thread.
CultureInfo cultureInfo =Thread.CurrentThread.CurrentCulture;//Create TextInfo object.
TextInfo textInfo = cultureInfo.TextInfo;// WHAT I AM TRYING TO DO...... Here I would like to wrap this with an if statement, if Records returned by the reader are 0, skip while loop and header display
// If one, then display in singular and if 2 add an s to the title.Convert to title case and display.
content.Text +="<H3>" + textInfo.ToTitleCase(title) +"</H3>";while (reader.Read()){
content.Text +="<a href='" + reader["website"] +"'>"
+ reader["f_name"] + reader["l_name"] +"</a>"+", " +reader["organization"]+"<br />";}
//Close the connection.
reader.Close();
conn.Close();
}
This issue has been discussed before several times, please search the forum. Basically, the SqlDataReader is forward only, which means that you can only step forward one row at a time. Hence, you cannot step to the last row to calculate the returned number of rows. And SQL won't do this automatically for you. One common method is to fill a DataSet or DataTable instead of using an SqlDataReader, as they both are capable of returning the number of rows.
If you choose to go down the SqlDataReader path, you can make use of the fact that the Read() method returns false if there are no rows. So that will solve your first situation. Now, how do you know if you need to do a plural header or not? Well, if count the lines while you create the output, you will know afterwards (once the loop is done). And then, you can set your header label.
Also, in your case I would definitely use StringBuilder instead of regular string concatenation, as performance will be better for large result sets (the theory behind this can be found if you google) :-)
Good luck!
sqlTuesday, March 20, 2012
Number of Columns
How can I tell how many column is returned in a query like this?
Select *.general, lname.newbusiness, contact.newbusiness, ….
ThanksThere is no built-in functions in SQL Server which does this. However, most
client side data access APIs will have a mechanism of identifying the number
of columns in the columns collection of the resultset.
Anith|||Depends how you are returning the data. For example, the Count porperty
of the ADO Fields collection gives this information.
Anyway, it is generally considered bad practice to use SELECT * in a
production application. List all the column names individually. Query
Analyzer lets you click and drag the column list into the editing
window so you don't have to do lots of typing.
--
David Portas
SQL Server MVP
--|||Actually, I don't really know that syntax, but I guess you mean something
similar to this :
select table1.*, table2.some_field, table2.some_otherfield, table3.*
etc
The easiest way I can see is by actually counting the fields in the
resultset. If you do not want the results than add a WHERE 1 = 2 to the end,
that way you will get the structure of the resultset, but without any data
in it (and without having to wait for the server to do all the work)
A rather complex route to find out upfront would be to do it like this :
select total_number_of_columns = (SELECT COUNT(*) FROM syscolumns col JOIN
sysobjects obj ON obj.id = col.id and obj.name = 'table1' and xtype =
'U') -- table 1 : * = all columns
+ 2 -- table 2 only two
columns asked for
+ (SELECT COUNT(*) FROM
syscolumns col JOIN sysobjects obj ON obj.id = col.id and obj.name =
'table3' and xtype = 'U') -- table 3 : * = all columns
Probably works, but I wonder what it's use is.
Cu
Roby
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||Emma,
Not sure what you are trying to accomplish, but here is one way to do it in
t-sql:
Select 1 as '1', 2 as '2', 3 as '3', 4 as '4', 5 as '5', 6 as '6'
into ##p
select count(*) from tempdb..syscolumns where id = object_id('tempdb..##p')
Ilya
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||This won't work in every case. SELECT INTO requires that the column
names are unique so if you join two tables and don't alias the columns
then it may fail. Perhaps the OP knows that her two tables won't have
conflicting column names but if the columns were really fixed and known
in advance then she wouldn't need a query to count them. If all the
columns are aliased then they are presumably known and therefore the
query is still pretty pointless. I guess the real question here is
exactly why the OP wouldn't know at development time how many columns
would be returned by her queries.
--
David Portas
SQL Server MVP
--|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!
Select *.general, lname.newbusiness, contact.newbusiness, ….
ThanksThere is no built-in functions in SQL Server which does this. However, most
client side data access APIs will have a mechanism of identifying the number
of columns in the columns collection of the resultset.
Anith|||Depends how you are returning the data. For example, the Count porperty
of the ADO Fields collection gives this information.
Anyway, it is generally considered bad practice to use SELECT * in a
production application. List all the column names individually. Query
Analyzer lets you click and drag the column list into the editing
window so you don't have to do lots of typing.
--
David Portas
SQL Server MVP
--|||Actually, I don't really know that syntax, but I guess you mean something
similar to this :
select table1.*, table2.some_field, table2.some_otherfield, table3.*
etc
The easiest way I can see is by actually counting the fields in the
resultset. If you do not want the results than add a WHERE 1 = 2 to the end,
that way you will get the structure of the resultset, but without any data
in it (and without having to wait for the server to do all the work)
A rather complex route to find out upfront would be to do it like this :
select total_number_of_columns = (SELECT COUNT(*) FROM syscolumns col JOIN
sysobjects obj ON obj.id = col.id and obj.name = 'table1' and xtype =
'U') -- table 1 : * = all columns
+ 2 -- table 2 only two
columns asked for
+ (SELECT COUNT(*) FROM
syscolumns col JOIN sysobjects obj ON obj.id = col.id and obj.name =
'table3' and xtype = 'U') -- table 3 : * = all columns
Probably works, but I wonder what it's use is.
Cu
Roby
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||Emma,
Not sure what you are trying to accomplish, but here is one way to do it in
t-sql:
Select 1 as '1', 2 as '2', 3 as '3', 4 as '4', 5 as '5', 6 as '6'
into ##p
select count(*) from tempdb..syscolumns where id = object_id('tempdb..##p')
Ilya
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||This won't work in every case. SELECT INTO requires that the column
names are unique so if you join two tables and don't alias the columns
then it may fail. Perhaps the OP knows that her two tables won't have
conflicting column names but if the columns were really fixed and known
in advance then she wouldn't need a query to count them. If all the
columns are aliased then they are presumably known and therefore the
query is still pretty pointless. I guess the real question here is
exactly why the OP wouldn't know at development time how many columns
would be returned by her queries.
--
David Portas
SQL Server MVP
--|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!
Friday, March 9, 2012
nullifying value from FOR XML EXPLICIT
In this query (using T-SQL, don't know about SQLXML) hello becomes NULL and an error is returned...
select 2 as tag, null as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
union all
select 19 as tag, 2 as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
for xml explicit
But change just about anything (make it xml auto, add or remove a few chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it will work fine.
Anyone know what gives? Seems to be related to string lengths... Our workaround is to start tag ID's at >100 but does that mean some other combination will flake out?
Cheers...
John
Try changing alias [aaaaaaaaaaaaaaaaaaaaaaa!19] to
[aaaaaaaaaaaaaaaaaaaaaaa!19!] - there's a known bug in FOR XML EXPLICIT
code.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Nowak" <anonymous@.discussions.microsoft.com> wrote in message
news:08168845-CD3E-47DD-A4A9-686AF2B2525F@.microsoft.com...
> In this query (using T-SQL, don't know about SQLXML) hello becomes NULL
and an error is returned...
> select 2 as tag, null as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> union all
> select 19 as tag, 2 as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> for xml explicit
> But change just about anything (make it xml auto, add or remove a few
chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it
will work fine.
> Anyone know what gives? Seems to be related to string lengths... Our
workaround is to start tag ID's at >100 but does that mean some other
combination will flake out?
> Cheers...
> John
select 2 as tag, null as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
union all
select 19 as tag, 2 as parent
,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
for xml explicit
But change just about anything (make it xml auto, add or remove a few chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it will work fine.
Anyone know what gives? Seems to be related to string lengths... Our workaround is to start tag ID's at >100 but does that mean some other combination will flake out?
Cheers...
John
Try changing alias [aaaaaaaaaaaaaaaaaaaaaaa!19] to
[aaaaaaaaaaaaaaaaaaaaaaa!19!] - there's a known bug in FOR XML EXPLICIT
code.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Nowak" <anonymous@.discussions.microsoft.com> wrote in message
news:08168845-CD3E-47DD-A4A9-686AF2B2525F@.microsoft.com...
> In this query (using T-SQL, don't know about SQLXML) hello becomes NULL
and an error is returned...
> select 2 as tag, null as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,null as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> union all
> select 19 as tag, 2 as parent
> ,null as [ffffff:eeeeeeeeeeeeeee!2!bbbbbbbbbbbbbbbbbbbbbbbb !element]
> ,null as [ffffff:eeeeeeeeeeeeeee!2!cccccccccccccccccccccccc !element]
> ,'hello' as [aaaaaaaaaaaaaaaaaaaaaaa!19]
> for xml explicit
> But change just about anything (make it xml auto, add or remove a few
chars, change the 2 to a 12 or the 19 to either a 9 or a 190...) and it
will work fine.
> Anyone know what gives? Seems to be related to string lengths... Our
workaround is to start tag ID's at >100 but does that mean some other
combination will flake out?
> Cheers...
> John
Wednesday, March 7, 2012
NULL values returned when reading values from a text file using Data Reader.
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics
I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.
I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.
I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
|||Shizelmah wrote:
I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback
Hope that helps.
Regards
-Jamie
|||I have encountered the same problem. It seems to be a bug in SSIS.
After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.
I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.
If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.
Regards,
Grzegorz
NULL values returned when reading values from a text file using Data Reader.
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics
I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.
I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.
I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
|||I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback
Hope that helps.
Regards
-Jamie
|||I have encountered the same problem. It seems to be a bug in SSIS.
After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.
I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.
If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.
Regards,
Grzegorz
NULL values returned when reading values from a text file using Data Reader.
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?Why aren't you using the flat file source connector?|||
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
Sorry: I reread my initial posting and it contained some incorrect details. I have modified the message accordingly and the modified content is in italics
I have a DTSX package which reads values from a delimited text file using a Flat File source component (and a Lookup for validating some of the data) and reads the data into a Table in an Oracle database. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. The typical file length is between 300,000 to 500,000 rows of data. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Flat File source component. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
|||One additional detail which may be pertinent: The DTSX is running inside of a virtual machine.|||Since you are using a lookup, a couple of things to consider:
Lookup matching is case sensitive, so you may have to adjust your data for comparison accordingly|||It appears that memory may be the answer. The VM where the DTSX was running only had 512 MB of RAM. When this was moved to an environment with 1 GB we do not see the same issues.|||Hmmm - seems I spoke too soon. One file which was about 75,000 rows of data / 30 MB of data processed successfully. However another file which was around 300,000 rows of data / 150 MB resulted in the same "false NULL" situation. I'm going to try breaking down the larger file into 4 smaller segments and will process them each individually to see if this makes a difference. I am not aware of any explicit size limitations in SSIS that we should be bumping up against with these file sizes but can anyone tell me if there are thresholds that should not be exceeded as a best practice when dealing with flat files or lookups?|||Ok, running the smaller file also resulted in the same error as before. We're now operating under the theory that this has to do with something being kept in memory after the package initially runs since we usually seem to be able to generate a "clean" result file after the first time we try running a package. To test this theory we will reboot the computer where the DTSX is stored and will then rerun a file which has already generated errors.|||
It appears that we have a solution: We broke up the DTSX package into 4 smaller packages, broke the file up in 4 smaller files, ran the packages on a non-virtual machine with 1GB of RAM, and executed the packages through the command line. When all of these changes were combined we get the expected result without any false NULL issues. Initial testing seems to reveal that omitting any one of these steps may still result in the original error but this isn't conclusive at this point in time as we haven't tried all of the different scenarios. All these changes seem to indicate that the root cause of the issue is related to available memory, and I am interested in anyone else has any insight - thanks.
I am encountering this error. My solution was to uncheck the "Retain null values..." within the Flat File Source. However, I had to change my logic that checked for nulls to check for empty strings.
I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
|||I really wish this would be resolved by Microsoft because it seems to happen when the files have many rows. I'm importing about 7 mil rows a time. It would be nice to have more confidence in the SSIS product.
So what are you going to do about it? Leaving pithy comments on here won't make the slightest bit of difference I'm afraid. The correct place to submit your bugs and suggestions is http://connect.microsoft.com/sqlserver/feedback
Hope that helps.
Regards
-Jamie
|||I have encountered the same problem. It seems to be a bug in SSIS.After doing some investigation it turned out it was the Union All component used in the dataflow which caused this situation. When I removed the Union All component the problem disappeared.
I can't explain this weird behaviour of SSIS - I suppose it is a bug related to internal SSIS buffer management.
If you have a Union All component with more than 4 inputs in your dataflow, do try to remove it. Maybe it will help, as it was in my case.
Regards,
Grzegorz
Saturday, February 25, 2012
NULL Returned for Populated Column
Here is a strange one:
The following partitioned table (partitioned for 31 days) with a single nonclustered, nonunique index is behaving improperly on a select. A standard select using the index returns all NULL values for altitude yet if I do a select for the TOP x rows ordered by reporttime desc for not null altitudes, I see the values stored in the column. There are over 5,000,000 rows in the table with a relatively even distribution over each partition (except partition 1 which is kept empty).
CREATE TABLE [dbo].[APRSTrack](
[CallsignSSID] [varchar](9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[ReportTime] [datetime] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[Icon] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Course] [smallint] NULL,
[Speed] [int] NULL,
[Altitude] [int] NULL
) ON [onemonth]([ReportTime])
CREATE NONCLUSTERED INDEX [IX_APRSTrack] ON [dbo].[APRSTrack]
(
[CallsignSSID] ASC,
[ReportTime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90, ONLINE = OFF) ON [onemonth]([ReportTime])
The failing select statement is:
SELECT *
FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime
and the working select statement is:
select top 100 *
from APRSTrack
where (not (altitude is null)) and reporttime >= '2006-06-06'
order by reporttime desc
The execution plan for the first lookup is:
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2047.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="2.67004" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.00992778" StatementText="SELECT *
 FROM [Ham Radio].[dbo].[APRSTrack]
where callsignssid = 'EA3ABN-9' and reporttime >= '2006-06-06'
order by reporttime" StatementType="SELECT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan CachedPlanSize="60">
<RelOp AvgRowSize="54" EstimateCPU="1.11608E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00992778">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="PtnIds1007" />
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
<RelOp AvgRowSize="38" EstimateCPU="2.67004E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032852">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
<ColumnReference Column="PtnIds1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="PtnIds1007" />
<ScalarOperator ScalarString="RangePartitionNew([Ham Radio].[dbo].[APRSTrack].[ReportTime],(1),'2006-05-07 00:00:00.000','2006-05-08 00:00:00.000','2006-05-09 00:00:00.000','2006-05-10 00:00:00.000','2006-05-11 00:00:00.000','2006-05-12 00:00:00.000','2006-05-13 00:00:00.000','2006-05-14 00:00:00.000','2006-05-15 00:00:00.000','2006-05-16 00:00:00.000','2006-05-17 00:00:00.000','2006-05-18 00:00:00.000','2006-05-19 00:00:00.000','2006-05-20 00:00:00.000','2006-05-21 00:00:00.000','2006-05-22 00:00:00.000','2006-05-23 00:00:00.000','2006-05-24 00:00:00.000','2006-05-25 00:00:00.000','2006-05-26 00:00:00.000','2006-05-27 00:00:00.000','2006-05-28 00:00:00.000','2006-05-29 00:00:00.000','2006-05-30 00:00:00.000','2006-05-31 00:00:00.000','2006-06-01 00:00:00.000','2006-06-02 00:00:00.000','2006-06-03 00:00:00.000','2006-06-04 00:00:00.000','2006-06-05 00:00:00.000','2006-06-06 00:00:00.000')">
<Intrinsic FunctionName="RangePartitionNew">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-07 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-08 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-09 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-10 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-11 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-12 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-13 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-14 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-15 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-16 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-17 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-18 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-19 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-20 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-21 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-22 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-23 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-24 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-25 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-26 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-27 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-28 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-29 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-30 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-05-31 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-01 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-02 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-03 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-04 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-05 00:00:00.000'" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="34" EstimateCPU="0.000159937" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.67004" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328494">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Index="[IX_APRSTrack]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="CallsignSSID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'EA3ABN-9'">
<Const ConstValue="'EA3ABN-9'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GE">
<RangeColumns>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="ReportTime" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="'2006-06-06 00:00:00.000'">
<Const ConstValue="'2006-06-06 00:00:00.000'" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator ScalarString="(32)">
<Const ConstValue="(32)" />
</ScalarOperator>
</ColumnReference>
</PartitionId>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="35" EstimateCPU="0.000251393" EstimateIO="0.003125" EstimateRebinds="1.67004" EstimateRewinds="0" EstimateRows="1" LogicalOp="RID Lookup" NodeId="7" Parallel="false" PhysicalOp="RID Lookup" EstimatedTotalSubtreeCost="0.00663141">
<OutputList>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</OutputList>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Latitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Longitude" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Icon" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Course" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Speed" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" Column="Altitude" />
</DefinedValue>
</DefinedValues>
<Object Database="[Ham Radio]" Schema="[dbo]" Table="[APRSTrack]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<PartitionId>
<ColumnReference Column="PtnIds1007" />
</PartitionId>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@.2" ParameterCompiledValue="'2006-06-06'" />
<ColumnReference Column="@.1" ParameterCompiledValue="'EA3ABN-9'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Any ideas?
Pete Loveall
AME Corp.
This is definitely an issue with the nonclustered index. I changed the index to a clustered index and everything works fine. In fact, the table originally had a clustered index just on reporttime and IX_APRSTrack was a nonclustered index. When I dropped the reporttime clustered index, all entries added after that point were improperly displayed in any query using the nonclustered index. Once I changed the nonclustered index to a clustered index, every row displays properly now including those that did not display properly previously.
SQL 2005 query has some definite issues with nonclustered indexes. Some of those issues may be related to partioned indexes on partitioned tables, but the nonclustered seeks are definitely wrong. The indexes and databases check just fine using CHECKDB. I also notice that doing a search using a nonclustered index which should be confined to one or two partitions is actually done across all partitions (see the execution plan above). The same SELECT done against a clustered index restricts the lookup to the relavent partitions.
This table has a high rate of insertions which make the clustered index undesireable. However, if that is what must be done until Microsoft fixes the query generator, we'll limp along.
Microsoft, are you listening?
Pete Loveall
AME Corp.
Null result returned even though IS NOT NULL specified.
Hi guys,
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info:
--
select @.@.Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,
Thanks for your reply
are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get
Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?
25212 + 0 + 2660565 = 2685777
so everything is accounted for
you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?
An incorrect assumption on my part. Moving right along :)
you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?
This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.
My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.
-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows
the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,
of course WHERE WorkStation = 'NULL' returns 0 rows
the correct syntax is WHERE WorkStation IS NULL
My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?
SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,
That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.
I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.
Example:
-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))
-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)
-- Display whats in scratch table for reference
select * from #NullVsEmpty
-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL
-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''
-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''
DROP TABLE #NullVsEmpty
I've got a query on a particular table returning an odd result:
SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation
This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.
SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber
I can't reproduce these results on another site on a table of the same structure, or on another table at this site.
Any suggestions as to what might be going on?
Pertinent info:
--
select @.@.Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice
SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation
Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?
TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,
Thanks for your reply
are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get
Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?
25212 + 0 + 2660565 = 2685777
so everything is accounted for
you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?
An incorrect assumption on my part. Moving right along :)
you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?
This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.
My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.
-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows
the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,
of course WHERE WorkStation = 'NULL' returns 0 rows
the correct syntax is WHERE WorkStation IS NULL
My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?
SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,
That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.
I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.
Example:
-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))
-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)
-- Display whats in scratch table for reference
select * from #NullVsEmpty
-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL
-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''
-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''
DROP TABLE #NullVsEmpty
Monday, February 20, 2012
Null fields (username, host, etc) returned in a trace!?!
I would be really grateful if someone would help me with this as I'm
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
--
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
---
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
--
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
DomHi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> ---
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/ ****************************************
************/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/ ****************************************
************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
--
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
---
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
--
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
DomHi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> ---
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/ ****************************************
************/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/ ****************************************
************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
Null fields (username, host, etc) returned in a trace!?!
I would be really grateful if someone would help me with this as I'm
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
Dom
Hi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom
|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/************************************************** **/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
Dom
Hi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom
|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/************************************************** **/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
Null fields (username, host, etc) returned in a trace!?!
I would be really grateful if someone would help me with this as I'm
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
--
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
---
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
--
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
DomHi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> ---
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/****************************************************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>> Hi
>> In profiler the data fields that will appear will depend on the
>> template you choose. You can add the data columns needed from the
>> subsequent dialog. If you get the columns/events working in profiler,
>> you can use the Script Trace on the file menu to give you the SQL
>> needed to re-create that profile.
>> At a guess you are probably not calling the sp_trace_setevent for the
>> correct event/column combinations.
>> John
>>
>> "Dom" <post.to.group@.newsgroup.com> wrote in message
>> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
really stumped...
I've written a stored procedure to enable me to kick off traces from the
SQL Agent and they run just fine - the problem is that some of the fields
that should have data in them just return null. For example I run one to
audit logins and the debug info I added tells me that the events/columns
below are being traced (it picks up what it should trace from a table I
query in the stored procedure). But when I look at the trace output
columns such as NTUserName, ClientHostName, etc, etc they are always null
(pretty much the only thing that does get populated is TextData, SPID and
ServerName). Interesting the fields don't even appear when I open the
file in Profiler but do show up as null if open the file using
::fn_trace_gettable.
If I run a trace using SQL Profiler then it shows all the fields as it
should! Any help would be much appreciated, and let me know if you want
me to post the full stored procedure. I'm using SQL 2000 on a Windows
2003 server.
To massively summarise the stored procedure, here it is
sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
-- pull the events to be traced from a table
exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
-- pull the columns to be reported on from a table
exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
Here is the debug info I added (just spits out the events and the columns
it traces
Trace Event Info
--
Trace Events for session
(1 rows(s) affected)
EventNumber Category EventName EventDescription
-- -- -- --
14 session Login Occurs when a use
15 session Logout Occurs when a use
20 session Login Failed Indicates that a
(3 rows(s) affected)
Trace Column Info
---
Trace Columns for session
(1 rows(s) affected)
Category ColumnNumber ColumnName ColumnDescription
-- -- -- --
all 1 TextData Text value depen
all 3 DatabaseID ID of the databa
all 6 NTUserName Microsoft Window
all 8 ClientHostName Name of the clie
all 9 ClientProcessID ID assigned by t
all 10 ApplicationName Name of the clie
all 11 SQLSecurityLoginName SQL Server login
all 12 SPID Server Process I
all 13 Duration Amount of elapse
(24 rows(s) affected)
TraceID
--
5
Thanks very much for all your help and apologies if this is a dumb
question but it has me stumped!
DomHi
In profiler the data fields that will appear will depend on the template you
choose. You can add the data columns needed from the subsequent dialog. If
you get the columns/events working in profiler, you can use the Script Trace
on the file menu to give you the SQL needed to re-create that profile.
At a guess you are probably not calling the sp_trace_setevent for the
correct event/column combinations.
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>I would be really grateful if someone would help me with this as I'm
> really stumped...
> I've written a stored procedure to enable me to kick off traces from the
> SQL Agent and they run just fine - the problem is that some of the fields
> that should have data in them just return null. For example I run one to
> audit logins and the debug info I added tells me that the events/columns
> below are being traced (it picks up what it should trace from a table I
> query in the stored procedure). But when I look at the trace output
> columns such as NTUserName, ClientHostName, etc, etc they are always null
> (pretty much the only thing that does get populated is TextData, SPID and
> ServerName). Interesting the fields don't even appear when I open the
> file in Profiler but do show up as null if open the file using
> ::fn_trace_gettable.
> If I run a trace using SQL Profiler then it shows all the fields as it
> should! Any help would be much appreciated, and let me know if you want
> me to post the full stored procedure. I'm using SQL 2000 on a Windows
> 2003 server.
> To massively summarise the stored procedure, here it is
> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize, NULL
> -- pull the events to be traced from a table
> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
> -- pull the columns to be reported on from a table
> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> Here is the debug info I added (just spits out the events and the columns
> it traces
> Trace Event Info
> --
> Trace Events for session
> (1 rows(s) affected)
> EventNumber Category EventName EventDescription
> -- -- -- --
> 14 session Login Occurs when a use
> 15 session Logout Occurs when a use
> 20 session Login Failed Indicates that a
> (3 rows(s) affected)
> Trace Column Info
> ---
> Trace Columns for session
> (1 rows(s) affected)
> Category ColumnNumber ColumnName ColumnDescription
> -- -- -- --
> all 1 TextData Text value depen
> all 3 DatabaseID ID of the databa
> all 6 NTUserName Microsoft Window
> all 8 ClientHostName Name of the clie
> all 9 ClientProcessID ID assigned by t
> all 10 ApplicationName Name of the clie
> all 11 SQLSecurityLoginName SQL Server login
> all 12 SPID Server Process I
> all 13 Duration Amount of elapse
> (24 rows(s) affected)
> TraceID
> --
> 5
> Thanks very much for all your help and apologies if this is a dumb
> question but it has me stumped!
> Dom|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in
news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
> Hi
> In profiler the data fields that will appear will depend on the
> template you choose. You can add the data columns needed from the
> subsequent dialog. If you get the columns/events working in profiler,
> you can use the Script Trace on the file menu to give you the SQL
> needed to re-create that profile.
> At a guess you are probably not calling the sp_trace_setevent for the
> correct event/column combinations.
> John
>
> "Dom" <post.to.group@.newsgroup.com> wrote in message
> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>
>
Thanks very much for the reply. There is a chance I could be looking at
the wrong columns but the debug I put into my stored procedure shows that
I'm at least adding the column to the trace (NTUserName for instance),
and it does show up as a field in ::fn_trace_gettable which I wouldn't
have thought it would unless the trace is doing something with it... I
just can't figure why it always comes up as null. Is there anyway to get
info from an active trace i.e. which columns are being recorded?
TIA
Dom|||Hi
If you got it working in profiler then scripting it would recreate it the
same and therefore should be no need to debug.
fn_trace_geteventinfo should say what events/data columns you are tracing.
This is what I get when I script the login/logout/login failed events
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 15/05/2005 09:13:32 */
/****************************************************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
set @.maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
exec @.rc = sp_trace_create @.TraceID output, 0, N'InsertFileNameHere',
@.maxfilesize, NULL
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 7, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
exec sp_trace_setevent @.TraceID, 14, 40, @.on
exec sp_trace_setevent @.TraceID, 14, 41, @.on
exec sp_trace_setevent @.TraceID, 15, 1, @.on
exec sp_trace_setevent @.TraceID, 15, 6, @.on
exec sp_trace_setevent @.TraceID, 15, 7, @.on
exec sp_trace_setevent @.TraceID, 15, 9, @.on
exec sp_trace_setevent @.TraceID, 15, 10, @.on
exec sp_trace_setevent @.TraceID, 15, 11, @.on
exec sp_trace_setevent @.TraceID, 15, 12, @.on
exec sp_trace_setevent @.TraceID, 15, 13, @.on
exec sp_trace_setevent @.TraceID, 15, 14, @.on
exec sp_trace_setevent @.TraceID, 15, 16, @.on
exec sp_trace_setevent @.TraceID, 15, 17, @.on
exec sp_trace_setevent @.TraceID, 15, 18, @.on
exec sp_trace_setevent @.TraceID, 15, 40, @.on
exec sp_trace_setevent @.TraceID, 15, 41, @.on
exec sp_trace_setevent @.TraceID, 20, 1, @.on
exec sp_trace_setevent @.TraceID, 20, 6, @.on
exec sp_trace_setevent @.TraceID, 20, 7, @.on
exec sp_trace_setevent @.TraceID, 20, 9, @.on
exec sp_trace_setevent @.TraceID, 20, 10, @.on
exec sp_trace_setevent @.TraceID, 20, 11, @.on
exec sp_trace_setevent @.TraceID, 20, 12, @.on
exec sp_trace_setevent @.TraceID, 20, 13, @.on
exec sp_trace_setevent @.TraceID, 20, 14, @.on
exec sp_trace_setevent @.TraceID, 20, 16, @.on
exec sp_trace_setevent @.TraceID, 20, 17, @.on
exec sp_trace_setevent @.TraceID, 20, 18, @.on
exec sp_trace_setevent @.TraceID, 20, 40, @.on
exec sp_trace_setevent @.TraceID, 20, 41, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
go
John
"Dom" <post.to.group@.newsgroup.com> wrote in message
news:zOthe.3865$Pi3.3156@.newsfe4-win.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in
> news:OY$JDIJWFHA.3488@.tk2msftngp13.phx.gbl:
>> Hi
>> In profiler the data fields that will appear will depend on the
>> template you choose. You can add the data columns needed from the
>> subsequent dialog. If you get the columns/events working in profiler,
>> you can use the Script Trace on the file menu to give you the SQL
>> needed to re-create that profile.
>> At a guess you are probably not calling the sp_trace_setevent for the
>> correct event/column combinations.
>> John
>>
>> "Dom" <post.to.group@.newsgroup.com> wrote in message
>> news:IT6he.4063$8K5.1665@.newsfe3-win.ntli.net...
>>I would be really grateful if someone would help me with this as I'm
>> really stumped...
>> I've written a stored procedure to enable me to kick off traces from
>> the SQL Agent and they run just fine - the problem is that some of
>> the fields that should have data in them just return null. For
>> example I run one to audit logins and the debug info I added tells me
>> that the events/columns below are being traced (it picks up what it
>> should trace from a table I query in the stored procedure). But when
>> I look at the trace output columns such as NTUserName,
>> ClientHostName, etc, etc they are always null (pretty much the only
>> thing that does get populated is TextData, SPID and ServerName).
>> Interesting the fields don't even appear when I open the file in
>> Profiler but do show up as null if open the file using
>> ::fn_trace_gettable.
>> If I run a trace using SQL Profiler then it shows all the fields as
>> it should! Any help would be much appreciated, and let me know if you
>> want me to post the full stored procedure. I'm using SQL 2000 on a
>> Windows 2003 server.
>> To massively summarise the stored procedure, here it is
>> sp_trace_create @.TraceID output, 2, @.traceFilename, @.maxfilesize,
>> NULL -- pull the events to be traced from a table
>> exec sp_trace_setevent @.TraceID, @.eventNumber, 1, @.on
>> -- pull the columns to be reported on from a table
>> exec sp_trace_setevent @.TraceID, 0, @.ColumnNumber, @.on
>> -- Set the trace status to start
>> exec sp_trace_setstatus @.TraceID, 1
>> Here is the debug info I added (just spits out the events and the
>> columns it traces
>> Trace Event Info
>> --
>> Trace Events for session
>> (1 rows(s) affected)
>> EventNumber Category EventName EventDescription
>> -- -- --
>> -- 14 session Login
>> Occurs when a use 15 session Logout
>> Occurs when a use 20 session Login Failed
>> Indicates that a
>> (3 rows(s) affected)
>> Trace Column Info
>> ---
>> Trace Columns for session
>> (1 rows(s) affected)
>> Category ColumnNumber ColumnName
>> ColumnDescription -- --
>> -- -- all 1
>> TextData Text value depen all 3
>> DatabaseID ID of the databa all 6
>> NTUserName Microsoft Window all 8
>> ClientHostName Name of the clie all 9
>> ClientProcessID ID assigned by t all 10
>> ApplicationName Name of the clie all 11
>> SQLSecurityLoginName SQL Server login all 12
>> SPID Server Process I all 13
>> Duration Amount of elapse
>> (24 rows(s) affected)
>> TraceID
>> --
>> 5
>> Thanks very much for all your help and apologies if this is a dumb
>> question but it has me stumped!
>> Dom
>>
> Thanks very much for the reply. There is a chance I could be looking at
> the wrong columns but the debug I put into my stored procedure shows that
> I'm at least adding the column to the trace (NTUserName for instance),
> and it does show up as a field in ::fn_trace_gettable which I wouldn't
> have thought it would unless the trace is doing something with it... I
> just can't figure why it always comes up as null. Is there anyway to get
> info from an active trace i.e. which columns are being recorded?
> TIA
> Dom
Subscribe to:
Posts (Atom)