FTS Index Size: attempt

显示标签为“attempt”的博文。显示所有博文

2012年3月29日星期四

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.com...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.

2012年3月11日星期日

FIX TO: The attempt to connect to the report server failed. Check your connection information...

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

If you then scan the recent logfiles in C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles and find the following exception: Microsoft.SqlServer.ReportingServices2005.RSConnection+MissingEndpointException: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. > System.Net.WebException: The request failed with HTTP status 404: .

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

2) edit the file C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\RSWebApplication.config and add the full url to the ReportServer into the ReportServerUrl tag (no ending / nessesary) AND remove the value from ReportServerVirtualDirectory (otherwise you get nasty, unprecise exceptions). THEN you must recycle the app-pool used by the Report-Manager so that config file is being reloaded.

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Could some one please explain the security piece of reporting services? What type of IDs do I need, do I need to use SPN accounts if yes how and where would I put/specify this SPN account. Basically I am trying to understand how to setup security when I install reporting services.

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

Thank you! Thank you! If you are ever in southwest Florida, look me up, and I'll buy you a beer! I mean it. And there aren't many Vangors in the phone book down here, so I'll be easy to find. Or google me.

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>

From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

FIX TO: The attempt to connect to the report server failed. Check your connection information...

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>
From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

FIX TO: The attempt to connect to the report server failed. Check your connection information...

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>

From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

2012年3月9日星期五

FIX TO: The attempt to connect to the report server failed. Check your connection informatio

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.

FIX TO: The attempt to connect to the report server failed. Check your connection informatio

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>
From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

FIX TO: Reporting server 2005 prompting for user name /ID and domain when accessing http:localho

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Thanks

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>

From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

Fix for transmission_status message Connection handshake failed [...] The logon attempt failed

Full message:

Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 66

Under these conditions, setting trustworthy on on the sending and receiving databases will solve this issue

1) Communicating between multiple instances

2) Using Kerberos security (NT Authentication, i.e. not certificates)

The problem is from the SQL service account (the Windows account that runs the SQL Server instance service). It may be that the password has expired, the service account is missing the needed priviledges over the SPN in the Active Directory or the SPN is missing completely.

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: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

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.

|||

Larry Grady wrote:

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

)

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

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)

)

BEGIN

DECLARE @.AuditMsg XML

SELECT @.AuditMsg = '<AuditMsg MsgType="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

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

)

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"

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:Message Classify

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

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

|||

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

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.

Hey!

but as i see it Remus has it handled pretty well around here so no worries

Mladen

|||

Thanks, these posts helped a lot.

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.

|||

Larry Grady wrote:

It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.

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.

The way i ahve it working now

..... Process to build @.SQLString.....

EXEC(@.SQLString)

EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source

|||

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

)

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

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

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)

)

BEGIN

DECLARE @.AuditMsg XML

SELECT @.AuditMsg = '<AuditMsg MsgType="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

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

)

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"

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:Message Classify

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

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

|||

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

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.

Hey!

but as i see it Remus has it handled pretty well around here so no worries

Mladen

|||

Thanks, these posts helped a lot.

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.

|||

Larry Grady wrote:

It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.

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.

The way i ahve it working now

..... Process to build @.SQLString.....

EXEC(@.SQLString)

EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source

|||

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

)

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

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

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)

)

BEGIN

DECLARE @.AuditMsg XML

SELECT @.AuditMsg = '<AuditMsg MsgType="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

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

)

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"

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:Message Classify

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

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

|||

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

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.

Hey!

but as i see it Remus has it handled pretty well around here so no worries

Mladen

|||

Thanks, these posts helped a lot.

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.

|||

Larry Grady wrote:

It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.

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.

The way i ahve it working now

..... Process to build @.SQLString.....

EXEC(@.SQLString)

EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source

|||

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

)

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

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

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)

)

BEGIN

DECLARE @.AuditMsg XML

SELECT @.AuditMsg = '<AuditMsg MsgType="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

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

)

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"

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:Message Classify

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

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

|||

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

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.

Hey!

but as i see it Remus has it handled pretty well around here so no worries

Mladen

|||

Thanks, these posts helped a lot.

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.

|||

Larry Grady wrote:

It scares me a little that if there is some unexpected data or formatting problem that my queues will just shut down.

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.

The way i ahve it working now

..... Process to build @.SQLString.....

EXEC(@.SQLString)

EXEC usp_SendSearchAudit @.userID,@.brandID,....other criteria....., @.source

|||

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

)

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

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

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)

)

BEGIN

DECLARE @.AuditMsg XML

SELECT @.AuditMsg = '<AuditMsg MsgType="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

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

)

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)

2012年3月29日星期四

Float to Decimal Conversion - Transaction Log Fills Up

Float to Decimal Conversion - Transaction Log Fills Up

Float to Decimal Conversion - Transaction Log Fills Up

2012年3月11日星期日

FIX TO: The attempt to connect to the report server failed. Check your connection information...

FIX TO: The attempt to connect to the report server failed. Check your connection information...

FIX TO: The attempt to connect to the report server failed. Check your connection information...

2012年3月9日星期五

FIX TO: The attempt to connect to the report server failed. Check your connection informatio

FIX TO: The attempt to connect to the report server failed. Check your connection informatio

FIX TO: Reporting server 2005 prompting for user name /ID and domain when accessing http:localho

Fix for transmission_status message Connection handshake failed [...] The logon attempt failed

2012年2月26日星期日

First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"

First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"

First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"

First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"

First Service Broker Attempt, Prajdic''s Example, Error: "queue has been disabled"

FTS Index Size

Blog Archive

About Me