Preventing Deadlock: following

Showing posts with label following. Show all posts

Monday, March 26, 2012

Primary Keys and Transactional Repl

I have read through the materials and still don't feel like I have a
definitive answer to the following: does (one way) transactional replication
require a primary key on all articles/tables?
THx.
Abosolutely!
Hilary
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:BB9CFBC7-A26E-4152-978E-AE4BA56F47A4@.microsoft.com...
>I have read through the materials and still don't feel like I have a
> definitive answer to the following: does (one way) transactional
> replication
> require a primary key on all articles/tables?
> THx.
|||ANY transactional replication(1 way, immediate,queued) requires PK's

Friday, March 23, 2012

Primary key vs Clustered Index with respect to Replication.

Is the following statement is TRUE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:

> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.

Primary Key Violation

Hi Everyone
I am occasionally getting the following error ......
Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplicat
e key in object 'TABLE1'
The sProc segment that is causing this error is ....
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field2 = field2 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
----
Where field1 and field2 is the composite primary key.
Any ideas how to modify my sProc to stop the primary key violation happening
Cheers
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try,
IF EXISTS (SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
if exists(SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2
+ 1)
print 'tell us what to do in this case.'
else
UPDATE
TABLE1
SET
field2 = field2 + 1
WHERE
field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
AMB
"Peter" wrote:

> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplic
ate key in object 'TABLE1'
> The sProc segment that is causing this error is ....
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field
2)
> UPDATE TABLE1
> SET field2 = field2 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
> ----
-
> Where field1 and field2 is the composite primary key.
> Any ideas how to modify my sProc to stop the primary key violation happeni
ng
> Cheers
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||"Peter" <peter@.dwstech.com> wrote in message news:427c1f0e$1_2@.127.0.0.1...
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert
> duplicate key in object 'TABLE1'
You need to check if the row referenced by (@.field1, @.field2 + 1) exists
before you try to UPDATE. For instance, consider the following:
Field1 | Field2
100 | 100
100 | 101
In this instance if @.field1 = 100 and @.field2 = 100, the following will
cause a PK violation:
UPDATE TABLE1
SET field2 = field2 + 1
WHERE field1 = @.field1
AND field2 = @.field2
What exactly are you trying to accomplish? Maybe someone can help with the
logic, if you can supply more info...|||Oooops, my bad
The sProc code shouls have read ...
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
----
The object of the table is to act as a simple counter. If the primary key al
ready exists in the table then update the counter column (field3) by increme
nting by 1. If the primary key does not exist then insert the primary key an
d set the counter column to
1.
Sorry for the confusion.
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Are you still getting the error? If so then I would guess this to be a
concurrency issue. How busy is this database? Is it likely that two
processes would cause this to occur? If so, there are two things you can
do:
--easy, no other changes to you system possiblilty
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--we don't want anyone to touch the row:
BEGIN TRANSACTION
--lock it exclusively so no one else can read it
IF EXISTS (SELECT 1 FROM TABLE1 (xlock) WHERE field1 = @.field1 AND field2 =
@.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--alternately, change this from a counter into a very thin table:
create table counter
(
field1 int --needs a different name
counter bigint identity,
actiontime datetime,
primary key (field1, counter)
)
Then just insert rows into this table and use aggregates. You can store
more information about each activity, if you want to get a richer set of
information. This method remove all contention on insert other than the
picking of the next counter value, and that is extremely fast.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427c3742$1_2@.127.0.0.1...
> Oooops, my bad
> The sProc code shouls have read ...
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 =
> @.field2)
> UPDATE TABLE1
> SET field3 = field3 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
> ----
-
> The object of the table is to act as a simple counter. If the primary key
> already exists in the table then update the counter column (field3) by
> incrementing by 1. If the primary key does not exist then insert the
> primary key and set the counter column to 1.
> Sorry for the confusion.
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Louis & Michael
Thank you both for your replies.
I will expand a little further on the purpose of this table.
The table is for counting how many times any particular url on a website is
clicked. The primary key fields of the table are click_hour and url_id. url_
id is passed to the sProc and click_hour is calculated within the sProc as d
atediff(hour,0,getutcdate()
). If an entry exists within the table for the particular click_hour and url
_id, then the click_count field is incremented by 1, else an entry is added
to the table for that click_hour and url_id with the click_count field set w
ith an initial value of 1.
I have chosen this method as only one row needs to exist in the table for ea
ch url for each hour regardless of the number of clicks ... thus if one url
is clicked 1000 times each hour for 24 hours there is only 24 rows in the ta
ble as opposed to 24,000.
This sProc can be called anywhere up to 50,000 times a day so the database i
s reasonably busy.
Michael, the suggestion you gave reverts back to the one entry per click sce
nario which I want to avoid as does the second suggestion offered by Louis.
Therefore, unless anyone can come up with a better suggestion, I am looking
at implementing Louis' first suggestion. Louis, I am just wondering what per
formance impact XLOCK will have on the sProc. Also, is XLOCK a better option
than TABLOCK and if so cou
ld you please explain why.
Thanks again to both of you for taking the time to reply.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||sorry for the slow reply. I have been way busy of late:
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first
>suggestion. Louis, I am just wondering what performance impact XLOCK will
>have on the sProc. Also, is >XLOCK a better option than TABLOCK and if so
>could you please explain why.
>
The important thing difference between xlock and tablock is that one takes a
type of lock, the other locks a certain type of resource. You want SQL
Server to take a lock that means no one else can even look at it., but we
only want it to lock the row we are concerned with, not the entire table.
The one entry per click solution is the best solution because no contention.
Make a view of the data to give you your count, and even update your column
once a day and add these rows to count of the newly inserted ones, but as
long as you don't have too many people trying to update the same row, the
fact that you are single threading acess to the row in TABLE1 should not be
too concerning.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427df11b$1_1@.127.0.0.1...
> Louis & Michael
> Thank you both for your replies.
> I will expand a little further on the purpose of this table.
> The table is for counting how many times any particular url on a website
> is clicked. The primary key fields of the table are click_hour and url_id.
> url_id is passed to the sProc and click_hour is calculated within the
> sProc as datediff(hour,0,getutcdate()). If an entry exists within the
> table for the particular click_hour and url_id, then the click_count field
> is incremented by 1, else an entry is added to the table for that
> click_hour and url_id with the click_count field set with an initial value
> of 1.
> I have chosen this method as only one row needs to exist in the table for
> each url for each hour regardless of the number of clicks ... thus if one
> url is clicked 1000 times each hour for 24 hours there is only 24 rows in
> the table as opposed to 24,000.
> This sProc can be called anywhere up to 50,000 times a day so the database
> is reasonably busy.
> Michael, the suggestion you gave reverts back to the one entry per click
> scenario which I want to avoid as does the second suggestion offered by
> Louis.
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first suggestion. Louis, I am just
> wondering what performance impact XLOCK will have on the sProc. Also, is
> XLOCK a better option than TABLOCK and if so could you please explain why.
> Thanks again to both of you for taking the time to reply.
> Regards
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--

