END CONVERSATION WITH CLEANUP, Is this bad? Records pile up in queues without it.

I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?

My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.

My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?

This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!

Thanks,
Greg Van Mullem

? "Bad" and "unnecessary" are interesting labels, because they can be applied so differently to various scenarios. If WITH CLEANUP were not necessary in any scenario, I seriously doubt it would have been included in the product. But is it right for every scenario? No. Essentially, according to the documentation and Roger Wolter's book (which you should definitely buy a copy of if you're working with SSB), those closed conversations are supposed to stick around for a while, to avoid certain types of replay attacks. But they should disappear after a while. As for the WITH CLEANUP, it is bad if your conversation is truly a conversation (i.e., a two-way message exchange), because when one side ends the conversation and uses that option, the other side doesn't find out about it. And that can certainly be bad in many cases. On the other hand, if you're only doing one-way transmission, that may or may not be an issue. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Greg Van Mullem@.discussions.microsoft.com> wrote in message news:1c9bb57b-0113-4fd6-ba81-14c686a99b16@.discussions.microsoft.com... I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!Thanks,Greg Van Mullem|||

Adam,

Thanks for the info. This is exactly what I was looking for. I will be removing the "WITH CLEANUP" clause from my code now that I know it's not necessary.

Also, I have Roger Wolters book and your book "Pro SQL Server 2005". Both are books excellent! I found the XML section in your book to be especially helpful in setting up my Service Broker messages.

Thanks,
Greg Van Mullem

Enabling Mixed Mode Auth. with tSQL?

Is it possible to run a tSql statement that will enable Mixed-Mode authentication? My customers need to have this set and I don't want them to have to go into Enterprise Mgr. Any ideas?
Thanks!I'm not aware of any way. You may want to run a profiler trace and then do it through enterprise manager to see what command sit executes.

Just an idea...|||Valid tip by Loach, but that does include reading thru registry keys which is not advisable to deal with a code.
YOu're better of with GUI supplied tools.|||Originally posted by riceman744
Is it possible to run a tSql statement that will enable Mixed-Mode authentication? My customers need to have this set and I don't want them to have to go into Enterprise Mgr. Any ideas?

Thanks!

Hello,
you can change security model this way

begin
DECLARE @.hr int
DECLARE @.sqlserver int
declare @.hresult int
DECLARE @.IntegratedSecutiry int
declare @.hostname varchar(255)

declare @.servername varchar(255) set @.servername = @.@.servername

EXEC @.hr = master.dbo.sp_OACreate 'SQLDMO.SQLServer', @.sqlserver OUTPUT
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver

EXEC @.hr = master.dbo.sp_OASetProperty @.sqlserver, 'LoginSecure', 'TRUE'
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver


exec @.hr = sp_OAMethod @.sqlserver, 'Connect', NULL, @.servername
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver

EXEC @.hr = master.dbo.sp_OAGetProperty @.sqlserver, 'IntegratedSecurity', @.IntegratedSecutiry OUTPUT
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver


EXEC @.hr = master.dbo.sp_OASetProperty @.IntegratedSecutiry, 'SecurityMode', '2'
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver


EXEC @.hr = master.dbo.sp_OAGetProperty @.IntegratedSecutiry, 'SecurityMode'--, @.IntegratedSecutiry OUTPUT
IF @.hr = 0
PRINT 'Ok'
ELSE
EXEC sp_OAGetErrorInfo @.sqlserver

EXEC @.hr = master.dbo.sp_OADestroy @.sqlserver
end
GO

by
Paan-cha|||You can do it by modifying the registry.
For default instance
HKLM/Software/Microsoft/MSSQLServer/MSSQLServer/LoginMode

= 1 integrated security, 2 mixed.

But you probably don't want them to do that either.|||If you want to go that route then how about:

declare @.IntValue int
exec xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServ er\MSSQLServer','LoginMode',@.IntValue OUTPUT
select @.IntValue as LoginMode

exec xp_regwrite 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServ er\MSSQLServer','LoginMode','REG_DWORD',1

exec xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServ er\MSSQLServer','LoginMode',@.IntValue OUTPUT
select @.IntValue as LoginMode

enable/disable Extende Stored Procedure in MSSQL2005

Hi,

Anybody now how to this programmaticly ? in registry or TSQL statement ?

