Binding WebService as Datasource

I've painstakingly managed to get a XmlDocument from a webservice and run this to retrieve data.

However, i cannot bind any of the returned elements to anything. Any help appreciated.

Are you using the XML data processing extension? What is the Query that you are using? More specifically, what is the Element Path for the query?

For more information, take a look at the following article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/RepServXMLDS.asp

Ian

BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.

Hello,

SQL Server 2005

I have tried and cannot return any rows either though there are records that should be returned, using the sql below.

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007

In the database scheduleDate is a dateTime datatype.

In the database I have copied and pasted. 23/05/2007 00:00:00

I tried the following

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'

And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Many thanks for any help with this,

Steve

First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:

Code Snippet

declare @.dt datetime

set @.dt = 20/5/2007

select @.dt

You could rewrite second query with explicit convert:

Code Snippet

SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)

Or use

Code Snippet

SET DATEFORMAT dmy

for setting default dateformat

|||That is because the implicit conversion thinks the date is a US date.

Do this:

.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)

or

SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||

steve_1_rm,

Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.

SELECT *

FROM Calls

WHERE

clientName IN ('Bankside Engineering Limited')

AND scheduleDate BETWEEN '20070527' AND '20070530'

AMB

|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||

What do you get when you issue

Code Snippet

SELECT CONVERT(varchar(10), [Date], 103) as [Date], *

FROM Calls

WHERE [Date] BETWEEN '20070521' AND '20070531'