managing SQL server 2000 servers with 2K5 management studio

For some reason when I try to use sql 2005 management studio to manage a sql server 2000 server and attempt to use activity monitor to view processes I get an error (see below) (BTW I love being able to copy the message text). I can perform this operation from enterprise manager without issue. Is this a problem with management studio?

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Unable to execute requested command.

Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Could you please confirm that you can connect to the server otherwise and run a query (say select * from sysprocesses) and only activity monitor is not working.

What are the versions of Management Tools and SQL Server you have? What is the configuration? Are you doing it on the same machine or going over network? Was it this way from the beginning?

Boris.

|||That query worked fine. It was being accessed over a network. I was able to run querys on databases. However the server did have an issue with 1 database not being accessable. Now that the server issue has been corrected (the drive repaired). It seems to work fine from management studio. I am going to try to recreate the original server in a lab to see if this is some sort of bug in 2k5.|||

I do this all the time, but as the DBA I have the luxury of logging into the 2K box as 'sa'. I would guess the permissions issue is the root here... what account are you using to login? What roles/permissions does the account have?

More info could help us all help you.

|||

You can connect a profiler to your SQL Server 2000 instance and see what is going on when you double click on Activity Monitor in Management Studio. Since you are getting a timeout error and you had a suspect database, one of the queries did not return result in time and thus the message.

If you are able to reproduce the problem in your lab environment, please post the results here.

Thank you,

Boris.

managing SQL server 2000 servers with 2K5 management studio

For some reason when I try to use sql 2005 management studio to manage a sql server 2000 server and attempt to use activity monitor to view processes I get an error (see below) (BTW I love being able to copy the message text). I can perform this operation from enterprise manager without issue. Is this a problem with management studio?

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Unable to execute requested command.

Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Could you please confirm that you can connect to the server otherwise and run a query (say select * from sysprocesses) and only activity monitor is not working.

What are the versions of Management Tools and SQL Server you have? What is the configuration? Are you doing it on the same machine or going over network? Was it this way from the beginning?

Boris.

|||That query worked fine. It was being accessed over a network. I was able to run querys on databases. However the server did have an issue with 1 database not being accessable. Now that the server issue has been corrected (the drive repaired). It seems to work fine from management studio. I am going to try to recreate the original server in a lab to see if this is some sort of bug in 2k5.|||

I do this all the time, but as the DBA I have the luxury of logging into the 2K box as 'sa'. I would guess the permissions issue is the root here... what account are you using to login? What roles/permissions does the account have?

More info could help us all help you.

|||

You can connect a profiler to your SQL Server 2000 instance and see what is going on when you double click on Activity Monitor in Management Studio. Since you are getting a timeout error and you had a suspect database, one of the queries did not return result in time and thus the message.

If you are able to reproduce the problem in your lab environment, please post the results here.

Thank you,

Boris.

Managing Scheduled Jobs from Management Studio Express

I'm working on a backup solution for my company. Right now we have three servers in three locations running SQL Server 2000. For testing purposes, I created a scheduled job on one of these servers. It worked fine but I'd like to tweak the job some, tinker with the timing and save locations. I'm using Management Studio Express on my laptop to remotely work with these databases but I can't seem to find a decent way to work with existing jobs. Am I missing something or does SSMSE lack the "manage jobs" functionality?

SSMSE only exposes functionality that is available in SQL Express. Since Express doesn't include Agent, SSMSE doesn't include the ability to manage Agent jobs.

The SQL 2005 Feature Pack includes some DTS 2000 add-in that may meet your needs. I've not worked with them, but they may work for you.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Managing PK/FK Relationships with the Tools

Ah, why doesn't SQL Server Management Studio (SSMS) or Visual Studio's Server Explorer permit me to set PK/FK constraints on the tables? They're supported in SQL Everywhere (SQL Ev), but I don't see a way to set them up.

Why don't these tools support scripting the database tables to SQL? Is this planned?

Bill,

Obviously Microsoft will have to address the why, but in terms of the how, there is a nice third party tool that might help you out. See www.primeworks.pt.

