Procedure Time out: field

Showing posts with label field. Show all posts

Friday, March 30, 2012

profiler and showplan

I turn on profiler Performance/Execution Plan, and expect to see the
plan in the textdata field - but it's not there.
Is there some event class or other field or something that must also
be included that I've missed?
Thanks.
JoshWhich SQL profiler template have you selected? One of the template (standard)
displays TextData field.
Thanks
GYK
"JXStern" wrote:
> I turn on profiler Performance/Execution Plan, and expect to see the
> plan in the textdata field - but it's not there.
> Is there some event class or other field or something that must also
> be included that I've missed?
> Thanks.
> Josh
>|||On Wed, 27 Oct 2004 16:03:02 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Which SQL profiler template have you selected? One of the template (standard)
>displays TextData field.
I am capturing/displaying the textdata field, but it's empty on the
showplan lines, and in the statement lines I still see just the
statements. I used the SQLProfilerStandard template, and one of my
own, didn't seem to matter. I added all the TSQL events, that didn't
help either.
J.
>Thanks
>GYK
>"JXStern" wrote:
>> I turn on profiler Performance/Execution Plan, and expect to see the
>> plan in the textdata field - but it's not there.
>> Is there some event class or other field or something that must also
>> be included that I've missed?
>> Thanks.
>> Josh
>>|||You have to capture the Binary Data to see the Showplan output.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9ta0o0d664jpr8ihme1kb2kngna64f721q@.4ax.com...
> On Wed, 27 Oct 2004 16:03:02 -0700, GYK
> <GYK@.discussions.microsoft.com> wrote:
>>Which SQL profiler template have you selected? One of the template
>>(standard)
>>displays TextData field.
> I am capturing/displaying the textdata field, but it's empty on the
> showplan lines, and in the statement lines I still see just the
> statements. I used the SQLProfilerStandard template, and one of my
> own, didn't seem to matter. I added all the TSQL events, that didn't
> help either.
> J.
>
>>Thanks
>>GYK
>>"JXStern" wrote:
>> I turn on profiler Performance/Execution Plan, and expect to see the
>> plan in the textdata field - but it's not there.
>> Is there some event class or other field or something that must also
>> be included that I've missed?
>> Thanks.
>> Josh
>>
>|||On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>You have to capture the Binary Data to see the Showplan output.
Wait a minute. I changed the template to capture BinaryData field,
and sure enough, now I got the Showplan tree - in the TextData field!
So I removed the BinaryData field, and now I *still* get the Showplan
tree in the TextData field. Even after I exit and restart Profiler!
Is this some weird bug in SQL2K, that you have to first ask for the
BinaryData, then you can stop asking for it, to get the Showplan tree?
Weird.
But it worked, so thanks!
Josh|||That IS weird. When I remove the binary data column I don't get the showplan
anymore.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ebf0o0pldmaluufjgic4lstkb6ovectqob@.4ax.com...
> On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>You have to capture the Binary Data to see the Showplan output.
> Wait a minute. I changed the template to capture BinaryData field,
> and sure enough, now I got the Showplan tree - in the TextData field!
> So I removed the BinaryData field, and now I *still* get the Showplan
> tree in the TextData field. Even after I exit and restart Profiler!
> Is this some weird bug in SQL2K, that you have to first ask for the
> BinaryData, then you can stop asking for it, to get the Showplan tree?
> Weird.
> But it worked, so thanks!
> Josh
>|||What event are you capturing... showplan or execution plan?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ebf0o0pldmaluufjgic4lstkb6ovectqob@.4ax.com...
> On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>You have to capture the Binary Data to see the Showplan output.
> Wait a minute. I changed the template to capture BinaryData field,
> and sure enough, now I got the Showplan tree - in the TextData field!
> So I removed the BinaryData field, and now I *still* get the Showplan
> tree in the TextData field. Even after I exit and restart Profiler!
> Is this some weird bug in SQL2K, that you have to first ask for the
> BinaryData, then you can stop asking for it, to get the Showplan tree?
> Weird.
> But it worked, so thanks!
> Josh
>|||On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>What event are you capturing... showplan or execution plan?
Execution plan.
J.|||On Wed, 27 Oct 2004 17:48:58 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>That IS weird. When I remove the binary data column I don't get the showplan
>anymore.
But when you get it, is it in the binary or text field?|||JXStern wrote:
> On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>> What event are you capturing... showplan or execution plan?
> Execution plan.
> J.
Execution Plan only requires TextData. Show Plan, if I'm not mistaken,
requires BinaryData.
--
David Gugick
Imceda Software
www.imceda.com|||I just found this... not that it gives a whole lot more info, but at least
it's documented:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OCy3cfKvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> JXStern wrote:
>> On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
>> <replies@.public_newsgroups.com> wrote:
>> What event are you capturing... showplan or execution plan?
>> Execution plan.
>> J.
> Execution Plan only requires TextData. Show Plan, if I'm not mistaken,
> requires BinaryData.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||On Thu, 28 Oct 2004 22:46:23 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>I just found this... not that it gives a whole lot more info, but at least
>it's documented:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
Kinky (eg, a bug) but it doesn't explain the even weirder case I saw.
J.|||But we just have your word for it, right.
Have you been able to duplicate it?
;-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:vjs7o0lvo1o2799j6033qlc1ibk6ab9uo9@.4ax.com...
> On Thu, 28 Oct 2004 22:46:23 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>I just found this... not that it gives a whole lot more info, but at least
>>it's documented:
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
> Kinky (eg, a bug) but it doesn't explain the even weirder case I saw.
> J.
>|||On Sat, 30 Oct 2004 15:19:57 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>But we just have your word for it, right.
>Have you been able to duplicate it?
>;-)
Now that you ask, no.
I just tried it, and on a clean startup, this time I *did* get the
execution plan - *without* also getting the binary!
Looks random. Isn't that special?
:)
J.

