change MSDB owner: enterprise

Showing posts with label enterprise. Show all posts

Tuesday, March 27, 2012

changing column type

hi,
i just want the change the type of a column in a table within sql server
2000 enterprise manager. however it warns that,
'MyTable' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'.
i don't want to get those options changed when i change the type of the
column and this is not the only database on the server that we cannot stop
the server to change the configuration options(ANSI_PADDING, ANSI_NULLS) of
the sql server. it seems that we have to use sp_dboption stored procedure to
change those options for the current database. my main intention is not to
deal with query analyzer to execute sql statements, only get the job done
through enterprise manager. isn't it possible through enterprise manager to
change the database options only for the current database? i looked through
the menus may be i missed.
thanksEM goes through a much more complex CREATE/INSERT/DROP table routine =when changing data types. Have you tried changing the column via Query =Analyzer?
I have not tried it...but I am guessing that it might work better via =Query Analyzer
ALTER TABLE x ALTER COLUMN y DesiredDataTypeGoesHere
You can always try to make the change via Enterprise Manager, but make =sure that you script out the changes instead of having it apply them for =you. You should then be able to modify the code as you wish.
-- Keith
"Richard" <rich@.thetop.com> wrote in message =news:eBhBgH4nDHA.2304@.TK2MSFTNGP11.phx.gbl...
> hi,
> i just want the change the type of a column in a table within sql =server
> 2000 enterprise manager. however it warns that,
> > 'MyTable' table
> - Warning: One or more existing columns have ANSI_PADDING 'off' and =will be
> re-created with ANSI_PADDING 'on'.
> - Warning: The table was created with ANSI_NULLS 'off' and will be
> re-created with ANSI_NULLS 'on'.
> > i don't want to get those options changed when i change the type of =the
> column and this is not the only database on the server that we cannot =stop
> the server to change the configuration options(ANSI_PADDING, =ANSI_NULLS) of
> the sql server. it seems that we have to use sp_dboption stored =procedure to
> change those options for the current database. my main intention is =not to
> deal with query analyzer to execute sql statements, only get the job =done
> through enterprise manager. isn't it possible through enterprise =manager to
> change the database options only for the current database? i looked =through
> the menus may be i missed.
> thanks
> >

Monday, March 19, 2012

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?
Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:
>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changes made in Query Analizer don't show in Enterprise Manager

If I run e.g. a table-creating sql statement then the changes won't show in
Enterprise Manager no matter how many times I refresh. They only show if I
restart Enterprise Manager.
1. Is there a way to get EM to reflect the changes made in Query Analizer?
2. I couldn't seem to find a way to run arbitrary sql statements in
Enterprise Manager (There are SQL panes when operating with tables or views,
but which is fine for running a select into, for instance?). Have I missed
something or should I use the Query Analizer?Hi
1. EM normally does. Make sure you are running SQL 2000 sp3a on both the
server and on the machine you have EM installed.
2. EM is intended for a DBA to manager servers. QA is the tool to use to run
queries and other SQL statements.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Vv6eMvrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> If I run e.g. a table-creating sql statement then the changes won't show
in
> Enterprise Manager no matter how many times I refresh. They only show if I
> restart Enterprise Manager.
> 1. Is there a way to get EM to reflect the changes made in Query Analizer?
> 2. I couldn't seem to find a way to run arbitrary sql statements in
> Enterprise Manager (There are SQL panes when operating with tables or
views,
> but which is fine for running a select into, for instance?). Have I missed
> something or should I use the Query Analizer?
>
>|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>1. Is there a way to get EM to reflect the changes made in Query Analizer?
Hi Agoston,
Right-click any icon in the right pane. Most have an option "Refresh".
Note that this option will only refresh the direct sublist of that
specific icon; there is no "full refresh" option (as far as I know).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 10 Oct 2004 20:00:39 +0200, Agoston Bejo wrote:

>no matter how many times I refresh
Hi Agoston,
It seems I missed this part of your message when I sent my previous
message. Please disregard it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in
a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.
Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Saturday, February 25, 2012

Change table file group filegroup

Hi There

I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.

In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.

In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.

Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?

If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.

In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?

Thanx

ALTER TABLE MOVE TO should only be used when droppin gthe old clustered index.

You cannot move the table and retain the index with above.

I guess the best thing to use is drop and create index. You can use ONLINE option (Enterprise Edition Only) so that there is no downtime.

Jag

|||

Hi Jag

I am not following you, i am using the ALTER table command i am not dropping any clustered index?

I was asking what is happening in the background.

Also you say "You cannot move the table and retain the index with above.", a re you saying you loose your indexes with this command, i find that hard to believe?

Please clarify?

|||

Sorry about the confusion.

What I meant was that, "MOVE TO clause is only available with ALTER TABLE when you do a DROP CONSTRAINT"

It is not available with ALTER TABLE on its own.

The command will look like this:

Code Snippet

alter table t1 drop constraint PK_t1 with (move to [second]);

You cannot have the following:

Code Snippet

alter table t1 move to [second]);

Jag

|||

Hi Jag

Ok cool i get it now.

That is really strange that you have to drop a constraint, and i would have thought 2005 would have provided an easier way to move filegroups for tables data and indexes?

So you reckon the drop an re-create clustered index is till the way to go ?

|||

Yes I think so.

Drop and create the index to move filegroups.

good luck and let us know how you get on.

Jag

|||

Hi Jag

Ok cool, i just find that weird that there is no better way in 2005, so basically i will do it exactly as i did in 2000.

ALso not so easy to do when you have to move hundreds or thousands of tables and their indexes.

Basically i have to cursor through all the tables drop indexes, dynmically re-create the index defintion from sysindexes and re-create the index, not very clean, please let me know if you can think of a better way.

Thanx

|||Hello,

So isn't there any way to move a table to another filegroup without any lost (PK;FK,RelationShip).

I have more than 100 tables in my database and i created 5 filegroups. I have move them in their filegruop. Is there any shortest way to move?

Thank you very much...|||

Exactly what i was wondering the whole time, but it seems there still is no better way to move filegroups than to have to drop and rebuild the PK clustered indexes etc, i am in the same situation, database with hundreds of tables and thousands of PK - FK relationships and no easy way to move them to new filegroups.

Thanx

|||

really big problem and you can't create a table on a filegroup (without code) when you using design tools

please solve this problem !!!

Change table file group filegroup

Hi There

I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.

In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.

In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.

Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?

In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?

Thanx

ALTER TABLE MOVE TO should only be used when droppin gthe old clustered index.

You cannot move the table and retain the index with above.

I guess the best thing to use is drop and create index. You can use ONLINE option (Enterprise Edition Only) so that there is no downtime.

Jag

|||

Hi Jag

I am not following you, i am using the ALTER table command i am not dropping any clustered index?

I was asking what is happening in the background.

Also you say "You cannot move the table and retain the index with above.", a re you saying you loose your indexes with this command, i find that hard to believe?

Please clarify?

|||

Sorry about the confusion.

What I meant was that, "MOVE TO clause is only available with ALTER TABLE when you do a DROP CONSTRAINT"

It is not available with ALTER TABLE on its own.

The command will look like this:

Code Snippet

alter table t1 drop constraint PK_t1 with (move to [second]);

You cannot have the following:

Code Snippet

alter table t1 move to [second]);

Jag

|||

Hi Jag

Ok cool i get it now.

That is really strange that you have to drop a constraint, and i would have thought 2005 would have provided an easier way to move filegroups for tables data and indexes?

So you reckon the drop an re-create clustered index is till the way to go ?

|||

Yes I think so.

Drop and create the index to move filegroups.

good luck and let us know how you get on.

Jag

|||

Hi Jag

Ok cool, i just find that weird that there is no better way in 2005, so basically i will do it exactly as i did in 2000.

ALso not so easy to do when you have to move hundreds or thousands of tables and their indexes.

Thanx

|||Hello,

So isn't there any way to move a table to another filegroup without any lost (PK;FK,RelationShip).

I have more than 100 tables in my database and i created 5 filegroups. I have move them in their filegruop. Is there any shortest way to move?

Thank you very much...|||

Thanx

|||

really big problem and you can't create a table on a filegroup (without code) when you using design tools

please solve this problem !!!

Friday, February 24, 2012

Change SQL Server Connection from Enterprise Manager

How can we change the login userid by SQL Server Enterprise Manager. I
login with my Windows userid but the last time I used a SQL Server account it
still uses that account when start SQL Server Enterprise Manager.
How do I change Enterpise Manager to use my windows userid.
Thanks,
Hi Joe,
In Enterprise Manager, right click on the registered server
that you want to change the login for. Select Edit SQL
Server Registration Properties. You can change the login
information from there.
-Sue
On Wed, 26 Jan 2005 13:03:09 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>How can we change the login userid by SQL Server Enterprise Manager. I
>login with my Windows userid but the last time I used a SQL Server account it
>still uses that account when start SQL Server Enterprise Manager.
>How do I change Enterpise Manager to use my windows userid.
>Thanks,