You are correct though, the degree to which you can manage constraints on SQL Mobile databases in SQL Server 2005 Management Studio is limited unless you write the DDL yourself. VS2005 is even more limited.

Darren

|||I took a quick look at the primeworks utilities. Yes, I think that's probably the best answer. Thankfully, the third-party community can step in where MS falls short. Hopefully the "Data Dude" (I hate that name) code will help, but it's gross overkill for most SQLCE developers--it's like sending a patient to an ICU for a stubbed toe.

Managing Jobs - server roles

Hello,
What server/permission role can i assign a user so he can use SQL
agent/management and see that jobs have run, created etc without assigning
him system admin. The most restrictive yet allowing him to see the jobs
thanksHi,
A user who is not a member of the sysadmin fixed role can use sp_help_job to
view only the jobs he/she owns.
Thanks
Hari
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:4DAEB3D9-4FA6-41C0-BCF3-E0795BFC1031@.microsoft.com...
> Hello,
> What server/permission role can i assign a user so he can use SQL
> agent/management and see that jobs have run, created etc without assigning
> him system admin. The most restrictive yet allowing him to see the jobs
> thanks

Management tools SQL2005 and table ovner name dbo.tablename.

Hei all
Using the new SQL Server2005 Management tools to view and edit
tables/storedprosedures it automatically prefix the table and SP names with
the ovner name. We usually type the first letters of the tablename to find
the table name. Now we have to start with dbo.tablename every time. Is there
a setting to get rid of this ovner name in this list?
thanx all
geirFor 'normal' users in the database, you can specify a default schema:
use master
create login user1 with password = 'bla'
use adventureworks
create user user1
alter user user1 with default_schema = person
Given that proper permissions are in place, user1 can now:
select * from address
where the actually table name is person.address.
Default schema is ignored for members of 'sysadmin'. All members of the
sysadmin fixed server role have a default schema of dbo.
Hope this helps,
Hans
"Geir Holme" <geir@.multicase.no> wrote in message
news:%231jTdJMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Hei all
> Using the new SQL Server2005 Management tools to view and edit
> tables/storedprosedures it automatically prefix the table and SP names
> with the ovner name. We usually type the first letters of the tablename to
> find the table name. Now we have to start with dbo.tablename every time.
> Is there a setting to get rid of this ovner name in this list?
> thanx all
> geir
>|||Hi Hans.
Thanx for your reply.
Mabe I don't explain good enough. The short version is:
In the Management tools (Enterprice manger on sql2000) I don't want to see
the owner of the tables and SP' in the list. Can I remove this.
Why?
Because when I want to look up a table I just writes the name and it
"autosearches" the tablename. In SQL2005 I have to start with dbo.tablename
every time. I want to scip dbo. and just write the tablename to find my
table and edit it. The same for procedures.
regards
geir
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23m0$VnMgGHA.1204@.TK2MSFTNGP02.phx.gbl...
> For 'normal' users in the database, you can specify a default schema:
> use master
> create login user1 with password = 'bla'
> use adventureworks
> create user user1
> alter user user1 with default_schema = person
> Given that proper permissions are in place, user1 can now:
> select * from address
> where the actually table name is person.address.
> Default schema is ignored for members of 'sysadmin'. All members of the
> sysadmin fixed server role have a default schema of dbo.
> Hope this helps,
> Hans
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%231jTdJMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Hei all
>> Using the new SQL Server2005 Management tools to view and edit
>> tables/storedprosedures it automatically prefix the table and SP names
>> with the ovner name. We usually type the first letters of the tablename
>> to find the table name. Now we have to start with dbo.tablename every
>> time. Is there a setting to get rid of this ovner name in this list?
>> thanx all
>> geir
>

Management tools is installed but i cant find it...

Hello
I just installed Visual Studio 2005 Rc1, after that i installed Sql server
2005 september ctp.
Everything worked out fine and i installed everyprogram and all the tools
from sql server 2005 september ctp.
But when i try to find management studio i cant find it... its not under
Start Menu\Programs\Microsoft SQL Server 2005 CTP
the only thing thats under Microsoft SQL Server 2005 CTP is configuration
tools...
do i need to do some configuration to make management studio works?
On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:

>Hello
>I just installed Visual Studio 2005 Rc1, after that i installed Sql server
>2005 september ctp.
>Everything worked out fine and i installed everyprogram and all the tools
>from sql server 2005 september ctp.
>But when i try to find management studio i cant find it... its not under
>Start Menu\Programs\Microsoft SQL Server 2005 CTP
>the only thing thats under Microsoft SQL Server 2005 CTP is configuration
>tools...
>do i need to do some configuration to make management studio works?
>
One common cause for not having the SQL Server Management Studio is
that you installed SQL Server Express Edition. It doesn't have SQL
Server Management Studio.
Another possibility is that you didn't choose to install SQL Server
Management Studio during Setup.
If you don't already have it, find a copy of SQL Server 2005 Developer
Edition (or above). Click the Advanced button during setup and study
the options for installation on the Advanced screen carefully.
Then you should be in good shape.
Andrew Watt
MVP - InfoPath
|||Hello Andrew.
When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
after i finished that installation i installed sql server 2005 september ctp
(standard edition) in an new instance.
i have removed the sql 2005 september ctp and installed it again but its
still not there.
when im installing sql server 2005 september ctp im choosing all the
checkbox when i come to the point where i get the question what programs i
want to install.
when i read your answere i wonder if i should remove both vs2005 rc1 and sql
server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
and then install sql server 2005 september ctp again.
Best regards
J?rgen
"Andrew Watt [MVP - InfoPath]" wrote:

> On Fri, 7 Oct 2005 05:43:02 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
> One common cause for not having the SQL Server Management Studio is
> that you installed SQL Server Express Edition. It doesn't have SQL
> Server Management Studio.
> Another possibility is that you didn't choose to install SQL Server
> Management Studio during Setup.
> If you don't already have it, find a copy of SQL Server 2005 Developer
> Edition (or above). Click the Advanced button during setup and study
> the options for installation on the Advanced screen carefully.
> Then you should be in good shape.
> Andrew Watt
> MVP - InfoPath
>
|||Jorgen,
As a first step make sure you click the Advanced button during SQL
Server 2005 setup.
On the advanced screen make sure you notice the visual difference
between the install this component and the install this component and
all its subcomponent options.
It's easy to miss out some desired subcomponents.
If that works, then fine.
But if not ...
The recommended install order is SQL Server 2005 then Visual Studo
2005. So if the simpler approach doesn't work that looks like the way
to go.
Check after installing SQL Server that SQL Server Management Studio
has installed. It should be in Start|All Programs|Microsoft SQL Server
2005 CTP.
Andrew Watt
MVP - InfoPath
On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hello Andrew.
>When i installed the VS2005 RC1 i choosed to install sql 2005 express. but
>after i finished that installation i installed sql server 2005 september ctp
>(standard edition) in an new instance.
>i have removed the sql 2005 september ctp and installed it again but its
>still not there.
>when im installing sql server 2005 september ctp im choosing all the
>checkbox when i come to the point where i get the question what programs i
>want to install.
>when i read your answere i wonder if i should remove both vs2005 rc1 and sql
>server 2005 september ctp. then install vs 2005 rc1 withour sql 2005 express
>and then install sql server 2005 september ctp again.
>Best regards
>Jrgen
>"Andrew Watt [MVP - InfoPath]" wrote:
|||Andrew,
Thank you very much for you quick support.
The problem was solved when i choosed advanced options in the setup just as
you suggested.
I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
Thanks allot!
"Andrew Watt [MVP - InfoPath]" wrote:

