0

I have multiple saved queries (each one was working fine when I developed them). But when I load them in SSMS they immediately show error in every table name that's in the script. Moreover, I can't select the relevant DB from "Available databases" box (or any DB on that server, for that matter) and USE [DB_NAME] command does not work, too.

However, if I'll navigate in "Object Explorer" to any table in any DB on the Server I want and do even a simple Select Top 1000, then paste in whole query that's not working - voila - it runs fine.

Is it important for this that I'm connected to multiple SQL servers on different machines at the same time? In case it's needed - all SQL Server 2012.

Does anyone have a thought as to what's happening? It's extremely annoying to go through all the above steps each time I need a query ran. Thanks!

2
  • What do you mean by the USE DB_NAME command 'doesn't work'? What error message do you get? – Mark Sinkinson Oct 10 '16 at 13:11
  • When I'm loading the sql query from file and try to execute it SSMS pops-up with "Connect to Server" dialog box (which is succesful) and immediately after that I'm presented with an error "Invalid object name [TABLE_NAME]" message. As for the "USE DB_Name" bit - I'm in the habit of adding to every query USE statement (as seen here: msdn.microsoft.com/en-GB/library/ms188366.aspx), so that I don't get an error like the above one. Working across multiple servers with each multiple DB's with -sometimes very- similar structure I want to be sure I'm pulling correct information. – AcePL Oct 10 '16 at 13:55
0

You might need to add "GO" after your "USE [DB_NAME]" statement. Some commands need to be first in the batch. With GO, the USE DB command is executed as first batch, with another batch following it.

So:

USE [DB_NAME]
GO

CREATE PROCEDURE...
0

Well, after investigating it further in a bit of spare time it looks like this is a feature, not a bug. SSMS by default opens query windows in the context of currently selected server in Object Explorer. Syntax will be then checked against this "active" server and if query uses different one, it will be painfully red-underscored everywhere.

Solution to this is somewhat simple and we'll see how well that will work in the future: use SSMS in SQLCMD mode with :CONNECT statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.