I Receive MSG 7707 When Trying To Split A Partition For The Second Time. Why ?

Nov 21, 2006

Hi

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

View 3 Replies


ADVERTISEMENT

SPLIT RANGE Partition Error

Feb 8, 2008



Hello All,

I am using SQL 2005 SP2. I have a table partitioned on date range. I am trying to SWITCH, MERGE and SPLIT partitions.
My SWITCH and MERGE work great. When the SPLIT query is executed, an error 9002 is thrown....

"The transaction log for database is full. To find out why space in log cannot be resued, see log_reuse_wait_desc column in sys.databases."


Below are more details...

- All SWITCH, MERGE and SPLIT are executed in one TRANSACTION.
- After SWITCH and MERGE, I execute a query set the partition scheme "NEXT USED [PRIMARY]".
- Finally i execute SPLIT statement.

Could you please tell me where am I going wrong?

Any help would be appreciated.

Thanks..................

View 2 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

View 15 Replies View Related

How To Partition The Split The Dataset Into Training And Validation When Running Descision Tree Model?

Jun 15, 2007



Can I ask how to split the dataset into training and validation when running descision tree model?

View 3 Replies View Related

Blocking With RECEIVE And SqlDataReader For Real-time Updates

Jul 17, 2007

I have this scenario which is working fine, but would like to know if others have tried it or can recommend a better approach. Below is a brief description, but code should fully explain:



A process updates a SQL Server table via a stored proc, which in turn writes information to a service broker target queue. In my client/server architecture, I would like clients to see this new information as soon as it gets written to the target queue. To do this, I have a WCF service that calls a stored procedure; the stored proc contains an infinite loop which has a RECEIVE statement with an infinite timeout. Once RECEIVE reads data from the SB queue, data is retrieved via a SqlDataReader and results are sent to clients via a pub/sub. I then wait for more rows until RECEIVE unblocks and so on.



Stroed Proc




Code Snippet

-- ...
WHILE 1 = 1
BEGIN
-- Param declarations

...



BEGIN TRANSACTION;
WAITFOR
(
-- Blocks while TargetQueue is empty
RECEIVE
TOP(1)
@conversation_handle = conversation_handle,
@message_type_name = message_type_name,
@conversation_group_id = conversation_group_id,
@message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'' AS XML)
END
FROM [dbo].[TargetQueue] -- No time out!
)



-- Handle errors
...



-- Return received information. After this statement is executed,
-- reader.NextResult unblocks and reader.Read() can read these
-- new values
SELECT 'Conversation Group Id' = @conversation_group_id,
'Conversation Handle' = @conversation_handle,
'Message Type Name' = @message_type_name,
'Message Body' = @message_body ;

COMMIT TRANSACTION


END -- WHILE



C# Code






Code Snippet

// Create a SqlCommand, and initialize to execute above stored proc
// (set command time to zero!)
...



// ExecuteReader blocks until RECEIVE reads data from the target queue
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Begin an infinite loop
while (true)
{
// Process data retrieved by RECEIVE
while (reader.Read())
{
Trace.WriteLine("Conversation Group Id :" + reader.GetGuid(0) +
"Conversation Handle: " + reader.GetGuid(1) +
"Message Type Name : " + reader.GetString(2) +
"Message Body : " + reader.GetString(3));

// Send data via pub/sub
...
}



// Blocks until stored procedure returns another select statement
// i.e., blobks until RECEIVE unblocks and retrieves more data from queue
reader.NextResult();
} // while
}





View 5 Replies View Related

A Key Time As A Split Variable In Microsoft Time Series?

Jul 4, 2007

Hello,

I was working with Microsoft Time Series model (MTS) with some data, when in the mining model viewer, decision tree tab, I realized that the key time variable that I define, it was acting like a split variable.

So, I ask you, this is possible?, because, for me, this should not happen€¦.

After, I review the Data Mining Tutorial by Seth Paul, Jamie MacLennan, Zhaohui Tang and Scott Oveson, and I found, in the Forecasting part, that the key time variable (Time Index) it was acting like a split variable too, in for example, M200 pacific:Quantity and R250 Europe:Quantity.

So people, it€™s possible that a key time variable act like a split variable in a MTS model?

Thanks

View 1 Replies View Related

Add Time To Datetime Value And Split Into Date And Time

Jun 12, 2007

Hi



i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).



could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).



Thanks

V.

View 3 Replies View Related

Could You Tell What's Wrong When I Split Table To The Target Partition Table?

Jan 22, 2007

Could you tell what's wrong when I split table to the target partition table?USE TEST--ADD FILEGROUP---------------------------------------------------------------------ALTER DATABASE TEST ADD FILEGROUP FG_01ALTER DATABASE TEST ADD FILEGROUP FG_02ALTER DATABASE TEST ADD FILEGROUP FG_03--ADD FILE--------------------------------------------------------------------------ALTER DATABASE TEST ADD FILE (NAME = DF_01,FILENAME = 'D:TESTDF_01.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_01ALTER DATABASE TEST ADD FILE (NAME = DF_02,FILENAME = 'D:TESTDF_02.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_02ALTER DATABASE TEST ADD FILE (NAME = DF_03,FILENAME = 'D:TESTDF_03.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_03--CREATE PARTITION FUNCTION---------------------------------------------------------CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)AS RANGE LEFT FOR VALUES ('20070101 23:59:59.997','20070102 23:59:59.997')--CREATE PARTITION SCHEME-----------------------------------------------------------CREATE PARTITION SCHEME PS_HIS_HTTP_LOGAS PARTITION PF_HIS_HTTP_LOG TO ( FG_01, FG_02, [PRIMARY])--CREATE PARTITION TABLE -----------------------------------------------------------CREATE TABLE HIS_HTTP_LOG ( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON PS_HIS_HTTP_LOG(STARTIME)--INSERT DATA,PARTITION 1 20070101-------------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 100BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())SET @i = @i +1END--INSERT DATA ,PARTITION 2 20070102-------------------------------------------------SET @i = 1WHILE @i <= 200BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())SET @i = @i +1END--CREATE A TABLE -------------------------------------------------------------------CREATE TABLE TMP_HTTP_LOG( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON FG_03--INSERT DATA TO TMP_HTTP_LOG 20070103-----------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 400BEGININSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())SET @i = @i +1END--ADD CONSTRAINT--------------------------------------------------------------------ALTER TABLE TMP_HTTP_LOGWITH CHECKADD CONSTRAINT CK001CHECK (STARTIME >= '20070103 00:00:00.000' AND STARTIME <= '20070103 23:59:59.997')--SPLIT RANGE ,SWITCH DATA----------------------------------------------------------ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3--==========================================�======================================Why is the error in step of“ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3�error infomation:message_id 4972,level 16,severity 1ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.Please tell me why ? check constraints ?Thank you very much !

View 1 Replies View Related

UNION With A Max On Split Date/Time Fields

Mar 30, 2000

I have 2 tables, each with one ID field, a separate
Date and Time fields and a number of other fields.
The tables contain duplicates on the ID field.
I want to do a UNION keeping only the record with the latest
Date and Time.

This would work:
SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate
FROM (SELECT myKeyField, myDate
from Table1
union
SELECT myKeyField, myDate
from Table2) AS MyTab
GROUP BY MyTab.myKeyField

But is only taking care of Date, not Time (some records have
the same date but different times)
The other problem is, when I add more fields, I have to
include them in the GROUP BY clause, and this way I end up
with duplicates (because some other fields have different
values)

Is there a way to do this?

View 1 Replies View Related

Split One Row Into Multiple Rows Based On Time Elements

Feb 5, 2007

I'm dealing with a problem.

The record information example

DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

what i want is for every half hour between start and end a record

10.30 action1
11.00 action1
11.30 action1

how can i create this, i'm a little stuck on this

View 2 Replies View Related

SQL Server 2008 :: How To Split Time Column Values Into Rows

Jun 6, 2015

I have the table as

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I want the output as : -

|start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL

I am trying the below mentioned code but it is not giving me the desired output..

with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn

[Code] .....

I am getting wrong output as -

| start || end1 |

1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00

View 1 Replies View Related

SQL Server 2014 :: Split Time Span Into Multiple Records?

Oct 30, 2015

I have a table that includes the fields below:

ID - INT
Machine - TINYINT
StartTime - DATETIME
EndTime - DATETIME

What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:

ID - 1
Machine - 2
StartTime - 2015-09-01 22:00:00.000
EndTime - 2015-09-03 22:00:00.000

So what I am looking for is taking the above record and turning it into 3 records like below:

ID Machine StartTime EndTime
1 2 2015-09-01 22:00:00.000 2015-09-01 23:59:59.999
1 2 2015-09-02 00:00:00.000 2015-09-02 23:59:59.999
1 2 2015-09-03 00:00:00.000 2015-09-03 22:00:00.000

View 5 Replies View Related

Tricky Schema Question - Dimension Can Split And Combine Over Time

Jul 20, 2005

