sql report manager

Thursday, March 29, 2012

Can't Sum Table Fields in SQL 2000 Reporting Services Report

I have a report in SQL 2000 Reporting Services with several tables.
The only thing showing in these tables are the group footers because
the detail data is not needed.
I am needing to add a total for a column in each table and then add
those totals together in a text box.
When ever I do this I am getting the following errors:
The value expression for the text box 'textbox58' uses an aggregate
function on a report item. Aggregate functions can be used only on
report items contained in page headers and footers.
The value expression for the text box 'textbox37' uses an aggregate
expression without a scope. A scope is required for all aggregates
used outside of a data region unless the report contains exactly one
data set.
I've tried several different ways and still can't figure it out, PLEASE
HELP!!A few design suggestions. If you don't need the detail then don't return it.
Let SQL Server do the work. But, let's say you continue the way you are
today. What I do sometimes is to add a colum to the result set and make it
calculated, then using the expression editor make it the sum of the two
fields. This makes things a whole lot easier at times.
Once you have done this you can use the expression editor, base it on the
dataset and sum up the column.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jasper.frazee@.gmail.com> wrote in message
news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>I have a report in SQL 2000 Reporting Services with several tables.
> The only thing showing in these tables are the group footers because
> the detail data is not needed.
> I am needing to add a total for a column in each table and then add
> those totals together in a text box.
> When ever I do this I am getting the following errors:
> The value expression for the text box 'textbox58' uses an aggregate
> function on a report item. Aggregate functions can be used only on
> report items contained in page headers and footers.
> The value expression for the text box 'textbox37' uses an aggregate
> expression without a scope. A scope is required for all aggregates
> used outside of a data region unless the report contains exactly one
> data set.
> I've tried several different ways and still can't figure it out, PLEASE
> HELP!!
>|||Thanks for the Responce Bruce!
In my dataset there are 5 columns that will alwayse have the same
result and one column that has detail data that needs to be summed. If
I sum that one column in SQL I have to have the other columns in an
aggregate function as well. In SQL 2000 the "First" function is not
supported. Any ways around that?
Once I get the totals in each table on my report how can I grab those
totals and sum them in a textbox? It keeps telling me that "Aggregate
functions can be used only on
report items contained in page headers and footers."
Thanks A Lot!
Jasper
Bruce L-C [MVP] wrote:
> A few design suggestions. If you don't need the detail then don't return it.
> Let SQL Server do the work. But, let's say you continue the way you are
> today. What I do sometimes is to add a colum to the result set and make it
> calculated, then using the expression editor make it the sum of the two
> fields. This makes things a whole lot easier at times.
> Once you have done this you can use the expression editor, base it on the
> dataset and sum up the column.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <jasper.frazee@.gmail.com> wrote in message
> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
> >I have a report in SQL 2000 Reporting Services with several tables.
> > The only thing showing in these tables are the group footers because
> > the detail data is not needed.
> > I am needing to add a total for a column in each table and then add
> > those totals together in a text box.
> > When ever I do this I am getting the following errors:
> >
> > The value expression for the text box 'textbox58' uses an aggregate
> > function on a report item. Aggregate functions can be used only on
> > report items contained in page headers and footers.
> >
> > The value expression for the text box 'textbox37' uses an aggregate
> > expression without a scope. A scope is required for all aggregates
> > used outside of a data region unless the report contains exactly one
> > data set.
> >
> > I've tried several different ways and still can't figure it out, PLEASE
> > HELP!!
> >|||You can sum the numbers by summing =ReportItems("textbox1").Value +
ReportItems("textbox2").Value etc.
You need to know the names of the textboxes, so you might want to name the
textboxes with something more descriptive than textbox1 etc before you start
summing. :)
(Not sure if it's ReportItems or ReportItem, so you might have to try both.)
Kaisa M. Lindahl Lervik
<jasper.frazee@.gmail.com> wrote in message
news:1162417277.149051.147270@.f16g2000cwb.googlegroups.com...
> Thanks for the Responce Bruce!
> In my dataset there are 5 columns that will alwayse have the same
> result and one column that has detail data that needs to be summed. If
> I sum that one column in SQL I have to have the other columns in an
> aggregate function as well. In SQL 2000 the "First" function is not
> supported. Any ways around that?
> Once I get the totals in each table on my report how can I grab those
> totals and sum them in a textbox? It keeps telling me that "Aggregate
> functions can be used only on
> report items contained in page headers and footers."
> Thanks A Lot!
> Jasper
> Bruce L-C [MVP] wrote:
>> A few design suggestions. If you don't need the detail then don't return
>> it.
>> Let SQL Server do the work. But, let's say you continue the way you are
>> today. What I do sometimes is to add a colum to the result set and make
>> it
>> calculated, then using the expression editor make it the sum of the two
>> fields. This makes things a whole lot easier at times.
>> Once you have done this you can use the expression editor, base it on the
>> dataset and sum up the column.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <jasper.frazee@.gmail.com> wrote in message
>> news:1162406083.894334.80910@.k70g2000cwa.googlegroups.com...
>> >I have a report in SQL 2000 Reporting Services with several tables.
>> > The only thing showing in these tables are the group footers because
>> > the detail data is not needed.
>> > I am needing to add a total for a column in each table and then add
>> > those totals together in a text box.
>> > When ever I do this I am getting the following errors:
>> >
>> > The value expression for the text box 'textbox58' uses an aggregate
>> > function on a report item. Aggregate functions can be used only on
>> > report items contained in page headers and footers.
>> >
>> > The value expression for the text box 'textbox37' uses an aggregate
>> > expression without a scope. A scope is required for all aggregates
>> > used outside of a data region unless the report contains exactly one
>> > data set.
>> >
>> > I've tried several different ways and still can't figure it out, PLEASE
>> > HELP!!
>> >
>sql

Can't store chinese in SQL Database

update Food set FoodName = ' 杏仁' where ID = 100

in database as ?

Hi SuperM,

You should use Encoding support from your .NET Application.

To Encoding:

Encoding target = Encoding.GetEncoding( "GB18030" );
Byte[] buffer = target.GetBytes( text );
return Convert.ToBase64String( buffer );

From Encoding:

Encoding target = Encoding.GetEncoding( "GB18030" );
Byte[] buffer = Convert.FromBase64String( text );
return target.GetString( buffer );

You could use GB18030 to simplified or Big5 to traditional.

Later, value converted you send to store procedure.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Or set the collation settings in SQL Server. If this is SQL Server 2000, you need to run the rebuildm.exe to change the current collation settings. But this will affect other databases in your server|||

Hi bass,

"But this will affect other databases in your server".

This could be very dangerous.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

SuperM wrote:

update Food set FoodName = ' 杏仁' where ID = 100
in database as ?

I would recommend defining the data column as NVarchar, NChar, or NText, when at all possible when working with non-ascii characters.

Jimmy

|||I know what you mean but that should have been a part of the planning phase so that it would be considered in the deployment. Our applications use different collation settings as they are being used in different countries. So before we even create the databases, the SQL Sever settings have been defined properly.|||

thanks,

the field needs to store chinese which must define as NVarchar, NChar, or NText

The Insert SQL as

insert into Food (FoodName) values (N'杏仁' )

The Update SQL as

update Food set FoodName = N' 杏仁' where ID = 100

Can't stop page breaks on my grouped list

All,
I've created a list and defined a group for monthly period. In the
list, I include several controls that show each month's summary values.
Unfortunately, each month's set of controls is appearing on separate
pages. The sections are small enough to fit two or three per page.
I've verified that the two page break properties are both set to False.
I've set the Keep Together property to False. What else am I missing?
Are their layout issues concerning margins or anything like that? This
is the first time I've tried to use grouped lists so I don't know what
to think.
I created the report with the report wizards and then modified it from
there. Maybe that will help.
Thanks,
DavidIt's me...the originator of this message.
I apparently have not found the list control's property that made it
serve as the Page field container that I chose in the New Report Wizard
(Matrix report). I would have thought the New Page After property.
Nevertheless, I created a new list control and copied my controls into
it. After defining my group, all worked as expected.
So if you're reading this and have a guess why the list control
mysteriously ejected pages, please respond. I'm interested.
David|||What was the property ? I am having a similar problem with the
occasional page in my report (200 pages) breaking about half way down
the page.
davidb@.mercurydata.com wrote:
> It's me...the originator of this message.
> I apparently have not found the list control's property that made it
> serve as the Page field container that I chose in the New Report Wizard
> (Matrix report). I would have thought the New Page After property.
> Nevertheless, I created a new list control and copied my controls into
> it. After defining my group, all worked as expected.
> So if you're reading this and have a guess why the list control
> mysteriously ejected pages, please respond. I'm interested.
> David

Can't start transactional replication SQL 2005. System.OutOfMemory Exception during start

We currently use log shipping to 2 other servers for reporting. We
want to use transactional replication instead, so that our reporting
databases are always available (when the restore agent runs the
database is unavailable for a few seconds) and to reduce the latency
between changes in the main database appearing in our reporting
database.
I've successfully got replication to work on a test scenario (with
only a few thousand rows) but when I try and get transactional
replication going on our production site I get a System.OutOfMemory
exception when performing the snapshot. It fails at about 40 percent
of one of our larger tables (157 million rows.) The database backup
is about 27GB.
The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
Server 2003.
We are running SQL2005 Standard edtion SP2, but I had the same problem
on SP1.
I've tried the /3GB switch.
I've tried putting the distrubutor on the main server, and on a
seperate server, and on the reporting box.
I've tried pull and push subscriptions.
I'm going to try a row filter to pull only the most recent data (to
drop the number of rows) and see at what point it fails, but this is a
fairly long interative process. And we do need all the data in the
end.
I'm looking for advice, has anybody else had similar problems?
Is there a way to start replication from a restored database?
I'm considering installing more Memory - but am unsure how to estimate
how much more memory we need. Also our machines run fine with 4GB of
memory normally; I can't really afford to buy more memory just to
"start" the replication process going (but of course I guess this
depends on how much more I would need!)
Daniel Bryars
This is something that I would really like to have a closer look so let me
ask you a few questions:
1) Is snapshot.exe consuming a large amount of memory according to taskmgr?
2) Do you have a pagefile configured on the distributor machine? I have
heard of one case where disabling the pagefile seemed to be the cause of OOM
error in the snapshot agent. I suspect that this may have something to do
with the use of memory mapped file in the underlying BCP api but I was never
able to prove one way or another.
3) Do you have a large amount of blob column data?
If possible, you may also want to consider upgrading your software stack at
your distributor (OS + SQL Server) to 64bit as it is quite possible that you
are running out of address space range. Most dual-core processors should
already be capable of running in 64bit mode.
-Raymond
<bryars@.hotmail.com> wrote in message
news:1174821642.773195.15500@.e65g2000hsc.googlegro ups.com...
> We currently use log shipping to 2 other servers for reporting. We
> want to use transactional replication instead, so that our reporting
> databases are always available (when the restore agent runs the
> database is unavailable for a few seconds) and to reduce the latency
> between changes in the main database appearing in our reporting
> database.
> I've successfully got replication to work on a test scenario (with
> only a few thousand rows) but when I try and get transactional
> replication going on our production site I get a System.OutOfMemory
> exception when performing the snapshot. It fails at about 40 percent
> of one of our larger tables (157 million rows.) The database backup
> is about 27GB.
> The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
> Server 2003.
> We are running SQL2005 Standard edtion SP2, but I had the same problem
> on SP1.
> I've tried the /3GB switch.
> I've tried putting the distrubutor on the main server, and on a
> seperate server, and on the reporting box.
> I've tried pull and push subscriptions.
> I'm going to try a row filter to pull only the most recent data (to
> drop the number of rows) and see at what point it fails, but this is a
> fairly long interative process. And we do need all the data in the
> end.
> I'm looking for advice, has anybody else had similar problems?
> Is there a way to start replication from a restored database?
> I'm considering installing more Memory - but am unsure how to estimate
> how much more memory we need. Also our machines run fine with 4GB of
> memory normally; I can't really afford to buy more memory just to
> "start" the replication process going (but of course I guess this
> depends on how much more I would need!)
> Daniel Bryars
>

