1

I have a SQL Server and Cache server and need to combine some data. Most all functions are working, except when I attempt to pass a date with a variable or a parameter.

Below is a test I ran (one of many).

declare @myDate datetime
set @myDate = convert(datetime,'2012-02-01',120)

select * from ccms..dbo.dcdnstat where timestamp > '2012-02-01'  -- WORKS

exec( 'select * from dbo.dcdnstat where cdn = ?', 21004) at ccms  -- WORKS

exec( 'select * from dbo.dcdnstat where timestamp > ?',@myDate) at ccms  -- ERROR 7213

select * from ccms..dbo.dcdnstat where timestamp > @myDate  -- ERROR 7322

Msg 7213, Level 16, State 1, Line 9 The attempt by the provider to pass remote stored procedure parameters to remote server 'ccms' failed. Verify that the number of parameters, the order, and the values passed are correct. Msg 7322, Level 16, State 2, Line 11 A failure occurred while giving parameter information to OLE DB provider "MSDASQL" for linked server "ccms".

I have tried different date formats, and as shown above I can query on other fields with variables and I can query on date if I use a specific value.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user1193540
  • 11
  • 1
  • 3

4 Answers4

1

Instead of a "DATETIME" type for your stored procedure parameter, just use "DATE"

(as long as you don't need the time requirements)

MobileMon
  • 8,341
  • 5
  • 56
  • 75
0

I had a problem similar to this today. It had to do with the fact that the calling server and the linked server were not the same version. One was SQL Server 2005 and the other was SQL Server 2008. The problem was due to the fact that the column being queried against at the remote end (2008) was a datetime2 and the calling server (2005) does not support datetime2.

groksrc
  • 2,910
  • 1
  • 29
  • 29
0

I know this is a little late but I stumbled upon it looking for something similar with the Cache system. Looks like we are working on the same backend system. Mine is the Avaya CCMS system. Here is what I do to pass dates as variables:

DECLARE @myDate DATETIME
SET @myDate = CAST('2012-07-01' AS DATETIME) 

SELECT SUM(CallsOffered), SUM(CallsAnswered), SUM(SkillsetAbandonedAftThreshold)
FROM AvayaCCMS..dbo.mSkillsetStat
WHERE Timestamp = @myDate
spinon
  • 10,760
  • 5
  • 41
  • 59
0

I was doing something very similar using a Sybase database as the linked server. Just as MobileMon said, I was able to change the datetime to date and it works fine now.

declare @myDate date
set @myDate = convert(datetime,'2012-02-01',120)
Scott
  • 43
  • 4