SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Scripting Database Objects using SMO
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/29/2005 :  13:57:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 12/02/2005 :  01:44:58  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/02/2005 :  13:21:03  Show Profile  Visit graz's Homepage  Reply with Quote
Interesting. I'll take a look this weekend and update the article. Thanks!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/04/2005 :  23:13:12  Show Profile  Visit graz's Homepage  Reply with Quote
Thanks Euan. The article and sample code are updated.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 12/13/2005 :  11:22:01  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com
Go to Top of Page

alzdba
Starting Member

Belgium
10 Posts

Posted - 12/14/2005 :  03:13:35  Show Profile  Reply with Quote
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!
Go to Top of Page

alzdba
Starting Member

Belgium
10 Posts

Posted - 03/24/2006 :  02:56:15  Show Profile  Reply with Quote
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

Edited by - alzdba on 03/24/2006 03:01:57
Go to Top of Page

avnkkishore
Starting Member

2 Posts

Posted - 06/19/2006 :  07:06:30  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 06/20/2006 :  18:10:30  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

avnkkishore
Starting Member

2 Posts

Posted - 06/21/2006 :  01:31:48  Show Profile  Reply with Quote
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
Go to Top of Page

blah
Starting Member

1 Posts

Posted - 04/08/2008 :  15:23:11  Show Profile  Reply with Quote
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
Go to Top of Page

kosmas
Starting Member

3 Posts

Posted - 06/06/2008 :  08:02:57  Show Profile  Reply with Quote
How we can script sql objects in sql 2005 from sql script ? (like sqldmo in sql 2000)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/06/2008 :  09:10:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
kosmas -- maybe try reading the article?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

kosmas
Starting Member

3 Posts

Posted - 06/06/2008 :  11:04:34  Show Profile  Reply with Quote
All previous posts are for scripting with SMO using a programming language. I need to script from transact-SQL.
Go to Top of Page

mbourgon
Starting Member

5 Posts

Posted - 10/06/2011 :  08:56:41  Show Profile  Reply with Quote
Alternatively, I'd love a T-SQL script that was able to invoke SMO.
Go to Top of Page

paultech
Yak Posting Veteran

Egypt
79 Posts

Posted - 10/29/2011 :  14:10:59  Show Profile  Reply with Quote
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
Go to Top of Page

deepakvermaseo
Starting Member

India
6 Posts

Posted - 11/22/2011 :  06:45:53  Show Profile  Reply with Quote
article is simple way of understanding of information but its should be very unique content.

Deepak Verma
Go to Top of Page

dhaya2011
Starting Member

1 Posts

Posted - 11/29/2011 :  07:49:42  Show Profile  Reply with Quote
I agree this one.. We can get more points through this article.. very interesting one...
unspammed
Go to Top of Page

localprice
Starting Member

USA
1 Posts

Posted - 03/24/2012 :  04:53:57  Show Profile  Reply with Quote
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
Go to Top of Page

webguru11
Starting Member

2 Posts

Posted - 03/27/2012 :  01:57:11  Show Profile  Reply with Quote
the sooner the bug is fixed the better it is for all us developers.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000