How to determine the best setting for Max Worker Threads

Dear All,
I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
environment. Right now, I am using the default 255 on Max Worker Threads, but
based on my analysis, during peak hour, the number of connection is around
300, is that better to set the max worker threads to a higher value e.g. 350?
What will happen with I set it to 510 instead? Many thanks!
From,
HenryEach thread takes a small amount of memory, even if it is not being used. I
don'tremember how much ( maybe 1k). The best practice is NOT to set it
arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
max connections occasionally. or alert on max connections > 310 so you will
know if you need to bump it again...
If these are not heads-down data entry people, it may even be OK for them to
thread share...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||IIRC, each worker thread uses closer to 384k of RAM.
I agree with Wayne. Use the 255 right now and use System Monitor and/or
Profiler and get a baseline value. Then bump it up to around 300 and check
the new values against your baseline. With a little tweaking here and
there, you should be able to come up with a reasonable value.
Rick Sawtell
MCT, MCSD, MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OGiSzYesEHA.3580@.TK2MSFTNGP10.phx.gbl...
> Each thread takes a small amount of memory, even if it is not being used.
I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you
will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them
to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Just because ht enumber of connection is over 255 does not in any way mean
you need more worker threads. Take a look at
dbcc sqlperf(UMSSTATS) and see how they are being used now.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> Dear All,
> I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> based on my analysis, during peak hour, the number of connection is around
> 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> What will happen with I set it to 510 instead? Many thanks!
> From,
> Henry|||Oops...
Thanks Andrew... I meant connections, not worker threads.
In fact, if I remember correctly, we demo'd a 4 processor SQL 7.0 instance a
number of years ago and found that tuning the worker threads down to
somewhere around 12 gave us the best performance...
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OLaR4thsEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker
Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is
around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>|||Dear Andrew,
Thanks for your advise. Would you please kindly explain how to use dbcc
sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
web article, we can use the "num runnable" to determine whether there is
bottleneck on CPU, if always 0, then is healthy, otherwise, may have
bottleneck. For my case, the current default worker thread setting 255, give
me 0 or 1 most of the time. Is that my goal is to adjust until the "num
runnable" always on 0? Please advise, many thanks!
From,
Henry :)
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||This is a sample output for the UMSSTATS. Your question is whether you
should bump up the max worker threads. The default is a maximum of 255 and
they get evenly divided between the processors assigned to SQL Server. In
your case that is 4 processors and that means the number of workers for each
processor can be over 60. But it will rarely get even close to that on a
system that is not extremely busy. The key is to look at the "idle workers"
size in realation to the "num workers". In your case if the value is always
0 or 1 you are hardly pushing the systems ability to work with what it has.
You are seeing normal behavior.
Scheduler ID 0.0
num users 18.0
num runnable 0.0
num workers 13.0
idle workers 11.0
work queued 0.0
cntxt switches 2.2994396E+7
cntxt switches(idle) 1.7793976E+7
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:E13223AE-6F93-4D5E-8082-472E73FA6585@.microsoft.com...
> Dear Andrew,
> Thanks for your advise. Would you please kindly explain how to use dbcc
> sqlperf(UMSSTATS) to determine the best setting? Coz as far as I know from
> web article, we can use the "num runnable" to determine whether there is
> bottleneck on CPU, if always 0, then is healthy, otherwise, may have
> bottleneck. For my case, the current default worker thread setting 255,
> give
> me 0 or 1 most of the time. Is that my goal is to adjust until the "num
> runnable" always on 0? Please advise, many thanks!
> From,
> Henry :)
> "Andrew J. Kelly" wrote:
>> Just because ht enumber of connection is over 255 does not in any way
>> mean
>> you need more worker threads. Take a look at
>> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> > but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> > 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||How do you know how many connections so that i can monitor this too.
thanks
"Wayne Snyder" wrote:
> Each thread takes a small amount of memory, even if it is not being used. I
> don'tremember how much ( maybe 1k). The best practice is NOT to set it
> arbitrarily high... In your case, I'd set it to 300 or 310. and watch for
> max connections occasionally. or alert on max connections > 310 so you will
> know if you need to bump it again...
> If these are not heads-down data entry people, it may even be OK for them to
> thread share...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>|||> How do you know how many connections so that i can monitor this too.
> thanks
You can monitor User Connections using Performance Monitor. You can also
get the current number of user connections with the following query:
SELECT cntr_value
FROM master..sysperfinfo
WHERE
object_name = 'SQLServer:General Statistics' AND
counter_name = 'User Connections'
Keep in mind that threads are pooled so you can have thousands of database
connections without the need to increase max worker threads. Connections
are often waiting for work and are not using a thread. Personally, I
wouldn't consider increasing the default unless the 'working thread limit of
255 has been reached' in appears in the error log.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:AFDCBDA1-9559-40E6-97A6-B0F52053920C@.microsoft.com...
> How do you know how many connections so that i can monitor this too.
> thanks
> "Wayne Snyder" wrote:
>> Each thread takes a small amount of memory, even if it is not being used.
>> I
>> don'tremember how much ( maybe 1k). The best practice is NOT to set it
>> arbitrarily high... In your case, I'd set it to 300 or 310. and watch
>> for
>> max connections occasionally. or alert on max connections > 310 so you
>> will
>> know if you need to bump it again...
>> If these are not heads-down data entry people, it may even be OK for them
>> to
>> thread share...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
>> > Dear All,
>> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
>> > environment. Right now, I am using the default 255 on Max Worker
>> > Threads,
>> but
>> > based on my analysis, during peak hour, the number of connection is
>> > around
>> > 300, is that better to set the max worker threads to a higher value
>> > e.g.
>> 350?
>> > What will happen with I set it to 510 instead? Many thanks!
>> > From,
>> > Henry
>>|||So if i run the dbcc sqlperf(umsstats) command say every hour i see how many
threads are in progress at any time......if reaching 255 at any one time
then i look at whether or not to change it
Thanks i have a look
"Andrew J. Kelly" wrote:
> Just because ht enumber of connection is over 255 does not in any way mean
> you need more worker threads. Take a look at
> dbcc sqlperf(UMSSTATS) and see how they are being used now.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:57C08B2F-4731-4771-BE39-BC8FFE922DD8@.microsoft.com...
> > Dear All,
> > I am using SQL server 2000 and have 4 CPU and 16 GB RAM on Cluster
> > environment. Right now, I am using the default 255 on Max Worker Threads,
> > but
> > based on my analysis, during peak hour, the number of connection is around
> > 300, is that better to set the max worker threads to a higher value e.g.
> > 350?
> > What will happen with I set it to 510 instead? Many thanks!
> > From,
> > Henry
>
>sql

