Number of Updated Rows

I want to get the number of rows updated by an update statement in a stored procedure. Is there a way to do this?

Example:

Declare @.NumRow int;

set @.NumRow = udpate table set Name = 'John Doe' where ID = 123456789;

print @.NumRow;

The above doesn't work.

use the @.@.ROWCOUNT internal; something like:

declare @.updateRows integer

UPDATE yourTable
SET ...
WHERE ...
SET @.updateRows = @.@.ROWCOUNT

Dave

|||

Oh yea, a hidden gotcha:

You need to be careful and make sure that the @.@.ROWCOUNT internal is used IMMEDIATELY after the update statement so that its value does not change. You may get surprised if you have any steps between your update statement and where you used @.@.ROWCOUNT.

Dave

|||Yeah, @.@.rowcount is very good that way.

Something else you might want to be aware of is that you can output the rows from an update query. This gives you the chance to do all kinds of extra analysis on what's changed - not just the number of rows, but all kinds of other details too.

Have a look through http://msdn2.microsoft.com/en-us/ms177564.aspx to see some of the things you can get up to.

Robsql

number of times procedure executes per second

hello all,

i have run into this problems a few times and don't really have a quick solution.

management wants to know how many times a procedure will execute in a second. i only have one procedure in mind and all it does is do a few different select and then returns a record set. so i am looking for a tool which will report how many times my "return customer profile" procedure can run. I have a tool which will execute a single procedure on multiple threads, but it has no reporting. If I have something to execute the procedure how can I report back on it's performance?

In perfmon there is a "transaction per second" however this does not count selects. so it is always 0.

Is there any native sql 2000 2005 tool to do this?

Is there any inexpensive 3 party tool which people use?

thanks in advance.

In SQL 2005 if you run the SP then one of the standard reports in management studio should tell you how many times it has run. You know how long the test was and thus how many times the SP can run /s.

You can also look at the SQL Batches/sec in perfmon, this should give you the figure you are after as long as that is the only thing you will be doing on the server.

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.

Number formatting

Here's a quickie - I have a stored procedure which returns a list of numbers.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,95000),1),LEN(Convert(varchar(12),Convert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend :)
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> > Here's a quickie - I have a stored procedure which returns a list of
> numbers.
> > I want the output formatted with commas so 95,000 instead of 95000 - how
> > would I do that?
> Do it at the client, not at the server.
>
>

Number formatting

Here's a quickie - I have a stored procedure which returns a list of numbers
.
I want the output formatted with commas so 95,000 instead of 95000 - how
would I do that?"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> Here's a quickie - I have a stored procedure which returns a list of
numbers.
> I want the output formatted with commas so 95,000 instead of 95000 - how
> would I do that?
Do it at the client, not at the server.|||Joe,
here is an attempt at doing it, this is definitely an overkill and can
reduce the performance in case you have a lot of data coming back,
Select
LEFT(Convert(varchar(12),Convert(money,9
5000),1),LEN(Convert(varchar(12),Con
vert(money,95000),1)) - 3)
I would agree with Greg it will be simpler & more efficient on the
front-end. But I just had to try and do it in the backend
Enjoy,
Rakesh Ajwani
MCSD, MCSD.NET
"Greg D. Moore (Strider)" wrote:

> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:F11D15E1-2FFB-4ADE-85C3-F5C97E278214@.microsoft.com...
> numbers.
> Do it at the client, not at the server.
>
>

Nulls in stored procedure

I have a stored procedure that is using Like and a parameter to query by Social Security Number. Not all of my records have SSN's, so some are null.

When I run the stored procedure and use the "%" to show everything, it does not show any records that are null. I have tried to use the IsNull funtion to give the null values a value of 'none', but that did not help.

Any help would be appreciated.Originally posted by shearness
I have a stored procedure that is using Like and a parameter to query by Social Security Number. Not all of my records have SSN's, so some are null.

When I run the stored procedure and use the "%" to show everything, it does not show any records that are null. I have tried to use the IsNull funtion to give the null values a value of 'none', but that did not help.

