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:

Production Vs Development

Hi,
I've just come into a new organisation and my unit's role is mostly
reporting on mostly OLAP type data (although our "warehouse" is pretty much
useless, and so we work off periodic snapshots of the OLTP data, which seems
to be tolerable performance-wise).
I'm having a bit of trouble coming to grips with their idea of
"Development", "Test", and "Production" environments.
First off, there is no such thing as "test", just dev and prod.
Next, the IT Department that manages the what they call "production" is
unrelated to my business unit. They are apparently very unresponsive and can
routinely take six months to effect any requested change to the "production"
reporting database objects. We have read-only access to the production
server/database.
To work around this, my unit has lapsed into the habit of running their
reports off the dev server (which they have control over). When I've pointed
out to them respectfully, that they appear to be doing production work on a
dev server, I come up against a brick wall.
"No." They say. "If we're not reporting off the production server, it's not
production reporting." But then they go on to publish these reports as
representative of the state of the business.
Now I would have though that any environment you use to produce outputs that
you regard as being a part of your business are defacto "production", but I'm
getting no traction with such a novel concept here.
It strikes me though, that Microsoft, given the price differential between
their development and production licensing would have a fairly water-tight
differentiation between dev and prod. What is MS's idea of the difference,
and where can I find a version of it that is written in words that grade
threes could cope with?
Cheers
GPOWhat you have is a problem in the leadership (or rather lack of it) of your
organization. If you want the kind of change you've described, a document
from Microsoft isn't going to change anything.
If you can get a server, load an Express version on it and declare it a
production instance. Then move the reporting to it as you can. Lead by
example.
"GPO" <GPO@.discussions.microsoft.com> wrote in message
news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> Hi,
> I've just come into a new organisation and my unit's role is mostly
> reporting on mostly OLAP type data (although our "warehouse" is pretty
> much
> useless, and so we work off periodic snapshots of the OLTP data, which
> seems
> to be tolerable performance-wise).
> I'm having a bit of trouble coming to grips with their idea of
> "Development", "Test", and "Production" environments.
> First off, there is no such thing as "test", just dev and prod.
> Next, the IT Department that manages the what they call "production" is
> unrelated to my business unit. They are apparently very unresponsive and
> can
> routinely take six months to effect any requested change to the
> "production"
> reporting database objects. We have read-only access to the production
> server/database.
> To work around this, my unit has lapsed into the habit of running their
> reports off the dev server (which they have control over). When I've
> pointed
> out to them respectfully, that they appear to be doing production work on
> a
> dev server, I come up against a brick wall.
> "No." They say. "If we're not reporting off the production server, it's
> not
> production reporting." But then they go on to publish these reports as
> representative of the state of the business.
> Now I would have though that any environment you use to produce outputs
> that
> you regard as being a part of your business are defacto "production", but
> I'm
> getting no traction with such a novel concept here.
> It strikes me though, that Microsoft, given the price differential between
> their development and production licensing would have a fairly water-tight
> differentiation between dev and prod. What is MS's idea of the difference,
> and where can I find a version of it that is written in words that grade
> threes could cope with?
> Cheers
> GPO
>|||I cling to the hope that some basic guidelines from MS will help. I don't
think management is dishonest; merely incompetent. Question is "Where can I
find such guidelines - and written in a way that even my management could
understand?"
The other thing is that SQL Express might not be appropriate anyway. I
thought Express was not intended for an enterprise sized operation, and the
4GB limit would kill us if nothing else.
Regards
GPO
"Jay" wrote:
> What you have is a problem in the leadership (or rather lack of it) of your
> organization. If you want the kind of change you've described, a document
> from Microsoft isn't going to change anything.
> If you can get a server, load an Express version on it and declare it a
> production instance. Then move the reporting to it as you can. Lead by
> example.
> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> > Hi,
> >
> > I've just come into a new organisation and my unit's role is mostly
> > reporting on mostly OLAP type data (although our "warehouse" is pretty
> > much
> > useless, and so we work off periodic snapshots of the OLTP data, which
> > seems
> > to be tolerable performance-wise).
> >
> > I'm having a bit of trouble coming to grips with their idea of
> > "Development", "Test", and "Production" environments.
> >
> > First off, there is no such thing as "test", just dev and prod.
> > Next, the IT Department that manages the what they call "production" is
> > unrelated to my business unit. They are apparently very unresponsive and
> > can
> > routinely take six months to effect any requested change to the
> > "production"
> > reporting database objects. We have read-only access to the production
> > server/database.
> >
> > To work around this, my unit has lapsed into the habit of running their
> > reports off the dev server (which they have control over). When I've
> > pointed
> > out to them respectfully, that they appear to be doing production work on
> > a
> > dev server, I come up against a brick wall.
> >
> > "No." They say. "If we're not reporting off the production server, it's
> > not
> > production reporting." But then they go on to publish these reports as
> > representative of the state of the business.
> >
> > Now I would have though that any environment you use to produce outputs
> > that
> > you regard as being a part of your business are defacto "production", but
> > I'm
> > getting no traction with such a novel concept here.
> >
> > It strikes me though, that Microsoft, given the price differential between
> > their development and production licensing would have a fairly water-tight
> > differentiation between dev and prod. What is MS's idea of the difference,
> > and where can I find a version of it that is written in words that grade
> > threes could cope with?
> >
> > Cheers
> >
> > GPO
> >
>
>|||MS might do a lot of things but one thing they don't do is dictate the
opeartional policies of your company.
That is a role of the Senior Management team of your company.
You talk about 'my unit'. Are you the Manager of 'my unit'? If you are then
simply issue a written instruction to all members of 'my unit' setting out
how things shall work and require that they acknowledge, in writing, that
they have read and understood the instruction. After that, if anyone
disregards the instruction then it becomes a disciplinary matter.
Is the Senior Management of the company aware of the 'unreponsive'ness of
the production IT team? If not then make them aware of it. If I were on the
Senior Management team and that sort of information came to my attention I
would be doing some very serious backside kicking.
"GPO" <GPO@.discussions.microsoft.com> wrote in message
news:7A2C1D04-3FEA-4A06-9C9F-70FEC67659F9@.microsoft.com...
>I cling to the hope that some basic guidelines from MS will help. I don't
> think management is dishonest; merely incompetent. Question is "Where can
> I
> find such guidelines - and written in a way that even my management could
> understand?"
> The other thing is that SQL Express might not be appropriate anyway. I
> thought Express was not intended for an enterprise sized operation, and
> the
> 4GB limit would kill us if nothing else.
> Regards
> GPO
> "Jay" wrote:
>> What you have is a problem in the leadership (or rather lack of it) of
>> your
>> organization. If you want the kind of change you've described, a document
>> from Microsoft isn't going to change anything.
>> If you can get a server, load an Express version on it and declare it a
>> production instance. Then move the reporting to it as you can. Lead by
>> example.
>> "GPO" <GPO@.discussions.microsoft.com> wrote in message
>> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
>> > Hi,
>> >
>> > I've just come into a new organisation and my unit's role is mostly
>> > reporting on mostly OLAP type data (although our "warehouse" is pretty
>> > much
>> > useless, and so we work off periodic snapshots of the OLTP data, which
>> > seems
>> > to be tolerable performance-wise).
>> >
>> > I'm having a bit of trouble coming to grips with their idea of
>> > "Development", "Test", and "Production" environments.
>> >
>> > First off, there is no such thing as "test", just dev and prod.
>> > Next, the IT Department that manages the what they call "production" is
>> > unrelated to my business unit. They are apparently very unresponsive
>> > and
>> > can
>> > routinely take six months to effect any requested change to the
>> > "production"
>> > reporting database objects. We have read-only access to the production
>> > server/database.
>> >
>> > To work around this, my unit has lapsed into the habit of running their
>> > reports off the dev server (which they have control over). When I've
>> > pointed
>> > out to them respectfully, that they appear to be doing production work
>> > on
>> > a
>> > dev server, I come up against a brick wall.
>> >
>> > "No." They say. "If we're not reporting off the production server, it's
>> > not
>> > production reporting." But then they go on to publish these reports as
>> > representative of the state of the business.
>> >
>> > Now I would have though that any environment you use to produce outputs
>> > that
>> > you regard as being a part of your business are defacto "production",
>> > but
>> > I'm
>> > getting no traction with such a novel concept here.
>> >
>> > It strikes me though, that Microsoft, given the price differential
>> > between
>> > their development and production licensing would have a fairly
>> > water-tight
>> > differentiation between dev and prod. What is MS's idea of the
>> > difference,
>> > and where can I find a version of it that is written in words that
>> > grade
>> > threes could cope with?
>> >
>> > Cheers
>> >
>> > GPO
>> >
>>|||"...MS might do a lot of things but one thing they don't do is dictate the
opeartional policies of your company..."
Yes but surely they'd take an interest in whether people were purchasing
developer software (cheap cheap cheap) and then using it in a production
capacity. My question is how do they make this judgement?
Sadly it's not "my unit" in the sense that I manage it. I don't. I say "my
unit" because it's the unit I work for.
"...Is the Senior Management of the company aware of the 'unreponsive'ness
..." Well it's a large government department. Unresponsiveness is culturally
ingrained.
"Stephany Young" wrote:
> MS might do a lot of things but one thing they don't do is dictate the
> opeartional policies of your company.
> That is a role of the Senior Management team of your company.
> You talk about 'my unit'. Are you the Manager of 'my unit'? If you are then
> simply issue a written instruction to all members of 'my unit' setting out
> how things shall work and require that they acknowledge, in writing, that
> they have read and understood the instruction. After that, if anyone
> disregards the instruction then it becomes a disciplinary matter.
> Is the Senior Management of the company aware of the 'unreponsive'ness of
> the production IT team? If not then make them aware of it. If I were on the
> Senior Management team and that sort of information came to my attention I
> would be doing some very serious backside kicking.
>
> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> news:7A2C1D04-3FEA-4A06-9C9F-70FEC67659F9@.microsoft.com...
> >I cling to the hope that some basic guidelines from MS will help. I don't
> > think management is dishonest; merely incompetent. Question is "Where can
> > I
> > find such guidelines - and written in a way that even my management could
> > understand?"
> >
> > The other thing is that SQL Express might not be appropriate anyway. I
> > thought Express was not intended for an enterprise sized operation, and
> > the
> > 4GB limit would kill us if nothing else.
> >
> > Regards
> >
> > GPO
> >
> > "Jay" wrote:
> >
> >> What you have is a problem in the leadership (or rather lack of it) of
> >> your
> >> organization. If you want the kind of change you've described, a document
> >> from Microsoft isn't going to change anything.
> >>
> >> If you can get a server, load an Express version on it and declare it a
> >> production instance. Then move the reporting to it as you can. Lead by
> >> example.
> >>
> >> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> >> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> >> > Hi,
> >> >
> >> > I've just come into a new organisation and my unit's role is mostly
> >> > reporting on mostly OLAP type data (although our "warehouse" is pretty
> >> > much
> >> > useless, and so we work off periodic snapshots of the OLTP data, which
> >> > seems
> >> > to be tolerable performance-wise).
> >> >
> >> > I'm having a bit of trouble coming to grips with their idea of
> >> > "Development", "Test", and "Production" environments.
> >> >
> >> > First off, there is no such thing as "test", just dev and prod.
> >> > Next, the IT Department that manages the what they call "production" is
> >> > unrelated to my business unit. They are apparently very unresponsive
> >> > and
> >> > can
> >> > routinely take six months to effect any requested change to the
> >> > "production"
> >> > reporting database objects. We have read-only access to the production
> >> > server/database.
> >> >
> >> > To work around this, my unit has lapsed into the habit of running their
> >> > reports off the dev server (which they have control over). When I've
> >> > pointed
> >> > out to them respectfully, that they appear to be doing production work
> >> > on
> >> > a
> >> > dev server, I come up against a brick wall.
> >> >
> >> > "No." They say. "If we're not reporting off the production server, it's
> >> > not
> >> > production reporting." But then they go on to publish these reports as
> >> > representative of the state of the business.
> >> >
> >> > Now I would have though that any environment you use to produce outputs
> >> > that
> >> > you regard as being a part of your business are defacto "production",
> >> > but
> >> > I'm
> >> > getting no traction with such a novel concept here.
> >> >
> >> > It strikes me though, that Microsoft, given the price differential
> >> > between
> >> > their development and production licensing would have a fairly
> >> > water-tight
> >> > differentiation between dev and prod. What is MS's idea of the
> >> > difference,
> >> > and where can I find a version of it that is written in words that
> >> > grade
> >> > threes could cope with?
> >> >
> >> > Cheers
> >> >
> >> > GPO
> >> >
> >>
> >>
> >>
>

Product Sub-Typing

Hi,

I am interested in opinions on the following,

main(product_id, short_text, price, type)
productTypes(type_id, type)
productTables(type_id, tableName)
productA(product_id, field1, field2)
productB(product_id, field1, field2)

To retrieve all products of type 'A', one must know the table name, in this case 'productA'.

Here is one method.

Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table....and the result of all this would be dynamic SQL, right? I would not recommend it.sql

Processing priority based on client connection?

Is there a way to set a process priority in SQL Server 2000 based upon the
client or connection type being used to access the data? For example, on a
particular database, I want to give the client application for which the
database was designed priority over other connections such as Microsoft
Access or Seagate Crystal Reports.
Can this be done?As far as I know, it is not possible to set priority based on the client =or the connection.
-- Keith
"Glen Appleton" <buglen@.hotmail.com> wrote in message =news:O432%23p2uDHA.1788@.tk2msftngp13.phx.gbl...
> Is there a way to set a process priority in SQL Server 2000 based upon =the
> client or connection type being used to access the data? For example, =on a
> particular database, I want to give the client application for which =the
> database was designed priority over other connections such as =Microsoft
> Access or Seagate Crystal Reports.
> > Can this be done?
> >

processing files with timestamp

hi guys,

i have a unique problem here.i get four different type of flat files,which i need to pick up and process parallelly,this i am doing using four different foreach containers for the four load process as i can get multiple files ( i had other issues like rollback etc for using forecah containers).

my problem is,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

If you want to sort the files by the second date, take a look at this thread:

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

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

Processing Cube error message?

Sorry about this "too stupid to live" type question, but I'm processing my first cube and I get the following error message: MdxScript(Manufacturing DM) (18,5) Parser: The syntax for "," is incorrect.

Dimensions are processed, it is working on a fact table. Where do I go to find a file that I might look for a bad comma?

(SQL Svr 2005 32bit w/sp 2, Vista Ultimate 32bit, Visual Studio w/ sp1)

Sorry, posted too soon....found my problem.....error in calculation code.....D'oh!!!