Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hi Remus

What if I need multiple clients to read (RECEIVE) the same message?

Would it be possible?

Thanks

No.

A message can only be received once. Normally the first RECEIVE statement removes it from the queue, so no other RECEIVE can find the same message.

Also there is no way for the clients to specify the message to be received. With a WHERE clause the RECEIVE statement at most can restrict the result set to a particular conversation, but not to a particular message.

And finally RECEIVE statement is always executing in READ COMMITED isolation level, so two clients cannot receive messages from the same conversation group in different transactions, since each RECEIVE will attempt to place an exclusive lock on the conversation group and only one transaction can have an exclusive lock at any given moment.

HTH,
~ Remus

|||

If you are looking at a publish/subscribe type scenario, where you want messages to be delivered to multiple services, you could implement a service that maintains a list of subscriber services and upon receiving a message, sends a copy out each of its subscribers. See the sample on Remus' blog:

http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx

|||

Hi Rushi/Remus

I tested that example, setting the same subscription from two different clients. Then I sent a publication, and read messages.

For what I understand in that example when a client subscribes for a particular publication, his conversationID is saved on a table.

When a publication occurs a procedure sends messages to all subscribers, using that conversationID.

BUT, in case there are two subscriptions and a single client is listening for messages, two identical messages are read by the client.

In case there are two clients listening a lot of confusion, sometimes one client gets two messages, sometimes one, sometimes nothing...

I was expecting , since the conversationID seems to address to a single endpoint, only one message...

|||

Assuming that on a publish/subscribe scenario each client must create a unique subscription, I realize that each client have to create its own queue and service.

The problem is sending messages then.

The initiator should send the same message to all queues, but how? The number of queues created is not defined, is there a way to do it?

Is my theory correct? Or am I on the wrong direction?

Thanks for helping

|||

The subscribers are individual conversations. If they are on the same queue, then you must use the RECEIVE ... FROM queue WHERE conversation_handle = ... syntax to retrieve only the notifications for a given client (subscription).

If you use the RECEIVE w/o a WHERE clause, then the clients will mix the notifications, if they are on the same queue.

In the pub/sub sample at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx the initiator doesn't know nor need to how many clients/queues are there. It will iterate through subscriptions and send a message to each one. Clients can be on the same queue or on different queue, it doesn't matter. The subscription notifications are all reply messages (from target to initiator, since is the client that initiates the subscription), so the pub/sub service does not need to know upfront how many clients are there, it just sends replies on the existing dialogs.

HTH,
~ Remus

|||

Thanks for the clarification Remus.

Now it's working good!

|||

Hi,

I have a couple of questions to make.

How can i trigger notifications to my application ( C#)

without hanging in WaitFor Operation?

It's possible for broker service to call some remote

object that my application provide ?

Can Publish/Subscribe using Broker Service be used

for a low latency notifications (150ms ) max with milions of

messages published per second?

Thanks in advance

Srgio

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!
<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegr oups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David
|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/pr...oads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:[vbcol=seagreen]
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You ca
n
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. T
he
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/pr...nloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious
Thanks heaps!

Constant WAITFOR(RECEIVE) in Profile Trace?

After seeing abnormally high load on the sql server this morning, i've
been investigating. After ruling out the usual sources, i ran a trace
on the DB, limited to showing only queries taking longer than 2000
miliseconds, only to find a constant stream of:
WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
TIMEOUT 5000
it stops ~5000ms, then another one occurs!
Is this causing my load? I've had no luck googling for that message,
so not sure how common it seems to be .. Only thing I can think of is
last week we set up database mail, is this related? The field
"message_body" seems to indicate perhaps it is related to this?
Is it normal? or is this my CPU load problem?
How do I fix it? Thanks in advance!<vidguide@.gmail.com> wrote in message
news:1170282805.989902.152780@.j27g2000cwj.googlegroups.com...
> After seeing abnormally high load on the sql server this morning, i've
> been investigating. After ruling out the usual sources, i ran a trace
> on the DB, limited to showing only queries taking longer than 2000
> miliseconds, only to find a constant stream of:
> WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),
> TIMEOUT 5000
> it stops ~5000ms, then another one occurs!
> Is this causing my load? I've had no luck googling for that message,
> so not sure how common it seems to be .. Only thing I can think of is
> last week we set up database mail, is this related? The field
> "message_body" seems to indicate perhaps it is related to this?
> Is it normal? or is this my CPU load problem?
> How do I fix it? Thanks in advance!
>
I am not sure exactly what process is causing this, but shouldn't be a
performance problem. This is a normal usage pattern for a service broker
application reading a queue.
The WAITFOR RECEIVE is the command to perform a blocking read on a queue.
It consumes no resources while waiting, times out after 5000ms and
immediately starts another blocking read.
David|||It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
find more information here:
http://msdn2.microsoft.com/en-us/library/ms181893.aspx
As David noted in his reply, this should not be a performance hit. RECIEVE
just waits until it either gets a message in it's queue or it times out. The
WMI provider is just launching another RECIEVE when one times out.
--
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Feb 1, 12:53 pm, "Alan Brewer [MSFT]" <ala...@.microsoft.com> wrote:
> It is set up by the SQL Server 2005 WMI Provider for Server Events. You can
> find more information here:
> http://msdn2.microsoft.com/en-us/library/ms181893.aspx
> As David noted in his reply, this should not be a performance hit. RECIEVE
> just waits until it either gets a message in it's queue or it times out. The
> WMI provider is just launching another RECIEVE when one times out.
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks very much for your information. Turns out it was one of the
clients causing the load, i'd just missed it on the first sweep, these
messages were just new/repeating, so looked suspicious :)
Thanks heaps!