cant start Trace

I just created a server side Trace with no filters for testing but Im not
able to start it. I call sp_trace_setstatus 1,1 and it says completed
successfully. It then creates the file it writes too but never writes to it
when I generate activity. Any ideas why?
TIA, ChrisR
sql2k sp3aHi Chris
With server side traces, SQL Server writes to the file in 128K chunks, so
you won't see anything written until you have 128K worth of activity.
Look at fn_trace_getinfo( ) to check that your trace really is running.
Then try stopping and closing the trace:
sp_trace_setstatus 1, 0 -- stop the trace
sp_trace_setstatus 1, 2 -- close the trace
You must stop before closing or the close won't do anything. Only after
closing SQL Server write to the file.
Or, you can stop the SQL Server services, and that will also flush the
collected trace events to the file.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>I just created a server side Trace with no filters for testing but Im not
>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>successfully. It then creates the file it writes too but never writes to it
>when I generate activity. Any ideas why?
> TIA, ChrisR
> sql2k sp3a
>|||Kalen I know this will sound odd, but it almost seems as though nothing gets
written ubtil the box is rebooted. It stays at 0kb until I reboot, at which
time there is a larger size.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
> Hi Chris
> With server side traces, SQL Server writes to the file in 128K chunks, so
> you won't see anything written until you have 128K worth of activity.
> Look at fn_trace_getinfo( ) to check that your trace really is running.
> Then try stopping and closing the trace:
> sp_trace_setstatus 1, 0 -- stop the trace
> sp_trace_setstatus 1, 2 -- close the trace
> You must stop before closing or the close won't do anything. Only after
> closing SQL Server write to the file.
> Or, you can stop the SQL Server services, and that will also flush the
> collected trace events to the file.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im not
>>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>>successfully. It then creates the file it writes too but never writes to
>>it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>|||Are you saying you have stopped and closed the trace, and still nothing gets
written?
If so, can you show the output of fn_trace_getinfo and then exact commands
you are running to stop and close the trace?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
> Kalen I know this will sound odd, but it almost seems as though nothing
> gets written ubtil the box is rebooted. It stays at 0kb until I reboot, at
> which time there is a larger size.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks, so
>> you won't see anything written until you have 128K worth of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im not
>>able to start it. I call sp_trace_setstatus 1,1 and it says completed
>>successfully. It then creates the file it writes too but never writes to
>>it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>|||--Status before stopping
traceid property value
-- -- -----------
1 1 0
1 2 c:\JobTrace
1 3 5
1 4 NULL
1 5 1
--stopping trace
sp_trace_setstatus 1,0
--status after stopping
traceid property value
-- -- -----------
1 1 0
1 2 c:\JobTrace
1 3 5
1 4 NULL
1 5 0
I dont see a command to close the Trace, Im just closing Query Anyalyzer.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Are you saying you have stopped and closed the trace, and still nothing
> gets written?
> If so, can you show the output of fn_trace_getinfo and then exact
> commands you are running to stop and close the trace?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but never
>>writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>|||Closing Qa will not stop or close the trace and the file size may not update
that often while it is running. If you look at sp_trace_setstatus in BOL
you will see hope to stop and close the trace.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
> --Status before stopping
> traceid property value
> -- -- -----------
> 1 1 0
> 1 2 c:\JobTrace
> 1 3 5
> 1 4 NULL
> 1 5 1
> --stopping trace
> sp_trace_setstatus 1,0
> --status after stopping
> traceid property value
> -- -- -----------
> 1 1 0
> 1 2 c:\JobTrace
> 1 3 5
> 1 4 NULL
> 1 5 0
> I dont see a command to close the Trace, Im just closing Query Anyalyzer.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of
>> activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only after
>> closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but
>>never writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>>
>|||As noted earlier I did this:
--stopping trace
sp_trace_setstatus 1,0
Was I supposed to do this:
--stopping trace
sp_trace_setstatus 1,2
I was hoping to avoid that because BOL say it will delete the definition
from the server. I was hoping to be able to reuse it. Will info not be
written until closed/ deleted?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Closing Qa will not stop or close the trace and the file size may not
> update that often while it is running. If you look at sp_trace_setstatus
> in BOL you will see hope to stop and close the trace.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though nothing
>> gets written ubtil the box is rebooted. It stays at 0kb until I reboot,
>> at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K chunks,
>> so you won't see anything written until you have 128K worth of
>> activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is
>> running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only
>> after closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush the
>> collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>I just created a server side Trace with no filters for testing but Im
>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>completed successfully. It then creates the file it writes too but
>>never writes to it when I generate activity. Any ideas why?
>> TIA, ChrisR
>> sql2k sp3a
>>
>>
>>
>>
>|||This is what I said in my first reply to you. Nothing will be written to the
file until the trace is CLOSED.
Please read my first reply again. You must first stop (set status to 0) and
then close (set status to 2).
If you want to save the trace definition, save the SQL commands that you
used to start it, and write them to a file that you can load up again.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ChrisR" <noemail@.bla.com> wrote in message
news:uZ8STvjOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> As noted earlier I did this:
> --stopping trace
> sp_trace_setstatus 1,0
> Was I supposed to do this:
> --stopping trace
> sp_trace_setstatus 1,2
> I was hoping to avoid that because BOL say it will delete the definition
> from the server. I was hoping to be able to reuse it. Will info not be
> written until closed/ deleted?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
>> Closing Qa will not stop or close the trace and the file size may not
>> update that often while it is running. If you look at sp_trace_setstatus
>> in BOL you will see hope to stop and close the trace.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query
>> Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still nothing
>> gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though
>> nothing gets written ubtil the box is rebooted. It stays at 0kb until
>> I reboot, at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>> Hi Chris
>> With server side traces, SQL Server writes to the file in 128K
>> chunks, so you won't see anything written until you have 128K worth
>> of activity.
>> Look at fn_trace_getinfo( ) to check that your trace really is
>> running.
>> Then try stopping and closing the trace:
>> sp_trace_setstatus 1, 0 -- stop the trace
>> sp_trace_setstatus 1, 2 -- close the trace
>> You must stop before closing or the close won't do anything. Only
>> after closing SQL Server write to the file.
>> Or, you can stop the SQL Server services, and that will also flush
>> the collected trace events to the file.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>>I just created a server side Trace with no filters for testing but Im
>>>not able to start it. I call sp_trace_setstatus 1,1 and it says
>>>completed successfully. It then creates the file it writes too but
>>>never writes to it when I generate activity. Any ideas why?
>>>
>>> TIA, ChrisR
>>>
>>> sql2k sp3a
>>>
>>
>>
>>
>>
>>
>|||Woops! Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ursw1NkOFHA.3292@.TK2MSFTNGP12.phx.gbl...
> This is what I said in my first reply to you. Nothing will be written to
> the file until the trace is CLOSED.
> Please read my first reply again. You must first stop (set status to 0)
> and then close (set status to 2).
> If you want to save the trace definition, save the SQL commands that you
> used to start it, and write them to a file that you can load up again.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uZ8STvjOFHA.3512@.TK2MSFTNGP15.phx.gbl...
>> As noted earlier I did this:
>> --stopping trace
>> sp_trace_setstatus 1,0
>> Was I supposed to do this:
>> --stopping trace
>> sp_trace_setstatus 1,2
>> I was hoping to avoid that because BOL say it will delete the definition
>> from the server. I was hoping to be able to reuse it. Will info not be
>> written until closed/ deleted?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:ui3XxrjOFHA.2788@.TK2MSFTNGP09.phx.gbl...
>> Closing Qa will not stop or close the trace and the file size may not
>> update that often while it is running. If you look at
>> sp_trace_setstatus in BOL you will see hope to stop and close the trace.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:u$QxpVjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> --Status before stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 1
>> --stopping trace
>> sp_trace_setstatus 1,0
>> --status after stopping
>> traceid property value
>> -- -- -----------
>> 1 1 0
>> 1 2 c:\JobTrace
>> 1 3 5
>> 1 4 NULL
>> 1 5 0
>> I dont see a command to close the Trace, Im just closing Query
>> Anyalyzer.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eatZvLjOFHA.1396@.TK2MSFTNGP10.phx.gbl...
>> Are you saying you have stopped and closed the trace, and still
>> nothing gets written?
>> If so, can you show the output of fn_trace_getinfo and then exact
>> commands you are running to stop and close the trace?
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:uOTRg8iOFHA.2568@.TK2MSFTNGP14.phx.gbl...
>> Kalen I know this will sound odd, but it almost seems as though
>> nothing gets written ubtil the box is rebooted. It stays at 0kb until
>> I reboot, at which time there is a larger size.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OUzjYPfOFHA.2728@.TK2MSFTNGP15.phx.gbl...
>>> Hi Chris
>>>
>>> With server side traces, SQL Server writes to the file in 128K
>>> chunks, so you won't see anything written until you have 128K worth
>>> of activity.
>>>
>>> Look at fn_trace_getinfo( ) to check that your trace really is
>>> running.
>>>
>>> Then try stopping and closing the trace:
>>> sp_trace_setstatus 1, 0 -- stop the trace
>>> sp_trace_setstatus 1, 2 -- close the trace
>>>
>>> You must stop before closing or the close won't do anything. Only
>>> after closing SQL Server write to the file.
>>> Or, you can stop the SQL Server services, and that will also flush
>>> the collected trace events to the file.
>>> --
>>> HTH
>>> --
>>> Kalen Delaney
>>> SQL Server MVP
>>> www.SolidQualityLearning.com
>>>
>>>
>>> "ChrisR" <noemail@.bla.com> wrote in message
>>> news:u0nrZLfOFHA.904@.tk2msftngp13.phx.gbl...
>>>I just created a server side Trace with no filters for testing but
>>>Im not able to start it. I call sp_trace_setstatus 1,1 and it says
>>>completed successfully. It then creates the file it writes too but
>>>never writes to it when I generate activity. Any ideas why?
>>>
>>> TIA, ChrisR
>>>
>>> sql2k sp3a
>>>
>>>
>>>
>>
>>
>>
>>
>>
>

Thursday, March 29, 2012

Can't Sum Table Fields in SQL 2000 Reporting Services Report

Can't store chinese in SQL Database

Can't stop page breaks on my grouped list

Can't start transactional replication SQL 2005. System.OutOfMemory Exception during start

cant start Trace

cant start Trace

cant start Trace

sql report manager

Blog Archive

About Me