SMOscript for Windows 10

February 9, 2020

I recently got notified that my tool SMOscript raises an error message when run on Windows 10.

I had a look at the problem, and indeed, as soon as the enumeration of database objects (e.g. using commands l (list) or s (script)) is to process a table, the program stop. In case of the script command, an error message is issued

Error: iterating objects: Attempt to retrieve data for object failed for Server ”.

The most recent version of SMOscript was still targeted on .Net 2.0, and used the SMO 100 assemblies. Time to upgrade! 😉

So I changed the .Net version to 4.5 and added the nuget SMO package and changed the .Net version to 4.7 to make the compiler happy.

Tests were performed successfully against SQL Server 2014 and 2019, but now SQL Server 2008 caused an error that I could work-around.

The tool now also supports options to sort database objects by schema and name, or by type and schema and name, rather than relying on the order resulting from the SMO method Database.EnumObjects().

Additionally, the -st (Sort Types) parameter allows to specify a custom sort order for database object types (rather than alphabetically), so that the parameter

smoscript -st Table,View

will process objects of types table and view first, and everything else afterwards. The parameter -st without a list of types will display the available types.

The latest version of SMOscript is available for download here.

Note that there is a regular build “V 0.40”, but also a build using the original SMO 100 assemblies.

smoscript 0.40 command-line parameters

smoscript 0.40 sort SMO Types


Database.EnumObjects() fails on SQL Server 2008

February 7, 2020

After replacing the SMO 100 assemblies (=SQL 2008) with the SMO 150 (=SQL 2019) nuget package, things worked for 2014 and 2019 databases.

But not for 2008 anymore: Calling Database.EnumObjects() now raised a FailedOperationException:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Enum objects failed for Database ‘myDatabase’.
—> Microsoft.SqlServer.Management.Sdk.Sfc.InvalidVersionEnumeratorException: Operation not supported on version 10.0.5538.
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadInternal()
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadFile(Assembly a, String strFile)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitDataFromAssemblyInternal(Assembly assemblyObject, String file, ServerVersion ver, String alias, StringCollection requestedFields, Boolean store, StringCollection roAfterCreation, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Stream configXml)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitData(String file, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.LoadElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetAllElements(Urn urn, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectsFromCache(Urn urn, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects()

However, iterating over the various SMO collections (Database.Tables, Database.Views, etc.) still works!

SMOscript tries to deal with this situation by handling the exception and calling the original enumeration code from back-in-the-old-days to retrieve the selected SMO objects.


SMO and .Net Frameworks and nuget

February 7, 2020

As I upgraded my tool SMOscript to .Net version 4.5, things compiled and ran nicely again.

Only after a while I noticed the Build warnings

1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Assessment, Version=1.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.1” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3275: The primary reference “Microsoft.SqlServer.Management.Assessment, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the assembly “Microsoft.SqlServer.Assessment, Version=1.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” which was built against the “.NETFramework,Version=v4.6.1” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Management.Assessment, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.2” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.
1>C:\Program Files\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets(2110,5): warning MSB3274: The primary reference “Microsoft.SqlServer.Management.SqlParser, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.6.2” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.5”.

So whatever functionality is implemented in the Management and Assessment assemblies, they come with the nuget package (latest version 150.18208.0 in my case) and are also copied to the project output.

Apparently the package definition contains the requirement for Framework “>= 4.6.0”, allows installation in a 4.5 project, but really requires 4.6.2. Reminds me of the dependency inconsistencies back in 2013.

Anyway, upgrading the project to .Net 4.7 got rid of these warnings.

 


GetSmoObject(table) throws FailedOperationException

February 5, 2020

For internal testing, I upgraded by tool SMOscript to .Net 4.5, and referenced the required SMO assemblies from the VS 2017 installation directory

C:\Program Files\Microsoft Visual Studio\2017\Community\Common7\IDE\Extensions\Microsoft\SQLCommon\150\

The most basic operation is to list all objects inside the database (command line parameter l), which worked in principle, except not for tables. GetSmoObject() raises an exception:

ERROR smoscript.SmoBuilder:105 - - Server[@Name='NIIGATA\SQL2014']/Database[@Name='devio.u8']/Table[@Name='cmsDictionary' and @Schema='dbo']
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attempt to retrieve data for object failed for Server 'localhost\sql2014'. 
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. 
---> System.Data.SqlClient.SqlException: Invalid object name 'dbo.cmsDictionary'.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, String dbName, Boolean poolConnection)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con, String database, Boolean poolConnection)
at Microsoft.SqlServer.Management.Smo.PostProcessTable.GetRowResults(DataProvider dp)
at Microsoft.SqlServer.Management.Smo.PostProcessTable.GetColumnData(String name, Object data, DataProvider dp)
at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildObject(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)

The internets were not much of a help, so I replaced the explicit assembly references with the SMO Nuget package and things worked out fine.

My guess is that reading table information via SMO requires more assemblies than immediately obvious, and you need the full package available.


VS – Side-Effects of Upgrading a Project’s .Net Framework Version

September 16, 2013

The other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into SMOscript.

Now, up to the current versions, both applications have been developed using Visual Studio 2008 targeting .Net 2.0. (I prefer to keep requirements to a minimum). But now I thought it was time to migrated them to VS2010/3.5 (client framework), and came across a couple of unresolved mysteries.

Missing Exceptions in SqlCommand.Execute*()

The first mystery is that the behavior of the Execute* methods changed such that ExecuteNonQuery() and ExecuteReader() do not reliably raise exceptions any more if an error occurs in T-SQL.

Take the example

CREATE PROCEDURE TestTemp AS
    CREATE TABLE #T (id int)    
    SELECT * FROM #T    
GO

which checktsql tries to verify (with the SET FMTONLY ON option) like this

BEGIN TRAN
SET FMTONLY ON; EXECUTE [dbo].TestTemp
ROLLBACK

(Note that the transaction is actually created by SqlConnection.BeginTransaction(), but essentially it’s the same mechanism)

SSMS returns an error:

Msg 208, Level 16, State 0, Procedure TestTemp, Line 6
Invalid object name '#T'.

What I noticed after migrating the code to VS2010/.Net3.5 is that some (most?) stored procedures cause the error to be raised as an exception in .Net, and some do not. However, the behavior is consistent for each stored procedure.

Fortunately, I noticed the different behavior only when using the SET FMTONLY ON option, but still, there is a difference depending on which VS or framework version is used.

Teh internets did not really help me – I found a couple of discussions, but no documentation of the change, or how to get the original semantics back:

SMO dependency on System.Core

When I tried to debug (and understand) above issue, and reverted the application back to .Net 2.0, but still build with VS2010, I suddenly got the error message during build:

The primary reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089” which could not be resolved in the currently targeted framework. “.NETFramework,Version=v2.0”. To resolve this problem, either remove the reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

There was nothing I could do to get VS2010 to the original behavior (i.e.: compile successfully) targeting .Net 2.0, even though the same assemblies were referenced both in the VS2008 and the VS2010 project.

This thread on MS connect shows the “amusing” dependency zigzag that SMO assemblies implement, switch dependencies even between minor releases:

Posted by Chris Dennis on 4/20/2011 at 10:19 AM
Installing SQL Server 2008R2 Cumulative Update 7 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1777.0 which again depends on System.Core v3.5.
Posted by Chris Dennis on 3/25/2011 at 12:50 PM
Installing SQL Server 2008R2 Cumulative Update 6 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1765.0 which does not depend on System.Core v3.5. Only version 10.50.1750.9 has a dependency on System.Core v3.5.
Posted by Microsoft on 3/25/2011 at 11:08 AM
Your assertion is correct the Microsoft.SqlServer.Management.SqlParser took a dependency on System.Core v3.5. Any projects which reference this assembly directly or indirectly need to be targeted to .NET Framework V3.5.
This is a nightmare!

SMOscript and Change Tracking

May 7, 2013

SMOscript user Robert pointed out that SMOscript did not support the Change Tracking option of the ScriptingOptions parameter class.

Indeed, of the new Change Tracking features found in SQL Server 2008 and higher, SMO only supports scripting Change Tracking, but does not generate the commands to script Change Data Capture.

This seems to be a known omission of SMO, and it will not be implemented any time soon:

We took a look at this DCR (this is not defect but actually DCR to add suport of SMO to CDC) along with several others. Unfortunately based on current customers votes count we decided not to proceed with this DCR in the next release. However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this implemented.

which is kind of strange considering you use SMO to re-create an existing database schema in a new schema, and maybe your T-SQL business logic relies on objects created by CDC.

Nonetheless, SMOscript 0.20 now also create the ENABLE CHANGE_TRACKING if used with the -ct switch.

As usual, the latest version of SMOscript is available for download here.


Writing “Funny Characters”? Make sure you use the correct Encoding!

January 14, 2013

A user of SMOscript sent me a bug report:

Some of our stored procedures have apostrophes in comments. If we export our DB and enable the “if not exists” option, our scripts become invalid, as the commented apostrophe ends the string.

He suspected SMOscript might encode the SP code incorrectly, but I made sure that was not the case, so I asked for a (redacted) sample of such a stored procedure.

I added the SP to a database, and in SSMS ran Generate Script on the procedure both with and without the IF NOT EXISTS option. In “plain” mode this simply generates the CREATE PROCEDURE statement, whereas in the IF NOT EXISTS case it will generate the IF NOT EXISTS check, and then create the SP using sp_executesql and dynamic SQL (and some people do not like this very much).

Next, I ran SMOscript with the -i switch, which activates the IF NOT EXISTS mode:

>smoscript.exe -s localhost -d mydatabase -o fooSP -i > fooSP.sql

When I opened the generated file in SSMS, and indeed, the lines that originally contained an apostrophe not contained a quote which ended the string. But I also noted that other quote characters got correctly escaped using 2 quotes.

Then it struck me: the email mentioned apostrophes, but what I saw here was quotes!

I opened the original file in Notepad++ in hex mode, and there it was: e2 80 99, the UTF-8 encoding for U+2019, formally named RIGHT SINGLE QUOTATION MARK, but apostrophe among friends 😉

Given its code point, it is obvious that this character is neither in the ASCII nor in the ASCII character set, so SMOscript has to generate Unicode or UTF-8 encoding.

Fortunately, this functionality is already built-in: use -T for Unicode, or -U for UTF-8 encoding:

>smoscript.exe -s localhost -d mydatabase -o fooSP -i -U -f .\fooSP.sql

Note that I use the -f switch to directly write the output to a file, since the console might not be able to display Unicode characters, or it might, and I’m not smart enough 😉 Anyway, directly creating the file from SMOscript frees you from the hassles of piping “funny” characters.

In hindsight, what happened was that SMOscript wrote the output to the console (via piping), and, using the native code page, the console was not able to correctly output the apostrophe character, and replaced it with the quote character, thus breaking the string parameter for sp_executesql.

You should always be aware that if you translate between code pages, not every character can be mapped to a character in the target code page, and some mappings will cause difficulties.

Fortunately, this problem was quite easy to solve.