Changing the Double Click Default in Object Explorer

Is there a way so that when I click on a stored proc it would default to the
modify option? Or when I double click on a table it would just open a query
window and show all records?
It would be great to be able to override the current default nature, for I
rarely need to see the params of an sp, but I do need to quickly edit an sp.
Keep the + for property info, but give me editablility!
Sorry for the tirade, lots of features, but I wonder how many developers
they asked for feed back on workflow when they designed the interface.
The standard behavior for Double clicking in a treeview when there are sub
nodes is to expand the node. What you are asking can be done by simply
right clicking and choosing the appropriate option. For instance with
stored procedures you can right click and choose Modify to open a window
with the sp code ready for editing. For a table you can right click then
choose Open to see all the rows. Either way it is two clicks of the mouse
so I really don't see where it is worth complaining about. It's really worth
everyone's while to spends a few moments exploring all the features that
SSMS has to offer. I agree that some things may not be intuitive or even
ideal but it has a lot of functionality if you give it the chance.
Andrew J. Kelly SQL MVP
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:8505D340-5A31-4878-9F2E-6A3D03D29B0C@.microsoft.com...
> Is there a way so that when I click on a stored proc it would default to
> the
> modify option? Or when I double click on a table it would just open a
> query
> window and show all records?
> It would be great to be able to override the current default nature, for I
> rarely need to see the params of an sp, but I do need to quickly edit an
> sp.
> Keep the + for property info, but give me editablility!
> Sorry for the tirade, lots of features, but I wonder how many developers
> they asked for feed back on workflow when they designed the interface.
>
|||Not to get really into a back and forth, but when I double click in any type
of explorer...what I am doing is executing or actioning an item. I would
think that the parameters is only part of an sp...if I click on a folder in
Explorer, do I want to see part of the folder (parameters) or all the
contents (complete script) of a folder?
Just thoughts and yes I am more than willing to try something new.
"Andrew J. Kelly" wrote:

> The standard behavior for Double clicking in a treeview when there are sub
> nodes is to expand the node. What you are asking can be done by simply
> right clicking and choosing the appropriate option. For instance with
> stored procedures you can right click and choose Modify to open a window
> with the sp code ready for editing. For a table you can right click then
> choose Open to see all the rows. Either way it is two clicks of the mouse
> so I really don't see where it is worth complaining about. It's really worth
> everyone's while to spends a few moments exploring all the features that
> SSMS has to offer. I agree that some things may not be intuitive or even
> ideal but it has a lot of functionality if you give it the chance.
> --
> Andrew J. Kelly SQL MVP
>
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:8505D340-5A31-4878-9F2E-6A3D03D29B0C@.microsoft.com...
>
>

Changing ORDER BY

Hi All
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO

Changing Licenses

We have a situation where we need to change instances from CAL to Proc on
Enterprise Edition instances. If we purchase the Proc licenses without
actually making the conversion, will this be a problem? Or do we need to
physically convert the license? I understand it is an honor system, but I
want to make sure that we have properly reported our licenses and done them
correctly. I also was told that in order to convert the licenses we had to
reinstall the instance. Is this true?
Thanks for any assistance.I'm disagree with the comment you needed to install your stuff all over
again.
According to my opinion there is nothing you are gonna do about this license
changing thing. You just need your new license.
However, I encourage you to consult an authorized vendor to be sure and to
know about the process of this transition.
--
Ekrem Ã?nsoy
"Cheetah2399" <Cheetah2399@.discussions.microsoft.com> wrote in message
news:D6A1E9F0-1637-417C-AD8D-B21D3EF7DA5F@.microsoft.com...
> We have a situation where we need to change instances from CAL to Proc on
> Enterprise Edition instances. If we purchase the Proc licenses without
> actually making the conversion, will this be a problem? Or do we need to
> physically convert the license? I understand it is an honor system, but I
> want to make sure that we have properly reported our licenses and done
> them
> correctly. I also was told that in order to convert the licenses we had
> to
> reinstall the instance. Is this true?
> Thanks for any assistance.

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the
course of a few days (2-5) the stored proc starts returning in about 50 sec
onds. The change is sudden, not a gradual degradation. The explain plan ha
s changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, comp
ile it (ie it is now exactly the same as it was), it then starts returning i
n 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile o
f the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query
?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the cours
e of a few days (2-5) the stored proc starts returning in about 50 seconds. The cha
nge is sudden, not a gradual degradation. The explain plan has changed. If I then
add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it
is now exactly the same as it was), it then starts returning in 4-5 seconds once again.[vbco
l=seagreen]
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this que
ry?
> TIA,
> SJT[/vbcol]
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a
hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJT
Hi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT
|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add
a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
(Please reply only to the newsgroup)

