Allen White

Copying a Table Structure into a New Table using SMO

This morning I was answering a question in the MSDN Forums about using SMO to copy the structure of an existing table to create a new one. Of course, this is very easy in Transact-SQL, as evidenced in this example:

	select * into NewTable
	from ExistingTable
	where 1=2

But there are times when you're building a management application using SMO and you want to work strictly with the objects. In this case, you need to iterate through the columns in the existing table, using their property values to create new corresponding columns in the new table. This code from a VB console application shows how it can be done:

        Dim srv As Server
        Dim srvConn As ServerConnection
        srv = New Server("MyServer")
        srvConn = srv.ConnectionContext
        srvConn.LoginSecure = True

        Dim db As Database
        db = srv.Databases("AdventureWorks")

        Dim tblExisting As Table
        Dim tblNew As Table

        tblExisting = db.Tables.Item("Employee", "HumanResources")
        tblNew = New Table(db, "NewEmp", "HumanResources")
        Dim colExistColumn As ColumnCollection
        Dim clmExist As Column
        colExistColumn = tblExisting.Columns
        For Each clmExist In colExistColumn
            Dim clmNew As Column
            clmNew = New Column(tblNew, clmExist.Name)
            clmNew.DataType = clmExist.DataType
            clmNew.Nullable = clmExist.Nullable
            tblNew.Columns.Add(clmNew)
        Next
        tblNew.Create()

Allen

Microsoft Certified Trainer (MCT)

In June and July 2006 I completed the requirements for the MCITP: Database Administrator, MCITP: Database Developer, and MCTS: SQL Server 2005 certifications. Yesterday I was notified of my newest certification, Microsoft Certified Trainer (MCT).

I'm just excited about the possibilities that it opens up for me, and wanted to share that.

Allen

Problem Changing SQL Service Account on Clustered Servers

I ran into a problem attempting to change my service account on the clustered servers from an administrative account to a non-privileged account under SQL Server 2005 Enterprise Edition. When I change the login properties in Configuration Manager I kept getting the following error:

"The user already belongs to this group"

I was then prevented from making any changes to the service account. I didn't know what I was supposed to do at this point to resolve the problem, so I asked for assistance on the MSDN and the SQLServerCentral.com forums.

After not getting any responses, I started poking around ADS and with help from my friendly network administrator I was able to solve the problem. When I'd first upgraded the servers I'd specified the non-admin account as the execution account for SQL Server, but I hadn't given that account sufficient file system rights, and the server failed. I solved the problem by starting the server under the account that had local admin rights. I then figured out what rights were required (full control in the SQL data file folders, not just read/write) and granted those rights. This is where I started getting the error identified in this post.

During the SQL cluster install you're required to provide domain groups for the cluster services for SQL Server, Agent and the Fulltext services. The error was caused because the non-admin account was already in these domain groups. (You'd think that the Configuration Manager program would check to see if the user was in the group and move on if so, but this is not the case.) By deleting the non-admin account from these groups I was able to change the service account for the SQL Server services to the non-admin account.

Allen

Revoking a Database Role using SMO

A developer posed an interesting problem in the MSDN SMO forum about removing certain permissions from a database user, and were attempting to use the ObjectPermissionSet object to adjust the permissions. In fact they were attempting to revoke the db_owner database role from the user, so I posted the following code to help them solve the problem:

	Dim objDBRole As DatabaseRole
	objDBRole = objDatabase.Roles("db_owner")
	objDBRole.DropMember(TextBox2.Text)

It's sometimes difficult to determine the proper objects required to make a change, but between the Object Browser and the BOL you can usually figure things out.

By the way, if you're interested in a little bio and picture of me, Microsoft Learning was kind enough to feature me on their site.

Allen

Copying SQL 2005 Maintenance Plans between Servers

I entered a set of database maintenance plans in SQL 2005 and wanted to export them to other servers. I've built four database servers on this Windows 2003 server, all named instances. One is SQL 2000, the others are all SQL 2005. SSIS is running on this Windows server using the server name, since it doesn't support multiple instances. The maintenance plans are all functioning properly.

I wanted to export the maintenance plans to other servers and the research I've done directs me to go into Integration Services, open up the MSDB database and select the packages there, and select export. The problem I had was that when I attempted to open up MSDB I would always get a connection failure.

After posting to MSDN Forums for some help, I solved the problem myself, by just slowing down a bit. I stopped Integration Services on the Windows Server, changed the c:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml file setting to the name of the server I thought contained the maintenance plans, and then restarted Integration Services. After that I was able to successfully connect to the SSIS and export the maintenance plans by opening up the MSDB database, drilling down to the maintenance plan I wanted to export, right-clicking, then selecting Export. I then entered the server name of the destination server (database server, not SSIS) and drilled down to the Maintenance Plans folder, and clicking OK.

The maint plans are now where I wanted them, without having to rebuild them for each server.

Allen

Inside T-SQL Querying - A Must Read

In between converting my servers to run under a non-adminstrative account I've been reading Itzik Ben-Gan's book Inside Microsoft® SQL Server™ 2005: T-SQL Querying, and I've gotta tell you that the first chapter alone is worth the price of the book. Get this book - it explains the complexities of T-SQL in a way that makes things incredibly clear.

By the way, a BIG thanks to Bob Beauchemin's blog entry SQL Server service accounts and privileges which helped me resolve a couple of problems I was having with the account conversion.

Allen