Hello,
I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
to find all possible paths (based on the PK - FK tables relations) in a
relational database schema (Graph).
Anyone some usefull tips?
Thanx,
Peter"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:1914E310-C649-4A24-8B95-32DD63B625F4@.microsoft.com...
> Hello,
> I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
> to find all possible paths (based on the PK - FK tables relations) in a
> relational database schema (Graph).
> Anyone some usefull tips?
> Thanx,
> Peter
See http://tinyurl.com/49gft.
There's a recursive solution first, that you can't use with SQL Server 2000
but you can with SQL Server 2005, which is followed by an iterative solution
.
The solutions were implemented for DB2 but if you add an @. to the front of
variable names and change END WHILE to END it should be legal T-SQL.
JAG|||Peter,
Here is a link to a naive transitive closure algorithm (keep taking
powers of the adjacency matrix until you get nothing new). It might
at least help you implement Warshall's or other graph algorithms.
http://groups.google.co.uk/groups?q=kass+transclose
Steve Kass
Drew University
PeterM wrote:
>Hello,
>I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
>to find all possible paths (based on the PK - FK tables relations) in a
>relational database schema (Graph).
>Anyone some usefull tips?
>Thanx,
>Peter
>
2012年3月11日星期日
Fixed v Changing v Historical attribute conflicts
We have an issue in a SCD where a number of records may be presented that have changes to their attributes of EVERY type.
Example,
BusinessKey: xxxxxxxx
BuildingTypeId: 7
BusinessUnitHistoryId: 4019
BusinessUnitId: 4019
CurrencyId: 26
DevelopmentTypeId: 14
MarketId: 182
Name: abcdefgh
CurrencyId is a fixed attribute
MarketId & BuildingTypeId and the BusinessUnitId & BusinessUnitHistoryIds are historical attributes
Name is a changing attribute
The behaviour of the ETL seems to suggest that if fixed attribute changes are detected, these rows will error and therefore the changing & historical attributes will NOT be amended during the SCD transformation. Is this correct... as it seems to be what is happening.
Yep, that's an error. A fixed attribute cannot change. How do you propose the SCD handles that scenario? To me, it's bad data and should be redirected.|||
Thanks for the confirmation Phil.
I figure we are going to amend CurrencyId from Fixed to Changing as it seems they just overwrite at source and do not keep the history.
So, fixed attributes aside, what happens if the same row contains both a changing attribute amendment AND a historical attribute amendment - do both get successfully changed or does one type take precedence over the other?
Let's say (in the above example) both the Name (changing) and MarketId (historical) had changed.... do both get done or just one... if it's just one, do you have a suggestion as to how we reflect both changes?
Thanks for your help
Will
|||The historical attribute change will always occur, and I think there's a setting on whether or not to change all historical instances with changing attributes.|||
Yes,
Ah i think i get it...
I'm aware that if a changing attribute is detected, you can elect to change all instances of that record.
What I was unclear about is whether during a single SCD pass, if a record contains a field which is a changing attribute AND a field which is a historical attribute, do BOTH get done ?
From what you have said, the historical attribute will get done AND the record will also pass down the changing attribute route if such a change is detected. ?
If that's that case then hats off the MSFT... I just have a sneaky feeling that only one will get done.... maybe I just need to set up some samle data and test this.
Think about it -- it's just input into the historical row -- so when it writes the new row it just reads in the value of whatever's in that column. As for updating all of the "expired" rows, that's similar behavior to a multicast and is executed independent of the historical row change.
If that isn't clear, just know that it will work!|||
No... it's clear enough Phil and I never had cause to question it before..... You know when you start questioning stuff like this you've obviously been working on a problem for too long!!
Thanks alot
Will
Fixed number of Detail Lines
I have to create a report that will always show 12 detail lines per group, no matter how many records are in the group. For example, if there are only 8 records in the group I need to print 4 blank detail lines. I have it printing and breaking on the group just fine. How Do I get it to force the needed blank lines?
There will never be more than 12 per group.
I already have the report doing the new page on new group.
At this point the report works other than filling in missing detail with blank lines.
The report is a class roster which shows the students that have signed up for the class in a grid type layout. I just need the extra blank detail lines as a nice formating step. Some classes only have 4-5 students some have a full enrollment of 12Place a formula in the group footer to print 'n' blank lines.
e.g.
whileprintingrecords;
local numbervar lines := 12 - count({table.field}, {table.group});
local stringvar text;
while lines > 0 do
(
lines := lines - 1;
text := text & chr(13); //you may need chr(10) too / instead.
);
text
Tick the 'Can Grow' property of the object (right click, format field, common tab) and use the section expert to make the footer 'Suppress Blank Section'.
There will never be more than 12 per group.
I already have the report doing the new page on new group.
At this point the report works other than filling in missing detail with blank lines.
The report is a class roster which shows the students that have signed up for the class in a grid type layout. I just need the extra blank detail lines as a nice formating step. Some classes only have 4-5 students some have a full enrollment of 12Place a formula in the group footer to print 'n' blank lines.
e.g.
whileprintingrecords;
local numbervar lines := 12 - count({table.field}, {table.group});
local stringvar text;
while lines > 0 do
(
lines := lines - 1;
text := text & chr(13); //you may need chr(10) too / instead.
);
text
Tick the 'Can Grow' property of the object (right click, format field, common tab) and use the section expert to make the footer 'Suppress Blank Section'.
Fixed decimal convertion
I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get data in 5 decimal points?
Your help with example would be appreciated.
aspx code:
<asp:GridView ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true"
AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None"
BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt"
OnLoad="GridView1_Load" >
<Columns>
<asp:TemplateField HeaderText="Show">
<ItemTemplate>
<asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" />
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:BoundField DataField="Latitude" HeaderText="Latitude ( ° )" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="Longitude" HeaderText="Longitude ( ° )" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>
In your <asp:BoundField /> for the Latitude, add the following:
DataFormatString="{0:F5}"
F represents a fixed decimal, and 5 represents the number of decimal places
|||On your gridview open the gridview task with clicking little arrow on the right upper corner.
Then click Edit Columns
Then select your field "Latitude",
Then on the right, scroll down and come to Data section.
Change the dataFormatString as {0:N5}
Also you will see another property under Behavior section which is "HtmlEncode" make it false...
Let me know if it does not work...
|||
Thanks a lot guys for your help.
In the mean time, I found another way to do it. Using Convert.ToDecimal in a Item Template also does the job.
<asp:TemplateField HeaderText="Latitude ( ° )">
<ItemTemplate>
<%# Convert.ToDecimal(Eval("Latitude")).ToString("0.00000") %>
</ItemTemplate></asp:TemplateField>
Hiavci,
It did not work in my case.
|||Hi,
It must work.I already tried and it's working.
Did you make HTMLEncode=False ?|||
I did. But may be I am missing something else.
<asp:BoundField DataField="Latitude" HeaderText="Latitude " DataFormatString="{0:N5}" HtmlEncode="False" >
<ItemStyle HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
</asp:BoundField>
Datafield "Latitude" is not defined varchar or text in the database,right?
Can you check its type in the database?
GPSID int Unchecked
VehicleID int Unchecked
Latitude varchar(50) Unchecked
Longitude varchar(50) Unchecked
GPSTime datetime Checked
ServerTime datetime Unchecked
Valid bit Unchecked
If those two are varchars, you will not be able to write them as decimals.That property we said will not work. You have to change their type to int, or decimal.
2012年3月9日星期五
Fit Report To Page Problem.....
Hi
when i am running the report and trying to export it ( acrobat for example) i get the report on 4 pages.
the report contain 4 small table.
i check the margins / padding and everything else......
don't know what to do, need HELP.......
Thanks........
When you are in the layout mode in report designer, click on Report from the main menu and select "Report Properties". In the Report Properties window click on the layout tab and increase page height and width. This should solve your problem.
Thanks,
-Rohit
2012年3月7日星期三
First time writing, string manipulation?
I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.
For Example:
"GOLF IS FUN,BOWLING IS GREAT"
What Id like to get as my results:
"Golf is fun, Bowling is great"
Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi
Welcome to the forum :D
By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase
I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.
Anyhoo - see how you get on.
HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))
set nocount on
While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))
Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO
Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.
what sql is good at is set based operations.
For Example:
"GOLF IS FUN,BOWLING IS GREAT"
What Id like to get as my results:
"Golf is fun, Bowling is great"
Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi
Welcome to the forum :D
By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase
I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.
Anyhoo - see how you get on.
HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))
set nocount on
While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))
Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO
Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.
what sql is good at is set based operations.
2012年2月26日星期日
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Larry Grady wrote:
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"
I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.
I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.
This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.
I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."
I'm hoping someone can point me in the right direciton, thanks.
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1\Larry
Enable the destination queue using ALTER QUEUE [<queuename>] WITH STATUS = ON; During development queues often become disabled as a result of poison message prevention mechanism noticing rollbacks, see http://technet.microsoft.com/en-us/library/ms171592.aspx
|||Ok, so it looks like the problem is before this. The real question is why is my queue being disabled. Because when I alter the queues to have WITH STATUS=ON or if I delete teh queues and services and recreate, when I run it the first time, my queue gets disabled. I'm not sure why, can't seem to find a good error message.
Here is profiler on that first run
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Message Classify Microsoft SQL Server Management Studio - Query
Broker:Conversation Group Microsoft SQL Server Management Studio - Query
Broker:Conversation STARTED_INBOUND Microsoft SQL Server Management Studio - Query
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query
Broker:Activation Microsoft SQL Server Management Studio - Query
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @.X Microsoft SQL Server Management Studio - Query sa
Broker:Queue Disabled
Broker:Activation
I get no results when I run SELECT * FROM sys.transmission_queue
Any idea why my que is disabling or where i can get a better message letting me know what's going on. This is my first attempt that this so it could be something simple I'm missing.
I used that original example and only changed the send and receive stored procedures and dbase names. No other administration was done.
|||Your activated procedure is rolling back and causing the queue to be disabled. Check ERRORLOG for error messages from the activated procedure. Turn off activation and run the procedure manually to catch it's output and debug it.|||Hey!
In the future if you have any problems you're welcome to post the error in the comments section of that article because i don't really check here so often. I'm practicaly online all the time on SQLTeam during the work day so i'd also be able to answer any question you might have.
but as i see it Remus has it handled pretty well around here so no worries
Mladen
|||Thanks, these posts helped a lot.
I had changed the XML structure and the audit table/insert from the article to meet my biz needs and as a result I wasn't querying the msgBody correctly. However, I had no idea why my queue was disabled when I ran my example. It seemed more like an administration or permission error to me, so that's what I was concentrating on. It wasn't until I started stepping through, running pieces of the sprocs on their own that I found it.
But there wasn't any kind of informative error letting me know what the problem was on the target side. I think perhaps I need to institute the same kind of error handling that is in the initiating sproc into the targe sproc. Then if there is a logic error I can have some kind of information about it.
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
Anyway, I have my basic example working and it seems to work great. Great example articles, thanks.
|||
It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.
In production you should monitor queues using event notifications for QUEUE_DISABLED event. This event is fired when a poison message disables a queue and you can react to the event (eg. notify the site administrator)|||
also in my example the error that causes the activation proc to rollback gets saved in the AuditErrors table.
you might want to check there for troubleshooting.
|||My project is moving along nicely. I have my queues working properly and i'm writing data to my audit tables.
I have one more issue, although it's not directly SSB related, it is part of this project.
My main stored procedure is a product search. For each product search we have to save the Criteria that was used and then we have to save the products that were returned. The stored procedure itself is a really long and complicated proc that builds a dynamic sql string. Then at the end it executes it.
The way i ahve it working now
..... Process to build @.SQLString.....
EXEC(@.SQLString)
This returns my search results. Now i've added after it my initiator query. This query creates the AuditMsg in XML and loads it into a message which is que'd and sent to my target database where this data is inserted into an audit table.
EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source
This is where i'm a little stuck. I need to then take the output, the actual records that are returned, and store 3 of the returned fields into an audit table. The results can be anywhere from a few records to a couple thousand.
I was thinking of INSERTING into a temp table and then sending that and doing a SELECT FOR XML to send to the audit table and then doing a SELECT @.tempResults to return from the main stored procedure. I don't know if that's really teh best way to do this. Anyone have any suggestions. I'm kind of struggling with the right way to do this. A few lines of pseudo code may go a long way. Thanks.
|||i'm not quite sure i understand what you're trying to do...
could you post some sample code or pseudo code?
|||Here is my initial search query (actually much of the dyamic part is stripped out because it doens't apply to the problem).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[spResults_Select]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.priceFrom real,
@.priceTo real,
[MORE CRITERIA]
@.bHiResImg int
)
AS
BEGIN
[LOTS OF CODE HERE BUILDING SQL STRING. Actually a TEMP table is build and then we select off of that temp table, it's very complicated. We plan on optimizing later but for now it is what it is]
[the string is @.SelectSQL]
EXEC(@.SelectSQL)
[This is where the original stored procedure ended. This EXEC line returns the search results back to the Search form]
[New Code: This stored procedure is an initiator query that takes the parameters, formulates it into XML, creates a messages and send it to SSB. This is for the search criteriea, this is working]
EXEC usp_SendSearchAudit
@.userID=@.userID,
@.brandList=@.brandList,
@.subCatList=@.subCatList,
@.keywordSearch=@.keywordSearch,
@.priceFrom=@.priceFrom,
@.priceTo=@.priceTo,
@.bHiResImg=@.bHiResImg,
@.source=@.source
[This is where I am stuck. EXEC(@.SelectSQL) returns a bunch of records to the searchresults object. However, I need to take those same records and extract the Product_ID from each and save it to the audit tables in teh other database. I am going to create XML object out of it, create a message, send it to SSB).
Something like
EXEC ups_SendSearchResultsAudit
[parameters]
Or, maybe It would all be a part of the usp_SendSearchAudit above, that I would pass a TABLE parameter into that stored procedure?
END
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SendSearchAudit]
(
@.userID varchar(36),
@.brandList varchar(MAX),
@.subCatList varchar(MAX),
@.keywordSearch varchar(2000),
@.priceFrom real,
@.priceTo real,
@.bHiResImg int,
@.source varchar(10)
)
AS
BEGIN
DECLARE @.AuditMsg XML
SELECT @.AuditMsg = '<AuditMsg MsgType="Search">
<Search>
<userID>' + @.userID + '</userID>
<brands>' + (SELECT 1 as Tag, NULL as Parent, gID [brandID!1] FROM dbo.f_ConvertGuidList_to_Table(@.brandList) Brands FOR XML EXPLICIT) + '</brands>
<subCats>' + (SELECT 1 as Tag, NULL as Parent, string [subCatID!1] FROM dbo.f_ConvertStringList_to_Table(@.subCatList) SubCats FOR XML EXPLICIT) + '</subCats>
<keywords>' + @.keywordSearch + '</keywords>
<priceFrom>' + CONVERT(varchar(10),@.priceFrom) + '</priceFrom>
<priceTo>' + CONVERT(varchar(10),@.priceTo) + '</priceTo>
<hiRes>' + CONVERT(varchar(10),@.bHiResImg) + '</hiRes>
<source>' + @.source + '</source>
</Search>
</AuditMsg>'
EXEC dbo.usp_SendAuditData @.AuditMsg
--SELECT @.AuditMsg
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Here is the query that is called from search results, it creates the xml and passes it into the initiator query
ALTER PROCEDURE [dbo].[usp_SendAuditData]
(
@.AuditedData XML
)
AS
BEGIN
BEGIN TRY
DECLARE @.dlgId UNIQUEIDENTIFIER
-- Begin the dialog, either with existing or new Id
BEGIN DIALOG @.dlgId
FROM SERVICE [//Audit/DataSender]
TO SERVICE '//Audit/DataWriter',
'X1X1X1X1-X1X1-X1X1-X1X1-X1X1X1X1X1X1'
ON CONTRACT [//Audit/Contract]
WITH ENCRYPTION = OFF;
;SEND ON CONVERSATION @.dlgId
MESSAGE TYPE [//Audit/Message] (@.AuditedData)
END TRY
END
I have stripped out a ton of code that doesn't really pertain, but this is basically what i'm doing.
|||you know... that is the coolest GUID i've seen in a while!
well if a temp table is created then you could select from that temp table.
for temp tabel scopes read this post of mine:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx
you can also use a global temp table with a guid for name to avoid problems.
订阅：
博文 (Atom)