Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
[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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
Wed Oct 05, 2016 6:56 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Wed Oct 05, 2016 9:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Thu Oct 06, 2016 11:52 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
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.
|
|
Thu Oct 06, 2016 1:11 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Any status updates on this one?
|
|
Tue Oct 11, 2016 9:02 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Tue Oct 11, 2016 9:35 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
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.
|
|
Tue Oct 11, 2016 10:01 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
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...
|
|
Fri Oct 28, 2016 5:34 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Fri Oct 28, 2016 9:54 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
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.
|
|
Fri Oct 28, 2016 10:10 am |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Fri Oct 28, 2016 10:16 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Functions, procedures, views and types come neatly here as well. Tables refuse.
|
|
Fri Oct 28, 2016 10:20 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Anything else I could try to make this work?
EDIT:
I checked the log and saw a bunch of 'failed' lines:
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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?
|
|
Wed Nov 09, 2016 6:56 am |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
Hadn't you used the SCS repo previously with success or am I remembering wrong?
|
|
Wed Nov 09, 2016 10:14 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
Wed Nov 09, 2016 10:24 am |
|
![](templates/GreenTech/images/spacer.gif) |
|