How to create a Linked Server to hitseft (Microsfoft Server 2005)

You can find good information here (http://msdn2.microsoft.com/en-us/library/aa259589(SQL.80).aspx)

Under, Object Explorer of (Microsfoft SQL Server Management Studio) go to:
Server Objects - Linked Servers
Right click on Linked Servers and select - New Linked Server...




































My situation is very simple, I have multiple server with 2 databases, some of the database are replicated to the other server. I have a store proc that need to be call the same way on both server, but one of my server host the replicated copy (read only) of my db so the sp need to write to the other one. That need to work the same way on both server

MYLINKSERVERNAME.myDBName.dbo.my_store_prod_name NULL, 'Tester', ' HELLO WORLD '

Server_A and Server_B both contain the link server name: MYLINKSERVERNAME, and both links, point to Server_A.

Join SQL Update

Folks,

Don't know about you but I've wondered on occasion whether or not it's possible to do a join update in Oracle and SQL Server as I've seen done in mySQL. For those who've never seen an update join, here's one to illustrate:

Old school:

update invc_item
set feed_id =
(select max(feed_id)
from calls
where invc_item_id = invc_item.invc_item_id)
where feed_id = 0
and exists
(select *
from calls
where invc_item_id = invc_item.invc_item_id
and feed_id > 0)

As you can see in the example, this SQL re-queries calls twice - once for the update and once for the where.

Update join:

update i
set i.feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0

Much simpler, far more efficient, and once you get the hang of it - less prone to error when doing these sorts of updates.

I'm not sure whether or not you have to use the table aliases as I did. Haven't tested without. This is how it was illustrated in the documentation I found.

Now for the bad news... SQL Server (the toy wannabe database) supports it, Oracle does not.

Thanks for the help Scott...

How to know what table is linking to the row you are trying to delete

You are trying to delete a row in a table but b/c of the db constraint you can't, here how to find out what table is causing the issue.

In oracle you would get an error like this:
ORA-02292: integrity constraint (TESTDB.FK_REF_114571) violated - child record found

select * from user_constraints where constraint_name = 'FK_REF_114571'

In MSSQL
select * from INFORMATION_SCHEMA.constraint_column_usage
where constraint_name = 'FK_REF_114571'

How to copy data between 2 different Oracle or MSSQL database

Well what if you did not do a backup of your data and you deleted the wrong stuff. Import your backup database on the same server. Login with a user that have access to both DB, system should work.
Oracle to Oracle:

insert into productiondb.bill_detail select * from backupdb.bill_detail
where not exists
(select *
from productiondb.bill_detail
where bill_id = backupdb.bill_detail.bill_id)

This query will copy everything that is missing back into the production DB.

MSSQL to MSSQL:
Here's how I populated nd_loc and it took only 10 or so seconds to copy ~150,000 rows! First connect to the production db and then create the link to the backup server. By running the command "sp_addlinkedserver sqldev" (where sqldev is the other server name where the backup db is. You don't have to do this if both db are on the same server)

insert into nd_loc select * from sqldev.backupdb.dbo.nd_loc

Now why can't their damned odbc driver be that fast? (Thanks Scott for the tips)

How to create a table from a select statement

Ready to make a big update, before you do make a backup of that data into a temp table with a select. This save me few times.

create table tmp_eric as 
( select * from ... where ... )

Each columns returned from the select will become the columns of the new table tmp_eric.

For MSSQL, you need to first create the table then you can use the insert

create table tmp_eric (col1, INTEGER PRIMARY KEY, col2...)

insert into tmp_eric (col1, col2,...)
select col1, col2,... from eric

You can also use:

select * into tmp_eric from eric