> Jorgen,
> As a first step make sure you click the Advanced button during SQL
> Server 2005 setup.
> On the advanced screen make sure you notice the visual difference
> between the install this component and the install this component and
> all its subcomponent options.
> It's easy to miss out some desired subcomponents.
> If that works, then fine.
> But if not ...
> The recommended install order is SQL Server 2005 then Visual Studo
> 2005. So if the simpler approach doesn't work that looks like the way
> to go.
> Check after installing SQL Server that SQL Server Management Studio
> has installed. It should be in Start|All Programs|Microsoft SQL Server
> 2005 CTP.
> Andrew Watt
> MVP - InfoPath
> On Fri, 7 Oct 2005 14:01:33 -0700, Diffen
> <Diffen@.discussions.microsoft.com> wrote:
>
>
|||You're welcome.
Andrew Watt
MVP - InfoPath
On Sat, 8 Oct 2005 04:31:02 -0700, Diffen
<Diffen@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Andrew,
>Thank you very much for you quick support.
>The problem was solved when i choosed advanced options in the setup just as
>you suggested.
>I reinstalled sql 2005 and vs 2005 rc1 and all worked out really fine.
>Thanks allot!
>"Andrew Watt [MVP - InfoPath]" wrote:

Management Tools in SQL SERVER 2005

Hi Team,

Is there any management tools in SQL SERVER 2005? I just installed my standard edition and also developer version in different computer, but seems that both versions doesn't support management tools such as query analyzer.

Can anyone explain to me...

Thanks.

Copy both to your harddrive and rerun setup and choose the repair option and when you get to select components choose Management tools the new Enterprise Manager is called Management Studio and install it in both. Then right click at the top of Management Studio to register the Standard Edition with the Developer Edition because it is the Enterprise Edition with no deployment restriction. Hope this helps.

|||

Thanks Caddre,

I found it.

management tools for sql 2005?

what tools are available to manage sql server 2005 (std version) databases if
you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
server 2005 system for development.
Thanks!
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> what tools are available to manage sql server 2005 (std version) databases
> if
> you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
> server 2005 system for development.
> Thanks!
I don't know what third party tools have been announced yet but if you are
licensed to use SQL Server Standard Ed then you are licensed to install the
SQL Server Management Studio so I suggest you install it. Get it from
whoever manages the server or download it if you are an MSDN subscriber.
David Portas
SQL Server MVP
|||thanks David.
"David Portas" wrote:

> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> I don't know what third party tools have been announced yet but if you are
> licensed to use SQL Server Standard Ed then you are licensed to install the
> SQL Server Management Studio so I suggest you install it. Get it from
> whoever manages the server or download it if you are an MSDN subscriber.
> --
> David Portas
> SQL Server MVP
> --
>
>

management tools for sql 2005?

what tools are available to manage sql server 2005 (std version) databases if
you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
server 2005 system for development.
Thanks!"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> what tools are available to manage sql server 2005 (std version) databases
> if
> you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
> server 2005 system for development.
> Thanks!
I don't know what third party tools have been announced yet but if you are
licensed to use SQL Server Standard Ed then you are licensed to install the
SQL Server Management Studio so I suggest you install it. Get it from
whoever manages the server or download it if you are an MSDN subscriber.
--
David Portas
SQL Server MVP
--|||thanks David.
"David Portas" wrote:
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> > what tools are available to manage sql server 2005 (std version) databases
> > if
> > you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
> > server 2005 system for development.
> >
> > Thanks!
> I don't know what third party tools have been announced yet but if you are
> licensed to use SQL Server Standard Ed then you are licensed to install the
> SQL Server Management Studio so I suggest you install it. Get it from
> whoever manages the server or download it if you are an MSDN subscriber.
> --
> David Portas
> SQL Server MVP
> --
>
>

management tools for sql 2005?

what tools are available to manage sql server 2005 (std version) databases i
f
you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
server 2005 system for development.
Thanks!"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> what tools are available to manage sql server 2005 (std version) databases
> if
> you don't have a copy of SQL Server 2005? i.e. connecting to a remote sql
> server 2005 system for development.
> Thanks!
I don't know what third party tools have been announced yet but if you are
licensed to use SQL Server Standard Ed then you are licensed to install the
SQL Server Management Studio so I suggest you install it. Get it from
whoever manages the server or download it if you are an MSDN subscriber.
David Portas
SQL Server MVP
--|||thanks David.
"David Portas" wrote:

> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:8855D067-2042-4A4F-AD0B-8B918C52CB0E@.microsoft.com...
> I don't know what third party tools have been announced yet but if you are
> licensed to use SQL Server Standard Ed then you are licensed to install th
e
> SQL Server Management Studio so I suggest you install it. Get it from
> whoever manages the server or download it if you are an MSDN subscriber.
> --
> David Portas
> SQL Server MVP
> --
>
>

Management Stusio install via SMS

s there a way to only install sql management studio via SMS?

I found the article on the unattend install, but I do not want the connectivity tools, etc.

I only need the management studio portion installed, nothing elsre. Has anyone accomplished this?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478384&SiteID=1|||

I replied to that post, but it did not really help me out.

I do not know the msi

|||

IS SSMSE (SQL Server Management Studio Express) any help here?

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796

|||

The full Management Studio application is able to manage all our SQL Server products - including SQL Server, Analysis Services, Integration Services, and SQL Server Agent. Because of its scope, SSMS has a lot of dependencies under the covers that end needing most of the components you aren't interested in running in order to start.

We tailored Management Studio Express to not have all those dependencies, so the install is fairly small and doesn't bring in anything other than what's needed to work with the SQL Server relational engine. Management Studio Express is missing functionality from the full SSMS that some users use frequently, like the ability to work with SQL Server Agent and SSIS, which prevents it from working with maintenance plans, importing and exporting data using a wizard, and using a wizard to copy databases. If you don't use that functionality, SSMSE might work well for you.

You can download SSMS Express here: http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en

Hope this helps,
Steve

Management Stusio install via SMS

s there a way to only install sql management studio via SMS?

I found the article on the unattend install, but I do not want the connectivity tools, etc.

I only need the management studio portion installed, nothing elsre. Has anyone accomplished this?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478384&SiteID=1|||

I replied to that post, but it did not really help me out.

I do not know the msi

|||

IS SSMSE (SQL Server Management Studio Express) any help here?

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796

|||

The full Management Studio application is able to manage all our SQL Server products - including SQL Server, Analysis Services, Integration Services, and SQL Server Agent. Because of its scope, SSMS has a lot of dependencies under the covers that end needing most of the components you aren't interested in running in order to start.

We tailored Management Studio Express to not have all those dependencies, so the install is fairly small and doesn't bring in anything other than what's needed to work with the SQL Server relational engine. Management Studio Express is missing functionality from the full SSMS that some users use frequently, like the ability to work with SQL Server Agent and SSIS, which prevents it from working with maintenance plans, importing and exporting data using a wizard, and using a wizard to copy databases. If you don't use that functionality, SSMSE might work well for you.

You can download SSMS Express here: http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en

Hope this helps,
Steve

Management Studion Express alongside full-blown Management Studio?

Is it possible to run both Sql Server Management Studio: Express and full blown side by side?

I am developing with the full blown product but would like to test Management Studio Express on the same box.
Is this possible?

Thanks

Eric Hi !

For a full and realistic test, I would put them not on the same machine, as you can′t be sure in which way the big brother will interfer with the deployed assemblies. Just deploy it on a dedicated test machine to do your tests.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Somehow this is the kind of answer I expected...

Well, I think I'll just do that then.

Thanks

Eric

Management Studio: when opening a table, any way to sort the table ?

In SQL Server 2000 Enterprise Manager when you open a table you can open it
with a query "order by" to sort the table by a column.
In the SQL Server 2005 Management Studio when you open a table, is there any
way to sort it by any of the column ?
Thank you
> In the SQL Server 2005 Management Studio when you open a table, is there
> any way to sort it by any of the column ?
My suggestion is to write...
SELECT * FROM table_name ORDER BY column_name;
...in a Query Editor window. Open Table is a bad joke.
|||I'll second that Aaron!
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eFIRE0TrHHA.1172@.TK2MSFTNGP03.phx.gbl...
> My suggestion is to write...
> SELECT * FROM table_name ORDER BY column_name;
> ...in a Query Editor window. Open Table is a bad joke.
>
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukOnG1TrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Yes - click on the buttons in the top left hand corner to get the SQL,
> Diagram and Criteria. You can do the sorting by selecting the column in
> the diagram (check the box) then select the ascending/descending option in
> the grid for the column. Alternatively just code the SQL directly in the
> SQL pane.
> HTH,
> Paul Ibison
>

Management Studio: when opening a table, any way to sort the table ?

In SQL Server 2000 Enterprise Manager when you open a table you can open it
with a query "order by" to sort the table by a column.
In the SQL Server 2005 Management Studio when you open a table, is there any
way to sort it by any of the column ?
Thank you> In the SQL Server 2005 Management Studio when you open a table, is there
> any way to sort it by any of the column ?
My suggestion is to write...
SELECT * FROM table_name ORDER BY column_name;
...in a Query Editor window. Open Table is a bad joke.|||Yes - click on the buttons in the top left hand corner to get the SQL,
Diagram and Criteria. You can do the sorting by selecting the column in the
diagram (check the box) then select the ascending/descending option in the
grid for the column. Alternatively just code the SQL directly in the SQL
pane.
HTH,
Paul Ibison|||I'll second that Aaron!
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eFIRE0TrHHA.1172@.TK2MSFTNGP03.phx.gbl...
> My suggestion is to write...
> SELECT * FROM table_name ORDER BY column_name;
> ...in a Query Editor window. Open Table is a bad joke.
>|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukOnG1TrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Yes - click on the buttons in the top left hand corner to get the SQL,
> Diagram and Criteria. You can do the sorting by selecting the column in
> the diagram (check the box) then select the ascending/descending option in
> the grid for the column. Alternatively just code the SQL directly in the
> SQL pane.
> HTH,
> Paul Ibison
>

Management Studio: when opening a table, any way to sort the table ?

In SQL Server 2000 Enterprise Manager when you open a table you can open it
with a query "order by" to sort the table by a column.
In the SQL Server 2005 Management Studio when you open a table, is there any
way to sort it by any of the column ?
Thank you> In the SQL Server 2005 Management Studio when you open a table, is there
> any way to sort it by any of the column ?
My suggestion is to write...
SELECT * FROM table_name ORDER BY column_name;
...in a Query Editor window. Open Table is a bad joke.|||Yes - click on the buttons in the top left hand corner to get the SQL,
Diagram and Criteria. You can do the sorting by selecting the column in the
diagram (check the box) then select the ascending/descending option in the
grid for the column. Alternatively just code the SQL directly in the SQL
pane.
HTH,
Paul Ibison|||I'll second that Aaron!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eFIRE0TrHHA.1172@.TK2MSFTNGP03.phx.gbl...
>> In the SQL Server 2005 Management Studio when you open a table, is there
>> any way to sort it by any of the column ?
> My suggestion is to write...
> SELECT * FROM table_name ORDER BY column_name;
> ...in a Query Editor window. Open Table is a bad joke.
>|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukOnG1TrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Yes - click on the buttons in the top left hand corner to get the SQL,
> Diagram and Criteria. You can do the sorting by selecting the column in
> the diagram (check the box) then select the ascending/descending option in
> the grid for the column. Alternatively just code the SQL directly in the
> SQL pane.
> HTH,
> Paul Ibison
>

Management Studio: Project Hierarchy

