Erratic behaviour of function when invoked via sqlcmd

Question

Erratic behaviour of function when invoked via sqlcmd

Stephen Grimshaw

Right there with Babe

Points: 738
More actions
March 7, 2012 at 7:30 am

#254366

I have a function that returns a string. The output is defined as varchar(max). The string returned usually contains fewer than 100 characters.
When I select a number of columns, including the output of the function, from inside SQL Server Management Studio it returns the values I should expect.
When the same query is run from inside SQLCMD (as the same user) :
- it returns some null values in the column when run in my production environment ;
- it consistently returns null values in the column when run in another.
I can see nothing in the values returned that gives any hint.
As the function runs properly in SQL Server Management Studio, I have not attached it.
Has anyone any ideas on why the behaviour can vary between tools?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Stephen Grimshaw (3/7/2012)
I have a function that returns a string. The output is defined as varchar(max). The string returned usually contains fewer than 100 characters.
When I select a number of columns, including the output of the function, from inside SQL Server Management Studio it returns the values I should expect.
When the same query is run from inside SQLCMD (as the same user) :
- it returns some null values in the column when run in my production environment ;
- it consistently returns null values in the column when run in another.
I can see nothing in the values returned that gives any hint.
As the function runs properly in SQL Server Management Studio, I have not attached it.
Has anyone any ideas on why the behaviour can vary between tools?

1. What function? DDL please.

2. What query? Script please

3. What null and not-null values? Sample of data, please?

4. Are your databases in production and "other" environment contain exactly the same data?

And the last one:

SQL user-defined function is executed in SQL Server, therefore its behaviour will be the same regardless of the tool which executed SQL to invoke it.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Any difference in connection SET options?

-- Gianluca Sartori

Stephen Grimshaw Right there with Babe Points: 738 More actions · Answer 3

Thank you Gianluca.

I'd spent so much time looking at SQLCMD that I hadn't checked the concatenate_null... setting in Management Studio (and. to be honest, I was convinced the function already allowed for all possible instance sof nULL).

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

spaghettidba

SSC Guru

Points: 105732

March 7, 2012 at 8:43 am

#1456060

Great! Glad I could help.

-- Gianluca Sartori