Change Sql Server 2000 Enterprise to Standard Edition

Hi,

One of our main servers running on SQL Server 2000 Enterprise edition which has transactional replications on it which replicates to other servers running on the SQL Server 2000 Enterprise edition as well.

Due to Hardware problems the server is being migrated to a new machine but the client has installed SQL Server 2000 standard edition on the new machine.

We will be using a two processor cpu with 4GB RAM and we are also not planning about clustering. Is there any problem if i migrate the server in Standard Edition will the replications work properly between Standard and Enterprise editions.

What other complications can be there if i switch over to standard edition from enterprise edition

Thanks in ADVANCE

Jacx

In SQL 2000 there are no edition aware features for transactional replication (other than for MSDE). So you will be fine!

change SQL Server 2000 Ent to Standard

How do I change a SQL Server 2000 Enterprise Edition
installation to a SQL Server 2000 Standard Edition
installation?Hi,
You need to remove sql enterprise edition and install sql standard edition.
You could do the below steps:-
1. Backup and databases (can be used if attach fails)
2. detach all the databases from sql enterrise edition
3. Remove sql enterprise edition and install sql standard with same
directory structure
4. Apply the same service pack level
5. Attach the MDF and LDF files back in standard edition.
If attach fails you could restore the database using the backup files
performed in step-1
Thanks
Hari
MCDBA
"Pooh" <anonymous@.discussions.microsoft.com> wrote in message
news:3e4101c47fa7$c89b1850$a301280a@.phx.gbl...
> How do I change a SQL Server 2000 Enterprise Edition
> installation to a SQL Server 2000 Standard Edition
> installation?|||Thanks Hari. I was hoping for some sort of "upgrade"
functionality, but if that is the only way....
>--Original Message--
>Hi,
>You need to remove sql enterprise edition and install sql
standard edition.
>You could do the below steps:-
>1. Backup and databases (can be used if attach fails)
>2. detach all the databases from sql enterrise edition
>3. Remove sql enterprise edition and install sql standard
with same
>directory structure
>4. Apply the same service pack level
>5. Attach the MDF and LDF files back in standard edition.
>If attach fails you could restore the database using the
backup files
>performed in step-1
>
>Thanks
>Hari
>MCDBA
>"Pooh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3e4101c47fa7$c89b1850$a301280a@.phx.gbl...
>> How do I change a SQL Server 2000 Enterprise Edition
>> installation to a SQL Server 2000 Standard Edition
>> installation?
>
>.
>|||Hi Pooh,
You should also look at this...
http://support.microsoft.com/default.aspx?scid=kb;en-
us;268361
Peter
>--Original Message--
>Thanks Hari. I was hoping for some sort of "upgrade"
>functionality, but if that is the only way....
>>--Original Message--
>>Hi,
>>You need to remove sql enterprise edition and install
sql
>standard edition.
>>You could do the below steps:-
>>1. Backup and databases (can be used if attach fails)
>>2. detach all the databases from sql enterrise edition
>>3. Remove sql enterprise edition and install sql
standard
>with same
>>directory structure
>>4. Apply the same service pack level
>>5. Attach the MDF and LDF files back in standard edition.
>>If attach fails you could restore the database using the
>backup files
>>performed in step-1
>>
>>Thanks
>>Hari
>>MCDBA
>>"Pooh" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:3e4101c47fa7$c89b1850$a301280a@.phx.gbl...
>> How do I change a SQL Server 2000 Enterprise Edition
>> installation to a SQL Server 2000 Standard Edition
>> installation?
>>
>>.
>.
>|||Hi Pooh,
You have to perform the manual steps (My previous post) to downgrade from
SQL Enterprise edition to SQL Standard Edition.
But the upgrade from Standard to Enterprise edition can be done directly
using the SQL server Enterprise edition CD.
Thanks
Hari
MCDBA
"Pooh" <anonymous@.discussions.microsoft.com> wrote in message
news:436a01c47fab$18ec6fe0$a401280a@.phx.gbl...
> Thanks Hari. I was hoping for some sort of "upgrade"
> functionality, but if that is the only way....
> >--Original Message--
> >Hi,
> >
> >You need to remove sql enterprise edition and install sql
> standard edition.
> >
> >You could do the below steps:-
> >
> >1. Backup and databases (can be used if attach fails)
> >2. detach all the databases from sql enterrise edition
> >3. Remove sql enterprise edition and install sql standard
> with same
> >directory structure
> >4. Apply the same service pack level
> >5. Attach the MDF and LDF files back in standard edition.
> >
> >If attach fails you could restore the database using the
> backup files
> >performed in step-1
> >
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >"Pooh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:3e4101c47fa7$c89b1850$a301280a@.phx.gbl...
> >> How do I change a SQL Server 2000 Enterprise Edition
> >> installation to a SQL Server 2000 Standard Edition
> >> installation?
> >
> >
> >.
> >

