June 28, 2006 at 12:00 am
Comments posted to this topic are about the item SMO Basics
June 28, 2006 at 3:25 am
A good introduction. I think it's a big and important subject, and there's not much detail on the web on this.
Specifically I've yet to find code that will script my database, and all the objects therein, IN THE CORRECT ORDER. I use tables, views, udfs, sprocs in every database, and when scripting the database have to respect an order of dependencies, if I want a script that will run. Sysdepends is hopeless, however SMO has dependency discovery, i.e. it wont rely on dependency information in system tables, so this should be finally an achievable goal. However the smo code is still rather difficult to realise.
By the way, I don't get the same result as you for the above code. (It doesn't script the database.)
Thanks,
Dave McKinney.
June 28, 2006 at 8:31 am
I had the some problem with the output until I commented out the line that adds the ScriptDrops option. Once I commented that out, the output was exactly the same for me.
Loved the article. I've been struggling with a similar project but this worked great.
Thanks,
Kevin
June 28, 2006 at 9:12 am
I had to add a reference in the project to:
Microsoft.SqlServer.ConnectionInfo
to be able to use Smo.Table. Visual Studio 2005 complained otherwise.
John Scarborough
MCDBA, MCSA
June 28, 2006 at 1:53 pm
It would be helpful (for those of us who are curious but uninitiated) to add what SMO stands for, such as:
SQL Server Management Objects (SMO) ...
I got the above from looking it up on Google. Thanks.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 28, 2006 at 3:00 pm
Andy,
I, as John had complains on the Microsoft.SqlServer.ConnectionInfo when creating an assembly from my own code incorporating your example. When I moved my DLL to C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies where this ConnectionInfo. is located it stopped complaining on ConnectionInfo and started to complain on Microsoft.sqlserver.batchparser
I found one in the Upgrade Advisor directory, copied to Assemblies directory, it now says it is not good.
Trying to resolve.
Also when trying to load the assembly: ???????
Warning: The SQL Server client assembly 'microsoft.sqlserver.smo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The SQL Server client assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
???? SMO ??? Was not fully tested in SQL Server Environment???
Regards,Yelena Varsha
June 29, 2006 at 6:25 am
Yelena, I suspect you have portions of a beta version remaining. You shouldn't have to move your dll, the 'right' way is to set the reference...see next comment.
John, you're correct, you need to add the reference to Microsoft.SqlServer.ConnectionInfo - sorry about that.
David, dependencies are tough because of delayed resolution. I have not found a 'script it all' method, you'd think there would be one, I need to look again! As much as SMO is handy, you might take a look at the Redgate offering, they offer api access to their comparison tool and it has a first class dependency engine built it (as may other comparison tools, but I dont think they offer the api).
June 29, 2006 at 9:00 am
Andy,
This is a new installation from CD given out during the launch event in Boston . It was advertised as a standard edition, not evaluation version. Select @@version returns:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
There was no previous version of SQL Server on this machine, it is a fresh image for Windows XP SP2. The database was moved by backup and restore from CTP, but I also tried on another database. It possibly could be and i will check it that my compliler line (I am using a coomand-line vbc compiler) may have references to something that changed versions. I will check that.
Regards,Yelena Varsha
June 29, 2006 at 9:11 am
I've today written SMO code to 'script it all'. I ran it on a database with 180 objects, including functions, materialised views, sprocs and loads of referential integrity. It managed to give me the correct order for 179 of the objects - and I've no idea why one object caused problems.
(I don't think delayed resolution is an issue though, as dependencies are discovered??)
The code isn't really publishable at the moment (it's a mess) but I'll try to post it once I get it a little prettier.
Essentially it does several passes through the list of objects, looking for objects with zero dependencies or only with dependencies which have already been scripted.
It keeps doing this until there is nothing left to script. In theory, it'll only get confused if there is a circular dependency (e.g. with foreign keys.).
June 29, 2006 at 9:32 am
I will try to work on the dll error when I have time, busy week for me.
David, we'd enjoy having the script all code built into an article, preface with why/how/challenges...contact myself or Steve if you want to give it a try.
To all - could I ask what uses you're finding for SMO? Areas of SMO you'd like to see more content from us on?
June 29, 2006 at 10:48 am
Andy, David
I too will try to make ny code work after the holidays, the changes I made to it was to log the script into files without the user interface, compiled as DLL and tried to import as an assembly to let it work as a stored procedure. It is when trying to Create Assembly I got into the dependency issues, not on the compile stage.
Andy,
One of the SMO uses I find convenient as a database administrator and an application support person is when applications come with a set of database administration tool for non-DBAs for limited application support. This is not for me but for other application support who don't have much of DBA skills. For example, I know one app that has database backup job creation and monitoring capabilities and tools that let an application support person to easily move the database to another server when moving the application. These tools do not replace DBA involvement in the project but can reduce time DBA spend helping app. support person. So SMO should be used by application vendors (creators) to enhance functionality.
Regards,Yelena Varsha
June 7, 2007 at 2:01 pm
Bumped into this article while I was looking for some help with a problem I recently came up with...
When doing a simple loop though all of the databases on a server I noticed something strange with one of the properties; the database.status property. I was originally running my code against a 2000 server and there is one db on that server that is set to Offiline. When looping though the dbs on that server and looking at the status property the offline db reported just like the rest - online. When I switched to a 2005 server and set a db to offline I ran my bit of code and noticed that the status now reported 544 as a code for the offline db, this in turn reports to NOTHING as a code from the enum
Debug.WriteLine([Enum].GetName(GetType(DatabaseStatus), db.Status)) again this return an empty value.
Any ideas on this one?
May 1, 2020 at 5:23 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply