MS ACCESS - ON DELETE CASCADE Problem

I cannot execute my sql to create a table with ON DELETE CASCADE option.
Here is my sql:

CREATE TABLE Employees (Name Text(10) not null, Age number,
CONSTRAINT pkEmployees
PRIMARY KEY (Name)); <-- This is ok!

CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,
CONSTRAINT pkCanTake
PRIMARY KEY (Name, Make),
CONSTRAINT fkCanTake
FOREIGN KEY (Name) REFERENCES Employees
ON UPDATE CASCADE <-- MS-ACCESS says 'syntax error'
ON DELETE CASCADE
);

When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works
(but that's not what I want). Why? Could anyone suggest me?
Thank you.

--
Message posted via http://www.sqlmonster.comMSDN suggests that you need to use ALTER TABLE to add cascading DRI:

http://msdn.microsoft.com/library/d...ml/acintsql.asp

However, I know very little about Access, so I suggest you post in an
Access group to get a better response.

Simon|||Thank you.

--
Message posted via http://www.sqlmonster.com

Moving transaction log for master/model/msdb databases

How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation...

Thanks

FunkyDwhy would you want to move these files? These db see little activity.

If you were posting a general question, How do I move a Database to a new location? then check out the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071|||I guess if they have little activity it's not necessary I suppose...

If you can't backup the system databases after a change then would the transaction logs help you with recovery?

We have a lot of users creating/deleting databases and I figured that the T-logs might come in handy and so thought it wise to give them the same protection as a normal db....|||What prevents you from backing up the system DB after a change?

Granted you might want to backup a user's db but the entry in the master db will ge tcreated when you restore the backup.

IMHO backing up the master db after changes saves you on lost logins and recovering from a bigger dissaster.|||How about that I don't know when changes are made lol...

That's another issue but one I am trying to work on. There is no way people should be doing stuff ad hoc on a production system... but they do!!

Thanks for the advice :-)|||You can always make friends by revoking sa from these people and force them to justify getting it back!

Again, just restoring the master DB would do little to help in restoring from a lost DB, so as long as you make a backup after changes you should be good to go. Also this might be a good reason for users to NOT be doing adhoc stuff on the production box.|||FunkyD,

The four system databases each have different methods for moving the physical files (including the log files).

Master:
1. Change the startup parameters (from the properties page for the server).
2. Shut down SQL Server.
3. Move the physical files (using Explorer)
4. Restart SQL Server (crossing your fingers here might help)

Model:
This involves setting a trace flag somewhere; look it up on line (use Google) but it is doable.

MSDB:
Same, it involves setting a trace flag. Look it up on Google.

TempDB:
This one is easy:
1. User ALTER DATABASE and the options to specify physical file locatations.
2. Restart SQL Server
3. The new TempDB master and log files will be created automatically; you can remove the old files by hand later.

As for why you might want to do this, I can't really think of any good reasons to move master or model. TempDB, on the other hand, can get a lot of use and it might help performance to move it to a separate partition that otherwise does not have a lot of I/O.

HTH,

Hugh Scott

Originally posted by FunkyD
How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation...

Thanks

FunkyD|||Thanks - okay, I won't bother moving these dataabases but instead will look at getting a procedure (another one!!) to ensure updates get a backup afterwards..

Moving the subscription database to a new server

I would like to be able to create my subscription database locally, then
take the database offline, move it to the main server where it will actually
be located, and re-attach it.
I am assuming this isn't simple - are there any instructions on how to do
it?
before is start is it merge replication or transactional.
and SQL 2000 or 2005
"Jane Parker" wrote:

> I would like to be able to create my subscription database locally, then
> take the database offline, move it to the main server where it will actually
> be located, and re-attach it.
> I am assuming this isn't simple - are there any instructions on how to do
> it?
>
>
|||We are wanting to set it up in both SQL Server 2000 and SQL Server 2005
environments.
We have one snapshot publication and one merge replication.
Thanks
"Jacx" <Jacx@.discussions.microsoft.com> wrote in message
news:0F6B5742-2959-48DD-9042-66F2BAF30BBB@.microsoft.com...[vbcol=seagreen]
> before is start is it merge replication or transactional.
> and SQL 2000 or 2005
> "Jane Parker" wrote:

Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.
Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.
|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
David Portas
SQL Server MVP
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.
|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com ...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>
|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
David Portas
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com ...
>

Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
David Portas
SQL Server MVP
--
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.gi
ganews.com...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
David Portas
SQL Server MVP
--
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.gi
ganews.com...
>

Moving Tables/File Groups

Let's say you want to move a table from file group A to file group B. What
is the easiest way to do that? Create a differently named table in file
group B and then DTS over the data and then do renames? Is there an ALTER
TABLE statement that will work and just move it over?
If I remember right, there's a trick if it has a clustered index but most
or at least many of these tables will not have a clustered index.
Any help would be appreciated.Create a clustered index using the CREATE INDEX statement with the ON
FILEGROUP clause.
HTH
Jerry
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||If you don't have a clustered index then you will have to create a new table
on the new filegroup and move the data over.
--
David Portas
SQL Server MVP
--
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
> Let's say you want to move a table from file group A to file group B.
> What
> is the easiest way to do that? Create a differently named table in file
> group B and then DTS over the data and then do renames? Is there an ALTER
> TABLE statement that will work and just move it over?
> If I remember right, there's a trick if it has a clustered index but most
> or at least many of these tables will not have a clustered index.
> Any help would be appreciated.|||David,
Is that correct? If he doesn't have a clustered index won't creating the
clustered index on the other filegroup move the data to the new filegroup?
HTH
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com...
> If you don't have a clustered index then you will have to create a new
> table on the new filegroup and move the data over.
> --
> David Portas
> SQL Server MVP
> --
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>> Let's say you want to move a table from file group A to file group B.
>> What
>> is the easiest way to do that? Create a differently named table in file
>> group B and then DTS over the data and then do renames? Is there an
>> ALTER
>> TABLE statement that will work and just move it over?
>> If I remember right, there's a trick if it has a clustered index but most
>> or at least many of these tables will not have a clustered index.
>> Any help would be appreciated.
>|||That's true. What I meant was that if you don't *want* to have a clustered
index you will have to create a new table. Of course it's questionable
whether it makes sense for many or most tables not to have a clustered
index. Sometimes you don't want a clustered index but more often it makes
sense to have one on every table.
--
David Portas
SQL Server MVP
--
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:udkaeiSyFHA.2212@.TK2MSFTNGP15.phx.gbl...
> David,
> Is that correct? If he doesn't have a clustered index won't creating the
> clustered index on the other filegroup move the data to the new filegroup?
> HTH
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:r8CdnRQBjquCbd_enZ2dnUVZ8qCdnZ2d@.giganews.com...
>> If you don't have a clustered index then you will have to create a new
>> table on the new filegroup and move the data over.
>> --
>> David Portas
>> SQL Server MVP
>> --
>> "CLM" <CLM@.discussions.microsoft.com> wrote in message
>> news:F43CC8F6-36F7-4250-9844-576640C6BF1B@.microsoft.com...
>> Let's say you want to move a table from file group A to file group B.
>> What
>> is the easiest way to do that? Create a differently named table in file
>> group B and then DTS over the data and then do renames? Is there an
>> ALTER
>> TABLE statement that will work and just move it over?
>> If I remember right, there's a trick if it has a clustered index but
>> most
>> or at least many of these tables will not have a clustered index.
>> Any help would be appreciated.
>>
>sql

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.
Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B...3A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.
|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.
|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI o
n PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns
and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate fil
egroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/...43A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default locati
on
to another one.
I would like to add a new file to the log file and remove the first file to
the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its[
vbcol=seagreen]
> own.[/vbcol]
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be
used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option i
n TSQL
to add a column at the desired location. The newly added NTEXT column should
be
right before the existing NTEXT column, so that after dropping the existing
NTEXT
column, its position is taken up by this new column. This is the only way to
guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series
of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

Moving Tables

