I've read a dozen different blogs, as well as reading through the msdn examples and they just aren't working for me. Ultimately what I'm trying to do is automate moving a DB from our production instance to our dev instance, or the other direction. The approach I've taken is thus: 1. backup/restore to a temp DB 2. detach temp DB 3 copy ...
I am trying to add new roles to a SQL 2005 database via the SMO assemblies. The Roles.Add method just does not seem to add the new role. I have my user account set as securityadmin and sysadmin. Below is the code extract that I am trying to use to set the new role: [assuming d has been set to a database object] Dim dr As New Da...
I'm making a Utility that compares 2 Databases and makes one Equal to the other by changing and adding objects (Tables, Views, UserDefinedDatatypes, Defaults, Functions etc etc etc) I'm trying to add a Column, not null with a userdefineddatatype wich has a Default defined, the problem is this: I'm not able to use BindDefault before the...
Hello, I've been looking at the possibility of creating a CLR trigger for insert, but every tutorial found gives me examples of using ado.net objects to do the logic - this is not very convenient way..(I don't think I have to convince anyone about that) I was wondering if maybe there is another way for doing this? SMO? Linq? Anything e...
I wrote an application to use SQL SMO to find all SQL Servers, databases, jobs and job outcomes. This application is executed through a scheduled task using a local service account. This service account is local to the application server only and is not present in any SQL Server to be inspected. I am having problems getting information o...
Hi, Is it possible to run a script using Microsoft.SqlServer.Management.Smo? I am able to generate script using the Microsoft.SqlServer.Management.Smo.Scripter class. I would like to edit it and run it to create copy of database objects. Thanks. ...
I'm trying to start using some SQL Server - SMO functions to write some VB.Net code to manage SQL Servers. I have SQL Server 2008 Express loaded on my workstation and Visual Studio 2003 for my development platform. When I try to create a reference in my VB project, I go to references->Add Reference and browse for SMO .dll files and no ma...
I have an application that uses SMO to manage databases. It works great on XP and Server 2003. However, when I try to run it on Windows 7, I get the following exception: Restore failed for Server 'localhost'. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Cultu...
I would like to use SMO to generate a set of insert statements to copy data between environments. I have this working, but for some reason, datetime fields are formated as hex values that are casted to datetime. This makes the scripts hard to read and impossible to update. Is there a way for me to change this to use a string represent...
I want to manage SQL Server dynamically, that is through code I wan to scan SQL Server for available databases than I want to create new database, access older database, alter it, and insert data into database depending upon table schema what is better approach what should I use? i heard name of nhibernate, SQL Server Management Object ...
I have an application where I create database's dynamically in a SQL Server using Server Management Objects through my application. I want to use the Entity Framework to access this database when it has been created, is this possible? As I can not generate Entity classes from a database in VS. I do have the structure of the database ...
Hello All, I am currently working on a process of restoring backup and upgrading it to a specific version in a project. To upgrade the backup to a specific version, I use batch sql files ordered in predefined format. While executing these batch files, I must set databases in single user mode. And, I use Smo(SQL Server Management Object...
Hello everybody I wrote a backup procedure using smo. this procedure works great on my dev box, but once deployed to the hosting company's web server it throws above exception ("System.Security.SecurityException: That assembly does not allow partially trusted callers.") I did some research on the subject and it seems that hosting compa...
I am trying to setup a test database for automated tests to run against. I know ideally we should be using mock objects, but this is an old app and doing so isn't as easy as it should be. There's simply too much logic in the database. Anyhow, enough backstory. I'm getting a clean db setup that the tests can load with known data so th...
I wrote a Program that backup SQL Databases using SMO Library on .net , and everything is just going smoothly, I just have one last point, I'm changing my way to save the .bak file on the hard disk, from DeviceType.File to DeviceType.Pipe, I have used pipes before, I just didn't know how to implement it here . ...
Prepared a manged c# DLL using assemblies of SMO of version:10.0.0.0 of SQL 2008 and I called the functions in this DLL from an unmanged c++ code. This application is running successfully for SQL Server 2008 but this application is throwing exception when i run for SQL Server 2005 as follows: Description: Could not load file or assembly...
Hi I am getting below error in ssis package. Package contains Transfer SQL Server Object Task which Replace Data in destination table. I have source dimension table from which I am replacing data in destination dimension table.There is no FK constraint present on source & destination dimension table which may not allow truncate table. ...
I am able to use SMO to build a treeview similar to the Locate Backup File dialog from SSMS. I can list folders on the server, and if there are backup devices, those will be listed as well. One thing I can't seem to duplicate is how to browse for a backup file to restore that was not originally backed up from the server (so it's not in...
Hi! I want to copy an existing SQL Server 2008 database schema, but not the data, to a new database on the same server (i.e MyDB => MyDB_Test) using C#. I found some sample code at http://www.codeproject.com/KB/cs/CopyDBSchemaUsingSMO.aspx that seems to do exactly what I want but it kept throwing the following error on the ddatabase....
I want to include a parent level property in a child result set in Powershell and I can't figure out how to do it. An example of what I want to do would be to include the ServerName as the first column of a pipe to get the DatabaseName and a list of Database properties of all the databases on the server similar to: [System.Reflection.A...