Came across a weird situation today...
The situation :
A Stored Procedure i've written which references global Temporary tables (populated using dynamic sql)
The scenario :
Procedure ran fine and returned results from Management Studio when logged in as my login (sysadmin).
Procedure ran fine and returned results from Management Studio when logged in as 'sa'.
Procedure ran fine and returned results from Management Studio when logged in as application user.
Failed when run from Linux TSQL interface with the error >
" Msg 515, Level 16, State 2, Server SERVERNAME, Line 1
Cannot insert the value NULL into column 'MaxFileSizeinMb', table 'tempdb.dbo.##Results'; column does not allow nulls. INSERT fails. "
2 ways to fix >
1) Explicitly declare column in temp table as allowing NULL (bizarre but it worked) >
CREATE TABLE ##Results
( DataFile VARCHAR,
MaxFileSizeinMB INT NULL
DataSizeinMB INT)
or (the systems team solution we've implemented)
2) Make sure the right drivers are being used!
In this case, SQL 2005 is being accessed from linux via FreeTDS
(TDS = Tabular Data Stream)
The version of TDS being used was incorrect
TDS versions refer to the SQL Server build number, therefore >
7.0 for Sql 7
8.0 for Sql 2000
9.0 for Sql 2005
To verify the settings >
Open /etc/freetds.conf
Locate the [global] section.
Find the line that says tds version = 4.2
Change to tds version = 9.0
Restart Apache > service httpd restart
Links :
FreeTDS http://www.freetds.org/
FreeTDS Versions http://www.freetds.org/tds.html
TSQL http://linux.die.net/man/1/tsql
No comments:
Post a Comment