Search code examples
sqlsql-serversql-server-2005tiny-tds

SQL Server 2005 SP call works in SSMS but not using tiny_tds

I'm trying to monitor SQL Server 2005 replication using the stored procedures they provide.

I can run the following in SSMS and get data:

exec sp_replmonitorhelpsubscription 
     @publisher = 'publisher', @publication = 'publication', @publication_type = 0

If I run the same query using tiny_tds I get the following:

mssql = TinyTds::Client.new(
      :dataserver => config[:host],
      :port => config[:port],
      :username => config[:username],
      :password => config[:password],
      :database => db
      )
result = mssql.execute("exec sp_replmonitorhelpsubscription @publisher='publisher', @publication='publication', @publication_type=0")

...............

MSSQLCheck WARNING: query did not complete: Cannot insert the value NULL into column 'publication_id', table 'tempdb.dbo.#tmp_replication_monitordata________________________________________________________________________________________000000001A64'; column does not allow nulls. INSERT fails.

I'm having no luck finding this particular error anywhere. Can anyone help?

Solution

  • I suspect tiny_tds might not set ANSI_DEFAULTS ON so columns created without a NULL or NOT NULL specification default to NOT NULL. Try executing SET ANSI_DEFAULTS ON or SET ANSI_NULL_DFLT_ON ON before executing the proc.