0

I have a view in my local db that references tables in another instance of SS (same physical server). It works perfectly. However, when I copy and paste the SQL from the view to a stored procedure, the table objects are not recognized. For e.g., rolling over a table reference in the FROM clause throws the pop-up:

Invalid object name 'AFIRALSRV06.AFI.dbo.RECEIVABLE'.

I am dbo on both databases/instances and logged in with windows authentication.

Also, the AFIRALSRV06 server is a linked server in my local database. This was set up before I got here 3 weeks ago, so could that link be creating a conflict? There are no logins configured in the linked server properties. But, as I said executing the query as a view works perfectly.

Any help would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BCanavan
  • 19
  • 3
  • Is that only when you are [hovering](https://stackoverflow.com/q/1362531/11683) over the table name in the editor, or when you are trying to save the procedure too? – GSerg Feb 18 '19 at 18:06
  • Does the stored procedure run, though? Microsoft's native Intellisense doesn't always acknowledge linked server objects. There's a good chance this is just an SSMS issue, not a real issue. – Eric Brandt Feb 18 '19 at 18:08
  • 1
    There is **no** SQL Server **2013** version - there's 2012, 2014, 2016, 2017 and soon 2019 - so which one **are you** using?? – marc_s Feb 18 '19 at 18:53
  • All I've done so far is to rt-click on Stored Procedures > Add New, and pasted my view's sql between BEGIN-END. I stopped there because I got the red squigglies under my fully qualified table names. This is the first time I've tried to reference tables in another instance and I guess you're saying to ignore the messages and try to run the sp. I'll give it a try. Thanks! – BCanavan Feb 18 '19 at 18:55
  • marc-s, maybe you can tell me what version this is. I just started working here, and didn't recognize the version in the connection name. – BCanavan Feb 18 '19 at 18:56
  • Connection name: AFIRALSRV06\DEV(SQL Server 13.0.4522.0 - ACCUFAB\bcanavan) – BCanavan Feb 18 '19 at 18:57
  • 1
    That's [SQL Server 2016](https://sqlserverbuilds.blogspot.com/). Run SELECT @@VERSION if you're curious. – Eric Brandt Feb 18 '19 at 19:11
  • And yes, for the moment, ignore the squiggles and try to proceed. I'm going to guess you'll be just fine. – Eric Brandt Feb 18 '19 at 19:12
  • Thanks. You all have a been a big help. I'll get back to you with results. – BCanavan Feb 18 '19 at 19:14
  • Incidentally, the sp was created with no errors. – BCanavan Feb 18 '19 at 19:18
  • Thanks, all. I just needed to ignore the "invalid object name" error. The sp saved with no problems, and my app is pulling the data from the sp perfectly. Thanks again! – BCanavan Feb 18 '19 at 20:21

0 Answers0