'SELECT returns NULL if run from application

I have an INSERT trigger on a view created with VIEW_METADATA. There's the following snippet:

    if @has_folder is null set @has_folder=(select REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'people' and COLUMN_NAME = 'has_folder')

The whole point of this subquery is to set @has_folder to default value for a column if it is NULL. And to avoid editing trigger text if default changes. As the default value is stored in DB surrounded by double parentheses ('((0))'), I remove them.

It does work when I run an INSERT from SQL Server Management Studio, but it does not work when the same query is run from application (@has_folder stays NULL). The query is the same, I've checked with SQL Profiler.

If I set the statement to

    if @has_folder is null set @has_folder=0

it works, but then I'll have to change it manually if I decide to change the default value for that column in database.

In general,

select COLUMN_DEFAULT from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'people' and COLUMN_NAME = 'has_folder'

returns correct value if run from SSMS, but NULL if run from application.

The same behavior is shown by

select definition from sys.columns 
join sys.tables on sys.columns.object_id = sys.tables.object_id
join sys.default_constraints on sys.default_constraints.object_id = default_object_id
where sys.columns.name = 'has_folder'
and sys.tables.name = 'people'

What is going on and how can it be fixed?

sql-servertsqlsql-server-2005


Solution 1:[1]

try this approach

SELECT
    @has_folder = COALESCE(@has_folder,
                       (
                          SELECT 
                             REPLACE(REPLACE(REPLACE(COLUMN_DEFAULT, '(', ''), ')', ''), '''', '')
                             FROM INFORMATION_SCHEMA.COLUMNS
                                WHERE TABLE_NAME = 'people'
                                    AND COLUMN_NAME = 'has_folder'
                       ),
                       0
                    )

Solution 2:[2]

FYI, I had this same issue, and it was simply a matter of not having the correct permission with the account the application was running under. Having just read/write permission wasn't sufficient. Adding Owner permission did resolve the issue. I'm sure there's some level between those two that may work, but wanted to post this so that others know to look in that direction if they have this problem.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Jonny DoGooder

Related Questions