rails-sqlserver
Query consisting the stored procedure works on SSMS or any SQL Editor but tiny tds returns null array
I am trying to execute a stored procedure from tiny_tds and it returns me the null value. If i execute the same query from any SQL editors, they return the value that i am expecting. What are the ways to debug this? I am able to execute the simple select statements using tinytds, but when i execute the stored procedure, it doesnt return any value,
TinyTds version: 2.10 OS: RHEL 7.4
This is the query/sproc that i am executing:
DECLARE @output NVARCHAR(MAX) EXEC getAppInstanceHieraData @output OUT SELECT * FROM OPENJSON(@output) WITH ( AttributeId INT '$.LabelId', AttributeKey NVARCHAR(MAX) '$.AttributeKey', AttributeValue NVARCHAR(MAX) '$.AttributeValue', AttributeScope NVARCHAR(MAX) '$.AttributeScope', EnvironmentId INT '$.EnvironmentId', AppId INT '$.AppId', AppInstanceId INT '$.AppInstanceId', ServerId INT '$.ServerId' ) WHERE AttributeKey = @attributeKey and AttributeScope = @MachineFqdn
Sometimes i am getting the below error:
TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending from (irb):25:in execute'
from (irb):25
from /opt/puppetlabs/puppet/bin/irb:11:in <main>'
Is there a way to enable the debugging on this library query execution? Same query working properly on SSMS and returns null on Ruby .
as I can see you are executing a sproc, but you don't return any data. (Maybe SSMS will show you some messages, but this not data. Add a 'SELECT @output' at the end of your code..
I have added the 'Select @output' to the end of my query and still it doesn't work. It just returns null.
Have you tried your code with a :message_handler?
opts = ... # host, username, password, etc
opts[:message_handler] = Proc.new { |m| puts m.message }
client = TinyTds::Client.new opts
client.execute(".....'").do
This is what i got when i executed it with the way that you shown above:
irb(main):088:0> client.execute(query).do SELECT @result = FQDN FROM tblAppInstanceServer ais LEFT JOIN tblAppInstance tAI on tAI.AppInstanceId = ais.AppInstanceId WHERE ais.ServerId = @ServerId => -1
Thats not your query from above? and still missing the SELECT @resultat the end..
I know what is happening, the SPROC consists of plenty of select statements. When i use tiny_tds, it is trying to execute the 1st select statement from the sproc and it is exiting.
Below are the screenshots:
- From Ruby:
- From SSMS (Messages tab):
SSMS knows that it has to wait till it executes all statements in the sproc, but tiny_tds exits after it execute the first select statement. How i can fix this issue?
I think you have multiple results sets (Known as MARS see https://docs.microsoft.com/de-de/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-2017)
I don't know if FreeTDS supports MARS. (try tsql -C)
I think you have multiple results sets (Known as MARS see https://docs.microsoft.com/de-de/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-2017) I don't know if FreeTDS supports MARS. (try `tsql -C)
