Help with remote query

Question

Help with remote query

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22471 More actions · Answer 1

Create your tables using real table names, not tempdb names. I would create them in the current DB or another staging DB specic to this purpose.

If you must use tempdb, you will have to have permission to create tables (be a member of dbowner role)

The probability of survival is inversely proportional to the angle of arrival.

David Schlieder SSC Rookie Points: 27 More actions · Answer 2

1. Add in the specific columns in your insert statements - even though you don't need to.

2. Us AS for all returned columns - even if you don't need to

The above can simply make it clearer to read the code and probably won't help but may end shedding some light on the issue.

Check the views outside of the procedure - make sure you can open, select, etc. I am sure you have already done so but somewhere something is throwing a monkey wrench into this.

Delete the tempdb tables by name - use dbo.#xxx to create them - just in case for some bizarre reason you are picking up another temp table based on owner name..

Break it into small pieces - if the view works by itself and not in the procedure then something outside of my WAGs

I can't explain the issue but hope this gives you some food for thought,

benreeve007 SSC Veteran Points: 251 More actions · Answer 3

I have the sysadmin server role so there shouldb't be any permission issues with the temp tables. I don't really want to create proper tables for this as that seems like a bit of a waste given that I don't need them for anything else.

The views all work fine and putting "dbo." infront of all the temp table names and specificying the columns in the inserts makes no difference.

benreeve007 SSC Veteran Points: 251 More actions · Answer 4

I seemed to have got this working again now. I decided to change the collations used in the query so that the collation SQL_Latin1_General_CP1_CI_AS was used. This is the collation of the remote server - previously I was using the collation of the local server.

I'm happy that this now works, but I'd love to hear any suggestions as to why it has worked.