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
ADVERTISEMENT
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
Jan 26, 2004
Thats it
How do you receive the last item(row) in a table.
Thanks
View 11 Replies
View Related
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
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
Sep 19, 2007
How can I have my email download automatically instead of clicking send/receive all the time?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
May 12, 2007
I understand that SQL Service Broker will RECEIVE messages in the same order of SEND, so long as the messages are on the same conversation.
I would like to accomplish queue-level ordering instead of (or in addition to) conversation-level ordering.
There is a significant business case for this level of ordering. Consider an order processing system which is specified to fulfill orders in the sequence they are received. The reason for the ordering is as follows. Suppose the process(es) that RECEIVEs from the queue is down for several hours and the messages back up in the queue. Various customers place orders throughout this period of time. If more orders are placed than there are quantity for an item, the customers who placed their orders earliest in the day ought to be the ones that receive the merchandise and the later orders should be placed on backorder.
Limited experimentation showed that SQL Server totally disregards the order in which the messages were sent (on different conversations to the same queue).
The potential solution to use the same conversation has some drawbacks:
1. Difficult to do error handling because of the way error handling works in conversations.
2. It is not possible to RECEIVE using multiple threads. (Yes, RECEIVEing on multiple threads also would reorder the messages, but the reorderings would be localized in time so this would be tolerable by a lot of applications. In other words, orders from 1:00 AM would not be mixed with orders from 9:00 AM.)
Can anyone see a good solution to this in the current version?
If there are no good solutions, it would be great if Microsoft considers adding a feature where you can declare an ordering requirement when you CREATE QUEUE. Even support for an "approximate" ordering (messages can rearrange +/- several seconds) would be helpful, but the current way which seems to totally randomize the message order is not good for certain kinds of significant applications.
View 4 Replies
View Related
Aug 7, 2006
Hi all!
Help, please!
I am trying to send messages between 2 different server instances. I am getting the following errors in Profiler:
"This message could not be delivered because the user with ID 0 in database ID 14 does not have permission to send to the service. Service name: 'TestService1'."
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
The scripts for object creation and messaging is following at the first server instance:
USE master
GO
CREATE ENDPOINT SBroker
STATE = STARTED
AS TCP ( LISTENER_PORT = 1212 )
FOR SERVICE_BROKER (
ENCRYPTION = DISABLED
);
GO
USE Test
GO
CREATE QUEUE TestQueue
WITH STATUS=ON
CREATE SERVICE TestService
AUTHORIZATION dbo
ON QUEUE TestQueue
CREATE ROUTE TestRoute
WITH
SERVICE_NAME = 'TestService1',
BROKER_INSTANCE ='2B7CE76A-9804-46F3-9AE8-0AE59313613A',
ADDRESS = 'TCP://10.17.11.17:4037' ;
// send message script:
DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [TestService]
TO SERVICE 'TestService1','2B7CE76A-9804-46F3-9AE8-0AE59313613A'
ON CONTRACT [DEFAULT]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dh MESSAGE TYPE [DEFAULT] ('this is message1');
DECLARE @status nvarchar(1024);
SELECT status = GET_TRANSMISSION_STATUS(@dh);
END CONVERSATION @dh;
on second server instance:
use master
GO
CREATE ENDPOINT SBroker2
STATE = STARTED
AS TCP ( LISTENER_PORT = 1212 )
FOR SERVICE_BROKER (
ENCRYPTION = DISABLED
);
GO
in first server, in sys.transmission_queue transmission_status is empty
Both servers in the same domain, and databases on this server has the same owner.
on both servers, select @@version:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks a lot for help and explanation!
Sveta.
View 3 Replies
View Related
Nov 13, 2007
Receive this error msg when trying to parse a SQL Query
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have just finished going thru a long process getting SP2 CU3 patch installed.
The first file microsoft sent was corrupted and in turn corrupted my .NET Framework, had to run repair on the Framework before installing CU3.
I am now on version 9.0.3186. I have two other boxes with the same version and do not have this problem
My understanding is that all of the patches/hotfixes since SP,1 that this problem was resolved
Any help will be greatly appreciated as I am in a very heavy development stage on this box.
Thanks
Greg
View 1 Replies
View Related
Mar 19, 2008
How do we do this in SS2k5?
in
EXEC(sqlstring)
sqlstring wants to pass back a resultset to the caller.
- Local temp tables are out of scope.
- Global temp table works but is a bad idea.
- Table variables not supported as OUTPUT parameters for EXEC.
Regards, Nick
View 4 Replies
View Related
Feb 6, 2008
I've been trying different things to "READ" the recordset from the "Message Queue". I can read it but with some weird characters. I've tried
ActiveXMessageFormatter
BinaryMessageFormatter
XMLMessageFormatter
So, far I have no luck.
MessageQueue msgQ3 = new MessageQueue("SERVERNM\" + msg.Label, false);
Message msg3 = new Message();
msg3.Formatter = new ActiveXMessageFormatter();
msg3 = msgQ3.Receive(new TimeSpan(0, 0, 30));
byte[] b = new byte[msg3.BodyStream.Length];
msg3.BodyStream.Read(b, 0, (int)msg3.BodyStream.Length); System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
returnVal = enc.GetString(b);
RESULTS:
I try to convert it to String and then deserialize later but I'm getting some junk.
<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tempuri.org/">5??m.????????#?_?XTG!???????#?_?Xg??c??????? ?<???m????_?X |?"???????Dw=?????"I?<???m????_?X21? Reply_Code?$??5? MultiEntries?$??7?dName?.$??3? Page_Number?$??1? Number? $??3? Status_Code?$??/?_Name?.$??5? _Address?+$??/? B_City?$$??1? _State?$??-? _Zip? $??A? Bank_Telephone_Num?"$?? ??11 033000333YHONDA2nd street xavier VA 326200000(555) 555-5500</string>
View 1 Replies
View Related
Jul 23, 2005
hi alli have a web application on a web server.and another windows application on another computer.there are databases on each of them.how can i send and receive data between these two applications?please reply me as soon as possibleregards
View 5 Replies
View Related
May 1, 2006
I have html files which want to store in database SQL Server with data type image. How store and receive html file from database SQL Server by ASP.NET.
Data type image is pointer 16 bit to file html. So where will content of files html with their image store ?
Can I help you.
View 1 Replies
View Related
Jun 21, 2002
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
View 1 Replies
View Related
Feb 22, 2007
Hi,
I want to receive a mail when a specific user connects.
How can this be done best? Raise an alert? How?
Btw, the 'Login auditing' is set to 'Failed logins only'.
Suggestions are very welcome!
Cheers,
E
View 1 Replies
View Related
Jul 23, 2005
HI,I am getting an error on my sqlserver database server. My clients aregetting kicked out of transaction, giving an error"New transaction cannot enlist in the specified transaction coordinator"When i saw the event log on the DB server i found the following message************************************Event Type: InformationEvent Source: MSDTCEvent Category: CMEvent ID: 4156Date: 6/13/2005Time: 9:30:48 AMUser: N/AComputer: XXXXXXXDescription:String message: ProcID= 0x780 QMGR::Receive Returning Out Of ResourcesError.************************************************** ***************I am using SQLServer 2000 Advanced server, on Windows 2000 Advanced server.Thanks for help.Indushekar.
View 4 Replies
View Related
Jul 20, 2005
Hi All,I have to write a stored procedure which will send/Receive text filefrom/to a server by using FTP.Is anybody have done anything on it? or know about it.If yes, I would like to know about it. (also provide the storedprocedure code if possible)Thanks in Adv.T.S.Negi
View 3 Replies
View Related
Jun 19, 2007
Hello,
My report, which has a matrix and a chart work fine. Then...
I click on the Data tab, drag a measure into the results grid. At this point I haven't added the new measure to the matrix or anything in Layout.
I click the Preview tab and get the following message: "The definition of the report XXX is invalid. More than one data set, data region, or grouing in the report has the name 'YYY'. Data set, data region and grouping names must be unique within a report.
Ok, I get the point. But why and how would adding a measure give me this issue? Where can I even find where this is being duplicated?
The message refers to a name that is a parameter, and I don't see anything in there that might cause this.
Thank you for the help.
-Gumbatman
View 1 Replies
View Related
Oct 6, 2015
I have a VM with Windows Server 2012 and SQL 2014 installed. Some things about the VM: Using VMware Workstation 11. Created with 160 GB hard disk and 4G memory (that's all we could do when needing to run multiple VMs from the box we're using). I am able to get to the internet and to our network but the internet connection tells me that there is no internet access and we can't seem to fix it.When I attempt to create a backup of one of my DBs, I receive the attached error. No other backup has been created for this particular DB so it would not need to overwrite anything. This is an issue for all of the DBs that are in the SQL Server and not just an isolated DB.
We ran DBCC CHECKDB against the DB and no errors were returned.A bit about the DBs: Our software creates a number of empty DBs upon install. For testing purposes we have DBs that are populated with data that we restore once the install is complete. So our procedure is, once all services are shut down for our software and the SQL connection has been cut, we delete the DBs created during install and restore the DBs that will replace them. All of the DBs restored successfully so I don't understand why there would be an issue with backing them up.
View 8 Replies
View Related
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
View Related
