Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 transfer data from one server to another

Author  Topic 

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 06:19:42
I need to transfer data from a database on one server to a database on another.

I need to do this programmatically as an end user will just click a button to do the transfer.

So far I've looked into the openrowset but realised that the linked server is the way to go.

I'm not a DBA and unsure what steps I need to make. I think I need to use sp_addlinkedsrvlogin but I'm out of my depth here. I know the IP address and login details of the other server. Can someone give me an example of what to do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-03 : 06:47:11
use OPENROWSET. have a look at books online for syntax or refer below

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 06:56:29
quote:
Originally posted by visakh16

use OPENROWSET. have a look at books online for syntax or refer below

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm

What makes you so sure to use the OPENROWSET? I tried using it but realised I needed to send the data rather than receive the data after using the following test command:

INSERT INTO dbo.testtable select testcolumn1, testcolumn2 FROM OPENROWSET('SQLOLEDB','111.222.333.444';'sa';'password','SELECT testcolumn1, testcolumn2 from SiteManager.TestTable')".

But I got errors with this command and thought about using the linked server instead. In the sql server management studio I can quite easily connect to the other server using the IP address and sa login details but don't know how to automate it.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-11-03 : 07:03:28
What exactly are you trying to do?Is it 1 time transfer or regular insert and update if value exists?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-03 : 07:04:26
you can use OPENROWSET for adhoc updates/inserts to remote servers. the advantage is that you dont have to setup a linked server connection.I'm not usre what error you got as you've not specified the error messages.
Alternatively, you can use Import/Export wizrad available in SQL mgmnt studio also for inserting the records to tables in other server db.
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 07:19:56
quote:
Originally posted by sodeep

What exactly are you trying to do?Is it 1 time transfer or regular insert and update if value exists?

The scenario is that we have a database on a server and the same database on other servers. We make regular changes (once a day or 2 days) and need to update the other databases as soon as a change is made. Someone suggested openrowset but the info in the link is very confusing. In the code behind the publish button on the website I'm at this point now:

string sqlString = "EXEC sp_dropserver 'S1_instance1'";
using (SqlConnection connection = ConnectionManager.GetConnection())
{
using (SqlCommand command = new SqlCommand(sqlString, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}

This has opened a linked server called 'S1_instance1'. Not sure what to do next but I know the ip address,login and password of all the other servers. Then I can use a sql command to update them then I guess I drop the linked server. But I'm a c# developer so finding this a little confusing
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 07:31:57
quote:
Originally posted by visakh16

you can use OPENROWSET for adhoc updates/inserts to remote servers. the advantage is that you dont have to setup a linked server connection.I'm not usre what error you got as you've not specified the error messages.
Alternatively, you can use Import/Export wizrad available in SQL mgmnt studio also for inserting the records to tables in other server db.


I used:
string sqlString = "INSERT INTO dbo.testtable select testcolumn1, testcolumn2 FROM OPENROWSET 'SQLOLEDB','111.222.333.444';'sa';'password','SELECT testcolumn1, testcolumn2 from SiteManager.TestTable')";

Just to confirm I can manually connect to the other database using the ip address, loginname and password in the sql management studio. I use the above sqlstring in my code behind the button on the website. At the point it executes I get this error message:

Statement(s) could not be prepared.
Invalid object name 'SiteManager.TestTable'.
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".

It looks like the problem is "Invalid object name 'SiteManager.TestTable'." but the database and table name are correct. But this retrieves the data rather than sends the data. So how would I send the data? I need to get a non technical user to click a button so cannot use the wizard you mentioned.

Also the info in the link was too complicated. I just need an example of using ip address loginname and password so I can insert local data into the other server.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-11-03 : 07:35:39
Then you need to do transactional replication rather than doing this way.
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 08:24:48
quote:
Originally posted by sodeep

Then you need to do transactional replication rather than doing this way.

Can this be automated?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2008-11-03 : 08:27:18
quote:
Originally posted by insanepaul

quote:
Originally posted by sodeep

Then you need to do transactional replication rather than doing this way.

Can this be automated?


Yes. Look at transactional replication in books online.
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 08:48:04
quote:
Originally posted by sodeep

quote:
Originally posted by insanepaul

quote:
Originally posted by sodeep

Then you need to do transactional replication rather than doing this way.

Can this be automated?

Yes. Look at transactional replication in books online.

This is getting even more complex.
I've had a look into this but my scenario isn't quite applicable. I have 1 master database and 15 subsets of this database on other servers. Whatever I update on the master will only update 1 of the 15 subsets. So it's not replicating all the subset databases. So surely I can use a linked server? But still I don't know how to use it behind the button.
Do I use this to create the linked server (but what about the password?):
string sqlString = "EXEC sp_addlinkedserver 'linkedServer', N'SQL Server'";
Then what do I do?
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 09:08:13
quote:
Originally posted by visakh16

you can use OPENROWSET for adhoc updates/inserts to remote servers. the advantage is that you dont have to setup a linked server connection.I'm not usre what error you got as you've not specified the error messages.
Alternatively, you can use Import/Export wizrad available in SQL mgmnt studio also for inserting the records to tables in other server db.

I'm getting somewhere with this but the last line is incorrect - can you help?
USE master
GO
EXEC sp_addlinkedserver
'111.222.000.111',
N'SQL Server'
GO
select * from [111.222.000.111].yourdb..remotetable
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts
Posted - 2008-11-03 : 10:46:28
quote:
Originally posted by visakh16

use OPENROWSET. have a look at books online for syntax or refer below

http://doc.ddart.net/mssql/sql70/oa-oz_5.htm

Well thanks for the non help. This forum is not very helpful. I'm getting closer but its taking ages.

EXEC sp_addlinkedserver
@server = "ServerAliasName",
@srvproduct = "SQLServer OLEDB Provider",
@provider = "SQLOLEDB",
@datasrc = "111.222.333.444",
@catalog = "SiteManager"
EXEC sp_addlinkedsrvlogin
@rmtsrvname = "ServerAliasName",
@locallogin = "sa",
@rmtuser = "sa",
@rmtpassword = "password#"

I tried the following line but not working.
select * from [ServerAliasName].SiteManager.dbo.TestTable

If I get the above correct which I'm stuck on then I need to run these next 2 lines:
EXEC sp_droplinkedsrvlogin 'ServerAliasName', 'sa'
EXEC sp_dropserver 'ServerAliasName'
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site