I don't know the internals, but I think that the only performance hit is that it has to figure out that stats on the other db...however, I don't know why in a compiled sproc couldn't get the plan from the catalog at the compilation time...unless the hit is that it won't know updated stsats...
Then again, the same thing would happen anyway in the db your in
hmmm, i seem to have hit a problem lol! I'm getting the following error:
"Msg 7202, Level 11, State 2, Procedure add_venue, Line 106
Could not find server 'DB1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
....seems to be asking for my server name (which i think is DSVR002386\SQLEXPRESS). Tried putting that in front but no joy
Are you sure the database is on the same server and in the same instance?
If it's in a different instance you are going to need a link server and you are going to have to make sure the login in the other server is there for the user that is executing the sproc...then you need to use a 4 part name...and the performance then will be definetly worse
It does indeed. But just to elbow myself to the front of the queue in case things get to this point.... if it is on another server and the instance is not named after the server (a la your example) you need to enclose your instance name in square brackets comme ca.... un petit puh: