Sql Activity log backup: running

Showing posts with label running. Show all posts

Tuesday, March 20, 2012

Adding table/article w/o starting snapshot!

Before running the snapshot agent, run
sp_refreshsubscriptions 'publicationname'
Cheers,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for ur response, but I still get the same error.
"A snapshot was not generated b/c no subscription needed intialization".
Regards
Naveed.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1aa701c4aa03$e43f6440$a501280a@.phx.gbl...
> Before running the snapshot agent, run
> sp_refreshsubscriptions 'publicationname'
> Cheers,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Naveed,
was the initial subscription a noinit one?
It looks like it was, in which case you'll have to
manually add the scripts
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;299903), and DTS the table. Alternatively you can
publish the table in a separate publication.
Rgds,
Paul Ibison[vbcol=seagreen]
|||Hello Paul;
I think if I reintialize the subscription then snapshot will start
publishing all articles instead of 1 article.
and I dont want that.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3fb201c4aa11$91875d30$a601280a@.phx.gbl...
> Naveed,
> was the initial subscription a noinit one?
> It looks like it was, in which case you'll have to
> manually add the scripts
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;299903), and DTS the table. Alternatively you can
> publish the table in a separate publication.
> Rgds,
> Paul Ibison
>
|||Naveed,
to recap - from your other posts you mention that running the snapshot agent
doesn't do anything. After adding a new article and refreshing the
subscription, still the snapshot agent doesn't do anything. My assumption is
that the initial setup was a noinit one, in which case no snapshot of a new
article is produced. The new article is a part of the publication and (I'm
not suggesting you try this) if you update a record in this article on the
publisher there should be an error in the distribution agent which complains
about the absence of an update stored procedure onthe subscriber. Now you
have a choice -
(a) set up these stored procedures by hand using
sp_scriptpublicationcustomprocs and transfer the table by hand. Be sure to
remove the identity attribute if there is one.
(b) reinitialize.
(c) add the new article to a separate publication. This can cause integrity
problems if the article is related to other articles in the first
publication.
I have suggested (a), which won't result in a new snapshot of all articles
being produced.
HTH,
Paul Ibison (MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul,
I'm using Transactional Replication and it's Push subscription.
Can u plz give me the complete steps to add a single table
w/o starting snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
|||Naveed,
I don't have a complete script to hand, but this is the basic pattern I have
followed:
On the publisher:
EXEC sp_addarticle
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.source_owner = N'dbo',
@.source_object = N'Categories',
@.destination_table = N'Categories',
@.type = N'logbased',
@.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000000073,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_Categories',
@.del_cmd = N'CALL sp_MSdel_Categories',
@.upd_cmd = N'MCALL sp_MSupd_Categories'
GO
exec sp_addsubscription
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.subscriber = N'HOME-WIN2K',
@.destination_db = N'Pubs',
@.sync_type = N'none',
@.update_mode = N'read only'
GO
sp_refreshsubscriptions
run sp_scriptpublicationcustomprocs to generate the necessary procs and
apply them to the subscriber.
(BTW, I have also found that sp_refreshsubscription works for push and
pull).
HTH,
Paul Ibison

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.

PROBLEM:

I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro. I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE: I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

Kevin,

I've seen this in a couple of circumstances:

1. you had a pre-release version of VS2005 (Whidbey) on your dev machine at some point , then installed the RTM, and something is disconnected in the feature that does the automatic BindingSource generation when you add a new database datasource to your project. this, unfortunately, requires a cleanup and reinstall ofVS2005

2. you are accidentally trying to use a SQL CE 2.0 database as the data source

Darren

|||

Darren,

This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.

I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.

I am connecting to it via as SQL Server Mobile Edition Data Source.

I did update the first post to add that it is in fact only 1 table that locks up the solution. The other 48 tables load just fine. I don't see any differences in this table from the others except that it has the most columns. It has 52 columns of types int, nvarchar, rowguid, bigint.

Is there anything else you can think of for me to try or check out?

|||

Kevin,

Would you be willing to email me a copy of your sdf file and I can try it in my VS2005 install and maybe see what it is about that one table that can't be modeled into a BindingSource? You can contact me through my blog (just Google me).

-Darren Shaffer

|||

Darren,

I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.

We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.

We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.

So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.

I'll will send you a copy of this database to checkout for yourself.

|||

Kevin,

Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.

thanks,

Darren

|||

Darren,

Thank you for the update.

Can you please eloborate a little as to what the problem was?

|||

Kevin,

I got the same behavior you did - you try to add the Vehicles table to a DataSet in VS2005 and it sits forever. I tried repairing the database first, and even tried removing the merge subscription from the SQL Mobile database first. Same behavior. The only thing I think might be involved here is that you have several BIGINT columns in that table and these translate to an INT64 in .NET. I'm wondering if the VSD (Visual Studio for Devices) team anticipated the need to support INT64 in the dataset designer for SQL Mobile data sources.

I'll let you know when I hear back from Laxmi on his investigation of the issue.

Darren

|||

Hi Kevin & Darren,

My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.

Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

|||I wonder if it's possible to be a little more specific...? I found this thread just doing a Google search for "Microsoft Visual Studio .NET 2005 'Delay Notification'" - Since I seem to get this message every now and then and haven't pinned it down to anything specific that causes it. My co-worker just walked up and I showed it to him and asked if he's ever seen it, and he said Yup, lots... Also, it happens on different machines... My home PC, my 2 work PCs, and my VMs.

Sometimes it comes back after a bit.. Sometimes (like now) it doesn't! (well, I've waited about 20 minutes.. I figure that's long enough!) ;) Nothing else is open and the CPU is more or less totally idle according to the Task Manager.
It would be nice to know what's causing it, so I can try to avoid certain behaviours.
I have to say that it's Excessively Lame that it says "If you regularly encounter this delay during normal usage please report this prolem to Microsoft." -- But it doesn't say to who, or what email, or what phone number I should report this to! Making me search for something that should have been known to whoever wrote that "Delay Notification" dialog is, as I say.. Lame. :(

Visual Studio 2005 in General ROCKS. This is just one particular very annoying (to me) thing.
Also, the PC is not directly connected to the Internet, so even though when I force-kill it with Task Manager and it asks me to "Please tell Microsoft about this problem." - I can't send it, and it doesn't give me any other way to do so! - That also is another thing about MS products that I find Particularly annoying!

Thanks
- Andrew

PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||

Hi Laxmi,

I thought I would let you know that this problem is NOT reserved just for mobile server connections. This problem is very common for straight Windows Applications. I receive this message VERY often. I thought it was due to my converting a VS2003 solution across to VS2005, but that is not the case either. Although, to clarify, converting a VS2003 solution to VS2005 does seem to cause this message to be raised more often than from a brand new creation of a solution.

I hope this helps identify the bug more specifically.

cheers,

Mark Chimes

|||

I think there has been lot of confusion about the context of this thread. There is a particular table schema and when this table is added to DataSet, VS is locked up. There can be other cases of VS locking up which are in no way related to this thread. So, please just dont take this thread as just "VS Locking up" case rather take it as "VS Locking up for a particular SQL Mobile Table Schema".

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation

|||no confusion occuring except when you say the error is restricted to '...a particular table schema'. i am experiencing the exact same problem in a web app i am busy with. i drag a dataset from the toolbox which then creates a default datatable...WITH an adapter ...et voila! i cant say for sure where the designer is trying to link the table adapter to but the result is a dead-end for vs.|||

Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?

Thanks,

Laxmi

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

PROBLEM:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro. I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE: I am editing this post with an update.

Kevin,

I've seen this in a couple of circumstances:

2. you are accidentally trying to use a SQL CE 2.0 database as the data source

Darren

|||

Darren,

This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.

I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.

I am connecting to it via as SQL Server Mobile Edition Data Source.

Is there anything else you can think of for me to try or check out?

|||

Kevin,

-Darren Shaffer

|||

Darren,

I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.

We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.

We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.

So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.

I'll will send you a copy of this database to checkout for yourself.

|||

Kevin,

Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.

thanks,

Darren

|||

Darren,

Thank you for the update.

Can you please eloborate a little as to what the problem was?

|||

Kevin,

I'll let you know when I hear back from Laxmi on his investigation of the issue.

Darren

|||

Hi Kevin & Darren,

My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.

Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation

Thanks
- Andrew

PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||

Hi Laxmi,

I hope this helps identify the bug more specifically.

cheers,

Mark Chimes

|||

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation

Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?

Thanks,

Laxmi

Monday, March 19, 2012

Adding staggered running total and average to query

Hi,

I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).

Given the sample data:

CREATE TABLE [dbo].[QtrInflation] (
[Qtr] [smalldatetime] NOT NULL ,
[CPI] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO QtrInflation (Qtr, CPI)
SELECT '1960-03-01', 0.7500 UNION
SELECT '1960-06-01', 1.4800 UNION
SELECT '1960-09-01', 1.4600 UNION
SELECT '1960-12-01', 0.7200 UNION
SELECT '1961-03-01', 0.7100 UNION
SELECT '1961-06-01', 0.7100 UNION
SELECT '1961-09-01',-0.7000 UNION
SELECT '1961-12-01', 0.0000 UNION
SELECT '1962-03-01', 0.0000 UNION
SELECT '1962-06-01', 0.0000 UNION
SELECT '1962-09-01', 0.0000 UNION
SELECT '1962-12-01', 0.0000 UNION
SELECT '1963-03-01', 0.0000 UNION
SELECT '1963-06-01', 0.0000 UNION
SELECT '1963-09-01', 0.7100 UNION
SELECT '1963-12-01', 0.0000 UNION
SELECT '1964-03-01', 0.7000 UNION
SELECT '1964-06-01', 0.7000 UNION
SELECT '1964-09-01', 1.3900 UNION
SELECT '1964-12-01', 0.6800 UNION
SELECT '1965-03-01', 0.6800 UNION
SELECT '1965-06-01', 1.3500 UNION
SELECT '1965-09-01', 0.6700 UNION
SELECT '1965-12-01', 1.3200

I am trying to return the following results:

Qtr CPI YrCPI AvgYrCPI
--- -- -- ---
1-Jun-60 1.48
1-Sep-60 1.46
1-Dec-60 0.72
1-Mar-61 0.71 4.37
1-Jun-61 0.71 3.60
1-Sep-61 -0.70 1.44
1-Dec-61 0.00 0.72 2.53
1-Mar-62 0.00 0.01 1.44
1-Jun-62 0.00 -0.70 0.37
1-Sep-62 0.00 0.00 0.01
1-Dec-62 0.00 0.00 -0.17
1-Mar-63 0.00 0.00 -0.18
1-Jun-63 0.00 0.00 0.00
1-Sep-63 0.71 0.71 0.18
1-Dec-63 0.00 0.71 0.36
1-Mar-64 0.70 1.41 0.71
1-Jun-64 0.70 2.11 1.24
1-Sep-64 1.39 2.79 1.76
1-Dec-64 0.68 3.47 2.45
1-Mar-65 0.68 3.45 2.96
1-Jun-65 1.35 4.10 3.45
1-Sep-65 0.67 3.38 3.60
1-Dec-65 1.32 4.02 3.74

Note, 4 data points are required to calculate a moving sum of CPI
(YrCPI) and 4 calculate YrCPI figures are required calculate the
annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
first 7 results

This sad effort is about as far as I've got:

SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
FROM QtrInflation I
JOIN (
SELECT TOP 4 Qtr, CPI
FROM QtrInflation
) S
ON S.Qtr <= I.Qtr
GROUP BY I.Qtr, I.CPI
ORDER BY I.Qtr ASC

Can anyone suggest how do achieve this result without having to resort
to cursors?

Thanks,

StephenHi

This will do it (I think!) but there may be a neater way!

SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
FROM QtrInflation S LEFT JOIN
( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
LEFT JOIN
( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
FROM QtrInflation
GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
GROUP BY Q.Qtr
HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
< B.Qtr
GROUP BY R.Qtr
HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
ORDER BY S.Qtr

John
"Stephen Miller" <jsausten@.hotmail.com> wrote in message
news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||Stephen,

Here is another approach that I think will work
for you:

-- alternate solution
create table Weights (
offset int,
weight decimal(3,2),
weightA decimal(3,2),
weightB decimal(3,2)
)
go

insert into Weights

select 6, 0, 0, 0.25 union all
select 5, 0, 0, 0.5 union all
select 4, 0, 0, 0.75 union all
select 3, 0, 1, 1.00 union all
select 2, 0, 1, 0.75 union all
select 1, 0, 1, 0.5 union all
select 0, 1, 1, 0.25
go

select
dateadd(month,3*Offset,Q1.Qtr) Qtr,
sum(Weight*Q1.CPI) CPI,
case when sum(WeightA) = 4 then sum(WeightA*Q1.CPI) else NULL end as YrCPI,
case when sum(WeightB) = 4 then sum(WeightB*Q1.CPI) else NULL end as MACPI
from QtrInflation Q1, Weights
group by dateadd(month,3*Offset,Q1.Qtr)
having sum(Weight) = 1
order by dateadd(month,3*Offset,Q1.Qtr)

-- Steve Kass
-- Drew University
-- Ref: 17F9A22A-8DDA-4812-A8CD-B68062BADFA1

Stephen Miller wrote:
> Hi,
> I am trying to add a staggered running total and average to a query
> returning quarterly CPI data. I need to add 4 quarterly data points
> together to calculate a moving 12-month sum (YrCPI), and then to
> complicate things, calculate a moving average of the 12-month figure
> (AvgYrCPI).
> Given the sample data:
> CREATE TABLE [dbo].[QtrInflation] (
> [Qtr] [smalldatetime] NOT NULL ,
> [CPI] [decimal](8, 4) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QtrInflation (Qtr, CPI)
> SELECT '1960-03-01', 0.7500 UNION
> SELECT '1960-06-01', 1.4800 UNION
> SELECT '1960-09-01', 1.4600 UNION
> SELECT '1960-12-01', 0.7200 UNION
> SELECT '1961-03-01', 0.7100 UNION
> SELECT '1961-06-01', 0.7100 UNION
> SELECT '1961-09-01',-0.7000 UNION
> SELECT '1961-12-01', 0.0000 UNION
> SELECT '1962-03-01', 0.0000 UNION
> SELECT '1962-06-01', 0.0000 UNION
> SELECT '1962-09-01', 0.0000 UNION
> SELECT '1962-12-01', 0.0000 UNION
> SELECT '1963-03-01', 0.0000 UNION
> SELECT '1963-06-01', 0.0000 UNION
> SELECT '1963-09-01', 0.7100 UNION
> SELECT '1963-12-01', 0.0000 UNION
> SELECT '1964-03-01', 0.7000 UNION
> SELECT '1964-06-01', 0.7000 UNION
> SELECT '1964-09-01', 1.3900 UNION
> SELECT '1964-12-01', 0.6800 UNION
> SELECT '1965-03-01', 0.6800 UNION
> SELECT '1965-06-01', 1.3500 UNION
> SELECT '1965-09-01', 0.6700 UNION
> SELECT '1965-12-01', 1.3200
>
> I am trying to return the following results:
> Qtr CPI YrCPI AvgYrCPI
> --- -- -- ---
> 1-Jun-60 1.48
> 1-Sep-60 1.46
> 1-Dec-60 0.72
> 1-Mar-61 0.71 4.37
> 1-Jun-61 0.71 3.60
> 1-Sep-61 -0.70 1.44
> 1-Dec-61 0.00 0.72 2.53
> 1-Mar-62 0.00 0.01 1.44
> 1-Jun-62 0.00 -0.70 0.37
> 1-Sep-62 0.00 0.00 0.01
> 1-Dec-62 0.00 0.00 -0.17
> 1-Mar-63 0.00 0.00 -0.18
> 1-Jun-63 0.00 0.00 0.00
> 1-Sep-63 0.71 0.71 0.18
> 1-Dec-63 0.00 0.71 0.36
> 1-Mar-64 0.70 1.41 0.71
> 1-Jun-64 0.70 2.11 1.24
> 1-Sep-64 1.39 2.79 1.76
> 1-Dec-64 0.68 3.47 2.45
> 1-Mar-65 0.68 3.45 2.96
> 1-Jun-65 1.35 4.10 3.45
> 1-Sep-65 0.67 3.38 3.60
> 1-Dec-65 1.32 4.02 3.74
> Note, 4 data points are required to calculate a moving sum of CPI
> (YrCPI) and 4 calculate YrCPI figures are required calculate the
> annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> first 7 results
> This sad effort is about as far as I've got:
> SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> FROM QtrInflation I
> JOIN (
> SELECT TOP 4 Qtr, CPI
> FROM QtrInflation
> ) S
> ON S.Qtr <= I.Qtr
> GROUP BY I.Qtr, I.CPI
> ORDER BY I.Qtr ASC
> Can anyone suggest how do achieve this result without having to resort
> to cursors?
> Thanks,
> Stephen|||John & Steve

Thank you for two very interesting (and very different) responses. You
guys are gurus! Both return the results I'm looking for and now I'm
stuck picking which one's best ;)

Thanks again,

Stephen

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f6d878f$0$10783$afc38c87@.news.easynet.co.uk>...
> Hi
> This will do it (I think!) but there may be a neater way!
> SELECT S.Qtr, S.CPI, D.YrCPI, E.AvgCPI
> FROM QtrInflation S LEFT JOIN
> ( SELECT Q.Qtr, SUM(A.CPI) AS YrCPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) D ON S.Qtr = D.Qtr
> LEFT JOIN
> ( SELECT R.Qtr, SUM(B.CPI)/4 AS AvgCPI
> FROM QtrInflation R LEFT JOIN ( SELECT Q.Qtr, SUM(A.CPI) AS CPI
> FROM QtrInflation Q LEFT JOIN ( SELECT Qtr, SUM(CPI) AS CPI
> FROM QtrInflation
> GROUP BY Qtr) A ON Q.Qtr >= A.Qtr AND DATEADD(YEAR,-1,Q.Qtr) < A.Qtr
> GROUP BY Q.Qtr
> HAVING COUNT(A.Qtr) = 4 ) B ON R.Qtr >= B.Qtr AND DATEADD(YEAR,-1,R.Qtr)
> < B.Qtr
> GROUP BY R.Qtr
> HAVING COUNT(B.Qtr) = 4 ) E ON S.Qtr = E.Qtr
> ORDER BY S.Qtr
> John
> "Stephen Miller" <jsausten@.hotmail.com> wrote in message
> news:cdb404de.0309210139.58ffad34@.posting.google.c om...
> > Hi,
> > I am trying to add a staggered running total and average to a query
> > returning quarterly CPI data. I need to add 4 quarterly data points
> > together to calculate a moving 12-month sum (YrCPI), and then to
> > complicate things, calculate a moving average of the 12-month figure
> > (AvgYrCPI).
> > Given the sample data:
> > CREATE TABLE [dbo].[QtrInflation] (
> > [Qtr] [smalldatetime] NOT NULL ,
> > [CPI] [decimal](8, 4) NOT NULL
> > ) ON [PRIMARY]
> > GO
> > INSERT INTO QtrInflation (Qtr, CPI)
> > SELECT '1960-03-01', 0.7500 UNION
> > SELECT '1960-06-01', 1.4800 UNION
> > SELECT '1960-09-01', 1.4600 UNION
> > SELECT '1960-12-01', 0.7200 UNION
> > SELECT '1961-03-01', 0.7100 UNION
> > SELECT '1961-06-01', 0.7100 UNION
> > SELECT '1961-09-01',-0.7000 UNION
> > SELECT '1961-12-01', 0.0000 UNION
> > SELECT '1962-03-01', 0.0000 UNION
> > SELECT '1962-06-01', 0.0000 UNION
> > SELECT '1962-09-01', 0.0000 UNION
> > SELECT '1962-12-01', 0.0000 UNION
> > SELECT '1963-03-01', 0.0000 UNION
> > SELECT '1963-06-01', 0.0000 UNION
> > SELECT '1963-09-01', 0.7100 UNION
> > SELECT '1963-12-01', 0.0000 UNION
> > SELECT '1964-03-01', 0.7000 UNION
> > SELECT '1964-06-01', 0.7000 UNION
> > SELECT '1964-09-01', 1.3900 UNION
> > SELECT '1964-12-01', 0.6800 UNION
> > SELECT '1965-03-01', 0.6800 UNION
> > SELECT '1965-06-01', 1.3500 UNION
> > SELECT '1965-09-01', 0.6700 UNION
> > SELECT '1965-12-01', 1.3200
> > I am trying to return the following results:
> > Qtr CPI YrCPI AvgYrCPI
> > --- -- -- ---
> > 1-Jun-60 1.48
> > 1-Sep-60 1.46
> > 1-Dec-60 0.72
> > 1-Mar-61 0.71 4.37
> > 1-Jun-61 0.71 3.60
> > 1-Sep-61 -0.70 1.44
> > 1-Dec-61 0.00 0.72 2.53
> > 1-Mar-62 0.00 0.01 1.44
> > 1-Jun-62 0.00 -0.70 0.37
> > 1-Sep-62 0.00 0.00 0.01
> > 1-Dec-62 0.00 0.00 -0.17
> > 1-Mar-63 0.00 0.00 -0.18
> > 1-Jun-63 0.00 0.00 0.00
> > 1-Sep-63 0.71 0.71 0.18
> > 1-Dec-63 0.00 0.71 0.36
> > 1-Mar-64 0.70 1.41 0.71
> > 1-Jun-64 0.70 2.11 1.24
> > 1-Sep-64 1.39 2.79 1.76
> > 1-Dec-64 0.68 3.47 2.45
> > 1-Mar-65 0.68 3.45 2.96
> > 1-Jun-65 1.35 4.10 3.45
> > 1-Sep-65 0.67 3.38 3.60
> > 1-Dec-65 1.32 4.02 3.74
> > Note, 4 data points are required to calculate a moving sum of CPI
> > (YrCPI) and 4 calculate YrCPI figures are required calculate the
> > annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
> > first 7 results
> > This sad effort is about as far as I've got:
> > SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
> > FROM QtrInflation I
> > JOIN (
> > SELECT TOP 4 Qtr, CPI
> > FROM QtrInflation
> > ) S
> > ON S.Qtr <= I.Qtr
> > GROUP BY I.Qtr, I.CPI
> > ORDER BY I.Qtr ASC
> > Can anyone suggest how do achieve this result without having to resort
> > to cursors?
> > Thanks,
> > Stephen|||Hi Stephen

I would expect Steve's solution to work alot better than mine under
large loads!

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI). <<

I hope you mean to have a key on this table and some contraints

CREATE TABLE QtrInflation
(qtr SMALLDATETIME NOT NULL PRIMARY KEY
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
cpi DECIMAL(8,4) NOT NULL
CHECK(cpi >= 0.0000));

CREATE TABLE QtrReportRanges
(start_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
end_date SMALLDATETIME NOT NULL
CHECK (MONTH(qtr) IN (03, 06, 09, 12)
AND (DAY(qtr) = 01)),
CHECK (start_date < end_date),
PRIMARY KEY (start_date < end_date));

INSERT INTO QtrReportRanges VALUES ('1960-03-01', '1960-12-01');
INSERT INTO QtrReportRanges VALUES ('1960-06-01', '1961-03-01');
etc,

now you can get the report easily.

SELECT R.start_date, R.end_date, SUM(cpi) AS yr_cpi, AVG(cpi) AS
avg_yr_cpi
FROM QtrInflation AS I, QtrReportRanges AS R
WHERE I.qtr BETWEEN R.start_date AND R.end_date
GROUP BY R.start_date, R.end_date;

Sunday, March 11, 2012

Adding Reporting Services to an existing SP1 install

HI,

Is there an easy way to install reporting services on a machine that is already running sp1? When I initially isntalled Sql Server I did not install ssrs. Now, SP1 is installed and when I run the setup tool to install ssrs, it warns me regarding a "version change", presumablly because my media is not sp1, but RTM. Any ideas?

1. Install RS from RTM media

2. Apply SP1 on RS.

Adding Report Filter on Float field

I am adding a filter on a float datatype field in my dataset.
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>

adding query results to email body using CDOSYSmail

Thanks in advance - Steve.
I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to preclude
using SQL Mail (as far as I know). So I've been working on using CDOSYSmail
to send email, and attachment, and the results from a stored procedure (a
set of queries that generates 6 summary reports with varying columns). I've
inserted the results from the stored procedure into a table called, results
varchar(4000).
I'm trying to insert the results from 'sproc_name' into the body of an email
and could use some help.
The stored procedure, 'usp_send_cdosysmail' is working fine except I can't
get the results from another stored procedure into the body of an email. The
table 'result' is one column varchar(4000); I'd prefer just executing the
'sproc_name' and avoid the cursor since i'm new to cursors. I receive the
error below:
Server: Msg 170, Level 15, State 1, Line 17
Line 17: incorrect syntax near @.body1
declare @.Body1 varchar(4000)
set @.Body1 = ''
declare r_cursor cursor for
select result from test.dbo.result
open r_cursor
declare @.r_return cursor
exec db_name.dbo.sproc_name @.r_return = OUTPUT
while (@.@.fetch_status=0)
begin
fetch next from @.r_return
end
close r_cursor
@.body1 = @.r_return
deallocate r_cursor
exec master..usp_send_cdosysmail
@.from='user1@.domain.com',
@.to ='user2@.domain.com',
@.subject ='Test',
@.body = @.body1,
@.attachments = 'C:\test.txt',
@.smtpserver = 'mailserver.domain.com',
@.bodytype ='TEXTBody' -- 'HTMLBody'Hi Steve
Assuming your result table is declared as something like:
CREATE TABLE dbo.result ( id int not null identity, result varchar(400))
Then each row returned from your stored procedure will be a separate row in
dbo.result. You can then cursor through the table and get each row.
INSERT INTO dbo.result (result)
EXEC dbo.sproc_name
DECLARE @.Body1 varchar(4000)
DECLARE @.lineresult varchar(400)
SET @.Body1 = ''
DECLARE r_cursor CURSOR FOR
SELECT result FROM dbo.result ORDER BY id
OPEN r_cursor
FETCH NEXT FROM r_cursor INTO @.Body1
WHILE @.@.FETCH_STATUS=0
BEGIN
FETCH NEXT FROM r_cursor INTO @.lineresult
SET @.Body1 = @.Body1 + CHAR(13) + CHAR(10) + @.lineresult
END
CLOSE r_cursor
DEALLOCATE r_cursor
John
"Steve" wrote:
> Thanks in advance - Steve.
> I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to preclude
> using SQL Mail (as far as I know). So I've been working on using CDOSYSmail
> to send email, and attachment, and the results from a stored procedure (a
> set of queries that generates 6 summary reports with varying columns). I've
> inserted the results from the stored procedure into a table called, results
> varchar(4000).
> I'm trying to insert the results from 'sproc_name' into the body of an email
> and could use some help.
> The stored procedure, 'usp_send_cdosysmail' is working fine except I can't
> get the results from another stored procedure into the body of an email. The
> table 'result' is one column varchar(4000); I'd prefer just executing the
> 'sproc_name' and avoid the cursor since i'm new to cursors. I receive the
> error below:
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: incorrect syntax near @.body1
> declare @.Body1 varchar(4000)
> set @.Body1 = ''
> declare r_cursor cursor for
> select result from test.dbo.result
> open r_cursor
> declare @.r_return cursor
> exec db_name.dbo.sproc_name @.r_return = OUTPUT
>
> while (@.@.fetch_status=0)
> begin
> fetch next from @.r_return
> end
> close r_cursor
> @.body1 = @.r_return
> deallocate r_cursor
> exec master..usp_send_cdosysmail
> @.from='user1@.domain.com',
> @.to ='user2@.domain.com',
> @.subject ='Test',
> @.body = @.body1,
> @.attachments = 'C:\test.txt',
> @.smtpserver = 'mailserver.domain.com',
> @.bodytype ='TEXTBody' -- 'HTMLBody'
>
>|||Hi John - big thanks!
I did not have an id property, only a column entitled results with datatype
varchar(4000).
Cursors need an id column (i'm familiar w/ the identity property - very
handy).
For my education, wow do the char(13) an char(10) work inconjunction with
@.body1 and @.lineresult to concatenate all the rows together? I interpret
@.body1 as being all the previous rows and @.lineresult being the current row,
but don't understand how char(13) and char(10) impact the query?
Thanks!
Steve
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:81EFD80A-10CE-4308-B710-7ABB7E79FD53@.microsoft.com...
> Hi Steve
> Assuming your result table is declared as something like:
> CREATE TABLE dbo.result ( id int not null identity, result varchar(400))
> Then each row returned from your stored procedure will be a separate row
in
> dbo.result. You can then cursor through the table and get each row.
> INSERT INTO dbo.result (result)
> EXEC dbo.sproc_name
> DECLARE @.Body1 varchar(4000)
> DECLARE @.lineresult varchar(400)
> SET @.Body1 = ''
> DECLARE r_cursor CURSOR FOR
> SELECT result FROM dbo.result ORDER BY id
> OPEN r_cursor
> FETCH NEXT FROM r_cursor INTO @.Body1
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> FETCH NEXT FROM r_cursor INTO @.lineresult
> SET @.Body1 = @.Body1 + CHAR(13) + CHAR(10) + @.lineresult
> END
> CLOSE r_cursor
> DEALLOCATE r_cursor
> John
> "Steve" wrote:
> > Thanks in advance - Steve.
> >
> > I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to
preclude
> > using SQL Mail (as far as I know). So I've been working on using
CDOSYSmail
> > to send email, and attachment, and the results from a stored procedure
(a
> > set of queries that generates 6 summary reports with varying columns).
I've
> > inserted the results from the stored procedure into a table called,
results
> > varchar(4000).
> >
> > I'm trying to insert the results from 'sproc_name' into the body of an
email
> > and could use some help.
> >
> > The stored procedure, 'usp_send_cdosysmail' is working fine except I
can't
> > get the results from another stored procedure into the body of an email.
The
> > table 'result' is one column varchar(4000); I'd prefer just executing
the
> > 'sproc_name' and avoid the cursor since i'm new to cursors. I receive
the
> > error below:
> >
> > Server: Msg 170, Level 15, State 1, Line 17
> > Line 17: incorrect syntax near @.body1
> >
> > declare @.Body1 varchar(4000)
> > set @.Body1 = ''
> > declare r_cursor cursor for
> > select result from test.dbo.result
> > open r_cursor
> >
> > declare @.r_return cursor
> > exec db_name.dbo.sproc_name @.r_return = OUTPUT
> >
> >
> > while (@.@.fetch_status=0)
> > begin
> > fetch next from @.r_return
> > end
> > close r_cursor
> > @.body1 = @.r_return
> > deallocate r_cursor
> >
> > exec master..usp_send_cdosysmail
> > @.from='user1@.domain.com',
> > @.to ='user2@.domain.com',
> > @.subject ='Test',
> > @.body = @.body1,
> > @.attachments = 'C:\test.txt',
> > @.smtpserver = 'mailserver.domain.com',
> > @.bodytype ='TEXTBody' -- 'HTMLBody'
> >
> >
> >|||Hi
The id column will be necessary if you want to keep the order the same as
the order returned by the stored procedure. The CHAR(13) and CHAR(10) are to
add a carriage return and linefeed into your results, they have no impact on
the query as they are not part of it. If you don't need them then they can be
omitted, but then the columns may not align.
@.Body1 was the variable you used for the email body, if you return more than
one line then the cursor will loop through subsequent lines by returning them
in @.lineresult and then appending them to @.Body1. The first line returned can
go straight into @.Body1.
I am not sure how big the body of your text could be, but if it is 4000
characters it may not be large enough for your needs. You may want to look at
creating a DTS package to create the files and (say) using XPSMTP to send it
http://www.sqldev.net/xp/xpsmtp.htm. It may be better to put the results into
a spreadsheet rather than leaving them as text, DTS can do this. See books
online for more information on DTS and check out
http://www.sqldts.com/default.aspx
John
"Steve" wrote:
> Hi John - big thanks!
> I did not have an id property, only a column entitled results with datatype
> varchar(4000).
> Cursors need an id column (i'm familiar w/ the identity property - very
> handy).
> For my education, wow do the char(13) an char(10) work inconjunction with
> @.body1 and @.lineresult to concatenate all the rows together? I interpret
> @.body1 as being all the previous rows and @.lineresult being the current row,
> but don't understand how char(13) and char(10) impact the query?
> Thanks!
> Steve
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81EFD80A-10CE-4308-B710-7ABB7E79FD53@.microsoft.com...
> > Hi Steve
> >
> > Assuming your result table is declared as something like:
> > CREATE TABLE dbo.result ( id int not null identity, result varchar(400))
> >
> > Then each row returned from your stored procedure will be a separate row
> in
> > dbo.result. You can then cursor through the table and get each row.
> >
> > INSERT INTO dbo.result (result)
> > EXEC dbo.sproc_name
> >
> > DECLARE @.Body1 varchar(4000)
> > DECLARE @.lineresult varchar(400)
> > SET @.Body1 = ''
> >
> > DECLARE r_cursor CURSOR FOR
> > SELECT result FROM dbo.result ORDER BY id
> >
> > OPEN r_cursor
> >
> > FETCH NEXT FROM r_cursor INTO @.Body1
> >
> > WHILE @.@.FETCH_STATUS=0
> > BEGIN
> > FETCH NEXT FROM r_cursor INTO @.lineresult
> > SET @.Body1 = @.Body1 + CHAR(13) + CHAR(10) + @.lineresult
> > END
> > CLOSE r_cursor
> > DEALLOCATE r_cursor
> >
> > John
> >
> > "Steve" wrote:
> >
> > > Thanks in advance - Steve.
> > >
> > > I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to
> preclude
> > > using SQL Mail (as far as I know). So I've been working on using
> CDOSYSmail
> > > to send email, and attachment, and the results from a stored procedure
> (a
> > > set of queries that generates 6 summary reports with varying columns).
> I've
> > > inserted the results from the stored procedure into a table called,
> results
> > > varchar(4000).
> > >
> > > I'm trying to insert the results from 'sproc_name' into the body of an
> email
> > > and could use some help.
> > >
> > > The stored procedure, 'usp_send_cdosysmail' is working fine except I
> can't
> > > get the results from another stored procedure into the body of an email.
> The
> > > table 'result' is one column varchar(4000); I'd prefer just executing
> the
> > > 'sproc_name' and avoid the cursor since i'm new to cursors. I receive
> the
> > > error below:
> > >
> > > Server: Msg 170, Level 15, State 1, Line 17
> > > Line 17: incorrect syntax near @.body1
> > >
> > > declare @.Body1 varchar(4000)
> > > set @.Body1 = ''
> > > declare r_cursor cursor for
> > > select result from test.dbo.result
> > > open r_cursor
> > >
> > > declare @.r_return cursor
> > > exec db_name.dbo.sproc_name @.r_return = OUTPUT
> > >
> > >
> > > while (@.@.fetch_status=0)
> > > begin
> > > fetch next from @.r_return
> > > end
> > > close r_cursor
> > > @.body1 = @.r_return
> > > deallocate r_cursor
> > >
> > > exec master..usp_send_cdosysmail
> > > @.from='user1@.domain.com',
> > > @.to ='user2@.domain.com',
> > > @.subject ='Test',
> > > @.body = @.body1,
> > > @.attachments = 'C:\test.txt',
> > > @.smtpserver = 'mailserver.domain.com',
> > > @.bodytype ='TEXTBody' -- 'HTMLBody'
> > >
> > >
> > >
>
>

Adding processors after the fact

Hello all,
When installing additional processor(s) into a Win Server 2003 w/SQL Server 2005 box after it is up and running, does the OS & SQL
Server need to be reinstalled, or will they automatically recognize the new processor(s)?
Thanks for any help anyone can provide,
Conan Kelly
Windows will automatically pick up the new CPUs, and SQL Server will find
whatever the OS has.
Now licensing, that is a different issue. Nothing automatic there. You must
license the new CPUs if necessary -depending upon version.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Conan Kelly" <CTBarbarin@.msn.com> wrote in message
news:cN%bh.403881$QZ1.187976@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> When installing additional processor(s) into a Win Server 2003 w/SQL
> Server 2005 box after it is up and running, does the OS & SQL Server need
> to be reinstalled, or will they automatically recognize the new
> processor(s)?
> --
> Thanks for any help anyone can provide,
> Conan Kelly
>

Adding processors after the fact

Hello all,
When installing additional processor(s) into a Win Server 2003 w/SQL Server 2005 box after it is up and running, does the OS & SQL
Server need to be reinstalled, or will they automatically recognize the new processor(s)?
--
Thanks for any help anyone can provide,
Conan KellyWindows will automatically pick up the new CPUs, and SQL Server will find
whatever the OS has.
Now licensing, that is a different issue. Nothing automatic there. You must
license the new CPUs if necessary -depending upon version.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Conan Kelly" <CTBarbarin@.msn.com> wrote in message
news:cN%bh.403881$QZ1.187976@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> When installing additional processor(s) into a Win Server 2003 w/SQL
> Server 2005 box after it is up and running, does the OS & SQL Server need
> to be reinstalled, or will they automatically recognize the new
> processor(s)?
> --
> Thanks for any help anyone can provide,
> Conan Kelly
>

Adding processors after the fact

Hello all,
When installing additional processor(s) into a Win Server 2003 w/SQL Server
2005 box after it is up and running, does the OS & SQL
Server need to be reinstalled, or will they automatically recognize the new
processor(s)?
Thanks for any help anyone can provide,
Conan KellyWindows will automatically pick up the new CPUs, and SQL Server will find
whatever the OS has.
Now licensing, that is a different issue. Nothing automatic there. You must
license the new CPUs if necessary -depending upon version.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Conan Kelly" <CTBarbarin@.msn.com> wrote in message
news:cN%bh.403881$QZ1.187976@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> When installing additional processor(s) into a Win Server 2003 w/SQL
> Server 2005 box after it is up and running, does the OS & SQL Server need
> to be reinstalled, or will they automatically recognize the new
> processor(s)?
> --
> Thanks for any help anyone can provide,
> Conan Kelly
>

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?
Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>
|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
adding[vbcol=seagreen]
|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>
|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to addi
ng
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>
adding[vbcol=seagreen]|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask a
nd Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is
AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask
and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there i
s AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
> > We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> > The machine currently has 4 procs and 32GB physical memory.
> > We will be going to 8 procs and 64GB.
> > I'm pretty sure that the memory add is not big deal.
> >
> > My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
adding
> > 4 more procs"?
> >
> >
> >|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
>> The machine currently has 4 procs and 32GB physical memory.
>> We will be going to 8 procs and 64GB.
>> I'm pretty sure that the memory add is not big deal.
>> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
>> 4 more procs"?
>>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:
> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> > Hi
> >
> > On startup, when the OS detects the additional CPU's it uses them
> > automatically. The same goes for SQL Server.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "frankm" wrote:
> >
> >> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> >> The machine currently has 4 procs and 32GB physical memory.
> >> We will be going to 8 procs and 64GB.
> >> I'm pretty sure that the memory add is not big deal.
> >>
> >> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> >> 4 more procs"?
> >>
> >>
> >>
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Thursday, March 8, 2012

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
>> I am running an upgrade to an existing system which I have found is using
> the
>> Alter Table statement to add a Not Null with Default column to huge table
>> (100 million records). Not surprisingly it is taking rather a long time!
>> Can anyone give me any other options to perform this operation ?
>> I'm currently thinking about
>> 1) Adding the column Nullable
>> 2) Running multiple updates to the table
>> 3) Altering the column to make it Not-Nullable.
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.
I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>
|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>
|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')
|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?
|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using th
e
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

adding new volume and swapping drive letters....

Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D

. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?

The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.

Chris
I would use the attach / detach method, would be the best solution for me in this case.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||As you indicated, the drive rename method may be less effort. As long as you are using the Disk Management applet -and not just mapping drives, it should work just fine.

Tuesday, March 20, 2012

Adding table/article w/o starting snapshot!

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

Adding Table to Dataset for SQL Server Mobile causes VS 2005 to lock up.

Monday, March 19, 2012

Adding staggered running total and average to query

Sunday, March 11, 2012

Adding Reporting Services to an existing SP1 install

Adding Report Filter on Float field

adding query results to email body using CDOSYSmail

adding query results to email body using CDOSYSmail

Adding processors after the fact

Adding processors after the fact

Adding processors after the fact

Adding physical CPU's to a server running SQL Server 2000

Adding physical CPU's to a server running SQL Server 2000

Adding physical CPU's to a server running SQL Server 2000

Thursday, March 8, 2012

Adding Not Null with Default column to large Table

Adding Not Null with Default column to large Table

Adding Not Null with Default column to large Table

adding new volume and swapping drive letters....

Sql Activity log backup

Blog Archive

About Me