I have the following PowerShell code: function Get-SmoConnection { param ([string] $serverName = "", [int] $connectionTimeout = 0) if($serverName.Length -eq 0) { $serverConnection = New-Object ` Microsoft.SqlServer.Management.Common.ServerConnection } else { $serverConnection...
I am trying to use SMO (VS 2010, SQL Server 2008) to connect to SQL Server and view the server protocol configuration. I can connect and list the Services and ClientProtocols as well as the account MSSQLSERVER service is running under. However, the ServerInstances collection is empty. The only instance on the target server is the default...
Hi, I want to programatically (c#) restore a database (SQL Server 2008) which was originally from my live environment. My local environment is obviously quite different, with different drive mappings. I have followed this article (http://www.mssqltips.com/tip.asp?tip=1849) which has been a great help, but I am struggling to find out h...
I am successfully using Powershell with SMO to backup most databases. However, I have several large databases in which I receive a "timeout" error "System.Data.SqlClient.SqlException: Timeout expired". The timout consistently occurs at 10 minutes. I have tried setting ConnectionContext.StatementTimeout to 0, 6000, and to [System.Int32]::...
I'm trying to generate scripts from a database, but with slight modifications to those objects. For example, I want to generate the script for a stored procedure, but I want the script to create it under a different schema. I have an example of what I'm currently trying below. If I'm going in a completely wrong direction then please poi...
I am using SMO in C# to run an SQL script. I need to output the results of the file into a text file. When using a command line to run the query I can get the desired result using the "-o [output file]" argument. Is there a way to carry out the same operation using an SMO object? At the moment my code simply sends an sql script to a se...
SSMS allows you to right-click a table and "Script Table as"->"Select to". I thought that almost everything in SSMS was done through the SMO scripting engine, but I can't seem to find a way to do this through SMO short of looping through columns and generating the script myself. Is my Google-Fu weak, or are people just not using SMO for...
I'm using the below to restore a database in VB.NET. This works but causes the interface to lockup if the user clicks anything. Also, I cannot get the progress label to update incrementally, it's blank until the backup is complete then displays 100% Sub DoRestore() Dim svr As Server = New Server("Server\SQL2008") Dim res As Rest...
I'm using IronPython and the clr module to retrieve SQL Server information via SMO. I'd like to retrieve/store this data in a SQL Server database using SQL Alchemy, but am having some trouble loading the pyodbc module. Here's the setup: IronPython 2.6.1 (installed at D:\Program Files\IronPython) CPython 2.6.5 (installed at D:\Python26...
Is it possible to list all triggers in all tables on a database? I can list all user stored procedures with: Private Sub StoredPro() Dim theServer As Server = New Server(serverName) Dim myDB As Database = theServer.Databases("mydatabase") Dim dt As DataTable = myDB.EnumObjects(DatabaseObjectTypes.StoredProcedure) End Sub B...
I am using SMO to create databases and tables on a SQL Server. I want to do so in a transaction. Are both of these methods of doing so valid and equivalent: First method: Server server; //... server.ConnectionContext.BeginTransaction(); //... server.ConnectionContext.CommitTransaction(); Second method: Server server; // ... SqlCon...
I'm trying to copy SQL database from one server to another. Please tell me how to use Transfer() method without loosing constraints in the target database. I've tried different parameters including CopySchema = true; // and CopyAllSchemas = true; //and even CopyAllObjects = true; and still this damn thing is losing all the...
Transfering databases between servers or creating a copy of db within the server's instance is easy enough. We can use Microsoft.SqlServer.Management.Smo.Transfer class. But what if I need to read all the structure of db, save it somewhere on the disk and then sometime later use that data to create a database according to the data previ...
I'm trying to copy sql db between servers using Smo.Transfer class. And it's ok when you're copying absolutely normal database. It works. But what if your data inconsistent? For example I have a function and it gets the value from a table and non-existed column (someone renamed the column and now the function wouldn't work). But if yo...
I don't understand why sometimes Transfer.ScriptTransfer() generates a script where some CREATE VIEW statements come even before the actual table declarations. How's that possible? ...
i use this SMO code to attach a database. Server mServer = new Server("."); mServer.AttachDatabase("DbName", new StringCollection { "DbName.mdf", "DbName_log.LDF" }, AttachOptions.None); But i keep getting the exception Failed to connect to server. Any Ideas? [EDITED] Inner Exception...
Tell me please what's the best way to copy db structure to another server? I'm trying to generate a script in Management Studio and then run that script on the remote machine. And I hate that thing. It works unpredictable, you have to fix manually a lot of crap in the generated script. I guess that isn't the best solution. Then I tried...
Hi, My problem is really simple. I have a .bak file that contains one or more backup set. When I'm using SMO to restore the database with this .bak file, it only takes the first backup set to do its work. It seems to ignore the remaining sets. Why's that ? See my code : //Sets the restore configuration Restor...
I am using the Microsoft.SqlServer.Management.Smo classes to script out SQL scripts for stored procedures, tables, views etc. I am doing this for putting them in source control. Instead of the CREATE scripts for stored procedures, how can I get ALTER scripts for them? Is there an setting / option in the ScriptingOptions' properties? ...
In a windows forms application, using C# I want to programmtically pass the SMO DependencyWalker a single object ( table, View, etc ), discover the dependencies related to that object and then load a WinForms TreeView with the results where the TreeView looks exactly like the TreeView within the SQL Server 2008 Management Studio "View De...