I'm aware of the two methods of moving a table from one filegroup to another
:
(re)create a clustered index on the new filegroup or create the table on the
new filegroup then bcp data into new table.
Is there just a simple way to say "move from PRIMARY to whatever"?
Thanks!!Hi,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
Enterprise Manager moves a table from one filegroup to another without using
an undocumented task, but T-SQL doesn't have a command that does the same
thing. The easiest way to move a table to another filegroup is to create a
clustered index on the table. If the table already has a clustered index,
you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recreate
the clustered index and move it to a particular filegroup. When a table has
a clustered index, the leaf level of the index and the data pages of the
table essentially become one and the same. The table must exist where the
clustered index exists, so if you create or recreate a clustered
index-placing the index on a particular filegroup-you're moving the table to
the new filegroup as well.
Thanks
Hari
SQL Server MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D6A7F27B-309D-40C1-9663-D28B2514673A@.microsoft.com...
> I'm aware of the two methods of moving a table from one filegroup to
> another:
> (re)create a clustered index on the new filegroup or create the table on
> the
> new filegroup then bcp data into new table.
> Is there just a simple way to say "move from PRIMARY to whatever"?
> Thanks!!
>|||I was kinda afraid of that...that's the way I've done it in the past.
Thanks!
What aboutr setting a filgroup to read only - through the gui only'
"Hari Pra" wrote:

> Hi,
> Easiest method is to re-create the clustered index in new file group. This
> will move the table to the new file group automatically.
> Enterprise Manager moves a table from one filegroup to another without usi
ng
> an undocumented task, but T-SQL doesn't have a command that does the same
> thing. The easiest way to move a table to another filegroup is to create a
> clustered index on the table. If the table already has a clustered index,
> you can use the CREATE INDEX command's WITH DROP_EXISTING clause to recrea
te
> the clustered index and move it to a particular filegroup. When a table ha
s
> a clustered index, the leaf level of the index and the data pages of the
> table essentially become one and the same. The table must exist where the
> clustered index exists, so if you create or recreate a clustered
> index-placing the index on a particular filegroup-you're moving the table
to
> the new filegroup as well.
> Thanks
> Hari
> SQL Server MVP
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:D6A7F27B-309D-40C1-9663-D28B2514673A@.microsoft.com...
>
>|||Hi,
I have moved the table to different file group couple of times. But ensure
that you take a full database backup before doing this.
I have never tried setting a file group to read only from GUI, but i did
once using TSQL. But i belive to set the file group to read only first you
need to set the database to SINGLE USER Mode.
Thanks
Hari
SQL Server MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:3613F67F-6AE7-4A5A-987D-85C5269675F1@.microsoft.com...
>I was kinda afraid of that...that's the way I've done it in the past.
> Thanks!
> What aboutr setting a filgroup to read only - through the gui only'
> "Hari Pra" wrote:
>

Moving Sql2000 databases to 2005 one by one

Sorry for cross-posting!
We need to migrate our SQLserver2K one by one to a new server with
SQLserver2005.
I plan to create an empty database in 05' with the same name of that of 2K,
create a backup for 2K database and then restore it in 05. Will it work?
If not, what is the process to do this correctly and safely?
Thanks a million.
Bill
Use SQL Server Upgrade Advisor before migarting.
You can backup/restore or detach/attach databases.
Change compatibility level to 90 after migrating databse on SQL Server
2005
Regards
Amish Shah
|||Bill,
You should be able to either take a backup under 2000 and then just restore
this backup to 2005 or detach from 2000 and attach the original MDF/LDF or a
copied version to 2005.
Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
first.
Chris Wood
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Sorry for cross-posting!
> We need to migrate our SQLserver2K one by one to a new server with
> SQLserver2005.
> I plan to create an empty database in 05' with the same name of that of
> 2K, create a backup for 2K database and then restore it in 05. Will it
> work?
> If not, what is the process to do this correctly and safely?
> Thanks a million.
> Bill
>
|||Which server I should install Upgrade Advisor? The new one (running 05) or
the old one (running 2000)?
I just did a backup/restore withour running Upgrade advisor. All tables and
views look ok, but I got error trying to ope Database diagram because "the
database doesn't have a valid owner!"
Where do I need to go to change the compatibility level?
Thanks
BN
"amish" <shahamishm@.gmail.com> wrote in message
news:1143649568.136416.302840@.g10g2000cwb.googlegr oups.com...
> Use SQL Server Upgrade Advisor before migarting.
> You can backup/restore or detach/attach databases.
> Change compatibility level to 90 after migrating databse on SQL Server
> 2005
>
> Regards
> Amish Shah
>
sql

