Casting to nvarchar(max) returns nil results. about tds

[CaldwellYSR]

Sorry, I was going back and forth with the guy the wrote the SQL in question and I think we were wrong about the problem. We tested with another couple queries that had similar casting but they didn't give the same issue. We are thinking the issue might be coming from something weird he is doing with the date variable that's being passed into the query. Unfortunately the query is 1500+ lines long so debugging it is a bit outside my scope.

I will get back to you if we get anywhere with it.

[moogle19]

Hi @CaldwellYSR ,
I tried to reproduce your problem, but in my example it seems to work.

I tried the following:

iex> Repo.query("CREATE TABLE varchar_test (top_pick_sentence varchar(499))")
{:ok, %Tds.Result{columns: nil, num_rows: 0, rows: nil}}

iex> Repo.query("INSERT INTO varchar_test VALUES ('some veeeeeeeeeeeeeeeeeeeeeeeeerrrrrrrrrrrrrrrrrrryyyyyyyyyyyyyyyyyyyyy long string')")
{:ok, %Tds.Result{columns: nil, num_rows: 1, rows: nil}}

iex> Repo.query("select cast(top_pick_sentence as nvarchar(max)) as top_pick_sentence from varchar_test", [])
{:ok,
 %Tds.Result{
   columns: ["top_pick_sentence"],
   num_rows: 1,
   rows: [
     ["some veeeeeeeeeeeeeeeeeeeeeeeeerrrrrrrrrrrrrrrrrrryyyyyyyyyyyyyyyyyyyyy long string"]
   ]
 }}

Could you try the example above in your environment and maybe enable debug output for the queries, which are problematic, so I can take a look at the raw SQL queries?

[moogle19]

@CaldwellYSR
Any chance you could supply me with some debug output for the problem?

[CaldwellYSR]

@moogle19 I guess I'm going to close this issue because it's clearly incorrect. The SQL guy has given me a new query that works even when casting to nvarchar so clearly the casting isn't the issue. He believes the issue was related to that query using a ton of unions but he doesn't know that for sure.

If we find out what broke it I will create another issue.