Prevents execution of query on F5

Hi,

I'm having a problem since moving to version 2.0.10 BETA. Sometimes when I'm working in SQL Server Management Studio and I'm altering a view or stored proceedure and press F5 to execute it, Management Studio reports back saying

Command(s) completed successfully.

But in reality it didn't actually execute it. The view/stored procedure doesn't change.

When I disconnect the query and then reconnect and run the alter statement, the query executes and it actually does change the view/stored proc.

It's also happens for select statements. Where if I have a query

Select * from table where col1 = value

then Management Studio doesn't return any records when it should. Running the exact same query in a different window returns results. When I disconnect the bad query window from the server then connect back, it returns the rows as expected.

Somehow SqlAssistant is stealing the result set.

Thanks for the bug report. Do you know if this happens only for the F5 hot key or other keys also stop working? Can you execute the query using appropriate commands in the SSMS menu or right-click menu?

I'm not 100% sure if it's just for the F5 button, but I'd guess that the problem would exist if I selected Execute from the menu.

It's not something that happens all the time so next time I notice it happening I'll be sure to try and find out as much information about the bug as possible.

What's strange also is that with the Select result set, it shows the header columns but just no rows returned. So something is being returned, but not everything.

Thanks for this free product too!

Do you use CTrl+F9 by any chance?

If it happens again please try executing
SET FMTONLY OFF
and check if it restores the full execute capabilities.

Hi,

It finally happened again!

I was creating a stored procedure that updated a table

Code:

UPDATE FEM_CASE_COST
SET
denormalised_CASE_AGENCY_masterAccountType_ID = AgencyMasterAccountType_ID
,denormalised_CASE_AGENCY_AgencyAccountID    = AgencyAccount_ID
FROM
(
SELECT -- TOP 100
   cc.CACO_ID       CaseCost_ID
   ,a.AGCY_ID       Agency_ID
   ,a.AgencyAccount_ID       AgencyAccount_ID
   ,a.masterAccountType_ID    AgencyMasterAccountType_ID
FROM
   FEM_CASE_COST cc
INNER JOIN
   FEM_CASE c
ON
   cc.CASE_NO = c.CASE_NO
INNER JOIN
   vFEM_AGENCY_AgencyAccount a
ON
   c.AGCY_ID = a.AGCY_ID
) Agency
WHERE Agency.CaseCost_ID = FEM_CASE_COST.CACO_ID

The intellisense was working for the inside select SQL. When I did some work on the last WHERE clause SqlAssistant was picking up typing 'Agency.' and displaying the four column names of the table I just named. But when I changed one of the column names on the Agency table the intellisense stopped working all together, both for the inside select SQL as well as the outer Agency part.

Edit: I think what actually happened was, I had only three columns on the Agency table, Agency_ID, AgencyAccount_ID, AgencyMasterAccountType_ID. I didn't have the CaseCost_ID column. When writing the WHERE clause, typing in Agency. then realised that the CaseCost_ID wasn't on there and I needed it for the join. So I added the CaseCost_ID alias and THAT's when the Intellisense stopped working.

Then when I pressed F5 it said it completed execution, but the stored procedure wasn't created. I then tried as you suggested selected from the Query menu and clicked Execute. That still didn't execute it. I never at any stage pressed Ctrl+F9.

When I executed

SET FMTONLY OFF

in the window it did restore the execute capabilities, so the procedure was finally created. It also restored the intellisense!

I hope my rambling is repeatable for your developers, I myself don't want to try and repeat the process :)

Now I've got a repeatable process.

If you type out

	Code:
	SELECT * FROM ( SELECT col.colid FROM syscolumns col ) Toast WHERE Toast

Then press . (dot) after the "WHERE Toast"

The intellisense works and it displays the colid smallint.

If you then type col. like below

	Code:
	SELECT * FROM ( SELECT col.colid col FROM syscolumns col ) Toast WHERE Toast.

It displays the intellisense then choose type. Now the SQL isn't correct because it's missing out on the , (comma). You have the following

	Code:
	SELECT * FROM ( SELECT col.colid col.type FROM syscolumns col ) Toast WHERE Toast.

Then if you go down the the WHERE clause again and remove the dot at the end, then press Dot again the intellisense no longer works.

What's weird now, is once when I tried to execute a statement after doing this process I received the following

An error occurred while executing batch. Error message is: There is already an open DataReader associated with this Command which must be closed first.

But now I'm having a hard time trying to get it again.

Anyway, I hope this is NOW useful to your developers.

Thanks for the detailed input. I hope the cause of the problem is now clear and we can fix it. I will send this issue to developers.

FYI: SQL Assistant is trying to obtain the description of the subquery result-set and something breaks during that process. Because of some internal error the execution context not restored. Manual execution of SET FMTONLY OFF restores the execution context.

Good News!

The problem I was having was for version 2.0.10 BETA and 2.0.11 BETA

Now that I've installed 2.0.13 BETA I can't replicate the error anymore.