I created a project type "SQL Server Scripts" in "Microsoft SQL Server
Management Studio", and the default structure created was:
+ Project Name
+ Connections (Type 2)
+ Queries (Type 0)
+ Miscellaneous (Type 3)
How can I create new folders? I'd like to organize my queries by subject
(Finance, Human Resources, Marketing, etc)
I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
but it seems those folders are based on "Type" and you cannot have multiple
folders of the same type.
<LogicalFolder Name="Queries" Type="0">
After frustrating attempts, I decided to use the existing hierarchy. I added
my existing stored procedures to the "Queries" folder and realized the files
were not in alphabetical order. It's impossible to find anything when list
contains more than a thousand queries.
Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
want? Please help!!!
I posted the same question a few days ago, and I also played around with the
project file to no avial. Someone replied then that this is an outstanding
issue with Microsoft. Please go to the site, vote on the issue, and add your
voice. If we don't complain, MS isn't going to do anything about it:
http://lab.msdn.microsoft.com/produc...7-be25db5080df
My feeling is that the whole VS 2005 suite, including Management Studio and
Team System, was rushed out and shipped half cooked.
Having replaced the graceful Query Analyzer and Enterprise Manager with a
lumberous piece of bloatware (VS 2005 engine), you'd expect them to
compensate the developers/DBA's by allowing them to integrate SSMS with VS
2005 solutions or at least give them the ability to organize their projects
in a meaningful way.
"YYZRic" wrote:

> I created a project type "SQL Server Scripts" in "Microsoft SQL Server
> Management Studio", and the default structure created was:
> + Project Name
> + Connections (Type 2)
> + Queries (Type 0)
> + Miscellaneous (Type 3)
> How can I create new folders? I'd like to organize my queries by subject
> (Finance, Human Resources, Marketing, etc)
> I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
> but it seems those folders are based on "Type" and you cannot have multiple
> folders of the same type.
> <LogicalFolder Name="Queries" Type="0">
> After frustrating attempts, I decided to use the existing hierarchy. I added
> my existing stored procedures to the "Queries" folder and realized the files
> were not in alphabetical order. It's impossible to find anything when list
> contains more than a thousand queries.
> Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
> creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
> want? Please help!!!
>
>
>
|||Thanks Buddie, I cast my vote!
"Homam" wrote:
[vbcol=seagreen]
> I posted the same question a few days ago, and I also played around with the
> project file to no avial. Someone replied then that this is an outstanding
> issue with Microsoft. Please go to the site, vote on the issue, and add your
> voice. If we don't complain, MS isn't going to do anything about it:
> http://lab.msdn.microsoft.com/produc...7-be25db5080df
> My feeling is that the whole VS 2005 suite, including Management Studio and
> Team System, was rushed out and shipped half cooked.
> Having replaced the graceful Query Analyzer and Enterprise Manager with a
> lumberous piece of bloatware (VS 2005 engine), you'd expect them to
> compensate the developers/DBA's by allowing them to integrate SSMS with VS
> 2005 solutions or at least give them the ability to organize their projects
> in a meaningful way.
>
>
> "YYZRic" wrote:

Management Studio: Fatal Scripting Error

I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
|||This was addressed in Bug ID FDBK44155.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:ucmK$k1VGHA.5592@.TK2MSFTNGP09.phx.gbl...
I found the problem. I was using an old script that had been set up for both Oracle and SQL Server to generate the views from metadata. The scripts were generating a CHAR(13) for carriage return without a CHAR(10) for line feed. This caused an error in Management Studio - but curiously not in Query Analyzer.
Is anybody else finding little idiosyncrasies in this that make Query Analyzer look still good? There are some things I really like in Management Studio but things like this drive me crazy. I really don't like that I can't script the DROP and PERMISSIONS when I script an object - at least I haven't found the configuration yet.
Chuck Hawkins
"Dr. Network" <charles.hawkins@.jenzabar.net> wrote in message news:%23CIX4S1VGHA.5100@.TK2MSFTNGP11.phx.gbl...
I'm trying to add more than one view to my database in a script. While the view in my scripts are different than the example below, it illustrates the problem. Essentially, when I try to add more than one view, I'm getting a parsing error on the word "GO". The script works OK in SQL 2000 Query Analyzer but fails in Management Studio. I've installed the SQL 2005 Service Pack 1 CTP on my Windows XP SP2 workstation. When I run only the top or bottom portion of the view, the respective view script parses and runs.
Here is the script:
CREATE view [dbo].[Object_View] as
select * from sysobjects where name = 'sysobjects'
GO
CREATE view [dbo].[Column_View] as
select * from table_detail where name = 'syscolumns'
GO
Here is the error:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Management Studio: connect again to load each SQL file

