conversation_handle in Queue doesn't match "SEND ON CONVERSATION..." value.

When examining the "conversation_handle" column value in the Queue (from: "select * from ProcessQueue"), I find the value of the conversation_handle appears to be different from the "conversation handle" ("SEND ON CONVERSATION @.conversationHandle...") on which the message was sent.

I'm trying to gather all messages for a given Dialog conversation, but am not able to do this because the conversation_handle doesn't appear consistent between messages in the Dialog.

note: the conversation_handle of "BEGIN CONVERSATION TIMER (@.conversationHandle)..." message does appear to be correct in the Queue.

Thoughts on why the conversation_handle listed in the Queue might be different than that of the
"SEND ON CONVERSATION @.conversationHandle..." command that sends the message?

-
-
-

ALTER PROCEDURE [dbo].[SendMessageStoredProcedure]
AS

-- Declare Conversation Handle
DECLARE @.conversationHandle uniqueidentifier;

-- Declare Message
DECLARE @.message nvarchar(max);

-- Begin Transaction
BEGIN TRANSACTION;

-- Begin Dialog from Service1 to Service2 on Contract
BEGIN DIALOG @.conversationHandle
FROM SERVICE ExecuteProcess
TO SERVICE 'ExecuteProcess'
ON CONTRACT Process;

FROM DEBUGGER: @.conversationHandle has value of AC8DF4E0-9F38-DB11-96A2-000CF1D46448

-- Set Message value
DECLARE @.requestDocument xml
SET @.requestDocument = N'<ProcessRequestMessage/>';

-- Start Converstation time
BEGIN CONVERSATION TIMER (@.conversationHandle) TIMEOUT = @.queueSeconds;

-- Send Message
SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE ProcessRequest (@.requestDocument);

-- Commit Transaction
COMMIT TRANSACTION;

-
-
-

RESULTS OF: select * from ProcessQueue;

status priorty q_order conversation_group_id conversation_handle msg_s_# service_name ser_id srv_cont_name ...
1 0 1 AD8DF4E0-9F38-DB11-96A2-000CF1D46448 AC8DF4E0-9F38-DB11-96A2-000CF1D46448 -2 ExecuteProcess 65536 Process 65536 http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer 5 E NULL
1 0 0 AE8DF4E0-9F38-DB11-96A2-000CF1D46448 AF8DF4E0-9F38-DB11-96A2-000CF1D46448 0 ExecuteProcess 65536 Process 65536 ProcessRequest 65536 X 0xFFFE3C004400610074006100620...

Conversation handles uniquely identify a conversation endpoint. Since a dialog has two endpoints (the initiator and the target), it will have two conversation handles. The handle returned by the BEGIN DIALOG statement is the conversation handle at the initiator whereas the one in the target queue is the handle for the target endpoint of the dialog.

Conversation IDs, on the other hand, uniquely identify a conversation. Hence for a dialog, you will notice that the conversation ID is the same at the initiator and the target.

You can see converstaion_handle, conversation_id and other properties of your conversation endpoints by looking at the sys.conversation_endpoints view.

|||Thanks! ...exactly the clarification I needed.

conversation_handle in Queue doesn't match "SEND ON CONVERSATION..." value.

When examining the "conversation_handle" column value in the Queue (from: "select * from ProcessQueue"), I find the value of the conversation_handle appears to be different from the "conversation handle" ("SEND ON CONVERSATION @.conversationHandle...") on which the message was sent.

I'm trying to gather all messages for a given Dialog conversation, but am not able to do this because the conversation_handle doesn't appear consistent between messages in the Dialog.

note: the conversation_handle of "BEGIN CONVERSATION TIMER (@.conversationHandle)..." message does appear to be correct in the Queue.

Thoughts on why the conversation_handle listed in the Queue might be different than that of the
"SEND ON CONVERSATION @.conversationHandle..." command that sends the message?

-
-
-

ALTER PROCEDURE [dbo].[SendMessageStoredProcedure]
AS

-- Declare Conversation Handle
DECLARE @.conversationHandle uniqueidentifier;

-- Declare Message
DECLARE @.message nvarchar(max);

-- Begin Transaction
BEGIN TRANSACTION;

-- Begin Dialog from Service1 to Service2 on Contract
BEGIN DIALOG @.conversationHandle
FROM SERVICE ExecuteProcess
TO SERVICE 'ExecuteProcess'
ON CONTRACT Process;