change SQL Server 2000 Ent to Standard

Change SQL Instance name to default(local) instrance

I found some information, but, it didn't work.
Now, i open the enterprise manager, and My SQL Server name is
DENNIS\DENNISDB(windows NT), but i want to change this name to default
name(local), when i installed sql, i changed the instance name, i want
default name.
I used this method.
EXEC sp_dropserver 'DENNISDB'
go
EXEC sp_addserver 'DENNIS', 'local'
go
and i restart SQL Server Service and,
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
result : DENNIS\DENNIDB
select @.@.SERVERNAME
result : DENNIS
But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I want
"(local)" instance
Help me plase..Hi
As far as I know you cannot change/switch a default instance with a named
one. Re-install SQL Server only.
"DennisLee" <ddongdog@.haja.or.kr.korea> wrote in message
news:ONofqOsMGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I found some information, but, it didn't work.
> Now, i open the enterprise manager, and My SQL Server name is
> DENNIS\DENNISDB(windows NT), but i want to change this name to default
> name(local), when i installed sql, i changed the instance name, i want
> default name.
> I used this method.
>
> EXEC sp_dropserver 'DENNISDB'
> go
> EXEC sp_addserver 'DENNIS', 'local'
> go
> and i restart SQL Server Service and,
> SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
> result : DENNIS\DENNIDB
> select @.@.SERVERNAME
> result : DENNIS
> But, stil Instance name On My Enterprize Manager is DENNIS\DENNISDB, I
> want
> "(local)" instance
> Help me plase..
>
>

Sunday, February 19, 2012

Change server name in MSSQL cluster

Hi all, I've got PDC and BDC Windows NT 4.0 Servers (Enterprise edition) installed in the MS cluster. The clustered application is MS SQL server 7.0. Now I need to change names of both nodes - PDC and BDC Windows NT 4.0 Servers. Has anyone any experience with such operation ? Is it too risky ? Thanks a lot for any hint MichalChanging the server names will break your SQL servers.

I did this on a standalone (changed the server name) and SQL stopped working. The fix according to Microsoft was to reinstall SQL on the servers. When the installation starts it will see that all the components are already installed and then see the name resolution conflict and fix it.

This worked for the standalone but I dont' know about the cluster.|||Furthermore, you can not change the name of a PDC without reinstalling NT 4 server.

Tuesday, February 14, 2012

Change Password in XP and Lose Servers in Enterprise

I have a frustrating situation but I have been unable to
find help elsewhere on this topic. Each time I change my
password at work on my Windows XP Professional machine I
lose all the servers that I had registered in SQL
Enterprise Manager. This is very frustrating because I
have to change my password every 45 days and I have over
20 servers registered. Any ideas on how to fix this to
not lose my registrations? Please help.
thanks,
allen...It happened few times to few developers at my company. After forced to
change the password, all registered servers were gone. However we haven't
seen this come back for about 8 - 10 months. Probably a windows update or
SQL update fixed it?
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||I've never lost any of my registrations this way. However, perhaps if you
use SQL authentication instead of Windows authentication for your
registration properties?
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||Allen,
Is this the issue?
FIX: Registered Remote Servers Disappear from SQL Enterprise Manager in
Windows XP When Non-Domain User Password is Changed
http://support.microsoft.com/default.aspx?scid=kb;en-us;323280
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||btw, we are using SQL logins, not Windows ...
"Flicker" <hthan@.superioraccess.com> wrote in message
news:%23s3IRckbDHA.1600@.TK2MSFTNGP09.phx.gbl...
> It happened few times to few developers at my company. After forced to
> change the password, all registered servers were gone. However we haven't
> seen this come back for about 8 - 10 months. Probably a windows update or
> SQL update fixed it?
> "Allen" <AMora@.tradestation.com> wrote in message
> news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> > I have a frustrating situation but I have been unable to
> > find help elsewhere on this topic. Each time I change my
> > password at work on my Windows XP Professional machine I
> > lose all the servers that I had registered in SQL
> > Enterprise Manager. This is very frustrating because I
> > have to change my password every 45 days and I have over
> > 20 servers registered. Any ideas on how to fix this to
> > not lose my registrations? Please help.
> >
> > thanks,
> > allen...
>