Have you looked at the Surface Area Configuration tools - the feature configuration controls? That section allows you to disable buckets of XPs that are used by a feature you don't use, but you cannot deactivate only individual XPs in a bucket.

Thanks

Laurentiu

|||

if you refer only at running xp_cmdshell , i quote from Books Online

" Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Surface Area Configuration tool or by running the sp_configure "

for others xp's i didn't find anything

Empty recordsets and artificial records

I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.

The user now wants a record to be displayed on the report when there is
no record in the database - she wants it to display a value of '$0'
for the given T_MULTILIST.code record. I tried to explain the fact that
is not possible the way things stand at the moment. Basically I need
some type
of case statement that says 'if there is no record returned, create a
single record and set T_PAYMENT.amount = 0' AFTER each query has been
executed.
Anyone have any idea how to accomplish this?

SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL
T_SHIPPING_DETAIL ON
T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

UNION
SELECT DISTINCT
'English Language Arts, Kindergarten' as Rec_Type, 'English
Language Arts (Consumable)' as Super_Type,
'' as Other_Type, 'Continuing Contracts' as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description,
T_MULTILIST.code, T_PAYMENT.amount
FROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOIN
T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='0040')
and (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Up to 40 more UNION statements follow the above 2.wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:

Quote:

Originally Posted by

I'm currently running the following statement that is used in a Crystal
Report. Basically, a record is returned when the T_PAYMENT.amount
has a record in the database based on the value of the T_MULTILIST.code
field. Currently, if there is no record returned, there is no listing
in the report for the given T_MULTILIST.code.
>
>
The user now wants a record to be displayed on the report when there is
no record in the database - she wants it to display a value of '$0'
for the given T_MULTILIST.code record. I tried to explain the fact that
is not possible the way things stand at the moment. Basically I need
some type
of case statement that says 'if there is no record returned, create a
single record and set T_PAYMENT.amount = 0' AFTER each query has been
executed.

Sounds like you are looking for an outer join. Now, since I don't know
your tables, or what is being presented, the below may not be the
exact match, but you may be able to get the drift.

SELECT DISTINCT
'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts
(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'
as Proc_Type,
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
amount = coalesce(T_PAYMENT.amount, 0)
FROM T_MULTILIST T_MULTILIST
LEFT JOIN (T_PAYMENT T_PAYMENT
INNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAIL
ON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)
ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code)
INNER JOIN T_MULTILIST_GRADE T_MULTILIST_GRADE ON
T_MULTILIST.code=T_MULTILIST_GRADE.multilist_code,
T_ORDER, T_REQUISITION, T_REQUISITION_DETAIL
WHERE
T_ORDER.id = T_SHIPPING_DETAIL.order_id AND
T_REQUISITION.id = T_ORDER.requisition_id AND
T_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id AND
T_REQUISITION_DETAIL.latest_record_flag = 1 AND
T_REQUISITION.latest_record_flag = 1 AND
T_ORDER.latest_record_flag = 1
AND (T_MULTILIST.code='1040')
AND (T_MULTILIST.expiration_year >= '2006' )
AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'
AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,
'2006' + '0601' ) )

Permit me that the query is quite messy with it's mix of ANSI-join operators
and comma-style cross-join with the join conditions in the WHERE clause.
With outer joins in the mix, you should rewrite all to use ANSI joins.

Quote:

Originally Posted by

Up to 40 more UNION statements follow the above 2.

40? Ouch! But why? From the two segments you posted, it appears to me
that all that differs is the condition on T_MULTILIST.code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 5 Jul 2006 09:41:41 -0700, wgblackmon@.yahoo.com wrote:

Quote:

Originally Posted by

>I'm currently running the following statement that is used in a Crystal
>Report. Basically, a record is returned when the T_PAYMENT.amount
>has a record in the database based on the value of the T_MULTILIST.code
>field. Currently, if there is no record returned, there is no listing
>in the report for the given T_MULTILIST.code.

(snip)

Hi wgblackmon,

The statement you gave is horrible, and I'd be surprised if it shows
anything even resembling performance.

If I may assume that the other 40 UNION'ed SELECT statements all
resemble the first two, you have 42 almost equal SELECT statements with
only one code in the WHERE clause and some constants in the SELECT list
different. Why don't you include the 42 relevant codes and the
accompanying values for Rec_Type, Super_Type, Other_Type, and Proc_Type
in a table? Then, yoou can rewrite the complete monster with one single
query.

