[SA - 6.5.258] - Source Control - saDDL invocation bug?

[SA - 6.5.258] - Source Control - saDDL invocation bug?

I get

	Quote:
	Cannot find source code for [schema_name].[object_name]. DDL utility returned error: Error: Exception has been thrown by the target of an invocation.

error message for all but two objects in my database when trying to update the workspace from database. Invoking the saDDL.exe manually retrieves the DDL.

Also, there are some other database entities (assemblies, database triggers, defaults, rules, synonyms, types, etc.) that could/should be retrieved and stored as well.

	Quote:
	Cannot find source code for [schema_name].[object_name]. DDL utility returned error: Error: Exception has been thrown by the target of an invocation. error message for all but two objects in my database when trying to update the workspace from database. Invoking the saDDL.exe manually retrieves the DDL.

This might be an issue with the version of SSMO (SQL Server Management Objects), connection context, or something related. Let's figure out the cause of that error.
Do you have multiple versions of SSMS installed now or in the past on this computer?
When you run it manually from the command line, do you use the same user credentials?
Do you get the same error if you try it fro ma different editor?

	Code:
	assemblies, database triggers, defaults, rules, synonyms, types, etc.

Defaults, indexes, and triggers are saved along the table DDL with all other table specific attributes required to recreate a table.
Assemblies, rules, synonyms, and types are not supported by the SA Source Control Interface yet. This is one of the things planned for future versions

	SysOp wrote:
	This might be an issue with the version of SSMO (SQL Server Management Objects), connection context, or something related. Let's figure out the cause of that error. Do you have multiple versions of SSMS installed now or in the past on this computer?

No. The one I currently use is the only SSMS ever installed to this computer.

	SysOp wrote:
	When you run it manually from the command line, do you use the same user credentials?

Yes. And (though I doubt it's important) the DDL is retrieved regardless of the command line being normal or an elevated one.

	SysOp wrote:
	Do you get the same error if you try it fro ma different editor?

I initiated the check from SA's Repository Browser which was called from SSMS context menu. But I get the same results when trying it from Database Explore in SSMS. Running it from Database Explorer in Notepad++ works though. Funny, I thought that the Repository Browser is an independent window/app regardless where it is called from. Obviously, it isn't.

	SysOp wrote:
	Defaults, indexes, and triggers are saved along the table DDL with all other table specific attributes required to recreate a table. Assemblies, rules, synonyms, and types are not supported by the SA Source Control Interface yet. This is one of the things planned for future versions

Great ;)

	Quote:
	I get the same results when trying it from Database Explore in SSMS. Running it from Database Explorer in Notepad++ works though. Funny, I thought that the Repository Browser is an independent window/app regardless where it is called from. Obviously, it isn't.

It's not independent, it's just like Code Structure View and other left-side panes.

What you have already found out provides a clue that the issue is editor specific. It is possible that a set of different assemblies is loaded depending on the editor from where the DDL Code feature is invoked. Let's try one more thing. Please open command prompt and and change current directory to the BINN folder from where SSMS is started, for example
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE"
from there run saDDL for one of the tables you can get DDL for when you are in SSMS. Please add to the end of the command line -n yes, for example

	Code:
	CD "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE" "C:\Program Files (x86)\SQL Assistant 6\saDDL" -c serverNameHere -d databaseNameHere -s dbo -t table -o -tableNameHere -n yes

This will make it print more diagnostic messages to the output and perhaps the messages will tell us which assembly cannot be loaded or provide a hint for what else could be going wrong.

I ran

	Code:
	C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio>"c:\Program Files (x86)\SQL Assistant 6\saDDL.exe" -c servername -u username -p password -d databasename -s schemaname -t table -o tablename -n yes

to no avail (that is, no errors). Then I checked the help with /? for saDLL.exe and -n was not listed so I tried -m (which was described as logmode). The only difference was that the latter one got some extra lines besides the DDL but nothing relevant:

	Quote:
	Begin Get DDL: ... Creating Connection: ... Searching Database: ... Defining Collate: ... Getting Smo.Scripter: ... Setuping options: ... Creatign SqlSmoObject: ... Result writing: ...

Also, the DDL was retrieved in both cases without any error messages. Seemingly, running saDDL.exe from the folder of the SSMS binary made no difference. What did we expect to happen?

EDIT: I messed around a little bit with the settings and found something that might be useful. It seems that addressing the server by its name is the problem here. We access most of our deployed servers by their IP address since the cannot be accessed using their names. After setting the DDL Extraction utility to :

	Code:
	saDDL.exe -c %1 -u %3 -p %4 -d %5 -s %6 -o %7 -t %8 -m yes

I got the following as error message

	Quote:
	Cannot find source code for [schema].[object]. DDL utility returned error : 'servername' " "" "" "" "databasename" "schemaname" "tablename" "TABLE' is not recognized as an internal or external command, operable program or batch file.

That gave me the idea to try to run saDDL with the servername instead of its IP address. And voilà! The result:

	Quote:
	Begin Get DDL: ... Creating Connection: ... Searching Database: ... Error: Exception has been thrown by the target of an invocation.

saDDL accepts IP for -c but when the Repository Browser is started from SSMS it fetches the server name and uses that instead of the IP address. Any hints on a solution or a workaround?

We wanted to run it from SSMS location to make the process load the same DLLs and assemblies it likely loads when invoked from within SSMS hoping it reproduce the error and print more details as part of the diagnostics. So far we haven't succeeded in reproducing the error.

I'm really unsure about the IP address theory. You have said earlier that it worked for 2 tables, but only for 2, for everything else it was throwing the error. That is contradictory to the IP theory, unless there is some other related cause. Are names of tables, database or schema very long or contain any special characters like non-alpha and/or non-digit characters? On the other hand, you have said DDL extraction works for the very same tables in Notepad++ and in SA built-in editor, it happens only when you try to obtain DDL code from within SSMS. So the issue scope is limited to SSMS, right?

I'm not sure anymore. The behavior is inconsistent. I rebooted and now even those two tables fail to yield their DDL and it doesn't work when initiating it from Repository Browser or Database Explorer in Notepad++ either. From Repo Browser I get the error message mentioned above. Using the Database Explorer with context menu on schema and Update Workspace from Database returns the same error. Trying the same on an single table has no visible result at all (no popup, no Repo Browser window, no progress bar, nada) whereas invoking the same function from an open Repository Browser returns the error message for the selected table.

The only thing I'm able to reproduce reliably are the results of executing saDDL from command prompt. Using the server name I get the error, using the IP address it works fine.

I haven't tried the built-in SA editor.

Please customize SQLServer_ddl.bat file located in C:\Program Files (x86)\SQL Assistant 6\ddl folder.
Ad the end of two saDDL.exe lines lines add -m yes just as you've done it before when running it from command line. From SSMS try getting DDL of a table. Do you see any new messages before the error?

Strange. The result is the same, regardless of -m yes being present or not, both in SSMS and Notepad++.

	Quote:
	/* Cannot find source code for [utastajekoztatas].[kozlemenyek]. DDL utility returned error: Error: Exception has been thrown by the target of an invocation.*/

Are you using Windows authentication in SSMS or SQL authentication?

SQL authentication.

It explains it. When you use SSMS and SQL authentication and connection is shared with SA, it doesn't know what the user/password is and it does even know the authentication method used. To reverse-engineer DDL of a table it runs saDDL utility attempting to connect to the server with Windows authentication and that fails. When you use it with other editors where SA owns the connection, it knows how to connect to the server and passes all required parameters to saDDL. One of the solutions in your case, is not to use saDDL, turn it off in options and make SA use "DDL (MSSQL)" query for all types of objects. You will need to add some code to that query to handle tables, a non-trivial thing. the other solution is to customize SQLServer_ddl.bat file and hard code user and password in that file along with the SQL authentication method, so that they are not parameterized .

By the way, the ADO.NET error returned from SMO could have been more specific, not a generic "the target of invocation failed."

Last edited by SysOp on Sun Dec 15, 2013 11:58 pm; edited 1 time in total

Indeed, now I understand. I should have guessed that from the trio of empty double quotes.

Regarding the solutions, I think I'll probably take the first one, despite I'd rather have SMO to retrieve the DDL. The second one would result in storing password in plain text (not to mention requiring to use the same user/password pair on every server or modifying the file for every connection).

How do I disable saDDL in options?

We are looking into that. It appears in this case the behavior should be a bit different from what I described previously. In case of shared connection with SQL authentication it supposed to trap the connection error in saDDL (once per session), display connect dialog to allow you enter your password, and then continue from there without further prompting.

As far as I understand you don't get that connection prompt with SQL 2012 and Hungarian regional settings on the client. We are checking if connection error trapping is not working on systems like yours.

Splendid! Let me know if I can help in any way!