How to rename a database without detaching it?

Hello,
Is there an SP for renaming a database without detaching it? Or is there a
method or feature in Enterprise Manager for doing this? How can I do this?
Thanks,
RichYou can use the ALTER DATABASE or the (older technique) sp_renamedb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>|||try this.. not sure of the syntax though :)
alter database <old_db_name> modify name <new_db_name>|||Look up sp_renamedb in Books Online.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
> a
> method or feature in Enterprise Manager for doing this? How can I do
> this?
> Thanks,
> Rich
>|||small change.. sorry abt that
alter database <old_db_name> modify name = <new_db_name>
--
"Omnibuzz" wrote:

> try this.. not sure of the syntax though :)
> alter database <old_db_name> modify name <new_db_name>
>|||Thank you all for your replies. My problem has been solved.
"Rich" wrote:

> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>

How to remove WITH FILLFACTOR = 100 when Generate Sql Script ?

Hello,

I'm using Entreprise Manager (for Sql Server 2000) to generate my
database's script. By mistake, i've changer FillFactor one time. And,
now I can't remove this data from generated sql script. How to remove
that ?

Thank's a lot.rabii (rabii.mail@.gmail.com) writes:
> I'm using Entreprise Manager (for Sql Server 2000) to generate my
> database's script. By mistake, i've changer FillFactor one time. And,
> now I can't remove this data from generated sql script. How to remove
> that ?

Can't you just run the file through a search/replace session in some
text editor? Then you could build a new database, and then script
from that one.

Even better, have all your SQL code under version control, so you
don't have to script from the databaes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for reply :),

I know this solutions :), but, the problem that I have a production
database. For each new version of my product, I have to compare my new
database schema and the old one (current production database). So the
tool "Sql Server Compare" (by comparing the databases script) show me
always that all the table are different although the difference is
simply the FILLFACTOR (in production database). Now I can change it
using Entreprise Manager but I can't remove it ?|||If that's Red-Gate's SQL Compare, I think that there is an option to
ignore fillfactors, among other things. Unfortunately, I'm at a machine
that doesn't have that tool installed, so I can't confirm that. I know
that you can definitely ignore some things, and I *think* that
FILLFACTOR is one of them.

Good luck,
-Tom.|||rabii (rabii.mail@.gmail.com) writes:
> I know this solutions :), but, the problem that I have a production
> database. For each new version of my product, I have to compare my new
> database schema and the old one (current production database). So the
> tool "Sql Server Compare" (by comparing the databases script) show me
> always that all the table are different although the difference is
> simply the FILLFACTOR (in production database). Now I can change it
> using Entreprise Manager but I can't remove it ?

So where is the fill factor wrong? In the development database or in
the production database? But whichever, can't you just adapt the
fillfactor of the development database to the production database?

Then again, fill factor is one of these things that could be different
from database to database, because different instances of the schema
has different data load. So you should probably see if your comparison
tool can ignore difference in fill factor.

Finally, I can't keep from saying that the whole thing of comparing
database schemas to build change scripts is not a very good idea. Do
you really want all sort of test junk in the dev database hit the
production server? If you have your code under version control,
you can build change scripts from the version-control system. This
gives you a much more solid base to stand on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks again for every one :)

I've said that I'm using the tool "Sql Server Compare" (it's a free
tool).

You're right when you said that it is better to use a version control
solution. But now the problem, that by mistake, I've added fillfactor =
x in the production database. In the dev database, I havn't did that.
When I compare the two databases, I notice that the comprator (sql
server compare) show me that all the table are different. When I
analyse the code, simply the fill factor is not the same. I can resolve
that by adding the same fillfactor in my dev database but I wan't to
know " if there is a way by script to remove fillfactor = x" ?|||rabii (rabii.mail@.gmail.com) writes:
> I've said that I'm using the tool "Sql Server Compare" (it's a free
> tool).
> You're right when you said that it is better to use a version control
> solution. But now the problem, that by mistake, I've added fillfactor =
> x in the production database. In the dev database, I havn't did that.
> When I compare the two databases, I notice that the comprator (sql
> server compare) show me that all the table are different. When I
> analyse the code, simply the fill factor is not the same. I can resolve
> that by adding the same fillfactor in my dev database but I wan't to
> know " if there is a way by script to remove fillfactor = x" ?

For an index you should be able to get rid of with with CREATE INDEX ...
WITH DROP_EXISTING. If it's a PRIMARY KEY constraint of a UNIQUE constraint,
I think you have to drop and recreate. Since you typically have FK
constraints to a PK, that can be kind of messy.

I played around a little, and it seems that Enterprise Manager does not
include fillfactor for primary keys, so changing your dev database may
not help.

Looks as if you either have to rebuild the production database or give
SQL Server Compare the boot...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to remove user authentication for my report server.