There are many other things at fault with your query too:
- Think about readability and maintainability: Why do you supply aliases
that are exactly equal to the table's name? Why do you mix "new style"
infixed joins with "old style" joins (using a comma-seperated list of
tables)? Why do you use nested INNER JOINs? Why don't you stick to one
style for using newlines and indentation to make your queries more
readable?
- Think about performance: Why do you use >'2006' + '0601' << to
denote a fixed date? If you use >'20060601' <<, it can be converted at
compile-time. Now, you're forcing string concatenation and conversion at
execution time. It gets even worse in the complicated datetime formula
that can be replaced with '20070601'. Also, why do you use "UNION"
instead of "UNION ALL", forcing SQL Server to search for duplicates if
the constants in the 42 SELECT lists are distinct anyway? And do you
really need a DISTINCT on the individual queries? It's often a token of
bad design.

Quote:

Originally Posted by

>The user now wants a record to be displayed on the report when there is
>no record in the database - she wants it to display a value of '$0'
>for the given T_MULTILIST.code record. I tried to explain the fact that
>is not possible the way things stand at the moment.

If you add the extra table suggested above, fulfilling this requirement
is as easy as changing an INNER JOIN to an OUTER JOIN and adding some
COALESCE functions in the SELECT list!

Here's a quick stab (retaining the DISTINCT for now, but do check if you
can leave it out). I have already added the user's wish.

First, set up and fill a table of codes and type descriptions:

CREATE TABLE dbo.CodeList
(Code char(4) NOT NULL PRIMARY KEY,
Rec_Type varchar(40) NOT NULL,
Super_Type varchar(40) NOT NULL,
Other_Type varchar(40) NOT NULL,
Proc_Type varchar(40) NOT NULL)
go
INSERT INTO CodeList (Code, Rec_Type, Super_Type, Other_Type, Proc_Type)
SELECT '1040', 'English Language Arts, Grade 1',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
UNION ALL
SELECT '0040', 'English Language Arts, Kindergarten',
'English Language Arts (Consumable)', '', 'Continuing Contracts'
-- etc for the other codes
go

Now, attempt to rewrite your query.

SELECT DISTINCT c.Rec_Type, c.Super_Type, c.Other_Type, c.Proc_Type,
mg.grade, m.description, m.code, p.amount
FROM T_PAYMENT AS p
INNER JOIN T_SHIPPING_DETAIL AS sd
ON p.transaction_id = sd.transaction_id
INNER JOIN T_MULTILIST AS m
ON m.code = sd.multilist_code
AND m.expiration_year >= '2006'
INNER JOIN T_MULTILIST_GRADE mg
ON m.code = mg.multilist_code
INNER JOIN T_ORDER AS o
ON o.id = sd.order_id
AND o.latest_record_flag = 1
INNER JOIN T_REQUISITION AS r
ON r.id = o.requisition_id
AND r.latest_record_flag = 1
AND r.requested_shipment_date >= '20060601'
AND r.requested_shipment_date < '20070601'
INNER JOIN T_REQUISITION_DETAIL AS rd
ON rd.requisition_id = r.id
AND rd.latest_record_flag = 1
RIGHT OUTER JOIN dbo.CodeList AS c
ON c.Code = m.code

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||On Thu, 06 Jul 2006 00:26:27 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>The statement you gave is horrible, and I'd be surprised if it shows
>anything even resembling performance.

From the original post, I would wonder if it was generated by Crystal
Reports. Code that bad does often come from such sources.

Roy Harvey
Beacon Falls, CT

Empty cube after date dimension added

In 2000, we could tell immediately whether there was data in the cube by running the SQL Statement that was used to process it. We could also troubleshoot join issues between dimension tables. In 2005 the process has changed. The cube update appears to be independent of the dimension update.

When I add a custom date dimension to my cube, all data within the cube disappears. My measures are empty, and I cannot get data back no matter what I slice and dice.

The SQL Statement used to process the cube returns records, however the cube browser is empty.

Has anyone else experienced this? How can we troubleshoot what data shows up in the cube, and if there is any data present?

Still looking for an answer on an easy way to troubleshoot missing data in the cube and relationships between dimensions & measure groups.

To resolve the problem above, I created a new dimension, and did not set it to a time dimension.