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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Join tables on linked servers?
 Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  11:23:03  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 12/26/2001 :  11:47:33  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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.

Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  12:06:54  Show Profile  Reply with Quote
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)



Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  13:03:11  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Did you fix the missing dbo statement? Can you SELECT from that linked server, without doing any joins?

Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  13:07:20  Show Profile  Reply with Quote
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.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  13:11:56  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  13:19:11  Show Profile  Reply with 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)

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  13:32:46  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  14:36:57  Show Profile  Reply with Quote
This doesn't work either. Now I'm getting ambiguous column name refering to t2.mailbox.

Any other ideas?

Thanks!

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  14:44:47  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  15:14:15  Show Profile  Reply with Quote
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!



Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  15:20:39  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  16:05:43  Show Profile  Reply with Quote
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

Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  16:21:13  Show Profile  Reply with Quote
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)

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 12/26/2001 :  16:33:34  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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'





Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 12/26/2001 :  16:38:04  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  16:44:08  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  16:55:48  Show Profile  Reply with Quote

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!



Go to Top of Page

TJ
Posting Yak Master

USA
201 Posts

Posted - 12/26/2001 :  16:58:09  Show Profile  Reply with Quote
[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

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 12/26/2001 :  17:14:38  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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...


Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  17:17:21  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000