SQL Server SMO/DMO Forum

Getting "wrong" data out of a microsoft.sqlserver.management.smo.database object property (using powershell 1.0)

Thu, 26 Nov 2009 13:50:29 Z

My problem:
I do something like this:
$dbsvr = new-object microsoft.sqlserver.management.smo.server "servername"
$dbsvr.databases | ft name,size -autosize
When i run this from two different PC's i get database sizes that are all the same (5.25 mb), the size of the first database listed - which is master.
I started to try out a few things, and found out that when i run the same command from one of our servers it returns the correct sizes...

Anyone have any idea if this a SMO issue, or maybe some package software version issue...- or what?

SQL Server 2005 and 2008 SMO compatibilty issues

Thu, 09 Oct 2008 17:28:32 Z

I have SMO library references in an application which needs to be able to compile and run on both SQL Server 2005 and SQL Server 2008. How do I make sure my application is compatible with both?

I developed on my application with SQL Server 2005.When I add SMO library references to my project they show up as:

But obviously this fails to compile if the server has only SQL server 2008 installed. Is there a work around for this? I tried adding the libraries with specific version set to false, this compiles fine but fails when the application is run even with SQL Server 2005.

Also, in another solution mentioned in this forum: "If you recompile your application with SQL Server 2008 SMO then it will work with both SQL Server 2005 and 2008." -

If I understand this correctly, this means recompile with version 10 of the smo libraries? Won't this be an issue if one tries to compile on SQL server 2005 then?

Note that we are not shipping smo libraries with the application, rather the application either runs on the server which has SQL server 2005 or 2008 installed or would try to connect to another server with SQL server 2005/2008 running.

Problems with SMO 2008 Redist + Microsoft.SqlServer.BatchParserClient

Wed, 30 Jul 2008 19:52:43 Z

Hi,

I'm trying to run my application using SMO 2008. Everything works just fine if i have sql 2008 server installed.

My problem starts when I try to use SMO 2008 Redist. First it does not install Microsoft.SqlServer.BatchParserClient or Microsoft.SqlServer.BatchParser. After a little research, I found that this is a problem and that these files have to be copied manually to the folder where the application runs.

So I tried copying the files: No luck, i get a Could not load file (Below).

Then i tried to adding these files to the GAC manually: Same thing, Could not load file:

Code Snippet

Then I looked at the version of the files and they seemed to be version 10.0.144.32, so I went to my app.config and added a bind redirection from 10.0.0.0 to 10.0.144.32. now it finds the assembly but says that Manifest doesnt mach the reference:

Code Snippet

Microsoft.SqlServer.Management.Smo.FailedOperationException: ExecuteNonQuery failed for Database 'DB2008'. ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.1442.32, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.SqlServer.BatchParser, Version=10.0.1442.32, Culture=neutral, PublicKeyToken=89845dcd8080cc91' ---> System.IO.FileLoadException: Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

Then I checked all the 3 versions: Microsoft.SqlServer.SMO, Microsoft.SqlServer.BatchParser and Microsoft.SqlServer.BatchParserClient. The three are 10.0.1442.32 which confused me even more because why it would need a binding redirection to load the file?

Anyways, I'm not sure what to do to make this app work on a machine with SMO 2008 Redist only.

Did anyone had same/similar problem and know a work around for this?

Thanks,

Marcelo

SMO server.databases properties - IndexSpaceUsage (permissions)

Mon, 23 Nov 2009 20:31:04 Z

According to msdn: To get Database object properties, users can be a member of the public fixed server role.

If I impersonate a login with only the public server role, I can get properties like "Name", but I cannot get properties like "DataSpaceUsage". If I switch to a user with sysadmin role, I can get DataSpaceUsage.

That makes me think it is a permissions issue. Or, a setting on the server that hides these properties. Or it is my code.

code:
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

$sc2 = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection')
$sc2.ConnectAsUser = $true
$sc2.ConnectAsUsername = '<login>@<domain>'
$sc2.ConnectAsUserPassword = '<password>'
$sc2.ServerInstance = '<server>'

$sqlservers2 = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sc2

$sqlservers2.databases[0].name (this returns the name)
$sqlservers2.databases[0].DataSpaceUsage (this returns nothing)

-Thanks

Mike

System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database.

Thu, 19 Nov 2009 15:09:53 Z

I received the following error...

*****
System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 2739929088 additional free bytes, while only 801185792 bytes are available. (Microsoft.SqlServer.Smo)
*****

I have looked at the solution recommended on this forum http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79848 but I am still baffled as to why it says that I don't have enough disk space? I ran the RESTORE FILELISTONLY command and it told me that the "size" was 1.4 GB and I've got 60GB left. I am starting to resolve that my .BAK is corrupt. Are there any other reasons as to why I am unable to restore my backup?

Thanks for any help in advance!

powershell in sqlserver

Mon, 23 Nov 2009 09:02:12 Z

I had installed powershell in sqlserver 2005 server.And I also had enabled cmdshell in sqlserver .
While I execute the powershell script in sqlserver, Iencounter following error:
'PowerShell' is not recognized as an internal or external command,operable program or batch file.

Does anyboby has any idea about this issue?

happyMan

SMO generated Triggers and the placement of sp_settriggerorder

Mon, 23 Nov 2009 05:14:28 Z

I generated a trigger with SMO and set trigger order using this syntax:

o.DeleteOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;
o.InsertOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;
o.UpdateOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;

The resulting trigger looks like this:

CREATE TRIGGER [dbo].[test_trigger] ON [dbo].[Table_1]  AFTER  INSERT, DELETE, UPDATE AS
if  1= 1
            begin
                insert into autit_t (dt) values (getdate())
            end
EXEC sp_settriggerorder @triggername=N'[dbo].[test_trigger]', @order=N'Last', @stmttype=N'DELETE'
EXEC sp_settriggerorder @triggername=N'[dbo].[test_trigger]', @order=N'Last', @stmttype=N'INSERT'
EXEC sp_settriggerorder @triggername=N'[dbo].[test_trigger]', @order=N'Last', @stmttype=N'UPDATE'

When I use SQL Servere Management Studio to Modify or Script the trigger EXEC sp_settriggerorder are always scripted as a part of create or alter script.

My question is if this looks ok. Should there be a "GO" statement before the trigger body and those sp_settriggerorder statements.
If there should be a GO statement. Should it be generated by SMO or should I put "GO" in trigger body text?

The code to gnerate triggers looks like this:

Server dbServer = new Server("xxx\\MSSQL2008,49999");
             var selectedDatabase = dbServer.Databases["TestTest"];
            selectedDatabase.DatabaseOptions.AutoClose = true;
            Table tb = selectedDatabase.Tables["Table_1"];
            Trigger tr = new Trigger(tb, "test_trigger");
             tr.TextMode = false;
            tr.Insert = true;
            tr.Update = true;
            tr.Delete = true;
            tr.DeleteOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;
            tr.InsertOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;
            tr.UpdateOrder = Microsoft.SqlServer.Management.Smo.Agent.ActivationOrder.Last;
            tr.TextBody =
            @"if  1= 1 
            begin 
                insert into autit_t (dt) values (getdate())
            end";

            dbServer.ConnectionContext.SqlExecutionModes =
                    SqlExecutionModes.CaptureSql;
            tr.Create();
            

            var commands = dbServer.ConnectionContext.CapturedSql.Text;
            var builder = new StringBuilder();
            foreach (var s in commands)
            {
                Console.WriteLine(s);
            }

How to set DefaultConstraint of column using SMO??

Tue, 17 Nov 2009 21:30:40 Z

How to set DefaultConstraint of column using SMO??

Plz help.

SMO usage

Fri, 13 Nov 2009 04:50:39 Z

Would someone please tell me why do i use SMO and where?

I know very well how to use this but dont know in which condition i exactly use this or not use this.

Thanks

How to Take the backup for a chat browser using vb or sql

Thu, 19 Nov 2009 05:16:55 Z

I bought a chat browser and i am using that now. I would like to know how to get the backup for the chat browser, if my system crashes or that chat engine crashes. i want to have a permanent backup whatever i am chatting to another person. i dont believe with the default browser back up and want to create a new application or a program to have a backup permanently in another computer (like server). How could i do that with VB or sql or oracle(which would be the backend).

How to determine a partial vs full backup set?

Fri, 13 Nov 2009 18:14:34 Z

I've written application that uses SMO to backup / restore database. I largely mimic the interface found in SQL Server Management Studio 05.
In the restore display I display all available backup sets and I query msdb to get the info for each backup set via the following query:

"select backup_set_id,name,description,recovery_model,type,server_name,database_name,position,
backup_finish_date,backup_size,user_name,expiration_date
from msdb.dbo.backupset Where msdb.dbo.backupset.database_name = '" + dbName.Text + "'";

How do I determine if the backup was full or incremental, so I can set the Partial property??

Copy an SMO.Table object?

Tue, 10 Nov 2009 14:39:46 Z

Hi All,

Does anybody know how to create a copy of a Table objects using SMO??

I need to create a Table object that references a table in a database. Then copy that Table Object to a new object, rename the old table and the create the new table effectivly backing it up???

I can't seem to find a way of doing this :(

List all SQL servers on network

Fri, 13 Nov 2009 10:39:23 Z

Hello,

I'm trying to find a way to produce a list of all the SQL servers running on a network and have been looking at the SQLDataSourceEnumerator GetDataSources method. However there is a note saying this may not show all the SQL servers and may return a different list each time it is called. I would be grateful if anyone has anymore information on why this will not always return a complete list and may be different each time or if anyone knows if there is a way I can get a list of SQL servers that is more consistent?

Thanks,

Caroline

how to convert Microsoft.SqlServer.Management.Smo.DataType to System.Type

Thu, 05 Oct 2006 11:53:24 Z

Hi there, I would like to map correctly SQL types (Microsoft.SqlServer.Management.Smo.DataType and System.Data.SqlDbType) to CLR types.

Is it possible to not use user function like this:

System.Type GetSystemTypeBySqlType(Microsoft.SqlServer.Management.Smo.DataType typeSql)

{

switch (typeSql.Name)

...

}

It is strange that StoredProcedureParameter.DataType has property SqlDataType and does not have SystemType. It would be positive to map this type automatically, because new types appear sometimes :)

Add a New Column with SMO not null

Wed, 11 Nov 2009 13:24:40 Z

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 column is added, because it does not exist (ofcourse) and I'm not able to add the column to the table because it has data and says it has to have a default defined.

Another thing, when I atribute the DataType I thought it might also Bind the Default (because it is Binded to the UserDefinedDataType) but it also didn't.

Hope I made myself clear.

Tks in advance.

PS - I'm solving this by making Nullable=True
Update the new collumn with the defaul value
BindDefault and Nullable=False

But this not a good sollution, I wanted to make changes to the table for several columns and only make the Alter of the Table at the End.

Error Dropping Database using SMO

Wed, 28 Mar 2007 13:07:36 Z

I use the method shown below to drop a SQL Server database using SMO. On occasion, I'll get the following error message when this method is called. I'm not sure what I can do to prevent an error like this from happening. I don't think I have any control over it. The error message is this:

Drop all active database connections failed for Server 'SDTPLCHost1\SQLServer2005'. : An exception occurred while executing a Transact-SQL statement or batch.: Process ID 57 is not an active process ID.

Here is the method I wrote that uses SMO to drop a database:

public void DropDatabase(string serverName, string databaseName)

{

Server smoServer;

try

{

smoServer = new Server(serverName);

if (!smoServer.Databases.Contains(databaseName))

return;

smoServer.KillAllProcesses(databaseName);

smoServer.KillDatabase(databaseName);

}

catch (Exception ex)

{

throw new Exception(BuildExceptionMessage(ex));

}

Smo Restore.ReadFileList(server) fails

Thu, 05 Nov 2009 21:27:15 Z

I am writing a C# application to backup and restore a SQL Server database from one instance to another. I am trying to get the logical names of the data and log files of the database from the .bak file and it fails at the statement "DataTable dt = restore.ReadFileList(server);" with the error {"Incorrect syntax near the keyword 'WITH'.\r\nIncorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."}

Is there anything I have to do before I can use the restore.ReadFileList method ? The logical names of the database files have '.' and '_' in the names, "DBName_data.mdf" and "DBName_log.ldf" . Could it be the reason for this error ?

I tried restore from SQL Server Express Instance and SQL Server Full Version to SQL Server Full Version. Both of the scenarios resulted in the same error and Restore could not complete.

SQL Backup Contents

Mon, 09 Nov 2009 12:39:28 Z

Hi,

I'm just wondering about the actual contents of a full database backup within SQL 2005.

If I start a full backup at 01:00 in the evening, and the backup takes 1 hour to run, what will be the contents of the backup?

If I perform a restore from the backup, up to what point will the data be correct? Will it recover up until the start of the backup, and then require transaction logs applying to bring it up to 02:00? Or, alternatively will the backup contain all data up until 02:00 so no additional recovery is required? (If we assume that I do not need any data after the end of the backup)

Or is there a third option which I've missed?

Thanks

StoredProcedureParameter comment

Fri, 06 Nov 2009 07:05:24 Z

Hi,

I am currently creating a StoredProcedure using this kind of code:

StoredProcedure storedProcedure = new StoredProcedure(database, name);
StoredProcedureParameter storedProcedureParameter = new StoredProcedureParameter(storedProcedure, parameterName, t.VariantType);
                                    storedProcedure.Parameters.Add(storedProcedureParameter);

This will generate sql like the folowing:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Book10]
	@Product [int]
AS

I want to be able to add comments to the sql parameter, so that the sql code should look like:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Book10]
	@Product [int] /*comment for firt param */
AS

Is there a way to achive this using StoredProcedure and StoredProcedureParameter ?

Thank you,

"ME: How do I achive the imposible !" "The Wise Man: With entusiams !"

Backup and recovery of SQL Server using VB.net

Tue, 05 Dec 2006 04:55:38 Z

Hi,

I have a small application in which i'm using Sql Server as Database. my requirement is how to take the backup of the entire database or some tables from the database when there is any delete from the database. My requirement is to do from the VB.net application.Hope i delivered my question correctly. Any little help is beneficial to me.

-regards

GRK

how to log execution result of script

Tue, 20 Oct 2009 13:07:44 Z

Hello All ,

I'm using following code to execute a .sql script-

SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
server.ConnectionContext.ExecuteNonQuery(script, ExecutionTypes.ContinueOnError);

This script file has bunch of "ALTER" statement which apply primary key and foreign key constraint. I'd like to log the execution output of that script to a text file. Please advise how that can be achieved.

Regards,
Gurmit

Property LoginSecure cannot be changed or read after a connection string has been set.

Fri, 06 Mar 2009 08:36:53 Z

hi,
i am trying to copy a table from one database to another using SMO's Transfer. I am getting the following error when i execute the Transfer.TransferData()

"Property LoginSecure cannot be changed or read after a connection string has been set."
i am using .net 2.0(x64),sql 2005 std edition

please help....................

SMO Restore unable to complete restore

Tue, 03 Nov 2009 16:09:00 Z

I am trying to write an application that will backup and restore a SQL Server Express 2008 database. I am using SMO to do this task. The backup seems to work fine, but when I try and restore from it using the SMO Restore oblect the restore seems to complete, but really does not. By this I mean the completed event fires properly and no visible exceptions have been thrown. When I open the SQL Server Management Studio, the database state shows as "Restoring..." and the database itself is unavailable. If I try to bring the database online programmatically following the restore, an exception is thrown indicating that "ALTER DATABASE is not permitted while a database is in the Restoring state ALTER DATABASE statement failed". I am using Microsoft Visual Studio 2008 SP1 and Microsoft SQL Server Express 2008 for this project. The following is the code that I am using to try and do this:

        public bool RestoreSMO(string filename)
        {
            try
            {
                Microsoft.SqlServer.Management.Common.ServerConnection conn =
                    new Microsoft.SqlServer.Management.Common.ServerConnection();
                conn.ServerInstance = this.DatabaseName;
                conn.LoginSecure = false;
                conn.Login = this.UserName;
                conn.Password = this.Password;
                conn.DatabaseName = "master"; // You cannot restore a database that you are connected to


                Microsoft.SqlServer.Management.Smo.Server svr = new Server(conn);

                svr.LoginMode = ServerLoginMode.Mixed;

                Database currentDb = svr.Databases[this.Database];
                if (currentDb != null)
                {
                    svr.KillAllProcesses(this.Database);
                }
                Restore restoreDB = new Restore();
                restoreDB.Database = this.Database;
                /* Specify whether you want to restore database, files or log */
                restoreDB.Action = RestoreActionType.Database;
                restoreDB.Restart = true;

                restoreDB.Devices.AddDevice(filename, DeviceType.File);

restoreDB.ReplaceDatabase = true;

restoreDB.NoRecovery = true;

                // * Wiring up events for progress monitoring
                restoreDB.PercentComplete += new PercentCompleteEventHandler(restoreDB_PercentComplete);
                restoreDB.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(restoreDB_Complete);

                restoreDB.SqlRestore(svr);

                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }

}

Failed to connect to server

Thu, 17 Apr 2008 18:00:07 Z

Using VB.Net 2005, Standard edition and the SQL-Server Express-edition.

Question: I'd like to write a backup-system in my small app. Hereby I need to detach the database, before copying the files.

References added:

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

Source code:

Dim srv As New Server

srv.DetachDatabase("DBname", True)

Problem: Inner exception error: Failed to connect to server.

I cannot see why connecting to the server is a problem. Using tables-adapters, etc. are no problem at all. I'm stuck; could anyone point me in the right direction ?

Thanks in advance; greetings from Holland,

Alfred Zwiep

database backup programmatically

Tue, 06 May 2008 16:16:19 Z

Hello all !

I am Delveloping Visual C# 2005 windows application which is connected to SQL server 2000 database at the back end.and my task is to create a button and when i press that button it should create the database backup automatically.

Can anybody Help me

thanks

Error produced when executing database.truncatelog

Mon, 26 Oct 2009 15:34:16 Z

I recently upgraded from sql 2005 to sql 2008. I had a routine to truncate the log using the database.truncatelog method. But now when I run the following code I get the following error. Books on line does not indicate that the method should not be used on 2008 databases. Note: The database was detched from a 2005 sql instance and attached to a sql 2008 instance. Any ideas?

db.Shrink(0, ShrinkMethod.Default)
db.TruncateLog()

Error:

Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: This method or property is accessible only while working against a version earlier than SQL Server 2008.

Thanks,
John

How to create a Table with a composite primary key with Microsoft.SqlServer.Management.Smo objects?

Tue, 27 Oct 2009 19:44:06 Z

I am trying to create a table in my C# program with Microsoft.SqlServer.Management.Smo.Table. This table would have 3 columns consisting the PK. However, Microsoft.SqlServer.Management.Smo.Column doesn't seem to allow to specify that. Any ideas? Thank you.

attempting to restore to a new DB and it keeps trying to save the mdf file as the original mdf file despite using the RelocateFiles stuff

Mon, 26 Oct 2009 21:05:33 Z

This is using SQL Server 2005.

I've had several problems and this is where I've ended up.

1. Despite the claim that this will create a new DB it doesn't. I was getting errors about the new DB not having the specified logical file name (despite the new DB not existing). At the time I was specifying the logical file name as it appeared in the backup.
2. if I manually create the DB I still get the error as above unless I manually change the logical file name via sql management studio. This is meant to be an automated solution so that is not accceptable.
3. I am now giving it the logical file name of the DB that I want to restore onto (if it doesn't exist I create it), but now it's trying to save the file with the old physical file name, which is absolutely *not* what I want.

My instincts tell me I need to change the logical file name in the database I'm wanting to restore onto, but I don't know how to do this via SMO. All of the explanations I've seen are through the SQL Management Studio and that is not acceptable as this needs to be an automated solution.

I have copy/pasted the code from the msdn examples verbatim changing only the dbName and the .bak file location and it has consistently failed.

Here is the code in it's current incarnation.

var serverName = @"myServer";
            var dbName = "MIQDesignTest3";
            var backupFileName = @"D:\myDirectory\MIQDesignTest.bak";

            var server = new Server(serverName);
            var createDB = !server.Databases.Contains(dbName);

            if (createDB)
            {
                var newDB = new Database(server, dbName);
                newDB.Create();
            }

            var bakDevice = new BackupDeviceItem(backupFileName, DeviceType.File);
            var restore = new Restore();

            restore.Database = dbName;
            restore.NoRecovery = true;
            restore.Action = RestoreActionType.Database;
            restore.Devices.Add(bakDevice);
            restore.ReplaceDatabase = true





            var mdf = new RelocateFile();
            var ldf = new RelocateFile();

            mdf.LogicalFileName = dbName;
            mdf.PhysicalFileName = String.Format(@"{0}\{1}.mdf", server.Information.MasterDBPath, dbName);
            ldf.LogicalFileName = dbName + "_log";
            ldf.PhysicalFileName = String.Format(@"{0}\{1}.ldf", server.Information.MasterDBPath, dbName);

            Console.WriteLine(mdf.PhysicalFileName);
            Console.WriteLine(ldf.PhysicalFileName);

            restore.RelocateFiles.Add(mdf);
            restore.RelocateFiles.Add(ldf);
            restore.SqlRestore(server);

What do I need to do to make this work?

Database Mail SQL 2005

Tue, 13 Jun 2006 05:36:18 Z

I'm having a problem with database mail in SQL Server 2005. I have set up a default profile. My SMTP server is not a localhost instead i've fed SMTP ip address and port no. The same setting in Outlook express works fine for Incoming and outgoing mails. but in SQL 2005 i'm not able to send test emails. The following errors show up in the sql server agent log

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-06-13T10:47:27). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine).
)

Please help me out....

The Integration Services

Fri, 15 Aug 2008 15:39:36 Z

Hi,

I am developing a program that gets all object from a SQL Server 2000 database so as to copy all objects including tables, storedprocs etc to another database. However something gone wrong with this line of code:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

Transfer t = new Transfer(db);

TransferData();

It throws that error while executing:

The Integration Services component is not installed or you do not have permission to use it.

SQL Server 2000 Enterprise and SQL Server 2005 Express Editions are installed on my machine. I searched a solution for this problem but I found nothing. How can I install SSIS on my machine or anybody have any solution for this problem.

EnumAvailableSqlServers() returns nothing when there is no network connection

Thu, 29 Mar 2007 18:40:21 Z

I am having a weird issue. I am using SMO's EnumAvailableSqlServers() method to fill a drop-down box. If I am connected to a network, it shows all networked Sql Servers, including the local instance. When I am not connected to any network, it shows nothing. Even if I connect to an ad-hoc wireless network, it works correctly, showing only the local instance, but if the wireless card is removed (and no other networks are enabled) it is blank. SQL Server Browser is running. I am running both SQL Server and SQL Browser services under a local administrator account (for testing).

The problem is that these are handheld devices and sometimes they will be on a network, and other times they will be inside secure facilities in which no network devices are ever allowed. So, it needs to work in either condition. And really, it's absurd that the method cannot work in an unnetworked environment - a major slip-up by MS that is about to force us to embedded linux.

So, two questions:

1. Is there a way to fix this such that it will fallback correctly and look for local instances when no network is available?

2. If not, is there a known way to "trick" a machine into thinking a network is there so this will run correctly, such as some sort of network driver that emulates a connected network?

faile copy database on same sql server (2005) error out on user or role even with CopyAllRoles set false

Fri, 16 Oct 2009 18:23:08 Z

I am running this code as script task in ssis 2005
here is my code:
-----------
Public Sub Main()

Dim dbSourceName As String = "YSCMII"

Dim dbDestName As String = "YSCMII_COPY"

Dim conn As New ServerConnection

conn.LoginSecure =

False

conn.Login =

"user"

conn.Password =

"password"

conn.ServerInstance =

"slcsqltest1"

'Connect to the remote SQL Server.

Dim srv As Server

srv =

New Server(conn)

'The actual connection is made when a property is retrieved.

Console.WriteLine(srv.Information.Version)

'Reference the source database

Dim db As Database

db = srv.Databases(dbSourceName)

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy =

New Database(srv, dbDestName)

dbCopy.Create()

'Define a Transfer object and set the required options.

Dim xfr As Transfer

xfr =

New Transfer(db)

xfr.CopyAllTables =

True

xfr.Options.WithDependencies =

True

xfr.Options.ContinueScriptingOnError =

True

xfr.DestinationDatabase = dbCopy.Name

xfr.DestinationServer = srv.Name

xfr.DestinationLoginSecure =

True

xfr.CopyAllRoles =

False

xfr.CopySchema =

True

'Include data

xfr.CopyData =

False

'Execute the transfer

xfr.TransferData()

Dts.TaskResult = Dts.Results.Success

End Sub

------------

Error I got as following:

script Task: The script threw an exception: ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @loginame = N'ppenzance', @name_in_db = N'ppenzance'

" failed with the following error: "User or role 'ppenzance' already exists in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

Task failed: Script Task

Thanks!

SMO could not be used copy sql 2000 database?

Fri, 16 Oct 2009 21:59:51 Z

Does any one know if SMO api should or should not be used to copy sql 2000 database?

If not what I should use.

I tried use it in ssis script task, and it did not work for me. I don't know if it is supported or just something I am doing wrong.

Thanks!

NumberOfReplayThreads

Mon, 08 Sep 2008 15:22:00 Z

I’m attempting to replace a trace that ran for 50 minutes I want the replay to simulate as close as possible to the original. I’m not sure but I was thinking that increasing the number of threads would facilitate this. During the original collection there were anywhere between 700-1000 users. Is there a best way to simulate this? I’m also not sure if I’m using the best replay mode considering what I’m attempting to do.

using System;

using System.Data;

using System.Collections;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Trace;

class Program