How to deploy updated database from development environment to live environment?

I have finished a change request from our client. I need to update clients' database with the one in developments.
Here is the changes i made to database:
Added/Changed some tables
Added/Changed some stored procedures
Added data to some dictionary table
The data in clients' current database MUST be kept.
So how can I merge the changed information to clients' database?

Create update script and run it on client database. It is always good to maintain update script.
For example:
If you create a table/SP, use create script
If you change the table structure/SP code, use alter script, so you can retain data
Set of insert and/or update statements to take care of dictionary data.

|||

potturi_rp wrote:

Create update script and run it on client database. It is always good to maintain update script.
For example:
If you change the table structure/SP code, use alter script, so you can retain data

Hi, Sam,

Thank you for your help! I am not very clear about "use alter script, so you can retain data". Could you explain further, or could you send me some documents or hyperlinks about this? My email address ischarles.du@.hotmail.com
Thanks a million!
Regards,
Charles

|||If you are adding a column to a table, user ALTER TABLE to add the column. That way you donot loose data.

How to deploy reports using only report manager?

Hello!
I'm having problems deploying reports...
I have a development environment where I use Visual Studio to deploy reports
- All reports deployed have been manually assigned to shared data sources
instead of the one attached to the project. This works great.
However, when I need to deploy data sources and reports to a new instance of
reporting services in a production environment - I only want to use report
manager for this. I know I can't upload data sources, so I have manually
created those I have. The problem is that the reports I upload have a data
source attached in the XML that has a different ID than the one I just
created - So report manager complaint about the data sources being missing.
Now I want to access the reports and manually point them to the new shared
data sources, but the Browse button for data sources doesn't work - Nothing
happens when pushed.
So how am I supposed to assign the reports to the new data sources? - I have
manually tried to change the XML without luck, but I don't want to do this
every time I have to update a report.
HELP!
--
Cheers,
Daniel AHi Daniel:
Instead of using the report manager, you might consider a small .RSS
script.
In the ReportingServices/Samples/Scripts directory is a file called
PublishSampleReports.rss that programatically creates a datasource and
uploads rdl files using the source. Copying this file and building on
it gives you a base to build on.
This approach will also give you something you can test out in
development and then take to production with confidence it will work.
I always try to script as much as I can because I forget steps as soon
as I step in front of a prod machine and try to do stuff by hand.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
<DanielA@.discussions.microsoft.com> wrote:
>Hello!
>I'm having problems deploying reports...
>I have a development environment where I use Visual Studio to deploy reports
>- All reports deployed have been manually assigned to shared data sources
>instead of the one attached to the project. This works great.
>However, when I need to deploy data sources and reports to a new instance of
>reporting services in a production environment - I only want to use report
>manager for this. I know I can't upload data sources, so I have manually
>created those I have. The problem is that the reports I upload have a data
>source attached in the XML that has a different ID than the one I just
>created - So report manager complaint about the data sources being missing.
>Now I want to access the reports and manually point them to the new shared
>data sources, but the Browse button for data sources doesn't work - Nothing
>happens when pushed.
>So how am I supposed to assign the reports to the new data sources? - I have
>manually tried to change the XML without luck, but I don't want to do this
>every time I have to update a report.
>HELP!|||Thanks Scott
I will investigate this method closer...
Daniel
"Scott Allen" wrote:
> Hi Daniel:
> Instead of using the report manager, you might consider a small .RSS
> script.
> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss that programatically creates a datasource and
> uploads rdl files using the source. Copying this file and building on
> it gives you a base to build on.
> This approach will also give you something you can test out in
> development and then take to production with confidence it will work.
> I always try to script as much as I can because I forget steps as soon
> as I step in front of a prod machine and try to do stuff by hand.
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> <DanielA@.discussions.microsoft.com> wrote:
> >Hello!
> >
> >I'm having problems deploying reports...
> >
> >I have a development environment where I use Visual Studio to deploy reports
> >- All reports deployed have been manually assigned to shared data sources
> >instead of the one attached to the project. This works great.
> >
> >However, when I need to deploy data sources and reports to a new instance of
> >reporting services in a production environment - I only want to use report
> >manager for this. I know I can't upload data sources, so I have manually
> >created those I have. The problem is that the reports I upload have a data
> >source attached in the XML that has a different ID than the one I just
> >created - So report manager complaint about the data sources being missing.
> >Now I want to access the reports and manually point them to the new shared
> >data sources, but the Browse button for data sources doesn't work - Nothing
> >happens when pushed.
> >
> >So how am I supposed to assign the reports to the new data sources? - I have
> >manually tried to change the XML without luck, but I don't want to do this
> >every time I have to update a report.
> >
> >HELP!
>|||Hi Scott
It's pretty easy to script the deployment, but the report is still pointing
to a data source that doesn't exist and for some reason the browse button to
point to another data source doesn't work - So it didn't really solve my
problem :o(
Daniel
"Scott Allen" wrote:
> Hi Daniel:
> Instead of using the report manager, you might consider a small .RSS
> script.
> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss that programatically creates a datasource and
> uploads rdl files using the source. Copying this file and building on
> it gives you a base to build on.
> This approach will also give you something you can test out in
> development and then take to production with confidence it will work.
> I always try to script as much as I can because I forget steps as soon
> as I step in front of a prod machine and try to do stuff by hand.
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> <DanielA@.discussions.microsoft.com> wrote:
> >Hello!
> >
> >I'm having problems deploying reports...
> >
> >I have a development environment where I use Visual Studio to deploy reports
> >- All reports deployed have been manually assigned to shared data sources
> >instead of the one attached to the project. This works great.
> >
> >However, when I need to deploy data sources and reports to a new instance of
> >reporting services in a production environment - I only want to use report
> >manager for this. I know I can't upload data sources, so I have manually
> >created those I have. The problem is that the reports I upload have a data
> >source attached in the XML that has a different ID than the one I just
> >created - So report manager complaint about the data sources being missing.
> >Now I want to access the reports and manually point them to the new shared
> >data sources, but the Browse button for data sources doesn't work - Nothing
> >happens when pushed.
> >
> >So how am I supposed to assign the reports to the new data sources? - I have
> >manually tried to change the XML without luck, but I don't want to do this
> >every time I have to update a report.
> >
> >HELP!
>|||Well I solved it - As a part of the scripted deployment I used the
SetReportDataSources method to update the data source of the report once
uploaded - This works great...
Required a bit of work, but it's totally easy to deploy the reports now -
Just execute a batch file :o)
Daniel
"Daniel A" wrote:
> Hi Scott
> It's pretty easy to script the deployment, but the report is still pointing
> to a data source that doesn't exist and for some reason the browse button to
> point to another data source doesn't work - So it didn't really solve my
> problem :o(
>
> Daniel
>
> "Scott Allen" wrote:
> > Hi Daniel:
> >
> > Instead of using the report manager, you might consider a small .RSS
> > script.
> >
> > In the ReportingServices/Samples/Scripts directory is a file called
> > PublishSampleReports.rss that programatically creates a datasource and
> > uploads rdl files using the source. Copying this file and building on
> > it gives you a base to build on.
> >
> > This approach will also give you something you can test out in
> > development and then take to production with confidence it will work.
> > I always try to script as much as I can because I forget steps as soon
> > as I step in front of a prod machine and try to do stuff by hand.
> >
> > --
> > Scott
> > http://www.OdeToCode.com/blogs/scott/
> >
> > On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> > <DanielA@.discussions.microsoft.com> wrote:
> >
> > >Hello!
> > >
> > >I'm having problems deploying reports...
> > >
> > >I have a development environment where I use Visual Studio to deploy reports
> > >- All reports deployed have been manually assigned to shared data sources
> > >instead of the one attached to the project. This works great.
> > >
> > >However, when I need to deploy data sources and reports to a new instance of
> > >reporting services in a production environment - I only want to use report
> > >manager for this. I know I can't upload data sources, so I have manually
> > >created those I have. The problem is that the reports I upload have a data
> > >source attached in the XML that has a different ID than the one I just
> > >created - So report manager complaint about the data sources being missing.
> > >Now I want to access the reports and manually point them to the new shared
> > >data sources, but the Browse button for data sources doesn't work - Nothing
> > >happens when pushed.
> > >
> > >So how am I supposed to assign the reports to the new data sources? - I have
> > >manually tried to change the XML without luck, but I don't want to do this
> > >every time I have to update a report.
> > >
> > >HELP!
> >
> >|||Cool!
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 29 Oct 2004 02:03:02 -0700, "Daniel A"
<DanielA@.discussions.microsoft.com> wrote:
>Well I solved it - As a part of the scripted deployment I used the
>SetReportDataSources method to update the data source of the report once
>uploaded - This works great...
>Required a bit of work, but it's totally easy to deploy the reports now -
>Just execute a batch file :o)
>
>Daniel
>
>"Daniel A" wrote:
>> Hi Scott
>> It's pretty easy to script the deployment, but the report is still pointing
>> to a data source that doesn't exist and for some reason the browse button to
>> point to another data source doesn't work - So it didn't really solve my
>> problem :o(
>>
>> Daniel
>>
>> "Scott Allen" wrote:
>> > Hi Daniel:
>> >
>> > Instead of using the report manager, you might consider a small .RSS
>> > script.
>> >
>> > In the ReportingServices/Samples/Scripts directory is a file called
>> > PublishSampleReports.rss that programatically creates a datasource and
>> > uploads rdl files using the source. Copying this file and building on
>> > it gives you a base to build on.
>> >
>> > This approach will also give you something you can test out in
>> > development and then take to production with confidence it will work.
>> > I always try to script as much as I can because I forget steps as soon
>> > as I step in front of a prod machine and try to do stuff by hand.
>> >
>> > --
>> > Scott
>> > http://www.OdeToCode.com/blogs/scott/
>> >
>> > On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
>> > <DanielA@.discussions.microsoft.com> wrote:
>> >
>> > >Hello!
>> > >
>> > >I'm having problems deploying reports...
>> > >
>> > >I have a development environment where I use Visual Studio to deploy reports
>> > >- All reports deployed have been manually assigned to shared data sources
>> > >instead of the one attached to the project. This works great.
>> > >
>> > >However, when I need to deploy data sources and reports to a new instance of
>> > >reporting services in a production environment - I only want to use report
>> > >manager for this. I know I can't upload data sources, so I have manually
>> > >created those I have. The problem is that the reports I upload have a data
>> > >source attached in the XML that has a different ID than the one I just
>> > >created - So report manager complaint about the data sources being missing.
>> > >Now I want to access the reports and manually point them to the new shared
>> > >data sources, but the Browse button for data sources doesn't work - Nothing
>> > >happens when pushed.
>> > >
>> > >So how am I supposed to assign the reports to the new data sources? - I have
>> > >manually tried to change the XML without luck, but I don't want to do this
>> > >every time I have to update a report.
>> > >
>> > >HELP!
>> >
>> >|||> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss
I don't see any such directory or file on my machine - where exactly is it located'|||> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss
Ewwww - never mind my other post, I found the file

How to Deploy Reports to a Production Environment

Our situation is that we are developing a set of "master" reports locally
using Visual Studio .NET 2003 (and, of course, the Reporting Services
designer). Once the master reports are finished, we will need to install
these reports to our customers' computers (their computers are not a part of
our network, we don't have access to them to deploy from our computers and
the Visual Studio .NET 2003 solution/project). And then we will need to
deploy to the ReportServer on their server.
Additionally, the customers' server will have VB .NET installed (the basic
edition to allow RDL development).
So, should we simply copy the solution, project, RDS, and RDL files to the
customers' server, then open the solution in VB .NET, and deploy? Or is
there a better way?
Also, since we are developing the master reports in a solution/project in
Visual Studio .NET on our local development servers, is there any
compatibility issues in trying to open that same solution in VB .NET on the
customers' server?
Thanks.I would think the easiest way would be to build a quick app to do this using
the CreateReport SOAP Api. You could then make this app as simple or
complex as you need it and not need to worry about your customers having any
VS components. We shipped a sample app (RSExplorer I believe) that does
allow you to deploy but it would have to be modified if you wanted to easily
upload a directory of rdls.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Belak" <nospam@.nospam.com> wrote in message
news:OrhITzzmEHA.3264@.TK2MSFTNGP12.phx.gbl...
> Our situation is that we are developing a set of "master" reports locally
> using Visual Studio .NET 2003 (and, of course, the Reporting Services
> designer). Once the master reports are finished, we will need to install
> these reports to our customers' computers (their computers are not a part
of
> our network, we don't have access to them to deploy from our computers and
> the Visual Studio .NET 2003 solution/project). And then we will need to
> deploy to the ReportServer on their server.
> Additionally, the customers' server will have VB .NET installed (the basic
> edition to allow RDL development).
> So, should we simply copy the solution, project, RDS, and RDL files to the
> customers' server, then open the solution in VB .NET, and deploy? Or is
> there a better way?
> Also, since we are developing the master reports in a solution/project in
> Visual Studio .NET on our local development servers, is there any
> compatibility issues in trying to open that same solution in VB .NET on
the
> customers' server?
> Thanks.
>

