Query consisting the stored procedure works on SSMS or any SQL Editor but tiny tds returns null array

[kandukurivarun]

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

[KDGundermann]

could you please show us your code?

[kandukurivarun]

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

[kandukurivarun]

And the affected_rows returns '-1'

[kandukurivarun]

Sometimes i am getting the below error:

TinyTds::Error: Attempt to initiate a new Adaptive Server operation with results pending from (irb):25:inexecute' from (irb):25 from /opt/puppetlabs/puppet/bin/irb:11:in <main>'

[kandukurivarun]

Is there a way to enable the debugging on this library query execution? Same query working properly on SSMS and returns null on Ruby .

[KDGundermann]

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..

[kandukurivarun]

I have added the 'Select @output' to the end of my query and still it doesn't work. It just returns null.

[KDGundermann]

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

[kandukurivarun]

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

[KDGundermann]

Thats not your query from above? and still missing the SELECT @resultat the end..

[kandukurivarun]

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:

1. From Ruby:

2. From SSMS (Messages tab):

[kandukurivarun]

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?

[KDGundermann]

can you show the "Results" pane from SSMS?

[kandukurivarun]

Below is the results pane from SSMS

[KDGundermann]

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)

[KDGundermann]

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)

[kandukurivarun]

I am seeing the output i am expecting on tsql