[SA 9.0.157 Pro BETA] - Missing result sets in $$.$$

[SA 9.0.157 Pro BETA] - Missing result sets in $$.$$

I have a snippet that should count all the rows in all tables in the current database (code at the end). It creates a dynamic sql that puts together another dynamic sql to achieve the goal. This worked in 7.x but now it does not. The message pane shows that all the inserts are done. However, he last two selects that should display the row counts for every table in a simple result set and the sum of them in another are either not executed or their result is not displayed.

Code:

$PROMPT(@include_views,Include views?,,Count all records,No Yes Only)$

DECLARE @stmt NVARCHAR(MAX) = ''
,@params NVARCHAR(MAX) = ''
,@schema_mask SYSNAME = '%'
,@object_mask SYSNAME = '%'
,@include_views SYSNAME = '$@include_views$'
;

DECLARE @db SYSNAME = '$DB$';

SET @stmt = 'USE [' + @db + '];

DECLARE @stmti NVARCHAR(MAX) = ''DECLARE @object_row_counts TABLE ([schema_name] SYSNAME, [object_name] SYSNAME, is_view BIT, row_count BIGINT);'' + CHAR(10);

SELECT @stmti = @stmti + (
SELECT
/*
sq.[schema_name]
,sq.[object_name]
,sq.is_view
,
*/
CONCAT(''INSERT INTO @object_row_counts([schema_name], [object_name], is_view, row_count)'',
''SELECT '', QUOTENAME(sq.[schema_name], ''''''''), '', '', QUOTENAME(sq.[object_name], ''''''''), '', '', sq.is_view, '', '',
''COUNT(1)'',
'' FROM '', QUOTENAME(sq.[schema_name]), ''.'', QUOTENAME(sq.[object_name]), '';'') + CHAR(10)

FROM
(
SELECT
SCHEMA_NAME(t.[schema_id]) AS [schema_name]
,t.name AS [object_name]
,0 AS is_view
FROM
sys.tables AS t
WHERE 1 = 1
AND SCHEMA_NAME(t.[schema_id]) LIKE @schema_mask
AND t.[name] LIKE @object_mask
AND @include_views IN (''No'' ,''Yes'')
UNION
SELECT
SCHEMA_NAME(v.[schema_id]) AS [schema_name]
,v.[name]
,1 AS is_view
FROM
sys.[views] AS v
WHERE 1 = 1
AND SCHEMA_NAME(v.[schema_id]) LIKE @schema_mask
AND v.[name] LIKE @object_mask
AND @include_views IN (''Yes'' ,''ONLY'')
) AS sq
ORDER BY
is_view
,[schema_name] COLLATE database_default
,[object_name] COLLATE database_default

FOR XML PATH(''''), TYPE
).value(''(./text()[1])'' ,''nvarchar(max)'')
;

SET @stmti = @stmti + CHAR(10) + ''
SELECT
t.[schema_name] -- AS shame
,t.[object_name] -- AS oname
,CONCAT(''''SELECT * FROM ['''', t.[schema_name] COLLATE database_default, ''''].['''', t.object_name COLLATE database_default, ''''];'''') AS sel_stmt
,t.is_view
,t.row_count AS [Row count]
,CAST(1.0 * t.row_count / POWER(10 ,3) AS DECIMAL(18 ,2)) AS [~Row count (K)]
,CAST(1.0 * t.row_count / POWER(10 ,6) AS DECIMAL(18 ,2)) AS [~Row count (M)]
,CAST(1.0 * t.row_count / POWER(10 ,9) AS DECIMAL(18 ,2)) AS [~Row count (G)]
,CAST(14.0 * t.row_count / POWER(10 ,3) AS DECIMAL(18 ,2)) AS [Table RLV increase (K)]
,CAST(14.0 * t.row_count / POWER(10 ,6) AS DECIMAL(18 ,2)) AS [Table RLV increase (M)]
,CAST(14.0 * t.row_count / POWER(10 ,9) AS DECIMAL(18 ,2)) AS [Table RLV increase (G)]
FROM
@object_row_counts AS t
ORDER BY
t.is_view ASC
,t.[schema_name] COLLATE database_default
,[object_name] COLLATE database_default
,row_count

SELECT
SUM(row_count) AS [All tables/views all rows COUNT]
,CAST(1.0 * SUM(t.row_count) / POWER(10 ,3) AS DECIMAL(18 ,2)) AS [~ATAR count (K)]
,CAST(1.0 * SUM(t.row_count) / POWER(10 ,6) AS DECIMAL(18 ,2)) AS [~ATAR count (M)]
,CAST(1.0 * SUM(t.row_count) / POWER(10 ,9) AS DECIMAL(18 ,2)) AS [~ATAR count (G)]
,SUM(t.row_count) * 14 AS [RLV increase (B)]
,CAST(14.0 * SUM(t.row_count) / POWER(10 ,3) AS DECIMAL(18 ,2)) AS [Database RLV increase (K)]
,CAST(14.0 * SUM(t.row_count) / POWER(10 ,6) AS DECIMAL(18 ,2)) AS [Database RLV increase (M)]
,CAST(14.0 * SUM(t.row_count) / POWER(10 ,9) AS DECIMAL(18 ,2)) AS [Database RLV increase (G)]
FROM
@object_row_counts AS t ''

select @stmti
exec(@stmti)
'

SET @params = '@schema_mask sysname, @object_mask sysname, @include_views SYSNAME'

-- select @stmt AS stmt ,@params AS params, @schema_mask as schema_mask, @object_mask as object_mask, @include_views as include_views

EXECUTE sp_executesql @stmt ,@params , @schema_mask = @schema_mask, @object_mask = @object_mask, @include_views = @include_views

-- $$

Sorry, it will take us a while to dig through this code.

In a mean time, I would like to suggest a workaround solution. In the editor text or in the Database Explorer hover mouse over a database name and from the balloon choose Count rows in all database tables. It will give you detailed count by table, which you can right-click and export to Excel where you may apply additional filters if desired.

All it does is that it creates the code that will create "INSERT INTO" + "SELECT COUNT" statements for every table in the database in a dynamic sql, executes it, and then select the results from the table variable.

I tried the workaround but it didn't seem to work. I got a message

	Quote:
	Statistics not available for all tables in the selected schema.

I also tried Count database objects but it showed extreme discrepancies to the reality. Is that a hardwired code or does it use something from DB Queries?

EDIT:
I forgot to mention that the snippet action is "Execute and Display Output Results".

Last edited by gemisigo on Mon Oct 10, 2016 4:30 pm; edited 1 time in total

	Quote:
	Is that a hardwired code or does it use something from DB Queries?

It uses Record Count (...) query from DB Queries.

It sounds like you have auto-stats disabled in your database. This likely affects row count numbers that appear in mouse over hints for tables.
There is also new Update Statistics command in the right-click menu for database schema in Database Explorer to update table stats for all tables in a schema. Note it's a schema level command.

EDIT:

	SysOp wrote:
	It sounds like you have auto-stats disabled in your database.

Any other reasons for it not to work? is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on, all these are set to true.

Some weird things happen there which make me think it's not related to snippets.
You see, there's a statement that 'prints' (selects) the dynamic sql into a SA result grid before passing it to exec.

	Code:
	select @stmti exec(@stmti)

Now that select result is retrieved, but the one that should come from the dynamic sql is not. The next step was to copy-paste the dynamic sql into the editor and execute it there. And to my greatest surprise it worked just the way it did when it was triggered from the snippet. The message pane listed tons of "1 row affected" lines but the result of the last two selects created no result set grid in the editor. I replaced the table variable and repeated the process. Again, nada. Then I executed the last two selects alone, and there they were.

EDIT: I created a (not so) short video of those you can watch here.

What do you get when you run the following code?

	Code:
	DECLARE @sel NVARCHAR(1000) = 'SELECT getdate() as now2' SELECT getdate() as now1 EXEC (@sel)

Do you use ADO or ODBC connection to the server?

I use ODBC.

The code you provided yields two result sets (grids) when run in SQL Editor.
When run as snippet, it either returns two result sets when action set to "Execute and Display Output Results"
or

	Code:
	2016-10-10 23:50:57.1872016-10-10 23:50:57.187

when action set to "Insert Output Into Code".

I'm not sure if that is the expected result. Let me check with the team and get back to you.

It's definitely what I'd expect if I came up with that code, in all three cases.

We will need some time to investigate this one. It's seems to be complicated.

About the row count stats that you have found incorrect. The results come from Record Count (MSSQL) query. Would you please try running that query manually for a couple of tables with incorrectly shown row counts and confirm the numbers are consistent with what you get from that query?

	SysOp wrote:
	We will need some time to investigate this one. It's seems to be complicated. About the row count stats that you have found incorrect. The results come from Record Count (MSSQL) query. Would you please try running that query manually for a couple of tables with incorrectly shown row counts and confirm the numbers are consistent with what you get from that query?

I don't get row counts for any table at all, I only get the message saying

	Quote:
	Statistics not available for all tables in the selected schema

If you meant this

	gemisigo wrote:
	I also tried Count database objects but it showed extreme discrepancies to the reality. Is that a hardwired code or does it use something from DB Queries?

these are no row count stats, they should be the numbers of different types of objects in the database. And it retrieved rather weird results, such as 4 for schemas, 72 for tables, 418 for views, 1500 for procedures, 43 for functions, and 59 for table functions. Those numbers are way off for a database only containing two tables, even if we take system objects into account.

We meant the Record Country query from SQL Assistant options. Here is one for MSSQL

	Code:
	if @@version like '%SQL Azure%' SELECT TOP 1 ps.row_count FROM sys.indexes AS si WITH (NOLOCK) JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK) ON ps.object_id = si.object_id AND ps.index_id = si.index_id WHERE si.object_id = :OBJECT_ID AND si.index_id < 2 else SELECT TOP 1 rows FROM dbo.sysindexes WITH (NOLOCK) WHERE id = :OBJECT_ID AND indid < 2

You can test it with your tables if you substitute :OBJECT_ID with OBJECT_ID('table name goes here'). I wonder if this query returns correct row counts

It does.

I re-checked it again and it seems that it only materializes when using ODBC connection. Connecting with ADO it works as expected.

By the way, what's the preferred connection type?

	Quote:
	I re-checked it again and it seems that it only materializes when using ODBC connection. Connecting with ADO it works as expected.

I would have never guessed that one. Can you please find out which ODBC driver you use? One way to find that out is to open Data Sources (ODBC) Admin applet from Control Panel, and the the Drivers tab. Note, there could be several drivers for SQL Server, please check the one you use in SA's Connect dialog.

	Quote:
	By the way, what's the preferred connection type?

I haven't seen an official recommendation. It probably depends. If you often transfer data between different database types and use ODBC for other servers, then ODBC to ODBC transfers may work better because of internal data types compatibility enforced by ODBC, but again this is not truly universal.
From my personal experience ADO.NET is richer in features comparing to default ODBC driver for SQL Server. On the other hand the default SQL Server ODBC driver pr-einstalled on all Windows systems might be faster in certain cases (compiled C code vs. managed .NET code). From SQL Assistant's version 9.0 point of view ADO.NET might be a better choice, as it allows printing of messages from procedures and commands while code is running. With ODBC connection you need to wait for the procedure completion before you see any output.

Last edited by SysOp on Tue Oct 18, 2016 6:18 pm; edited 3 times in total