Convert a time field in the select statement of the query

Hi,

I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.

Can anyone help me please?

Thx

I'm not entirely sure what you're trying to get at, but SQL Server has the following function that will get you the current UTC time:

GETUTCDATE()

If you are trying to convert the time from the timestamp to the corresponding local timestamp value, you would need to know the offset and add this time to the existing field.

Hope this helps some.

Simone

|||

Yeah!! That's the solution...... 2 weeks of deep frustrations and just such a simple solution..... wow!

Thx a lot!!!

|||

No problem, glad it worked for you. If you mark this as the answer it will better help others. Thanks.

Simone

sqlsql

Conversion Of Oracle Date Time To Sql Server Date Time in SSIS

This is driving me nuts..

I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.

I'm struggling with this for a quite a while and I'm not able to get it working.

I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000

the space in MM : SS is intentional here, since without that space it appread as smiley

I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data

I'm struck with error for hours now. Any pointers would be helpful.

Thanks

Any idea why this simple straight forward string to date time conversion keeps failing with the error message, conversion failed due to potential loss of data?

The input values looks like this 20070511 23:06:30, what is that I'm missing here for the conversion to fail?

Thanks

|||

As much as it sounds ridiculous, looks like SSIS does not understand YYYYMMDD format...

Thanks to Jamie Thomson' s post here, which solved the problem.

http://blogs.conchango.com/jamiethomson/archive/2006/06/26/SSIS_3A00_-Parsing-datetime-values.aspx

Conversion for Time

I can get my DB to accept my date by doing the following: row.Item("RequestDate") =Me.fullDate.Date --I have fulldate dimensioned as date above. However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says

?row.Item("BeginTime")
#6:00:00 AM# {Date}
[Date]: #6:00:00 AM#

row.Item("BeginTime") =CDate(ddlBegin.SelectedValue & beginAMPM)
row.Item("EndTime") =CDate(ddlEnd.SelectedValue & endAMPM)
The SQL Error I get is the following:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:

Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)Line 336: DsVacationData1.RequestData.AddRequestDataRow(row)Line 337: SqlDataAdapter2.Update(DsVacationData1)Line 338: DsVacationData1.AcceptChanges()Line 339: End Sub

Thanks for any help.Ok i figured out what I was doing wrong, to a point. I got it to store my time but it also put in today's date along w/the time is there a way to just enter in the time w/no date?|||There's no Time data type. There's only datetime andsmalldatetime. So the answer to your question, only way is toutilize char, var, nchar, nvar or add in a universal date that will beignored.
|||

The cause of the problem is ANSI SQL NULL is an unknown while .NET NULL is an empty string so the difference is causing the overflow. I found two VB code and a C# link with code. Hope this helps.
http://www.inq.net/WebLog/dbalzer/archive/2005/06/16/125145.aspx

(If SomeDate = DateTime.MinValue Then
cmd.Parameters("@.SomeDate").Value = DBNull.Value
Else
cmd.Parameters("@.SomeDate").Value = SomeDate
End If)
First codeblock

(Public Function chkDateParam(ByVal d As Date, ByRef sqlParam As
SqlParameter)
Try
If d = System.DateTime.MinValue Then
sqlParam.Value = DBNull.Value
Else
sqlParam.Value = d
End If

Catch ex As NullReferenceException

'if the field is blank, str will = null. so set the param to
null too!
sqlParam.Value = DBNull.Value

End Try
End Function )
Second codeblock

sqlsql

Conversion errors on date field from Access to SQL server7

Hi
1st time trying to migrate Access 2000 tables to SQLServer7.
The tables transport but I'm getting errors on the data transfer.

The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.

When the error occurs in transport it reads:
Error at Destination for Row number 310...
Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.

**What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.

I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!

Any ideas on how I get the SQL Server to accept these records??use datetime rather than smalldatetime.

valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999|||I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!

If I change those to 01/01/1900...they will transfer.|||I must be missing the point... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?|||Here goes...there were some records that had dates like this:
01/01/9194
01/01/1897
01/01/1583 etc...

You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999

Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.

[And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].

Hope this clears it up.|||thanks!

You may have other problems here, consider the following code:

declare @.dt datetime, @.vc varchar(100)
set @.dt = '01/01/9194'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1897'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1583'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc

As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?

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

Context connection and transactions

