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!!
I'm writing some generalized stored procedures for use form managed code. The following stored proc works great:
CREATE PROCEDURE fnd_Send ( @Message VARCHAR(5000), @Contract SYSNAME, @MessageType SYSNAME, @FromService SYSNAME, @ToService VARCHAR(100), @Encrypted Bit ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle FROM SERVICE @FromService TO SERVICE @ToService ON CONTRACT @Contract WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle MESSAGE TYPE @MessageType(@Message);
Notice the use of SYSNAME parameters €“ translating the parameter into an object name - allows it to work in the BEGIN DIALOG statement when an system object is needed. Works create and cues up generic messages.
The following will not compile:
CREATE PROCEDURE fnd_Receive ( @Queue SYSNAME, @Message VARCHAR(5000) out, @MessageType SYSNAME out ) AS BEGIN DECLARE @Handle UNIQUEIDENTIFIER;
RECEIVE TOP (1) @Handle = conversation_handle, @MessageType = message_type_name, @Message = message_body FROM @Queue; END GO
It won€™t let me use the SYSNAME field @Queue in the FROM clause. I€™m getting an unfriendly:
Msg 102, Level 15, State 1, Procedure fnd_Receive, Line 15 Incorrect syntax near '@Queue'.
It looks like maybe I can€™t bind a variable in the FROM clause €“ perhaps like you cannot in a SELECT statement. I can fall back to dynamic SQL in the stored proc or in the managed code; I was hoping not to.
Can anyone shed any light on this? Also, are there any other techniques I'm missing to avoid falling back to a dynamic SQL statement.
INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID) SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1 FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR] AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX WHERE PLAN_DEMAND.BOD_INDEX IS NULL
When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.
I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?
UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]
SET [LaborBudget_USD] = Week1
FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN
[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON
Labor.Location_Code = Budget.Location_Code
JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON
Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
myCommand2.CommandText = "INSERT INTO testimonials(name,email,testimonial,approved,time) VALUES ('" + exp.escString(txtName.Text) + "','" + exp.escString(txtEmail.Text) + "','" + txtTestimonial.Text + "','" + false + "','" + DateTime.Now + "')"; ok the form has a field txtTestimonial.Text these strings work when i submit them thoughthats all folksthat"s (double quotes) that\"sthat\"s these failthat's that's that\'s that\'s tried up to 7 just to make sure Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'. I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind thatI tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess. Any help would be greatly appreciated. Jim
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
can somebody explain to me why the below update fails to update one row and updates the entire table?
Code:
UPDATE addlist SET add_s = 1 WHERE EXISTS (SELECT a.add_s, a.email_address, e.public_name FROM add a, edit e WHERE a.email_address = e.email_address and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
Hi, I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number  [code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), Â Â Â Â Â Â CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) Â Â Â FROM [DataGapTest]
Gives the right output:
NULL 1 1 3548 0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time) RETURNS INT AS  BEGIN  DECLARE @timeInterval INT   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))    FROM dbo.[DataGapTest]   RETURN @timeInterval  END
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?
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
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
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
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"
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.
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.
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
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.
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.
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.