[SA 9.0.176 Pro] - Update workspace

[SA 9.0.176 Pro] - Update workspace

I tried to update the workspace from database in Repository Browser. Views, procedures and functions were successfully extracted but no approximately correct code for any table were retrieved. I got tons of errors stating it couldn't find source code for myschema.mytable and that I should check "DDL Code (MSSQL)" which I did, but upon fetching the code there:

Code:

IF (@@version LIKE '%SQL Server 20%' AND @@version NOT LIKE '%SQL Server 2000%')
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WITH (NOLOCK) WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o WITH (NOLOCK)
   JOIN [$DB_NAME$].sys.schemas s WITH (NOLOCK) ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT text
   FROM [$DB_NAME$].dbo.syscomments WITH (NOLOCK)
   WHERE id = :OBJECT_ID
   ORDER BY colid
ELSE IF @@version LIKE '%SQL Azure%'
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WITH (NOLOCK) WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o WITH (NOLOCK)
   JOIN [$DB_NAME$].sys.schemas s WITH (NOLOCK) ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT definition
   FROM [$DB_NAME$].sys.sql_modules WITH (NOLOCK)
   WHERE object_id = :OBJECT_ID
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments WITH (NOLOCK)
WHERE id = :OBJECT_ID
ORDER BY colid

I can't imagine how that query could possibly retrieve proper CREATE TABLE statements. I guess this might also be the reason why clicking Code on table hover popups return the same error message or some random garbage from the syscomments for tables but yield valid code for eg. stored procedures.

I recall this working in previous versions but cannot remember how. What did I do wrong?

EDIT:
After scanning the help:

Quote:

The Show Object DDL menu can be used with procedural objects of different types including:
• Views (in all supported database systems) See notes for Oracle versions 7 and 8 below.
• Stored procedures (in all supported database systems)
• User defined functions (in all supported database systems)
• Oracle packages (applicable to Oracle database systems only)
• Oracle types (applicable to Oracle database systems only)
• SQL Server triggers (applicable to SQL Server database systems only)

This sort of tells me that object DDL should not work with tables anyway. Why? It's pretty sane to suppose someone will want to fetch table creation script.

Last edited by gemisigo on Fri Oct 28, 2016 5:34 am; edited 3 times in total

This is certainly a regression. I opened a high priority ticket to get this fixed ASAP.

Internally it uses SMO interface to query DDL of various objects unless default options are changed and specific utility or "DDL Query" is selected as the DDL reverse-engineering method. We internal y call saDDL.exe in that case, but even that one doesn't seem to work correctly right now when it's called from SSMS or other targets as it cannot simply locate it in the path.

The scope of this issue is likely limited to SSMS and SQL authenticated connections. It should work when the same operations are invoked from SQL Assistant SQL Editor environment, or alternatively when using Windows authenticated connections.

It's caused by incorrect parsing of connection strings by SQL Assistant plugins for some SSMS versions.

We are working on a fix.

I'm not sure about that. I rarely use SSMS nowadays, almost exclusively for manipulating jobs and schedules. Things mentioned above happened either in SQL Editor or in Repository Browser, which was invoked from SQL Editor.

Any status updates on this one?

I believe there is a fix for that. It should be working correctly in builds 9.0.159 and later. Please let us know if this still doesn't work for you.

It doesn't. I still get tons of error messages.

EDIT: I'm about to give it up. Nothing I tried would help. I couldn't get table definitions into workspace. As a last resort I restored factory defaults, still the errors persisted.

I can't get this to comply after updating SA to 9.0.176. I recall Mindflux having some problems with comparing table objects with SCS so he must have this working. Hence there must be something on my side that prevents normal operation. I just can't figure it out what. I've already restored factory defaults with this last version to no avail.

It's driving me nuts...

The issue is certainly not in the DDL Code query.

Please try a simple test.
Backup your SQL Assistant settings file. You can use the Export button the the Options for that purpose.
Import factory default settings, please be sure the tick the "default" check-box so it does full overwrite of the option and not a merge. DB connections and source control connections should remain intact
Try Show DDL for any table to see if that works.
Now try updating workspace.

Please let us know if the above doesn't work. If it does work, please compare your previous settings and default settings on DB Options tab.

Also, if you have previous versions of SQL Assistant installed, you may try uninstalling them because they may cause various conflicts. Windows 8 and 10 versions cashe executable file names for quick search in the start menu and other places, and remember their locations by executable name. Sometimes when executable file is invoked by name only Windows may pick old version if it's first in the cache.

Neither Show DDL nor updating works after restoring defaults.

It's a rather fresh Windows install (less than two weeks old) with no other SA versions ever installed but 9.

	gemisigo wrote:
	I can't get this to comply after updating SA to 9.0.176. I recall Mindflux having some problems with comparing table objects with SCS so he must have this working. Hence there must be something on my side that prevents normal operation. I just can't figure it out what. I've already restored factory defaults with this last version to no avail. It's driving me nuts...

Yeah I can update the workspace ok from what I can tell.

Functions, procedures, views and types come neatly here as well. Tables refuse.

Anything else I could try to make this work?

EDIT:
I checked the log and saw a bunch of 'failed' lines:

Quote:

2016-11-09 12:19:08 SqlEditor64.exe Selected DB items count: 6
2016-11-09 12:19:08 SqlEditor64.exe fmSCSRepProgress: Create
2016-11-09 12:19:08 SqlEditor64.exe Start task TSCSTask_DatabaseToWorkspace at $000000000CFCE650
2016-11-09 12:19:08 SqlEditor64.exe Failed establish SMO connection: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe Failed getting DDL: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe Failed getting DDL: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe Failed getting DDL: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe Failed getting DDL: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe Failed getting DDL: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
2016-11-09 12:19:08 SqlEditor64.exe RefreshTrees(262) begin.
2016-11-09 12:19:08 SqlEditor64.exe RefreshTrees end.
2016-11-09 12:19:08 SqlEditor64.exe End task TSCSTask_DatabaseToWorkspace at $000000000CFCE650

What are those errors and how do I fix them?

Hadn't you used the SCS repo previously with success or am I remembering wrong?

I believe what you see in the log indicates the root cause of the issue. There is something wrong with SQL Server Shared Management Objects. To fix this issue, please follow instructions here
https://msdn.microsoft.com/en-us/library/ms162189.aspx?f=255&MSPPError=-2147217396

Another way to fix that is to install a recent version of SSMS, which is now free. You can download one here
https://msdn.microsoft.com/en-us/library/mt238290.aspx?f=255&MSPPError=-2147217396