linked server problem

I am trying to run this in the SQL Query Analyzer. I am running it from SQL
Server 2000 which links to a MSDE 2000 Server.
declare @.StartDate as nvarchar(12)
declare @.EndDate as nvarchar(12)
declare @.EmpID as nvarchar(4000)
declare @.query1 as varchar(8000)
declare @.empWhere as varchar(4500)
set @.StartDate='7/22/2003'
set @.EndDate='7/22/2003'
set @.EmpID='123456'
if @.EmpID='ALL'
set @.empWhere=''
else
set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
replace(@.EmpID,',',''',''') + ''')) '
set @.query1 = 'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
'UNION ALL ' +
'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
'ORDER BY displayname,PunchDate'
print @.query1
EXEC(@.query1)
SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
ORDER BY p.LastName,p.FirstName,PunchDate
This causes the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1: Incorrect syntax near 'Id'.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Unclosed quotation mark before the character string 'Id'.]
We have been able to determine that the error is only generated when we add
a where clause for a text based field. So, if we remove the "AND
(bh.EmployeeNumber in ('123456')) " or use a numeric based field, the query
works correctly. Also, if we run the query from the linked server, the
query works.I still don't know what is causing the error, but i have been able to find a
workaround by using openquery.
"Kent Elmer" <kent_elmer@.nospamforme.com> wrote in message
news:Ojap6xUUDHA.1052@.TK2MSFTNGP09.phx.gbl...
> I am trying to run this in the SQL Query Analyzer. I am running it from
SQL
> Server 2000 which links to a MSDE 2000 Server.
> declare @.StartDate as nvarchar(12)
> declare @.EndDate as nvarchar(12)
> declare @.EmpID as nvarchar(4000)
> declare @.query1 as varchar(8000)
> declare @.empWhere as varchar(4500)
> set @.StartDate='7/22/2003'
> set @.EndDate='7/22/2003'
> set @.EmpID='123456'
> if @.EmpID='ALL'
> set @.empWhere=''
> else
> set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
> replace(@.EmpID,',',''',''') + ''')) '
> set @.query1 = 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
> 'UNION ALL ' +
> 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
> 'ORDER BY displayname,PunchDate'
> print @.query1
> EXEC(@.query1)
> SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
> Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
> 2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> ORDER BY p.LastName,p.FirstName,PunchDate
> This causes the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'MSDASQL'.
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Statement(s) could not be prepared.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Line 1: Incorrect syntax near 'Id'.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Unclosed quotation mark before the character string 'Id'.]
> We have been able to determine that the error is only generated when we
add
> a where clause for a text based field. So, if we remove the "AND
> (bh.EmployeeNumber in ('123456')) " or use a numeric based field, the
query
> works correctly. Also, if we run the query from the linked server, the
> query works.
>sql