On Management Studio, can we choose to open any SQL files in the same Query window?

Right now, whenever we open a SQL file, we always have to "Connect to Database Engine" each time. In the new Query window, we have to select a database each time. For example, if we want to run 3 SQL scripts in the database "DB1", we have to do the following steps:
1. Click "Open File" to load the SQL file "S1.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;
2. Click "Open File" to load the SQL file "S2.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;
3. Click "Open File" to load the SQL file "S3.sql"; Connect to Database Engine; On the new Query window, select the database "DB1"; Execute;

Is there any easier way to run a few SQL files in the same database without signing on and selecting the database so many times?

Thanks,
Tony

You can use the sqlcmd.exe/osql.exe command line tool to run your files.

i.e.

sqlcmd.exe -E -S%computername% -dpubs -ifile1.sql
sqlcmd.exe -E -S%computername% -dpubs -ifile2.sql
sqlcmd.exe -E -S%computername% -dpubs -ifile3.sql|||Thanks, Greg.
As a DBA, I use "SQL Server 2000 Enterprise Manager" and "Query Analyzer" daily to administer and run SQL scripts among a lot of database servers. I do use your suggested commands to run a few fix scripts by SQL jobs or Task schedulers. But for some ad hoc SQL scripts, query analyzer gives me a nice way to load and run any SQL script file in any database.
Some users also logged a suggestion on MSDN product feedback center to request this for SQL Server 2005. According to Microsoft:

Resolved as By Design by Microsoft on 2005-05-24 at 09:19:03
Hi,
This will significantly improve with the June CTP. The New Query command is more aware of where you are in the UI to guess your connection. If the connection can't be established within 5 seconds, we will then display the connection dialog to allow you to Cancel the connection.
Thank you,
Bill Ramos

We are using the June CTP. The "New Query" command works great. But the "Open File" command is still awkward. Hope Microsoft can improve the "Open File" command also. There is a workaround though. I can use "notepad" to open all SQL script files. Then copy and paste to the same query window to run them one at a time. It is ironic by doing so in this next-generation SQL Server. But at least, this workaround is better than logging on and selecting a database for each "Open File" command.|||

I dug around a bit, and found a solution that may/may not work for you. Here's what I did:

In SQL Enterprise Manager:
- create a new Project.
- Add all your SQL Files to your project in "Queries"
- Create a new connection ( or multiple connections if you have multiple servers to connect to) in "Connections". Make sure to set a default database.

Once this is done, every time you double-click on your file in "Queries", it will open it in the server and database entry specified in "Connections".

As far as doing the same thing in "Open File", my guess is we'd need a whole new icon to do this, similar to how we have "New Query", which opens a new window based on the existing connection, and "Database Engine Query", which opens a new window and prompts for a new connection, regardless what connections you have open.

|||I logged a suggestion with Microsoft to add a "Load SQL Script" command within the current existing query window. See the details:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=8098e484-1019-4331-aaba-1d11828d887a

They will take it under consideration for the next release. At meantime, they also suggested to use "Project" to hold connections and queries. I tried it. The only thing I do not like is that we cannot change its "Associated Connection" in the query properties. That means I cannot run the same query in another server. I have to save a copy of the same query for each different connection.|||Thanks to Erland Sommarskog for his post on my suggestion, we can use "Edit -> Insert File as Text". It is perfect.|||

Not quite perfect. I'd like to be able to select several files in Windows Explorer at once, hit "Enter", and have them open with my current cnxn or prompt me once for the cnxn to be used for all files.

Even better:
1. If I'm opening several files at once, and I am not currently connected to a server or the tab/window that has focus isn't connected, prompt me for the first file's cnxn and ask if I want to apply that cnxn to the subsequent files.
2. If I am connected then presume I want to use the existing cnxn of the tab/window I have focus on (in the case I have several tabs open with different cnxns).