All -
Facts:
(a) SqlConnection allows only one "Context connection" to be opened at a tim
e.
(b) SqlConnection doesnt support parallel transactions. (calling a second
BeginTransaction before committing the first BeginTransaction throws
exception)
Problem:
Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
registered as SQLProcedure, is invoked.
Inside this code (which is now in SqlServer context) - i open a
SqlConnection as "context connection".
All the SqlCommand objects that uses this context connection, need the same
transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
different transaction. (lets say TransB)
How to do this?
Calling BeginTransaction twice blows up.
Creating 2 seperate instances of SqlConnection with context connection =
true, also blows up.
Remember that somehow i might want to rollback TransA but always committ
TransB. For instance
try
{
// Do some actions
// SqlCommands that use TransA and TransB will be executed here
transA.Committ(); // things are fine - committ A
}
catch
{
transA.Rollback(); // things are incorrect - rollback A
}
finally
{
transB.Committ(); // Always comitt the logs...
}
How to do this when we are in context connection?
Regardz
Grafix."Grafix" <Grafix@.discussions.microsoft.com> wrote in message
news:54F45F4C-4F6B-44D7-8B13-0D55F13BB75C@.microsoft.com...
> All -
> Facts:
> (a) SqlConnection allows only one "Context connection" to be opened at a
> time.
> (b) SqlConnection doesnt support parallel transactions. (calling a second
> BeginTransaction before committing the first BeginTransaction throws
> exception)
> Problem:
> Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
> registered as SQLProcedure, is invoked.
> Inside this code (which is now in SqlServer context) - i open a
> SqlConnection as "context connection".
> All the SqlCommand objects that uses this context connection, need the
> same
> transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
> different transaction. (lets say TransB)
> How to do this?
> Calling BeginTransaction twice blows up.
> Creating 2 seperate instances of SqlConnection with context connection =
> true, also blows up.
> Remember that somehow i might want to rollback TransA but always committ
> TransB. For instance
>
You will have exactly the same issue in TSQL. SQL Server does not support
autonomous transactions.
A common workaround is to write data into a table variable (or List<T> ), and
do something with it after the transaction is rolled back.
David|||I haven't gone into CLR stored procedures too much. But can't you add this
one SqlCommand (LogCmd) that needs a different transaction
into a seperate class and use the transaction option as requiresNew and with
the method haveing an autocommit tag to be true. and then call it from the
parent transaction.|||> one SqlCommand (LogCmd) that needs a different transaction
> into a seperate class and use the transaction option as requiresNew
There is the problem.
I am not able to have 2 active transaction scopes at the same time.
Remember LogCmd will be executed multiple times inbetween other regular
xxxCmd.
Which means i want 2 active transaction scopes (each with Option
"RequiresNew")
Unfortunately when i am in context connection (C# sp), i am not allowed to
create 2 connections that i can enlist with the TransactionScope.
I tried ur suggestion with the new .NET 2.0's
System.Transactions.TransactionScope class (Promotable transaction) for
seeing if i can enlist a single connection with multiple transactions - but
the problem is the same. So bad that they have introduced new Options like
TransactionScopeOption.Suppress and i am not able to use it still.
With one SqlConnection - u cannot associate with 2 different transaction
scopes.
And this C# sps allow only one connection at a time.
Seems i have to follow David Browne's suggestion of in-memory datastructure
to hold the logs and committ at the end.

Constructing a View into time dependant data

1. I have a table with data like this:

PersonID
DateTime
Temperature
Pressure

2. I want to build a view into this table so that it shows up as follows:

PersonID DateTime1 DateTime2 DateTime3 .....
1 Pressure1 Pressure2 Pressure3 ......
1 Temperature1 Temperature2 Tempearture3 .....
2 :
:

how would I do this?

Hello,

Your resultset seems to contain and thus represent two disparate data sets (one of Pressure and one of Temperature). Why would you want to do this when there would be no way to determine what is pressure and what is temperature?

Regardless, you would use the new PIVOT operator, and if you did want to include two data sets, you would need to union the results of two separate pivots in your view. BOL has some good examples of using PIVOT.

Cheers,

Rob

|||Thanks for responding!

I will construct 2 separate views - one for temperarure and one for pressure.
But I am confused as to how to do it for even just temperature.

Is there a way of doing this without Pivot? I am using SQL 2000|||

Hello,

Have a look at http://dotnetjunkies.com/WebLog/thomasswilliams/archive/2005/10/23/133383.aspx for starters. The actual solution will depend upon your data.

Cheers,

Rob

constraint/trigger in sql server in Asp.net

In my Project

i want to check the date at the time of insert in A-Table

that it should be Greater than (>) Date Defined in B-Table

Note:-B-table have only one record

so plz tell me how can i do using Sql-Server Backend only

Hopefully you are using a stored proc wherein you could:

CREATE PRO...@.param1 ..@.param2datetimeDeclare @.datefromBdatetimeSELECT @.datefromB = datecolumnFROM tableBIF @.param2 > @.datefromBBEGIN--Do the insert hereENDELSEBEGIN

-- dp whatever needs to be done return 0END

|||But where i defined that stored procedure in my code|||Looks like you need DB 101. Check out documentation for "stored procedures".

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!