problem using System.Data.Oledb.OleDbDataAdapter for importing Excel

Hi All,

I am facing this weird problem of partial data being fetched when using oledbadapter for fetching data from a Excel spreadsheet. A few cells containing text values are not being fetched..I get an empty string. When i replace this text value by an integer, the problem is no more a problem...

Anyone having faced this problem or having knowledge about this..pls respond..

Thanks in advance

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from the application

Problem Using Stored Procedures in Report Datasets

I have a local Reporting Services report that I am modifying to use a stored procedure.

Although I am executing a stored procedure in the dataset query window, I also have to run a SELECT statement to retrieve the fields from a table that will populate the report.

The code that I have in the dataset query window looks like the following:

EXECUTE @.retCode = RunClaimVerification @.parmID, @.parmDate, @.parmRecordID OUTPUT

SELECT *

FROM ClaimsDetail

WHERE ClaimRecordID = @.parmRecordID

When I execute this code, the only results that are returned SEEM TO BE the return code associated with running the stored procedure.

I thought about putting the SELECT code in the stored procedure and returning a table or a cursor from the stored procedure BUT it looks like tables are not supported as Report Parameter data types.

The stored procedure code generates Claim data that is stored in a SQL Table. The fields in this SQL table need to be retrieved by a unique record id to populate the fields in the report.

Does anybody have any suggestions as to how to go about doing this OR any suggestions that would help me resolve this problem?

Reporting Services only allows one result (table or the return value of a stored procedure) to be retrieved per query. This is the reason that only the return code seems to be included in the dataset. Also, out parameters for stored procedures are not supported in Reporting Services.

Try changing the stored procedure to also Select the data from the ClaimsDetail table, and return the resultant table instead of the return code. However, don't set the return value to a parameter--just execute the stored procedure. This should produce a dataset containing the results of the Select statement.

Ian

Problem Using Stored Proc

Hi Folks.. I'm really new to SQL server

I'm writing a stored procedure as:

ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

Now when I run this stored procedure, the table just does not get affected.
But on the other hand when I run the query alone that is:
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

Thanks,
Shruti Majithia,
Quinnox Consultancy Services.Hi Shruti,

'ALTER PROC' is used to modify the existing stored procedure without changing the permissions and without affecting the dependent PROCs in the database. So the procedure should be already there in the database. You can use the following check:

if exists (select name from sysobjects where type = 'P' and name = 'usp_CDE_IssueProcedure1')
BEGIN
ALTER PROCEDURE usp_CDE_IssueProcedure1 AS
DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
END
else
BEGIN
CREATE PROCEDURE usp_CDE_IssueProcedure1 AS
DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
END
go|||I have the exactly same problem i think..
i have made an sp to update a table.
and everything works with no errors.
exept from that nothing happens.
i the SQL Profiler it says:

exec Content_update @.ContentID = 1, @.Content = N'some text'

but nothing happens

thx in advance

Originally posted by shrutimajithia
Hi Folks.. I'm really new to SQL server

I'm writing a stored procedure as:

ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

Now when I run this stored procedure, the table just does not get affected.
But on the other hand when I run the query alone that is:
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

Thanks,
Shruti Majithia,
Quinnox Consultancy Services.

problem using sql statement in Visual Studio 2005

thanks for read my question.
i have problem when i using sql statement in Visual Studio 2005:
for example:
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries',1;
GO
RECONFIGURE;
GO
with these statement i have put on separate line to exec, but if i but these statement on separate in VS2005 it is failed, what can i do?
how can i run file .sql from VS2005
HOW?
thanks a lot.

use the following statement to execute the .sql file from your sql server..

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Exec XP_CmdShell 'sqlcmd -SWXP-J72MM1S\MSSQL2005 -USA -Psqladmin -i"PATH OF THE .SQL FILE"'

|||it return error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

What can i do? how can i solve?
thanks
|||

Before executing the xp_cmdshell you have to change the server config.

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

To execute the above batch you shoule be a System Admin.

|||thanks for your answer but i can't get my result
it return 6 rows with output

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote c
onnections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
NULL

what must i do?|||

The cause may be,

#1. The server is not enabled the Remote Connection (if you are not executing your query from remote server leave this cause and goto #2). To enable the remote login goto Programs -> Microsoft SQL Server 2005 -> Configuration Tools and open the "SQL Server Surface Area Configuration" click the Service & Connections link.

Select the Remote Connections node under the data base engine and check the Local and Remote Connection radio button.

#2. The login you entered is incorrect. don't put more space between your password and -i

sqlcmd -SSERVERNAME -UUSERNAME -PPASSWORD -i"PATH OF THE FILE"

Problem using SQL Server Mobile 2005 with WM5 and Windows Vista

I have a problem using Remote Data Access (RDA) to connect SQL Server Mobile 3.0 and SQL Server Express 2005 in windows Vista. The problem is that I can't install SQL Server Windows Moble 3.0 because i get a message that no replication componets have been istalled. I thought that installing SQL Server Mobile would do that. Any ideas?

You need to install the SQL Server replication components as indicated. See this page for detailed instructions:

http://www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx

|||

Thanks EricEJ,

I have already tried this. If you take a closer look at the link that you sent me you will see that Windows Vista OS is not included. Any other ideas?

|||You can install SQL 2005 Developer edition replication tools on Vista. (The page is at bit outdated, correct, but SQL CE 3.1 IS supported on Vista)|||Yes but SQLCe 3.1 is for SQL Server Compact Edition and not SQL Mobile 2005. Am I right or wrong?|||The server tools (which require the replication components to be installed) will work both with SQL Mobile (3.0) and Compact Edition (3.1)|||I have exactly the same problem on an XP machine the tools install as you would expect however on the Vista Machine it will not. Any assistance would be greatly appreciated.|||

Please see this KB article for issues regarding installation on Vista: http://support.microsoft.com/Default.aspx?kbid=920700

Look at the heading called Known issues for SQL Server Compact Edition 3.1 on Windows Vista.

I quote from same KB article: "The SQL Server Compact Edition Runtime is supported on Windows Vista."

|||Eric that was just what i needed to get it running:

Issue 2

When you run SQL Server Compact Edition Server Tools (Sqlce30setupen.msi) Setup under Least Privilege to User Accounts (LUA) mode on Windows Vista, you receive the following error message:

SQL Server Replication Components not found
This issue occurs even if the SQL Server replication components are present. When you run the Sqlce30setupen.msi file, you receive a message that asks you to use elevated privileges. However, even after you click Yes in the message, you still experience this issue.

Workaround for issue 2

Open a Command Prompt window that has elevated privileges, and then run the Sqlce30setupen.msi file.

|||Happy to be able to help.

Problem using SQL Server Mobile 2005 with WM5 and Windows Vista

I have a problem using Remote Data Access (RDA) to connect SQL Server Mobile 3.0 and SQL Server Express 2005 in windows Vista. The problem is that I can't install SQL Server Windows Moble 3.0 because i get a message that no replication componets have been istalled. I thought that installing SQL Server Mobile would do that. Any ideas?

You need to install the SQL Server replication components as indicated. See this page for detailed instructions:

http://www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx

|||

Thanks EricEJ,

I have already tried this. If you take a closer look at the link that you sent me you will see that Windows Vista OS is not included. Any other ideas?

|||You can install SQL 2005 Developer edition replication tools on Vista. (The page is at bit outdated, correct, but SQL CE 3.1 IS supported on Vista)|||Yes but SQLCe 3.1 is for SQL Server Compact Edition and not SQL Mobile 2005. Am I right or wrong?|||The server tools (which require the replication components to be installed) will work both with SQL Mobile (3.0) and Compact Edition (3.1)|||I have exactly the same problem on an XP machine the tools install as you would expect however on the Vista Machine it will not. Any assistance would be greatly appreciated.|||

Please see this KB article for issues regarding installation on Vista: http://support.microsoft.com/Default.aspx?kbid=920700

Look at the heading called Known issues for SQL Server Compact Edition 3.1 on Windows Vista.

I quote from same KB article: "The SQL Server Compact Edition Runtime is supported on Windows Vista."

|||Eric that was just what i needed to get it running:

Issue 2

When you run SQL Server Compact Edition Server Tools (Sqlce30setupen.msi) Setup under Least Privilege to User Accounts (LUA) mode on Windows Vista, you receive the following error message:

SQL Server Replication Components not found
This issue occurs even if the SQL Server replication components are present. When you run the Sqlce30setupen.msi file, you receive a message that asks you to use elevated privileges. However, even after you click Yes in the message, you still experience this issue.

Workaround for issue 2

Open a Command Prompt window that has elevated privileges, and then run the Sqlce30setupen.msi file.

|||Happy to be able to help.

Problem using SQL Server Mobile 2005 with Windows Mobile 5 application

Hello,

I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database.

The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration.

The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK.
So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h".

Did I miss something to install ?
Do you know how I can resolve the problem ?

By advance, thank you for any answer.

--
Gregoire

May be http://support.microsoft.com/default.aspx?scid=kb;en-us;914174&sd=rss&spid=2852 can help you!

If this link does not help you much, please let us know!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I am having the same problem and no that article does not help.

I am developing a native application which targeted for a Windows Mobile 5.0 device and which must communicate with a SQL Mobile 2005 database.

I have used the include ssceoledb30.h and get a compile error about a missing include "transact.h". I did a search and sure enough, this file is not included in the WM5 SDK.

I copied the version included with the PocketPC2003 SDK and it now compiles but it doesn't give me much confidence.

Any help here would be greatly appreciated.

Bill

|||

I really do not understand what you meant by "does not give me much confidence". If you are facing any issue, please post back.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||

I manually copied a file from an older version of SDK to get the newer version to compile and you don't understand why I don't have much confidence in it?

I would think the reasons for my lack of confidence would be obvious but let me give you a list:

- File may have changed for the new release but I have no way of knowing as it is not there.

- May lead to runtime errors which may not show up in testing.

- What would I have done if I only had the new SDK with the file missing? Should I have made up my own version of the missing file?

|||

All points are valid if transact.h is owned and shipped SQL Server Mobile. However,

1) transact.h is not produced/owned by SQL Server Mobile

2) It comes with Platform Builder. If you can install Platform Builder latest version, you would get that.

3) SQL CE 2.0, SQL Mobile 3.0 have been integrated with Platform Builder.

4) If you are using VS 2005, then there are SDK for Windows CE/Mobile 5.0 downloadable. If you download them, you should be able to get transact.h and you dont need to copy from another machine. Here are the downloads URLs which I am referring to:

http://www.microsoft.com/downloads/details.aspx?familyid=83a52af2-f524-4ec5-9155-717cbe5d25ed&displaylang=en

http://www.microsoft.com/downloads/details.aspx?familyid=dc6c00cb-738a-4b97-8910-5cd29ab5f8d9&displaylang=en

Irrespective of all this, I will check with VS team why are not they shipping this SDK files for Windows Mobile/CE 5.0 by default.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corp.

|||

Thanks for the links but I have the WM 5.0 SDK installed on my machine and as I said in my original post, the transact.h file is not part of the SDK installation for some reason. So none of this solves the problem.

I don't see how anything you said invalidates my points, just means I posted the problem in the wrong forum. Instead of asking the VS team why they don't distrubute the WM 5.0 SDK by default, maybe you should inform the SDK team that the file is missing in the distribution.

|||

Please excuse us for the inconvenience caused. I have followed it up with some teams and found that transact.h is not shipped with WM 5.0 SDK. This problem would be fixed in the next SDK release. Also, I found that transact.h has not changed from WM 4.x SDK to WM 5.x SDK. Hence, I would request you to use the transact.h from WM 4.x SDK. We are also working towards writing a KB article where all the relevant information would be kept along with transact.h download.

Thanks alot for pointing out this BUG to us.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corporation

|||

There was a bug in the WM5 SDK that caused the transact.h file not to be included. This has been fixed for future versions.
In the meantime, a workaround is to copy the transact.h that was part of the Pocket PC 2003 SDK to the WM5 headers.
(i.e. " \Program Files\Windows CE Tools\wce500\Windows Mobile 5.0 Pocket PC SDK\Include\Armv4i" ).

Thanks,

Luis E. Cabrera
Windows Mobile Developer

Problem using SQL Server Mobile 2005 with Windows Mobile 5 application

Hello,

I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database.

The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration.

The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK.
So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h".

Did I miss something to install ?
Do you know how I can resolve the problem ?

By advance, thank you for any answer.

--
Gregoire

May be http://support.microsoft.com/default.aspx?scid=kb;en-us;914174&sd=rss&spid=2852 can help you!

If this link does not help you much, please let us know!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I am having the same problem and no that article does not help.

I am developing a native application which targeted for a Windows Mobile 5.0 device and which must communicate with a SQL Mobile 2005 database.

I have used the include ssceoledb30.h and get a compile error about a missing include "transact.h". I did a search and sure enough, this file is not included in the WM5 SDK.

I copied the version included with the PocketPC2003 SDK and it now compiles but it doesn't give me much confidence.

Any help here would be greatly appreciated.

Bill

|||

I really do not understand what you meant by "does not give me much confidence". If you are facing any issue, please post back.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||

I manually copied a file from an older version of SDK to get the newer version to compile and you don't understand why I don't have much confidence in it?

I would think the reasons for my lack of confidence would be obvious but let me give you a list:

- File may have changed for the new release but I have no way of knowing as it is not there.

- May lead to runtime errors which may not show up in testing.

- What would I have done if I only had the new SDK with the file missing? Should I have made up my own version of the missing file?

|||

All points are valid if transact.h is owned and shipped SQL Server Mobile. However,

1) transact.h is not produced/owned by SQL Server Mobile

2) It comes with Platform Builder. If you can install Platform Builder latest version, you would get that.

3) SQL CE 2.0, SQL Mobile 3.0 have been integrated with Platform Builder.

4) If you are using VS 2005, then there are SDK for Windows CE/Mobile 5.0 downloadable. If you download them, you should be able to get transact.h and you dont need to copy from another machine. Here are the downloads URLs which I am referring to:

http://www.microsoft.com/downloads/details.aspx?familyid=83a52af2-f524-4ec5-9155-717cbe5d25ed&displaylang=en

http://www.microsoft.com/downloads/details.aspx?familyid=dc6c00cb-738a-4b97-8910-5cd29ab5f8d9&displaylang=en

Irrespective of all this, I will check with VS team why are not they shipping this SDK files for Windows Mobile/CE 5.0 by default.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corp.

|||

Thanks for the links but I have the WM 5.0 SDK installed on my machine and as I said in my original post, the transact.h file is not part of the SDK installation for some reason. So none of this solves the problem.

I don't see how anything you said invalidates my points, just means I posted the problem in the wrong forum. Instead of asking the VS team why they don't distrubute the WM 5.0 SDK by default, maybe you should inform the SDK team that the file is missing in the distribution.

|||

Please excuse us for the inconvenience caused. I have followed it up with some teams and found that transact.h is not shipped with WM 5.0 SDK. This problem would be fixed in the next SDK release. Also, I found that transact.h has not changed from WM 4.x SDK to WM 5.x SDK. Hence, I would request you to use the transact.h from WM 4.x SDK. We are also working towards writing a KB article where all the relevant information would be kept along with transact.h download.

Thanks alot for pointing out this BUG to us.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corporation

|||

There was a bug in the WM5 SDK that caused the transact.h file not to be included. This has been fixed for future versions.
In the meantime, a workaround is to copy the transact.h that was part of the Pocket PC 2003 SDK to the WM5 headers.
(i.e. " \Program Files\Windows CE Tools\wce500\Windows Mobile 5.0 Pocket PC SDK\Include\Armv4i" ).

Thanks,

Luis E. Cabrera
Windows Mobile Developer

Problem using SQL Server Mobile 2005 with Windows Mobile 5 application

Hello,

I am developping a non-managed C++ application for PocketPC using a SQL Server mobile database.

The application is compiled for PocketPC 2003 and uses SQL Server Mobile v2. I use Visual Studio 2005. But I need to compile the application for Windows Mobile 5.0 devices. So I installed the WM5 SDK and had the WM5 into my project configuration.

The "ssceoledb.h" which I include incluses the "transact.h" file. But my problem is that this file is only provided with the PocketPC 2003 SDK and not in the WM5 SDK.
So I cannot use the WM5 configuration project with SQL Server. I also tried with the last SQL Server Mobile 2005 (ie v3.0) and the "ssceoledb30.h" also includes "transact.h".

Did I miss something to install ?
Do you know how I can resolve the problem ?

By advance, thank you for any answer.

--
Gregoire

May be http://support.microsoft.com/default.aspx?scid=kb;en-us;914174&sd=rss&spid=2852 can help you!

If this link does not help you much, please let us know!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

I am having the same problem and no that article does not help.

I am developing a native application which targeted for a Windows Mobile 5.0 device and which must communicate with a SQL Mobile 2005 database.

I have used the include ssceoledb30.h and get a compile error about a missing include "transact.h". I did a search and sure enough, this file is not included in the WM5 SDK.

I copied the version included with the PocketPC2003 SDK and it now compiles but it doesn't give me much confidence.

Any help here would be greatly appreciated.

Bill

|||

I really do not understand what you meant by "does not give me much confidence". If you are facing any issue, please post back.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||

I manually copied a file from an older version of SDK to get the newer version to compile and you don't understand why I don't have much confidence in it?

I would think the reasons for my lack of confidence would be obvious but let me give you a list:

- File may have changed for the new release but I have no way of knowing as it is not there.

- May lead to runtime errors which may not show up in testing.

- What would I have done if I only had the new SDK with the file missing? Should I have made up my own version of the missing file?

|||

All points are valid if transact.h is owned and shipped SQL Server Mobile. However,

1) transact.h is not produced/owned by SQL Server Mobile

2) It comes with Platform Builder. If you can install Platform Builder latest version, you would get that.

3) SQL CE 2.0, SQL Mobile 3.0 have been integrated with Platform Builder.

4) If you are using VS 2005, then there are SDK for Windows CE/Mobile 5.0 downloadable. If you download them, you should be able to get transact.h and you dont need to copy from another machine. Here are the downloads URLs which I am referring to:

http://www.microsoft.com/downloads/details.aspx?familyid=83a52af2-f524-4ec5-9155-717cbe5d25ed&displaylang=en

http://www.microsoft.com/downloads/details.aspx?familyid=dc6c00cb-738a-4b97-8910-5cd29ab5f8d9&displaylang=en

Irrespective of all this, I will check with VS team why are not they shipping this SDK files for Windows Mobile/CE 5.0 by default.