{

static void Main(string[] args)

{

TraceReplayOptions to = new TraceReplayOptions();

TraceReplay tr = new TraceReplay();

TraceFile tf = new TraceFile();

to.NumberOfReplayThreads = 1000;

to.Mode = ReplayMode.ConnectionLevelSync;

tf.InitializeAsReader(@"C: \tracesTest\tracesTest\bin\Debug\test.trc");

tr.Source = tf;

tr.Connection = new SqlConnectionInfo("test");

tr.ReplayEvent += new ReplayEventHandler(tr_ReplayEvent);

tr.Start();

Console.WriteLine(Environment.NewLine + "Press any key to continue.");

Console.ReadKey();

}

static void tr_ReplayEvent(object sender, ReplayEventArgs args)

{

Console.WriteLine("--- Record number: " + args.RecordNumber + " ---");

for (int i = 0; i < args.CurrentRecord.FieldCount; i++)

Console.WriteLine(args.CurrentRecord.ToString());

Console.WriteLine();

}

.NET 3.0 WF : Accessing "WorkFlowInstance" from an Activity within the workflow

Mon, 08 Oct 2007 09:30:17 Z

The problem is , Im creating a Windows Sequential Workflow which just has only two "Code Activities" . Now Im trying to persist the workflow at the end of the first Activity . So that before commencing the second activity I have the WorkFlow persisted . But workflow will get persisted only on calling the 'WorkflowInstance.Unload()' method , but im not able to call this method at the activity level.

Im using "SqlServerPersistenceStorage" for persistence.

Please help me with this. !!!!!!

Failed to transfer primary key and foreign key

Thu, 15 Oct 2009 05:30:43 Z

Hello,

We're trying to transfer database from SQL 2005 to SQL 2008 with following code-

Server

srcSrv = new Server("einpxp102\\sqlexpress");

Server dstSrv = new Server("einpxp102");

// Get source database from the source server.

Database srcDb = srcSrv.Databases[srcDatabaseName];

// Create backup databse on the destination server

Database dstDb = new Database(dstSrv, dstDatabaseName);

dstDb.Create();

// This creates the new database on the 'destination' server.

// Create transfer.

Transfer t = new Transfer(srcDb);

t.CopyAllObjects =

true;

t.CopySchema =

true;

t.CopyData =

true;

// Set the destination table for the transfer object.

t.DestinationDatabase = dstDb.Name;

// Now set up the destination server

t.DestinationServer = dstSrv.Name;

// and to the transfer, this should do it.

t.TransferData();

But it is not transferring primary keys and foreign keys. I'd appreciate any help on it.

Regards,
Gurmit

Server backup

Sun, 27 Sep 2009 22:53:03 Z

I've got this code to backup my dbs that seemed to work but the last time I did the backup was a few months ago and now I cannot seem to run it. I get an exception at the statemetn I marked: NullReferenceException, although I am suer that the data and log folders are put in correctly.

Much of this code was given to me by Alok and I again entreat to him to make sense out of it. I am too preoccupied with other problems to do it myself.

        public void backupDBFull ( string dbName )
        {
            ProvideSecurity.provideSecurity ( );
            DoServer.getSrvAndConnOrSwitch ( dbName );
            BackupDevice dev = new BackupDevice ( );
            dev.Name = "SqlBackUp Default Instance_" + dbName;
            UserDefinedSqlServerData data = ( UserDefinedSqlServerData )Globals.srv.UserData;
            if ( Globals.srv.BackupDevices[ "SqlBackUp Default Instance_" + dbName ] == null ) // <== Exception here.
            {
                Globals.srv.BackupDirectory = ( ( UserDefinedSqlServerData )Globals.srv.UserData ).dataFolder;
                dev = new BackupDevice ( Globals.srv, "SqlBackUp Default Instance_" + dbName );
                dev.BackupDeviceType = BackupDeviceType.Disk;
                dev.PhysicalLocation = Globals.srv.BackupDirectory + dbName + ".bak";
                dev.Initialize ( );
                dev.Create ( );
            }
            foreach ( Database db in Globals.srv.Databases )
            {
                if ( db.Name.ToUpper ( ).IndexOf ( dbName.ToUpper ( ) ) != -1 )
                {
                    int recoverymod = 0;
                    recoverymod = ( int )db.DatabaseOptions.RecoveryModel;
                    Microsoft.SqlServer.Management.Smo.Backup bk = new Backup ( );
                    bk.Action = BackupActionType.Database;
                    bk.BackupSetDescription = "Full backup of " + dbName;
                    bk.BackupSetName = "sqlBack for " + dbName + "_1";
                    bk.Database = dbName;
                    BackupDeviceItem bdi = default ( BackupDeviceItem );
                    bdi = new BackupDeviceItem ( dev.Name, DeviceType.LogicalDevice );
                    //Add the device to the Backup object.
                    bk.Devices.Add ( bdi );
                    bk.Checksum = true;
                    string dated = DateTime.Now.Date.ToString ( "MM-dd-yy" );
                    bk.BackupSetName = "BackupFull_" + dated;
                    bk.ContinueAfterError = false;
                    bk.ExpirationDate = DateTime.Now.AddDays ( 1825 );
                    bk.MediaDescription = "Disk";
                    bk.FormatMedia = false;
                    bk.Incremental = false;
                    bk.LogTruncation = BackupTruncateLogType.Truncate;
                    bk.Information += new ServerMessageEventHandler ( back_Information );
                    bk.Complete += new ServerMessageEventHandler ( back_Complete );
                    bk.Initialize = true;
                    bk.PercentCompleteNotification = 10;
                    bk.PercentComplete += new PercentCompleteEventHandler ( bk_PercentComplete );

                    bk.SqlBackupAsync ( Globals.srv );
                    // Inform the user that the backup has been completed.
                    Console.WriteLine ( "Full Backup complete." );
                    // Remove the backup device from the Backup object.
                    bk.Devices.Remove ( bdi );
                }
            }
        }                                               // backupDBFull

        public class UserDefinedSqlServerData
        {
            public string dataFolder = @"G:\DataBackup\";
            public string logFolder = @"G:\LogBackup\";
        }

Thanks.

AlexB

How to Generate Script for a table using SMO when the table is in Transaction

Sat, 30 Jun 2007 09:11:52 Z

Hi,

I am facing a problem when i am trying to generate script for a table.The altered table is in transaction.

The following is the sample code:

Code Snippet

SqlConnection con = new SqlConnection("server=.;database=TestSMO;uid=sa;pwd=sa2005");

con.Open();

SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadUncommitted);

SqlCommand cmd = new SqlCommand("ALTER TABLE Table1 ADD Col3 varchar(20) null", con);

cmd.Transaction = trans;

cmd.ExecuteNonQuery();

// Get the bind token of the transaction related to the supplied connection

SqlCommand cmd1 = new SqlCommand("sp_getbindtoken", con, trans);

cmd1.CommandType = CommandType.StoredProcedure;

SqlParameter paramBindToken = new SqlParameter("@token", SqlDbType.VarChar, 255);

paramBindToken.Direction = ParameterDirection.Output;

cmd1.Parameters.Add(paramBindToken);

cmd1.ExecuteNonQuery();

string sBindtoken = paramBindToken.Value.ToString();

string sCmd = "";

if ((sBindtoken != null) && (sBindtoken.Length > 0))

{

// Bind to the transaction of the connection to the current

// connection

sCmd = "EXEC sp_bindsession '" + sBindtoken + "'";

//svr.ExecuteImmediate(sCmd, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sCmd.Length);

}

ServerConnection svrCon = new ServerConnection(".","sa","sa2005");

Server server = new Server(svrCon);

Database db = server.Databases["TestSMO"];

Console.WriteLine(db.GetTransactionCount(TransactionTypes.Both));

db.ExecuteNonQuery(sCmd,ExecutionTypes.Default);

Table table = db.Tables["Table1"];

ScriptingOptions options = new ScriptingOptions();

options.Default = true;

StringCollection sc = table.Script(options);

foreach (string str in sc)

{

Console.WriteLine(str);

}

trans.Commit();

con.Close();

I am able to achive the above using SQL-DMO but with the new SMO i am not able to.Can any please help me how do i generate script for a table which is altered in a transaction.

Changing field type

Tue, 13 Oct 2009 23:57:35 Z

I have a db with numerous tables each having "volume" field. Historically long ago I started assigning some of the tables Int value for the field but then changed my mind and continued adding tables with BigInt. Now when I iterate thru the tables with my C# code I get in trouble. Of course I can check for the type and take action but I want to resolve the issue once and for all. How can I do it programmatically with Smo?

Thanks.

AlexB

userdefined Function wit SMO get Error Msg 6522

Wed, 07 Oct 2009 12:58:14 Z

Hello I am trying to create a userdefined function that works with SMO. I wrote this class and function in C #. This will generate the Create Script for an available table, then drop the table and execute the generate script. I managed to create DLL to generate a assembly and a userdefined function of it in SQl Server. When i try to use this function i get an error.
I work with SqlServer 2008 and Visual C# 2005 Express.

Message 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "genScript":
System.Security.SecurityException:The assembly does not allow callers who are not fully trusted.
System.Security.SecurityException:
bei ScriptGen.Program.genScript()

I Create the Assembly with persmissions_set = safe, unsafe and external acces, but nothing work.

I were very grateful if someone could help me further

Here is my C# Class with Function

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Collections;
namespace ScriptGen
{
    public class Program
    {

        public static SqlString genScript()
        {

            Server srv = new Server("Test_server"); //SQL Server connection string Source database

            Database sourceDB = srv.Databases["Test_DB"];

            Table t = sourceDB.Tables["test_table"];

            StringCollection k = t.Script();

            t.Drop();

            foreach (String s in k)
            {
                sourceDB.ExecuteNonQuery(s);
                              
               //Console.WriteLine(s);

            }
             
            return "test";

        }
    }
}