I'm trying to sniff or view a fairly large query that get's passed to my sql server.
The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.
Is there another method or a way to increase the size of this so I can see the whole query?
Thanks
- jeff
if the inputbuffer contains more than 256 chars, it only reports out the first 256 characters of the buffer. that's because EventInfo is defined as a nvarchar(256) field.
is there any way to get the full inputbuffer...even if it's more than 256 chars? i'm trying to run this:
exec mn @parameter_number_1 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_2 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_3 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_4 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_5 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_6 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_7 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_8 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_9 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_10 = 'abcdefghijklmnopqrstuvwxyz'
and all i get is this:
EventType Parameters EventInfo
--------- ---------- -----------------------------
Language Event 0 exec mn @parameter_number_1 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_2 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_3 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_4 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_5 = 'abcdefghijkl
is there any way to get the entire input buffer?
Thanks in advance,
mike
Hi folks.
DBCC INPUTBUFFER(79)
the statement returned is too short, not full. Can i get full STATEMENT that is sent by the user.
Howdy!
Does anyone know if there is an equivalent way to get to the information that DBCC Inputbuffer returns using 2005s dynamic management objects? sys.dm_exec_requests only returns information that is currently executing...as DBCC inptubuffer returns the last statement executed for a session.
Thanks,
Tim
We are running a Windows NT Terminal server 4.0 with SQL 7. The application we are using has two main connections to the database that may at times update the same table simultaneously. On two seperate occasions we have run into a lock that causes these processes to hang. They appear to still be running in the Task Manager, but have no CPU time. When we review the current activity on the SQL server under Object/locks one of the items listed is "tempdb.dbo.##lockinfo13(the spid)". The last TSQL command batch is "dbcc inputbuffer(13). We have tried to research what this dbcc inputbuffer does, and why there is a conflic here... but have come up empty. Can you help with any information on this dbcc?
View 2 Replies View RelatedHello,
I must get the content of dbcc inputbuffer (nn).
The structure of the result is well described in the BOL.
However, I don't find a way to store the result in temporary table or other stuff.
Thanks in advance.
Regards
Patrick
Does anyone know if there is a simple way to get the results of a DBCCINPUTBUFFER() request into a table? I have a process for monitoringactivity that will give me the results of sp_who2 into a temp table,and want to scroll through the active connections and get the inputbuffers into another table for review:Insert into #TmpWhoexec sp_who2 'active'Something like that with the dbcc command.I am using SQL 2000 SP4.Thanks,Tom
View 3 Replies View RelatedThanks for the help everyone.
Updated versions of the proc and view can be found here for now: http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx
Here's a quick script that will return the inputbuffer of processes that are holding more than a certain threshold of locks (by default, 10, but it's easy to modify). Handy for tracking down what query is holding those 1400 locks.
Yes, it uses a cursor. I'd welcome amendment by anyone who has the insight on converting it to pure set-based.
Cheers
-b
CREATE PROCEDURE sp_lock4 AS
set nocount on
DECLARE @tSpids table(spid int PRIMARY KEY CLUSTERED,count int)
DECLARE @iSpid int,@iCount int
insert into @tSpids (spid,count)
select convert (smallint, req_spid) As spid,
count(*) as count
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
group by converT(smallint,req_spid),'dbcc inputbuffer(' + cast(req_spid as varchar(4)) + ')'
having count(*)>10
order by count(*) desc
DECLARE cLoop cursor for
select spid,count from @tSpids
OPEN cLoop
FETCH NEXT FROM cLoop INTO @iSpid,@iCount
WHILE @@FETCH_STATUS=0
BEGIN
select 'spid ' + cast(@iSpid as varchar(4)) + ' has ' + cast(@iCount as varchar(5)) + ' locks.'
exec ("dbcc inputbuffer (" + @ispid + ")")
FETCH NEXT FROM cLoop INTO @iSpid,@iCount
END
CLOSE cLoop
DEALLOCATE cLoop
return (0) -- sp_lock
GO
Guys,
I need to send a group of people a list of specific processes running on the server, one of the requirements is to send them what's actually being ran on the machine. I have the information I was on the sysprocess tabke and the results of the DBCC Inputbuffer. Is there a way to link both result sets?
This is the criteria of the processes that neeed to be sent out to my users:
SELECT *
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('DBName'))
ORDER BY p.spid
Thanks,
I have a stored proc. in that proc i need to select a value based on which one is greater.
Here is a non working example
select
name,
if (truck1.age > truck2.age, truck1.age, truck2.age)
from person
left join truck truck1
on truck1.make = person.make
left join truck truck2
on truck2.make = person.make
Thanks in advance for any help with this
Hi Guys
I have a table with id, date column in it. How do i list all records which has date greater than 30 day?
Thanks
Is this Correct?
SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
I was comparing diffrent columns and within those columns there are specific values. I want to get the greter values using SQL 2000
i want something like these but i think there was an error in scripting
CASE
WHEN a > b,c,d,e THEN a
WHEN b > a,c,d,e THEN b
WHEN c > a,b,d,e THEN c
WHEN d > a,b,c,e THEN d
WHEN e > a,b,c,d THEN e
END
Please help. Is there any possible way to implement this? Thank you.
Hello,
I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?
Thanks,
Nina Melhem
The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.
What should i do....???
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
I created this unique codes and I need all [FRMDAT] field set to "12/31/2014" in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > "12/31/2014"
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus".
is there some whay to modify this code so that it will work?
commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
View 2 Replies View RelatedProblem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ?
Hello All,
Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.
The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...
Code Snippet
=Count(Fields!ResponseTime.Value > "500")
However, this appears to just return the count of all rows and ignores the "500" part.
Am I missing something? If someone could post a alternate code snippet, that would be great.
Thanks in advance,
Clint
I'm using and Execute Sql Task to get a count of the record in the table: How can I make the workflow to stop if it doesn;t meet the count requirement and continue if it does to the next flow. I'm looking at expression...but a bit comfused about using it.
thanks
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
View 2 Replies View RelatedI think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years.
Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
finding the first value to cross a specific threshold in SQL Server
LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
300 350 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45
How do I design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
Greetings,
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Thanks
I want SQL to look at a date field and sort the data by todays date and greater. Even though there may be some data older than today. I've tried something like this but not working
order by SSD_SED >= GETDATE()
Where the date field is SSD_SED.
I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2
paola -- 3
paola -- 10
jessica -- 3
jessica -- 12
james -- 3
james -- 2
james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...
What is the work around?
Thanks in advance
JEK
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
I have a created a table and entered data into the table as follows:
CREATE TABLE t ( id INT , txtcol varchar(1000) )
INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )
Now if i select data using the query below the txtcol field displays only 255 characters :
SELECT * FROM t
Y is this happening?