Any help would be appreciated.
assign default value % to the parameter|||If you want to display records with NULL SSN (even though they should not be, unless I didn't get your requirements right) then just do ISNULL(SSN, @.SSN)|||I want to be able to search the SSN field for anything. For example:
last four digits 00
entire field 000-00-0000
or
if I don't know the social, I want to be able to use just % to display all social security #'s, even those with null values.

Does this make sense?|||Also, I have tried both of your posts and I am having the same problem. When the message box pops up asking for me to specify a value for SSN, I enter %, which in theory should display all records. Instead, it is only displaying records that do not have a null value for SSN. How do I get the % to include even those that have null values? Thanks in advance for any help.|||Originally posted by shearness
Also, I have tried both of your posts and I am having the same problem. When the message box pops up asking for me to specify a value for SSN, I enter %, which in theory should display all records. Instead, it is only displaying records that do not have a null value for SSN. How do I get the % to include even those that have null values? Thanks in advance for any help.
I think now I got it. I guess some of the records's SSN are NULL. If that is correct those records will be elimated no matter what you specify in the where clause.

TRYING USING

SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END

FROM TABLE1
WHERE
(
CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END) LIKE 'PARAM1%'

THIS IS NOT A COMPELE CODE. You have do decalare a variable and use it in the like operator

Hope it helps|||Originally posted by smasanam
I think now I got it. I guess some of the records's SSN are NULL. If that is correct those records will be elimated no matter what you specify in the where clause.

TRYING USING

SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END

FROM TABLE1
WHERE
(
CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END) LIKE 'PARAM1%'

THIS IS NOT A COMPELE CODE. You have do decalare a variable and use it in the like operator

Hope it helps

Sorry for my typo errors in the previous reply|||if @.PARAM1 is null
set @.PARAM1 = '%'
else
set @.PARAM1 = '%' + ltrim(rtrim(@.PARAM1)) + '%'

SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END

FROM TABLE1
WHERE ISNULL(SSN, @.PARAM1) LIKE @.PARAM1|||Mind you though, that using this approach may yield very undesireable results in terms of performance, because you may be returning ALL the rows in the table. Think about limiting your search range to a reasonable number of rows.|||I'm not able to use either of your code in my stored procedure. I'm get errors whenever I try to input it and save it. I'll admit, I'm new to this function, but I'm really stumped, it seems easy enough. Please help me draft this stored procedure so I can leave you alone.|||Okay, I've got the code in correctly, but I have one more question. I am able to view all entries now when I use %. But now, I am unable to limit the search to whatever I specified in the parameter box. How do I get the best of both worlds? Again, I want to be able to input any of the following in the parameter box and have the results:

% shows all records
321% shows all records with SSN that start with 321 only
321456789 shows only the one record with that SSN

Thanks again for all of your help.|||If you choose to control the contents of @.PARAM1 in the front-end, then the following should work:

SELECT CASE WHEN ISNULL(SSN, '-1') = '-1' THEN 'NO SSN'
ELSE SSN
END

FROM TABLE1
WHERE ISNULL(SSN,
case when datalength(@.PARAM1) != 1 then SSN else @.PARAM1 end
) LIKE @.PARAM1|||That's got it. I would ask for an explanation of what everything means, but you have done plenty. Thank you again for all of your help. That is exactly what I wanted.

NullPointerException in CallableStatement

Hi,

First of all sorry for my pour english.

My development work is SqlServer 2000 + Web Server + JDBC
I have in my db a simple store procedure which inserts in a table rows.
My problem is when I try to insert over 1030 rows more or less. In my program the JDBC returns to me a NullPointerException in the Connection object, but when i try to insert for example 100 rows everything is ok.

I have try it with some web servers like JRun, Tomcat, etc.. and with differents jdbc too and its the same, so I think that the problem is in my database.

This Store Procedure doesnt work:
------------
declare @.i int
set @.i = 0
while @.i<10000
begin
insert into mig values (getdate(), 'p', @.i)
set @.i = @.i + 1
end
--------

And this Store Procedure works:
--------
declare @.i int
set @.i = 0
while @.i<1000 (<1030 +o-)
begin
insert into mig select getdate(), 'p', 1 from sanresco where reciae between 1 and 100000
set @.i = @.i + 1
end
----------

So the problem is when the db tries to do over 1023 individual inserts.

Any idea??

Thanks.Sure it does...

USE Northwind
GO

CREATE TABLE mig (col1 datetime, col2 char(1), col3 int)
GO

CREATE PROC mySPROC AS

SET NOCOUNT ON
DECLARE @.i int
SET @.i = 0
WHILE @.i < 10000
BEGIN
INSERT INTO mig (col1, col2, col3)
VALUES (getdate(), 'p', @.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

EXEC mySPROC
GO

SELECT COUNT(*) FROM mig
GO

DROP TABLE mig
GO

DROP PROC mySPROC
GO

Why not post the DDL of the table...any triggers? Are you timing out on the front end?|||Thanks a lot!!!!!!!
I put "set nocount on" in the sp and it works!!!!!
I had very long time ago with this problem.
Greetings.

Originally posted by Brett Kaiser
Sure it does...

USE Northwind
GO

CREATE TABLE mig (col1 datetime, col2 char(1), col3 int)
GO

CREATE PROC mySPROC AS

SET NOCOUNT ON
DECLARE @.i int
SET @.i = 0
WHILE @.i < 10000
BEGIN
INSERT INTO mig (col1, col2, col3)
VALUES (getdate(), 'p', @.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

EXEC mySPROC
GO

SELECT COUNT(*) FROM mig
GO

DROP TABLE mig
GO

DROP PROC mySPROC
GO

Why not post the DDL of the table...any triggers? Are you timing out on the front end?

NULL values handled differently in stored procedure

Can someone explain why the following is happening:
SELECT EventId, MDResponse FROM tblEvent
WHERE (MDResponse NOT IN(1, 3))
ORDER BY MDResponse
If I run the above statement in Query Analyzer it returns
all rows where MDResponse is not 1 or 3. No NULL
MDResponse rows are returned. This is what I expected.
If I run it as a stored procedure it also returns the
NULL valued rows.
Why the difference?
Thanks
MikeLooks like your SP was created with the setting SET ANSI_NULLS OFF. The
setting is persisted with the SP.
Re-create the proc with SET ANSI_NULLS ON:
SET ANSI_NULLS ON
GO
CREATE PROC ...
--
David Portas
SQL Server MVP
--|||It's possible this is due to the infamous ANSI-NULL handling, which the
analyzer sets to a default value that is different than the server itself
(IIRC). For instance the analyzer will handle xxx != null, whereas that will
fail in a storedproc. That said, I can't imagine why you'd get this
particular case if that was the problem. See about turning ANSI NULL off in
the analyzer, and see if you suddenly get the nulls in there too.
I'd also like to point out that the performance guide says anything with a
NOT is slower.

Null Value

Hello,

I have SQL 2005 stored procedure to generate parameters in the report.

one of the stored procedure parameters need to be null (one of the @. in the SP)

How can I send null value to the stored procedure ?

Currently I create a new report parameters with default value NULL that it is internal (hide from the user) and I point the parameters in the SP need to be null to this parameters.

This look not a nice solution.

any other solution ?

Have you tried "=Nothing" as the value of the SP parameter?

Null Paramemter in Store Procedure

Hi,
I am sending a an argument to a store procedure, I will
add a value to the "where" condition depending of that
parameter.
For example, the parameter is name, if i send "name1" I
will add "AND name = 'name'" to the store produre
(dynamic sql).
Is there a way to do it without concatenating it to a
nvarchar?
like "where name = *" or sometign like this?
Thanks a lot for your help!!
JCWithout dynamic SQL:
WHERE @.name IS NULL OR name = @.name
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:03eb01c34431$51a993a0$a301280a@.phx.gbl...
> Hi,
> I am sending a an argument to a store procedure, I will
> add a value to the "where" condition depending of that
> parameter.
> For example, the parameter is name, if i send "name1" I
> will add "AND name = 'name'" to the store produre
> (dynamic sql).
> Is there a way to do it without concatenating it to a
> nvarchar?
> like "where name = *" or sometign like this?
> Thanks a lot for your help!!
> JC|||For a detailed list of options refer to:
http://www.algonet.se/~sommar/dyn-search.html
--
- Anith
( Please reply to newsgroups only )|||Hi JC,
You may try something like this:
ISNULL( 'AND name = ''' + @.name + ''', '')
then, if @.name is null, the condition won't appear in WHERE clause... hope
this help
--
---
Charlie Yukio Nakagawa
---
"Juan Carlos" <jcarlos_mn@.hotmail.com> escreveu na mensagem
news:03eb01c34431$51a993a0$a301280a@.phx.gbl...
> Hi,
> I am sending a an argument to a store procedure, I will
> add a value to the "where" condition depending of that
> parameter.
> For example, the parameter is name, if i send "name1" I
> will add "AND name = 'name'" to the store produre
> (dynamic sql).
> Is there a way to do it without concatenating it to a
> nvarchar?
> like "where name = *" or sometign like this?
> Thanks a lot for your help!!
> JC

Null Filters?

Hi all,
My report consists of a matrix and displays results from a stored
procedure.
One of the groups is called country. The matrix displays countries
with all the products in that country.
By default, all the countries are shown, but I want to be able to
filter by country from a parameter.
I created a parameter and set it to allow nulls as I also want to be
able to display all countries.
Then I set the country field to be equal to the parameter. The filter
works if i suppoly a value for the filter (from parameter), but if i
leave the parameter (for the filter) blank, then it does not return
any rows at all. If i leave the parameter blank, then the report
should ignore the filter. Is this possible?
Thanks in advance,
KSFrom the description I assume your current filter expression is similar to
this:
FilterExpr: =Fields!Country.Value
Operator: =FilterValue: =Parameters!Country.Value
Change it to the following:
FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
Fields!Country.Value=Parameters!Country.Value)
Operator: =FilterValue: =true
This assumes that the user sets the parameter to Null. Consequently, the
filter expression will then evaluate to true for all countries and they will
be shown in the report.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saleek" <saleem75k@.hotmail.com> wrote in message
news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> Hi all,
> My report consists of a matrix and displays results from a stored
> procedure.
> One of the groups is called country. The matrix displays countries
> with all the products in that country.
> By default, all the countries are shown, but I want to be able to
> filter by country from a parameter.
> I created a parameter and set it to allow nulls as I also want to be
> able to display all countries.
> Then I set the country field to be equal to the parameter. The filter
> works if i suppoly a value for the filter (from parameter), but if i
> leave the parameter (for the filter) blank, then it does not return
> any rows at all. If i leave the parameter blank, then the report
> should ignore the filter. Is this possible?
> Thanks in advance,
>
> KS|||Hi Robert,
Thanks for getting back to me on this. I have tried the expression you
showed, but it is giving run-time error. The error is below:
"An error has ocurred during the report processing.
The processing of filter expression for the matrix 'Stats' cannot be
performed. The comparison failed. Please check the data type reutrned by
filter expression."
I assume that the "true" part of the expression is cusing problems'
thanks,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Sorry Robert,
It did actually work, I had left out the equals sign before the "true" for
the value of the filter. I have noticed that it is case sensitive tho.
thanks for your help,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Another question if you dont mind!
I am working with the ReportViewer component and can pass parameter from
asp.net page to it, but it only allows me to bind once - if I click the
button to bind again with different parameter it does not work. It just shows
the component default "To render a report, enter the ServerUrl and
ReportPath."
Below is some code I use:
ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
ReportViewer1.ReportPath = "/testing/Stats"
ReportViewer1.SetQueryParameter("theTitle", prm1)
ReportViewer1.DataBind()
The above code works once only, if I click to run the above code again, the
component does not reload or rebind it sits there as if the code above hasnt
even run.
Any help appreciated.
regards,
KS
"Robert Bruckner [MSFT]" wrote:
> From the description I assume your current filter expression is similar to
> this:
> FilterExpr: =Fields!Country.Value
> Operator: => FilterValue: =Parameters!Country.Value
> Change it to the following:
> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> Fields!Country.Value=Parameters!Country.Value)
> Operator: => FilterValue: =true
> This assumes that the user sets the parameter to Null. Consequently, the
> filter expression will then evaluate to true for all countries and they will
> be shown in the report.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saleek" <saleem75k@.hotmail.com> wrote in message
> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> > Hi all,
> >
> > My report consists of a matrix and displays results from a stored
> > procedure.
> >
> > One of the groups is called country. The matrix displays countries
> > with all the products in that country.
> >
> > By default, all the countries are shown, but I want to be able to
> > filter by country from a parameter.
> >
> > I created a parameter and set it to allow nulls as I also want to be
> > able to display all countries.
> >
> > Then I set the country field to be equal to the parameter. The filter
> > works if i suppoly a value for the filter (from parameter), but if i
> > leave the parameter (for the filter) blank, then it does not return
> > any rows at all. If i leave the parameter blank, then the report
> > should ignore the filter. Is this possible?
> >
> > Thanks in advance,
> >
> >
> > KS
>
>|||Where does the code live? In a button click event handler or page load
or elsewhere?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 20 Oct 2004 02:43:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Another question if you dont mind!
>I am working with the ReportViewer component and can pass parameter from
>asp.net page to it, but it only allows me to bind once - if I click the
>button to bind again with different parameter it does not work. It just shows
>the component default "To render a report, enter the ServerUrl and
>ReportPath."
>Below is some code I use:
> ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
> ReportViewer1.ReportPath = "/testing/Stats"
> ReportViewer1.SetQueryParameter("theTitle", prm1)
> ReportViewer1.DataBind()
>The above code works once only, if I click to run the above code again, the
>component does not reload or rebind it sits there as if the code above hasnt
>even run.
>Any help appreciated.
>regards,
>KS
>"Robert Bruckner [MSFT]" wrote:
>> From the description I assume your current filter expression is similar to
>> this:
>> FilterExpr: =Fields!Country.Value
>> Operator: =>> FilterValue: =Parameters!Country.Value
>> Change it to the following:
>> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
>> Fields!Country.Value=Parameters!Country.Value)
>> Operator: =>> FilterValue: =true
>> This assumes that the user sets the parameter to Null. Consequently, the
>> filter expression will then evaluate to true for all countries and they will
>> be shown in the report.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>> "saleek" <saleem75k@.hotmail.com> wrote in message
>> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
>> > Hi all,
>> >
>> > My report consists of a matrix and displays results from a stored
>> > procedure.
>> >
>> > One of the groups is called country. The matrix displays countries
>> > with all the products in that country.
>> >
>> > By default, all the countries are shown, but I want to be able to
>> > filter by country from a parameter.
>> >
>> > I created a parameter and set it to allow nulls as I also want to be
>> > able to display all countries.
>> >
>> > Then I set the country field to be equal to the parameter. The filter
>> > works if i suppoly a value for the filter (from parameter), but if i
>> > leave the parameter (for the filter) blank, then it does not return
>> > any rows at all. If i leave the parameter blank, then the report
>> > should ignore the filter. Is this possible?
>> >
>> > Thanks in advance,
>> >
>> >
>> > KS
>>|||Hi Scott,
Yes the code lives in the button click event handler.
thx,
KS
"Scott Allen" wrote:
> Where does the code live? In a button click event handler or page load
> or elsewhere?
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 20 Oct 2004 02:43:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Another question if you dont mind!
> >
> >I am working with the ReportViewer component and can pass parameter from
> >asp.net page to it, but it only allows me to bind once - if I click the
> >button to bind again with different parameter it does not work. It just shows
> >the component default "To render a report, enter the ServerUrl and
> >ReportPath."
> >
> >Below is some code I use:
> >
> > ReportViewer1.ServerUrl = "http://www.doneitfor.com/reportserver"
> > ReportViewer1.ReportPath = "/testing/Stats"
> > ReportViewer1.SetQueryParameter("theTitle", prm1)
> > ReportViewer1.DataBind()
> >
> >The above code works once only, if I click to run the above code again, the
> >component does not reload or rebind it sits there as if the code above hasnt
> >even run.
> >
> >Any help appreciated.
> >
> >regards,
> >
> >KS
> >
> >"Robert Bruckner [MSFT]" wrote:
> >
> >> From the description I assume your current filter expression is similar to
> >> this:
> >>
> >> FilterExpr: =Fields!Country.Value
> >> Operator: => >> FilterValue: =Parameters!Country.Value
> >>
> >> Change it to the following:
> >>
> >> FilterExpr: =iif(Parameters!Country.Value is Nothing, true,
> >> Fields!Country.Value=Parameters!Country.Value)
> >> Operator: => >> FilterValue: =true
> >>
> >> This assumes that the user sets the parameter to Null. Consequently, the
> >> filter expression will then evaluate to true for all countries and they will
> >> be shown in the report.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
> >> "saleek" <saleem75k@.hotmail.com> wrote in message
> >> news:e1e85a95.0410190307.4d5f0342@.posting.google.com...
> >> > Hi all,
> >> >
> >> > My report consists of a matrix and displays results from a stored
> >> > procedure.
> >> >
> >> > One of the groups is called country. The matrix displays countries
> >> > with all the products in that country.
> >> >
> >> > By default, all the countries are shown, but I want to be able to
> >> > filter by country from a parameter.
> >> >
> >> > I created a parameter and set it to allow nulls as I also want to be
> >> > able to display all countries.
> >> >
> >> > Then I set the country field to be equal to the parameter. The filter
> >> > works if i suppoly a value for the filter (from parameter), but if i
> >> > leave the parameter (for the filter) blank, then it does not return
> >> > any rows at all. If i leave the parameter blank, then the report
> >> > should ignore the filter. Is this possible?
> >> >
> >> > Thanks in advance,
> >> >
> >> >
> >> > KS
> >>
> >>
> >>
>|||Hi saleek:
I'm not sure what could be going wrong, but I would remove the call to
DataBind as this isn't needed with the ReportViewer component.
Here is a simple form that has been working for me. I can enter a new
parameter for a reports (say /SampleReports/Sales Order Detail) and
the report viewer rerenders the report.
<form id="Form1" method="post" runat="server">
<cc1:ReportViewer id="ReportViewer1"
runat="server" Width="600px" Height="400px"/>
<asp:Button id="Button1" runat="server" Text="Button"/>
<asp:TextBox id="TextBox1" runat="server"/>
<asp:TextBox id="TextBox2" runat="server"/>
</form>
And in code behind:
private void Button1_Click(object sender, System.EventArgs e)
{
ReportViewer1.ServerUrl = "http://localhost/reportserver";
ReportViewer1.ReportPath = TextBox1.Text;
if(TextBox2.Text.Length > 0)
{
ReportViewer1.SetQueryParameter(
"SalesOrderNumber",
TextBox2.Text
);
}
}
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Thu, 21 Oct 2004 01:49:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Yes the code lives in the button click event handler.
>thx,
>KS
>"Scott Allen" wrote:
>> Where does the code live? In a button click event handler or page load
>> or elsewhere?
>> --|||Hi Scott,
Having looked at my code I realised that I had some incorrect logic going on!
What I would like to know is this...
When the page is posted back by another button control, the reportviewer
seems to render to nothing and displays the caption about server url and path
etc. I would like the reportviewer to maintain its state when the page is
posted back by another button.
What I am trying to do is:
-User has a drop down list of options
-User selects an option then clicks an "ADD" button.
-the "ADD" button runs a sub to add the value of the drop down list to a
listbox
-Next to the listbox control is a "REMOVE" button
The idea is that the user can add and remove values as he/she desires into
the listbox and when they are ready they can click a "SUMMARY" button to run
the report.
What is happening now is that when i initially run the report it is fine.
When I add or remove and item from the listbox control, the viewer seems to
reset.
How can I stop this from happening?
Thanks for your time.
regards,
KS
"Scott Allen" wrote:
> Hi saleek:
> I'm not sure what could be going wrong, but I would remove the call to
> DataBind as this isn't needed with the ReportViewer component.
> Here is a simple form that has been working for me. I can enter a new
> parameter for a reports (say /SampleReports/Sales Order Detail) and
> the report viewer rerenders the report.
> <form id="Form1" method="post" runat="server">
> <cc1:ReportViewer id="ReportViewer1"
> runat="server" Width="600px" Height="400px"/>
> <asp:Button id="Button1" runat="server" Text="Button"/>
> <asp:TextBox id="TextBox1" runat="server"/>
> <asp:TextBox id="TextBox2" runat="server"/>
> </form>
> And in code behind:
> private void Button1_Click(object sender, System.EventArgs e)
> {
> ReportViewer1.ServerUrl = "http://localhost/reportserver";
> ReportViewer1.ReportPath = TextBox1.Text;
> if(TextBox2.Text.Length > 0)
> {
> ReportViewer1.SetQueryParameter(
> "SalesOrderNumber",
> TextBox2.Text
> );
> }
> }
> HTH,
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Hi Scott,
> >
> >Yes the code lives in the button click event handler.
> >
> >thx,
> >
> >KS
> >
> >"Scott Allen" wrote:
> >
> >> Where does the code live? In a button click event handler or page load
> >> or elsewhere?
> >>
> >> --
>|||Hi saleek:
Do you have ViewState enabled? Is there any logic in the Page Load
event handler that is touching the reportviewer on every postback?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 22 Oct 2004 02:01:03 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Having looked at my code I realised that I had some incorrect logic going on!
>What I would like to know is this...
>When the page is posted back by another button control, the reportviewer
>seems to render to nothing and displays the caption about server url and path
>etc. I would like the reportviewer to maintain its state when the page is
>posted back by another button.
>What I am trying to do is:
>-User has a drop down list of options
>-User selects an option then clicks an "ADD" button.
>-the "ADD" button runs a sub to add the value of the drop down list to a
>listbox
>-Next to the listbox control is a "REMOVE" button
>The idea is that the user can add and remove values as he/she desires into
>the listbox and when they are ready they can click a "SUMMARY" button to run
>the report.
>What is happening now is that when i initially run the report it is fine.
>When I add or remove and item from the listbox control, the viewer seems to
>reset.
>How can I stop this from happening?
>Thanks for your time.
>regards,
>KS
>"Scott Allen" wrote:
>> Hi saleek:
>> I'm not sure what could be going wrong, but I would remove the call to
>> DataBind as this isn't needed with the ReportViewer component.
>> Here is a simple form that has been working for me. I can enter a new
>> parameter for a reports (say /SampleReports/Sales Order Detail) and
>> the report viewer rerenders the report.
>> <form id="Form1" method="post" runat="server">
>> <cc1:ReportViewer id="ReportViewer1"
>> runat="server" Width="600px" Height="400px"/>
>> <asp:Button id="Button1" runat="server" Text="Button"/>
>> <asp:TextBox id="TextBox1" runat="server"/>
>> <asp:TextBox id="TextBox2" runat="server"/>
>> </form>
>> And in code behind:
>> private void Button1_Click(object sender, System.EventArgs e)
>> {
>> ReportViewer1.ServerUrl = "http://localhost/reportserver";
>> ReportViewer1.ReportPath = TextBox1.Text;
>> if(TextBox2.Text.Length > 0)
>> {
>> ReportViewer1.SetQueryParameter(
>> "SalesOrderNumber",
>> TextBox2.Text
>> );
>> }
>> }
>> HTH,
>> --
>> Scott
>> http://www.OdeToCode.com/blogs/scott/
>> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
>> <saleek@.discussions.microsoft.com> wrote:
>> >Hi Scott,
>> >
>> >Yes the code lives in the button click event handler.
>> >
>> >thx,
>> >
>> >KS
>> >
>> >"Scott Allen" wrote:
>> >
>> >> Where does the code live? In a button click event handler or page load
>> >> or elsewhere?
>> >>
>> >> --
>>|||Hi Scott,
Viewstate IS enabled on the reportviewer component and my page_load event is:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
ReportViewer1.Visible = False
'LastLoggedIn()
PassDLLsToBind()
End If
End Sub
Thanks,
KS
"Scott Allen" wrote:
> Hi saleek:
> Do you have ViewState enabled? Is there any logic in the Page Load
> event handler that is touching the reportviewer on every postback?
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
>
> On Fri, 22 Oct 2004 02:01:03 -0700, saleek
> <saleek@.discussions.microsoft.com> wrote:
> >Hi Scott,
> >
> >Having looked at my code I realised that I had some incorrect logic going on!
> >
> >What I would like to know is this...
> >
> >When the page is posted back by another button control, the reportviewer
> >seems to render to nothing and displays the caption about server url and path
> >etc. I would like the reportviewer to maintain its state when the page is
> >posted back by another button.
> >
> >What I am trying to do is:
> >
> >-User has a drop down list of options
> >-User selects an option then clicks an "ADD" button.
> >-the "ADD" button runs a sub to add the value of the drop down list to a
> >listbox
> >-Next to the listbox control is a "REMOVE" button
> >
> >The idea is that the user can add and remove values as he/she desires into
> >the listbox and when they are ready they can click a "SUMMARY" button to run
> >the report.
> >
> >What is happening now is that when i initially run the report it is fine.
> >When I add or remove and item from the listbox control, the viewer seems to
> >reset.
> >
> >How can I stop this from happening?
> >
> >Thanks for your time.
> >
> >regards,
> >
> >KS
> >
> >"Scott Allen" wrote:
> >
> >> Hi saleek:
> >>
> >> I'm not sure what could be going wrong, but I would remove the call to
> >> DataBind as this isn't needed with the ReportViewer component.
> >>
> >> Here is a simple form that has been working for me. I can enter a new
> >> parameter for a reports (say /SampleReports/Sales Order Detail) and
> >> the report viewer rerenders the report.
> >>
> >> <form id="Form1" method="post" runat="server">
> >> <cc1:ReportViewer id="ReportViewer1"
> >> runat="server" Width="600px" Height="400px"/>
> >> <asp:Button id="Button1" runat="server" Text="Button"/>
> >> <asp:TextBox id="TextBox1" runat="server"/>
> >> <asp:TextBox id="TextBox2" runat="server"/>
> >> </form>
> >>
> >> And in code behind:
> >>
> >> private void Button1_Click(object sender, System.EventArgs e)
> >> {
> >> ReportViewer1.ServerUrl = "http://localhost/reportserver";
> >> ReportViewer1.ReportPath = TextBox1.Text;
> >>
> >> if(TextBox2.Text.Length > 0)
> >> {
> >> ReportViewer1.SetQueryParameter(
> >> "SalesOrderNumber",
> >> TextBox2.Text
> >> );
> >> }
> >> }
> >>
> >> HTH,
> >>
> >> --
> >> Scott
> >> http://www.OdeToCode.com/blogs/scott/
> >>
> >> On Thu, 21 Oct 2004 01:49:03 -0700, saleek
> >> <saleek@.discussions.microsoft.com> wrote:
> >>
> >> >Hi Scott,
> >> >
> >> >Yes the code lives in the button click event handler.
> >> >
> >> >thx,
> >> >
> >> >KS
> >> >
> >> >"Scott Allen" wrote:
> >> >
> >> >> Where does the code live? In a button click event handler or page load
> >> >> or elsewhere?
> >> >>
> >> >> --
> >>
> >>
>|||Hi saleek:
The Page_Load event looks fine. I assume during other event handlers
you'll set the visibility to true and set the server and item path,
right?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 27 Oct 2004 06:21:05 -0700, saleek
<saleek@.discussions.microsoft.com> wrote:
>Hi Scott,
>Viewstate IS enabled on the reportviewer component and my page_load event is:
>Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles MyBase.Load
> 'Put user code to initialize the page here
> If Not Page.IsPostBack Then
> ReportViewer1.Visible = False
> 'LastLoggedIn()
> PassDLLsToBind()
> End If
> End Sub
>Thanks,
>KS
>

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

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

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