I am new to .Net Programming and would like to know if Reflection is suitable for traversing an Object to discover Collections within an instance of a class. I am working with the Microsoft.SqlServer.Management.Smo Namespace. The plan is to connect to a database, determine what collections exists, then for each of those collections, if...
I am trying to enumerate all SQL Server instances installed on a local machine. I am using SmoApplication.EnumAvailableSqlServers(true). However, only SQL Server Express 2005 instances are shown. Default 2008 instance is not shown at all! I tried 2 other solutions with SqlServerRegistrations.EnumRegisteredServers() and SqlDataSourceEnum...
I recently copied a MSSQL2000 DB to a MSSQL2008 DB. Afterwards I ran a script to update all the text/varchar fields in the tables to the same collation as the model db. However, all my copied views still are using the same collation as the previous db. The easiest way I found to fix this would be to have MS SQL Management Studio create t...
We upgraded our application to .NET 4.0 a while ago, and now need to add the ability to backup SQL Server 2008 databases from the app. However, it doesn't seem that SMO is available for .NET 4. Is there a workaround or better way to perform a database backup (note that our backups need to be user initiated over a website, so we cannot ...
Hello, in our company's product, we attach a database to SQL Server, and we need to add credentials to some accounts to the catalog. Currently we're using SMO to do this, but this gives us problems (x86 vs x64, SQL Server 2005 vs 2008, etc) Is there's anyway of adding a login (to the server) and a user (to the catalog) using SQL? This...
I'm making a call to odbc32.dll (SQLBrowseConnect) to return a list of databases on a sql server. From running a trace I can see the query being executed is select name from master..sysdatabases where has_dbaccess(name)=1 If the credentials I pass aren't the sa user it returns just the system databases. Is there anyway I can use SQL...
The project I'm working uses SMO to restore the needed databases as part of the application installer. The problem is, on a machine that has never had the application installed. From the event logs, it seems like the database can't be restored because it doesn't exist in the instance. (Due to the fresh install) Can I use SMO to restore...
I am trying to script data of a table. If I use windows authentication (Integrated Security=true), code works. If I switch to sql authentication with existing user and password, it fails trying to access returned scripts enumerable. The user is an SQL login and admin. Simplified version of code is here: //string connectionString ...
I am developing a tool that will eventually be used to run a sql script on 5000+ production databases simultaneously. For now it will only be used to run scripts on our Dev and QA databases. But I want to design it in the most extensible way, and have no experience doing something like this, so I could use some advice. Technology I'm cu...
I get this error message when I run the Powershell script at the bottom: Exception calling "EnumScript" with "1" argument(s): "Script failed for Table 'dbo.Product'. " At :line:48 char:35 + foreach ($s in $scripter.EnumScript <<<< ($tbl)) { write-host $s } However, when I comment out the output_file line #$output_file="C:\Product.sq...
In my C# 3.5 application I am using SMO to locate runnin SQL Server instances. However, on a clean machine with SQL Server installed I receiving the following exception: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> System.IO.FileNotFoundException: Could not find Assembly "Mic...
I'm using the following code to back up a SQL Database : void BackupDatabase(string sConnect, string dbName, string backUpPath) { using (SqlConnection cnn = new SqlConnection(sConnect)) { cnn.Open(); dbName = cnn.Database.ToString(); ServerConnection sc = new ServerConnection(cnn); Server sv = ne...
I'm using the following code to restore a backup sql database with C# and SMO. void RestoreDatabaseWithSMO(string sConnect, string dbName, string backUpPath) { using (SqlConnection cnn = new SqlConnection(sConnect)) { cnn.Open(); cnn.ChangeDatabase("master"); ServerConnection sc = new ServerConnection(cn...
I googled but cannot find proper simple code for using backup database full and restore the backup, using defaults by using C#. I am using C# Visual Studio 2008 and SQL Server 2008 R2 evalution but I guess SQL Server 2005 and C# will work too. The Microsoft site doesn't have code regarding it, it only show description of Microsoft.SqlS...
I am creating a simple web management studio using SMO. Is it secure enough to persist/store user information (login and password for database) using ASP.NET mechanism (e.g. formsauthentication, cookies, etc.). What will be the best practice to do this? thanks ...