Calling Stored Procedure Gives Invalid Data Type

[allenwyma]

Hi, first of all, would love to say thanks for this library! Has made my life with MS SQL so much better...

On to the bad news, I've been calling quite a few stored procedures using the format:

Ecto.Adapters.SQL.query(Repo, "storedProcedure @arg1=$1, @arg2=$2", ["string", 1]) and it's been working great until now one is returning a weird error:

{:error,
 %Tds.Error{
   message: nil,
   mssql: %{
     class: 16,
     length: 150,
     line_number: 0,
     msg_text: "Error converting data type money to nvarchar.",
     number: 8114,
     proc_name: "storedProcedure",
     server_name: "75486b8b9487",
     state: 5
   }
 }}

It seems to think that the string is a money value. Any ideas on how this could happen and any type of work around? One of the things that I think may be causing this issue is because it does have a 3rd argument that's default set to null.

[mjaric]

Try to call stored procedure like this

Ecto.Adapters.SQL.query(Repo, "storedProcedure @1, @2", ["string", 1])

$1 is not valid parameter, and probably sql server is complaining about it thinking it is USD :)

[allenwyma]

perfect! but it seems it breaks a different stored procedure call:

%{class: 16, length: 182, line_number: 0, msg_text: "Error converting data type nvarchar to datetime." ...}

It seems to break with this: 2018-02-02 which should be a nvarchar but its' being interpreted as a datetime.

[mjaric]

I'm afraid I'm unable to help you here, it is your SQL script (stored procedure) error you should definitely check it

[allenwyma]

Indeed, was very strange. seems the $ kind of worked up until that one query, but then @ works... need to do some cleanup here. anyways, thanks!