How Can The Profiler Textdata Field Be Enlarged?
Jul 20, 2005
When running Profiler to save a trace to a table the field 'TextData' gets
trunciated. The datatype for that field is as shown:
Column name | Data Type | Length | Allow Null
TextData ntext 16 yes
I can not find a way to set the trace up to create a tablewith TextData of
length greater than 16. This really causes a problem when trying to capture
long running queries for tuning as the query itself maybe truncated. Is
there a way around this?
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
I'm running SQL Profiler to analyze the queries being run against mySQL server. The problem is, that in the Profiler results, theTextData column, which contains the SQL statements executed, istruncated so I don't get to see the entire SQL statements. I've gonethrough all the documentation and menu options but I can't seem tofind a way to make it show me the entire SQL statement regardless oflength. Can anybody help?Thanks,Huey
View 1 Replies
View Related
Jul 20, 2005
Is there a way to determine why the Textdata field has "NULL" for a value.The trace shows multiple "NULL" value records with the longest durations;many greater than 50,000ms. The {} indicate the TextData and Duration onthe line below:rownumber | textdata | Eventclass | ......| Duration |...966 | 15 | {NULL} | 24 smm5413 | 808 | Microsoft Office XP | misview012 | 78| {604636} | 2004-02-11 15:25:12.010 | 4747 | 0 | 469
View 1 Replies
View Related
Sep 25, 2007
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
View 4 Replies
View Related
Jul 20, 2005
I am trying to view all the ntext from a profiler trace. The data istruncated at 256 and I am not sure why... The max length is 1820 viathis command:select max(datalength(textdata)) from "monitor forms usage"where textdata like '%gforms%' .I then issueset textsize 8000select (textdata) from "monitor forms usage" where textdata like'%gforms%' and datalength(textdata) >1800and still only 256 is returned. this is true even if I redirect theoutput to a file.Any ideas on how a humble man like me can see all of the data.Mike--Posted via http://dbforums.com
View 2 Replies
View Related
Dec 4, 2007
I tried to trace all sql statements performed on a database. I don't use Profiler because this is a SQL Server version distributed with Studio 2008 Express. So I made a trace in TSQL, but most columns in the trace file are nulls...
Here is what I did to create this trace:
Create a new trace:
Code Block
DECLARE @TraceID INT
DECLARE @MaxSize bigint
SET @MaxSize = 15
EXECUTE sp_trace_create @TraceID output,
@options = 2,
@tracefile = N'C:Tracefile',
@maxfilesize = @MaxSize
(here @TraceID == 2)
Set up columns and events:
Code BlockDECLARE @TraceID int
DECLARE @On BIT
DECLARE @Event1 int
SET @On = 1
SET @TraceID = 2
SET @Event1 = 14
EXECUTE sp_trace_setevent @TraceID, @Event1, 1, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 2, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 4, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 6, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 8, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 9, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 11, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 14, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 15, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 18, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 22, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 27, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 28, @On
-- the same code for events 15, 40, 41, 42, 43, 50, 71, 72
END
Activate the trace:
Code BlockEXECUTE sp_trace_setstatus 2, 1
Then I entered some sql statements using both MS SQL Server Management Studio Express and my ADO.NET application written in Studio 2008 Express mainly by dragging and dropping (so it was autogenerated). I logged in and out.
Disactivated the trace:
Code BlockEXECUTE sp_trace_setstatus 2, 0
Finally the following statement:
Code BlockSELECT
TextData,BinaryData, TransactionID, NTUserName, HostName, ClientProcessID, LoginName, StartTime, EndTime, CPU, ObjectID, EventClass, ObjectType
FROM fn_trace_gettable('c:Tracefile.trc',default)
produced mostly null values, except StartTime and EventClass columns. The EventClass values were 65528, 65534, 65533.
So, the question is, how to get the sql statements that are performed on the server? What is wrong with this procedure? I think I picked up correct events (41 and 42) to show the statements. Besides.. I think all other columns should not be null as well. Why almost everything is null ?
Last question, what are these events 65528, 65534, 65533 ? There are no such numbers in tables sys.events and sys.trace_events and sys.trace_subclass_values. Also I couldn't find them in internet.
View 5 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Apr 26, 2001
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 Related
Dec 23, 1999
Does 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
View 2 Replies
View Related
Mar 10, 1999
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.
View 1 Replies
View Related
Aug 1, 2003
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.
View 3 Replies
View Related
Jul 7, 2004
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 Related
Feb 28, 2007
Hello,
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
View 2 Replies
View Related
Apr 28, 2008
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 Related
Apr 30, 2008
Hi
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
View 9 Replies
View Related
May 16, 2008
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
View 2 Replies
View Related
Feb 28, 2006
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
View 10 Replies
View Related
Apr 20, 2006
Any one show me the basic guide or tutorial about SQL Profiler for begginer .
Thank in advance !
View 1 Replies
View Related
Sep 12, 2006
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
View 7 Replies
View Related
Oct 27, 2006
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.
View 3 Replies
View Related
Nov 22, 2006
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?
View 2 Replies
View Related
Feb 8, 2007
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 Related
Oct 17, 2007
When 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.
View 4 Replies
View Related
Oct 24, 2007
Hi
Can anyone tell me what is SQL profiler?its function and all.
Thanks in Advance
RKNAIR
View 4 Replies
View Related
Feb 29, 2008
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.
View 3 Replies
View Related
Mar 30, 2008
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
View 1 Replies
View Related
Jul 23, 2005
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 Related
Feb 21, 2007
Hi,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 Related
Mar 3, 2008
Hello 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 ?
View 4 Replies
View Related
Oct 18, 2007
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
View 1 Replies
View Related
Sep 12, 2007
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
View 4 Replies
View Related
Oct 16, 2006
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 Related