Hi Experts,
I had tried to remove report authentication (on a report-by-report
basis), select
a report in the Report Manager, then select the Properties tab and
select the very bottom radio button that says 'Credentials are not
required' and then 'Apply.'
I have tried above things but after doing like that report is not
rendring it is showng error that
credential is not set.
So please tell what to do... This i done for a single report only
suppoese if i want to remove authentication from my report server it
self then what to do, how i can implement that.
Or can any body tell me how to remove authentication from my report
server so that any body can run the report and it should not ask any
user name or pass word.
Regards
DineshI presume that you are using Shared Data source,then open your project and go
to shared data source. click properties and there you select "Credentials not
required", and before doing that go to the "Edit" data source and click on
Advance and click on security "UserID" give the admin user id or an Id
equivalent to admin.
Amarnath, MCTS.
"Dinesh" wrote:
> Hi Experts,
> I had tried to remove report authentication (on a report-by-report
> basis), select
> a report in the Report Manager, then select the Properties tab and
> select the very bottom radio button that says 'Credentials are not
> required' and then 'Apply.'
> I have tried above things but after doing like that report is not
> rendring it is showng error that
> credential is not set.
> So please tell what to do... This i done for a single report only
> suppoese if i want to remove authentication from my report server it
> self then what to do, how i can implement that.
> Or can any body tell me how to remove authentication from my report
> server so that any body can run the report and it should not ask any
> user name or pass word.
> Regards
> Dinesh
>

How to remove lock?

From Sql-server enterprise manager : Management-Current Activity-Locks/Process ID, I found several locks, which preventing some of application working property.
So, I want to remove locks manually.
How Can I do this?do sp_who2 and post the results...

Do mean you blocking? Locks are held until the work is done...which is what should be happening...

if it's a blocked spid, you'll probably have to kill the blocking spid...|||I tried to kill process of the locks(property, Kill Process) but, it doesn't work!

1 BACKGROUND sa . . NULL LAZY WRITER 563 0 06/15 13:44:44 1
2 BACKGROUND sa . . master SIGNAL HANDLER 31 0 06/15 13:44:44 2
3 sleeping sa . . NULL LOG WRITER 9719 0 06/15 13:44:44 3
4 BACKGROUND sa . . NULL LOCK MONITOR 141 0 06/15 13:44:44 4
5 BACKGROUND sa . . master TASK MANAGER 0 530 06/15 13:44:44 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 94 282 06/15 13:44:44 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 8
9 BACKGROUND sa . . master TASK MANAGER 0 11 06/15 13:44:44 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 12
13 BACKGROUND sa . . master TASK MANAGER 0 20 06/15 13:44:44 13
14 BACKGROUND sa . . master TASK MANAGER 0 10 06/15 13:44:44 14
15 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 15
51 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 9892 228 06/21 13:05:58 MS SQLEM 51
52 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 52
53 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 53
54 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 0 0 06/21 13:07:43 prjMapDrive 54
55 RUNNABLE HRUser IT009 . TimeClock SELECT INTO 16 3 06/21 13:19:21 SQL Query Analyzer 55
56 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 63 0 06/21 12:58:38 56
57 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 93 29 06/17 09:57:05 SQLAgent - Generic Refresher 57
58 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 601318 1 06/21 13:19:26 SQLAgent - Alert Engine 58
63 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 219 0 06/21 12:59:05 63|||Which process did you try to kill?|||I do not see any blocking activity in this output. Are you sure there is a problem? Maybe I am not understanding what the problem is?|||I want to remove TimeClock DB related locks..
Since My application has problem with transaction on TimeClock DB, it sometimes begins transaction and doesn't commit or rollback. In this case problem occurs...
Then I have to remove locks related those transaction. but, I couldn't...
I just stopped Sql server and restarted.. But, I want to remove locks without restarting and affecting other DBs.

Thanks...|||Fix the application. Anytime an application has a timeout, it should automatically roll back the transaction. Killing processes is dangerous and should only be used in rare, extreme cases.|||If the application is leaving transactions open, without committing them, you can use dbcc opentran to identify the connection (SPID) that is at fault. This command must be run from the TimeClock database. With this information, you can convince the vendor/contractor/programmer to clean up their mess.sql

how to remove index

hi I am new to sql server and I want to remove an index in a table in the northwinds practice database. Can I do it in enterprise manager or is there a script to run in query analyzer? thanks!
You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!
|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

how to remove index

hi I am new to sql server and I want to remove an index in a table in the no
rthwinds practice database. Can I do it in enterprise manager or is there a
script to run in query analyzer? thanks!You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.
To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network
|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or network

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or network

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or networksql

How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:

How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:

> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
[vbcol=seagreen]
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
>sql

How to release the memory allocated to SQL Server?

