SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - Update workspace
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - Update workspace
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.0.176 Pro] - Update workspace Reply with quote
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
Wed Oct 05, 2016 6:56 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Any status updates on this one?
Tue Oct 11, 2016 9:02 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Fri Oct 28, 2016 10:16 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Functions, procedures, views and types come neatly here as well. Tables refuse.
Fri Oct 28, 2016 10:20 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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?
Wed Nov 09, 2016 6:56 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
Hadn't you used the SCS repo previously with success or am I remembering wrong?
Wed Nov 09, 2016 10:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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
Wed Nov 09, 2016 10:24 am 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.