Profiler

Where can I change the length or data type (preferrably to nvarchar 4000) of
the textdata field in profiler? My intent is to capture as much of a stored
proc script as possible & save as a trace table, but the TextData column type
of ntext (16) is not sufficient. Or is there a way to capture the body of
text in the lower pane?
JDArsenault wrote:
> Where can I change the length or data type (preferrably to nvarchar
> 4000) of the textdata field in profiler? My intent is to capture as
> much of a stored proc script as possible & save as a trace table, but
> the TextData column type of ntext (16) is not sufficient. Or is there
> a way to capture the body of text in the lower pane?
NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
in length. it's possible the Profiler UI has limitations on the amount
of text it displays. If you use a server-side trace, you will see
everything.
David Gugick
Imceda Software
www.imceda.com
|||Can you briefly describe a server side trace & what I need to do?
"David Gugick" wrote:

> JDArsenault wrote:
> NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
> in length. it's possible the Profiler UI has limitations on the amount
> of text it displays. If you use a server-side trace, you will see
> everything.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||JDArsenault wrote:
> Can you briefly describe a server side trace & what I need to do?
Profiler is just a GUI for the SQL Trace API, with some major
disadvantages. Profiler uses traces that produce rowsets so the UI can
display those results to the user in a streaming fashion. It's a good
option for seeing a short amount of activity on a server. Profiler is
not intended for use, nor is it recommended for use, in an enterprise
fashion because of the increased overhead and no guarantee of seeing all
results.
A server-side trace is a programmatic method to create, start, stop, and
manage traces. The API (accessed through T-SQL) is somewhat verbose, but
does provide a guarantee of seeing all traced information and causes
much less overhead on the server.
You use the sp_trace_* system procs and fn_trace_* system functions to
do this.
The easiest way to see the code for server-side trace is to set one up
using Profiler. Select the options to save the results to a file and
have the server manage the trace. Select the events you want and columns
you need to see. Start the trace. Stop it. Select the File - Script
Trace menu option and Profiler generates much of the code for you.
You can then modify the code as you see fit. You'll also need to
manually stop the trace, so getting the trace handle when the trace
starts is important. You sp_trace_setstatus to start/stop traces.
You always want to trace to a flat file on one of the server's local
drives. Never to a network share or a NAS device. The tempdb/log device
is usually a good place (RAID 5 arrays are not ideal for traces).
When the trace is stopped, you can query the information from the file
using fn_trace_gettable(), import the information into a table using
that function, or open the trace file in Profiler.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for the straight-forward help...
"David Gugick" wrote:

> JDArsenault wrote:
> Profiler is just a GUI for the SQL Trace API, with some major
> disadvantages. Profiler uses traces that produce rowsets so the UI can
> display those results to the user in a streaming fashion. It's a good
> option for seeing a short amount of activity on a server. Profiler is
> not intended for use, nor is it recommended for use, in an enterprise
> fashion because of the increased overhead and no guarantee of seeing all
> results.
> A server-side trace is a programmatic method to create, start, stop, and
> manage traces. The API (accessed through T-SQL) is somewhat verbose, but
> does provide a guarantee of seeing all traced information and causes
> much less overhead on the server.
> You use the sp_trace_* system procs and fn_trace_* system functions to
> do this.
> The easiest way to see the code for server-side trace is to set one up
> using Profiler. Select the options to save the results to a file and
> have the server manage the trace. Select the events you want and columns
> you need to see. Start the trace. Stop it. Select the File - Script
> Trace menu option and Profiler generates much of the code for you.
> You can then modify the code as you see fit. You'll also need to
> manually stop the trace, so getting the trace handle when the trace
> starts is important. You sp_trace_setstatus to start/stop traces.
> You always want to trace to a flat file on one of the server's local
> drives. Never to a network share or a NAS device. The tempdb/log device
> is usually a good place (RAID 5 arrays are not ideal for traces).
> When the trace is stopped, you can query the information from the file
> using fn_trace_gettable(), import the information into a table using
> that function, or open the trace file in Profiler.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Just to comment on that:
Profiler limits amount of data displayed in TextData column in the grid,
however entire content of TextData is visible in the bottom pane once the
event is selected. It can be copied from there to clipboard using standard
Ctrl-C menu.
Excellent write-up on server side trace.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:B5F43559-6464-4463-9A13-5BC1C2164E5F@.microsoft.com...[vbcol=seagreen]
> Thanks for the straight-forward help...
> "David Gugick" wrote:

Monday, March 26, 2012

Product of a Field

Hi All,

I have a field with numbers (double datatype) as values and I want a aggregate function that gives me the product of all the values in that field. Is there a way to do that in SSRS?

Check this post where I have suggestes a solution.

Shyam

|||

Check this post where I have suggestes a solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1384503&SiteID=1

Shyam

sql

Product of a Field

Hi All,

I have a field with numbers (double datatype) as values and I want a aggregate function that gives me the product of all the values in that field. Is there a way to do that in SSRS?

Check this post where I have suggestes a solution.

Shyam

|||

Check this post where I have suggestes a solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1384503&SiteID=1

Shyam

Product for Creating Data Dictionary

We have a large SQL Server database that has poorly documented tables and
field names. Is there a product that will create a Word document in table
format from the actual database that can be used to add documentation about
the meaning of each field?
--
WillWill
SQL Server 2000 has an option called column description
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:OOrsHkGUFHA.3636@.TK2MSFTNGP14.phx.gbl...
> We have a large SQL Server database that has poorly documented tables and
> field names. Is there a product that will create a Word document in
table
> format from the actual database that can be used to add documentation
about
> the meaning of each field?
> --
> Will
>
>

Tuesday, March 20, 2012

Processing XML data in a SQL Server field

Has anyone cracked this? I want to process xml data which is contained within
a SQL server field. I'm using other fields in the table in the normal way.
The xml field actually contains numerous data values which I want to pass
into the the chart object.
So far there seems to be two options to me:
1) Export the entire row of the table to xml and then use a custom data
extension which knows about xml to read it. This route is described in Teos'
book.
2) Call out to a custom assembly and have it process the xml.
My views on the above are:
1) Seems clunky. Already have the data and now I need to export it to a file
and then read it back in?
2) Seems ok but how do I handle multiple items,, such as the data points?
Anyone have any other ideas before I wade in with my boots?!using a custom assembly works like a charm - We have entire XML docs stored
in blob fields and parse them on the fly to get the information we need form
them.

Processing the TEXT datatype with TSQL

Hi;

I have a table with a TEXT datatype.
Its a comment field.

Right now the users who put in singlequotes are killing the web front
end.

The programmer responsible is fixing this issue but it might be a few
weeks until we get the patch.

I would like to write a trigger that whenever this field is updated it
will scan the text for single quotes ( and hard returns \r ) and
extract them.

I found some nice string functions in HELP.

Will these string functions work with the TEXT datatype in a TSQL
script/trigger?

Thanks in advance

SteveI handle quotes with the REPLACE function. All languages that I work with
has it.

Two single quotes in a row signify an escape sequence from the normal
interpretation of the single quote character. When two single quotes appear
together, they are interpreted by SQL as one literal single quote. All we
need do, then, is replace any single quote with two single quotes in strings
that we want interpreted literally by SQL.

This won't work on a Text datatype, however it does work on varchars and
stuff. Check your max len() on that field and see if it actually is using
more than the capacity of other datatypes and see about changing it to
varchar or something. This t-sql replaces one quote with two and would save
your web person endless hours of javascript'ing validation code!!

Select REPLACE(testColumn, char(39), char(39) + char(39)) as texta from
myTable

After all, quotes are valid characters too!!!