The task manager shows my SQL Server use 1.7G memory. However, it returns
130M if I stop and start the service and it will keep this amount quite a
while. Anyway to "release" the memory without stop the server?> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?
Set server option "max memory".
INF: SQL Server Memory Usage
http://www.support.microsoft.com/?id=321363
AMB
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||You can set the Max memory option so that sql server won't take more than
that memory...normally sql server release the memory if other processes
requires more memory...do u have any particular reason to reduce the sql
server memory usage?
"nick" wrote:
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||SQL Server does not release memory unless the OS asks for it. This is the
intended behavior.
http://www.support.microsoft.com/?id=321363 SQL Server 7 & 2000 memory
usage
--
Andrew J. Kelly SQL MVP
"nick" <nick@.discussions.microsoft.com> wrote in message
news:6251FBB2-9DCE-4990-A03E-54A0197B7E06@.microsoft.com...
> The task manager shows my SQL Server use 1.7G memory. However, it returns
> 130M if I stop and start the service and it will keep this amount quite a
> while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?|||I am running Analysis Service at the same machine. However, it looks the AS
has problem if SQL Server already takes a lot of memory and SQL Server
doesn't release memory when AS keep allocating more and more memory and
finally behave strange.
"harvinder" wrote:
> You can set the Max memory option so that sql server won't take more than
> that memory...normally sql server release the memory if other processes
> requires more memory...do u have any particular reason to reduce the sql
> server memory usage?
> "nick" wrote:
> > The task manager shows my SQL Server use 1.7G memory. However, it returns
> > 130M if I stop and start the service and it will keep this amount quite a
> > while. Anyway to "release" the memory without stop the server?

How to register a server automatically in Enterprise Manager

Hi
I know how to register a sql server instance in Enterprise
Manager manually (by right clicking on an SQL Server group
and choosing "New SQL Server registration" and following
the wizard.
But how to do this automatically in a script or using
osql/isql?
I know of the system stored procedures: "sp_addserver"
and "sp_addlinkedserver", but - according to my
understanding - this is another type of registration
(that is a registration of a remote/linked server so sql
statements can be used to query databases on the
remote/linked server).
What I am looking is something different (at least I
think): the ability to automatically do what the wizard
does so I can register a server in the folder structure of
the left pane i the Microsoft Management Console.
Is it some kind of MMC statement that has to be used?
yours truly
Persson
You could create your own appication using Distributed Management Objects
(DMO).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Persson" <jpn_1966@.yahoo.dk> wrote in message
news:536a01c4c667$129a26c0$a301280a@.phx.gbl...
> Hi
> I know how to register a sql server instance in Enterprise
> Manager manually (by right clicking on an SQL Server group
> and choosing "New SQL Server registration" and following
> the wizard.
> But how to do this automatically in a script or using
> osql/isql?
> I know of the system stored procedures: "sp_addserver"
> and "sp_addlinkedserver", but - according to my
> understanding - this is another type of registration
> (that is a registration of a remote/linked server so sql
> statements can be used to query databases on the
> remote/linked server).
> What I am looking is something different (at least I
> think): the ability to automatically do what the wizard
> does so I can register a server in the folder structure of
> the left pane i the Microsoft Management Console.
> Is it some kind of MMC statement that has to be used?
> yours truly
> Persson

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
Sheetu
First, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A
|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> from the databse which is unused.
>
>
|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
all
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse
it through SQl Enterprise manager, I could see there that only 12GB space is
used and rest of 15 GB space is free. I have already tried shrinking the da
tabase but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from
the databse which is unused.
Thanks in advance
SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging
all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I hav
e
> used is:
> from the databse which is unused.
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succeded. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
:) SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
:) Sheetu
"Adam Machanic" wrote:
> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> > Hi All,
> > I have got Central site SMS database whose size is 27 GB. But when I
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> > DBCC shrinkdatabse (SMS_DB, Truncateonly)
> >
> > Could anybody suggest how to get space released to the operating system
> from the databse which is unused.
> >
> > Thanks in advance
> >
> > :) Sheetu
> >
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
> > sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag
all
> of
> > the clustered indexes only
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)
> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.
light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
David G.
|||Thanks, David.
There is "use master;".
|||Which is exactly what you should remove.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".
|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
David G.

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
--
David G.|||Thanks, David.
There is "use master;".|||Which is exactly what you should remove.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
--
David G.

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
David G.|||Thanks, David.
There is "use master;".|||Which is exactly what you should remove.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
David G.

how to query database through proxy

Hi,
I want to know what is the name of the manager used in
MSSQL Server 2000 that queries a database through a proxy.
The name of that manager in Oracle is Connection manager.
"Daniela Tsigarovska" <danielat@.ramstore.bg> wrote in message
news:40be01c42b6a$09024d10$a601280a@.phx.gbl...
> I want to know what is the name of the manager used in
> MSSQL Server 2000 that queries a database through a proxy.
> The name of that manager in Oracle is Connection manager.
There is no dedicated manager in SQL Server 2000 to do this... Custom
applications typically use IIS and/or ISA Server to interface between the
public and private networks.
Steve