MS SQL Linked passing parameter
I've been scratching my head over this one for a while but am unable to
work it out. I have an SQL Express 2012 database on one server and SQL
2008 Standard on another. On the 2008 server I have created a linked
server back to the Express server and I am trying to pass a date parameter
that I can then reference in reporting services. I am building this in a
stored procedure, here is my code:
ALTER PROCEDURE [dbo].[usp_OTRS_PhoneTicketsTEST]
AS
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT * FROM OPENQUERY
([UKOTRS\SQLEXPRESS],'
SELECT
otrs.dbo.ticket.tn AS TicketNum,
otrs.dbo.ticket.create_time,
otrs.dbo.users.first_name + '' '' + otrs.dbo.users.last_name As Name
FROM
otrs.dbo.ticket INNER JOIN otrs.dbo.users
ON otrs.dbo.ticket.create_by = otrs.dbo.users.id INNER JOIN
otrs.dbo.queue
ON otrs.dbo.ticket.queue_id = otrs.dbo.queue.id
WHERE
customer_id IS NULL
AND create_time >= '''' + @StartDate + '''' and create_time <= ''''+
@EndDate + ''''')
But I receive the following:
OLE DB provider "SQLNCLI10" for linked server "OTRS\SQLEXPRESS" returned
message "Deferred prepare could not be completed.". Msg 8180, Level 16,
State 1, Line 1 Statement(s) could not be prepared. Msg 137, Level 15,
State 2, Line 13 Must declare the scalar variable "@StartDate".
I've declared the startdate though...
Any help would be greatly appreciated.
Regards,
Michael
No comments:
Post a Comment