SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Prevents execution of query on F5

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Prevents execution of query on F5
Author Message
toast



Joined: 19 Apr 2007
Posts: 13
Country: Australia

Post Prevents execution of query on F5 Reply with quote
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.
Thu Apr 19, 2007 10:56 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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?
Thu Apr 19, 2007 11:11 pm View user's profile Send private message
toast



Joined: 19 Apr 2007
Posts: 13
Country: Australia

Post Reply with quote
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!
Thu Apr 19, 2007 11:20 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Fri Apr 20, 2007 12:16 am View user's profile Send private message
toast



Joined: 19 Apr 2007
Posts: 13
Country: Australia

Post Reply with quote
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 :)
Tue May 01, 2007 11:38 pm View user's profile Send private message
toast



Joined: 19 Apr 2007
Posts: 13
Country: Australia

Post Reply with quote
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.
Wed May 02, 2007 12:22 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Wed May 02, 2007 12:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
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.
Wed May 02, 2007 12:43 am View user's profile Send private message
toast



Joined: 19 Apr 2007
Posts: 13
Country: Australia

Post Reply with quote
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.
Wed May 02, 2007 12:54 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.