Wednesday, March 21, 2012

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

If the code lists dupes, you will have to clean your data in table t2 before you insert it into t1. The bottom line, you have to guarantee the data from t2 is unique before you commit inserting into t1 - this involves either deleting the duped data or only selecting a row for each name. Else, you will get the primary constraint violation. This is by design.

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Here is some sample data from the source table that demonstrates the problem (non-black lines indicates duplicated rows). Note that the DateTime value is duplicated but the other fields contain different values:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:22:00,502.90,502.90,502.90,502.90
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:26:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:28:00,498.40,498.40,498.40,498.40
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30
2005-11-28 18:39:00,498.10,498.10,498.00,498.00

Desired results:

2005-11-28 18:21:00,498.70,498.70,498.70,498.70
2005-11-28 18:22:00,498.50,498.50,498.50,498.50
2005-11-28 18:23:00,498.40,498.40,498.40,498.40
2005-11-28 18:26:00,498.30,498.30,498.30,498.30
2005-11-28 18:28:00,502.70,502.70,502.70,502.70
2005-11-28 18:30:00,502.60,502.60,502.60,502.60
2005-11-28 18:31:00,498.30,498.30,498.30,498.30
2005-11-28 18:32:00,502.60,502.60,502.60,502.60
2005-11-28 18:33:00,502.60,502.60,502.60,502.60
2005-11-28 18:34:00,502.60,502.60,502.60,502.60
2005-11-28 18:36:00,502.50,502.50,502.50,502.50
2005-11-28 18:39:00,502.40,502.40,502.30,502.30

Any and all help appreciated.

|||

Here you go.

Code Snippet

create table #tmp([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)
go
create table #tmp2([DateTime] [datetime] NOT NULL,
[Field1] [decimal](10, 2) NULL,
[Field2] [decimal](10, 2) NULL,
[Field3] [decimal](10, 2) NULL,
[Field4] [decimal](10, 2) NULL)

