Join tables on linked servers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Join tables on linked servers?

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 11:23:03

Good Morning! I hope everyone had a happy holiday! :o)

Here is the scenario:

server2 is linked to server1. I pull records from server1 and insert them into server2. While doing this I update the records I'm grabbing and update a field with 'W'. Another person will update the records in server2 and then I need to run a proc that will look at server2 for updated recs and then update a field in server1 to say with a 'C' to show that all process for this record are complete.

The issue is getting the info back to server1. I've had to use a cursor to run through the recs on server2 and then look up the recs in server1. It takes 24 min to update 85 recs. This is just in the testing phase. I can't imagine what will happen when production starts. At this rate it will never complete. I need a quicker way to do it. Does anyone know if I can join tables on linked servers. If so, what is the correct syntax?

select distinct mailbox from dbo.ih_accounts join ntssql1.efiling.dbo.efile on dbo.ih_accounts.mailbox = ntssql1.efiling.efile.mailbox
where dbo.ih_accounts.mailbox = @mbox and accountkey = '0'

Any additional advice would be greatly appreciated.

Thanks in advance!

Have a great day,
Teresa

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 11:47:33

quote:
select distinct mailbox from dbo.ih_accounts join ntssql1.efiling.dbo.efile on dbo.ih_accounts.mailbox = ntssql1.efiling.dbo.efile.mailbox
where dbo.ih_accounts.mailbox = @mbox and accountkey = '0'

You left out the dbo in your join syntax. Other than that, it looks proper. This assumes that you have proper security permissions and that you have solved the issues from your previous posts.

If this does not work, please provide us with the EXACT error message that you are getting to help us troubleshoot it.

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 12:06:54

quote:
specified sql server not found

I'm logged on as sa. I was having this problem when I tried it on Friday too. So I stepped back and decided not join server1 to server2 and revert back to joining server2 to server1 and running the code with a cursor. This works but it's taking too long to run. Perhaps my reasonings and decisions are incorrect? I'll do it which ever way works and works quickly. I also have to have this done within the hour. No stress there! :o)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 13:03:11

Did you fix the missing dbo statement? Can you SELECT from that linked server, without doing any joins?

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 13:07:20

I did fix the dbo statement. It says that I have exceeded the maximum number of prefixes allowed. I do not know if the select will work because of this error.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 13:11:56

Well, maybe you should just assign an alias to the tables and use that in the join statement. Something like


select distinct mailbox 
from dbo.ih_accounts t1 
join ntssql1.efiling.dbo.efile t2 on t1.mailbox = t2.mailbox
where t1.mailbox = @mbox and accountkey = '0'

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 12/26/2001 13:12:35

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 13:19:11

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Thanks! I'll try that and see if I can get it to work.

I think your new title might be "TJ's saving grace" :o)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 13:32:46

quote:

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Thanks! I'll try that and see if I can get it to work.

I think your new title might be "TJ's saving grace" :o)

Thanks, but we've got to get this thing to work first...

Good luck!

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 14:36:57

This doesn't work either. Now I'm getting ambiguous column name refering to t2.mailbox.

Any other ideas?

Thanks!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 14:44:47

Oops! I forgot to include a table reference in the SELECT part...

select distinct mailbox

Put either "t1." or "t2." (whichever is appropriate) in front of mailbox.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 15:14:15

That's not working either. It's looking like the cursor is going to be my only option.

I also tried to set up a crystal report using two servers. It doesn't like that at all!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 15:20:39

No, I don't imagine that Crystal Reports likes multiple servers. It probably only wants ONE database connection to be made for each report. I don't understand why this won't work for you. What was the indicator that the last solution didn't work? Wrong data? Or error message?

Anybody else out there that can jump in with more help?

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 16:05:43

here's the code with a cursor (74 test records in the ih_accounts table = 24minutes to run):

declare @mbox varchar (5)

declare mbox_cursor cursor for
select distinct mailbox
from dbo.ih_accounts
where dbo.ih_accounts.mailbox is not null
open mbox_cursor
fetch next from mbox_cursor
into @mbox
while @@fetch_status = 0
begin
print @mbox
update ih_accounts set accountkey = '999' where accountkey = '0'
select distinct accountkey, mailbox
from dbo.ih_accounts
where dbo.ih_accounts.mailbox = @mbox and accountkey = '999'
update ntssql1.efiling.dbo.efile set taxwrkstat = 'C' where mailbox = @mbox and taxwrkstat = 'W'
update ih_accounts set accountkey = '9' where accountkey = '999'

fetch next from mbox_cursor
into @mbox
end
close mbox_cursor
deallocate mbox_cursor

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 16:21:13

there is an acronym for what I just did, it is 'tplbtcak' (the problem lies between the chair and the keyboard)

This select code works! It was my mistake and I apologize for not catching the typo!

select distinct t1.mailbox
from dbo.ih_accounts t1
join ntssql1.efiling.dbo.efile t2 on t1.mailbox = t2.mailbox
where t1.mailbox = t2.mailbox

Now... to get it to what I need it to do! :o)

izaltsman
A custom title

1139 Posts

Posted - 2001-12-26 : 16:33:34

First of all, why are you setting accountkey to '999' and then later to '9' inside a loop?! Couldn't you just update it to '9' right away, and do it outside the loop? And you probably don't want to run a SELECT on every iteration of the loop either!

Anyhow... before we get into debugging your cursor/loop, let's see if this will do the trick:


update ef
set ef.taxwrkstat = 'C' 
FROM 
	ntssql1.efiling.dbo.efile ef 
	INNER JOIN 
	(
	select distinct mailbox
	from dbo.ih_accounts
	where dbo.ih_accounts.mailbox is not null
	) as  mb ON ef.mailbox  = mb.mailbox
	
where taxwrkstat = 'W'

izaltsman
A custom title

1139 Posts

Posted - 2001-12-26 : 16:38:04

I really should learn to type faster! ;)

Anyhow, the only comment about your SELECT statement that you finally got to work -- loose the WHERE clause (it duplicates your JOIN condition)

Cheers!

Edited by - izaltsman on 12/26/2001 16:39:11

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 16:44:08

What happened to the original WHERE clause, that included the variable @mbox?

quote:
where t1.mailbox = @mbox and accountkey = '0'

Maybe that was the source of your last error because @mbox was not declared or assigned? Anyway, looks like you're back on track now.

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 16:55:48


update ef
set ef.taxwrkstat = 'C' 
FROM 
	ntssql1.efiling.dbo.efile ef 
	INNER JOIN 
	(
	select distinct mailbox
	from dbo.ih_accounts
	where dbo.ih_accounts.mailbox is not null
	) as  mb ON ef.mailbox  = mb.mailbox
	
where taxwrkstat = 'W'

This works wonderfully! I'd really like to know where you picked this knowledge up from? I could use a lot of it!

Thank you!

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-26 : 16:58:09

[quote]
What happened to the original WHERE clause, that included the variable @mbox?

that was part of the cursor code. I really had it messed up!

Thank you for you help and your support. I truly appreciate it!

Best regards,
Teresa

izaltsman
A custom title

1139 Posts

Posted - 2001-12-26 : 17:14:38

quote:

I'd really like to know where you picked this knowledge up from? I could use a lot of it!

Well, there is this site out there called SQLTeam.com... Oh, I guess you've already heard...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-26 : 17:17:21

Thanks for saving the day, Ilya!

I was digging myself a good hole pretty quickly there, wasn't I?

A saying about treating symptoms vs. treating the problem comes to mind... Must be time for another latte...

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources