Posted on 2 Comments

Change database owner in SQL server
(single and multiple items)

Changing a single table owner:

Exec sp_changeobjectowner ‘dbo12323.my_table’,’dbo’

dbo12323 = make this whatever the table currently has as the db owner
dbo = the new db owner

Changing multiple items owners:

SELECT * from sysobjects where uid = user_id(‘UseNAme’)
declare @Return int
exec @Return = sp_configure ‘allow updates’, ‘1’
SELECT @Return as ‘Returned Code’
GO
reconfigure WITH OVERRIDE
GO
DECLARE @Rows int, @Error int
BEGIN TRANSACTION
update sysobjects set uid = user_id(‘dbo’) where uid = user_id(‘UseNAme’)
SELECT @Error = @@Error, @Rows = @@RowCount
SELECT @Rows as ‘#Rows’
IF @Rows > 0
BEGIN
SELECT @Rows AS ‘#Rows’
COMMIT TRANSACTION
END
else
BEGIN
SELECT @Error AS ‘Error #’
ROLLBACK TRANSACTION
END

exec sp_configure ‘allow updates’, ‘0’
reconfigure WITH OVERRIDE
go

Posted on Leave a comment

Import CSV to SQL Server Database

Quick steps to importing a csv to a sql server database. There are a couple first steps incase you have an existing database that you should make a backup copy of first or you can skip right to step number 3 which highlights the import itself.

1. Creating a backup of existing table

The code to take a table and back it up in case you do something wrong!

SELECT *
INTO tbl_mytable20070110
FROM tbl_mytable

TRUNCATE TABLE tbl_mytable;

That will take all of your existing table data and put it in a safe backup file. The truncate function then erases all of your tbl_mytable data, ready for the new data to be entered.

2. Conforming the CSV to date compliancy

Ensure the dates of your csv are in yyyy-mm-dd format to avoid any global date format issues.

3. Import the CSV data to the SQL database

  • Open SQL server and right click on the database table section.
  • Choose All Tasks > Import Data > Next
  • Choose Text from the drop down as the import file type and then browse to the location of your CSV file.
  • Click for larger example of choosing text

  • Ensure delimited option is chose and that ‘first row has column names is ticked. Hit next
  • Click for larger example of choosing file format

  • Choose Comma delimited and hit next.
  • For the destination source, make sure Microsoft OLE DB Provider for SQL Server is chosen. I used the SQL server authentication. Choose the Database you wish to import the data too and hit next.
  • Click for larger example of choosing destination

  • In the source tables and views window, select destination and choose the tbl_mytable that you will be importing the data to.
  • Click transform (the … button)
  • Ensure that all of the Source items correctly match the Destination. Change as necessary.
  • Click for larger example of column mapping

  • Un-tick Enable identity insert and hit OK.
  • Choose run immediately if not already chosen and hit next and then finish.

Now this is the way that some CSV imports will work, you may have to adjust if you do not use column names in your excel/CSV document for example.

Posted on Leave a comment

Restoring a SQL server database

I was trying to move a database from the client’s existing live site to our testing database on another server.

(I actually have three databases atm, one is the existing db used for the site (old server), the second is one that I used to test before I initially put the site live (new server), and the third is one that is for the new site about to replace the current one (new server))

After much frustration, this is how I managed to move the database over and then transfer the information I needed:

1. Create the new db (or skip this step if you are going to restore to a testing database).
2. Backup the current db from the old server and transfer to the new server
3. Through EM on the new server right click the created db>All Tasks>restore database
4. Browse to the backup of the db by selecting a new device
5. Under options choose restore over existing db and be sure to have the path and names of the mdf and ldf match what is on your server for your other databases. It will be listed with the path and names from the old server.
6. You may receive an error saying it can not import over existing files. If so then specify a directory under your current data and log directories (such as one named imported).
7. Be sure to remove the old users from the other db and add the user your cp created for the db. If objects are owned by a user on the old server you will need to change ownership over to dbo before removing the old user. If this is needed there are queries through google that will change the ownership of tables and sps through query analyizer.

How to change the database owner:
Exec sp_changeobjectowner ‘dbo12323.my_table’,’dbo’

dbo12323 = make this whatever the table currently has as the db owner
dbo = the new db owner

Now I can go ahead and move the information over to my new database.
INSERT INTO myDatabase.dbo.tbl_myTable
(field1, field2, field3)
SELECT
source1, source2, source3
FROM oldDatabase.dbo.tbl_oldTable