Thanks

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corp.

|||

Thanks for the links but I have the WM 5.0 SDK installed on my machine and as I said in my original post, the transact.h file is not part of the SDK installation for some reason. So none of this solves the problem.

I don't see how anything you said invalidates my points, just means I posted the problem in the wrong forum. Instead of asking the VS team why they don't distrubute the WM 5.0 SDK by default, maybe you should inform the SDK team that the file is missing in the distribution.

|||

Please excuse us for the inconvenience caused. I have followed it up with some teams and found that transact.h is not shipped with WM 5.0 SDK. This problem would be fixed in the next SDK release. Also, I found that transact.h has not changed from WM 4.x SDK to WM 5.x SDK. Hence, I would request you to use the transact.h from WM 4.x SDK. We are also working towards writing a KB article where all the relevant information would be kept along with transact.h download.

Thanks alot for pointing out this BUG to us.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Server Everywhere, Microsoft Corporation

|||

There was a bug in the WM5 SDK that caused the transact.h file not to be included. This has been fixed for future versions.
In the meantime, a workaround is to copy the transact.h that was part of the Pocket PC 2003 SDK to the WM5 headers.
(i.e. " \Program Files\Windows CE Tools\wce500\Windows Mobile 5.0 Pocket PC SDK\Include\Armv4i" ).

Thanks,

Luis E. Cabrera
Windows Mobile Developer

Problem using SQL Server BI Development Studio

Hello

I'm using SQL Server BI Development Studio to create a mining structure to forecast time series analysis.
The process to create a model for time series has these steps:

Create a connection to the Database;

Create a source view. Create relationships between tables using the foreign keys;

Create the model with the mining structure. Define the fields that I won’t to predict and the algorithm properties aren’t changed.

Is something wrong in these steps, I missing something?

Other problem is the utilization of the model created earlier. In some tests that I made, the models don’t do forecasting upper then 20 steps. The problem could be the number of lines? But other data set with a large number of null values doesn’t have any problem predicting more than 20 steps using the time series algorithm.

Can someone help me?

Thanks in advance.

Jo?o Santos

<!--[if !vml]--><!--[endif]-->

I don't think there is a problem. The nature of the time series algorithm we use - Auto Regression Trees with Cross Predict (ARTXP) - is that predictions in the future can become unstable, since predictions become dependent on predictions. When we detect this instability, we stop producing output - this behavior can not be changed.

The dataset with large numbers of nulls is using some sort of missing value substitution which actually smooths the series - although you will have to determine the accuracy (usually more data = better).

We are considering allowing users to control the "instability" threshold in future versions.

|||The time series algorithm has a mechanism for detecting instability in predictions and stops returning predicted values when the variance exceeds a certain threshold. The point at which this happens is data-dependent - this explains the behavior you're seeing.

problem using SQL Server 2005 database from C# application through

Hi all.
I dont know if it is right group but maybe you guys be able to show me
a right direction. And im sorry, if i crossposted this message to
several groups.
Ok ,the problem is following:
We have a large ATL COM dll, which is used as common interface for our
database and it is used by many different applications (like Notes, VB6
apps etc). We wrote it a few years ago using VC6++(if it matters) and
everything worked just fine. This spring we updated our developement
tools to VS2005 and i updated VC6 project to VS2005. After few days of
work i got it compiled. NOW the real problem.
Our new C# applications use same COM dll through interop dll and once
again, everything works just fine EXCEPT, C# applications cannot use
SQLServer 2005 database through interop dll!!! This is the only
combination which does not work (C# -> interop dll -> ATL-COM dll -> SQL
Server 2005).
All other tests passed normally, like:
C# app -> interop dll -> ATL-COM dll -> ms access db.
normal C++ app -> ATL-COM dll -> SQL Server 2005 db.
normal C++ app -> ATL-COM dll -> ms access db.
but this one does NOT!!!!
C# -> interop dll -> ATL-COM dll -> SQL Server 2005
Although database object is correctly created in C# app and even
connection to SQL Server 2005 established successfully, every call to
get data from database failes to exception (seems like the exception
type differs randomly, like ArgumentException etc.). Sometimes DLL COM
returns database error like "specified object does not exist").
Can you guys tell me whats going on!!!
Im running out of ideas cause EXACTLY same dll works with other
databases and other programs that C# can use SAME dll with SQL2005 and
no problems...
thanks
Asko.
Roger Wolter[MSFT] wrote:
> Definitely a long shot but C# strings are always Unicode and your VC6 dll
> might not be expecting unicode.
>
Thank you for your response.
Our ATL DLL is definitely unicode cause we have alot of international
data in db and thus it cannot be the cause to my problem.
Asko.

problem using sql server

Hello there
I'm using windows authentication on sql server 2000
Suddenly i got error when i'm trying to access the sql server from my
computer 'Failed to logon user 'Null' . not ....
How can it happen?Hello,
Looks like the windows domain user do not have access to login to SQL
Server. Can you login as SA using sql authentication in enterprise manager,
go to security and add the
windows domain user and give necessary db rights and try.
Thanks
Hari
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eZRUA4FRHHA.996@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I'm using windows authentication on sql server 2000
> Suddenly i got error when i'm trying to access the sql server from my
> computer 'Failed to logon user 'Null' . not ....
> How can it happen?
>

Problem using SQL Debugger

Hello. I have only used the Debugger a few times, mostly from within VB. At a
client site, we had used the Debugger from an XP SP2 machine to a
WindowsServer 2000 machine with SQL Server 2000 (unknown service pack level)
without problem. It had not been used for several months, during which time
the server was replaced with a Windows 2003 Server, SQL Server 2000 (SP2+,
but unsure of actual SP level).
The problems? We can't install the VB software on the server to enable the
debugger. So we thought we'd just use the Query Analyzer debugger instead.
But when we try to run it, we get the error: "The debugger interface is not
installed. Please re-run setup and select 'add components to your existing
installation' ..". We did rerun setup (on both the client and server) but
when we ran it on the client it already clearly had the debugger interface
option installed. Verifying files on the server I found that I could not
register the file 'mssdi98.dll', it would error "the file was loaded but the
entry point was not found'". This file was then re-copied from the SQL Server
CD and tried again, same error.
Is there some known issue using the debugger when SQL Server is on a Windows
Server 2003 machine? This stored procedure is particularly nasty and we have
no chance of debugging it without this interface. Please advise.
Thank you,
Brad Ashforth
Hello Brad,
mssdi98.dll is not a Activex control dll. Therefore, you could not regsiter
it by using regsvr32.
This issue can occur if SQLDebugger account does not have the porper
rights. Normally SQLDebugger has "Log on as a batch job" and "Deny logon
locally" user rights. Please run gpedit.msc and go to Computer
Configuration->Windows Settings->Security Settings->Local Policie->User
rights assignments to check if SQLDebugger account has the proper rights.
See: 818374 INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User
Account
http://support.microsoft.com/?id=818374
Also, the following article shall be a good reference
280101 INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for
SQL
http://support.microsoft.com/?id=280101
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Problem using SQL Debugger
| thread-index: AcVhOfWOQfF7UKMrR42/aEn39rd3uA==
| X-WBNR-Posting-Host: 71.113.19.127
| From: "=?Utf-8?B?QnJhZCBBc2hmb3J0aA==?=" <banospam@.nospam.nospam>
| Subject: Problem using SQL Debugger
| Date: Wed, 25 May 2005 07:56:41 -0700
| Lines: 25
| Message-ID: <5A3081B3-D52B-46B6-A2BF-E53C9BF35403@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.tools:4469
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| Hello. I have only used the Debugger a few times, mostly from within VB.
At a
| client site, we had used the Debugger from an XP SP2 machine to a
| WindowsServer 2000 machine with SQL Server 2000 (unknown service pack
level)
| without problem. It had not been used for several months, during which
time
| the server was replaced with a Windows 2003 Server, SQL Server 2000
(SP2+,
| but unsure of actual SP level).
|
| The problems? We can't install the VB software on the server to enable
the
| debugger. So we thought we'd just use the Query Analyzer debugger
instead.
| But when we try to run it, we get the error: "The debugger interface is
not
| installed. Please re-run setup and select 'add components to your
existing
| installation' ..". We did rerun setup (on both the client and server) but
| when we ran it on the client it already clearly had the debugger
interface
| option installed. Verifying files on the server I found that I could not
| register the file 'mssdi98.dll', it would error "the file was loaded but
the
| entry point was not found'". This file was then re-copied from the SQL
Server
| CD and tried again, same error.
|
| Is there some known issue using the debugger when SQL Server is on a
Windows
| Server 2003 machine? This stored procedure is particularly nasty and we
have
| no chance of debugging it without this interface. Please advise.
|
| Thank you,
|
| Brad Ashforth
|
|||Hi Peter. Thank you for your reply. I checked the machine in question and I
do not see a "SQLDebugger" account. Would this account have been created
automatically during the install? Or do I need to create it manually? I will
review the article you mention but am surprised to find this so difficult to
set up. Perhaps this will be easier in SQL2005?
Brad Ashforth
"Peter Yang [MSFT]" wrote:

> Hello Brad,
> mssdi98.dll is not a Activex control dll. Therefore, you could not regsiter
> it by using regsvr32.
> This issue can occur if SQLDebugger account does not have the porper
> rights. Normally SQLDebugger has "Log on as a batch job" and "Deny logon
> locally" user rights. Please run gpedit.msc and go to Computer
> Configuration->Windows Settings->Security Settings->Local Policie->User
> rights assignments to check if SQLDebugger account has the proper rights.
> See: 818374 INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User
> Account
> http://support.microsoft.com/?id=818374
> Also, the following article shall be a good reference
> 280101 INF: Transact-SQL Debugger Limitations and Troubleshooting Tips for
> SQL
> http://support.microsoft.com/?id=280101
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Problem using SQL Debugger
> | thread-index: AcVhOfWOQfF7UKMrR42/aEn39rd3uA==
> | X-WBNR-Posting-Host: 71.113.19.127
> | From: "=?Utf-8?B?QnJhZCBBc2hmb3J0aA==?=" <banospam@.nospam.nospam>
> | Subject: Problem using SQL Debugger
> | Date: Wed, 25 May 2005 07:56:41 -0700
> | Lines: 25
> | Message-ID: <5A3081B3-D52B-46B6-A2BF-E53C9BF35403@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.tools
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.tools:4469
> | X-Tomcat-NG: microsoft.public.sqlserver.tools
> |
> | Hello. I have only used the Debugger a few times, mostly from within VB.
> At a
> | client site, we had used the Debugger from an XP SP2 machine to a
> | WindowsServer 2000 machine with SQL Server 2000 (unknown service pack
> level)
> | without problem. It had not been used for several months, during which
> time
> | the server was replaced with a Windows 2003 Server, SQL Server 2000
> (SP2+,
> | but unsure of actual SP level).
> |
> | The problems? We can't install the VB software on the server to enable
> the
> | debugger. So we thought we'd just use the Query Analyzer debugger
> instead.
> | But when we try to run it, we get the error: "The debugger interface is
> not
> | installed. Please re-run setup and select 'add components to your
> existing
> | installation' ..". We did rerun setup (on both the client and server) but
> | when we ran it on the client it already clearly had the debugger
> interface
> | option installed. Verifying files on the server I found that I could not
> | register the file 'mssdi98.dll', it would error "the file was loaded but
> the
> | entry point was not found'". This file was then re-copied from the SQL
> Server
> | CD and tried again, same error.
> |
> | Is there some known issue using the debugger when SQL Server is on a
> Windows
> | Server 2003 machine? This stored procedure is particularly nasty and we
> have
> | no chance of debugging it without this interface. Please advise.
> |
> | Thank you,
> |
> | Brad Ashforth
> |
>
|||Hello Brad,
The account shall be created automatically. You may want to install SP4 to
see if it fixes this problem
How to obtain the latest SQL Server 2000 service pack
http://support.microsoft.com/?kbid=290211
SQL Server 2000 Service Pack 4
http://www.microsoft.com/sql/downloads/2000/sp4.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Problem using SQL Debugger
| thread-index: AcVmqY2l2xUiQ0dvSSeg0vsuxcXaRA==
| X-WBNR-Posting-Host: 71.113.19.127
| From: "=?Utf-8?B?QnJhZCBBc2hmb3J0aA==?=" <banospam@.nospam.nospam>
| References: <5A3081B3-D52B-46B6-A2BF-E53C9BF35403@.microsoft.com>
<eW4WbbbYFHA.2184@.TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Problem using SQL Debugger
| Date: Wed, 1 Jun 2005 05:58:06 -0700
| Lines: 114
| Message-ID: <6FC66EE3-C581-4503-95A9-B1A467D93F32@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.tools
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.tools:4517
| X-Tomcat-NG: microsoft.public.sqlserver.tools
|
| Hi Peter. Thank you for your reply. I checked the machine in question and
I
| do not see a "SQLDebugger" account. Would this account have been created
| automatically during the install? Or do I need to create it manually? I
will
| review the article you mention but am surprised to find this so difficult
to
| set up. Perhaps this will be easier in SQL2005?
| --
| Brad Ashforth
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Brad,
| >
| > mssdi98.dll is not a Activex control dll. Therefore, you could not
regsiter
| > it by using regsvr32.
| >
| > This issue can occur if SQLDebugger account does not have the porper
| > rights. Normally SQLDebugger has "Log on as a batch job" and "Deny
logon
| > locally" user rights. Please run gpedit.msc and go to Computer
| > Configuration->Windows Settings->Security Settings->Local Policie->User
| > rights assignments to check if SQLDebugger account has the proper
rights.
| >
| > See: 818374 INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User
| > Account
| > http://support.microsoft.com/?id=818374
| >
| > Also, the following article shall be a good reference
| >
| > 280101 INF: Transact-SQL Debugger Limitations and Troubleshooting Tips
for
| > SQL
| > http://support.microsoft.com/?id=280101
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > ================================================== ===
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >
| > --
| > | Thread-Topic: Problem using SQL Debugger
| > | thread-index: AcVhOfWOQfF7UKMrR42/aEn39rd3uA==
| > | X-WBNR-Posting-Host: 71.113.19.127
| > | From: "=?Utf-8?B?QnJhZCBBc2hmb3J0aA==?=" <banospam@.nospam.nospam>
| > | Subject: Problem using SQL Debugger
| > | Date: Wed, 25 May 2005 07:56:41 -0700
| > | Lines: 25
| > | Message-ID: <5A3081B3-D52B-46B6-A2BF-E53C9BF35403@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.tools
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSF TNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.tools:4469
| > | X-Tomcat-NG: microsoft.public.sqlserver.tools
| > |
| > | Hello. I have only used the Debugger a few times, mostly from within
VB.
| > At a
| > | client site, we had used the Debugger from an XP SP2 machine to a
| > | WindowsServer 2000 machine with SQL Server 2000 (unknown service pack
| > level)
| > | without problem. It had not been used for several months, during
which
| > time
| > | the server was replaced with a Windows 2003 Server, SQL Server 2000
| > (SP2+,
| > | but unsure of actual SP level).
| > |
| > | The problems? We can't install the VB software on the server to
enable
| > the
| > | debugger. So we thought we'd just use the Query Analyzer debugger
| > instead.
| > | But when we try to run it, we get the error: "The debugger interface
is
| > not
| > | installed. Please re-run setup and select 'add components to your
| > existing
| > | installation' ..". We did rerun setup (on both the client and server)
but
| > | when we ran it on the client it already clearly had the debugger
| > interface
| > | option installed. Verifying files on the server I found that I could
not
| > | register the file 'mssdi98.dll', it would error "the file was loaded
but
| > the
| > | entry point was not found'". This file was then re-copied from the
SQL
| > Server
| > | CD and tried again, same error.
| > |
| > | Is there some known issue using the debugger when SQL Server is on a
| > Windows
| > | Server 2003 machine? This stored procedure is particularly nasty and
we
| > have
| > | no chance of debugging it without this interface. Please advise.
| > |
| > | Thank you,
| > |
| > | Brad Ashforth
| > |
| >
| >
|

Problem using SQL 2005 Reporting Services & asp.net app