How to deploy a single package of a multiple package solution

Hi,

I have a multiple package solution that I've deployed using the manifest file produced with the development environment. If I need to make a change to a single package, how do I then deploy this package? Is it a case of rebuilding the entire solution and re-running the manifest file, or is there a simpler way?

Any help would be much appreciated, cheers.

Can't you just go into the bin folder of your solution and select the appropriate package then copy this to your desired location?|||

I've experienced some strange behaviour doing this in the past that was solved by redeploying the entire solution using the deployment wizard. So I wasn't sure if there was something going on with the registry that I wasn't appreciating.

Are you confident that simply copying a modified package to the SSIS package store location will work consistently?

|||

To be perfectly honest, I've never used the manifest or deployment utility. All I ever do is copy the package from the bin folder to my release folder. I then use the SQL package store and import from my release folder (although you can do this from your development directory, I like to keep one additional layer of last good build that I can reimport to the sql package store if worst comes to worst).

I do not believe that there is anything modified in the registry at any point in time during package deployment. I could very well be wrong, but from what I have read that is not the case. (NOTE: there is the possibility that you are using registry configurations, which you will need to set up in your new location)

-- From Microsoft SQL Server 2005 Integration Services by Kirk Haselden

"Integration Services provides a utility for moving packages, butfor a moment, let's take a step back and think about the deployment problem. What is it you're trying to accomplish? Is there something in the package, some setting or variable that can't be moved by simply copying the package to another machine? Not really. However, problems arise when you move a package that references external resources that are available on one machine that aren't available on another. For example, no amount of configuration magic is going to help if you attempt to run a package that references a custom task that isn't installed on the destination machine."