Changing Explain Plan

Hi,
We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
Why is it so? How to stop SQL Server from changing the plans for this query?
TIA,
SJTHi SJT,
Sudden performance degrade might might be due to "Fragmetation". Can you
execute the DBCC SHOWCONTIG on all tables and identify the tables
which is fragmented. For all fragmented table you could run DBCC DBREINDEX
or DBCC INDEXDEFRAG to remove fragmetation.
See the details of DBCC SHOWCONTIG, DBCC DBREINDEX and DBCC INDEXDEFRAG in
boks online.
How to stop SQL Server from changing the plans for this query?
See "Execution Plan Caching and Reuse" in books online.
Thanks
Hari
MCDBA
"SJT" <SJT@.discussions.microsoft.com> wrote in message
news:E48CD7B7-28C0-4A13-99C7-5ED85FB7E5D8@.microsoft.com...
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over
the course of a few days (2-5) the stored proc starts returning in about 50
seconds. The change is sudden, not a gradual degradation. The explain plan
has changed. If I then add a hint to the main query (loop join), compile
it, take the hint back out, compile it (ie it is now exactly the same as it
was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile
of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this
query?
> TIA,
> SJT|||SJT,
instead of rewriting the query two times, see if "sp_recompile <name of
SP>" achieves the same thing.
Also, search Google for "parameter sniffing", and see if that is causing
your procedure's peformance fluctuation.
Hope this helps,
Gert-Jan
SJT wrote:
> Hi,
> We have a stored proc that generally returns in about 4-5 seconds. Over the course of a few days (2-5) the stored proc starts returning in about 50 seconds. The change is sudden, not a gradual degradation. The explain plan has changed. If I then add a hint to the main query (loop join), compile it, take the hint back out, compile it (ie it is now exactly the same as it was), it then starts returning in 4-5 seconds once again.
> We have automatic creation and update of statisics turned on. The profile of the data doesn't change that much day-to-day.
> Why is it so? How to stop SQL Server from changing the plans for this query?
> TIA,
> SJT
--
(Please reply only to the newsgroup)

Changing database within a stored proc

Is this possible in a stored procedure to receive a database name in parameter to be able to apply the code within that stored procedure to all table in the database?

I need to go trough all of the database of the server trough the database list of the master and gather data from all the table and i was wondering if there was a way for me to do a generic code for each database, the only thing that i would need to change would be the database it look into at the moment.

Would something like this work? :

*Code*

*a loop in the database list*

My_stored_proc current_database_name

My stored proc:

Use current_database_name

*Code that work with this database*

Unfortunately, that is not just a simple task.

Yes, you can create a loop through the databases from sys.databases (SQL 2005) or sysdatabases (SQL 2000).

However, you cannot simply use an 'Object' name as you would use a variable or parameter. So the statement [ USE @.Variable ] does not work. You could create an entire SQL statement as a variable, and then execute that statement using dynamic SQL -but that too has it issues.

First you have to 'build' the entire SQL statement; that's not too difficult. something like (peusdocode):

Code Snippet

DECLARE
@.dbName nvarchar(200),
@.SQLStatement nvarchar(1000)
{loop here}
SET @.dbName = name in sys.databases {selected from loop position}
SET @.SQLStatement = 'Use ' + @.dbname
EXECUTE( @.SQLStatement )

Now at this point, there is a problem, because the EXECUTE (or sp_executesql) statement operates in a separate thread, and any results are not directly accessible to the rest of your code. The next line of your code would NOT use any database other then the one you started out with because as soon as the EXECUTE statement completes, control is returned back to the original thread -which has no idea what happened when the EXECUTE statement ran.

There are methods to get around that behavior, but the most common ones require extensive knowledge of how EXECUTE() or sp_executesql operates.

I recommend that you first review this article, and then come back for assistance if you wish to continue down this path.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

The key element that I hope you get here is, yes, someone can create the functionality. But will you be able to understand and maintain the code, or will you be 'up a creek' if something changes? ... Your best option if you have to do this, is to learn and understand how to use and what is happening with dynamic SQL as it is being used. Without that understanding, you may continually be frustrated.

|||

Thanks for the text, it was very interesting.

I found out that using cursor will be much more usefull for going trough all the table of the databases.