Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and
change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
--
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
--
thanks - dave
"David Thielen" wrote:
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA
|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave
|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening por
t
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out wi
th
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening port
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

Change version of SQL 2000 on running system

Hi,

is it possible to change the installed version on a server from SQL 2000 Standard Edition to SQL 2000 Developer Edition?

The server has been a production server and is now only used for testing by development, not from end users.

Best regards, Stefoon

I think the only supported way to do this is to uninstall and reinstall SQL 2000.

Thanks,
Sam Lester (MSFT)

|||Yes as you're downgrading the edition, but you can use the database without any issues with a backup.|||

Satya, what's the trick to change a current edition on a machine other than uninstall/reinstall?

Thanks,
Sam

Change version from SQL2K Developer Edition to SQL2K Standard Edition ?

Is there a "recipe" to do this ?
B.r.
Keld ThomsenJust upgrade!
"Keld Stig Thomsen" <dsl85271@.vip.cybercity.dk> wrote in message news:ckmnu6
$1mun$1@.news.cybercity.dk...
Is there a "recipe" to do this ?
B.r.
Keld Thomsen|||This type of "upgrade" is not supported according to the Online Books, Techn
et etc.
You can only upgrade an SQL2K Developer Edition to SQL2K Enterprise Edition
- NOT to Standard Edition:
http://msdn.microsoft.com/library/d.../>
ew_2xtf.asp
And that's why I'm asking for a 'recipe'.
Best regards
Keld Stig Thomsen
"TJ" <tunj@.hotmail.com> wrote in message news:uUvZyTlsEHA.3412@.TK2MSFTNGP14.
phx.gbl...
Just upgrade!
"Keld Stig Thomsen" <dsl85271@.vip.cybercity.dk> wrote in message news:ckmnu6
$1mun$1@.news.cybercity.dk...
Is there a "recipe" to do this ?
B.r.
Keld Thomsen

Change version from SQL2K Developer Edition to SQL2K Standard Edition ?

Is there a "recipe" to do this ?
B.r.
Keld Thomsen
Just upgrade!
"Keld Stig Thomsen" <dsl85271@.vip.cybercity.dk> wrote in message news:ckmnu6$1mun$1@.news.cybercity.dk...
Is there a "recipe" to do this ?
B.r.
Keld Thomsen
|||This type of "upgrade" is not supported according to the Online Books, Technet etc.
You can only upgrade an SQL2K Developer Edition to SQL2K Enterprise Edition - NOT to Standard Edition:
http://msdn.microsoft.com/library/de...rview_2xtf.asp
And that's why I'm asking for a 'recipe'.
Best regards
Keld Stig Thomsen
"TJ" <tunj@.hotmail.com> wrote in message news:uUvZyTlsEHA.3412@.TK2MSFTNGP14.phx.gbl...
Just upgrade!
"Keld Stig Thomsen" <dsl85271@.vip.cybercity.dk> wrote in message news:ckmnu6$1mun$1@.news.cybercity.dk...
Is there a "recipe" to do this ?
B.r.
Keld Thomsen

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

Hi

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?

|||

Ok

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...|||

Hi

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 ?

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

Hi

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?

|||

Ok

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...|||

Hi

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 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 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.

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
..
..
..
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr Kolodziej
Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
Ekrem nsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
.
.
.
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr KolodziejThe error message tell you what the problem is. The model database determine
s the minimum database
size. The size of model in 2005 (data file) is 3MB, which is why you can't m
ake the new database
smaller. You could shrink model first, but I do not recommend that solution.
Amend your script
instead and make the new database a reasonable size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej|||Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
Ekrem nsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej

Change minimum database size from 3mb to 2mb

Hi
I have been using sql express so far with my FileManager database.
Since I migrated to SQL 2005 developer edition, I am unable to execute
sql script created from FileManager database before uninstalling sql
express.
Code:
USE [master]
GO
/****** Object: Database [FileManager] Script Date: 11/14/2007
19:58:28 ******/
CREATE DATABASE [FileManager] ON PRIMARY
( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
.
.
.
Error:
Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at
least 3 MB to accommodate a copy of the model database.
How do i correct that.
Regards
Piotr KolodziejThe error message tell you what the problem is. The model database determines the minimum database
size. The size of model in 2005 (data file) is 3MB, which is why you can't make the new database
smaller. You could shrink model first, but I do not recommend that solution. Amend your script
instead and make the new database a reasonable size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej|||Hello,
You can not create database files less than your model database's database
files.
By default, model database has a 3MB in size mdf file and 1MB in size ldf
file. So you can not create a data file (mdf) less than 3MB in this
situation. If you want to
create a 2MB in size data file, then go and change your model databases mdf
data file size and set it 2MB. Then you'll be able to run your following
code successfully.
--
Ekrem Önsoy
"Piotrekk" <Piotr.Kolodziej@.gmail.com> wrote in message
news:6ef9dfd8-9cee-4ba2-82aa-bf09176b79bb@.b36g2000hsa.googlegroups.com...
> Hi
> I have been using sql express so far with my FileManager database.
> Since I migrated to SQL 2005 developer edition, I am unable to execute
> sql script created from FileManager database before uninstalling sql
> express.
> Code:
> USE [master]
> GO
> /****** Object: Database [FileManager] Script Date: 11/14/2007
> 19:58:28 ******/
> CREATE DATABASE [FileManager] ON PRIMARY
> ( NAME = N'FileManager', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\FileManager.mdf' , SIZE = 2048KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'FileManager_log', FILENAME = N'c:\Program Files\Microsoft
> SQL Server\MSSQL.1\MSSQL\DATA\FileManager_log.ldf' , SIZE = 1024KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> .
> .
> .
>
> Error:
> Msg 1803, Level 16, State 1, Line 2
> The CREATE DATABASE statement failed. The primary file must be at
> least 3 MB to accommodate a copy of the model database.
>
> How do i correct that.
> Regards
> Piotr Kolodziej