FROM DEBUGGER: @.conversationHandle has value of AC8DF4E0-9F38-DB11-96A2-000CF1D46448

-- Set Message value
DECLARE @.requestDocument xml
SET @.requestDocument = N'<ProcessRequestMessage/>';

-- Start Converstation time
BEGIN CONVERSATION TIMER (@.conversationHandle) TIMEOUT = @.queueSeconds;

-- Send Message
SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE ProcessRequest (@.requestDocument);

-- Commit Transaction
COMMIT TRANSACTION;

-
-
-

RESULTS OF: select * from ProcessQueue;

status priorty q_order conversation_group_id conversation_handle msg_s_# service_name ser_id srv_cont_name ...
1 0 1 AD8DF4E0-9F38-DB11-96A2-000CF1D46448 AC8DF4E0-9F38-DB11-96A2-000CF1D46448 -2 ExecuteProcess 65536 Process 65536 http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer 5 E NULL
1 0 0 AE8DF4E0-9F38-DB11-96A2-000CF1D46448 AF8DF4E0-9F38-DB11-96A2-000CF1D46448 0 ExecuteProcess 65536 Process 65536 ProcessRequest 65536 X 0xFFFE3C004400610074006100620...

Conversation handles uniquely identify a conversation endpoint. Since a dialog has two endpoints (the initiator and the target), it will have two conversation handles. The handle returned by the BEGIN DIALOG statement is the conversation handle at the initiator whereas the one in the target queue is the handle for the target endpoint of the dialog.

Conversation IDs, on the other hand, uniquely identify a conversation. Hence for a dialog, you will notice that the conversation ID is the same at the initiator and the target.

You can see converstaion_handle, conversation_id and other properties of your conversation endpoints by looking at the sys.conversation_endpoints view.

|||Thanks! ...exactly the clarification I needed.

conversation handle

hi all, i am having a hard time getting the conversation handle id to match
..
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. I
f
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>sqlsql

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query?
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tin
The conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query?
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>
|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:

> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>
|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:

> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>

conversation handle

hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> > hi all, i am having a hard time getting the conversation handle id to
> > match ...
> >
> > scripts that create the relevant objects ...
> >
> > create message type QueryMessage validation = none
> > create contract QueryContract (QueryMessage sent by initiator)
> > create queue QueueSender
> > create queue QueueReceiver
> > create service Sender on queue QueueSender
> > create service Receiver on queue QueueReceiver (QueryContract)
> >
> > send with conversation handle id ...
> >
> > begin transaction
> > declare @.conversationhandle uniqueidentifier;
> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> > begin dialog @.conversationhandle
> > from service [Sender]
> > to service 'Receiver'
> > on contract [QueryContract]
> > with encryption = off;
> > send on conversation @.conversationhandle
> > message type [QueryMessage] ('blah blah blah;');
> > commit transaction
> >
> > after i ran the above send query a few times, and do the below select ...
> >
> > select conversation_group_id, conversation_handle, cast(message_body as
> > varchar(1000)) from QueueReceiver
> >
> > i got all kinds of different conversation_group_id and
> > conversation_handle?
> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> > specified
> > in the send query'
> >
> > and when i do the below receive query, it says:
> > Msg 8426, Level 16, State 20, Line 1
> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> > found.
> >
> > receive top(1) convert(varchar(1000),message_body) as message
> > from QueueReceiver
> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >
> > please help! thanks in advance!
> >
> > - tin
> >
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>> The conversation handle is an output parameter for the BEGIN DIALOG
>> statement. Each time you run the statement, a new conversation will be
>> created and the @.conversationhandle will get a new value assigned to it.
>> If
>> the variable had a previous value, it will be overwriten.
>> So the conversation with the handle
>> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> was never created, hence the error on the RECEIVE statement.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "tin" <tin@.discussions.microsoft.com> wrote in message
>> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>> > hi all, i am having a hard time getting the conversation handle id to
>> > match ...
>> >
>> > scripts that create the relevant objects ...
>> >
>> > create message type QueryMessage validation = none
>> > create contract QueryContract (QueryMessage sent by initiator)
>> > create queue QueueSender
>> > create queue QueueReceiver
>> > create service Sender on queue QueueSender
>> > create service Receiver on queue QueueReceiver (QueryContract)
>> >
>> > send with conversation handle id ...
>> >
>> > begin transaction
>> > declare @.conversationhandle uniqueidentifier;
>> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> > begin dialog @.conversationhandle
>> > from service [Sender]
>> > to service 'Receiver'
>> > on contract [QueryContract]
>> > with encryption = off;
>> > send on conversation @.conversationhandle
>> > message type [QueryMessage] ('blah blah blah;');
>> > commit transaction
>> >
>> > after i ran the above send query a few times, and do the below select
>> > ...
>> >
>> > select conversation_group_id, conversation_handle, cast(message_body as
>> > varchar(1000)) from QueueReceiver
>> >
>> > i got all kinds of different conversation_group_id and
>> > conversation_handle?
>> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
>> > specified
>> > in the send query'
>> >
>> > and when i do the below receive query, it says:
>> > Msg 8426, Level 16, State 20, Line 1
>> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
>> > found.
>> >
>> > receive top(1) convert(varchar(1000),message_body) as message
>> > from QueueReceiver
>> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> >
>> > please help! thanks in advance!
>> >
>> > - tin
>> >
>>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> > ah, no wonder. another question:
> >
> > if i want an application to send messages to 2 applications, then i guess
> > i
> > will have to create two queues? i was trying to use one queue with 2
> > conversation handles.
> >
> > many thanks.
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> The conversation handle is an output parameter for the BEGIN DIALOG
> >> statement. Each time you run the statement, a new conversation will be
> >> created and the @.conversationhandle will get a new value assigned to it.
> >> If
> >> the variable had a previous value, it will be overwriten.
> >>
> >> So the conversation with the handle
> >> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> was never created, hence the error on the RECEIVE statement.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> HTH,
> >> ~ Remus Rusanu
> >>
> >> SQL Service Broker
> >> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
> >>
> >>
> >> "tin" <tin@.discussions.microsoft.com> wrote in message
> >> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> >> > hi all, i am having a hard time getting the conversation handle id to
> >> > match ...
> >> >
> >> > scripts that create the relevant objects ...
> >> >
> >> > create message type QueryMessage validation = none
> >> > create contract QueryContract (QueryMessage sent by initiator)
> >> > create queue QueueSender
> >> > create queue QueueReceiver
> >> > create service Sender on queue QueueSender
> >> > create service Receiver on queue QueueReceiver (QueryContract)
> >> >
> >> > send with conversation handle id ...
> >> >
> >> > begin transaction
> >> > declare @.conversationhandle uniqueidentifier;
> >> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> > begin dialog @.conversationhandle
> >> > from service [Sender]
> >> > to service 'Receiver'
> >> > on contract [QueryContract]
> >> > with encryption = off;
> >> > send on conversation @.conversationhandle
> >> > message type [QueryMessage] ('blah blah blah;');
> >> > commit transaction
> >> >
> >> > after i ran the above send query a few times, and do the below select
> >> > ...
> >> >
> >> > select conversation_group_id, conversation_handle, cast(message_body as
> >> > varchar(1000)) from QueueReceiver
> >> >
> >> > i got all kinds of different conversation_group_id and
> >> > conversation_handle?
> >> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> >> > specified
> >> > in the send query'
> >> >
> >> > and when i do the below receive query, it says:
> >> > Msg 8426, Level 16, State 20, Line 1
> >> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> >> > found.
> >> >
> >> > receive top(1) convert(varchar(1000),message_body) as message
> >> > from QueueReceiver
> >> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> >
> >> > please help! thanks in advance!
> >> >
> >> > - tin
> >> >
> >>
> >>
> >>
>
>

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

publicpartialclassKonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

privatevoid KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

privatevoid KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

ProtectedSub Button1_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer AsByte(), f AsString, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

WithMe.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

EndWith

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

EndSub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Controlling PDF export filename

Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,

--randy

If you are using the ReportViewer control, you can set the DisplayName property. This will affect the export file name and the document map root node label.

If you are using direct url access to the report server, the name can't be changed. In that case, you would need to implement your own export UI that retrieved the exported report from the server and relayed it to the client with a custom content disposition header.

|||

Hello,

I need help on this isse too, it sounded that you somehow exported it to pdf.

I am using SQL 2006 and Reporting Services 2005 and rendering report in my asp.net application. I am trying to export reports under a button click, can anyone give me a sample code?

Thanks

|||

Dear Brian,

I am coding with C# and can not find the reportViewer's DisplayName property?

