Passing data-parameter to stored procedure (SQL-Server)

[Former Member]

Hi,

I'm using MS SQL 2008R2, CR 2008 (Business One Version).

Problem: I want to use a stored procedure from SQL - Server, witch needs a parameter date or datetime (tried both).

I tried to use the SP as a command and as choose from the dbo - list. In both cases i get an error, even if I use an exec-statement without CR parameters (static), and although there are no date or datetime columns in the result set.

Error:

Failed to retrieve data from the database.... Error converting nvarchar in datetime....

Here is the exec-statement:

exec dbo.MySP '10303', {ts '2012-01-23 15:14:57'}, {ts '2013-01-23 15:14:54'} (works fine on SQL-Server!)

any idea?

Thank you for your help!

Andreas

[former_member362494]

Hi ,

the parameter is passed as a string , i think you need  to explicitly convert in the stored procedure  :

declare @cardate as date

set @vardate = SELECT convert(datetime, <<your param>>,          102)  ( 102 is only an example )

[Former Member]

Hi,

I would suggest not to use SQL Server syntax in the command object, rather than that you may use ODBC syntax in command object.

-Shahnawaz

[Former Member]

Hi Shahnawaz,

I don't think it's a matter of date-syntax (btw: I thought my example above was ODBC-Syntax? Nut sure...) I tried it also with exec dbo.MySP '10303', '20120101', '20121231'

Every time the stored procedure receives a date or datetime - parameter, even if I pass it as constant, I get the error message.

Any other Idea?

Thanks

Andreas

[former_member11696]

Moved to the B1 forums, CR for B1 is an OEM build so they support it here.

SP's have a cursor so the first parameter is always a "1".

Don

[Former Member]

Hi,

You may try the following

call dbo.MySP '10303', '20120101', '20121231'

-Shahnawaz