Moving Sql2000 databases to 2005 one by one

Sorry for cross-posting!
We need to migrate our SQLserver2K one by one to a new server with
SQLserver2005.
I plan to create an empty database in 05' with the same name of that of 2K,
create a backup for 2K database and then restore it in 05. Will it work?
If not, what is the process to do this correctly and safely?
Thanks a million.
BillUse SQL Server Upgrade Advisor before migarting.
You can backup/restore or detach/attach databases.
Change compatibility level to 90 after migrating databse on SQL Server
2005
Regards
Amish Shah|||Bill,
You should be able to either take a backup under 2000 and then just restore
this backup to 2005 or detach from 2000 and attach the original MDF/LDF or a
copied version to 2005.
Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
first.
Chris Wood
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Sorry for cross-posting!
> We need to migrate our SQLserver2K one by one to a new server with
> SQLserver2005.
> I plan to create an empty database in 05' with the same name of that of
> 2K, create a backup for 2K database and then restore it in 05. Will it
> work?
> If not, what is the process to do this correctly and safely?
> Thanks a million.
> Bill
>|||Which server I should install Upgrade Advisor? The new one (running 05) or
the old one (running 2000)?
I just did a backup/restore withour running Upgrade advisor. All tables and
views look ok, but I got error trying to ope Database diagram because "the
database doesn't have a valid owner!"
Where do I need to go to change the compatibility level?
Thanks
BN
"amish" <shahamishm@.gmail.com> wrote in message
news:1143649568.136416.302840@.g10g2000cwb.googlegroups.com...
> Use SQL Server Upgrade Advisor before migarting.
> You can backup/restore or detach/attach databases.
> Change compatibility level to 90 after migrating databse on SQL Server
> 2005
>
> Regards
> Amish Shah
>|||Chris;
I got this error message trying to restore the backup (from old server -
SQL2000) to new server (SQL2005):
TITLE: Microsoft SQL Server Management Studio
--
Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
Please help!!
Bill
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
> Bill,
> You should be able to either take a backup under 2000 and then just
> restore this backup to 2005 or detach from 2000 and attach the original
> MDF/LDF or a copied version to 2005.
> Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
> first.
> Chris Wood
> "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
>|||Chris,
I am trying to migrate from SQL 7 to SQL 2005. I also get an error when I
try to restore
my SQL 7 backup to SQL 2005. Your link did not work. I get an error when I
click on
it as well.
I also took a different route by just using the import and export function
in both SQLs.
This was working fine until I got to a table that had a field longer than 50
chars. For
some reason I get an error "Status value 4" and the import fails. If I
reduce the size of
the field to 50 or less it works fine. How do I get the import procedure to
except field sizes
larger than 50 chars?
Thanks
"Bill Nguyen" wrote:

> Chris;
> I got this error message trying to restore the backup (from old server -
> SQL2000) to new server (SQL2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a databas
e
> other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
> Please help!!
> Bill
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
>
>|||Chris,
I am trying to migrate from SQL 7 to SQL 2005. I also get an error when I
try to restore
my SQL 7 backup to SQL 2005. Your link did not work. I get an error when I
click on
it as well.
I also took a different route by just using the import and export function
in both SQLs.
This was working fine until I got to a table that had a field longer than 50
chars. For
some reason I get an error "Status value 4" and the import fails. If I
reduce the size of
the field to 50 or less it works fine. How do I get the import procedure to
except field sizes
larger than 50 chars?
Thanks
"Bill Nguyen" wrote:

> Chris;
> I got this error message trying to restore the backup (from old server -
> SQL2000) to new server (SQL2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a databas
e
> other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
> Please help!!
> Bill
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
>
>|||I don't use the GUI for these tasks. Typically, I would just do 'restore
filelistonly' and 'restore headeronly' to get the info about the backup set.
Then I do 'restore database <db> ... with replace, move...'
I suggest you take a look at bookonline for these commands and try them out.
-oj
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:1AA68C0A-71FE-44E4-BD07-4B2B09DC4AC1@.microsoft.com...[vbcol=seagreen]
> Chris,
> I am trying to migrate from SQL 7 to SQL 2005. I also get an error when I
> try to restore
> my SQL 7 backup to SQL 2005. Your link did not work. I get an error when
> I
> click on
> it as well.
> I also took a different route by just using the import and export function
> in both SQLs.
> This was working fine until I got to a table that had a field longer than
> 50
> chars. For
> some reason I get an error "Status value 4" and the import fails. If I
> reduce the size of
> the field to 50 or less it works fine. How do I get the import procedure
> to
> except field sizes
> larger than 50 chars?
> Thanks
> "Bill Nguyen" wrote:
>

Moving Sql2000 databases to 2005 one by one

Sorry for cross-posting!
We need to migrate our SQLserver2K one by one to a new server with
SQLserver2005.
I plan to create an empty database in 05' with the same name of that of 2K,
create a backup for 2K database and then restore it in 05. Will it work?
If not, what is the process to do this correctly and safely?
Thanks a million.
BillUse SQL Server Upgrade Advisor before migarting.
You can backup/restore or detach/attach databases.
Change compatibility level to 90 after migrating databse on SQL Server
2005
Regards
Amish Shah|||Bill,
You should be able to either take a backup under 2000 and then just restore
this backup to 2005 or detach from 2000 and attach the original MDF/LDF or a
copied version to 2005.
Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
first.
Chris Wood
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Sorry for cross-posting!
> We need to migrate our SQLserver2K one by one to a new server with
> SQLserver2005.
> I plan to create an empty database in 05' with the same name of that of
> 2K, create a backup for 2K database and then restore it in 05. Will it
> work?
> If not, what is the process to do this correctly and safely?
> Thanks a million.
> Bill
>|||Which server I should install Upgrade Advisor? The new one (running 05) or
the old one (running 2000)?
I just did a backup/restore withour running Upgrade advisor. All tables and
views look ok, but I got error trying to ope Database diagram because "the
database doesn't have a valid owner!"
Where do I need to go to change the compatibility level?
Thanks
BN
"amish" <shahamishm@.gmail.com> wrote in message
news:1143649568.136416.302840@.g10g2000cwb.googlegroups.com...
> Use SQL Server Upgrade Advisor before migarting.
> You can backup/restore or detach/attach databases.
> Change compatibility level to 90 after migrating databse on SQL Server
> 2005
>
> Regards
> Amish Shah
>|||Chris;
I got this error message trying to restore the backup (from old server -
SQL2000) to new server (SQL2005):
TITLE: Microsoft SQL Server Management Studio
--
Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
Please help!!
Bill
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
> Bill,
> You should be able to either take a backup under 2000 and then just
> restore this backup to 2005 or detach from 2000 and attach the original
> MDF/LDF or a copied version to 2005.
> Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
> first.
> Chris Wood
> "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
>> Sorry for cross-posting!
>> We need to migrate our SQLserver2K one by one to a new server with
>> SQLserver2005.
>> I plan to create an empty database in 05' with the same name of that of
>> 2K, create a backup for 2K database and then restore it in 05. Will it
>> work?
>> If not, what is the process to do this correctly and safely?
>> Thanks a million.
>> Bill
>>
>|||Chris,
I am trying to migrate from SQL 7 to SQL 2005. I also get an error when I
try to restore
my SQL 7 backup to SQL 2005. Your link did not work. I get an error when I
click on
it as well.
I also took a different route by just using the import and export function
in both SQLs.
This was working fine until I got to a table that had a field longer than 50
chars. For
some reason I get an error "Status value 4" and the import fails. If I
reduce the size of
the field to 50 or less it works fine. How do I get the import procedure to
except field sizes
larger than 50 chars?
Thanks
"Bill Nguyen" wrote:
> Chris;
> I got this error message trying to restore the backup (from old server -
> SQL2000) to new server (SQL2005):
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a database
> other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
> Please help!!
> Bill
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
> > Bill,
> >
> > You should be able to either take a backup under 2000 and then just
> > restore this backup to 2005 or detach from 2000 and attach the original
> > MDF/LDF or a copied version to 2005.
> >
> > Use the Upgrade Advisor to see that the code works under 2005 (90 compat)
> > first.
> >
> > Chris Wood
> >
> > "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
> > news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> >> Sorry for cross-posting!
> >>
> >> We need to migrate our SQLserver2K one by one to a new server with
> >> SQLserver2005.
> >>
> >> I plan to create an empty database in 05' with the same name of that of
> >> 2K, create a backup for 2K database and then restore it in 05. Will it
> >> work?
> >> If not, what is the process to do this correctly and safely?
> >>
> >> Thanks a million.
> >>
> >> Bill
> >>
> >>
> >
> >
>
>|||I don't use the GUI for these tasks. Typically, I would just do 'restore
filelistonly' and 'restore headeronly' to get the info about the backup set.
Then I do 'restore database <db> ... with replace, move...'
I suggest you take a look at bookonline for these commands and try them out.
-oj
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:1AA68C0A-71FE-44E4-BD07-4B2B09DC4AC1@.microsoft.com...
> Chris,
> I am trying to migrate from SQL 7 to SQL 2005. I also get an error when I
> try to restore
> my SQL 7 backup to SQL 2005. Your link did not work. I get an error when
> I
> click on
> it as well.
> I also took a different route by just using the import and export function
> in both SQLs.
> This was working fine until I got to a table that had a field longer than
> 50
> chars. For
> some reason I get an error "Status value 4" and the import fails. If I
> reduce the size of
> the field to 50 or less it works fine. How do I get the import procedure
> to
> except field sizes
> larger than 50 chars?
> Thanks
> "Bill Nguyen" wrote:
>> Chris;
>> I got this error message trying to restore the backup (from old server -
>> SQL2000) to new server (SQL2005):
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Restore failed for Server 'SQLSERVER1'. (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
>> --
>> ADDITIONAL INFORMATION:
>> System.Data.SqlClient.SqlError: The backup set holds a backup of a
>> database
>> other than the existing 'gasstation' database. (Microsoft.SqlServer.Smo)
>> Please help!!
>> Bill
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ubgoi%230UGHA.5092@.TK2MSFTNGP10.phx.gbl...
>> > Bill,
>> >
>> > You should be able to either take a backup under 2000 and then just
>> > restore this backup to 2005 or detach from 2000 and attach the original
>> > MDF/LDF or a copied version to 2005.
>> >
>> > Use the Upgrade Advisor to see that the code works under 2005 (90
>> > compat)
>> > first.
>> >
>> > Chris Wood
>> >
>> > "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
>> > news:%235al72zUGHA.5108@.TK2MSFTNGP09.phx.gbl...
>> >> Sorry for cross-posting!
>> >>
>> >> We need to migrate our SQLserver2K one by one to a new server with
>> >> SQLserver2005.
>> >>
>> >> I plan to create an empty database in 05' with the same name of that
>> >> of
>> >> 2K, create a backup for 2K database and then restore it in 05. Will it
>> >> work?
>> >> If not, what is the process to do this correctly and safely?
>> >>
>> >> Thanks a million.
>> >>
>> >> Bill
>> >>
>> >>
>> >
>> >
>>

Moving report server to new virtual server

Hi
I've about had it trying to mix WSS and report server so I'd like to create
a new virtual server and put report server on it. Do I have to reinstall
the whole thing or can I just move it?
TIA
BillYou can move them. The following article tells you how:
http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<sutphinwb@.nospam.nospam> wrote in message
news:OjOS%23dHEFHA.3580@.TK2MSFTNGP10.phx.gbl...
> Hi
> I've about had it trying to mix WSS and report server so I'd like to
> create a new virtual server and put report server on it. Do I have to
> reinstall the whole thing or can I just move it?
> TIA
> Bill
>

Moving Logins from 6.5 to 2000

Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
--
New SQL Server DBAHi,
The method to transfer the logins with the password from SQL Server 6.5 to S
QL Server 2000 is
to upgrade the databases using "upgrade wizard". There is no documented way
to move logins with password using scripts.
But there is undocumented method method to do this. see the below link. I ha
ve never tried this.
http://groups-beta.google.com/group...99a55c7fbed5496
FOR SQL 7 and SQL 2000 servers you can read the below article to transfer lo
gins.
http://support.microsoft.com/?kbid=246133
Thanks
Hari
SQL Server MVP
____________________________________
Newbie Wrote:
Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
--
New SQL Server DBA
Sent via SreeSharp NewsReader http://www.SreeSharp.com|||Thanks for the advise. How do I get this upgrade wizard? Thanks.
"Hari Prasad" wrote:

> Hi,
> The method to transfer the logins with the password from SQL Server 6.5 to
SQL Server 2000 is
> to upgrade the databases using "upgrade wizard". There is no documented wa
y to move logins with password using scripts.
> But there is undocumented method method to do this. see the below link. I
have never tried this.
> http://groups-beta.google.com/group...99a55c7fbed5496
>
> FOR SQL 7 and SQL 2000 servers you can read the below article to transfer
logins.
> http://support.microsoft.com/?kbid=246133
>
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Newbie Wrote:
> Hello all,
> I need to move over all the users in 6.5 into 2000, but when i created the
> script to create the logins, the passwords came in as null into the new 20
00
> db. How do I birng over the passwords from 6.5 into the new 2000? Any he
lp
> is appreciated. Thanks in advance.
> --
> New SQL Server DBA
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com
>|||Hi,
This comes automatically when you install SQL Server 2000. After
installation see the SQL Server program groups.
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:C222A6D0-0AF9-4F6E-88D8-AA280AE1E2CF@.microsoft.com...[vbcol=seagreen]
> Thanks for the advise. How do I get this upgrade wizard? Thanks.
> "Hari Prasad" wrote:
>

Moving Logins from 6.5 to 2000

Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
New SQL Server DBA
Hi,
The method to transfer the logins with the password from SQL Server 6.5 to SQL Server 2000 is
to upgrade the databases using "upgrade wizard". There is no documented way to move logins with password using scripts.
But there is undocumented method method to do this. see the below link. I have never tried this.
http://groups-beta.google.com/group/...9a55c7fbed5496
FOR SQL 7 and SQL 2000 servers you can read the below article to transfer logins.
http://support.microsoft.com/?kbid=246133
Thanks
Hari
SQL Server MVP
____________________________________
Newbie Wrote:
Hello all,
I need to move over all the users in 6.5 into 2000, but when i created the
script to create the logins, the passwords came in as null into the new 2000
db. How do I birng over the passwords from 6.5 into the new 2000? Any help
is appreciated. Thanks in advance.
New SQL Server DBA
Sent via SreeSharp NewsReader http://www.SreeSharp.com
|||Thanks for the advise. How do I get this upgrade wizard? Thanks.
"Hari Prasad" wrote:

> Hi,
> The method to transfer the logins with the password from SQL Server 6.5 to SQL Server 2000 is
> to upgrade the databases using "upgrade wizard". There is no documented way to move logins with password using scripts.
> But there is undocumented method method to do this. see the below link. I have never tried this.
> http://groups-beta.google.com/group/...9a55c7fbed5496
>
> FOR SQL 7 and SQL 2000 servers you can read the below article to transfer logins.
> http://support.microsoft.com/?kbid=246133
>
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Newbie Wrote:
> Hello all,
> I need to move over all the users in 6.5 into 2000, but when i created the
> script to create the logins, the passwords came in as null into the new 2000
> db. How do I birng over the passwords from 6.5 into the new 2000? Any help
> is appreciated. Thanks in advance.
> --
> New SQL Server DBA
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com
>
|||Hi,
This comes automatically when you install SQL Server 2000. After
installation see the SQL Server program groups.
Thanks
Hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:C222A6D0-0AF9-4F6E-88D8-AA280AE1E2CF@.microsoft.com...[vbcol=seagreen]
> Thanks for the advise. How do I get this upgrade wizard? Thanks.
> "Hari Prasad" wrote:

moving logins and password to new server

Hi Freinds,
SQL2000
I am using sp_help_revlogin to create the script for account to recreate on
new server.
It is ok, but when I am trying to add a server role or add database access
to new users on new servre , it is asking me the password for verification.
But on the old server it is not like this, no password asked for adding a
new server role.
Any idea?
Thanks
PatHOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Patrick" wrote:

> Hi Freinds,
> SQL2000
> I am using sp_help_revlogin to create the script for account to recreate o
n
> new server.
> It is ok, but when I am trying to add a server role or add database access
> to new users on new servre , it is asking me the password for verification
.
> But on the old server it is not like this, no password asked for adding a
> new server role.
> Any idea?
> Thanks
> Pat
>
>|||Are you attempting to make these changes using Enterprise Manager ? If
so, see "FIX: You are prompted for password confirmation after you
change a standard SQL Server login"
at http://support.microsoft.com/default.aspx/kb/826161
From MS page - Sympton:
After you install MS03-031: Cumulative Security Patch for SQL Server,
when you make changes to a standard SQL Server login by using Enterprise
Manager, you are prompted for a password, even if you did not change the
password. If you did not change the password, you cannot successfully
close the dialog box, regardless of the entry that you use.
*** Sent via Developersdex http://www.examnotes.net ***

Moving logins

Hi
How can I copy or move the users from one SQL server to another?
Is there a utility that transfer them or should I create one by one?
Than'x
SHay
This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
"Tibor Karaszi" wrote:

> Google on sp_help_revlogin. It does exactly what you want (assuming you mean logins and not users).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
>
|||Wonderful! I was looking for this for a long time. Thank you very much!
"Tibor Karaszi" wrote:

> Here's an article that covers 2005:
> http://support.microsoft.com/kb/246133
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in message
> news:B5A02027-1873-4E6D-956A-F1DAC98332A3@.microsoft.com...
>

Moving logins

Hi
How can I copy or move the users from one SQL server to another?
Is there a utility that transfer them or should I create one by one?
Than'x
SHayGoogle on sp_help_revlogin. It does exactly what you want (assuming you mean logins and not users).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I copy or move the users from one SQL server to another?
> Is there a utility that transfer them or should I create one by one?
> Than'x
> SHay
>|||This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
"Tibor Karaszi" wrote:
> Google on sp_help_revlogin. It does exactly what you want (assuming you mean logins and not users).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
> > Hi
> >
> > How can I copy or move the users from one SQL server to another?
> > Is there a utility that transfer them or should I create one by one?
> >
> > Than'x
> > SHay
> >
> >
>|||Here's an article that covers 2005:
http://support.microsoft.com/kb/246133
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in message
news:B5A02027-1873-4E6D-956A-F1DAC98332A3@.microsoft.com...
> This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
> "Tibor Karaszi" wrote:
>> Google on sp_help_revlogin. It does exactly what you want (assuming you mean logins and not
>> users).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
>> > Hi
>> >
>> > How can I copy or move the users from one SQL server to another?
>> > Is there a utility that transfer them or should I create one by one?
>> >
>> > Than'x
>> > SHay
>> >
>> >|||Wonderful! I was looking for this for a long time. Thank you very much!
"Tibor Karaszi" wrote:
> Here's an article that covers 2005:
> http://support.microsoft.com/kb/246133
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in message
> news:B5A02027-1873-4E6D-956A-F1DAC98332A3@.microsoft.com...
> > This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Google on sp_help_revlogin. It does exactly what you want (assuming you mean logins and not
> >> users).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
> >> > Hi
> >> >
> >> > How can I copy or move the users from one SQL server to another?
> >> > Is there a utility that transfer them or should I create one by one?
> >> >
> >> > Than'x
> >> > SHay
> >> >
> >> >
> >>
>

Moving logins

Hi
How can I copy or move the users from one SQL server to another?
Is there a utility that transfer them or should I create one by one?
Than'x
SHayGoogle on sp_help_revlogin. It does exactly what you want (assuming you mean
logins and not users).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I copy or move the users from one SQL server to another?
> Is there a utility that transfer them or should I create one by one?
> Than'x
> SHay
>|||This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
"Tibor Karaszi" wrote:

> Google on sp_help_revlogin. It does exactly what you want (assuming you me
an logins and not users).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "S" <s@.s.com> wrote in message news:eh2Rqc4ZHHA.4008@.TK2MSFTNGP05.phx.gbl.
.
>|||Here's an article that covers 2005:
http://support.microsoft.com/kb/246133
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in me
ssage
news:B5A02027-1873-4E6D-956A-F1DAC98332A3@.microsoft.com...[vbcol=seagreen]
> This holds good for SQL 2000. I could not find one for SQL 2005. Thanks.
> "Tibor Karaszi" wrote:
>|||Wonderful! I was looking for this for a long time. Thank you very much!
"Tibor Karaszi" wrote:

> Here's an article that covers 2005:
> http://support.microsoft.com/kb/246133
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in
message
> news:B5A02027-1873-4E6D-956A-F1DAC98332A3@.microsoft.com...
>