go
insert #tmp
select '2005-11-28 18:21:00',498.70,498.70,498.70,498.70
union all select '2005-11-28 18:22:00',498.50,498.50,498.50,498.50
union all select '2005-11-28 18:22:00',502.90,502.90,502.90,502.90
union all select '2005-11-28 18:23:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:26:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:26:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',502.70,502.70,502.70,502.70
union all select '2005-11-28 18:28:00',498.40,498.40,498.40,498.40
union all select '2005-11-28 18:30:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:31:00',498.30,498.30,498.30,498.30
union all select '2005-11-28 18:32:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:33:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:34:00',502.60,502.60,502.60,502.60
union all select '2005-11-28 18:36:00',502.50,502.50,502.50,502.50
union all select '2005-11-28 18:39:00',502.40,502.40,502.30,502.30
union all select '2005-11-28 18:39:00',498.10,498.10,498.00,498.00
go
;with cte
as
(select *,
row_number() over(partition by [datetime] order by [datetime] ) r
from #tmp
)
insert #tmp2
select [Datetime],Field1,Field2,Field3,Field4
from cte
where r=1
and not exists(select 1 from #tmp2 t2 where t2.[Datetime]=cte.[Datetime])
go
select * from #tmp2
go
drop table #tmp, #tmp2

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

Since we partition by datetime, order by datetime again will force the engine to generate the rownumber based on the logical order of the rows which we then select only the first row. Essentially, it is equivalent to "select top 1 * from tb" - this is what was asked by the OP as the desired result.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

Just as a summary, my original intention was to insert records into a new or existing table without including duplicate DateTime (primary key) values. If that's not possible, I would like to remove records in the source table which contain duplicate DateTime values.

Since the fields are not likely to contain exactly the same values in the duplicated records, DISTINCT won't work. Only the DateTime values are duplicated, inserting only the first occurrence of a duplicated DateTime would be acceptable. Or, alternatively, deleting subsequent duplications in the source table.

|||

Below is what I have done to resolve this problem. Add a primary key ID to the source table to aid in identification of duplicate DateTime's. Then delete duplicates. After that I can insert into a new or existing table.

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Desired results:

Any and all help appreciated.

|||

Here you go.

Code Snippet

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

|||

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

primary key error with INSERT INTO

Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?

INSERT INTO [destSchema].[destTable]

SELECT t2.*

FROM [srcSchema].[srcTable] t2

LEFT JOIN [destSchema].[destTable] t1

ON t2.[DateTime] = t1.[DateTime]

WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])

ORDER BY t1.[DateTime];

Is [destTable].[DateTime] the primary key?

Code Snippet

INSERT INTO [destSchema].[destTable]

SELECT

t2.*

FROM

[srcSchema].[srcTable] t2

LEFT OUTER JOIN

[destSchema].[destTable] t1

t2.[DateTime] = t1.[DateTime]

WHERE

t1.[DateTime] IS NULL

|||

Yes

|||

The dupe data can be coming from t2. So, you will have to decide what you want to insert into t1.

This query will give you a list of dupe dates.

Code Snippet

select t2.[DateTime]

from [srcSchema].[srcTable] t2

where not exists(select 1 from [destSchema].[destTable] t1 where t2.[DateTime] = t1.[DateTime])

group by t2.[DateTime]

having count(*)>1

|||

The code to list dupe dates works great. However, the other code generates the same primary key error that I′ve been getting all along:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_destTable'. Cannot insert duplicate key in object 'destSchema.destTable'.

The statement has been terminated.

|||

Only you know your data, you will have to make the choice of what to insert into t1. If you post DDL + sample data + expected result here, we might be able to offer a solution.

|||

The following are sample fields in the source table, actual field names vary depending on the source but they all contain DateTime (Field0):

[DateTime] [datetime] NOT NULL,

[Field1] [decimal](10, 2) NULL,

[Field2] [decimal](10, 2) NULL,

[Field3] [decimal](10, 2) NULL,

[Field4] [decimal](10, 2) NULL

Desired results:

Any and all help appreciated.

|||

Here you go.

Code Snippet

|||

With mycte

(SELECT myDatatime, f1, f2, f3, f4 FROM

(SELECT myDatatime, f1, f2, f3, f4, ROW_NUMBER() OVER(partition by myDatatime ORDER BY f1) as RowNum

FROM dupDateTimedata) t

WHERE RowNum=1)

SELECT * INTO dupDateTimedataRemoved

FROM mycte

|||

limno, "order by f1" will not give you the "top 1"...i.e. you will get this instead of the desired row.

2005-11-28 18:39:00.000 498.10 498.10 498.00 498.00

|||

Thanks oj for pointing this out. The problem is even with order by [datetime], we may still not get the right result.

We may need a little more clarification from rwbta to confirm your result.

My intention was by using Partion by datetime then I will keep the samllest number for f1 within the same datetime rows.

|||

This certainly turned out more complicated than I imagined. What additional information is needed?

|||

Add PK ID:

ALTER TABLE srcSchema.srcTable

ADD

DataID int NOT NULL IDENTITY(1, 1),

CONSTRAINT PK_srcTable PRIMARY KEY(DataID)

Delete Duplicates:

DELETE FROM

FROM

srcSchema.srcTable t1

INNER JOIN

(

SELECT

MIN(DataID) AS DataID,

[DateTime]

FROM

srcSchema.srcTable

GROUP BY

[DateTime]

HAVING

COUNT(*) > 1

) t2

ON(

t1.[DateTime] = t2.[DateTime]

AND

t1.DataID <> t2.DataID

)