Hi all,I'm working on the schema for a database that must represent data about stock& bond funds over time. My connundrum is that, for any of several dimensionfields, including the fund name itself, the dimension may be represented indifferent ways over time, and may split or combine from one period to thenext.When querying from the database for an arbitrary time period, I need the datato be rolled up to the smallest extent possible so that apples can be comparedto apples. For instance, if the North America region becomes 2 regions, USAand Canada, and I query a time period that spans the period in which thissplit occurred, I should roll up USA and Canada and for records in the periodthat has both, and I should call the result something like "(NorthAmerica)(USA/Canada)" in the output. The client specifies that the dimensionoutput must represent all dimensions that went into the input.Of course, I have to account for more complex possibilities as well, e.g.Fund-A splits into Fund-B and Fund-C, then Fund-C merges into Fund-D producing(Fund-A/Fund-D)(Fund-B/Fund-C/Fund-D)(Fund-B/Fund-D)I can think of several ways to handle this issue, and they're allextraordinarily complex and ugly. Any suggestions?Thanks,- Steve Jorgensen

View 9 Replies View Related

Transact SQL :: Split Rows Based On Time Difference More Than 1day

Aug 5, 2015

equipmentid downtimestartdate downtimeenddate  dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00            69
b6e4 2015-03-18 13:00 2015-03-20 04:00            39

i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)

equipmentid downtimestartdate downtimeenddate dowtime
a3er          2015-03-15 02:00 2015-03-15 24:00            22 
a3er          2015-03-16 00:00 2015-03-15 24:00            24
a3er          2015-03-17 00:00 2015-03-15 23:00            23

[code]...

View 2 Replies View Related

RECEIVE Vs RECEIVE TOP(1)

Apr 6, 2007



In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?


WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [Queue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR (N'Received error %s from service [Target]', 10, 1, @message_body) WITH LOG;
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [Queue]), TIMEOUT 1000;
END
COMMIT;



Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?



WAITFOR(RECEIVE TOP(1)


@conversationHandle = conversation_handle,

@messageTypeName = message_type_name,

@messageBody = message_body

FROM [Queue]), TIMEOUT 1000;



And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?



WAITFOR(RECEIVE


queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @tableMessages), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)


BEGIN


COMMIT;

BREAK;
END



What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

View 5 Replies View Related

Receive Top 20

Jul 5, 2007

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.



WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away



However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting



thanks

P

View 4 Replies View Related

How Do You Receive The Last Item In A Table

Jan 26, 2004

Thats it
How do you receive the last item(row) in a table.
Thanks

View 11 Replies View Related

Customer Should Receive Email Only Once

Jan 31, 2007

Hello Guys,
I really need you help to debug this query.
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.


SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress,


from
(
select
MIN(CAST(customer_guid AS varchar(36))) as Customer_GUID,
Transaction_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()


and Terminate_dm is null
and customer_guid
not
in
(
select CAST(customer_guid AS varchar(36)) as Customer_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()

and Terminate_dm is null
)
group by transaction_guid, customer_guid
)z
inner
jointblTransaction a
onz.Transaction_GUID = a.Transaction_GUID

View 8 Replies View Related

What Is The Way To Receive The Data As We Entered

Jul 20, 2005

Hai ,I created a table with primary key clustered. I have entered the datathru E.Manager . If a close the table and open it again , Ii shows therows with the (default) ascending order. Is, there any way to get therows in the user entered order(neither asc or dec order)With ThanksRaghu

View 2 Replies View Related

Email Send/receive

Sep 19, 2007

How can I have my email download automatically instead of clicking send/receive all the time?

View 1 Replies View Related

SSIS And The Receive Command

Feb 1, 2007

I built a system where I am sending batches of messages using a single conversation. I want to pull these messages out of my queue using Service Broker. I may have more than one batch sitting in the queue waiting to be picked up, so....

In my SSIS package i started by getting a unique list of conversation_handles where the message type is my end of batch message ( should only be one per batch). Then I used the foreach loop construct thinking I could pass the conversation_handles around and into the data flow.

In the data flow I want to pull all of the messages at once. It looks like the receive statement is designed to do this with the concept of using a table variable.

So I built this SQL to use in for the SQL Command of my OLE DB Source. It gives me this error "Syntax error, permission violation, or other nonspecific errorr"

declare @messages table ( conversation_handle uniqueidentifier, message_type_name sysname, message_body xml );

receive conversation_handle, message_type_name, message_body
from dm.[consultant queue]
into @messages
where conversation_handle = ?

select conversation_handle, message_type_name, message_body
from @messages

If I change it to this, by removing the passed parameter and looking up what should be the same value that I am passing in then it works.

declare @ch uniqueidentifier;
declare @messages table ( conversation_handle uniqueidentifier, message_type_name sysname, message_body xml );

select top 1 @ch = conversation_handle
from dm.[consultant queue]
where message_type_name = 'BatchEnd'
order by queuing_order;

-- select conversation_handle, message_type_name, message_body
receive conversation_handle, message_type_name, message_body
from dm.[consultant queue]
into @messages
where conversation_handle = @ch

select conversation_handle, message_type_name, message_body
from @messages

I have tried sneaking the parameter into the SQL in other ways, but always get the same I have tried sneaking the parameter into the SQL in other ways, but always get the same error message. It just seems that SSIS, or OLE DB, don't want to pass parameters into a block of SQL that is executing this receive command. Has anyone else done something similar to what I am doing here? Any ideas on how to resolve this?

Obviously by using SSIS I want to work on the whole batch at once and not iterate message by message. Right now I just don't like the idea that I am getting the conversation_handle twice and possibly getting a different batch of messages to process.

Thanks!

View 7 Replies View Related

RECEIVE Statement Not Working

Nov 19, 2007

I followed an example using the AdventureWorks database to set up a simple messaging test on one database:




Code Block
-- We will use adventure works as the sample database
USE AdventureWorks
GO
-- First, we need to create a message type. Note that our message type is
-- very simple and allowed any type of content
CREATE MESSAGE TYPE JobRequest
VALIDATION = NONE
GO
-- Once the message type has been created, we need to create a contract
-- that specifies who can send what types of messages
CREATE CONTRACT JobRequestor
(JobRequest SENT BY INITIATOR)
GO
-- The communication is between two endpoints. Thus, we need two queues to
-- hold messages
CREATE QUEUE RequestorQueue
CREATE QUEUE ReceiverQueue
GO
-- Create the required services and bind them to be above created queues
CREATE SERVICE Requestor
ON QUEUE RequestorQueue
CREATE SERVICE Receiver
ON QUEUE ReceiverQueue (JobRequestor)
GO
-- At this point, we can begin the conversation between the two services by
-- sending messages
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Requestor
TO SERVICE 'Receiver'
ON CONTRACT JobRequestor
WITH ENCRYPTION=OFF, LIFETIME= 600;
-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE JobRequest (@message)
COMMIT TRANSACTION
END
GO
-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO


This all works fine but if I run the section that creates the message and then copy the RECEIVE section to a new query window and execute it, nothing is returned. If I run the RECEIVE section within the same query window it returns the 'Hello World' message as expected. I am new to Service Broker and so am assuming that I am missing something obvious!!

View 5 Replies View Related

Not Able To Receive Return Messages From Far End...

Apr 27, 2007

Dear all,



I have set up service broker to work between two instances of SQL server with Dialog Security (implemented using certificates). The initiator queue has a activation procedure attached to process the return messages.



I'm receiving the messages from inside a SSIS package using Receive statement. Once I recive the message, I store the conversation handle, message type and message body in variables and execute the remaining ETL package based on the input.



Towards the end of the package, I send a message back to the Initiator for the same conversation to indicate sucess or failure.




Code Snippet

declare @conversation_handle UNIQUEIDENTIFIER

select @conversation_handle = <<SSIS User Variable>>;

SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [/OLAP/Error] (N'<Error>My custom error</Error>');

END CONVERSATION @conversation_handle ;



The problem now is that, the initiator queue receives only the "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" message. I would expect it to recieve "/OLAP/Error" first and then the "EndDialog" message.



Any idea on what's happening here? Any help is appreciated.



Cheers,

Arun

View 5 Replies View Related

Dynamic Queue Receive Sql ?

Sep 27, 2006

Hi There

My activation sp must be able to read of various queues.

I load a variable with the queue name that activated the sp btu i cannot get the syntax working to receive or get a conversation group of a queue name that is a variable.

I have tried:

WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @callingQueue INTO @msgTable WHERE conversation_group_id = @conversationGroup

), TIMEOUT 2000;

But i get this error:

Incorrect syntax near '@callingQueue'.

Looks like you cannot use a variable.

So i tried the following:

SELECT @SQL = N' WAITFOR

(

RECEIVE message_body, conversation_handle, message_type_name, message_sequence_number, conversation_group_id FROM @callingQueue INTO @msgTable WHERE conversation_group_id = @conversationGroup

), TIMEOUT 2000'

EXEC sp_executesql @SQL, N'@msgTable table output'

But i get the same error.

How do i receive of a queue using a vriable holding the queue name ?

Thanx

View 13 Replies View Related

Receive Multiple FTP Files

Dec 28, 2006

Is there a way to get more than one file with a single ftp task in SQL 2005??

I need to get 5 files from one server. They are in two different directories is that makes any difference. Right now I have a separate task for each but would like to have one task if possible.

Thanks

View 4 Replies View Related

Receive All Messages On Queue

Feb 12, 2007

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.



WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @tableMessages), TIMEOUT 2000;



View 4 Replies View Related

Probleim In Using RECEIVE Command.

Feb 23, 2008



Hi,
My msdb grown up abnormally to 35 GB.
I used :

use msdb
go
SELECT TOP 10 OBJECT_NAME([object_id]), *
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
ORDER BY in_row_reserved_page_count DESC;

And i found queue_messages_407672500 table is occupying a lot of space.

When i tried to query :
select TOP 5 * FROM queue_messages_407672500 ;
I got error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'queue_messages_407672500'.

Also , to clear the queued messages in msdb , i tried to use :
RECEIVE TOP (1) * FROM queue_messages_407672500 ;
But got the same error as above.

Please suggest , how can i shrink the msdb now, as without receving the messages i am not able to shrink it.

Many Thanks in advace.
Mohit

View 2 Replies View Related

How To Receive Files In FTP Task

May 5, 2006

Hi everyone,

I want to design a FTP task to download all the xml files from a FTP site. And I don't know what the file's name is.

How can I design this task?

Thank you for your helps!

Tony

View 5 Replies View Related

How To Receive Unique Row In Database Table

Feb 26, 2013

I am getting problem in unique row in database table, but not getting unique row because I have used Distinct keyword but not getting unique row so how can we do?

I have two table one table information another table Id. But second table in two code same but I am using distinct keyword i get some row unique but second table in two row in same code but when i am fetching row same auto_id render same id create duplicate row. But I am getting only unique row.

View 1 Replies View Related

External Activation And Receive Timeout

Oct 31, 2006

I have a set of service broker services setup that rely on external activation to process messages. I'm using the GotDotNet ExternalActivator, and it launches console applications that do the actual retrieval from the queues. The console applications are written to run continuously to avoid the cost of starting up .NET based console apps over and over again.

I am observing very odd timing behavior. With the receive queues empty and the external activator configured to run a minimum and maximum of 5 instances, I observe in SQL Profiler that most of the receive operations finish in about the same amount of time as my WAITFOR command in my receive stored procedure. However, there is usually one receive command that consistently takes upwards of 30 seconds and often causes sql timeout exceptions to be thrown. I know that I could code around this, but I wasn't really expecting this behavior.

Does anyone have any thoughts on why it might be occurring? I would have expected to routinely see my receive operations taking 15 seconds, give or take, especially when the queue is empty. Also, I have observed this behaviour on both SQL 2k5 Express and Dev Editions, so I don't think it's a version thing.

The stored procedure I am using to do the receive is:

Create PROCEDURE [dbo].[P_RTD_MessageBase_Receive]

@receiving_queue_name varchar(255),

@receiving_time_out int

AS

Declare @receiveQuery nvarchar(300)

Set @receiveQuery = 'WAITFOR(RECEIVE * FROM [dbo].['+ @receiving_queue_name +']),

TIMEOUT ' + cast (@receiving_time_out as varchar)

Execute sp_executeSql @receiveQuery

View 6 Replies View Related

Don't Receive Response From Target Server

Feb 19, 2008

Hi,

I have a problem using service broker, a send the message from server SSB1(initiator) and a receive this message on server SSB2(target), but I don't receive response to SSB1...

In my server SSB2 has this messages on Profiler:
- This message could not be delivered because it is a duplicate.
- Could not forward the message because forwarding is disabled in this SQL Server instance.
- The message could not be delivered because it could not be classified. Enable broker message classification trace to see the reason for the failure.


Message from SSB1 Profiler:

- This message was dropped because it could not be dispatched on time. State: 1


And the messages not end in both servers


Tks


Fernando Bueno

View 8 Replies View Related

Exception From DBComms.receive.method

Jul 11, 2006

Hi,

I am getting SQLException when I connect one of SQL Server 2000 Integrated Security host. I would like to know how to correct this problem.

An exception occurred during the DBComms.receive method. Operation:An existing connection was forcibly closed by the remote host. Context:(1) [Thread[main,5,main], IO:adc97, Dbc:8460d]. PktNum:1. TotalReceived:173. PktSize:4,096.

Thanks.

View 44 Replies View Related

Recovery :: Operator Does Not Receive Notification

May 18, 2015

alert fires, but the responsible operator does not receive notification.We area using this alert from past few months. But last week got an TempDB full and we found the error in SQL server 2012 error log. But we didn't get notification to email.But the email id is working fine because we are using same email id for all other alerts.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved