SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.157 Pro BETA] - Missing result sets in $$.$$
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.157 Pro BETA] - Missing result sets in $$.$$
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.157 Pro BETA] - Missing result sets in $$.$$ Reply with quote
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
   
-- $$

Sun Oct 09, 2016 4:45 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Mon Oct 10, 2016 3:45 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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
Mon Oct 10, 2016 4:20 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Mon Oct 10, 2016 4:30 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Mon Oct 10, 2016 4:38 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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?
Mon Oct 10, 2016 5:17 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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".
Mon Oct 10, 2016 5:54 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
I'm not sure if that is the expected result. Let me check with the team and get back to you.
Mon Oct 10, 2016 7:51 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
It's definitely what I'd expect if I came up with that code, in all three cases.
Tue Oct 11, 2016 3:00 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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?
Tue Oct 11, 2016 10:51 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.
Wed Oct 12, 2016 3:10 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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
Wed Oct 12, 2016 3:30 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
It does.
Wed Oct 12, 2016 3:38 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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?
Tue Oct 18, 2016 3:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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
Tue Oct 18, 2016 4:54 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.