What's the best method to check if SQL server exists or not? I'm trying Microsoft.SqlServer.Management.Smo.Server.PingSqlServerVersion() and it works fine if server exists and available. But it kinda pretty slow, if there is no such a server. Is there any fast enough method to check without even defining user credentials (only the se...
What would be the simplest way to create an exact structural replica for Table1 and all its indexes in the same database. Let's say Table1Dupl where all constraints/indexes from Table1 with suffix Dupl. I understand that I may do manual renaming "script table as " "create to" from Management Studio, but looking for fully programmatic w...
I'm working on a project that leverages database metadata. To date I've just queried the database catalog views (e.g. sys.tables) but I know this will be "easier" with SMO if I'm willing to add the dependency. What are the advantages to SMO over metadata queries? Any "gotchas" (e.g. needing to have additional SQL libraries on my local ...
my only problem is that Im trying to save the data i get from the MS SQL when I call the backup function using Pipe ! I dont want to save it as File , I can use VirtualDevice too , but i found NOTHING about both of them online ! some say that Pipe is not used anymore , any suggestions ? or alternatives ? I dont want to use this code: B...
My question is straight forward, how can I program SQL SMO to take local backup, by connecting to a remote server. I want to save the bak file to a local machine which is connecting to the remote server. Also I want users with only certain privilege to be able to save backups locally. ...
source http://technet.microsoft.com/en-us/library/ms162234%28SQL.100%29.aspx code //Connect to the local, default instance of SQL Server. { Server srv = default(Server); srv = new Server(); //Create a linked server. LinkedServer lsrv = default(LinkedServer); lsrv = new LinkedServer(srv, "OLEDBSRV"); ...
The following is the code. I have written every posssible handler for server object I can think of, but still the horrible page appears. when i give the name of the SERVER INCORRECTLY in textbox1 which is used further. protected void Page_Load(object sender, EventArgs e) if (TextBox1.Text.Length>0) { Server srv; ServerCo...
I'm in the midst of writing a script that is updating the collation of all varchar columns for every table in my database (Sql Server 2008). I need a way to collect a list of each constraint in a table so that I can drop it, alter the columns the constraint affects, and then re-add the constraint. I know that I am able to grab UK constra...
In this previous question I asked I found that constraints created with Create Index do not exist in sys.objects. I'm already using ReportSchema() and looping through Indexes and IndexColumns collections. Is there a way to determine the method a constraint was created by using the collections available to GetSchema() or ReportSchema()...
I am using SQL Server 2005 Express. I want to use SMO to loop through each table in a database and change each Char column to a Varchar column. If a column is a member of the primary key, I need to first drop the primary key before altering the datatype of the column. Then I need to recreate the index. Here is the code I am trying to...
Has anyone managed to get the transfer mechanism working with FILESTREAM columns? My code is as follows: Transfer transfer = new Transfer(sourceDatabase); transfer.CopyAllObjects = true; transfer.DropDestinationObjectsFirst = true; transfer.CopySchema = true; transfer.CopyData = false; transfer.CopyAllTables = true; transfer.Options.W...
How can I determine from code, if SQL Server Agent is running - using SMO? I would think there was a property on the JobServer class, but I can't find anything. Powershell is not an option. ...
I've written a Powershell script which will compare two databases and come up with a list of objects in one of the databases to remove. I put those items (as a customized object with a name and schema) into an array. In the next step of my script I iterate through the objects in the database and see if they match an object in my array. ...
How do I deploy to a shared hosting environment... code and database (full blown SQL). What is your favorite way to get a 50MB single project ASP.NET Web Application Project and database to a live server with FTP and SQL Management Studio ports accessible? After a day of exploration mine is: - Use the Web.Debug.config to easily allow t...
I would like to automate script generation (in SSMS --> Tasks --> Generate Scripts) in SSMS 2008. I have read that SQL Server 2008 does not support Database Publishing Wizard (including SQLPUBWIZ SCRIPT) but this automation can be done using SMO in SQL Server 2008. I don't have a clue about SMO and how to do this using SMO, so could you ...
1) If there is already a login 'user1' then I create a user 'user1' for database 'db1' by calling CreateDatabaseUser function 2) Else, I create a login 'user1' with password 'password1' with default database as 'db1' (only after creating db1) using CreateServerLogin and then I create user 'user1' for 'db1' using CreateDatabaseUser ...
I'm trying to dynamically get a databases Table structure using only C# code as follows: using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; public class LoadStuff { ... public void LoadDatabase(string vDatabaseName) { using (var vSqlConnection = new SqlConnection(DatabaseConnectio...
Hi How do you make SMO release it's connections? I have this code: public static class SqlServerConnectionFactory { public static Server GetSmoServer() { using (var c = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)) { var s = new ServerConnection(c)...
My C# application uses SMO to do various things with SQL Server instance chosen by a user. Particularly, it changes authentication mode: ServerConnection conn = new ServerConnection(connection); Server server = new Server(conn); server.Settings.LoginMode = ServerLoginMode.Mixed; After changing login more instance should be restarted....
My application lists the available network SQL Servers using Return SmoApplication.EnumAvailableSqlServers(false) Until recently, I was running XP Pro, with SQL 2005, SQL 2008, and Visual Studio 2008. All worked perfectly, correctly listing the default SQL05 server, and the SQL08 named instance. I've now upgraded to Windows 7 x64, ...