help emergency: receive

Showing posts with label receive. Show all posts

Monday, March 12, 2012

I receive MSG 7707 when trying to split a partition for the second time. Why ?

I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem.
I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.

At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"

Here's what i do:

/*-*/
CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/

CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/

CREATE TABLE [dbo].[Letture](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data)
) ON schemaPartizioneLive(data)

ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))

CREATE TABLE [dbo].[LettureStorico](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data)
) ON schemaPartizioneStorico(data)

ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))

/*-*/

Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:

/*--*/
SET @.NewBoundary = dateadd(dd,-1, @.dateOfToday)

--this new partition contains the rows i want to switch
ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)
--this new partition is empty
ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@.NewBoundary)

--this works fine, rows are moved
ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2

--these two merges lead to two tables partitioned in two partitions each
ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@.OldBoundaryLive)
ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@.OldBoundaryStorico)

/**/

Till now, everything is working as expected.
Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)" i receive a 7707 error message:
"Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."

How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?

Someone can help me on this, please ?

Many thankx

Wentu

Hello,

I've experienced the same problem. Even if you map all your partitions to the PRIMARY filegroup when you create your scheme, each time before you split your range, you have to call

ALTER PARTITION SCHEME schemapartizioneLive NEXT USED [PRIMARY].

However, why it works the first time is still a mystery to me. Probably when you mal all your partitions, the "next used" is probably also set. As you haven't done it when you split the first time, you got the error the second time.

Greetings,

Adriano

Wednesday, March 7, 2012

I need some serious help

Out of the 3 tables below. I need to get a tabular report like this
First Name Last Name Email
Can you receive HTML email Address State
Zip
Tom Thompson xxxx@.xxxxxxxx.com
Yes 575 mystreet Rd
AK 14525
steve Smith
aaaa@.aaaaaaaa.com No
575 double Dam Rd AL 13323
table 1
QuestionID ModID Question
18 362 First Name
19 362 Last Name
20 362 Email
21 362 Can you receive HTML email
23 362 Address
24 362 State
25 362 Zip
Table 2
Resultid QuestionID OptionD
OptionTextboxValue SurveyResultID modid
2051 18 -55 Tom
140 362
2052 19 -55
Thompson 140 362
2053 20 -55
xxxx@.xxxxxxxx.com 140 362
2055 21 47
140 362
2056 23 -55 575
mystreet Rd 140 362
2057 24 52
140 362
2058 25 -55 14525
140 362
2059 18 -55 steve
140 362
2060 19 -55 Smith
140 362
2061 20 -55
aaaa@.aaaaaaaa.com 140 362
2063 21 48
140 362
2064 23 -55 575
double Dam Rd 140 362
2065 24 53
140 362
2066 25 -55 13323
140 362
Table3
optionid QuestionID optionText
41 18 firstName
43 19 lastName
45 20 emailAdd
47 21 Yes
48 21 No
50 23 address
52 24 AK
53 24 AL
55 25 zipOn Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>Out of the 3 tables below. I need to get a tabular report like this
(snip)
Hi Dave,
You posted this same message about a week ago in .programming. David
Portas gave you an excellent answer. For your convenience, I've copied the
complete answer below. Do read the entire message and follow the link in
the last paragraph!
(start quote)
What is the rationale for your table design and for persisting the UI
information in the database? Don't you have normalized tables to
represent this information? If this is some kind of content management
layer then I would suggest you don't use it for reporting. Utilize it
as a Staging database with an ETL process to load into a normalized
data model. There are plenty of reasons why the
"entity-attribute-value" model you are proposing should be avoided in
SQL.
Anyway, take a look here for some solutions to your cross-tab report:
http://www.aspfaq.com/2462
(end quote)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'David' to a column of data type
int.
SELECT SurveyResultID,
SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
FROM FormCreator_Results
GROUP BY SurveyResultID
GO
--
David Fetrow
Helixpoint LLC.
http://www.helixpoint.com
davef@.helixpoint.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
> >Out of the 3 tables below. I need to get a tabular report like this
> (snip)
> Hi Dave,
> You posted this same message about a week ago in .programming. David
> Portas gave you an excellent answer. For your convenience, I've copied the
> complete answer below. Do read the entire message and follow the link in
> the last paragraph!
> (start quote)
> What is the rationale for your table design and for persisting the UI
> information in the database? Don't you have normalized tables to
> represent this information? If this is some kind of content management
> layer then I would suggest you don't use it for reporting. Utilize it
> as a Staging database with an ETL process to load into a normalized
> data model. There are plenty of reasons why the
> "entity-attribute-value" model you are proposing should be avoided in
> SQL.
> Anyway, take a look here for some solutions to your cross-tab report:
> http://www.aspfaq.com/2462
> (end quote)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
returns different datatypes, then SQL Server will try implicit datatype conversion so that end
result is of the datatype which has the highest precedence according to "Datatype Precedence" in
Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted to
an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
integer so you convert them to appropriate strings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value 'David' to a column of data type
> int.
> SELECT SurveyResultID,
> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
> FROM FormCreator_Results
> GROUP BY SurveyResultID
> GO
> --
>
> David Fetrow
> Helixpoint LLC.
> http://www.helixpoint.com
> davef@.helixpoint.com
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||It looks like your OptionTextBoxValue column must be a VARCHAR yet you
are trying to SUM it! I'll guess you only want to sum the numeric
values. Add a WHERE clause:
...
WHERE QuestionID BETWEEN 18 AND 21
or
...
WHERE OptionTextboxValue NOT LIKE '%[^0-9]%'
This complexity is one of the consequences of a having weakly-typed,
multi-valued columns and is an example of why your table design badly
needs fixing.
--
David Portas
SQL Server MVP
--|||Oops. See David's post. I missed the fact that SUM is performed, and it is pretty darn hard to sum
strings. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:%238OmS7OFFHA.1296@.TK2MSFTNGP10.phx.gbl...
> The result of a CASE need to be of the same datatype. Is the different expressions inside the CASE
> returns different datatypes, then SQL Server will try implicit datatype conversion so that end
> result is of the datatype which has the highest precedence according to "Datatype Precedence" in
> Books Online. Int is higher than the string datatypes, and the string 'David' cannot be converted
> to an int. What you can do is, inside the CASE, use an explicit CAST around the columns which are
> integer so you convert them to appropriate strings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DaveF" <dfetrow@.geodecisions.com> wrote in message news:unF4GjOFFHA.2608@.TK2MSFTNGP10.phx.gbl...
>> Server: Msg 245, Level 16, State 1, Line 1
>> Syntax error converting the varchar value 'David' to a column of data type
>> int.
>> SELECT SurveyResultID,
>> SUM(CASE QuestionID WHEN 18 THEN OptionTextboxValue ELSE 0 END) AS Q1,
>> SUM(CASE QuestionID WHEN 19 THEN OptionTextboxValue ELSE 0 END) AS Q2,
>> SUM(CASE QuestionID WHEN 20 THEN OptionTextboxValue ELSE 0 END) AS Q3,
>> SUM(CASE QuestionID WHEN 21 THEN OptionTextboxValue ELSE 0 END) AS Q4
>> FROM FormCreator_Results
>> GROUP BY SurveyResultID
>> GO
>> --
>>
>> David Fetrow
>> Helixpoint LLC.
>> http://www.helixpoint.com
>> davef@.helixpoint.com
>> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> news:gsq811lbdg6r3sl5rm1088li7hegceq459@.4ax.com...
>> On Wed, 16 Feb 2005 22:31:36 -0500, DaveF wrote:
>> >Out of the 3 tables below. I need to get a tabular report like this
>> (snip)
>> Hi Dave,
>> You posted this same message about a week ago in .programming. David
>> Portas gave you an excellent answer. For your convenience, I've copied the
>> complete answer below. Do read the entire message and follow the link in
>> the last paragraph!
>> (start quote)
>> What is the rationale for your table design and for persisting the UI
>> information in the database? Don't you have normalized tables to
>> represent this information? If this is some kind of content management
>> layer then I would suggest you don't use it for reporting. Utilize it
>> as a Staging database with an ETL process to load into a normalized
>> data model. There are plenty of reasons why the
>> "entity-attribute-value" model you are proposing should be avoided in
>> SQL.
>> Anyway, take a look here for some solutions to your cross-tab report:
>> http://www.aspfaq.com/2462
>> (end quote)
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>
>

Monday, March 12, 2012

I receive MSG 7707 when trying to split a partition for the second time. Why ?

Wednesday, March 7, 2012

I need some serious help

I need some serious help

I need some serious help

help emergency

Blog Archive

About Me