Below is my code for the form that I display the reportviewer in where and what code should I insert so that I change the filename automatically?

Thanks in advance.

Best regards,

Kaan Demirtas

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace GECKOV2

{

public partial class KonfirmasyonRaporForm : Form

{

public KonfirmasyonRaporForm()

{

InitializeComponent();

}

private void KonfirmasyonRaporForm_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar' table. You can move, or remove it, as needed.

this.KonfirmasyonVerilenOrderlarTableAdapter.Fill(this.KonfirmasyonDataSet.KonfirmasyonVerilenOrderlar);

this.reportViewer1.RefreshReport();

}

private void KonfirmasyonRaporForm_FormClosed(object sender, FormClosedEventArgs e)

{

;

if (MessageBox.Show("Raporda yer alan urunlerin konfirmasyon bilgilerini gonderildi olarak isaretlemek istermisiniz?", "Konfirmasyon islemi onaylama", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes)

{

string konfirmeEdildiDiyeIsaretle = "update siparisler set FabrikaOnayiMusteriyeGonderildi=1 from KonfirmasyonVerilenOrderlar where siparisler.OrderID=KonfirmasyonVerilenOrderlar.KonfirmasyonVerilenOrderID";

MessageBox.Show(Program.SqlKomutIsle(konfirmeEdildiDiyeIsaretle));

}

}

}

}

|||

What a huge PITA! Is MS going to fix this anytime soon? This should be something that can be set regardless of whether you are using direct url access or not.

|||

>> I am trying to export reports under a button click, can anybody give me a sample code?

Sure. <s> Is this remote or local mode ? And did you want the output to show up in a window?

In this example, it happens to be a local mode report, and I bring the PDF up in a window... it is definitely not the only way... You can see where I am setting the temporary file name on the server -- considering that the user can decide what filename to save it to on the client side, I didn't see the point of getting all excited about the server-side file name, but you'll get the picture.

Also, I wrote this example for somebody else, and in that case we needed to adjust the datasource before export -- probably not true in your case, but it won't hurt to keep it in, it's not much code, and you'll get a better idea of what is really going on here, how to add in whatever you do need to add.

If it turns out you're in remote mode, it's doable also.

HTH,

>L<

Code Snippet

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

Control Result of ExecuteScalar

The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL.

For Example:

If this doesn't find a match:

select amount from Lookup Where application = @.app

Then I'd like to return:

select amount from Lookup Where application = "DEFAULT"

My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value? Or someone determine how many records are in the last SQL to execute?

Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query?

ExecuteScalar returns only one value -what that value is depends upon the stored procedure.

Yes, you can return any one value from any combination of queries.

To best assist you, please post the entire stored procedure, a description of what results are desired.

|||

I'm including my SP below. The Lookup table has a column named amount. If the CASE statement, when method="LIST", I'd like to return Lookup.amount if there are no matching records in the LookupList table.

USE [SharedDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PR_LookupPrice]

(

@.app varchar(8),

@.billcode varchar(4),

@.code varchar(20),

@.value sql_variant

)

AS

SET NOCOUNT OFF;

SELECT CASE

WHEN method = 'SET'

THEN (select amount from Lookup Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'VAR'

THEN (Select LookupVariable.amount from Lookup INNER JOIN LookupVariable

On Lookup.lookupid = LookupVariable.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and low <= @.value and high >= @.value)

WHEN method = 'COMP'

THEN (select SUM(LookupCompounding.amount) from Lookup INNER JOIN LookupCompounding

On Lookup.lookupid = LookupCompounding.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'LIST'

THEN (select LookupList.amount from Lookup INNER JOIN LookupList

On Lookup.lookupid = LookupList.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and lookuplist.value = @.value)

END AS 'result'

FROM Lookup WHERE application = @.app AND billcode = @.billcode AND code = @.code

|||

Thanks, that helps.

I've revised the procedure for readibiltiy.

Code Snippet

ALTER PROCEDURE [dbo].[PR_LookupPrice]
( @.App varchar(8),
@.BillCode varchar(4),
@.Code varchar(20),
@.Value sql_variant
)
AS

BEGIN

SET NOCOUNT OFF;

DECLARE @.ReturnValue decimal(10,2)

SELECT @.ReturnValue = CASE Method
WHEN 'SET'
THEN Amount
WHEN 'VAR'
THEN (SELECT lv.Amount
FROM Lookup l
INNER JOIN LookupVariable lv
ON l.LookupID = lv.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ( lv.Low <= @.Value
AND lv.High >= @.Value
)
)
)
WHEN 'COMP'
THEN (SELECT sum( lc.Amount )
FROM Lookup l
INNER JOIN LookupCompounding lc
ON l.LookupID = lc.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
)
)
WHEN 'LIST'
THEN (SELECT isnull( ll.Amount, l.Amount )
FROM Lookup l
INNER JOIN LookupList ll
ON l.LookupID = ll.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ll.Value = @.Value
)
)
END
FROM Lookup
WHERE ( Application = @.App
AND BillCode = @.BillCode
AND Code = @.Code
)

IF @.ReturnValue IS NULL
SELECT Amount
FROM Lookup
WHERE Application = "DEFAULT"

RETURN @.ReturnValue

END

The changes are in yellow, I think that everything else is just formatting.

Contains(*) question

When I do a full text index on 2 columns, then do a query like below, it
appears to only match rows where 1 column of the index satisfies the
criteria. I want the query to return all rows where a combination of the 2
columns satisfy the query. Do I have something set up wrong?
SELECT * FROM <table>
WHERE CONTAINS(*,'"lord","rings","dvd"')
For the following data, no row is returned, but I want it to be
column 1 contains 'lord' and 'rings'
column 2 contains 'dvd'
For the following data, a row is returned.
column 1 contains 'lord' and 'rings' and 'dvd'How about :
SELECT * FORM <table>
WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
>
>|||But I need the results to contain all 3 terms. An 'or' would return results
that contain 1 of the 3.
"Mike Hodgson" wrote:

> How about :
> SELECT * FORM <table>
> WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>|||On Sun, 9 Oct 2005 19:17:01 -0700, Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
Hi Brian,
I don't know much about full text indexing, so this one is a shot in the
dark - but would this work?
SELECT col01, col02, ...
FROM YourTable
WHERE CONTAINS (*, '"lord" AND "rings"')
AND CONTAINS (*, '"dvd"')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I misunderstood what you were trying to achieve. It seems like
the media type ought to be in its own column and referenced with normal
string operators rather than the CONTAINS() predicate. Something like:
select MediaTitle, MediaType, ... from Media
where CONTAINS (MediaTitle, '"lord" AND "rings"')
and MediaType = "dvd"
With a nonclustered index on the MediaType column, that would work much
more efficiently than a couple full-text searches. If you cannot change
the design then Hugo's suggestion looks like it should work, but that's
a really poor design (just having all the metadata jumbled together like
that) - you may as well just have a bunch of text files containing the
search terms in a directory structure and use the Windows explorer
search function to trawl through the text files. Why store data in a
relational database if it's not relational data?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:
>But I need the results to contain all 3 terms. An 'or' would return result
s
>that contain 1 of the 3.
>"Mike Hodgson" wrote:
>
>|||Brian,
This is a FAQ in the fulltext newsgroup, so I've blogged about how to do FTS
across columns - "SQL Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
Enjoy,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OYUovffzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sorry, I misunderstood what you were trying to achieve. It seems like
> the media type ought to be in its own column and referenced with normal
> string operators rather than the CONTAINS() predicate. Something like:
> select MediaTitle, MediaType, ... from Media
> where CONTAINS (MediaTitle, '"lord" AND "rings"')
> and MediaType = "dvd"
> With a nonclustered index on the MediaType column, that would work much
> more efficiently than a couple full-text searches. If you cannot change
> the design then Hugo's suggestion looks like it should work, but that's
> a really poor design (just having all the metadata jumbled together like
> that) - you may as well just have a bunch of text files containing the
> search terms in a directory structure and use the Windows explorer
> search function to trawl through the text files. Why store data in a
> relational database if it's not relational data?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>

CONTAINS phrase behavior

Shouldn't matter.
Is the catalog up to date?
Are you searching on the data that is returned? i.e. is the match on data that you are not seeing.
It should just get the phrase.good ideas! Thank you. I just rebuilt and repopulated the index, same negative results. I deleted the index and recreated it. I doubled checked the columns that were indexed compare to the results that are returned. Nothing hidden. I tried "product select" and it returned 0, so it's not really like an AND afterall. Just a flook I guess.

I'm sure there's a user end problem in here somewhere since the query works as expected with other phrases.