How to fetch data from another database's table

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 2008 Forums

Transact-SQL (2008)

How to fetch data from another database's table

Author

Topic

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-07 : 04:31:02

Hello Friends

I have created a same schema od database in two different server. I want to insert some data from one server's table to others servers table.

ie:
10.0.22.243 . DB_SAL..TBL_Salary
To
10.0.22.246 . DB_SAL..TBL_Salary

Insert into 10.0.22.246.DB_SAL..TBL_Salary
Select * from 10.0.22.243.DB_SAL..TBL_Salary ;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 04:33:33

You can use linked server or import/export wizard.

No, you're never too old to Yak'n'Roll if you're too young to die.

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-07 : 04:35:54

INSERT INTO LinkedServerName.myproj.dbo.SomeTable (Column1, ...)
SELECT Column1, ... FROM mytestproj.dbo.SomeTable ...

But how to configure it. That was my trouble.

Thanks
Rohan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 04:57:23

I have googled that for you...
http://msdn.microsoft.com/en-us/library/aa560998(BTS.10).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-07 : 04:58:09

You should copy and paste that link to get it working...

No, you're never too old to Yak'n'Roll if you're too young to die.

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-07 : 05:21:27

Thanks

but i am stuck in to identify my server's 'data_source' and 'providers name'

please help.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 05:29:47

I think the below method should work. (But i don't guarantee

)

1.Save a file on desktop with .udl extension.
2.Double click on the newly saved file.
3.Select the provider, enter server name and other credentials.
4.Close the "Data Link" application (the application opened in step 2).
5. Open the file with notepad. You can find the provider details.

In case this method is not working for you then let us know.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

rohan_iiitc
Starting Member

11 Posts

Posted - 2010-05-07 : 05:40:17

thanks bohra, I was able to get it. And I also created linked

but when i fired some query i got the below querry

OLE DB provider "SQLNCLI10" for linked server "DELL1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "DELL1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

PLease help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources