Article: Scripting Database Objects using SMO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Scripting Database Objects using SMO

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-29 : 13:57:38

With the introduction of SQL Server 2005, Microsoft has created a new .NET management API for SQL Server called SQL Management Objects (SMO). As I started working with SQL Server in earnest following its release I discovered a few limitations that I hoped to correct using SMO. This article describes those problems and how to use SMO to script database objects.

Article Link.

euan_garden
Microsoft SQL Server Product Team

34 Posts

Posted - 2005-12-02 : 01:44:58

The perf issue you are having is somewhat by design. Let me explain.

As part of optimising the perf of SMO it no longer brings back all properties by default as DMO used to, an attempt has been made to guess the most common properties and to fetch those by default. System is not one of those so when you retrieve it, its not efficient. You can force SMO to pull back the System property.

Check out info and examples on DefaultInitFields, also this blog post:
http://sqljunkies.com/WebLog/euang/archive/2004/04/01/1889.aspx

-Euan

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-12-02 : 13:21:03

Interesting. I'll take a look this weekend and update the article. Thanks!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-12-04 : 23:13:12

Thanks Euan. The article and sample code are updated.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jhermiz

3564 Posts

Posted - 2005-12-13 : 11:22:01

Nice article graz,

I wonder how soon they are going to fix that bug, I can't seem to understand why one cant use both in a single script.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

alzdba
Starting Member

10 Posts

Posted - 2005-12-14 : 03:13:35

Yep, it is _very_ slooooow indeed !
I have this appl wich scripts all my servers userdatabases according to settings in a "mysqlservers"-database.
With sqldmo it works at an acceptable speed, with smo it takes more than double that time!

alzdba
Starting Member

10 Posts

Posted - 2006-03-24 : 02:56:15

Anyone have an idea why SQLSMO.Server.SystemMessages only generates messages < 50001 ?

And how to extract usermessages ? (i.e. > 50000)

Never mind userdefinedmessages collection helps a lot

avnkkishore
Starting Member

2 Posts

Posted - 2006-06-19 : 07:06:30

Hi All,

The information is useful. And i started writing a small application by visiting the following URL.
http://www.sqlteam.com/item.asp?ItemID=23185.

My intentions is collect information from an sql server which was running on a different domain. I used the following lines of code...

Server sqlServer = new Server("tefen\\pespi01");
MessageBox.Show("Version:"+ sqlServer.Information.Version.ToString());

Here 'tefen' is the domain name, and 'pespi01' is my sql server's computer name(this is present in tefen domain).
When i run the application i got the following error message 'Failed to connect to server tefen\pespi01'.

I tried using the IP address of computer 'pespi01'. But the same error message is coming.

Can any one please explain is there any thing wrong in the above code or do i need to anything more for this.

Thanks in advance.
Kishore

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-06-20 : 18:10:30

It should be just

Server sqlServer = new Server("pespi01");

It doesn't use the domain name. Can you connect to the server by that name using SQL Server Management Studio?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

avnkkishore
Starting Member

2 Posts

Posted - 2006-06-21 : 01:31:48

Hi qraz,

Thanks for the reply. I slightly modified my code and now it is working fine. The following is the one that i used to connect...

ServerConnection serverConnection = new ServerConnection();

// set the connection attributes
serverConnection.ServerInstance = strSQLServerName;
serverConnection.LoginSecure = false;
serverConnection.Login = strUserName;
serverConnection.Password = strPassword;

// create sql server instance
Server sqlServer = new Server(serverConnection);

here 'strSQLServerName' is the server name (like 'pespi01' as i mentioned in my previous mail). this SQL Server authentication is successful.

I have a small doubt regarding windows authentication. when i pass server name directly to 'Server sqlServer = new Server(sqlServerName)' it is failing.

I observed that if the sql server is present in the same domain, there is no problem. Is it the expected behavior of SMO. Can any one explain me is there any way to connect & get information from a different domain SQLServer with windows authentication?

Thanks in advance,
Kishore

blah
Starting Member

1 Post

Posted - 2008-04-08 : 15:23:11

I'm using SQLDMO , sp_OAmethod from t-sql to script objects.

Ex: ( from http://www.dbazine.com/sql/sql-articles/larsen4 )

quote:

EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
...
SET @ScriptType = 1|4|32|262144
SET @method = 'Databases("Northwind").Tables("Orders").Script(262214)'
EXEC sp_OAMethod @oServer, @method, @Tsql Output

M$ documentation: msdn2.microsoft.com/en-us/library/ms135191.aspx

Using this, we can script both 'DROP' & 'CREATE' statements in one go

Is there an option number to script 'Use Database'?

Thanks

kosmas
Starting Member

3 Posts

Posted - 2008-06-06 : 08:02:57

How we can script sql objects in sql 2005 from sql script ? (like sqldmo in sql 2000)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-06 : 09:10:43

kosmas -- maybe try reading the article?

- Jeff
http://weblogs.sqlteam.com/JeffS

kosmas
Starting Member

3 Posts

Posted - 2008-06-06 : 11:04:34

All previous posts are for scripting with SMO using a programming language. I need to script from transact-SQL.

mbourgon
Starting Member

6 Posts

Posted - 2011-10-06 : 08:56:41

Alternatively, I'd love a T-SQL script that was able to invoke SMO.

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-29 : 14:10:59

It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.

This also brings up one limitation of T-SQL: how to get an object definition to disk

paul Tech

deepakvermaseo
Starting Member

6 Posts

Posted - 2011-11-22 : 06:45:53

article is simple way of understanding of information but its should be very unique content.

Deepak Verma

dhaya2011
Starting Member

1 Post

Posted - 2011-11-29 : 07:49:42

I agree this one.. We can get more points through this article.. very interesting one...
unspammed

localprice
Starting Member

1 Post

Posted - 2012-03-24 : 04:53:57

Anyone who can help me with online platform to learn more about SQL server modules. Quite new to this and want to understand this better.
unspammed