Ruby with MS SQL to truncate all table in a DB

Question

I am using ruby and tiny_tds (ms sql). I want to truncate all the tables in a database.

below is what I have now:

require 'tiny_tds'
@client = TinyTds::Client.new(username: 'sa', database: 'test123', password: 'Auto1', host: 'localhost', port: 1433)
puts 'Connecting to SQL Server'

if @client.active? == true then puts 'Done' end

out = @client.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_CATALOG='test123'");
#@client.do
#puts "out ==> #{out}"
out.each do |x|
        puts "tabname1 ==> #{x['TABLE_NAME']}"
        next if x['TABLE_NAME'] =~ /api_user|version_info/
        @client.execute("TRUNCATE TABLE #{x['TABLE_NAME']}")
        @client.do
end
@client.close

Output:

$ ruby mssql.rb
Connecting to SQL Server
Done
tabname1 ==> test
mssql.rb:14:in `execute': Attempt to initiate a new Adaptive Server operation with results pending (TinyTds::Error)
    from mssql.rb:14:in `block in <main>'
    from mssql.rb:10:in `each'
    from mssql.rb:10:in `<main>'

I know we need to use do [@client.execute(sql).do]. but still getting the error

Update:

tables in test123 db

test, test1

Answer 1

I believe the issue is that you're trying to run a new execution inside of your out.each block, although the initial out = @client.execute(...) execution hasn't completed.

Here you execute some sql:

out = @client.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA....

... and then inside of your each loop you're trying to execute again:

@client.execute("TRUNCATE TABLE #{x['TABLE_NAME']}")

... before your initial out execution has been completed using out.do as you suggested.