Queries on non-linked databases

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

General SQL Server Forums

New to SQL Server Programming

Queries on non-linked databases

Author

Topic

litchick10
Starting Member

1 Post

Posted - 2012-01-06 : 13:12:55

I am trying to run a query pulling data from one database then pulling data from another database and joining the information. I am not an administrator and cannot change any of the settings. Because the databases are not linked and are on different servers I cannot connect to both data sources at the same time.

I need to be able to
1 - connect to data source 1 on Server A
2 - pull data into make table
3 - disconnect data source 1
4 - connect to data source 2 on Server B
5 - join table info with data source 2 queries

I know how to make tables what I don't know how to do is write code to connect data sources and disconnect data sources. Can anyone help? Or do you have another solution?

~H.T.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 13:21:33

if this is sql server

go into SSMS and add a linked server or use sp_addlinked server

Do you have permission?

If not contact the sa's of their respective boxes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-06 : 13:26:00

You can use a Temporary Table, for the duration of your "task", so that would avoid you having to create a permanent table.

Temporary Tables have names that start with "#" - e.g. #TempTable - but in other respects are the same as any other CREATE TABLE - the details of the syntax are in the SQL DOCs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-06 : 13:27:15

Another option is to use something like SSIS to move the data that you need between servers. I tend to dis-allow linked servers because people do really dumb things with them. But, they do serve a purpose and might be applicable in this scenario.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 13:30:31

quote:
Originally posted by Lamprey
because people do really dumb things with them.

"Control, you must learn control" -- master Yoda

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-06 : 13:33:04

quote:
Originally posted by Lamprey

I tend to dis-allow linked servers because people do really dumb things with them

DBA job-protection insurance, surely? That little Profiler query you have that finds SQL code that JOINs multiple tables for different Linked Servers with some local tables for good measure ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources