September 22, 2014 at 5:07 pm
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 T-SQL I get the following:
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.
What's the difference?
September 22, 2014 at 6:14 pm
jslusher (9/22/2014)
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 T-SQL I get the following:
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.
What's the difference?
If you run
exec sp_replmonitorhelpsubscription @publisher='publisher', @publication='publication', @publication_type=0
from within SSMS, then you are running it in TSQL.
Could you explain your issue a little more clearly please?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2014 at 6:30 pm
My apologies. I'm trying to execute the query using a ruby gem called tiny_tds. I've used this many times before and I haven't seen this behavior, so I assumed that T-SQL and the SSMS aren't exactly the same thing. The code is below. A you can see, the statement is the same, but I get the error I mentioned in the original post.
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")
September 22, 2014 at 6:42 pm
Since it works in tsql in ssms, there has to be something different about how it is being sent to SQL Server from tiny_tds.
Have you confirmed that you are running the procedure against your distribution database when executing from tiny_tds?
Does the user in the connection from tiny_tds have dbo on the distribution and published databases? Or is that user a member of the replmonitor role?
The temp table accepts null values in SQL 2005 through SQL 2012 so it is not the proc (also given that it works from ssms).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2014 at 6:45 pm
The solution turned out to be adding this to the query:
SET ANSI_DEFAULTS ON;
Apparently the gem does not account for that.
September 22, 2014 at 6:49 pm
jslusher (9/22/2014)
The solution turned out to be adding this to the query:SET ANSI_DEFAULTS ON;
Apparently the gem does not account for that.
Fair enough. That would have been a piece of the next group of things I would have recommended to check. Connection settings like ansi_defaults, ansi_nulls, ansi_null_dflt_on, ansi_padding, ansi_warnings, arithabort, concat_null_yields_null, quoted_identifier, transaction_isolation_level.
Glad it is working tho.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply