I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?
Thanks,
Ken Nicholson
Hi every one,
I want to filter application name in sql profiler, so I write SQL% (for every one who connects by sql qery analyzer) in "application name" filter box but it doesn't work correctly,and shows all applications. Why it works incorrect?
Thank You
Kini
How do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 Replies View RelatedHow do you get sql2005 profiler to filter on host name, (computer name of the client) like you can in sql2000 profiler. 2005 Profiler column filter options seem to omit this, this makes the application virtually useless in most situations.
View 1 Replies View Related
We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.
Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.
When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.
But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().
We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?
FYI, here's the text of the article filter statement:
SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
end
)
)
And here's the statement we ran from Query Analyzer:
declare @id varchar(10)
select @id = 'aultnc'
SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
end
)
)
Thanks
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
Can only member in the System Administrators role use the SQL Profiler’s? Is there any other role or way to allow user to use this tool other then adding them to this role? Thanks you
View 1 Replies View RelatedDoes anybody have any idea if there is a bug in SQL 7.0 where running multiple traces causes the SQL 7.0 to crash ??
This is running in a clustered environment.
Thanks in advance.
Ajay
I've looked high and low for information regarding this problem to no avail. Profiler works fine from the local server where SQL7 is running,
(using NT security). But even though my client is multiprotocol, I cannot connect from my desktop using SQL security (ODBC error) or NT
authentication. NT security gives me ConnectionOpenRcpBindingSetAuthInfo(). I do have a valid id on the NT machine as well and am not
getting any errors in the security events log.
Can/How do you connect remotely to the profiler using TCP/IP or Mulitprotocol client?
Why doesn't NT authentication work?
Thanks in advance for any help.
Hi Everybody,
Can anyone tell me what is a profiler in SQL server 7.0? What I exactly want to know is, in which practical situation u will find the profiler useful. Explaination with example will be appreciated.
Regards,
Samir.
How do I write a process in Profiler to categorise the performance of Stored procedures into buckets of <4 secs,>4secs,between 4 and 10 secs
View 4 Replies View RelatedHello,
My qn is related to profiler.
my app calls an sp, say, sp_DisplayResults. in profiler, i can see multiple entries for this sp call eventhough it is invoked only once from the UI.
no other process/users is invoking the sp.
could any one please let me know why the profiler is displaying it multiple times.
im using MSSQL2k
I have a small doubt through profiler how can we find out the long running queries.specifically which events we have to capture.
View 2 Replies View RelatedHi
I need to track the connections hitting to my server through application???
I m doing some work in Profiler but i m not sure from where to start it..I m using SQL SERVER 2005...I m accesing few pages on application and that i want to track that activity involving at server side
T.I.A
I have a small doubt.
where is the ideal place to run a profiler .
can we run a profiler on production server
or we have to run profiler on a seperate instance and connect to production server
Lets say I have a username & password , I dont want people to see it being passed on to the SQL Server.
What are the ways I can prevent the parameters being passed on .
Thanks,
Regards
Kidddddddddddddddddd
Any one show me the basic guide or tutorial about SQL Profiler for begginer .
Thank in advance !
hello friends sorry for this basic question.
i heard a word sql profiler.
i tried through BOL, but not got any good information how to start and from where to start. can anybody give me some brief about this
thank you very much
1/When I create a new trace, it gives me sevral events including the SQL server s own functions and provcedures. How do I tell profiler to display only the requests we send SQL server from a VB program or other company made programs instead of including sql server own events as well. That will help me filter the requests and locate the problem easily.
2/ also ,. sometimes, my text data column description gets truncated and as a result I can't see the whole text of a request.
What causes that and how can I fox it pls?
Thank you, i am not familiar with profiler.
Hi hi, We had a sql express version running fine untill recently.
Now it seems cause high processor use, high memory load, etc.
So the websites being dependant on SQL start performing slower.
Is there some way to monitor what is causing this.
I can't seem to read any log files, there is no profiler in express, etc. So what options do I have?
im trying to use the profiler to find out which tables are still being accesses as there seems to be alot of suspected tables lying around and stored objects (sps, functions etc) can the trace profiler be used in this situation?
View 12 Replies View RelatedWhen running a profiler trace for long running queries during peak usage what is a typically expected additional load the process of running it will create on that Database?
Future guru in the making.
Hi
Can anyone tell me what is SQL profiler?its function and all.
Thanks in Advance
RKNAIR
Dear All,
where can i found sql profiler in sql server 2005?
i'm using
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Vinod
Even you learn 1%, Learn it with 100% confidence.
I want to know if user defined functions like fn_example can be traced in profiler and what event to choose to trace functions executed.
will it displays the function being executed or just the body of the fucntion?
Thnxxxxxxxx
HiI am just started at a new position. This organization has a number ofdatabase servers with mission critical databases. However, I have twodatabase server, 1 a test server and the other a catch all db server thatcontain a hodge podge of databases. On the test server, there are 54databases while the other has 40. I am pretty confindent that not all ofthese databases are being used. My goal is to find out which ones are nolonger needed.In an attempt to find out who/what is using these databases, I have set upSQL Profiler.My SQL profile setup is as suchObjects - Objects openedSecurity Audit - Audit Login, Login Failed, LogoutSession - Existing ConnectionStored Proc - RPC Completed, SP:StmtCompleted, SP:StmtStartingTSQL - SQL Batch completed, SQL:StmtCompletedMy data columns are the standard except I added DatabaseName.And there lies my problem that I hope someone can help. As I run the trace,all other fields seems to be working except Database Name. I see everythingbut the only way to tell what database is being accessed is looking at theSPID number and comparing it with Process Info in EM or sp_who.Does anyone have any idea why this field is not working? Am I missingsomething? Or am I just giving myself a headache for nothing because thereis an easier way to find the information I want: What the heck is going onwith these database and who is doing what on them?Thanks in advance for any and all help:)Akinja
View 2 Replies View RelatedHi,We have a requirement to run profiler continuously to find out themisbehaving applications/SQL in the dataserver. And we are notinterested in running it from an individuals local machine. Also wewant the profiler to start automatically whenever there are SQL Server/Machine restarts.Is there a way to invoke the profiler instance from cmd prompt or isthere a way to script the profiler?Thanks in advance,Thyagu.
View 1 Replies View RelatedHello friends...I want to ask that Why do we need SQL Profiler as SQL Database Developer and How to use it ?
How can I know about that ?
I have the following values on a sql 2000 box, are these values high?
scale 0 to 100
Page/Sec = 0 to 5
Avg. Disk Queue Length = 40 to 50
% Processor Time = 85 to 100
I read in a thread that to understand better how a package is run I need to run the SQL Profiler.
How do I run the SQL Profiler?.
thanks
Hi guys, I'd just like to know how to setup a new Trace in SQL Server Profiler that tells me every time the database is hit with a query. Thanks.
View 1 Replies View RelatedWhat is it mean GHOST in SQL Trace column ?
Thanx in advance.