CODEWITHSUNDEEP
sqlt-sqldatetimestored-proceduresopenquery
Brie
Brie

Reputation: 2359

Error converting data type nvarchar to datetime with stored procedure

[Using SQL Server 2008 R2 Enterprise x64 SP1]

I am trying to use some form of GETDATE() to pass today's date to a stored procedure inside OPENQUERY(), but I keep getting the error

Msg 8114, Level 16, State 1, Procedure spCalcProjection, Line 0
Error converting data type nvarchar to datetime

Here is the code (spCalcProjection takes a datetime):

SELECT top 1 multi FROM OPENQUERY([production], 'exec proddb.dbo.spCalcProjection "GETDATE()"')

If I use 2014-05-22 or any literal in place of GETDATE() then I have no problem and get the correct, expected result. If I use some other functionality like CAST(GETDATE() AS DATE) or CONVERT(varchar, GETDATE(), 112) then I get the above error again.

Upvotes: 0

Views: 7370

Answers (2)

Brie
Brie

Reputation: 2359

Got an answer from elsewhere that I will use:

SELECT top 1 multi FROM OPENQUERY([production], 'DECLARE @dt datetime SELECT @dt = GETDATE() exec proddb.dbo.spCalcProjection @dt')

This avoids having to create any additional objects in the db.

Upvotes: 1

Esc
Esc

Reputation: 195

Conrad, posting original syntax error for GETDATE() without double quotes could help more than you think. I also don't see why would you need to escape the function here. (Sorry, can't add to your thread with Lamak, not enough reputation for comments). Also, why do you need an open query to call your sp? When you say SQL Server 2008 R2, is it both on the calling side and on your [production] server? If the other end is not SQL Server it might not have GETDATE() function. If the other end is SQL Server you don't need OpenQuery.

[UPDATE] I think I have your answer. You cannot use a function as a parameter for stored procedure. Has nothing to do with open query. What you can do, you can replace that stored procedure with table-valued function. I just tried it and it worked.

Definition:

CREATE FUNCTION TestFun
(
    @TestDateParam datetime
)
RETURNS 
@RetTable TABLE 
(
    Line nvarchar(20)
)
AS
BEGIN
    INSERT INTO @RetTable
    SELECT aString
        FROM sometable
        WHERE aDate = @TestDateParam

    RETURN 
END

Call:

SELECT *
FROM dbname.dbo.TestFun(GETDATE())

Upvotes: 1

Related Questions