Changing Table Ownership in SQL Server

Recently I had the unfortunate event of a live server being setup and used by only 1 user which wasn’t dbo. Either the programmer didn’t know any better or… well… he was the village idiot… anyway, so… on with the code. Here is a simple T-SQL statement that will get and loop through all Stored Procedures, Tables and Views and update them to use dbo (or any other user you’d like):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE @tbl sysname
DECLARE tblcur INSENSITIVE CURSOR FOR
	SELECT name FROM sysobjects
	WHERE xtype IN ('U','V','P') AND uid = user_id('olddbuser')
OPEN tblcur
WHILE 1 = 1
	BEGIN
		FETCH tblcur INTO @tbl
			IF @@fetch_status <0
				BREAK
 
			SET @tbl = 'olddbuser.' + @tbl 
			EXEC sp_changeobjectowner @tbl, 'dbo'
	END
DEALLOCATE tblcur

Using openrowset to connect directly to another database without a linked server and execute a stored procedure

1
2

SELECT *
FROM OPENROWSET('SQLOLEDB',database_location;username;password, 'set fmtonly off exec database.dbo.stored_Procedure') AS Whatever

The above piece of code is self-explanatory. I’ve only used this in SQL 2005 though, so if it doesn’t work on SQL 2000 don’t come crying to me.

Replace database_location with the IP/Destination of the server you’d like to connect to
Replace username/password with… well… do I really have to explain this?
Replace database with the catalog/database you’d like to query
Replace stored_Procedure with the stored procedure you’d like to execute
The “As Whatever” is necessary. You could name this anything you’d like.

I’ve used the above to query a database directly for data extraction/manipulation. You could go as far as to inner join this in your normal queries if you’re not doing an update/add cursor loop on this.

Enjoy. Took me a while to figure it out

Using a Linked Server in SQL 2005 express to connect to Excel

I’m quite frustrated when it comes to the DTS options lacking in SQL 2005 Express, especially when it comes to importing excel spreadsheets into a table so I can reference it easy within a T-SQL statement to import data into my already existing relational database.

Eventually I found a little gem that seemed to work:

1
2
3
4
5
6
7
8
9

EXEC SP_ADDLINKEDSERVER 'EXCEL2',
	'Excel',
	'Microsoft.Jet.OLEDB.4.0',
	'D:\data.xls',
	NULL,
	'Excel 8.0',
	NULL
 
SELECT * FROM excel2...a$ WHERE [Whatever COLUMN] IS null

Effectively you alias the excel document as a linked sql server (in this example, excel2). You only have to do this once. Once it’s setup it works until you de-link the server alias (or you move the data source)

This helped me tremendously in getting data and querying it with T-SQL without having to import it like I’m used to with DTS. You might want to be on the look-out for language differences between the two, but a simple COLLATE DATABASE DEFAULT should do the trick in most instances when inner join’ing or comparing two different server data.