See if this may help you:

http://support.microsoft.com/?kbid=919710

Check permissions of the login linked server uses to connect to SVR2. Is it mapped to databases you need to access?

Piotr

That was a good one 🙂

The idea is to get normalized numbers for months (1..12) and join on them.

the ; must be there if with is not first statement in...

Here, this works:

update rentalsTest set sortOrder = newSortOrder

from rentalsTest a inner join

(select id, userid, movieid, row_number() over(partition by(userid) order by id) newSortOrder

from rentalsTest) b on a.id = b.id

Piotr

Well, you seem to have syntax error. Shouldn't month names be in quotes?

Could you send DDL, and a few rows of sample inserts?

It would be easier. By exceeding 12 I...

What results / error messages do you get when you run the query?

What I meant, is that you join for example month 8 with month 7 in the lower...

I think that there is one issue here - you can't join straight on previous month number because Jan will not join to Dec. Try to normalize month numbers so...

Well the options are documented in help, one of them allows for locking the table during insert. This in my opinion can cause blocking. These amounts of rows are not...

Hi David,

This is CTE - new construct in SQL 2005 that allows for easy browsing of recursive structures. In this case you can't say that data are recursive, but...

Yes Jeff I agree, but there are only 12 moths in an average year. 😉

Piotr

Just a question - if you have circular dependencies and you cannot delete data, how can you insert data to these tables? Do you drop constraints as well?

Piotr

I gave it a bash, see if this can work for you:

create table monthacc(monthnum int, amount int)

go

insert monthacc(monthnum, amount)

select 1, 10

union all

select 2, 30

union all

select 3, 15

union all...

How do you know it is quicker? Did you measure both queries on your data?

What if you have ten times more data? Do you realize that your solution will not...

Now this works but this is going to perform poorly always for big tables. There is no way this query can use any indexes.

Piotr

How did you do it Sandy? What is your solution?

I believe Grant wanted to say that if these three columns are indexed on both tables, outer join approach will perform...