smo

SMO and Sql Server 7.0

Does anyone have a definitive answer to whether Sql Server Management Objects is compatible with Sql Server 7.0? The docs state: Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008, you easily manage a multi-version environment. But trying to connect to a Sql 7 instance gets ...

What's the best way to detect the presence of SMO?

I have some code that uses SMO to populate a list of available SQL Servers and databases. While we no longer support SQL Server 2000, it's possible that the code could get run on a machine that SQL Server 2000 and not have the SMO library installed. I would perfer to check for SMO first and degrade the functionality gracefully instead ...

VB6 support for SQL SMO and .Net 2.0

We are trying to move from using SQL DMO to SMO in our COM+ based application, as we are dropping support for SQL Server 2000 and adding support for SQL Server 2008 in addition to SQL Server 2005. I have been reading around on this, and found this particular quip on this microsoft forum: "SMO is only supported in VB/C#.Net 2005. It r...

Microsoft.SqlServer.Management.Smo and its brothers works on SQL2000?

As title, I need that to run an import script generated by SQL Server DB Publishing Tool. Would that work on Sql2000 server too? Also I have seen ppl reporting missing library issues related to GAC, which libraries I precisely need to include if I am not controlling the deployment server? To know how this thing works you can check here ...

How to make app fully SQL Server 2005 compatible?

We have an application that use MSSQL, when we move to 2005, basically we recommend to our client to apply the backward compatibility package and that’s it, but somebody ask me what to do to be 100% compatible on 2005. I was thinking on 1. Compatibility level: SQL Server 2005 (90) 2. Remove any DMO reference, and replaced by SMO 3. Conne...

Using SMO to copy a database and data

I am trying to make a copy of a database to a new database on the same server. The server is my local computer running SQL 2008 Express under Windows XP. Doing this should be quite easy using the SMO.Transfer class and it almost works! My code is as follows (somewhat simplified): Server server = new Server("server"); Database sourceDa...

Using SMO to get create script for table defaults

I'm trying to create a database scripter tool for a local database I'm using. I've been able to generate create scripts for the tables, primary keys, indexes, and foreign keys, but I can't find any way to generate create scripts for the table defaults. For indexes, it's as easy as foreach (Index index in table.Indexes) { Scriptin...

Imitating SQL Server Profiler in a C# app??

I want to create a trace on a database server from my C# app, like what you do in SQL Server Profiler. I have found stored procedures (sys.sp_trace_create etc...) that dont seem to work for my SQL management studio. I was wondering if anyone could help with the coding, or where i would start to do this?! ...

Using SMO to create and set Database permissions?

Is there a way to create new logins and set database permissions on a SQL Server 2005 using C# and SMO? If so, how? If not, what is the best way of doing that? ...

server.Databases[databaseName].Drop(); fails in MSTest

The following exception is thrown: Error Message: Microsoft.SqlServer.Management.Smo.FailedOperationException: Drop failed for Database '4d982a46-58cb-4ddb-8999-28bd5bb900c7'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Dat...

Source Safe not saving "exclude" SQL SMO dll settings in Visual studio 2005

We use VS2005 and SourceSafe here at work for a project. I have this Setup and Deployment project for a couple of projects which have SQL SMO dll dependencies. I do not want to deliver these DLLs, so i set them all to "exclude" from the target. However when I check the solution in, and pull it back out from VSS, four of those 8 dlls come...

How to find SPID of a executing SqlAgent job using SMO

With SMO objects using Server.JobServer.jobs to get a list of jobs, I can find the status of each job. For those that are currently executing I would like to find the SPID it is executing on. I can also get a list of the server's processes using Server.EnumProcesses(). This gives me a list of currently active SPIDs. I want to match th...

Is there anyway to speed up SQL Server Management Objects traversal of a existing database?

I'm currently using SMO and C# to traverse databases to create a tree of settings representing various aspects of the two databases, then comparing these trees to see where and how they are different. The problem is, for 2 reasonably sized database, it takes almost 10mins to crawl them locally and collect table/column/stored procedure i...

Use smo to rename datafiles

How can I use SMO to rename the physical .mdf .ndf .ldf files. This article was helpful but I need to use C# SMO Objects. Using the SMO Server Object I can retrieve the database, then Get Access to the DataFile objects. Per this link. These have a Rename, however after rename, nothing changes. ...

SQL 2008 SMO Database Status property memory leak.

It appears there is a memory leak in the Status property of the SMO Database class. Using the code below with SQL 2005 SMO libraries works fine, but as soon as you use SQL 2008, the memory leak appears.... Any other good way of getting the database staus in SQL 2008? A quick example that magnifies the problem: private void button...

SMO scripting objects and security

I have a customer that has a SQL database on a hosted server; call the db "myDatabase". The hosting co. has locked down object explorer - I can't myDatabase thed database listed (I see tempdb and master). However, if I "use myDatabase" and then "select * from myTable", all works fine. Since we have no access to object explorer, I can'...

How to change table name when script a table from SQL Server SMO

Let's say I have a table "foo" that I want to script out using SMO. Is there a way to have it so that in the generated script the table has a different name such as "foo2"? Database db = sqlServer.Databases["testdb"]; Table foo = db.Tables["foo"]; foo.Name = "foo2"; If I do this, I get this exception when I try to set foo.Name: "Can...

How to get the table a foreign key refers to

I have an small question I didn't found an answer yet: how do I get in c# and using Microsoft.SqlServer.Smo the table a foreign key column is referring to? foreach (Column column in currentTable.Columns) { if (column.IsForeignKey) { //GET TABLE FOREIGN KEY REFERS TO } } ...

How do I programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

Any pointers on how I can programmatically get exactly the identical stored procedure source from SQL Server 2005, as when I right-click on that stored procedure in SQL Server Management Studio and select modify? I'm trying using SMO, but there are some textual differences. The procedure always has CREATE, not ALTER, and there are some...

Sql SMO: How to get path of database physical file name?

I am trying to return the physical file path of a database's mdf/ldf files. I have tried using the following code: Server srv = new Server(connection); Database database = new Database(srv, dbName); string filePath = database.PrimaryFilePath; However this throws an exception "'database.PrimaryFilePath' threw an exception of type 'Mi...