Tuesday, March 20, 2012

Primary Key Conflict Resolution

Hello,

I'm running into problems with my replication where I get the following error:
Violation of PRIMARY KEY constraint

I know what the error means, and I know what is causing it. In my case, a property is being added to an inventory item independently at the publisher and subscriber end. Each available property has a particular ID, each inventory item has its own unique ID, and of course the properties per inventory item are stored in a linking table. I am using UUIDs for the inventory items, to avoid collisions in that aspect, but the list of properties is fixed (currently only 15 - 20 available properties), so it doesn't make sense to me to have managed ranges, UUIDs, or other such things for the properties. Of course, I could apply a "source ID" to each added property to avoid these collisions, but I'd prefer not having to redesign the database, not to mention deal with the extraneous copies of properties.

My preference would be to simply have the server delete the copy on the server and take the subscriber row. I would have thought that using the "subscriber always wins" conflict resolver would have this effect, but it doesn't work for me. Is there a straightforward way of dealing with this problem? Am I missing something obvious? I've looked into a custom conflict resolver, but that seems like overkill for what must be a fairly common scenario.

For the record, the publisher in my case is SQL Server 2005, and the subscribers are SQL Server Mobile clients.

Any advice would be greatly appreciated!

Thanks,
Adrien.

Adrien,

In your case, "subscriber always wins" conflict resolver won't work for you because although you have inserted at both publisher and subscriber, they are treated as different rows with different rowguid. I think what you can do is just ignore that failure since your subscriber row will be rolled back if you set @.compensate_for_errors='true'. Or you need to make sure only one side insert into property table, and make sure the other side gets it, then both side can insert into the linking table which refers to the property table.

Hope it helps

Wanwen

|||Wanwen,

This "compensate_for_errors" property does exactly what I needed. I've tested causing PK collisions on purpose, and it seems to do more or less what I'd expect, and gets rid of the errors.

Thanks!

Adrien.

Primary Key and Table Design Question

What would be a reasonable primary key to use in the following scenario?
I need to provide new functionality to an existing Web site. This new
functionality will be questionnaires (containing from 3 to 35 questions
each). Visitors to the site will open a questionnaire, answer the
questions, then click a "Submit" button. When the page is submitted to the
Web server, responses will need to be saved to the database (SQL 2K). Users
will not be logged in. For the sake of this question, please assume we have
dealt elsewhere with the issue of individual users submitting the same
survey multiple times (and other such issues not directly related to the
table design required to support this new functionality). Administrative
pages will enable the site's administrators to (1) define new Surveys, (2)
create new questions for each survey, and (3) retrieve and review responses
to existing surveys.
Three obvious entities are apparent to me: "Surveys", "Survey Questions" and
"Survey Response Sets"
"Surveys" would have a corresponding table that describes each survey
(title, subject, start_date, end_date, etc).
"Survey Questions" would have a corresponding table that holds things like
question_text, presentation_sequence, etc.
"Survey Response Sets" would have a corresponding table that holds responses
to each question.
I see one-to-many relationship from Surveys to SurveyQuestions, and from
SurveyQuestions to SurveyRespons Sets.
Given this scenario, what would you use as the primary key for each of these
tables? In a former life I would have used an IDENTITY property for each
table - but I've painfuly realized the downsides of going that route. So,
now that I'm trying to get away from IDENTITY, I'm wondering what would make
sense for my scenario. There isn't any standardized or well-known/industry
standard for Survey IDs, nor Question IDs, nor Survey Response Set IDs. Nor
is there any legacy system I'm converting from that already has the PK for
me to use.
Thanks!What form do the responses take? Multiple choice? Free form text? Or
something else? As you aren't recording names it would seem a bit strange to
allow entirely free format responses (there's probably little you can do to
analyze such data in the database anyway) but you haven't mentioned any
other scheme. As you aren't identifying the individual users I assume you
are only interested in the total number of times each reply is given, hence
the "response_tally" column in the following first-guess at a logical
design:
CREATE TABLE surveys (survey_no INTEGER PRIMARY KEY, survey_title
VARCHAR(50) NOT NULL UNIQUE, survey_subject VARCHAR(50) NOT NULL, start_date
DATETIME NOT NULL, end_date DATETIME NOT NULL, CHECK (start_date<=end_date))
CREATE TABLE survey_questions (survey_no INTEGER NOT NULL REFERENCES surveys
(survey_no), sequence INTEGER NOT NULL CHECK (sequence>0), question_text
VARCHAR(255) NOT NULL, PRIMARY KEY (survey_no, sequence))
CREATE TABLE survey_responses (survey_no INTEGER NOT NULL, sequence INTEGER
NOT NULL, FOREIGN KEY (survey_no, sequence) REFERENCES survey_questions
(survey_no, sequence), response_text VARCHAR(50) NOT NULL /* constraints ?
*/, response_tally INTEGER NOT NULL /* number of times this answer was given
*/, PRIMARY KEY (survey_no, sequence, response_text))
David Portas
SQL Server MVP
--|||Thank you so much David for your response. I understand that I didn't give a
whole lot about the project's overall objectives... That's a judgement call
I made based on my wanting, most particularly, to learn alternative ways to
implement a primary key that is as something other than an IDENTITY property
(in cases where I don't want to use a natural key). I didn't want a natural
key here because the question_text column, which would be a candidate, not
only will be a varchar, but it may be quite long in some cases.
So, your response shows me something I'd be comfortable using - as integers
are used in the primary key. Now, continuing with your DDL, from where would
I get the actual integer values to use for [survey_no]? I have seen some of
you experts recommend a "numbers table" Would that be appropriate in this
scenario?
FWIW, these "surveys" are really not very static. It's not like we can say
that they all will take a specific format, have a pre-determined number of
questions, each of which is of any certain data type. For a sample of the
sort of thing we're implementing, you can look at this one:
http://www.jaguarwoman.com/order.html What we want to do is present a form
to the site's visitor, control to the best extent we can the number of times
a given user/visitor can submit the form, and then store the results for
later reporting. Some such forms will be simple info request forms like at
the above URL, others will be actual surveys or questionnaires with Likert
scale-type responses, upon which we'll be performing statistical analyses.
And yes - we are most certainly NOT treating these as anything near
scientific (unless that particular Web site does force login with a valid
ID/password...). Given that these surveys/forms/questionnaires are
potentially so different per customer Web site, I didn't think it would be
useful to post DDL for each possible one - HOWEVER each implementation would
likely involve some variation of the three tables described in the OP, and
for which you provided a "best guess" DDL given that you can't read my mind
: )
Thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:8NSdnSiIaJDU9QPfRVn-pA@.giganews.com...
> What form do the responses take? Multiple choice? Free form text? Or
> something else? As you aren't recording names it would seem a bit strange
> to allow entirely free format responses (there's probably little you can
> do to analyze such data in the database anyway) but you haven't mentioned
> any other scheme. As you aren't identifying the individual users I assume
> you are only interested in the total number of times each reply is given,
> hence the "response_tally" column in the following first-guess at a
> logical design:
> CREATE TABLE surveys (survey_no INTEGER PRIMARY KEY, survey_title
> VARCHAR(50) NOT NULL UNIQUE, survey_subject VARCHAR(50) NOT NULL,
> start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, CHECK
> (start_date<=end_date))
> CREATE TABLE survey_questions (survey_no INTEGER NOT NULL REFERENCES
> surveys (survey_no), sequence INTEGER NOT NULL CHECK (sequence>0),
> question_text VARCHAR(255) NOT NULL, PRIMARY KEY (survey_no, sequence))
> CREATE TABLE survey_responses (survey_no INTEGER NOT NULL, sequence
> INTEGER NOT NULL, FOREIGN KEY (survey_no, sequence) REFERENCES
> survey_questions (survey_no, sequence), response_text VARCHAR(50) NOT NULL
> /* constraints ? */, response_tally INTEGER NOT NULL /* number of times
> this answer was given */, PRIMARY KEY (survey_no, sequence,
> response_text))
> --
> David Portas
> SQL Server MVP
> --
>|||I've worked with a design that uses two procedures: GetNextSurrogateKey and
GetNextBlockSurrogateKey. The first reserves the next key value and returns
it in an output parameter. The second reserves a specified number of key
values and returns the first key value in an output parameter. The next key
value for each table is stored in a table with one record per Surrogate Key
table. GetNextSurrogateKey increments the next key value field. The proble
m
with this approach is two-fold: (1) it increases the probability of deadlock
s
and (2) it reduces concurrency. Calls to GetNextSurrogateKey must occur in
the same order in every transaction--in other words, you have to get the nex
t
key for TableA before getting the next key for TableB in each transaction
that occurs against the database. Even if you use the WITH ROWLOCK hint, th
e
optimizer may escalate to a PAGE LOCK, which effectively blocks inserts into
tables whose SurrogateKey record resides on that page. This can lead to
deadlocks which are really hard to debug, or at a minimum waiting for record
s
locked by another process. The implementation I saw padded the records so
that they were stored one per page in a logically flawed attempt to get
around this.
I prefer to use IDENTITY columns to avoid the above pitfalls. It requires
extra code on the client to obtain the identity value(s), and it's painful
when you're inserting records into related tables en mass, but in my opinion
the benefits outweigh the subsequent maintenance and debugging nightmares
that are sure to ensue.
If you're dead set against using IDENTITY columns, you could write an
extended stored procedure or COM object to implement the above
GetNextSurrogateKey pattern. The xp would execute outside the current
connection, which would prevent the concurrency and deadlock issues describe
d
above. A COM object would scale better, because it would minimize the
overhead associated with initiating a new connection for each call, because
it could maintain a pool of open connections..
"Jeffrey Todd" wrote:

> Thank you so much David for your response. I understand that I didn't give
a
> whole lot about the project's overall objectives... That's a judgement cal
l
> I made based on my wanting, most particularly, to learn alternative ways t
o
> implement a primary key that is as something other than an IDENTITY proper
ty
> (in cases where I don't want to use a natural key). I didn't want a natura
l
> key here because the question_text column, which would be a candidate, not
> only will be a varchar, but it may be quite long in some cases.
> So, your response shows me something I'd be comfortable using - as integer
s
> are used in the primary key. Now, continuing with your DDL, from where wou
ld
> I get the actual integer values to use for [survey_no]? I have seen some o
f
> you experts recommend a "numbers table" Would that be appropriate in this
> scenario?
>
> FWIW, these "surveys" are really not very static. It's not like we can say
> that they all will take a specific format, have a pre-determined number of
> questions, each of which is of any certain data type. For a sample of the
> sort of thing we're implementing, you can look at this one:
> http://www.jaguarwoman.com/order.html What we want to do is present a form
> to the site's visitor, control to the best extent we can the number of tim
es
> a given user/visitor can submit the form, and then store the results for
> later reporting. Some such forms will be simple info request forms like at
> the above URL, others will be actual surveys or questionnaires with Likert
> scale-type responses, upon which we'll be performing statistical analyses.
> And yes - we are most certainly NOT treating these as anything near
> scientific (unless that particular Web site does force login with a valid
> ID/password...). Given that these surveys/forms/questionnaires are
> potentially so different per customer Web site, I didn't think it would be
> useful to post DDL for each possible one - HOWEVER each implementation wou
ld
> likely involve some variation of the three tables described in the OP, and
> for which you provided a "best guess" DDL given that you can't read my min
d
> : )
> Thanks!
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:8NSdnSiIaJDU9QPfRVn-pA@.giganews.com...
>
>|||Thank you Brian for your thoughtful response. It is very helpful to have
that insight before I go off and paint myself into another corner that has
bad problems like the IDENTITY implementation would have.
<<but in my opinion the benefits outweigh the subsequent maintenance and
debugging nightmares that are sure to ensue>>
From the research I have done in my efforts to get away from IDENTITY and do
things "properly", I've discovered that a lot of work will have to be done
regardless of which approach is chosen (IDENTITY vs anything else). There
are substantial problems to be mitigated with intelligent decision-making
with every approach I've seen - even with the highly touted "natural keys"
(cascading updates notwithstanding).
So, I guess for my scenario, if you were the one having to implement it,
you'd go with IDENTITY. Maybe I should reconsider, and go with IDENTITY too.
The thing I hate most about IDENTITY is that everything falls apart when
migrating data from one DB to another, something I want to be able to do
without having to think about changing primary key values.
Anyone else? Thoughts, rants, opinions, and perspective or suggestions on my
particular scenario are greatly appreciated.
-JT
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:48354E54-A332-40B1-A9C8-B5AA78A3843F@.microsoft.com...
> I've worked with a design that uses two procedures: GetNextSurrogateKey
> and
> GetNextBlockSurrogateKey. The first reserves the next key value and
> returns
> it in an output parameter. The second reserves a specified number of key
> values and returns the first key value in an output parameter. The next
> key
> value for each table is stored in a table with one record per Surrogate
> Key
> table. GetNextSurrogateKey increments the next key value field. The
> problem
> with this approach is two-fold: (1) it increases the probability of
> deadlocks
> and (2) it reduces concurrency. Calls to GetNextSurrogateKey must occur
> in
> the same order in every transaction--in other words, you have to get the
> next
> key for TableA before getting the next key for TableB in each transaction
> that occurs against the database. Even if you use the WITH ROWLOCK hint,
> the
> optimizer may escalate to a PAGE LOCK, which effectively blocks inserts
> into
> tables whose SurrogateKey record resides on that page. This can lead to
> deadlocks which are really hard to debug, or at a minimum waiting for
> records
> locked by another process. The implementation I saw padded the records so
> that they were stored one per page in a logically flawed attempt to get
> around this.
> I prefer to use IDENTITY columns to avoid the above pitfalls. It requires
> extra code on the client to obtain the identity value(s), and it's painful
> when you're inserting records into related tables en mass, but in my
> opinion
> the benefits outweigh the subsequent maintenance and debugging nightmares
> that are sure to ensue.
> If you're dead set against using IDENTITY columns, you could write an
> extended stored procedure or COM object to implement the above
> GetNextSurrogateKey pattern. The xp would execute outside the current
> connection, which would prevent the concurrency and deadlock issues
> described
> above. A COM object would scale better, because it would minimize the
> overhead associated with initiating a new connection for each call,
> because
> it could maintain a pool of open connections..
> "Jeffrey Todd" wrote:
>

Friday, March 9, 2012

Primary filegroup full SQL error 42000

(SQL SERVER 2000)

I keep getting the "primary filegroup is full" error when ever I try to write data even though the following things are true:

allow unlimited growth set for BOTH database and transaction log
auto shrink and auto update statistics flags are set in database options
growth set to 10 percent for both
database is 4GB but disk has 12GB freespace
transaction log is on same disk but is only 8MB ?

SQL shows the database has 2% free space but any attempts to write cause the error, shrinking drops this to 1% but still produces the same error on writes

SQL shows the transaction log has 90% free space

backup maintenance plans are in operation for both transaction log and database (write to different drive)

I am completely confused!Google's a wonderful thing

http://support.jodohost.com/showthread.php?t=981

Try bumping up the percent growth

I don't personally like to set to autogrow...the database should be managed to meet particular expectations, you should predicate the growth, and set alerts to imform you when your reaching those limits...

But I betcha that should fix it.|||Changed the growth percentage to 80% restarted SQL server agent shrank database, tried the write operation SAME ERROR! But now refuses to perfrom the transaction log backup but only give a general fail error, yet the drive still has 12GB free space!!!!

For some reason it just won't go beyond the 4GB size, weird, is Windows 2000 Server anything to do with it??|||Any chance you might have installed MSDE on that machine? According to the Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), MSDE has a 2 Gb limit on the data portion of a database that might be causing havok.

-PatP|||If that was MSDE the poster wouldn't have had 4GB worth of data.

primary file group run out of free space??

Hi all
I got the following error massage when tried to delete some 3 GB records
from a 26 GB records table - 'tblName':
'Could not allocate space for object 'tblName' in database 'dbName' because
the primary
filegroup is full'.
At the time of this error there were some 4 GB free disk space on the disk
where the datababse data and log file were.
Also these files were not limitted in grow size.
My questions are:
1) why does a 'delete' operation involves allocation of space for the table
that is being deleted'
Does this operation involves creating image data for the table that is being
deleted, in the transaction log, where the deletion actually occures and
than commited back to the original table'
So if i am deleting data from a 26GB table, there should be this amount of
free space on the disk where the log's file group is located?
2) Where can i see the state of the primary filegroup and how can i increase
its size?
Thanks for your attention
ReaRea
delete is a logged operation , so have you seen the log file during the
deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Perfrom your DELETION (TRUNCATION would be more efficient)
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I got the following error massage when tried to delete some 3 GB records
> from a 26 GB records table - 'tblName':
> 'Could not allocate space for object 'tblName' in database 'dbName'
because
> the primary
> filegroup is full'.
> At the time of this error there were some 4 GB free disk space on the disk
> where the datababse data and log file were.
> Also these files were not limitted in grow size.
> My questions are:
> 1) why does a 'delete' operation involves allocation of space for the
table
> that is being deleted'
> Does this operation involves creating image data for the table that is
being
> deleted, in the transaction log, where the deletion actually occures and
> than commited back to the original table'
> So if i am deleting data from a 26GB table, there should be this amount of
> free space on the disk where the log's file group is located?
> 2) Where can i see the state of the primary filegroup and how can i
increase
> its size?
>
> Thanks for your attention
> Rea
>|||Thanks alot!
So how much disk space should a deletion of 3 GB from a 26GB table consume'
Is there a way to estimate the amount of disk space deletion operations
consume from
the transaction log's disk'
2) in your code below: what is the edvantage of doing deletions this way'
Thanks again
Rea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Rea
> delete is a logged operation , so have you seen the log file during the
> deletion?
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> --Perfrom your DELETION (TRUNCATION would be more efficient)
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > Hi all
> > I got the following error massage when tried to delete some 3 GB records
> > from a 26 GB records table - 'tblName':
> >
> > 'Could not allocate space for object 'tblName' in database 'dbName'
> because
> > the primary
> > filegroup is full'.
> >
> > At the time of this error there were some 4 GB free disk space on the
disk
> > where the datababse data and log file were.
> > Also these files were not limitted in grow size.
> >
> > My questions are:
> > 1) why does a 'delete' operation involves allocation of space for the
> table
> > that is being deleted'
> > Does this operation involves creating image data for the table that is
> being
> > deleted, in the transaction log, where the deletion actually occures and
> > than commited back to the original table'
> > So if i am deleting data from a 26GB table, there should be this amount
of
> > free space on the disk where the log's file group is located?
> >
> > 2) Where can i see the state of the primary filegroup and how can i
> increase
> > its size?
> >
> >
> > Thanks for your attention
> > Rea
> >
> >
>|||Rea
I divide a long/big transaction into a small one.
CHECKPOINT flows a data from transaction log into the disk to remove an
inactive portions (btw you can also perform BACKUP LOG operation)
With that way you don't lock others by running your big deletion and also
keep a log file with an appropriate size.
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:OPOU01AZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks alot!
> So how much disk space should a deletion of 3 GB from a 26GB table
consume'
> Is there a way to estimate the amount of disk space deletion operations
> consume from
> the transaction log's disk'
> 2) in your code below: what is the edvantage of doing deletions this way'
> Thanks again
> Rea
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Rea
> > delete is a logged operation , so have you seen the log file during the
> > deletion?
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > --Perfrom your DELETION (TRUNCATION would be more efficient)
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > > I got the following error massage when tried to delete some 3 GB
records
> > > from a 26 GB records table - 'tblName':
> > >
> > > 'Could not allocate space for object 'tblName' in database 'dbName'
> > because
> > > the primary
> > > filegroup is full'.
> > >
> > > At the time of this error there were some 4 GB free disk space on the
> disk
> > > where the datababse data and log file were.
> > > Also these files were not limitted in grow size.
> > >
> > > My questions are:
> > > 1) why does a 'delete' operation involves allocation of space for the
> > table
> > > that is being deleted'
> > > Does this operation involves creating image data for the table that is
> > being
> > > deleted, in the transaction log, where the deletion actually occures
and
> > > than commited back to the original table'
> > > So if i am deleting data from a 26GB table, there should be this
amount
> of
> > > free space on the disk where the log's file group is located?
> > >
> > > 2) Where can i see the state of the primary filegroup and how can i
> > increase
> > > its size?
> > >
> > >
> > > Thanks for your attention
> > > Rea
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

Previous() function not working with scope parameter.

This works:
=Previous(Fields!Jobs.Value)
The following variants get this error:
"The value expression for the textbox 'textbox17' has an incorrect number of
parameters for the function 'Previous'."
=Previous(Fields!Jobs.Value,"scope")
=Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
The definition found here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
m/rcr_creating_expressions_v1_61f7.asp?frame=true
Previous(Expression, AggFunction, PreviousScope, AggScope)
Ideas'In this particular case, the documentation is ahead of its time. The MSDN
documentation describes the full implementation of the previous aggregate
function in a future release.
Currently, the previous aggregate only support the first argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rick Todd" <rtodd@.spicer.com> wrote in message
news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> This works:
> =Previous(Fields!Jobs.Value)
> The following variants get this error:
> "The value expression for the textbox 'textbox17' has an incorrect number
of
> parameters for the function 'Previous'."
> =Previous(Fields!Jobs.Value,"scope")
> =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> The definition found here
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> m/rcr_creating_expressions_v1_61f7.asp?frame=true
> Previous(Expression, AggFunction, PreviousScope, AggScope)
> Ideas'
>|||For my situation I did a little workaround by testing the previous rows'
(using previous()) group and comparing the current rows' group and taking
action on the original field I was testing with previous() with a few IIF()
statements. Did the trick...
Thanks
"Rick" wrote:
> Thanks for the quick reply. Are there possibilities of this getting fixed
> with some future service pack?
> "Robert Bruckner [MSFT]" wrote:
> > In this particular case, the documentation is ahead of its time. The MSDN
> > documentation describes the full implementation of the previous aggregate
> > function in a future release.
> > Currently, the previous aggregate only support the first argument.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Rick Todd" <rtodd@.spicer.com> wrote in message
> > news:ePUhUkJhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> > > This works:
> > > =Previous(Fields!Jobs.Value)
> > >
> > > The following variants get this error:
> > >
> > > "The value expression for the textbox 'textbox17' has an incorrect number
> > of
> > > parameters for the function 'Previous'."
> > >
> > > =Previous(Fields!Jobs.Value,"scope")
> > >
> > > =Previous(Fields!Jobs.Value,Nothing,"scope",Nothing)
> > >
> > > The definition found here
> > >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/ht
> > > m/rcr_creating_expressions_v1_61f7.asp?frame=true
> > >
> > > Previous(Expression, AggFunction, PreviousScope, AggScope)
> > >
> > > Ideas'
> > >
> > >
> >
> >
> >

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

Previous Year To Date:

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

|||

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Monday, March 26, 2012

Primary Keys and Transactional Repl

Friday, March 23, 2012

Primary key vs Clustered Index with respect to Replication.

Primary Key Violation

Wednesday, March 21, 2012

primary key error with INSERT INTO

primary key error with INSERT INTO

primary key error with INSERT INTO

Tuesday, March 20, 2012

Primary Key Conflict Resolution

Primary Key and Table Design Question

Friday, March 9, 2012

Primary filegroup full SQL error 42000

primary file group run out of free space??

primary file group run out of free space??

Wednesday, March 7, 2012

Previous() function not working with scope parameter.

Previous Month To Date Calculation

Previous Month To Date Calculation

Preventing Deadlock

Blog Archive

About Me