|||

Ah, maybe that is the answer, simply use the Import option to load modified packages to the SSIS package store. Thanks for taking the time to reply, much appreciated.

How to delete all data from table when foreign-key exists.

I would like to delete all data from the tables in my database so that I can
test in a clean environment. With no data, it makes creating controlled
data much easier.
I would normally use "truncate", but it seems that I cannot since the tables
I want to delete from have foreign key constraints.
So, I end up using the "delete" command; however, this takes forever since
most that tables have between 100000 and millions of records.
I would imaging the performance issue is related to the fact that "delete"
writes to the transition log while "truncate" does not.
How can I delete all data from a table with foreign key constructs quickly?Disable the foreign key constraints temporarily. What's the point of
keeping them there if you're deleting all the data?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||Another option would be to drop the tables and re-create them (in a
dependent order, of course).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>

How to delete all data from table when foreign-key exists.

I would like to delete all data from the tables in my database so that I can
test in a clean environment. With no data, it makes creating controlled
data much easier.
I would normally use "truncate", but it seems that I cannot since the tables
I want to delete from have foreign key constraints.
So, I end up using the "delete" command; however, this takes forever since
most that tables have between 100000 and millions of records.
I would imaging the performance issue is related to the fact that "delete"
writes to the transition log while "truncate" does not.
How can I delete all data from a table with foreign key constructs quickly?Disable the foreign key constraints temporarily. What's the point of
keeping them there if you're deleting all the data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||Another option would be to drop the tables and re-create them (in a
dependent order, of course).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||If you want an easy way which doesn't involve dropping the
constraints, you could use truncate table on all the
tables, but being careful about the order - apply the
command from the outside inwards relative to your schema
eg do the lookup tables first and the most central table
last.
Regards,
Paul Ibison|||Paul,
I'm afraid that truncate isn't allowed event if you have 0 rows in the
referencing tables. And even if you disable the FK, it isn't allowed. I
usually truncate the tables "at the bottom", these are typically the ones
with the most rows in. Then I just work my way "upward" and do DELETE on
those tables.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote in message
news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
> If you want an easy way which doesn't involve dropping the
> constraints, you could use truncate table on all the
> tables, but being careful about the order - apply the
> command from the outside inwards relative to your schema
> eg do the lookup tables first and the most central table
> last.
> Regards,
> Paul Ibison|||Thanks Tibor - had forgotten this behaviour.
Regards,
Paul Ibison
>--Original Message--
>Paul,
>I'm afraid that truncate isn't allowed event if you have
0 rows in the
>referencing tables. And even if you disable the FK, it
isn't allowed. I
>usually truncate the tables "at the bottom", these are
typically the ones
>with the most rows in. Then I just work my way "upward"
and do DELETE on
>those tables.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote
in message
>news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
>> If you want an easy way which doesn't involve dropping
the
>> constraints, you could use truncate table on all the
>> tables, but being careful about the order - apply the
>> command from the outside inwards relative to your schema
>> eg do the lookup tables first and the most central table
>> last.
>> Regards,
>> Paul Ibison
>
>.
>