I'm using VS 2005, SQL 2005 reporting services.
SQL reporting services is working, and I have it both on my local
computer, as well as on a server.
I've created a report in the SQL Business Intelligence development
studio that works in that environment.
I've uploaded the same report to both the Reporting services on my local
computer as well as the server, and can log in to them and run the
report there.
In VS 2005, in a test application that otherwise functions, I brought in
a Reportviewer from the toolbar, and added the report to it.
The reportServerUrl is:
http://mylocalcomputer/reports$sql2005
(I have both sql 2000 and sql 2005 on this local box).
The report path is:
J4 Report/J4 Detail Report
When I try to run it, I get:
The attempt to connect to the report server failed. Check your
connection information and that the report server is a compatible version.
The request failed with HTTP status 404: Not Found.
When I change the server name to the other one, I get the same message.
However, when I go into SQL Server Reporting services, to this link:
http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
The report displays fine.
When I go to my local computer & sql reporting services, to this link:
http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
I'm not using Localhost either in the VS 2005 app (in the properties for
the report viewer), but I am calling it (the web app itself where I'm
trying to call the report viewer from) thru localhost there.
http://localhost:2228/testjob/default.aspx
I'm calling other things (not report viewers) on this page that appear
to work.
I've seen some notes regarding this error, and that there is a lot of
registry hacks and config file updates you have to make for it, but not
sure if that's a true fix for the problem.
Anyone have any idea why SQL Reporting Services displays it fine, and
the report viewer - which is supposedly calling the same thing, doesn't?
BCOn Jul 27, 9:16 am, Blasting Cap <goo...@.christian.net> wrote:
> I'm using VS 2005, SQL 2005 reporting services.
> SQL reporting services is working, and I have it both on my local
> computer, as well as on a server.
> I've created a report in the SQL Business Intelligence development
> studio that works in that environment.
> I've uploaded the same report to both the Reporting services on my local
> computer as well as the server, and can log in to them and run the
> report there.
> In VS 2005, in a test application that otherwise functions, I brought in
> a Reportviewer from the toolbar, and added the report to it.
> The reportServerUrl is:
> http://mylocalcomputer/reports$sql2005
> (I have both sql 2000 and sql 2005 on this local box).
> The report path is:
> J4 Report/J4 Detail Report
> When I try to run it, I get:
> The attempt to connect to the report server failed. Check your
> connection information and that the report server is a compatible version.
> The request failed with HTTP status 404: Not Found.
> When I change the server name to the other one, I get the same message.
> However, when I go into SQL Server Reporting services, to this link:
> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+repo...
> The report displays fine.
> When I go to my local computer & sql reporting services, to this link:
> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2f...
> I'm not using Localhost either in the VS 2005 app (in the properties for
> the report viewer), but I am calling it (the web app itself where I'm
> trying to call the report viewer from) thru localhost there.
> http://localhost:2228/testjob/default.aspx
> I'm calling other things (not report viewers) on this page that appear
> to work.
> I've seen some notes regarding this error, and that there is a lot of
> registry hacks and config file updates you have to make for it, but not
> sure if that's a true fix for the problem.
> Anyone have any idea why SQL Reporting Services displays it fine, and
> the report viewer - which is supposedly calling the same thing, doesn't?
> BC
This might be a long shot, but you should check to see if the the
virtual directories (ReportServer and Reports) in IIS are set to the
correct .NET Framework (.NET 2.0) [via: right-click My Computer ->
select Manage -> Services and Applications -> Internet Information
Services -> Web Sites -> Default Web Site -> right-click the Reports/
ReportServer virtual directories -> select Properties -> select the
ASP.NET tab and verify that the ASP.NET version is 2.0.50727]. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Connect to ReportServer instead of Reports...i.e.
http://mylocalcomputer/ReportServer$sql2005
"Blasting Cap" wrote:
> I'm using VS 2005, SQL 2005 reporting services.
> SQL reporting services is working, and I have it both on my local
> computer, as well as on a server.
> I've created a report in the SQL Business Intelligence development
> studio that works in that environment.
> I've uploaded the same report to both the Reporting services on my local
> computer as well as the server, and can log in to them and run the
> report there.
> In VS 2005, in a test application that otherwise functions, I brought in
> a Reportviewer from the toolbar, and added the report to it.
> The reportServerUrl is:
> http://mylocalcomputer/reports$sql2005
> (I have both sql 2000 and sql 2005 on this local box).
> The report path is:
> J4 Report/J4 Detail Report
>
> When I try to run it, I get:
> The attempt to connect to the report server failed. Check your
> connection information and that the report server is a compatible version.
> The request failed with HTTP status 404: Not Found.
>
> When I change the server name to the other one, I get the same message.
>
> However, when I go into SQL Server Reporting services, to this link:
> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
> The report displays fine.
> When I go to my local computer & sql reporting services, to this link:
> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
> I'm not using Localhost either in the VS 2005 app (in the properties for
> the report viewer), but I am calling it (the web app itself where I'm
> trying to call the report viewer from) thru localhost there.
> http://localhost:2228/testjob/default.aspx
> I'm calling other things (not report viewers) on this page that appear
> to work.
> I've seen some notes regarding this error, and that there is a lot of
> registry hacks and config file updates you have to make for it, but not
> sure if that's a true fix for the problem.
> Anyone have any idea why SQL Reporting Services displays it fine, and
> the report viewer - which is supposedly calling the same thing, doesn't?
> BC
>|||Thank you.
This appeared to work, although I had to put in a slash at the end of
the http://mylocalcomputer/ReportServer$sql2005 line.
However, now that I've gotten it to run, I have some unusual behavior -
The "e" on Internet explorer at the top of the tab now flickers, like
the page is reloading. It also runs the CPU up to 100% on the computer
and although I can go from tab to tab in it (I am using AJAX tab panels
in the page), it will take like up to a minute to go to the next tab.
I'm not doing anything really data-intensive on those tabs, and they had
been functioning fine prior to putting in the report viewer (i.e. they
weren't flickering & clocking the CPU).
I eventually have to kill the page to do anything, because it has the
system up to 100%.
Any idea why reportviewer might make this act this way?
Thanks for the help,
BC
David wrote:
> Connect to ReportServer instead of Reports...i.e.
> http://mylocalcomputer/ReportServer$sql2005
> "Blasting Cap" wrote:
>> I'm using VS 2005, SQL 2005 reporting services.
>> SQL reporting services is working, and I have it both on my local
>> computer, as well as on a server.
>> I've created a report in the SQL Business Intelligence development
>> studio that works in that environment.
>> I've uploaded the same report to both the Reporting services on my local
>> computer as well as the server, and can log in to them and run the
>> report there.
>> In VS 2005, in a test application that otherwise functions, I brought in
>> a Reportviewer from the toolbar, and added the report to it.
>> The reportServerUrl is:
>> http://mylocalcomputer/reports$sql2005
>> (I have both sql 2000 and sql 2005 on this local box).
>> The report path is:
>> J4 Report/J4 Detail Report
>>
>> When I try to run it, I get:
>> The attempt to connect to the report server failed. Check your
>> connection information and that the report server is a compatible version.
>> The request failed with HTTP status 404: Not Found.
>>
>> When I change the server name to the other one, I get the same message.
>>
>> However, when I go into SQL Server Reporting services, to this link:
>> http://myserver/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+reports%2fJ4+Detail+Report
>> The report displays fine.
>> When I go to my local computer & sql reporting services, to this link:
>> http://mylocalcomputer/Reports$SQL2005/Pages/Report.aspx?ItemPath=%2fJ4+Reports%2fJ4+Detail+Report
>> I'm not using Localhost either in the VS 2005 app (in the properties for
>> the report viewer), but I am calling it (the web app itself where I'm
>> trying to call the report viewer from) thru localhost there.
>> http://localhost:2228/testjob/default.aspx
>> I'm calling other things (not report viewers) on this page that appear
>> to work.
>> I've seen some notes regarding this error, and that there is a lot of
>> registry hacks and config file updates you have to make for it, but not
>> sure if that's a true fix for the problem.
>> Anyone have any idea why SQL Reporting Services displays it fine, and
>> the report viewer - which is supposedly calling the same thing, doesn't?
>> BC
>>

Problem using SP_TRACE_SETFILTER - it doesn't seem to apply the fi

Warning - this is quite a LONG post.
Hi all,
Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
want to do some tracing in our production environment, and have jobs set up
to automaGically run the traces, so I am building the traces in an SP, via
SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
SQL scheduled jobs, and will happily create the trace files. I have another
SP to then copy these files to another server, and automaGically import them
in some tables for analyzing.
All good and well, so far, EXCEPT that while my trace get's created with no
errors, when I look at the trace data, it seems that the filters are not
being applied. When I open it up from Profiler, I can see the trace
properties, and the Events and Data columns are all correct, but there are no
Filters. Anyone see where I am going wrong?
So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
The SP
if exists (select 1 from sysobjects where name = 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
required by SP_CREATE_TRACE. The File to be created. @.StopTime
datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
required by SP_CREATE_TRACE. The maximum size per
file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
the creation of profiler traces that write to files.-- -- Example:-- exec
Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
future additions:-- 1> simple enhancement to specify DB to store data, and
table.-- 2> accepting a parameter instead of apply the default .trc. Use the
.trc as default-- 3> implementing default value's for dir's, so that it can
run without parm's ... good or bad...- Change Control: version 1 - Regan
Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
generation of limited data, -- filtering on duration-- Added Output
parametre @.TracId to return for lookup on trace. --AS--Declare Control
Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
@.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
that when the max_file_size is reached, the current trace file is closed and
a new file is created.--Set Trace Filter to exclude System Ids - that is
ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
@.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On = 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
@.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
Cannot create tracefile - check if file already exists, or this trace already
running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
there is not enough memory to perform the specified action.' if @.ReturnCode = 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
Invalid parameters. Returned when the user supplied incompatible
parameters.' else Print 'Unexpected and Unknown error In creating trace -
Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
@.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
Cursor Recompileset @.EventId = 75exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
@.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
= 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
@.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId = 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
set @.EventId = 98-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
@.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
= 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
Transaction-- set @.EventId = 50-- exec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
@.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
BatchCompletedset @.EventId = 12exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
@.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
@.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
@.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
@.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
@.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
@.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
@.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
@.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
@.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
'ERROR 13 - Out of memory. Returned when there is not enough memory to
perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
Error' GoTo ErrorHandlerendexec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
@.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
= 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
@.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
enough memory to perform the specified action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
--Greater than exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
@.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace is currently running. Changing the trace at this time will result in an
error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
Returned when there is not enough memory to perform the specified
action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
ErrorHandler endendErrorHandler:Return @.ReturnCode
The Code that calls the trace
DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
@.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
@.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
@.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
@.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
@.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize = 100 -- this is the maximum size for a file, in MBset @.DurationFilter = 1000--Set Running Variables--the time the trace will stopset @.RunStopTime = dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
UNC)set @.FileName = 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
char(3)),2) --Months +cast(datepart(d,getdate()) as
char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
char(3)),2) --Minutes--Display variablesprint 'File created is :
'+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
varchar(20))--Create trace, writing out to tracefile, until endtimeexec
@.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
Handlingselect @.rcselect @.TraceIDSELECT * FROM
::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
Ta...the data type of the @.durationfiltervalue must correspond with the data type
of the column to be filtered. otherwise it will not work.
unfortunately I was unable to post this into a QA session my connection kept
crashing .
Hope that helps
"Wanderer" wrote:
> Warning - this is quite a LONG post.
> Hi all,
> Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
> want to do some tracing in our production environment, and have jobs set up
> to automaGically run the traces, so I am building the traces in an SP, via
> SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
> SQL scheduled jobs, and will happily create the trace files. I have another
> SP to then copy these files to another server, and automaGically import them
> in some tables for analyzing.
> All good and well, so far, EXCEPT that while my trace get's created with no
> errors, when I look at the trace data, it seems that the filters are not
> being applied. When I open it up from Profiler, I can see the trace
> properties, and the Events and Data columns are all correct, but there are no
> Filters. Anyone see where I am going wrong?
> So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> The SP
> if exists (select 1 from sysobjects where name => 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
> required by SP_CREATE_TRACE. The File to be created. @.StopTime
> datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
> required by SP_CREATE_TRACE. The maximum size per
> file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
> the creation of profiler traces that write to files.-- -- Example:-- exec
> Up_Auto_Profiler_Perf
> 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
> future additions:-- 1> simple enhancement to specify DB to store data, and
> table.-- 2> accepting a parameter instead of apply the default .trc. Use the
> .trc as default-- 3> implementing default value's for dir's, so that it can
> run without parm's ... good or bad...- Change Control: version 1 - Regan
> Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> generation of limited data, -- filtering on duration-- Added Output
> parametre @.TracId to return for lookup on trace. --AS--Declare Control
> Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
> intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
> that when the max_file_size is reached, the current trace file is closed and
> a new file is created.--Set Trace Filter to exclude System Ids - that is
> ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
> than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
> output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
> @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> Cannot create tracefile - check if file already exists, or this trace already
> running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> there is not enough memory to perform the specified action.' if @.ReturnCode => 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> Invalid parameters. Returned when the user supplied incompatible
> parameters.' else Print 'Unexpected and Unknown error In creating trace -
> Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
> Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
> is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
> @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
> = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
> Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId => 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
> set @.EventId = 98-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
> @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
> exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
> = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
> Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
> BatchCompletedset @.EventId = 12exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
> @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
> sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> enough memory to perform the specified action.' else if @.ReturnCode => 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> --Greater than exec sp_trace_setfilter
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> trace is currently running. Changing the trace at this time will result in an
> error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> The specified Comparison Operator is not valid. ' else if @.ReturnCode => 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> Returned when there is not enough memory to perform the specified
> action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> ErrorHandler endendErrorHandler:Return @.ReturnCode
>
> The Code that calls the trace
> DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
> @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize => 100 -- this is the maximum size for a file, in MBset @.DurationFilter => 1000--Set Running Variables--the time the trace will stopset @.RunStopTime => dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
> UNC)set @.FileName => 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> char(3)),2) --Months +cast(datepart(d,getdate()) as
> char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> char(3)),2) --Minutes--Display variablesprint 'File created is :
> '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> @.Rc=Up_Auto_Profiler_Perf
> @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> Handlingselect @.rcselect @.TraceIDSELECT * FROM
> ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
> Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
>
> Ta...
>|||Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before, and
got error message saying that it needed to be BIGINT.
"Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line 272
Procedure expects parameter '@.value' of type 'bigint'."
"Olu Adedeji" wrote:
> the data type of the @.durationfiltervalue must correspond with the data type
> of the column to be filtered. otherwise it will not work.
> unfortunately I was unable to post this into a QA session my connection kept
> crashing .
> Hope that helps
> "Wanderer" wrote:
> > Warning - this is quite a LONG post.
> >
> > Hi all,
> >
> > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the picture - I
> > want to do some tracing in our production environment, and have jobs set up
> > to automaGically run the traces, so I am building the traces in an SP, via
> > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get called from
> > SQL scheduled jobs, and will happily create the trace files. I have another
> > SP to then copy these files to another server, and automaGically import them
> > in some tables for analyzing.
> >
> > All good and well, so far, EXCEPT that while my trace get's created with no
> > errors, when I look at the trace data, it seems that the filters are not
> > being applied. When I open it up from Profiler, I can see the trace
> > properties, and the Events and Data columns are all correct, but there are no
> > Filters. Anyone see where I am going wrong?
> >
> > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > The SP
> >
> > if exists (select 1 from sysobjects where name => > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar as
> > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt as
> > required by SP_CREATE_TRACE. The maximum size per
> > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > On: 2004-12-28-- Purpose: -- This stored procedure was written to facilitate
> > the creation of profiler traces that write to files.-- -- Example:-- exec
> > Up_Auto_Profiler_Perf
> > 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-- Possible
> > future additions:-- 1> simple enhancement to specify DB to store data, and
> > table.-- 2> accepting a parameter instead of apply the default .trc. Use the
> > .trc as default-- 3> implementing default value's for dir's, so that it can
> > run without parm's ... good or bad...- Change Control: version 1 - Regan
> > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > generation of limited data, -- filtering on duration-- Added Output
> > parametre @.TracId to return for lookup on trace. --AS--Declare Control
> > Variabledeclare @.ReturnCode Int--Declare Option Variables declare @.Option
> > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > int--Set Option Variableset @.Option = 2 --TraceFileRollOver --Specifies
> > that when the max_file_size is reached, the current trace file is closed and
> > a new file is created.--Set Trace Filter to exclude System Ids - that is
> > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator = 2 --Greater
> > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create @.TraceId
> > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect @.ReturnCode=@.@.Errorif
> > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when
> > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error 12 -
> > Cannot create tracefile - check if file already exists, or this trace already
> > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when
> > there is not enough memory to perform the specified action.' if @.ReturnCode => > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > Invalid parameters. Returned when the user supplied incompatible
> > parameters.' else Print 'Unexpected and Unknown error In creating trace -
> > Please review' Goto ErrorHandlerEnd--Populate Trace with Events--SECTION
> > CURSORS-- this set is : cursor executeset @.EventId = 74exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > @.EventId = 53exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is :
> > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS-- this set
> > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:DeadLockChainset
> > @.EventId = 59exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset @.EventId
> > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is :
> > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset @.EventId => > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan Statistics--
> > set @.EventId = 98-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan Text-- set
> > @.EventId = 96-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData--
> > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset @.EventId
> > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is : SQL
> > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is : SQL
> > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writes-- --this set is : SQL:StmtCompleted-- set
> > @.EventId = 41-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu-- exec
> > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus @.TraceId,1select
> > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR 9 -
> > The specified Trace Handle is not valid.' else if @.ReturnCode = 8 print
> > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 - Unknown
> > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print 'ERROR
> > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace is
> > currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is not
> > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column is not
> > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7 print
> > 'ERROR 7 - The specified Logical Operator is not valid.' else if @.ReturnCode
> > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there is not
> > enough memory to perform the specified action.' else if @.ReturnCode => > 16 print 'ERROR 16 - The function is not valid for this trace.' else Print
> > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue <>
> > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > --Greater than exec sp_trace_setfilter
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue select
> > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
> > trace is currently running. Changing the trace at this time will result in an
> > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column is
> > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
> > The specified Comparison Operator is not valid. ' else if @.ReturnCode => > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > Returned when there is not enough memory to perform the specified
> > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is not
> > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > ErrorHandler endendErrorHandler:Return @.ReturnCode
> >
> >
> > The Code that calls the trace
> >
> > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC INTEGERDECLARE
> > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control Variablesset
> > @.MinutesToRun = 5 -- this is how long the trace will run forset @.MaxSize => > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > 1000--Set Running Variables--the time the trace will stopset @.RunStopTime => > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name (not
> > UNC)set @.FileName => > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > varchar(20))--Create trace, writing out to tracefile, until endtimeexec
> > @.Rc=Up_Auto_Profiler_Perf
> > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR ('Create/Run of
> > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> >
> >
> > Ta...
> >|||I have never had problems with this procedure, however I try and stick with
the syntax format as seen below(one of my earlier posts ie. using
@.bigintfilter and @.intfilter input parameters) because of the nature of the
procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
CREATE PROCEDURE sp_DBA_LRQ
@.duration int, -- in minutes
@.QueryDuration int = 5, -- in seconds
@.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
@.dbname sysname = null, -- the database to monitor (null = monitor all
databases)
@.maxfilesize bigint = 20, -- expected size of trc file in MB
@.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
backslash
AS
SET NOCOUNT ON
-- Name: sp_DBA_LRQ
-- Date: 22/10/2004
-- Author: Olu Adedeji
-- Note: auto set @.duration (mins)
-- auto set @.filename (no extension)
-- monitor @.dbname (database you wish to run the sql against)
-- The latest file will always be servername_yyyymmdd.trc
-- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
at the end of the directory
-- Capture Long Running Queries(LRQ)
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++
-- WHAT WHO WHEN
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++
-- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
-- Resolve Execution plan annomalies on prod servers Olu
Adedeji 16/12/2004
-- Version: 1.04 - customised to capture specific information for PrecisDM
Olu Adedeji 16/12/2004
-- Version: 1.05 - customised to capture long running Queries Olu
Adedeji 22/12/2004
-- I have included to capture SQLBatches and Stored Procs
-- Display Procedure information
Print 'Application: SQL Profiler'
Print 'Procedure: sp_DBA_LRQ'
Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
for ' + convert(varchar,@.duration) + '(mins)'
Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
-- Create a Queue
declare @.rc int,
@.TraceID int, -- sqlassigns the trace ID
@.DateTime datetime, -- trace stop time
@.CurrentTime datetime, -- current time
@.appendDate int, -- append datestamp
@.Cmd varchar(1000),
@.RenameCmd varchar(1000),
@.newFileName varchar(1000)
-- auto set stoptime
set @.CurrentTime = current_timestamp
set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
--set @.maxfilesize = 50 -- set max file size to 10Mb
set @.filename = @.filename + '_' + (select
convert(varchar,current_timestamp,112))
set @.newFileName = @.filename
set @.filename = @.filedir + @.filename
-- create temp table
create table ##fileexists (FileExists int, DirectoryExists int,
ParentDirectoryExists int)
-- New file name to rename to
declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
extension)
set @.ActualFilename = @.filename + '.trc'
-- populate temp table
insert into ##fileexists
exec master.dbo.xp_fileexist @.Actualfilename
-- Check that file exists and rename file or proceed if it does not exist
if (select fileexists from ##fileexists where fileexists = 1) is not null
begin
-- I have used 114 to allow for 24h time conversion (similar to how
logshipping works)
select @.appendDate = (select CASE WHEN
convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
convert(varchar,datepart(hh,current_timestamp),114)
WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
convert(varchar,datepart(hh,current_timestamp),114)
END
+ CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
convert(varchar,datepart(mi,current_timestamp),114)
END)
select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
+ convert(varchar,@.AppendDate) + '.trc'
-- display the rename command (useful if file exists)
Print @.RenameCmd
exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
End
exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
@.Datetime
if (@.rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @.on bit
set @.on = 1
--++++++++++++++++++++++++++++++++++++++
-- Capture RPC:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 10, 1, @.on
exec sp_trace_setevent @.TraceID, 10, 2, @.on
exec sp_trace_setevent @.TraceID, 10, 3, @.on
exec sp_trace_setevent @.TraceID, 10, 8, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 21, @.on
exec sp_trace_setevent @.TraceID, 10, 26, @.on
exec sp_trace_setevent @.TraceID, 10, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture SP:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 43, 1, @.on
exec sp_trace_setevent @.TraceID, 43, 2, @.on
exec sp_trace_setevent @.TraceID, 43, 3, @.on
exec sp_trace_setevent @.TraceID, 43, 8, @.on
exec sp_trace_setevent @.TraceID, 43, 9, @.on
exec sp_trace_setevent @.TraceID, 43, 10, @.on
exec sp_trace_setevent @.TraceID, 43, 11, @.on
exec sp_trace_setevent @.TraceID, 43, 12, @.on
exec sp_trace_setevent @.TraceID, 43, 13, @.on
exec sp_trace_setevent @.TraceID, 43, 14, @.on
exec sp_trace_setevent @.TraceID, 43, 15, @.on
exec sp_trace_setevent @.TraceID, 43, 21, @.on
exec sp_trace_setevent @.TraceID, 43, 26, @.on
exec sp_trace_setevent @.TraceID, 43, 35, @.on
--++++++++++++++++++++++++++++++++++++++
--Capture SQL:BatchCompleted
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 2, @.on
exec sp_trace_setevent @.TraceID, 12, 3, @.on
exec sp_trace_setevent @.TraceID, 12, 8, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 21, @.on
exec sp_trace_setevent @.TraceID, 12, 26, @.on
exec sp_trace_setevent @.TraceID, 12, 35, @.on
--++++++++++++++++++++++++++++++++++++++
-- Capture Generic Information
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @.TraceID, 70, 1, @.on
exec sp_trace_setevent @.TraceID, 70, 2, @.on
exec sp_trace_setevent @.TraceID, 70, 3, @.on
exec sp_trace_setevent @.TraceID, 70, 8, @.on
exec sp_trace_setevent @.TraceID, 70, 9, @.on
exec sp_trace_setevent @.TraceID, 70, 10, @.on
exec sp_trace_setevent @.TraceID, 70, 11, @.on
exec sp_trace_setevent @.TraceID, 70, 12, @.on
exec sp_trace_setevent @.TraceID, 70, 14, @.on
exec sp_trace_setevent @.TraceID, 70, 21, @.on
exec sp_trace_setevent @.TraceID, 70, 26, @.on
exec sp_trace_setevent @.TraceID, 70, 35, @.on
exec sp_trace_setevent @.TraceID, 71, 1, @.on
exec sp_trace_setevent @.TraceID, 71, 2, @.on
exec sp_trace_setevent @.TraceID, 71, 3, @.on
exec sp_trace_setevent @.TraceID, 71, 8, @.on
exec sp_trace_setevent @.TraceID, 71, 9, @.on
exec sp_trace_setevent @.TraceID, 71, 10, @.on
exec sp_trace_setevent @.TraceID, 71, 11, @.on
exec sp_trace_setevent @.TraceID, 71, 12, @.on
exec sp_trace_setevent @.TraceID, 71, 14, @.on
exec sp_trace_setevent @.TraceID, 71, 21, @.on
exec sp_trace_setevent @.TraceID, 71, 26, @.on
exec sp_trace_setevent @.TraceID, 71, 35, @.on
exec sp_trace_setevent @.TraceID, 72, 1, @.on
exec sp_trace_setevent @.TraceID, 72, 2, @.on
exec sp_trace_setevent @.TraceID, 72, 3, @.on
exec sp_trace_setevent @.TraceID, 72, 8, @.on
exec sp_trace_setevent @.TraceID, 72, 9, @.on
exec sp_trace_setevent @.TraceID, 72, 10, @.on
exec sp_trace_setevent @.TraceID, 72, 11, @.on
exec sp_trace_setevent @.TraceID, 72, 12, @.on
exec sp_trace_setevent @.TraceID, 72, 14, @.on
exec sp_trace_setevent @.TraceID, 72, 21, @.on
exec sp_trace_setevent @.TraceID, 72, 26, @.on
exec sp_trace_setevent @.TraceID, 72, 35, @.on
exec sp_trace_setevent @.TraceID, 74, 1, @.on
exec sp_trace_setevent @.TraceID, 74, 2, @.on
exec sp_trace_setevent @.TraceID, 74, 3, @.on
exec sp_trace_setevent @.TraceID, 74, 8, @.on
exec sp_trace_setevent @.TraceID, 74, 9, @.on
exec sp_trace_setevent @.TraceID, 74, 10, @.on
exec sp_trace_setevent @.TraceID, 74, 11, @.on
exec sp_trace_setevent @.TraceID, 74, 12, @.on
exec sp_trace_setevent @.TraceID, 74, 14, @.on
exec sp_trace_setevent @.TraceID, 74, 21, @.on
exec sp_trace_setevent @.TraceID, 74, 26, @.on
exec sp_trace_setevent @.TraceID, 74, 35, @.on
exec sp_trace_setevent @.TraceID, 77, 1, @.on
exec sp_trace_setevent @.TraceID, 77, 2, @.on
exec sp_trace_setevent @.TraceID, 77, 3, @.on
exec sp_trace_setevent @.TraceID, 77, 8, @.on
exec sp_trace_setevent @.TraceID, 77, 9, @.on
exec sp_trace_setevent @.TraceID, 77, 10, @.on
exec sp_trace_setevent @.TraceID, 77, 11, @.on
exec sp_trace_setevent @.TraceID, 77, 12, @.on
exec sp_trace_setevent @.TraceID, 77, 14, @.on
exec sp_trace_setevent @.TraceID, 77, 21, @.on
exec sp_trace_setevent @.TraceID, 77, 26, @.on
exec sp_trace_setevent @.TraceID, 77, 35, @.on
exec sp_trace_setevent @.TraceID, 78, 1, @.on
exec sp_trace_setevent @.TraceID, 78, 2, @.on
exec sp_trace_setevent @.TraceID, 78, 3, @.on
exec sp_trace_setevent @.TraceID, 78, 8, @.on
exec sp_trace_setevent @.TraceID, 78, 9, @.on
exec sp_trace_setevent @.TraceID, 78, 10, @.on
exec sp_trace_setevent @.TraceID, 78, 11, @.on
exec sp_trace_setevent @.TraceID, 78, 12, @.on
exec sp_trace_setevent @.TraceID, 78, 14, @.on
exec sp_trace_setevent @.TraceID, 78, 21, @.on
exec sp_trace_setevent @.TraceID, 78, 26, @.on
exec sp_trace_setevent @.TraceID, 78, 35, @.on
exec sp_trace_setevent @.TraceID, 100, 1, @.on
exec sp_trace_setevent @.TraceID, 100, 2, @.on
exec sp_trace_setevent @.TraceID, 100, 3, @.on
exec sp_trace_setevent @.TraceID, 100, 8, @.on
exec sp_trace_setevent @.TraceID, 100, 9, @.on
exec sp_trace_setevent @.TraceID, 100, 10, @.on
exec sp_trace_setevent @.TraceID, 100, 11, @.on
exec sp_trace_setevent @.TraceID, 100, 12, @.on
exec sp_trace_setevent @.TraceID, 100, 14, @.on
exec sp_trace_setevent @.TraceID, 100, 21, @.on
exec sp_trace_setevent @.TraceID, 100, 26, @.on
exec sp_trace_setevent @.TraceID, 100, 35, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
-- set database filter if @.dbname is not null
if @.dbname is not null
begin
-- check @.dbname is on the server
if (select db_id(@.dbname)) is not null
set @.intfilter = db_id(@.dbname)
exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
end
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- set filter for Long Running Query(LRQ) Duration
-- Added this 22/12/2004
select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
milliseconds(ms)
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
-- 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:
-- display Trace information (can output this to a logfile in sqlagent)
select * from ::fn_trace_getinfo(default)
-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists
GO
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
and
> got error message saying that it needed to be BIGINT.
> "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
272
> Procedure expects parameter '@.value' of type 'bigint'."
> "Olu Adedeji" wrote:
> > the data type of the @.durationfiltervalue must correspond with the data
type
> > of the column to be filtered. otherwise it will not work.
> >
> > unfortunately I was unable to post this into a QA session my connection
kept
> > crashing .
> >
> > Hope that helps
> >
> > "Wanderer" wrote:
> >
> > > Warning - this is quite a LONG post.
> > >
> > > Hi all,
> > >
> > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
picture - I
> > > want to do some tracing in our production environment, and have jobs
set up
> > > to automaGically run the traces, so I am building the traces in an SP,
via
> > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
called from
> > > SQL scheduled jobs, and will happily create the trace files. I have
another
> > > SP to then copy these files to another server, and automaGically
import them
> > > in some tables for analyzing.
> > >
> > > All good and well, so far, EXCEPT that while my trace get's created
with no
> > > errors, when I look at the trace data, it seems that the filters are
not
> > > being applied. When I open it up from Profiler, I can see the trace
> > > properties, and the Events and Data columns are all correct, but there
are no
> > > Filters. Anyone see where I am going wrong?
> > >
> > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > The SP
> > >
> > > if exists (select 1 from sysobjects where name => > > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar
as
> > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt
as
> > > required by SP_CREATE_TRACE. The maximum size per
> > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
facilitate
> > > the creation of profiler traces that write to files.-- -- Example:--
exec
> > > Up_Auto_Profiler_Perf
> > >
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
-- Possible
> > > future additions:-- 1> simple enhancement to specify DB to store data,
and
> > > table.-- 2> accepting a parameter instead of apply the default .trc.
Use the
> > > .trc as default-- 3> implementing default value's for dir's, so that
it can
> > > run without parm's ... good or bad...- Change Control: version 1 -
Regan
> > > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > > generation of limited data, -- filtering on duration-- Added Output
> > > parametre @.TracId to return for lookup on trace. --AS--Declare
Control
> > > Variabledeclare @.ReturnCode Int--Declare Option Variables declare
@.Option
> > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > > int--Set Option Variableset @.Option =2 --TraceFileRollOver --Specifies
> > > that when the max_file_size is reached, the current trace file is
closed and
> > > a new file is created.--Set Trace Filter to exclude System Ids - that
is
> > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator =2 --Greater
> > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
@.TraceId
> > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
@.ReturnCode=@.@.Errorif
> > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
Returned when
> > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
12 -
> > > Cannot create tracefile - check if file already exists, or this trace
already
> > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
when
> > > there is not enough memory to perform the specified action.' if
@.ReturnCode => > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > Invalid parameters. Returned when the user supplied incompatible
> > > parameters.' else Print 'Unexpected and Unknown error In creating
trace -
> > > Please review' Goto ErrorHandlerEnd--Populate Trace with
Events--SECTION
> > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > > @.EventId = 53exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec
> > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is
:
> > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS--
this set
> > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
Lock:DeadLockChainset
> > > @.EventId = 59exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset
@.EventId
> > > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is
:
> > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
@.EventId => > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
Statistics--
> > > set @.EventId = 98-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
Text-- set
> > > @.EventId = 96-- exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryData--
> > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset
@.EventId
> > > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
sp_trace_setevent
> > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is :
SQL
> > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is :
SQL
> > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
SQL:StmtCompleted-- set
> > > @.EventId = 41-- exec sp_trace_setevent
@.TraceId,@.EventId,18,@.On --cpu-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
sp_trace_setevent
> > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
@.TraceId,1select
> > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
9 -
> > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
print
> > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
Unknown
> > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
'ERROR
> > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
is
> > > currently running. Changing the trace at this time will result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is not
> > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
is not
> > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
print
> > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
@.ReturnCode
> > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
is not
> > > enough memory to perform the specified action.' else if @.ReturnCode => > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
Print
> > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
<>
> > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > --Greater than exec sp_trace_setfilter
> > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select
> > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
The
> > > trace is currently running. Changing the trace at this time will
result in an
> > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
is
> > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
6 -
> > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
=> > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
if
> > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > Returned when there is not enough memory to perform the specified
> > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
not
> > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > >
> > >
> > > The Code that calls the trace
> > >
> > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
INTEGERDECLARE
> > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
Variablesset
> > > @.MinutesToRun = 5 -- this is how long the trace will run forset
@.MaxSize => > > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > > 1000--Set Running Variables--the time the trace will stopset
@.RunStopTime => > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name
(not
> > > UNC)set @.FileName => > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > varchar(20))--Create trace, writing out to tracefile, until
endtimeexec
> > > @.Rc=Up_Auto_Profiler_Perf
> > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
('Create/Run of
> > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > >
> > >
> > > Ta...
> > >|||I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
seem to apply the filter. When I open the trace file in Profiler, there are
NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
(you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
"Olu Adedeji" wrote:
> I have never had problems with this procedure, however I try and stick with
> the syntax format as seen below(one of my earlier posts ie. using
> @.bigintfilter and @.intfilter input parameters) because of the nature of the
> procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
>
> CREATE PROCEDURE sp_DBA_LRQ
> @.duration int, -- in minutes
> @.QueryDuration int = 5, -- in seconds
> @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> @.dbname sysname = null, -- the database to monitor (null = monitor all
> databases)
> @.maxfilesize bigint = 20, -- expected size of trc file in MB
> @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
> backslash
> AS
> SET NOCOUNT ON
> -- Name: sp_DBA_LRQ
> -- Date: 22/10/2004
> -- Author: Olu Adedeji
> -- Note: auto set @.duration (mins)
> -- auto set @.filename (no extension)
> -- monitor @.dbname (database you wish to run the sql against)
> -- The latest file will always be servername_yyyymmdd.trc
> -- to use this locally set @.filedir = c:\temp\ -- remember to add the '\'
> at the end of the directory
> -- Capture Long Running Queries(LRQ)
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++++++++++++++++++++++++++
> -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++
> -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
> -- Resolve Execution plan annomalies on prod servers Olu
> Adedeji 16/12/2004
> -- Version: 1.04 - customised to capture specific information for PrecisDM
> Olu Adedeji 16/12/2004
> -- Version: 1.05 - customised to capture long running Queries Olu
> Adedeji 22/12/2004
> -- I have included to capture SQLBatches and Stored Procs
> -- Display Procedure information
> Print 'Application: SQL Profiler'
> Print 'Procedure: sp_DBA_LRQ'
> Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername + '
> for ' + convert(varchar,@.duration) + '(mins)'
> Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> -- Create a Queue
> declare @.rc int,
> @.TraceID int, -- sqlassigns the trace ID
> @.DateTime datetime, -- trace stop time
> @.CurrentTime datetime, -- current time
> @.appendDate int, -- append datestamp
> @.Cmd varchar(1000),
> @.RenameCmd varchar(1000),
> @.newFileName varchar(1000)
> -- auto set stoptime
> set @.CurrentTime = current_timestamp
> set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> --set @.maxfilesize = 50 -- set max file size to 10Mb
> set @.filename = @.filename + '_' + (select
> convert(varchar,current_timestamp,112))
> set @.newFileName = @.filename
> set @.filename = @.filedir + @.filename
> -- create temp table
> create table ##fileexists (FileExists int, DirectoryExists int,
> ParentDirectoryExists int)
> -- New file name to rename to
> declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> extension)
> set @.ActualFilename = @.filename + '.trc'
> -- populate temp table
> insert into ##fileexists
> exec master.dbo.xp_fileexist @.Actualfilename
>
> -- Check that file exists and rename file or proceed if it does not exist
> if (select fileexists from ##fileexists where fileexists = 1) is not null
> begin
> -- I have used 114 to allow for 24h time conversion (similar to how
> logshipping works)
> select @.appendDate = (select CASE WHEN
> convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> convert(varchar,datepart(hh,current_timestamp),114)
> WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> convert(varchar,datepart(hh,current_timestamp),114)
> END
> + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> convert(varchar,datepart(mi,current_timestamp),114)
> END)
> select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName + '_'
> + convert(varchar,@.AppendDate) + '.trc'
> -- display the rename command (useful if file exists)
> Print @.RenameCmd
> exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> End
> exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> @.Datetime
> if (@.rc != 0) goto error
> -- Client side File and Table cannot be scripted
> -- Set the events
> declare @.on bit
> set @.on = 1
> --++++++++++++++++++++++++++++++++++++++
> -- Capture RPC:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 10, 1, @.on
> exec sp_trace_setevent @.TraceID, 10, 2, @.on
> exec sp_trace_setevent @.TraceID, 10, 3, @.on
> exec sp_trace_setevent @.TraceID, 10, 8, @.on
> exec sp_trace_setevent @.TraceID, 10, 9, @.on
> exec sp_trace_setevent @.TraceID, 10, 10, @.on
> exec sp_trace_setevent @.TraceID, 10, 11, @.on
> exec sp_trace_setevent @.TraceID, 10, 12, @.on
> exec sp_trace_setevent @.TraceID, 10, 13, @.on
> exec sp_trace_setevent @.TraceID, 10, 14, @.on
> exec sp_trace_setevent @.TraceID, 10, 15, @.on
> exec sp_trace_setevent @.TraceID, 10, 21, @.on
> exec sp_trace_setevent @.TraceID, 10, 26, @.on
> exec sp_trace_setevent @.TraceID, 10, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture SP:Completed
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 43, 1, @.on
> exec sp_trace_setevent @.TraceID, 43, 2, @.on
> exec sp_trace_setevent @.TraceID, 43, 3, @.on
> exec sp_trace_setevent @.TraceID, 43, 8, @.on
> exec sp_trace_setevent @.TraceID, 43, 9, @.on
> exec sp_trace_setevent @.TraceID, 43, 10, @.on
> exec sp_trace_setevent @.TraceID, 43, 11, @.on
> exec sp_trace_setevent @.TraceID, 43, 12, @.on
> exec sp_trace_setevent @.TraceID, 43, 13, @.on
> exec sp_trace_setevent @.TraceID, 43, 14, @.on
> exec sp_trace_setevent @.TraceID, 43, 15, @.on
> exec sp_trace_setevent @.TraceID, 43, 21, @.on
> exec sp_trace_setevent @.TraceID, 43, 26, @.on
> exec sp_trace_setevent @.TraceID, 43, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> --Capture SQL:BatchCompleted
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 2, @.on
> exec sp_trace_setevent @.TraceID, 12, 3, @.on
> exec sp_trace_setevent @.TraceID, 12, 8, @.on
> exec sp_trace_setevent @.TraceID, 12, 9, @.on
> exec sp_trace_setevent @.TraceID, 12, 10, @.on
> exec sp_trace_setevent @.TraceID, 12, 11, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> exec sp_trace_setevent @.TraceID, 12, 13, @.on
> exec sp_trace_setevent @.TraceID, 12, 14, @.on
> exec sp_trace_setevent @.TraceID, 12, 15, @.on
> exec sp_trace_setevent @.TraceID, 12, 21, @.on
> exec sp_trace_setevent @.TraceID, 12, 26, @.on
> exec sp_trace_setevent @.TraceID, 12, 35, @.on
> --++++++++++++++++++++++++++++++++++++++
> -- Capture Generic Information
> --++++++++++++++++++++++++++++++++++++++
> exec sp_trace_setevent @.TraceID, 70, 1, @.on
> exec sp_trace_setevent @.TraceID, 70, 2, @.on
> exec sp_trace_setevent @.TraceID, 70, 3, @.on
> exec sp_trace_setevent @.TraceID, 70, 8, @.on
> exec sp_trace_setevent @.TraceID, 70, 9, @.on
> exec sp_trace_setevent @.TraceID, 70, 10, @.on
> exec sp_trace_setevent @.TraceID, 70, 11, @.on
> exec sp_trace_setevent @.TraceID, 70, 12, @.on
> exec sp_trace_setevent @.TraceID, 70, 14, @.on
> exec sp_trace_setevent @.TraceID, 70, 21, @.on
> exec sp_trace_setevent @.TraceID, 70, 26, @.on
> exec sp_trace_setevent @.TraceID, 70, 35, @.on
> exec sp_trace_setevent @.TraceID, 71, 1, @.on
> exec sp_trace_setevent @.TraceID, 71, 2, @.on
> exec sp_trace_setevent @.TraceID, 71, 3, @.on
> exec sp_trace_setevent @.TraceID, 71, 8, @.on
> exec sp_trace_setevent @.TraceID, 71, 9, @.on
> exec sp_trace_setevent @.TraceID, 71, 10, @.on
> exec sp_trace_setevent @.TraceID, 71, 11, @.on
> exec sp_trace_setevent @.TraceID, 71, 12, @.on
> exec sp_trace_setevent @.TraceID, 71, 14, @.on
> exec sp_trace_setevent @.TraceID, 71, 21, @.on
> exec sp_trace_setevent @.TraceID, 71, 26, @.on
> exec sp_trace_setevent @.TraceID, 71, 35, @.on
> exec sp_trace_setevent @.TraceID, 72, 1, @.on
> exec sp_trace_setevent @.TraceID, 72, 2, @.on
> exec sp_trace_setevent @.TraceID, 72, 3, @.on
> exec sp_trace_setevent @.TraceID, 72, 8, @.on
> exec sp_trace_setevent @.TraceID, 72, 9, @.on
> exec sp_trace_setevent @.TraceID, 72, 10, @.on
> exec sp_trace_setevent @.TraceID, 72, 11, @.on
> exec sp_trace_setevent @.TraceID, 72, 12, @.on
> exec sp_trace_setevent @.TraceID, 72, 14, @.on
> exec sp_trace_setevent @.TraceID, 72, 21, @.on
> exec sp_trace_setevent @.TraceID, 72, 26, @.on
> exec sp_trace_setevent @.TraceID, 72, 35, @.on
> exec sp_trace_setevent @.TraceID, 74, 1, @.on
> exec sp_trace_setevent @.TraceID, 74, 2, @.on
> exec sp_trace_setevent @.TraceID, 74, 3, @.on
> exec sp_trace_setevent @.TraceID, 74, 8, @.on
> exec sp_trace_setevent @.TraceID, 74, 9, @.on
> exec sp_trace_setevent @.TraceID, 74, 10, @.on
> exec sp_trace_setevent @.TraceID, 74, 11, @.on
> exec sp_trace_setevent @.TraceID, 74, 12, @.on
> exec sp_trace_setevent @.TraceID, 74, 14, @.on
> exec sp_trace_setevent @.TraceID, 74, 21, @.on
> exec sp_trace_setevent @.TraceID, 74, 26, @.on
> exec sp_trace_setevent @.TraceID, 74, 35, @.on
> exec sp_trace_setevent @.TraceID, 77, 1, @.on
> exec sp_trace_setevent @.TraceID, 77, 2, @.on
> exec sp_trace_setevent @.TraceID, 77, 3, @.on
> exec sp_trace_setevent @.TraceID, 77, 8, @.on
> exec sp_trace_setevent @.TraceID, 77, 9, @.on
> exec sp_trace_setevent @.TraceID, 77, 10, @.on
> exec sp_trace_setevent @.TraceID, 77, 11, @.on
> exec sp_trace_setevent @.TraceID, 77, 12, @.on
> exec sp_trace_setevent @.TraceID, 77, 14, @.on
> exec sp_trace_setevent @.TraceID, 77, 21, @.on
> exec sp_trace_setevent @.TraceID, 77, 26, @.on
> exec sp_trace_setevent @.TraceID, 77, 35, @.on
> exec sp_trace_setevent @.TraceID, 78, 1, @.on
> exec sp_trace_setevent @.TraceID, 78, 2, @.on
> exec sp_trace_setevent @.TraceID, 78, 3, @.on
> exec sp_trace_setevent @.TraceID, 78, 8, @.on
> exec sp_trace_setevent @.TraceID, 78, 9, @.on
> exec sp_trace_setevent @.TraceID, 78, 10, @.on
> exec sp_trace_setevent @.TraceID, 78, 11, @.on
> exec sp_trace_setevent @.TraceID, 78, 12, @.on
> exec sp_trace_setevent @.TraceID, 78, 14, @.on
> exec sp_trace_setevent @.TraceID, 78, 21, @.on
> exec sp_trace_setevent @.TraceID, 78, 26, @.on
> exec sp_trace_setevent @.TraceID, 78, 35, @.on
> exec sp_trace_setevent @.TraceID, 100, 1, @.on
> exec sp_trace_setevent @.TraceID, 100, 2, @.on
> exec sp_trace_setevent @.TraceID, 100, 3, @.on
> exec sp_trace_setevent @.TraceID, 100, 8, @.on
> exec sp_trace_setevent @.TraceID, 100, 9, @.on
> exec sp_trace_setevent @.TraceID, 100, 10, @.on
> exec sp_trace_setevent @.TraceID, 100, 11, @.on
> exec sp_trace_setevent @.TraceID, 100, 12, @.on
> exec sp_trace_setevent @.TraceID, 100, 14, @.on
> exec sp_trace_setevent @.TraceID, 100, 21, @.on
> exec sp_trace_setevent @.TraceID, 100, 26, @.on
> exec sp_trace_setevent @.TraceID, 100, 35, @.on
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> -- set database filter if @.dbname is not null
> if @.dbname is not null
> begin
> -- check @.dbname is on the server
> if (select db_id(@.dbname)) is not null
> set @.intfilter = db_id(@.dbname)
> exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> end
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> -- set filter for Long Running Query(LRQ) Duration
> -- Added this 22/12/2004
> select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> milliseconds(ms)
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
>
> -- 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:
> -- display Trace information (can output this to a logfile in sqlagent)
> select * from ::fn_trace_getinfo(default)
> -- clear down temp table
> if (select object_id('tempdb.dbo.##fileexists') from
> tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> drop table ##fileexists
> GO
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT before,
> and
> > got error message saying that it needed to be BIGINT.
> >
> > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line
> 272
> > Procedure expects parameter '@.value' of type 'bigint'."
> >
> > "Olu Adedeji" wrote:
> >
> > > the data type of the @.durationfiltervalue must correspond with the data
> type
> > > of the column to be filtered. otherwise it will not work.
> > >
> > > unfortunately I was unable to post this into a QA session my connection
> kept
> > > crashing .
> > >
> > > Hope that helps
> > >
> > > "Wanderer" wrote:
> > >
> > > > Warning - this is quite a LONG post.
> > > >
> > > > Hi all,
> > > >
> > > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
> picture - I
> > > > want to do some tracing in our production environment, and have jobs
> set up
> > > > to automaGically run the traces, so I am building the traces in an SP,
> via
> > > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
> called from
> > > > SQL scheduled jobs, and will happily create the trace files. I have
> another
> > > > SP to then copy these files to another server, and automaGically
> import them
> > > > in some tables for analyzing.
> > > >
> > > > All good and well, so far, EXCEPT that while my trace get's created
> with no
> > > > errors, when I look at the trace data, it seems that the filters are
> not
> > > > being applied. When I open it up from Profiler, I can see the trace
> > > > properties, and the Events and Data columns are all correct, but there
> are no
> > > > Filters. Anyone see where I am going wrong?
> > > >
> > > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > > The SP
> > > >
> > > > if exists (select 1 from sysobjects where name => > > > 'Up_Auto_Profiler_Perf') drop procedure Up_Auto_Profiler_Perfgocreate
> > > > procedure Up_Auto_Profiler_Perf @.TraceFile nvarchar(128), --Nvarchar
> as
> > > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > > datetime, --The time to stop the trace. @.MaxSize BigInt = 10, --BigInt
> as
> > > > required by SP_CREATE_TRACE. The maximum size per
> > > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on (in
> > > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
> facilitate
> > > > the creation of profiler traces that write to files.-- -- Example:--
> exec
> > > > Up_Auto_Profiler_Perf
> > > >
> 'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
> -- Possible
> > > > future additions:-- 1> simple enhancement to specify DB to store data,
> and
> > > > table.-- 2> accepting a parameter instead of apply the default .trc.
> Use the
> > > > .trc as default-- 3> implementing default value's for dir's, so that
> it can
> > > > run without parm's ... good or bad...- Change Control: version 1 -
> Regan
> > > > Galbraith 2004-12-28-- Creation and adding of comment-- version 1.2 -
> > > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to allow
> > > > generation of limited data, -- filtering on duration-- Added Output
> > > > parametre @.TracId to return for lookup on trace. --AS--Declare
> Control
> > > > Variabledeclare @.ReturnCode Int--Declare Option Variables declare
> @.Option
> > > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > > @.ComparisonOperator intdeclare @.ColumnId intdeclare @.LogicalOperator
> > > > int--Set Option Variableset @.Option => 2 --TraceFileRollOver --Specifies
> > > > that when the max_file_size is reached, the current trace file is
> closed and
> > > > a new file is created.--Set Trace Filter to exclude System Ids - that
> is
> > > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator => 2 --Greater
> > > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
> @.TraceId
> > > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
> @.ReturnCode=@.@.Errorif
> > > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> Returned when
> > > > options specified are incompatible.' if @.ReturnCode = 12 Print 'Error
> 12 -
> > > > Cannot create tracefile - check if file already exists, or this trace
> already
> > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory. Returned
> when
> > > > there is not enough memory to perform the specified action.' if
> @.ReturnCode => > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop time
> > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error 15 -
> > > > Invalid parameters. Returned when the user supplied incompatible
> > > > parameters.' else Print 'Unexpected and Unknown error In creating
> trace -
> > > > Please review' Goto ErrorHandlerEnd--Populate Trace with
> Events--SECTION
> > > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor Openset
> > > > @.EventId = 53exec sp_trace_setevent
> @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this set is
> :
> > > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND WARNINGS--
> this set
> > > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set is :
> > > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
> Lock:DeadLockChainset
> > > > @.EventId = 59exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is : Lock:Timeoutset
> @.EventId
> > > > = 27exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this set is
> :
> > > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
> @.EventId => > > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
> Statistics--
> > > > set @.EventId = 98-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
> Text-- set
> > > > @.EventId = 96-- exec sp_trace_setevent
> @.TraceId,@.EventId,2,@.On --BinaryData--
> > > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION STORED
> > > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData--this set is SP:RPC:Completedset
> @.EventId
> > > > = 10exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set is :
> SQL
> > > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is :
> SQL
> > > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
> SQL:StmtCompleted-- set
> > > > @.EventId = 41-- exec sp_trace_setevent
> @.TraceId,@.EventId,18,@.On --cpu-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
> sp_trace_setevent
> > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
> @.TraceId,1select
> > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13 Print
> > > > 'ERROR 13 - Out of memory. Returned when there is not enough memory to
> > > > perform the specified action.' else if @.ReturnCode = 9 Print 'ERROR
> 9 -
> > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> print
> > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR 1 -
> Unknown
> > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> 'ERROR
> > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The trace
> is
> > > > currently running. Changing the trace at this time will result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is not
> > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified Column
> is not
> > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 - The
> > > > specified Comparison Operator is not valid. ' else if @.ReturnCode = 7
> print
> > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> @.ReturnCode
> > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.' else if
> > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when there
> is not
> > > > enough memory to perform the specified action.' else if @.ReturnCode => > > > 16 print 'ERROR 16 - The function is not valid for this trace.' else
> Print
> > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif @.DurationFilterValue
> <>
> > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > --Greater than exec sp_trace_setfilter
> > > >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> select
> > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1 print
> > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 -
> The
> > > > trace is currently running. Changing the trace at this time will
> result in an
> > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified Column
> is
> > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> Column
> > > > is not allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR
> 6 -
> > > > The specified Comparison Operator is not valid. ' else if @.ReturnCode
> => > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.' else
> if
> > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > Returned when there is not enough memory to perform the specified
> > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function is
> not
> > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > >
> > > >
> > > > The Code that calls the trace
> > > >
> > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime DATETIMEDECLARE
> > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> INTEGERDECLARE
> > > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error VariablesSET
> > > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
> Variablesset
> > > > @.MinutesToRun = 5 -- this is how long the trace will run forset
> @.MaxSize => > > > 100 -- this is the maximum size for a file, in MBset @.DurationFilter => > > > 1000--Set Running Variables--the time the trace will stopset
> @.RunStopTime => > > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full name
> (not
> > > > UNC)set @.FileName => > > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > > varchar(20))--Create trace, writing out to tracefile, until
> endtimeexec
> > > > @.Rc=Up_Auto_Profiler_Perf
> > > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output--Error
> > > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
> ('Create/Run of
> > > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > > >
> > > >
> > > > Ta...
> > > >
>
>|||Wanderer,
as per my email, I was intimating that you open profiler and set the filter
manually, then script out the trace and cut the section that performs the
sp_trace_setfilter into your procedure substituting your parameters for
hardcoded values. then run your procedure and check the trc files to see if
the traces have been set. . from your post it is obvious that profiler does
not understand the parameters you have passed into sp_trace_setfilter and as
a result it is unable to present it in the trc file. PS. out of interest
what are you trying to filter?
PS. I don't dispute that your trace runs fine . . I am merely trying to
help resolve the problem
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:B40C9908-9984-452E-88A6-FF3D1A453ABD@.microsoft.com...
> I'm not having datatype errors. The SP runs fine, EXCEPT that it doesn't
> seem to apply the filter. When I open the trace file in Profiler, there
are
> NO filters set. Yet the SP_TRACE_SETFILTER does not return any error's
> (you'll see I do do error checking after the SP_TRACE_SETFILTER commands.
>
> "Olu Adedeji" wrote:
> > I have never had problems with this procedure, however I try and stick
with
> > the syntax format as seen below(one of my earlier posts ie. using
> > @.bigintfilter and @.intfilter input parameters) because of the nature of
the
> > procedure(ie. it cannot be viewed using sp_helptext for obvious reasons)
> >
> >
> > CREATE PROCEDURE sp_DBA_LRQ
> > @.duration int, -- in minutes
> > @.QueryDuration int = 5, -- in seconds
> > @.filename nvarchar(100)= @.@.servername, -- script appends yyyymmddhh
> > @.dbname sysname = null, -- the database to monitor (null = monitor all
> > databases)
> > @.maxfilesize bigint = 20, -- expected size of trc file in MB
> > @.filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to
include
> > backslash
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > -- Name: sp_DBA_LRQ
> > -- Date: 22/10/2004
> > -- Author: Olu Adedeji
> > -- Note: auto set @.duration (mins)
> > -- auto set @.filename (no extension)
> > -- monitor @.dbname (database you wish to run the sql against)
> > -- The latest file will always be servername_yyyymmdd.trc
> > -- to use this locally set @.filedir = c:\temp\ -- remember to add the
'\'
> > at the end of the directory
> > -- Capture Long Running Queries(LRQ)
> >
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > ++++++++++++++++++++++++++++++++
> > -- WHAT WHO WHEN
> --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++
> > +++++++++++++++++++++++++++++++++
> > -- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL)
to
> > -- Resolve Execution plan annomalies on prod servers Olu
> > Adedeji 16/12/2004
> > -- Version: 1.04 - customised to capture specific information for
PrecisDM
> > Olu Adedeji 16/12/2004
> > -- Version: 1.05 - customised to capture long running Queries Olu
> > Adedeji 22/12/2004
> > -- I have included to capture SQLBatches and Stored Procs
> >
> > -- Display Procedure information
> >
> > Print 'Application: SQL Profiler'
> > Print 'Procedure: sp_DBA_LRQ'
> > Print 'Function: Capture Long Running Queries(LRQ) on ' + @.@.servername +
'
> > for ' + convert(varchar,@.duration) + '(mins)'
> > Print 'MaxFileSize: '+ convert(varchar,@.maxfilesize) + 'Mb'
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> >
> > -- Create a Queue
> > declare @.rc int,
> > @.TraceID int, -- sqlassigns the trace ID
> > @.DateTime datetime, -- trace stop time
> > @.CurrentTime datetime, -- current time
> > @.appendDate int, -- append datestamp
> > @.Cmd varchar(1000),
> > @.RenameCmd varchar(1000),
> > @.newFileName varchar(1000)
> >
> > -- auto set stoptime
> > set @.CurrentTime = current_timestamp
> > set @.DateTime = dateadd(mi,@.duration,@.CurrentTime)
> > --set @.maxfilesize = 50 -- set max file size to 10Mb
> > set @.filename = @.filename + '_' + (select
> > convert(varchar,current_timestamp,112))
> > set @.newFileName = @.filename
> > set @.filename = @.filedir + @.filename
> >
> > -- create temp table
> > create table ##fileexists (FileExists int, DirectoryExists int,
> > ParentDirectoryExists int)
> >
> > -- New file name to rename to
> > declare @.ActualFilename varchar(100) -- actual file to rename (with .trc
> > extension)
> > set @.ActualFilename = @.filename + '.trc'
> >
> > -- populate temp table
> > insert into ##fileexists
> > exec master.dbo.xp_fileexist @.Actualfilename
> >
> >
> > -- Check that file exists and rename file or proceed if it does not
exist
> > if (select fileexists from ##fileexists where fileexists = 1) is not
null
> > begin
> > -- I have used 114 to allow for 24h time conversion (similar to how
> > logshipping works)
> > select @.appendDate = (select CASE WHEN
> > convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
> > convert(varchar,datepart(hh,current_timestamp),114)
> > WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
> > convert(varchar,datepart(hh,current_timestamp),114)
> > END
> > + CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
> > THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
> > WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
> > convert(varchar,datepart(mi,current_timestamp),114)
> > END)
> > select @.RenameCmd = 'rename ' + @.filename + '.trc ' + @.NewFileName +
'_'
> > + convert(varchar,@.AppendDate) + '.trc'
> >
> > -- display the rename command (useful if file exists)
> > Print @.RenameCmd
> > exec master.dbo.xp_cmdshell @.RenameCmd,NO_OUTPUT
> > End
> >
> > exec @.rc = sp_trace_create @.TraceID output, 0, @.filename, @.maxfilesize,
> > @.Datetime
> > if (@.rc != 0) goto error
> >
> > -- Client side File and Table cannot be scripted
> >
> > -- Set the events
> > declare @.on bit
> > set @.on = 1
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture RPC:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 10, 1, @.on
> > exec sp_trace_setevent @.TraceID, 10, 2, @.on
> > exec sp_trace_setevent @.TraceID, 10, 3, @.on
> > exec sp_trace_setevent @.TraceID, 10, 8, @.on
> > exec sp_trace_setevent @.TraceID, 10, 9, @.on
> > exec sp_trace_setevent @.TraceID, 10, 10, @.on
> > exec sp_trace_setevent @.TraceID, 10, 11, @.on
> > exec sp_trace_setevent @.TraceID, 10, 12, @.on
> > exec sp_trace_setevent @.TraceID, 10, 13, @.on
> > exec sp_trace_setevent @.TraceID, 10, 14, @.on
> > exec sp_trace_setevent @.TraceID, 10, 15, @.on
> > exec sp_trace_setevent @.TraceID, 10, 21, @.on
> > exec sp_trace_setevent @.TraceID, 10, 26, @.on
> > exec sp_trace_setevent @.TraceID, 10, 35, @.on
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture SP:Completed
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 43, 1, @.on
> > exec sp_trace_setevent @.TraceID, 43, 2, @.on
> > exec sp_trace_setevent @.TraceID, 43, 3, @.on
> > exec sp_trace_setevent @.TraceID, 43, 8, @.on
> > exec sp_trace_setevent @.TraceID, 43, 9, @.on
> > exec sp_trace_setevent @.TraceID, 43, 10, @.on
> > exec sp_trace_setevent @.TraceID, 43, 11, @.on
> > exec sp_trace_setevent @.TraceID, 43, 12, @.on
> > exec sp_trace_setevent @.TraceID, 43, 13, @.on
> > exec sp_trace_setevent @.TraceID, 43, 14, @.on
> > exec sp_trace_setevent @.TraceID, 43, 15, @.on
> > exec sp_trace_setevent @.TraceID, 43, 21, @.on
> > exec sp_trace_setevent @.TraceID, 43, 26, @.on
> > exec sp_trace_setevent @.TraceID, 43, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > --Capture SQL:BatchCompleted
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 12, 1, @.on
> > exec sp_trace_setevent @.TraceID, 12, 2, @.on
> > exec sp_trace_setevent @.TraceID, 12, 3, @.on
> > exec sp_trace_setevent @.TraceID, 12, 8, @.on
> > exec sp_trace_setevent @.TraceID, 12, 9, @.on
> > exec sp_trace_setevent @.TraceID, 12, 10, @.on
> > exec sp_trace_setevent @.TraceID, 12, 11, @.on
> > exec sp_trace_setevent @.TraceID, 12, 12, @.on
> > exec sp_trace_setevent @.TraceID, 12, 13, @.on
> > exec sp_trace_setevent @.TraceID, 12, 14, @.on
> > exec sp_trace_setevent @.TraceID, 12, 15, @.on
> > exec sp_trace_setevent @.TraceID, 12, 21, @.on
> > exec sp_trace_setevent @.TraceID, 12, 26, @.on
> > exec sp_trace_setevent @.TraceID, 12, 35, @.on
> >
> > --++++++++++++++++++++++++++++++++++++++
> > -- Capture Generic Information
> > --++++++++++++++++++++++++++++++++++++++
> > exec sp_trace_setevent @.TraceID, 70, 1, @.on
> > exec sp_trace_setevent @.TraceID, 70, 2, @.on
> > exec sp_trace_setevent @.TraceID, 70, 3, @.on
> > exec sp_trace_setevent @.TraceID, 70, 8, @.on
> > exec sp_trace_setevent @.TraceID, 70, 9, @.on
> > exec sp_trace_setevent @.TraceID, 70, 10, @.on
> > exec sp_trace_setevent @.TraceID, 70, 11, @.on
> > exec sp_trace_setevent @.TraceID, 70, 12, @.on
> > exec sp_trace_setevent @.TraceID, 70, 14, @.on
> > exec sp_trace_setevent @.TraceID, 70, 21, @.on
> > exec sp_trace_setevent @.TraceID, 70, 26, @.on
> > exec sp_trace_setevent @.TraceID, 70, 35, @.on
> > exec sp_trace_setevent @.TraceID, 71, 1, @.on
> > exec sp_trace_setevent @.TraceID, 71, 2, @.on
> > exec sp_trace_setevent @.TraceID, 71, 3, @.on
> > exec sp_trace_setevent @.TraceID, 71, 8, @.on
> > exec sp_trace_setevent @.TraceID, 71, 9, @.on
> > exec sp_trace_setevent @.TraceID, 71, 10, @.on
> > exec sp_trace_setevent @.TraceID, 71, 11, @.on
> > exec sp_trace_setevent @.TraceID, 71, 12, @.on
> > exec sp_trace_setevent @.TraceID, 71, 14, @.on
> > exec sp_trace_setevent @.TraceID, 71, 21, @.on
> > exec sp_trace_setevent @.TraceID, 71, 26, @.on
> > exec sp_trace_setevent @.TraceID, 71, 35, @.on
> > exec sp_trace_setevent @.TraceID, 72, 1, @.on
> > exec sp_trace_setevent @.TraceID, 72, 2, @.on
> > exec sp_trace_setevent @.TraceID, 72, 3, @.on
> > exec sp_trace_setevent @.TraceID, 72, 8, @.on
> > exec sp_trace_setevent @.TraceID, 72, 9, @.on
> > exec sp_trace_setevent @.TraceID, 72, 10, @.on
> > exec sp_trace_setevent @.TraceID, 72, 11, @.on
> > exec sp_trace_setevent @.TraceID, 72, 12, @.on
> > exec sp_trace_setevent @.TraceID, 72, 14, @.on
> > exec sp_trace_setevent @.TraceID, 72, 21, @.on
> > exec sp_trace_setevent @.TraceID, 72, 26, @.on
> > exec sp_trace_setevent @.TraceID, 72, 35, @.on
> > exec sp_trace_setevent @.TraceID, 74, 1, @.on
> > exec sp_trace_setevent @.TraceID, 74, 2, @.on
> > exec sp_trace_setevent @.TraceID, 74, 3, @.on
> > exec sp_trace_setevent @.TraceID, 74, 8, @.on
> > exec sp_trace_setevent @.TraceID, 74, 9, @.on
> > exec sp_trace_setevent @.TraceID, 74, 10, @.on
> > exec sp_trace_setevent @.TraceID, 74, 11, @.on
> > exec sp_trace_setevent @.TraceID, 74, 12, @.on
> > exec sp_trace_setevent @.TraceID, 74, 14, @.on
> > exec sp_trace_setevent @.TraceID, 74, 21, @.on
> > exec sp_trace_setevent @.TraceID, 74, 26, @.on
> > exec sp_trace_setevent @.TraceID, 74, 35, @.on
> > exec sp_trace_setevent @.TraceID, 77, 1, @.on
> > exec sp_trace_setevent @.TraceID, 77, 2, @.on
> > exec sp_trace_setevent @.TraceID, 77, 3, @.on
> > exec sp_trace_setevent @.TraceID, 77, 8, @.on
> > exec sp_trace_setevent @.TraceID, 77, 9, @.on
> > exec sp_trace_setevent @.TraceID, 77, 10, @.on
> > exec sp_trace_setevent @.TraceID, 77, 11, @.on
> > exec sp_trace_setevent @.TraceID, 77, 12, @.on
> > exec sp_trace_setevent @.TraceID, 77, 14, @.on
> > exec sp_trace_setevent @.TraceID, 77, 21, @.on
> > exec sp_trace_setevent @.TraceID, 77, 26, @.on
> > exec sp_trace_setevent @.TraceID, 77, 35, @.on
> > exec sp_trace_setevent @.TraceID, 78, 1, @.on
> > exec sp_trace_setevent @.TraceID, 78, 2, @.on
> > exec sp_trace_setevent @.TraceID, 78, 3, @.on
> > exec sp_trace_setevent @.TraceID, 78, 8, @.on
> > exec sp_trace_setevent @.TraceID, 78, 9, @.on
> > exec sp_trace_setevent @.TraceID, 78, 10, @.on
> > exec sp_trace_setevent @.TraceID, 78, 11, @.on
> > exec sp_trace_setevent @.TraceID, 78, 12, @.on
> > exec sp_trace_setevent @.TraceID, 78, 14, @.on
> > exec sp_trace_setevent @.TraceID, 78, 21, @.on
> > exec sp_trace_setevent @.TraceID, 78, 26, @.on
> > exec sp_trace_setevent @.TraceID, 78, 35, @.on
> > exec sp_trace_setevent @.TraceID, 100, 1, @.on
> > exec sp_trace_setevent @.TraceID, 100, 2, @.on
> > exec sp_trace_setevent @.TraceID, 100, 3, @.on
> > exec sp_trace_setevent @.TraceID, 100, 8, @.on
> > exec sp_trace_setevent @.TraceID, 100, 9, @.on
> > exec sp_trace_setevent @.TraceID, 100, 10, @.on
> > exec sp_trace_setevent @.TraceID, 100, 11, @.on
> > exec sp_trace_setevent @.TraceID, 100, 12, @.on
> > exec sp_trace_setevent @.TraceID, 100, 14, @.on
> > exec sp_trace_setevent @.TraceID, 100, 21, @.on
> > exec sp_trace_setevent @.TraceID, 100, 26, @.on
> > exec sp_trace_setevent @.TraceID, 100, 35, @.on
> >
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > -- set database filter if @.dbname is not null
> > if @.dbname is not null
> > begin
> > -- check @.dbname is on the server
> > if (select db_id(@.dbname)) is not null
> > set @.intfilter = db_id(@.dbname)
> > exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> > end
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> >
> > -- set filter for Long Running Query(LRQ) Duration
> > -- Added this 22/12/2004
> > select @.bigintfilter = (@.QueryDuration*1000) -- need to set this in
> > milliseconds(ms)
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> >
> > -- 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:
> > -- display Trace information (can output this to a logfile in sqlagent)
> > select * from ::fn_trace_getinfo(default)
> >
> > -- clear down temp table
> > if (select object_id('tempdb.dbo.##fileexists') from
> > tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
> > drop table ##fileexists
> > GO
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E8FD0981-2E9F-4D42-B489-304E8AE57E16@.microsoft.com...
> > > Yes - @.DurationFilter needs to be BIGINT, and is - I ran with INT
before,
> > and
> > > got error message saying that it needed to be BIGINT.
> > >
> > > "Server: Msg 214, Level 16, State 3, Procedure sp_trace_setfilter,
Line
> > 272
> > > Procedure expects parameter '@.value' of type 'bigint'."
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > the data type of the @.durationfiltervalue must correspond with the
data
> > type
> > > > of the column to be filtered. otherwise it will not work.
> > > >
> > > > unfortunately I was unable to post this into a QA session my
connection
> > kept
> > > > crashing .
> > > >
> > > > Hope that helps
> > > >
> > > > "Wanderer" wrote:
> > > >
> > > > > Warning - this is quite a LONG post.
> > > > >
> > > > > Hi all,
> > > > >
> > > > > Anyone got experience using SP_TRACE_SETFILTER? Let me paint the
> > picture - I
> > > > > want to do some tracing in our production environment, and have
jobs
> > set up
> > > > > to automaGically run the traces, so I am building the traces in an
SP,
> > via
> > > > > SP_TRACE sp's. This main sp (UP_Auto_Profiler_Perf) can then get
> > called from
> > > > > SQL scheduled jobs, and will happily create the trace files. I
have
> > another
> > > > > SP to then copy these files to another server, and automaGically
> > import them
> > > > > in some tables for analyzing.
> > > > >
> > > > > All good and well, so far, EXCEPT that while my trace get's
created
> > with no
> > > > > errors, when I look at the trace data, it seems that the filters
are
> > not
> > > > > being applied. When I open it up from Profiler, I can see the
trace
> > > > > properties, and the Events and Data columns are all correct, but
there
> > are no
> > > > > Filters. Anyone see where I am going wrong?
> > > > >
> > > > > So, here's the code FEEL FREE TO SUGGEST IMPROVEMENTS
> > > > > The SP
> > > > >
> > > > > if exists (select 1 from sysobjects where name => > > > > 'Up_Auto_Profiler_Perf') drop procedure
Up_Auto_Profiler_Perfgocreate
> > > > > procedure Up_Auto_Profiler_Perf @.TraceFile
nvarchar(128), --Nvarchar
> > as
> > > > > required by SP_CREATE_TRACE. The File to be created. @.StopTime
> > > > > datetime, --The time to stop the trace. @.MaxSize BigInt =10, --BigInt
> > as
> > > > > required by SP_CREATE_TRACE. The maximum size per
> > > > > file. @.DurationFilterValue BIGINT = 0, --The duration to filter on
(in
> > > > > ms). @.TraceID int OUTPUT-- Create By: Regan Galbraith-- Create
> > > > > On: 2004-12-28-- Purpose: -- This stored procedure was written to
> > facilitate
> > > > > the creation of profiler traces that write to files.-- --
Example:--
> > exec
> > > > > Up_Auto_Profiler_Perf
> > > > >
> >
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500-
> > -- Possible
> > > > > future additions:-- 1> simple enhancement to specify DB to store
data,
> > and
> > > > > table.-- 2> accepting a parameter instead of apply the default
.trc.
> > Use the
> > > > > .trc as default-- 3> implementing default value's for dir's, so
that
> > it can
> > > > > run without parm's ... good or bad...- Change Control: version
1 -
> > Regan
> > > > > Galbraith 2004-12-28-- Creation and adding of comment-- version
1.2 -
> > > > > Regan Galbraith 2004-12-29-- Added @.DurationFilterValue logic to
allow
> > > > > generation of limited data, -- filtering on duration-- Added
Output
> > > > > parametre @.TracId to return for lookup on trace. --AS--Declare
> > Control
> > > > > Variabledeclare @.ReturnCode Int--Declare Option Variables
declare
> > @.Option
> > > > > intdeclare @.EventId intdeclare @.On bitdeclare @.Value intdeclare
> > > > > @.ComparisonOperator intdeclare @.ColumnId intdeclare
@.LogicalOperator
> > > > > int--Set Option Variableset @.Option => > 2 --TraceFileRollOver --Specifies
> > > > > that when the max_file_size is reached, the current trace file is
> > closed and
> > > > > a new file is created.--Set Trace Filter to exclude System Ids -
that
> > is
> > > > > ObjectId > 100set @.Value = 100 set @.ColumnId = 22 --0bjectidset
> > > > > @.LogicalOperator = 0 --and (1 = OR)set @.ComparisonOperator => > 2 --Greater
> > > > > than--Set Control Variablesset @.ReturnCode = 0 --No Errorset @.On => > > > > 1 --True--Create a trace, retrieve @.TraceId exec sp_trace_create
> > @.TraceId
> > > > > output,@.Option,@.TraceFile,@.MaxSize,@.StopTimeselect
> > @.ReturnCode=@.@.Errorif
> > > > > @.ReturnCode <> 0 Begin if @.ReturnCode = 1 Print 'Error 1 - Unknown
> > > > > error.' if @.ReturnCode = 10 Print 'Error 10 - Invalid options.
> > Returned when
> > > > > options specified are incompatible.' if @.ReturnCode = 12 Print
'Error
> > 12 -
> > > > > Cannot create tracefile - check if file already exists, or this
trace
> > already
> > > > > running' if @.ReturnCode = 13 Print 'Error 13 - Out of memory.
Returned
> > when
> > > > > there is not enough memory to perform the specified action.' if
> > @.ReturnCode => > > > > 14 Print 'Error 14 - Invalid stop time. Returned when the stop
time
> > > > > specified has already happened.' if @.ReturnCode = 15 Print 'Error
15 -
> > > > > Invalid parameters. Returned when the user supplied incompatible
> > > > > parameters.' else Print 'Unexpected and Unknown error In creating
> > trace -
> > > > > Please review' Goto ErrorHandlerEnd--Populate Trace with
> > Events--SECTION
> > > > > CURSORS-- this set is : cursor executeset @.EventId = 74exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- this set is : Cursor
Openset
> > > > > @.EventId = 53exec sp_trace_setevent
> > @.TraceId,@.EventId,27,@.On --EventClassexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- this
set is
> > :
> > > > > Cursor Recompileset @.EventId = 75exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION ERRORS AND
WARNINGS--
> > this set
> > > > > is : MissingJoinPredicateset @.EventId = 80exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass--SECTION LOCKS -- this set
is :
> > > > > Lock:DeadLockset @.EventId = 25exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
> > Lock:DeadLockChainset
> > > > > @.EventId = 59exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- this set is :
Lock:Timeoutset
> > @.EventId
> > > > > = 27exec sp_trace_setevent
@.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,24,@.On --indexIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,32,@.On --Modeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID--SECTION PERFORMANCE-- this
set is
> > :
> > > > > Execution Plan-- set @.EventId = 68-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- this set is : Show Planset
> > @.EventId => > > > > 97exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryDataexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClassexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- --this set is : Show Plan
> > Statistics--
> > > > > set @.EventId = 98-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,2,@.On --BinaryData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- -- --this set is : Show Plan
> > Text-- set
> > > > > @.EventId = 96-- exec sp_trace_setevent
> > @.TraceId,@.EventId,2,@.On --BinaryData--
> > > > > exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass--
exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData--SECTION
STORED
> > > > > PROCEDURES--this set is SP:Recompileset @.EventId = 37exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,29,@.On --NestLevelexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectIDexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,34,@.On --ObjectNameexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData--this set is
SP:RPC:Completedset
> > @.EventId
> > > > > = 10exec sp_trace_setevent
@.TraceId,@.EventId,27,@.On --EventClassexec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --durationexec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writes--SECTION TRANSACTIONS--this set
is :
> > SQL
> > > > > Transaction-- set @.EventId = 50-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --duration-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTime-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,21,@.On --EventSubClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,34,@.On --ObjectName-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,4,@.On --TRansactionID--SECTION TSQL--this set is
:
> > SQL
> > > > > BatchCompletedset @.EventId = 12exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,18,@.On --cpuexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,13,@.On --durationexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,15,@.On --EndTimeexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClassexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Readsexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextDataexec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writes-- --this set is :
> > SQL:StmtCompleted-- set
> > > > > @.EventId = 41-- exec sp_trace_setevent
> > @.TraceId,@.EventId,18,@.On --cpu-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration-- exec
> > > > > sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime-- exec
> > sp_trace_setevent
> > > > > @.TraceId,@.EventId,27,@.On --EventClass-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,25,@.On --IntegerData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,29,@.On --NestLevel-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,22,@.On --ObjectID-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,16,@.On --Reads-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,1,@.On --TextData-- exec sp_trace_setevent
> > > > > @.TraceId,@.EventId,17,@.On --Writesexec sp_trace_setstatus
> > @.TraceId,1select
> > > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 13
Print
> > > > > 'ERROR 13 - Out of memory. Returned when there is not enough
memory to
> > > > > perform the specified action.' else if @.ReturnCode = 9 Print
'ERROR
> > 9 -
> > > > > The specified Trace Handle is not valid.' else if @.ReturnCode = 8
> > print
> > > > > 'ERROR 8 - The specified Status is not valid.' else Print 'ERROR
1 -
> > Unknown
> > > > > Error' GoTo ErrorHandlerendexec sp_trace_setfilter
> > > > >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Valueselect
> > > > > @.ReturnCode=@.@.Errorif @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > 'ERROR
> > > > > 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR 2 - The
trace
> > is
> > > > > currently running. Changing the trace at this time will result in
an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is not
> > > > > valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
Column
> > is not
> > > > > allowed for filtering.' else if @.ReturnCode = 6 print 'ERROR 6 -
The
> > > > > specified Comparison Operator is not valid. ' else if @.ReturnCode
= 7
> > print
> > > > > 'ERROR 7 - The specified Logical Operator is not valid.' else if
> > @.ReturnCode
> > > > > = 9 print 'ERROR 9 - The specified Trace Handle is not valid.'
else if
> > > > > @.ReturnCode = 13 print 'ERROR 13 - Out of memory. Returned when
there
> > is not
> > > > > enough memory to perform the specified action.' else if
@.ReturnCode => > > > > 16 print 'ERROR 16 - The function is not valid for this trace.'
else
> > Print
> > > > > 'ERROR x - Unknown Error' GoTo ErrorHandlerendif
@.DurationFilterValue
> > <>
> > > > > 0 begin set @.columnId = 13 --Duration set @.ComparisonOperator = 2
> > > > > --Greater than exec sp_trace_setfilter
> > > > >
> >
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > select
> > > > > @.ReturnCode=@.@.Error if @.ReturnCode <> 0 Begin if @.ReturnCode = 1
print
> > > > > 'ERROR 1 - Unknown error.' else if @.ReturnCode = 2 Print 'ERROR
2 -
> > The
> > > > > trace is currently running. Changing the trace at this time will
> > result in an
> > > > > error.' else if @.ReturnCode = 4 Print 'ERROR 4 - The specified
Column
> > is
> > > > > not valid.' else if @.ReturnCode = 5 print 'ERROR 5 - The specified
> > Column
> > > > > is not allowed for filtering.' else if @.ReturnCode = 6 print
'ERROR
> > 6 -
> > > > > The specified Comparison Operator is not valid. ' else if
@.ReturnCode
> > => > > > > 7 print 'ERROR 7 - The specified Logical Operator is not valid.'
else
> > if
> > > > > @.ReturnCode = 9 print 'ERROR 9 - The specified Trace Handle is not
> > > > > valid.' else if @.ReturnCode = 13 print 'ERROR 13 - Out of memory.
> > > > > Returned when there is not enough memory to perform the specified
> > > > > action.' else if @.ReturnCode = 16 print 'ERROR 16 - The function
is
> > not
> > > > > valid for this trace.' else Print 'ERROR x - Unknown Error' GoTo
> > > > > ErrorHandler endendErrorHandler:Return @.ReturnCode
> > > > >
> > > > >
> > > > > The Code that calls the trace
> > > > >
> > > > > DECLARE @.FileName NVARCHAR(128)DECLARE @.RunStopTime
DATETIMEDECLARE
> > > > > @.MaxSize BIGINTDECLARE @.DurationFilter BIGINTDECLARE
> > > > > @.TraceID INTEGERDECLARE @.MinutesToRun INTEGERDECLARE @.RC
> > INTEGERDECLARE
> > > > > @.DBID INTEGERDECLARE @.DBNAME NVARCHAR(128)-- Set Error
VariablesSET
> > > > > @.DBNAME = DB_NAME()SET @.DBID = DB_ID()SET @.rc = 0--Set Control
> > Variablesset
> > > > > @.MinutesToRun = 5 -- this is how long the trace will run forset
> > @.MaxSize => > > > > 100 -- this is the maximum size for a file, in MBset
@.DurationFilter => > > > > 1000--Set Running Variables--the time the trace will stopset
> > @.RunStopTime => > > > > dateadd(mi,@.MinutesToRun,getdate())--the file to be create - full
name
> > (not
> > > > > UNC)set @.FileName => > > > > 'C:\Auto_Prof\AutoProf_SP_' +cast(datepart(yyyy,getdate()) as
> > > > > char(4)) --Years +right(cast(datepart(m ,getdate())+100 as
> > > > > char(3)),2) --Months +cast(datepart(d,getdate()) as
> > > > > char(2))+'_' --Days +right(cast(datepart(hh,getdate())+100 as
> > > > > char(3)),2) --Hours +right(cast(datepart(mi,getdate())+100 as
> > > > > char(3)),2) --Minutes--Display variablesprint 'File created is :
> > > > > '+@.FileNameprint 'End time will be : '+cast(@.RunStopTime as
> > > > > varchar(20))--Create trace, writing out to tracefile, until
> > endtimeexec
> > > > > @.Rc=Up_Auto_Profiler_Perf
> > > > > @.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID
output--Error
> > > > > Handlingselect @.rcselect @.TraceIDSELECT * FROM
> > > > > ::fn_trace_getfilterinfo(@.TraceID)if (@.rc <> 0 ) RAISERROR
> > ('Create/Run of
> > > > > Trace FAILED', 16, 1, @.DBID, @.DBNAME)GO
> > > > >
> > > > >
> > > > > Ta...
> > > > >
> >
> >
> >|||Thanks Olu, for the responses - just realized I hadn't been polite enough to
acknowledge them!
I must have mis-understood yuo last mail - I thought you were proposing I
use your SP, or check how you are using the SP_TRACE_SETFILTER.
The specific Traces I am trying to turn on are:
set @.LogicalOperator = 0 --and (1 = OR)
set @.columnId = 13 --Duration
set @.ComparisonOperator = 2 --Greater than
@.DurationFilterValue - I am passing in 1000, i.e. 1 second
I see you are using comparison operator of 4 - greater than or equal to - I
will try that and see if that is somehopw causing the issue. If not, I will
create the trace via profiler, and then try and gen the trace and see what's
what.
Thanks for the help so far!!!
"Olu Adedeji" wrote:
> Wanderer,
> as per my email, I was intimating that you open profiler and set the filter
> manually, then script out the trace and cut the section that performs the
> sp_trace_setfilter into your procedure substituting your parameters for
> hardcoded values. then run your procedure and check the trc files to see if
> the traces have been set. . from your post it is obvious that profiler does
> not understand the parameters you have passed into sp_trace_setfilter and as
> a result it is unable to present it in the trc file. PS. out of interest
> what are you trying to filter?
>
> PS. I don't dispute that your trace runs fine . . I am merely trying to
> help resolve the problem
>|||Hmmm- there looks like mine:
--Profiler's trace
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
set @.bigintfilter = 1000
exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
set @.intfilter = 100
exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
--My trace
set @.LogicalOperator = 0
set @.columnId = 13
set @.ComparisonOperator = 4
@.DurationFilterValue = 1000 (bigint)
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
which is effectively
exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
I'll see if the hardcoding works better ... sighs
... and it would seem it doesn't! Grrr
"Wanderer" wrote:
> Thanks Olu, for the responses - just realized I hadn't been polite enough to
> acknowledge them!
> I must have mis-understood yuo last mail - I thought you were proposing I
> use your SP, or check how you are using the SP_TRACE_SETFILTER.
> The specific Traces I am trying to turn on are:
> set @.LogicalOperator = 0 --and (1 = OR)
> set @.columnId = 13 --Duration
> set @.ComparisonOperator = 2 --Greater than
> @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> I see you are using comparison operator of 4 - greater than or equal to - I
> will try that and see if that is somehopw causing the issue. If not, I will
> create the trace via profiler, and then try and gen the trace and see what's
> what.
> Thanks for the help so far!!!
> "Olu Adedeji" wrote:
> > Wanderer,
> >
> > as per my email, I was intimating that you open profiler and set the filter
> > manually, then script out the trace and cut the section that performs the
> > sp_trace_setfilter into your procedure substituting your parameters for
> > hardcoded values. then run your procedure and check the trc files to see if
> > the traces have been set. . from your post it is obvious that profiler does
> > not understand the parameters you have passed into sp_trace_setfilter and as
> > a result it is unable to present it in the trc file. PS. out of interest
> > what are you trying to filter?
> >
> >
> > PS. I don't dispute that your trace runs fine . . I am merely trying to
> > help resolve the problem
> >
>|||sure thing! I hope that works. . .
"Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> Hmmm- there looks like mine:
> --Profiler's trace
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> set @.bigintfilter = 1000
> exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> set @.intfilter = 100
> exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> --My trace
> set @.LogicalOperator = 0
> set @.columnId = 13
> set @.ComparisonOperator = 4
> @.DurationFilterValue = 1000 (bigint)
> exec sp_trace_setfilter
>
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> which is effectively
> exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> I'll see if the hardcoding works better ... sighs
> ... and it would seem it doesn't! Grrr
>
> "Wanderer" wrote:
> > Thanks Olu, for the responses - just realized I hadn't been polite
enough to
> > acknowledge them!
> >
> > I must have mis-understood yuo last mail - I thought you were proposing
I
> > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> >
> > The specific Traces I am trying to turn on are:
> >
> > set @.LogicalOperator = 0 --and (1 = OR)
> > set @.columnId = 13 --Duration
> > set @.ComparisonOperator = 2 --Greater than
> > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> >
> > I see you are using comparison operator of 4 - greater than or equal
to - I
> > will try that and see if that is somehopw causing the issue. If not, I
will
> > create the trace via profiler, and then try and gen the trace and see
what's
> > what.
> >
> > Thanks for the help so far!!!
> >
> > "Olu Adedeji" wrote:
> >
> > > Wanderer,
> > >
> > > as per my email, I was intimating that you open profiler and set the
filter
> > > manually, then script out the trace and cut the section that performs
the
> > > sp_trace_setfilter into your procedure substituting your parameters
for
> > > hardcoded values. then run your procedure and check the trc files to
see if
> > > the traces have been set. . from your post it is obvious that profiler
does
> > > not understand the parameters you have passed into sp_trace_setfilter
and as
> > > a result it is unable to present it in the trc file. PS. out of
interest
> > > what are you trying to filter?
> > >
> > >
> > > PS. I don't dispute that your trace runs fine . . I am merely trying
to
> > > help resolve the problem
> > >
> >|||Hmm - not working.
When I run:
SELECT * FROM ::fn_trace_getfilterinfo(1)
I get 0 rows, that implies to me that the filters are not getting created.
Can you confirm on a test that you DO get something from this?
Thanks
"Olu Adedeji" wrote:
> sure thing! I hope that works. . .
> "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > Hmmm- there looks like mine:
> > --Profiler's trace
> > -- Set the Filters
> > declare @.intfilter int
> > declare @.bigintfilter bigint
> >
> > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > set @.bigintfilter = 1000
> > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> >
> > set @.intfilter = 100
> > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> >
> > --My trace
> > set @.LogicalOperator = 0
> > set @.columnId = 13
> > set @.ComparisonOperator = 4
> > @.DurationFilterValue = 1000 (bigint)
> > exec sp_trace_setfilter
> >
> @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > which is effectively
> > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> >
> > I'll see if the hardcoding works better ... sighs
> >
> > ... and it would seem it doesn't! Grrr
> >
> >
> >
> > "Wanderer" wrote:
> >
> > > Thanks Olu, for the responses - just realized I hadn't been polite
> enough to
> > > acknowledge them!
> > >
> > > I must have mis-understood yuo last mail - I thought you were proposing
> I
> > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > >
> > > The specific Traces I am trying to turn on are:
> > >
> > > set @.LogicalOperator = 0 --and (1 = OR)
> > > set @.columnId = 13 --Duration
> > > set @.ComparisonOperator = 2 --Greater than
> > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > >
> > > I see you are using comparison operator of 4 - greater than or equal
> to - I
> > > will try that and see if that is somehopw causing the issue. If not, I
> will
> > > create the trace via profiler, and then try and gen the trace and see
> what's
> > > what.
> > >
> > > Thanks for the help so far!!!
> > >
> > > "Olu Adedeji" wrote:
> > >
> > > > Wanderer,
> > > >
> > > > as per my email, I was intimating that you open profiler and set the
> filter
> > > > manually, then script out the trace and cut the section that performs
> the
> > > > sp_trace_setfilter into your procedure substituting your parameters
> for
> > > > hardcoded values. then run your procedure and check the trc files to
> see if
> > > > the traces have been set. . from your post it is obvious that profiler
> does
> > > > not understand the parameters you have passed into sp_trace_setfilter
> and as
> > > > a result it is unable to present it in the trc file. PS. out of
> interest
> > > > what are you trying to filter?
> > > >
> > > >
> > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> to
> > > > help resolve the problem
> > > >
> > >
>
>|||Ok - so it seems that, for some reason, the sp_trace_setfilter is not
working. Going to try and build in some logic to catch and check why.
Anything that you can see from my SP, that points the way, I'd appreciate
hearing it.
Have a great New Year's eve, and a peaceful and prosperous new year
"Wanderer" wrote:
> Hmm - not working.
> When I run:
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I get 0 rows, that implies to me that the filters are not getting created.
> Can you confirm on a test that you DO get something from this?
> Thanks
> "Olu Adedeji" wrote:
> > sure thing! I hope that works. . .
> >
> > "Wanderer" <Wanderer@.discussions.microsoft.com> wrote in message
> > news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@.microsoft.com...
> > > Hmmm- there looks like mine:
> > > --Profiler's trace
> > > -- Set the Filters
> > > declare @.intfilter int
> > > declare @.bigintfilter bigint
> > >
> > > exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
> > > set @.bigintfilter = 1000
> > > exec sp_trace_setfilter @.TraceID, 13, 0, 4, @.bigintfilter
> > >
> > > set @.intfilter = 100
> > > exec sp_trace_setfilter @.TraceID, 22, 0, 4, @.intfilter
> > >
> > > --My trace
> > > set @.LogicalOperator = 0
> > > set @.columnId = 13
> > > set @.ComparisonOperator = 4
> > > @.DurationFilterValue = 1000 (bigint)
> > > exec sp_trace_setfilter
> > >
> > @.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
> > > which is effectively
> > > exec sp_trace_setfilter @.tracid,13,0,4,@.DurationFilterValue
> > >
> > > I'll see if the hardcoding works better ... sighs
> > >
> > > ... and it would seem it doesn't! Grrr
> > >
> > >
> > >
> > > "Wanderer" wrote:
> > >
> > > > Thanks Olu, for the responses - just realized I hadn't been polite
> > enough to
> > > > acknowledge them!
> > > >
> > > > I must have mis-understood yuo last mail - I thought you were proposing
> > I
> > > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > > >
> > > > The specific Traces I am trying to turn on are:
> > > >
> > > > set @.LogicalOperator = 0 --and (1 = OR)
> > > > set @.columnId = 13 --Duration
> > > > set @.ComparisonOperator = 2 --Greater than
> > > > @.DurationFilterValue - I am passing in 1000, i.e. 1 second
> > > >
> > > > I see you are using comparison operator of 4 - greater than or equal
> > to - I
> > > > will try that and see if that is somehopw causing the issue. If not, I
> > will
> > > > create the trace via profiler, and then try and gen the trace and see
> > what's
> > > > what.
> > > >
> > > > Thanks for the help so far!!!
> > > >
> > > > "Olu Adedeji" wrote:
> > > >
> > > > > Wanderer,
> > > > >
> > > > > as per my email, I was intimating that you open profiler and set the
> > filter
> > > > > manually, then script out the trace and cut the section that performs
> > the
> > > > > sp_trace_setfilter into your procedure substituting your parameters
> > for
> > > > > hardcoded values. then run your procedure and check the trc files to
> > see if
> > > > > the traces have been set. . from your post it is obvious that profiler
> > does
> > > > > not understand the parameters you have passed into sp_trace_setfilter
> > and as
> > > > > a result it is unable to present it in the trc file. PS. out of
> > interest
> > > > > what are you trying to filter?
> > > > >
> > > > >
> > > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> > to
> > > > > help resolve the problem
> > > > >
> > > >
> >
> >
> >|||Wanderer wrote:
> Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> working. Going to try and build in some logic to catch and check why.
> Anything that you can see from my SP, that points the way, I'd
> appreciate hearing it.
>
You wouldn't see the filter in Profiler if you opened a TRC file after a
server-side trace. All you see in Profiler is the events captured during
the trace. The other information (filters) are not saved anywhere in the
trace file.
Also, I assume when you ran the following statement, you knew that the
running trace had a handle of 1? It seems unlikely that the handle would
be 1 unless you just cycled the server.
SELECT * FROM ::fn_trace_getfilterinfo(1)
I can't read your script file because of the wrapping. What events did
you capture that are not showing the filter applied?
--
David Gugick
Imceda Software
www.imceda.com|||Hi David, thanks for the response!
Thanks for the heads-up on not being able to see the traces! Learnt
something new, there. The problem is I am attempteing to Filter on duration >
1000, but seem to be getting everything. I really don't want to run in our
production environment and capture everything, I strongly suspect that I will
end up with GB's of tracefiles.
Regards the " SELECT * FROM ::fn_trace_getfilterinfo(1)", I also used:
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceId)
where @.TraceId was the @.TraceID variable used to assign the traces in the
SP, and is an output variable. And still got 0 rows returned.
Here is how I called the SP, and then checked the settings:
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
--Error Handling
select @.rc
select @.TraceID
SELECT * FROM ::fn_trace_getfilterinfo(1)
SELECT * FROM ::fn_trace_getfilterinfo(default)
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
select * from ::fn_trace_getinfo(1)
select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
The 3 ::fn_trace_getfilterinfo select ALL return 0 rows, whereas the 3
::fn_trace_getinfo ALL return data about the trace I had just created!
I stringly suspect that either I have somehow failed to exec the
SP_TRACE_SETFILTER (and when debugging it certainly didn't seem like a
problem), or it failed (and I do do fairly complete error checking, per the
error codes listed in the BOL), or else, for some reason, it is just not
being applied.
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Well, I've tried to add steps to check on the adding of the filters, and all
i can say is - it doesn't seem to work!
Here is a section of the code (hopefully the wordwrap doesn't make it
completely illegible).
****code****
exec sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
select @.ReturnCode=@.@.Error
Print 'Trace Filter 2 set'
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
****code****
I get NO response that an error has occurred, the "print's" confirm that the
code stepped through, yet the ::fn_trace_setfilter returns NO ROWS!
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Found it!!!
*sigh* - a blonde moment!
Just before I add the filters, I exec:
exec sp_trace_setstatus @.TraceId,1
In other words, I start the trace, hence I cannot apply filters. And because
I am checing @.@.error for the exec sp_trace_setfilter, and not doing what I
SHOULD be doing, which is:
exec @.ReturnCode=sp_trace_setfilter ...
I never received the response saying that the filter was running (error 2).
Thanks for your help !
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||OH, btw,
I found that after properly creating those filters, when I open the trace
file created by my SP, in profiler, I DO see the filters under the properties
tab for the trace.
Just FYI
"David Gugick" wrote:
> Wanderer wrote:
> > Ok - so it seems that, for some reason, the sp_trace_setfilter is not
> > working. Going to try and build in some logic to catch and check why.
> > Anything that you can see from my SP, that points the way, I'd
> > appreciate hearing it.
> >
> You wouldn't see the filter in Profiler if you opened a TRC file after a
> server-side trace. All you see in Profiler is the events captured during
> the trace. The other information (filters) are not saved anywhere in the
> trace file.
> Also, I assume when you ran the following statement, you knew that the
> running trace had a handle of 1? It seems unlikely that the handle would
> be 1 unless you just cycled the server.
> SELECT * FROM ::fn_trace_getfilterinfo(1)
> I can't read your script file because of the wrapping. What events did
> you capture that are not showing the filter applied?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Wanderer wrote:
> OH, btw,
> I found that after properly creating those filters, when I open the
> trace file created by my SP, in profiler, I DO see the filters under
> the properties tab for the trace.
> Just FYI
>
Strange. I don't see them on some test traces I just ran. Are you saying
that you run a server-side trace, stop the trace, open the trace file in
Profiler, and then see the trace filters? I can't see it here using that
method.
Any verification appreciated.
Thanks
David Gugick|||Hi David,
Using that SP I created, and then fixed :-), I basically run a server side
trace to file. Then I copy the file to another machine, where I do my
analysis.
When opening that trace file, in Profiler, and going to the properties of
the trace, I definitely see my filter.
HTH
"David Gugick" wrote:
> Wanderer wrote:
> > OH, btw,
> >
> > I found that after properly creating those filters, when I open the
> > trace file created by my SP, in profiler, I DO see the filters under
> > the properties tab for the trace.
> >
> > Just FYI
> >
> Strange. I don't see them on some test traces I just ran. Are you saying
> that you run a server-side trace, stop the trace, open the trace file in
> Profiler, and then see the trace filters? I can't see it here using that
> method.
> Any verification appreciated.
> Thanks
>
> --
> David Gugick
>|||Wanderer wrote:
> Hi David,
> Using that SP I created, and then fixed :-), I basically run a server
> side trace to file. Then I copy the file to another machine, where I
> do my analysis.
> When opening that trace file, in Profiler, and going to the
> properties of the trace, I definitely see my filter.
> HTH
> "David Gugick" wrote:
>> Wanderer wrote:
>> OH, btw,
>> I found that after properly creating those filters, when I open the
>> trace file created by my SP, in profiler, I DO see the filters under
>> the properties tab for the trace.
>> Just FYI
>>
>> Strange. I don't see them on some test traces I just ran. Are you
>> saying that you run a server-side trace, stop the trace, open the
>> trace file in Profiler, and then see the trace filters? I can't see
>> it here using that method.
>> Any verification appreciated.
>> Thanks
>>
>> --
>> David Gugick
Can you repost your code so I can see if there are any differences?
--
David Gugick|||Sure. here goes:
****code****
if exists (select 1 from sysobjects where name = 'Up_Auto_Profiler_Perf')
drop procedure Up_Auto_Profiler_Perf
go
create procedure Up_Auto_Profiler_Perf
@.TraceFile nvarchar(128), --Nvarchar as required by SP_CREATE_TRACE. The
File to be created.
@.StopTime datetime, --The time to stop the trace.
@.MaxSize BigInt = 10, --BigInt as required by SP_CREATE_TRACE. The
maximum size per file.
@.DurationFilterValue BIGINT, --The duration to filter on (in ms).
@.TraceID int OUTPUT
-- Create By: Regan Galbraith
-- Create On: 2004-12-28
-- Purpose:
-- This stored procedure was written to facilitate the creation of profiler
traces that write to files.
--
-- Example:
-- exec Up_Auto_Profiler_Perf
'C:\TraceFiles\AutoProf_SP_20041228_1000','2004-12-28-10:05:00.000',100,500
--
-- Possible future additions:
-- 1> simple enhancement to specify DB to store data, and table.
-- 2> accepting a parameter instead of apply the default .trc. Use the .trc
as default
-- 3> implementing default value's for dir's, so that it can run without
parm's ... good or bad?
--
-- Change Control: version 1 - Regan Galbraith 2004-12-28
-- Creation and adding of comment
-- version 1.2 - Regan Galbraith 2004-12-29
-- Added @.DurationFilterValue logic to allow generation of limited data,
-- filtering on duration
-- Added Output parametre @.TracId to return for lookup on trace.
-- version 1.3 - Regan Galbraith 2005-01-03
-- Fixed the problems with applying trace filters by correcting error
checking to
-- use return code, and by moving start trace to end, sicne filters
cannot apply
-- to running trace.
--
AS
set nocount on
--Declare Control Variable
declare @.ReturnCode Int
--Declare Option Variables
declare @.Option int
declare @.EventId int
declare @.On bit
declare @.Value int
declare @.ComparisonOperator int
declare @.ColumnId int
declare @.LogicalOperator int
--Set Option Variable
set @.Option = 2 --TraceFileRollOver
--Specifies that when the max_file_size is reached, the current trace file
is closed and a new file is created.
--Set Trace Filter to exclude System Ids - that is ObjectId > 100
set @.Value = 100
set @.ColumnId = 22 --0bjectid
set @.LogicalOperator = 0 --and (1 = OR)
set @.ComparisonOperator = 2 --Greater than
--Set Control Variables
set @.ReturnCode = 0 --No Error
set @.On = 1 --True
--Create a trace, retrieve @.TraceId
exec @.ReturnCode=sp_trace_create @.TraceId
output,@.Option,@.TraceFile,@.MaxSize,@.StopTime
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
Print 'Error 1 - Unknown error.'
if @.ReturnCode = 10
Print 'Error 10 - Invalid options. Returned when options specified are
incompatible.'
if @.ReturnCode = 12
Print 'Error 12 - Cannot create tracefile - check if file already exists,
or this trace already running'
if @.ReturnCode = 13
Print 'Error 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
if @.ReturnCode = 14
Print 'Error 14 - Invalid stop time. Returned when the stop time specified
has already happened.'
if @.ReturnCode = 15
Print 'Error 15 - Invalid parameters. Returned when the user supplied
incompatible parameters.'
else
Print 'Unexpected and Unknown error In creating trace - Please review'
Goto ErrorHandler
End
--Populate Trace with Events
--SECTION CURSORS
-- this set is : cursor execute
set @.EventId = 74
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Open
set @.EventId = 53
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- this set is : Cursor Recompile
set @.EventId = 75
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION ERRORS AND WARNINGS
-- this set is : MissingJoinPredicate
set @.EventId = 80
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
--SECTION LOCKS
-- this set is : Lock:DeadLock
set @.EventId = 25
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:DeadLockChain
set @.EventId = 59
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- this set is : Lock:Timeout
set @.EventId = 27
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,24,@.On --indexID
exec sp_trace_setevent @.TraceId,@.EventId,32,@.On --Mode
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
--SECTION PERFORMANCE
-- this set is : Execution Plan
set @.EventId = 68
exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- this set is : Show Plan
-- set @.EventId = 97
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- --this set is : Show Plan Statistics
-- set @.EventId = 98
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--
-- --this set is : Show Plan Text
-- set @.EventId = 96
-- exec sp_trace_setevent @.TraceId,@.EventId,2,@.On --BinaryData
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--SECTION STORED PROCEDURES
--this set is SP:Recompile
set @.EventId = 37
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,29,@.On --NestLevel
exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
exec sp_trace_setevent @.TraceId,@.EventId,34,@.On --ObjectName
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
--this set is SP:RPC:Completed
set @.EventId = 10
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
--SECTION TRANSACTIONS
--this set is : SQL Transaction
-- set @.EventId = 50
-- exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
-- exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,21,@.On --EventSubClass
-- exec sp_trace_setevent @.TraceId,@.EventId,34,@.On --ObjectName
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- exec sp_trace_setevent @.TraceId,@.EventId,4,@.On --TRansactionID
--SECTION TSQL
--this set is : SQL BatchCompleted
set @.EventId = 12
exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
-- --this set is : SQL:StmtCompleted
-- set @.EventId = 41
-- exec sp_trace_setevent @.TraceId,@.EventId,18,@.On --cpu
-- exec sp_trace_setevent @.TraceId,@.EventId,13,@.On --duration
-- exec sp_trace_setevent @.TraceId,@.EventId,15,@.On --EndTime
-- exec sp_trace_setevent @.TraceId,@.EventId,27,@.On --EventClass
-- exec sp_trace_setevent @.TraceId,@.EventId,25,@.On --IntegerData
-- exec sp_trace_setevent @.TraceId,@.EventId,29,@.On --NestLevel
-- exec sp_trace_setevent @.TraceId,@.EventId,22,@.On --ObjectID
-- exec sp_trace_setevent @.TraceId,@.EventId,16,@.On --Reads
-- exec sp_trace_setevent @.TraceId,@.EventId,1,@.On --TextData
-- exec sp_trace_setevent @.TraceId,@.EventId,17,@.On --Writes
--Add a trace filter to exclude System Ids - that is ObjectId > 100
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.Value
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
if @.DurationFilterValue <> 0
begin
set @.columnId = 13 --Duration
set @.ComparisonOperator = 4 --Greater than or equal to
--Add trace for Trace being create (@.TraceID, on column Duration, Greater
than Equal to, @.DurationFilterValue)
exec @.ReturnCode=sp_trace_setfilter
@.TraceId,@.ColumnId,@.LogicalOperator,@.ComparisonOperator,@.DurationFilterValue
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 1
print 'ERROR 1 - Unknown error.'
else
if @.ReturnCode = 2
Print 'ERROR 2 - The trace is currently running. Changing the trace at
this time will result in an error.'
else
if @.ReturnCode = 4
Print 'ERROR 4 - The specified Column is not valid.'
else
if @.ReturnCode = 5
print 'ERROR 5 - The specified Column is not allowed for filtering.'
else
if @.ReturnCode = 6
print 'ERROR 6 - The specified Comparison Operator is not valid. '
else
if @.ReturnCode = 7
print 'ERROR 7 - The specified Logical Operator is not valid.'
else
if @.ReturnCode = 9
print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 13
print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 16
print 'ERROR 16 - The function is not valid for this trace.'
else
Print 'ERROR x - Unknown Error'
GoTo ErrorHandler
end
end
--Some information on the trace
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
SELECT * FROM ::fn_trace_getinfo(@.TraceID)
Print 'Start Trace'
exec @.ReturnCode=sp_trace_setstatus @.TraceId,1
if @.ReturnCode <> 0
Begin
if @.ReturnCode = 13
Print 'ERROR 13 - Out of memory. Returned when there is not enough memory
to perform the specified action.'
else
if @.ReturnCode = 9
Print 'ERROR 9 - The specified Trace Handle is not valid.'
else
if @.ReturnCode = 8
print 'ERROR 8 - The specified Status is not valid.'
else
Print 'ERROR 1 - Unknown Error'
GoTo ErrorHandler
end
ErrorHandler:
Return @.ReturnCode
****code****
Code I use to invoke it:
****code****
set nocount on
DECLARE @.FileName NVARCHAR(128)
DECLARE @.RunStopTime DATETIME
DECLARE @.MaxSize BIGINT
DECLARE @.DurationFilter BIGINT
DECLARE @.TraceID INTEGER
DECLARE @.MinutesToRun INTEGER
DECLARE @.RC INTEGER
DECLARE @.DBID INTEGER
DECLARE @.DBNAME NVARCHAR(128)
-- Set Error Variables
SET @.DBNAME = DB_NAME()
SET @.DBID = DB_ID()
SET @.rc = 0
--Set Control Variables
set @.MinutesToRun = 5 -- this is how long the trace will run for
set @.MaxSize = 100 -- this is the maximum size for a file, in MB
set @.DurationFilter = 1000
--Set Running Variables
--the time the trace will stop
set @.RunStopTime = dateadd(mi,@.MinutesToRun,getdate())
--the file to be create - full name (not UNC)
set @.FileName = 'C:\Auto_Prof\AutoProf_SP_'
+cast(datepart(yyyy,getdate()) as char(4)) --Years
+right(cast(datepart(m ,getdate())+100 as char(3)),2) --Months
+right(cast(datepart(d,getdate()) +100 as char(3)),2)+'_' --Days
+right(cast(datepart(hh,getdate())+100 as char(3)),2) --Hours
+right(cast(datepart(mi,getdate())+100 as char(3)),2) --Minutes
--Display variables
print 'File created is : '+@.FileName
print 'End time will be : '+cast(@.RunStopTime as varchar(20))
--Create trace, writing out to tracefile, until endtime
exec @.Rc=Up_Auto_Profiler_Perf
@.FileName,@.RunStopTime,@.MaxSize,@.DurationFilter,@.TraceID output
SELECT * FROM ::fn_trace_getfilterinfo(@.TraceID)
-- select * from ::fn_trace_getinfo(1)
-- select * from ::fn_trace_getinfo(default)
select * from ::fn_trace_getinfo(@.TraceID)
if (@.rc <> 0 )
RAISERROR ('Create/Run of Trace FAILED', 16, 1, @.DBID, @.DBNAME)
GO
****code****
"David Gugick" wrote:
> Can you repost your code so I can see if there are any differences?
> --
> David Gugick
>|||Hello everyone,
I appear to be having the same problem as Wanderer. I would appreciate any
suggestions.
I have a running trace. I stop the trace with
sp_trace_setstatus 1,0
I apply a filter on dbid as follows:
declare @.bigintfilter bigint
select @.bigintfilter = 22
exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
I then check to see if the filter is in place:
select * from ::fn_trace_getfilterinfo(1)
The filter is not listed.
Where am I going wrong?
Thanks in advance. Any help would be appreciated.
Jim|||Check the return code from sp_trace_setfilter! All trace calls you make you
need to check the return code and check it before continuing.
Besides that which trace column is of type bigint?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Jim Strange" <Jim Strange@.discussions.microsoft.com> wrote in message
news:278E6A2B-FDBE-4198-95F5-8E91C2376338@.microsoft.com...
> Hello everyone,
> I appear to be having the same problem as Wanderer. I would appreciate
> any
> suggestions.
> I have a running trace. I stop the trace with
> sp_trace_setstatus 1,0
> I apply a filter on dbid as follows:
> declare @.bigintfilter bigint
> select @.bigintfilter = 22
> exec sp_trace_setfilter 1,4,0,3,@.bigintfilter
> I then check to see if the filter is in place:
> select * from ::fn_trace_getfilterinfo(1)
> The filter is not listed.
> Where am I going wrong?
> Thanks in advance. Any help would be appreciated.
> Jim
>