Friday, February 10, 2012

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RTYou cannot change instance name, nor from a default to a named instance (or other way around). One
option is to install 2005 again, as a named instance (after removing 2000). Then copy the databases
(backup/restore, detach/attach, for instance). But there are extra steps handling the data inside
the system databases, though...
Or, handle this on the client machines:
Either change the connection string.
Or, create an alias for the named instance, with only the machine name as the alias name.
(Basically, rename it seen from the client's perspective.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
> default instance. Can anyone describe the steps need to do this. We want to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Sorry to use an existing post but when I try to start a new thread, nothing
happends.
I just reinstalled SQL Server 2000. During the install. I used the default
Server name of GATEWAY_COMPUTE. Now when I try to start SQL Server, the
Server name comes up as GATEWAY_COMPUTE, services is SQL Server. When I
click start I receive error message ' The service did not start due to a
login failure'.
Can someone tell me how to correct this?

change MSDE to SQL security

We have an installation that already has MSDE (SQL 2000) loaded on their
machine, so there is no enterprise manager or QA.
I need to change this to use SQL security and I need to set the sa
password.
How can I do those from the osql prompt?
Darin
*** Sent via Developersdex http://www.developersdex.com ***Darin <darin_nospam@.nospamever> wrote in news:eSrnUNgfHHA.3508
@.TK2MSFTNGP03.phx.gbl:
> We have an installation that already has MSDE (SQL 2000) loaded on their
> machine, so there is no enterprise manager or QA.
> I need to change this to use SQL security and I need to set the sa
> password.
> How can I do those from the osql prompt?
To set the password, use sp_password (described in BOL).
There is no straightforward way of using T-SQL to change the authentication
mode. It can however be done by:
- SQLDMO
- hacking the registry (e.g. see http://www.microforge.net/kb/41)
as well as using Enterprise Manager or a clone.
The non-straightforward way using T_SQL is to use the sp_OA_Create, etc
series of SPs to instantiate and manipulate an SQLDMO COM object, but I've
never tried doing this that way.|||If you mean to enable mixed security mode (i.e allowing both Windows
security and SQL Security), you can modify corresponding Windows registry
key:
1. Start "regedt32.exe"
2. Go this key:
HKEY_LOCAL_MACHINE->Software->Microsoft->MSSQLServer->MSSQLServer->LoginMode
3. Double click it to edit its value: change the "value Data" from 0 to 2
4. Click "OK" and close Registry Editor.
Now the SQL Server's mixed security mode is enabled. Disclaimer: at your own
risk to edit registry.
"Darin" <darin_nospam@.nospamever> wrote in message
news:eSrnUNgfHHA.3508@.TK2MSFTNGP03.phx.gbl...
> We have an installation that already has MSDE (SQL 2000) loaded on their
> machine, so there is no enterprise manager or QA.
> I need to change this to use SQL security and I need to set the sa
> password.
> How can I do those from the osql prompt?
>
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***

Tuesday, March 27, 2012

changing column type

Monday, March 19, 2012

Changes made in Query Analizer don't show in Enterprise Manager

Changes made in Query Analizer don't show in Enterprise Manager

Changes made in Query Analizer don't show in Enterprise Manager

Changes made in Query Analizer don't show in Enterprise Manager

Changed sa password - can't connect via Enterprise Manager now

Changed sa password - can't connect via Enterprise Manager now

Changed sa password - can't connect via Enterprise Manager now

Saturday, February 25, 2012

Change table file group filegroup

Change table file group filegroup

Friday, February 24, 2012

Change SQL Server Connection from Enterprise Manager

Change Sql Server 2000 Enterprise to Standard Edition

change SQL Server 2000 Ent to Standard

change SQL Server 2000 Ent to Standard

change SQL Server 2000 Ent to Standard

Change SQL Instance name to default(local) instrance

Sunday, February 19, 2012

Change server name in MSSQL cluster

Tuesday, February 14, 2012

Change Password in XP and Lose Servers in Enterprise

Friday, February 10, 2012

Change Named to Default Instance

change MSDE to SQL security

change MSDB owner

Blog Archive

About Me