Good luck!

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Steve" <stevesusenet@.yahoo.com> wrote in message
news:6f8cb8c9.0311140657.59346a15@.posting.google.c om...
> Hi;
> I have a table with a TEXT datatype.
> Its a comment field.
> Right now the users who put in singlequotes are killing the web front
> end.
> The programmer responsible is fixing this issue but it might be a few
> weeks until we get the patch.
> I would like to write a trigger that whenever this field is updated it
> will scan the text for single quotes ( and hard returns \r ) and
> extract them.
> I found some nice string functions in HELP.
> Will these string functions work with the TEXT datatype in a TSQL
> script/trigger?
> Thanks in advance
> Steve

Monday, February 20, 2012

Procedure/Cursor question about returning results

To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.

DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end

Close Authors_cursor
deallocate authors_cursor

Thanks in advance

Tony Murunion

If you only need the previous value, the best thing would be to store the previous data in a local variable. I think that is a common approach.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the reply

The actual script I was working with used local variables to get the data I wanted. I was just stuck with getting the results back in one set instead of one for each record.

I was able to resolve my problem by dumping the data into a table in a function (decided to try that instead of a proc)

Tony

Procedure/cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some
raw data and combine the the current record with the datetime field of the
prior record. I have been able to write a script to do that with cursors
and variables but my problem is it returns each record separately. How do I
go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass
through the data as a seperate query. Since I can't do a select *, what
approach should I take? If you want the actual script I have so far, I
would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony MurunionTony Murnion (remove) wrote:
> I'm working on a procedure that needs to cycle through the records of some
> raw data and combine the the current record with the datetime field of the
> prior record. I have been able to write a script to do that with cursors
> and variables but my problem is it returns each record separately. How do
I
> go about getting the procedure to return all the records as one set of dat
a?
> To see what I mean, the following script for the Pubs DB returns each pass
> through the data as a seperate query. Since I can't do a select *, what
> approach should I take? If you want the actual script I have so far, I
> would be happy to provide it.
> DECLARE authors_cursor CURSOR
> FOR SELECT * FROM authors
> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> WHILE @.@.FETCH_STATUS = 0
> begin
> FETCH NEXT FROM authors_cursor
> end
> Close Authors_cursor
> deallocate authors_cursor
> Thanks in advance
> Tony Murunion
Your cursor wouldn't give predictable results anyway because you
haven't specified ORDER BY.
Cursors are rarely a good way to get results out of data. In this case
you can possibly use a query. To take another example from Pubs:
SELECT T1.title_id, T1.title,
T1.pubdate AS current_pubdate,
MAX(T2.pubdate) AS previous_pubdate
FROM titles AS T1
LEFT JOIN titles AS T2
ON T1.pubdate > T2.pubdate
GROUP BY T1.title, T1.title_id, T1.pubdate
ORDER BY current_pubdate, previous_pubdate ;
To do that with a cursor you could insert each row to a table variable
and then SELECT from the variable. Don't forget ORDER BY though!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply. I just started looking into the tables option. I'm
fairly new to advanced script writing. I've done a lot of select and
updates over the years but most of my data manipulation\retrieval has been
through Crystal. In this current project, I need to do the manipulaton
before the ending result.
With my cursor testing, I do have the order by clause for just the reasons
you stated. The results I'm getting are valid - I just need them to come
over as one record set. The tables option may do that for me. I was also
just looking at the user definde functions. Since this database I am reading
will be generating a few thousand records a day, what do you think will
ultimately give me the best performance? The join example you gave seems
like it would bog down with larger volumes of data.
I did not mention before but this is on sql 2000
Thanks again.
Tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149798283.293874.324730@.j55g2000cwa.googlegroups.com...
> Tony Murnion (remove) wrote:
> Your cursor wouldn't give predictable results anyway because you
> haven't specified ORDER BY.
> Cursors are rarely a good way to get results out of data. In this case
> you can possibly use a query. To take another example from Pubs:
> SELECT T1.title_id, T1.title,
> T1.pubdate AS current_pubdate,
> MAX(T2.pubdate) AS previous_pubdate
> FROM titles AS T1
> LEFT JOIN titles AS T2
> ON T1.pubdate > T2.pubdate
> GROUP BY T1.title, T1.title_id, T1.pubdate
> ORDER BY current_pubdate, previous_pubdate ;
> To do that with a cursor you could insert each row to a table variable
> and then SELECT from the variable. Don't forget ORDER BY though!
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Friday, March 30, 2012

profiler and showplan

profiler and showplan

profiler and showplan

Profiler

Monday, March 26, 2012

Product of a Field

Product of a Field

Product for Creating Data Dictionary

Tuesday, March 20, 2012

Processing XML data in a SQL Server field

Processing the TEXT datatype with TSQL

Monday, February 20, 2012

Procedure/Cursor question about returning results

Procedure/cursor question about returning results

Procedure Time out

Blog Archive

About Me