Hi,

I'm getting the following error (loosely translated from the German) while
trying to query a view in a linked server:

View
SELECT AUFTLT FROM AUFTRAEGE WHERE (AUFTLT > convert(datetime,
'20030714',112 ))

Error:
During the converstion of a char-datatype in a datetime-datatype, the
datetime-value is outside the allowed values
(Original for the german speakers :-) Bei der Konvertierung eines
char-Datentyps in einen datetime-Datentyp liegt
der datetime-Wert außerhalb des gültigen Bereichs.)

I've tried just about everything else and landed at the convert. Normal date
strings didn't work. Interestingly the view
SELECT AUFTLT FROM AUFTRAEGE WHERE (AUFTLT > convert(datetime,
'20030707',112 )) does work. I can also switch the day and month values and
use different styles but nothing has worked. CAST (AUFTLT as DateTime) in
the original database also occured to me.

The above view works no problem when run directly in the databases (MSDE
v7.0). It just isn't working when I link the server and try to query in the
other.

I'd be thankful for any tips (or pity)

Ben

B. Cline (bc_News@gmx.net) writes:
[quoted text, click to view]

I don't know if I have any good suggestions, but could you post a
script that demonstrates the problem? That is, the CREATE VIEW statement
and the query you use. Please also specify on which server you have
which. To make it simple, specify the view against the Northwind
database.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Erland,

Thanks for looking at my problem. While looking at creating some scripts to
better describe the problem, I switched to stored procedures. If I pass my
parameters as strings, I don't have any problems. It seems to be a problem
across the Server boundries. Since I found (an extremely fast) workaround, I
will just let the problem lay.

Btw, just for the people googling, my stored procedures look like this

(In connected server (what my program is calling)
CREATE PROCEDURE PL_GETFILTERED_AUFTLIST
(
@ShowAbgesch Int = 0,
@VonDate VarChar(20) = '19500101',
@BisDate VarChar(20) = '20500101'
)
AS

/* SET NOCOUNT ON */

EXECUTE LINKEDSERV.DBNAME.dbo. PL_GETFILTERED_AUFTLIST @ShowAbgesch,
@VonDate, @BisDate

RETURN
GO

(In Linked Server)
CREATE PROCEDURE PL_GETFILTERED_AUFTLIST
(
@ShowAbgesch Int = 0,
@VonDate VarChar(20) = '19500101',
@BisDate VarChar(20) = '20500101'
)
AS

/* SET NOCOUNT ON */
IF @ShowAbgesch = 0
SELECT *
FROM vwAUFTRAG_LIST
WHERE (AuftAbgesch = -1 OR AuftAbgesch = 1) AND
AUFTSLT Between convert(datetime, @VonDate,112 ) And
convert(datetime, @BisDate,112 )

Else
SELECT *
FROM vwAUFTRAG_LIST
WHERE AUFTSLT Between convert(datetime, @VonDate,112 ) And
convert(datetime, @BisDate,112 )


RETURN
GO

Once again thanks anyway.
Ben


"Erland Sommarskog" <sommar@algonet.se> schrieb im Newsbeitrag
news:Xns93C4E365C1F45Yazorman@127.0.0.1...
[quoted text, click to view]

B. Cline (bc_News@gmx.net) writes:
[quoted text, click to view]

Great to hear that you found a solution just by composing scripts for a
post!

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at