MS Access & MS SQL Server: long time answers

I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?Are you using a standard Microsoft Access database with links to the SQL Server tables, or are you using a Microsoft Access Data Project (.adp file extenstion)?

If you are linking to SQL server, MS Access will do it's best to formulate your queries as "pass through", meaning only the sql text is sent to the server and only the process results are returned. If it is unable to formulate it as a pass-through query than Access must temporarily transfer all the tables involved across your network and then process the results locally. You can imagine how this could be unimaginably slow. (?)

I recommend that you convert your application to an Access Data Project if possible.

blindman|||I am not sure about ADP, but Access2K does not use pass-through for linked objects. In fact, the only way you can use pass-through is by selecting it from the query builder.|||It is standard Access 97 VBA application with use of DLOOKUP function.
The long time answers for DLOOKUP queries are not the rule.
I can use DLOOKUP about 10 times with fast answer (under 1 second) and next time I have to wait 10 seconds.sql

Moving to a filegroup

I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo.
Your would be able to Create to new Filegroup and to Create a New DataFile a
nd to establish this the new FileGroup. Then you should modify the tables t
hat you want to move the new filegroup.
You can utilize the Query Analyzer and to execute the "Alter Table" statemen
t for each table.
Hermilson.
****************************************
********************
I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
Ricardo

Moving to a filegroup

I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo
Your would be able to Create to new Filegroup and to Create a New DataFile and to establish this the new FileGroup. Then you should modify the tables that you want to move the new filegroup
You can utilize the Query Analyzer and to execute the "Alter Table" statement for each table
Hermilson
***********************************************************
I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
Ricard

Moving the SQL 2005 System Tables

2 Questions Actually!
1. I know how to move all the system DB's in 2000, but I am a bit confused
as to how to accomplish this in 2005.
I need to move:
Master
Model
MSDB
I've already moved Temp.
2. Now, there is this other DB, called the resource DB, which they tell you
not to rename or move. The confusing part is that at install, I did not
have an option to specify where I wanted my log files to go, only my DB
files. Did I miss something?> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
The steps for moving all the system databases are in Books Online (December
2005 version) in a topic titled 'Moving System Databases'. It sounds like
you don't have the December version. You can download it from here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move.
There are specific steps for moving the Resource database in the Books
Online topic mentioned above,. Note that the Resource database must be
moved to the same location as the master.mdf file. This is discussed in the
topic.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:eQe9C9SHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>2 Questions Actually!
> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
> I need to move:
> Master
> Model
> MSDB
> I've already moved Temp.
> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move. The confusing part is that at install, I did not
> have an option to specify where I wanted my log files to go, only my DB
> files. Did I miss something?
>
>

Moving the SQL 2005 System Tables

2 Questions Actually!
1. I know how to move all the system DB's in 2000, but I am a bit confused
as to how to accomplish this in 2005.
I need to move:
Master
Model
MSDB
I've already moved Temp.
2. Now, there is this other DB, called the resource DB, which they tell you
not to rename or move. The confusing part is that at install, I did not
have an option to specify where I wanted my log files to go, only my DB
files. Did I miss something?
> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
The steps for moving all the system databases are in Books Online (December
2005 version) in a topic titled 'Moving System Databases'. It sounds like
you don't have the December version. You can download it from here:
http://www.microsoft.com/technet/pro...ads/books.mspx

> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move.
There are specific steps for moving the Resource database in the Books
Online topic mentioned above,. Note that the Resource database must be
moved to the same location as the master.mdf file. This is discussed in the
topic.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:eQe9C9SHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>2 Questions Actually!
> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
> I need to move:
> Master
> Model
> MSDB
> I've already moved Temp.
> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move. The confusing part is that at install, I did not
> have an option to specify where I wanted my log files to go, only my DB
> files. Did I miss something?
>
>

Moving the SQL 2005 System Tables

2 Questions Actually!
1. I know how to move all the system DB's in 2000, but I am a bit confused
as to how to accomplish this in 2005.
I need to move:
Master
Model
MSDB
I've already moved Temp.
2. Now, there is this other DB, called the resource DB, which they tell you
not to rename or move. The confusing part is that at install, I did not
have an option to specify where I wanted my log files to go, only my DB
files. Did I miss something?> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
The steps for moving all the system databases are in Books Online (December
2005 version) in a topic titled 'Moving System Databases'. It sounds like
you don't have the December version. You can download it from here:
http://www.microsoft.com/technet/pr...oads/books.mspx

> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move.
There are specific steps for moving the Resource database in the Books
Online topic mentioned above,. Note that the Resource database must be
moved to the same location as the master.mdf file. This is discussed in the
topic.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:eQe9C9SHGHA.2040@.TK2MSFTNGP14.phx.gbl...
>2 Questions Actually!
> 1. I know how to move all the system DB's in 2000, but I am a bit confused
> as to how to accomplish this in 2005.
> I need to move:
> Master
> Model
> MSDB
> I've already moved Temp.
> 2. Now, there is this other DB, called the resource DB, which they tell
> you
> not to rename or move. The confusing part is that at install, I did not
> have an option to specify where I wanted my log files to go, only my DB
> files. Did I miss something?
>
>sql

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, procs ... etc from full version of SQL Server to MS

I have all of the tables, procs ... etc created using a full version of SQL
Server. Now I want to move the tables, proc ... etc to MSDE so I can
distribute what I've created. Is there an easy way?
detach db .. copy Db and log file to MSDE installed computer
attach db on MSDE
"MisterB" <MisterB@.discussions.microsoft.com> wrote in message
news:55FAC4EA-DC77-426F-84D0-8395521103C8@.microsoft.com...
> I have all of the tables, procs ... etc created using a full version of
SQL
> Server. Now I want to move the tables, proc ... etc to MSDE so I can
> distribute what I've created. Is there an easy way?

Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, ChuckYou can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:
> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, Chuck
You can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:

> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>
|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving tables to new schema

I am using sql05 and need to move some tables/data/keys to a new
schema (same database).
Any easy way to do that?
thanks, ChuckYou can use ALTER SCHEMA to move an object from one schema to another.
For example to move the HumanResources.Department table to the Sales schema
you can use the following command:
USE AdventureWorks
GO
ALTER SCHEMA Sales TRANSFER HumanResources.Department
HTH
- Peter Ward
WARDY IT Solutions
"Chuck P" wrote:

> I am using sql05 and need to move some tables/data/keys to a new
> schema (same database).
> Any easy way to do that?
> thanks, Chuck
>|||Thanks, you saved me a lot of work, I was getting ready to do it the
old fashion way.
Great command too, my thanks to MS for that one.

Moving tables in SQL Server 2005 from one server to another

I have been working with databases for nearly 10 years now, but for
the life of me I can't figure out how to move database tables from one
server to another in SQL Server 2005 without using scripts.
I used to use DTS in SQL Server 2000 and everything seemed to go
smoothly. I used a combination of a Visio document (to drop and
recreate constraints, add columns, change defaults, etc) and DTS to
manage my whole deployment.
Now I am trying these tasks in SQL Server 2005 and while Visio still
can keep the structure of my tables in sync, I am having issues with
the replacement tool for DTS.
I tried using SSIS to move the data, and this is a description of the
issues I am having. I can't find any posts from anybody having these
issues, and I really don't understand why because I am getting the
same results on 2 different installations of SQL Server 2005.
1. I start by going through the menu and clicking the "import data" or
"export data" command.
2. I choose the source and destination database. I use the "SQL Native
Client" driver.
3. I choose the "Copy data from one or more tables or views" option.
4. I choose my table and give it a name in the destination database.
5. I click the "Edit Mappings" button, and choose the option to delete
rows in destination table, and check the box for "enable identity
insert".
6. Then I choose to execute the package immediately.
When I follow these exact steps, I get the following results:
1. The destination data is not removed from the table before the data
from the source is inserted.
2. The data that is inserted is renumbered starting from the last
index of the table, rather than using the original identity values.
Can somebody please explain to me why I can't get the exact same data
into another table in another database like I could in the previous
version of SQL Server?
If I have to switch to scripts to do this I will even though I much
prefer the GUI. If someone can suggest an easy way to generate the
scripts, it would be helpful too.I use linked server for example. However , i tried to reproduce your problem
( I don't have right now access to the second server) so I tried to import
table from one database to another. When I cliked on Edit Mappings button
a 'Delete rows in destination table' was not enabled at all. So I went for
re-create destination table and it worked fine
What is the version are you using?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458357.350505.83230@.z24g2000prh.googlegroups.com...
>I have been working with databases for nearly 10 years now, but for
> the life of me I can't figure out how to move database tables from one
> server to another in SQL Server 2005 without using scripts.
> I used to use DTS in SQL Server 2000 and everything seemed to go
> smoothly. I used a combination of a Visio document (to drop and
> recreate constraints, add columns, change defaults, etc) and DTS to
> manage my whole deployment.
> Now I am trying these tasks in SQL Server 2005 and while Visio still
> can keep the structure of my tables in sync, I am having issues with
> the replacement tool for DTS.
> I tried using SSIS to move the data, and this is a description of the
> issues I am having. I can't find any posts from anybody having these
> issues, and I really don't understand why because I am getting the
> same results on 2 different installations of SQL Server 2005.
> 1. I start by going through the menu and clicking the "import data" or
> "export data" command.
> 2. I choose the source and destination database. I use the "SQL Native
> Client" driver.
> 3. I choose the "Copy data from one or more tables or views" option.
> 4. I choose my table and give it a name in the destination database.
> 5. I click the "Edit Mappings" button, and choose the option to delete
> rows in destination table, and check the box for "enable identity
> insert".
> 6. Then I choose to execute the package immediately.
> When I follow these exact steps, I get the following results:
> 1. The destination data is not removed from the table before the data
> from the source is inserted.
> 2. The data that is inserted is renumbered starting from the last
> index of the table, rather than using the original identity values.
> Can somebody please explain to me why I can't get the exact same data
> into another table in another database like I could in the previous
> version of SQL Server?
> If I have to switch to scripts to do this I will even though I much
> prefer the GUI. If someone can suggest an easy way to generate the
> scripts, it would be helpful too.
>|||On Aug 30, 12:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> I use linked server for example. However , i tried to reproduce your problem
> ( I don't have right now access to the second server) so I tried to import
> table from one database to another. When I cliked on Edit Mappings button
> a 'Delete rows in destination table' was not enabled at all. So I went for
> re-create destination table and it worked fine
> What is the version are you using?
>
I have tried it on 2 different physical servers. One of them is using
version 9.0.3054 and the other is still on 9.0.1399 because I haven't
installed the service pack yet.
I am able to reproduce these results reliably (I have never been able
to get another result than this) in both environments. I also tried
dropping the table in the destination database and using the "create
table" option as you did, but the identity columns are always
completely regenerated instead of inserted as-is.|||Hi
I ran SQL Server Profiler while I use Import data wizard and I observered
that SQL Server used TRUNCATE TABLE when you specufy delete daat from
destination , so as you know it would delete/reset an identity on the table.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188460907.491792.264000@.q3g2000prf.googlegroups.com...
> On Aug 30, 12:44 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> I use linked server for example. However , i tried to reproduce your
>> problem
>> ( I don't have right now access to the second server) so I tried to
>> import
>> table from one database to another. When I cliked on Edit Mappings
>> button
>> a 'Delete rows in destination table' was not enabled at all. So I went
>> for
>> re-create destination table and it worked fine
>> What is the version are you using?
> I have tried it on 2 different physical servers. One of them is using
> version 9.0.3054 and the other is still on 9.0.1399 because I haven't
> installed the service pack yet.
> I am able to reproduce these results reliably (I have never been able
> to get another result than this) in both environments. I also tried
> dropping the table in the destination database and using the "create
> table" option as you did, but the identity columns are always
> completely regenerated instead of inserted as-is.
>|||On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> I ran SQL Server Profiler while I use Import data wizard and I observered
> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
> destination , so as you know it would delete/reset an identity on the table.
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
>
TRUNCATE TABLE is fine. The problem is that when the new data is
inserted, it renumbers all of the identities of the records. For
example, if the first record has an identity of 2, it renumbers it to
1, the second record that was 3 is now 2, and so on. This is a major
problem since this is the colunn all of the other foreign keys use. I
have tried changing drivers, and pretty much any combination of
actions you can think of to try to get this to work with SSIS, but no
luck.|||Hi
I have not seen in Profiler statement SET INSERT_IDENTITY ON for specific
table. So It could be bug as well.
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468028.899752.220000@.x35g2000prf.googlegroups.com...
> On Aug 30, 1:33 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> I ran SQL Server Profiler while I use Import data wizard and I
>> observered
>> that SQL Server used TRUNCATE TABLE when you specufy delete daat from
>> destination , so as you know it would delete/reset an identity on the
>> table.
>> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> TRUNCATE TABLE is fine. The problem is that when the new data is
> inserted, it renumbers all of the identities of the records. For
> example, if the first record has an identity of 2, it renumbers it to
> 1, the second record that was 3 is now 2, and so on. This is a major
> problem since this is the colunn all of the other foreign keys use. I
> have tried changing drivers, and pretty much any combination of
> actions you can think of to try to get this to work with SSIS, but no
> luck.
>sql

Moving tables in SQL Server 2005 from one server to another

I have been working with databases for nearly 10 years now, but for
the life of me I can't figure out how to move database tables from one
server to another in SQL Server 2005 without using scripts.
I used to use DTS in SQL Server 2000 and everything seemed to go
smoothly. I used a combination of a Visio document (to drop and
recreate constraints, add columns, change defaults, etc) and DTS to
manage my whole deployment.
Now I am trying these tasks in SQL Server 2005 and while Visio still
can keep the structure of my tables in sync, I am having issues with
the replacement tool for DTS.
I tried using SSIS to move the data, and this is a description of the
issues I am having. I can't find any posts from anybody having these
issues, and I really don't understand why because I am getting the
same results on 2 different installations of SQL Server 2005.
1. I start by going through the menu and clicking the "import data" or
"export data" command.
2. I choose the source and destination database. I use the "SQL Native
Client" driver.
3. I choose the "Copy data from one or more tables or views" option.
4. I choose my table and give it a name in the destination database.
5. I click the "Edit Mappings" button, and choose the option to delete
rows in destination table, and check the box for "enable identity
insert".
6. Then I choose to execute the package immediately.
When I follow these exact steps, I get the following results:
1. The destination data is not removed from the table before the data
from the source is inserted.
2. The data that is inserted is renumbered starting from the last
index of the table, rather than using the original identity values.
Can somebody please explain to me why I can't get the exact same data
into another table in another database like I could in the previous
version of SQL Server?
If I have to switch to scripts to do this I will even though I much
prefer the GUI. If someone can suggest an easy way to generate the
scripts, it would be helpful too.Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
Where you using the "Copy data and objects between SQL Sever databases" in DTS?
If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458567.638491.35330@.m37g2000prh.googlegroups.com...
>I have been working with databases for nearly 10 years now, but for
> the life of me I can't figure out how to move database tables from one
> server to another in SQL Server 2005 without using scripts.
> I used to use DTS in SQL Server 2000 and everything seemed to go
> smoothly. I used a combination of a Visio document (to drop and
> recreate constraints, add columns, change defaults, etc) and DTS to
> manage my whole deployment.
> Now I am trying these tasks in SQL Server 2005 and while Visio still
> can keep the structure of my tables in sync, I am having issues with
> the replacement tool for DTS.
> I tried using SSIS to move the data, and this is a description of the
> issues I am having. I can't find any posts from anybody having these
> issues, and I really don't understand why because I am getting the
> same results on 2 different installations of SQL Server 2005.
> 1. I start by going through the menu and clicking the "import data" or
> "export data" command.
> 2. I choose the source and destination database. I use the "SQL Native
> Client" driver.
> 3. I choose the "Copy data from one or more tables or views" option.
> 4. I choose my table and give it a name in the destination database.
> 5. I click the "Edit Mappings" button, and choose the option to delete
> rows in destination table, and check the box for "enable identity
> insert".
> 6. Then I choose to execute the package immediately.
> When I follow these exact steps, I get the following results:
> 1. The destination data is not removed from the table before the data
> from the source is inserted.
> 2. The data that is inserted is renumbered starting from the last
> index of the table, rather than using the original identity values.
> Can somebody please explain to me why I can't get the exact same data
> into another table in another database like I could in the previous
> version of SQL Server?
> If I have to switch to scripts to do this I will even though I much
> prefer the GUI. If someone can suggest an easy way to generate the
> scripts, it would be helpful too.
>|||On Aug 30, 12:38 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
> Where you using the "Copy data and objects between SQL Sever databases" in DTS?
> If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
> Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks for the tip Tibor.
Yes, I used to use the 'Copy data and objects" option. Do you have a
link to the documentation that shows how to create this task in SQL
Server 2005 using Business Intelligence Development Studio? This type
of thing used to take 5 minutes to do, I hope it is the same now...|||This BOL link of probably a good start:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-00c4ea92a632.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188460612.048157.157090@.i13g2000prf.googlegroups.com...
> On Aug 30, 12:38 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Perhaps you aren't using the same functionality in SSIS as you were using in DTS?
>> Where you using the "Copy data and objects between SQL Sever databases" in DTS?
>> If so, this isn't available in the 2005 wizard. You have to create a SSIS project using "Business
>> Intelligence Development Studio" (which is Visual Studio), and you will find this task type here.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Thanks for the tip Tibor.
> Yes, I used to use the 'Copy data and objects" option. Do you have a
> link to the documentation that shows how to create this task in SQL
> Server 2005 using Business Intelligence Development Studio? This type
> of thing used to take 5 minutes to do, I hope it is the same now...
>|||On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5=a-=AD00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asp=
http://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||I'm on (May 2007) SQL Server 2005 Books Online, as in the title of the BOL window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468165.952853.123760@.l22g2000prc.googlegroups.com...
On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-­00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||I think you omit some part of it cause it works for me. Don't omit "ms-help"
or anything, it's a whole address.
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188468165.952853.123760@.l22g2000prc.googlegroups.com...
On Aug 30, 1:21 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This BOL link of probably a good start:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-ac5a-­00c4ea92a632.htm
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Thanks. What BOL version do I need? That link doesn't work for me.|||On Aug 30, 3:08 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> I think you omit some part of it cause it works for me. Don't omit "ms-he=lp"
> or anything, it's a whole address.
>
I copied and pasted the link exactly into SQL Server 2005 books
online. I also double checked I have May 2007 version. This is the
result I get:
This program cannot display the webpage
Most likely causes:
You are not connected to the Internet.
The website is encountering problems.
There might be a typing error in the address.
What you can try:
Check your Internet connection. Try visiting another website to
make sure you are connected.
Retype the address.
Go back to the previous page.
More information
---
Of course, if I am not connected to the Internet, how am I able to
post this? :) Do you have an MSDN link to the same document? Thanks.|||Strange...
Here's the Internet/MSDN URL:
http://msdn2.microsoft.com/en-us/library/ms141823.aspx
Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there? I'm
worried if there are two versions of BOL out there with different URL structures...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188470148.019177.12830@.x40g2000prg.googlegroups.com...
On Aug 30, 3:08 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> I think you omit some part of it cause it works for me. Don't omit "ms-help"
> or anything, it's a whole address.
>
I copied and pasted the link exactly into SQL Server 2005 books
online. I also double checked I have May 2007 version. This is the
result I get:
This program cannot display the webpage
Most likely causes:
You are not connected to the Internet.
The website is encountering problems.
There might be a typing error in the address.
What you can try:
Check your Internet connection. Try visiting another website to
make sure you are connected.
Retype the address.
Go back to the previous page.
More information
---
Of course, if I am not connected to the Internet, how am I able to
post this? :) Do you have an MSDN link to the same document? Thanks.|||Weird - I think it is something having to do with either the browser
or this forum, most likely related to character sets. When I copied
and pasted the link into my browser, it came up as a bad link, but
when I searched for the title of the document, I got it and it came up
with the same URL you posted. Here it is:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-
ac5a-00c4ea92a632.htm
I did notice when I copied and pasted the URL, it had a sequence of 3
dashes in a row in it, which isn't in the one you posted so it appears
that the copy on the clipboard was being converted somehow.
Anyway, I will take a look at the article to learn about this new
method of doing deployment. Thanks for the assistance with this.
I would still like to know why SSIS just doesn't even seem to function
though.
On Aug 30, 3:51 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Strange...
> Here's the Internet/MSDN URL:http://msdn2.microsoft.com/en-us/library/ms141823.aspx
> Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there? I'm
> worried if there are two versions of BOL out there with different URL structures...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>|||> I would still like to know why SSIS just doesn't even seem to function
> though.
You mean through the Wizard? Because you don't use SSIS the connect way, and the correct way isn't
exposed through the wizard...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188479488.270829.77320@.q4g2000prc.googlegroups.com...
> Weird - I think it is something having to do with either the browser
> or this forum, most likely related to character sets. When I copied
> and pasted the link into my browser, it came up as a bad link, but
> when I searched for the title of the document, I got it and it came up
> with the same URL you posted. Here it is:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/ececa4c4-1880-4d57-
> ac5a-00c4ea92a632.htm
> I did notice when I copied and pasted the URL, it had a sequence of 3
> dashes in a row in it, which isn't in the one you posted so it appears
> that the copy on the clipboard was being converted somehow.
> Anyway, I will take a look at the article to learn about this new
> method of doing deployment. Thanks for the assistance with this.
> I would still like to know why SSIS just doesn't even seem to function
> though.
>
> On Aug 30, 3:51 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Strange...
>> Here's the Internet/MSDN URL:http://msdn2.microsoft.com/en-us/library/ms141823.aspx
>> Can you navigate to the same topic in your local SQL Server BOL and post the URL you have there?
>> I'm
>> worried if there are two versions of BOL out there with different URL structures...
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>

moving tables from one server to another

Hi all,
I'm new to MSSql server and need some advice on this. I need to move a few
tables from a database in server A to another database in server B. How
should I do this?
Thanks,
TLUse DTS in SQL Server 2000
or
Use Integration Service in SQL Server 2005
Regards
Amish Shah|||TL
As an alternative you can use SELECT * INTO TableName
FROM Database.dbo.TableName WHERE 1=2
--In above script SQL Server will move only structure's table ( it has some
drawbacks (does not move constraints,keys))
WHERE 1=1 the actual data will be moved as well
"TL" <TL@.discussions.microsoft.com> wrote in message
news:8A3E6BD1-8FFE-4258-8273-BBD15781F950@.microsoft.com...
> Hi all,
> I'm new to MSSql server and need some advice on this. I need to move a few
> tables from a database in server A to another database in server B. How
> should I do this?
> Thanks,
> TL

moving tables from one server to another

Hi all,
I'm new to MSSql server and need some advice on this. I need to move a few
tables from a database in server A to another database in server B. How
should I do this?
Thanks,
TL
Use DTS in SQL Server 2000
or
Use Integration Service in SQL Server 2005
Regards
Amish Shah
|||TL
As an alternative you can use SELECT * INTO TableName
FROM Database.dbo.TableName WHERE 1=2
--In above script SQL Server will move only structure's table ( it has some
drawbacks (does not move constraints,keys))
WHERE 1=1 the actual data will be moved as well
"TL" <TL@.discussions.microsoft.com> wrote in message
news:8A3E6BD1-8FFE-4258-8273-BBD15781F950@.microsoft.com...
> Hi all,
> I'm new to MSSql server and need some advice on this. I need to move a few
> tables from a database in server A to another database in server B. How
> should I do this?
> Thanks,
> TL

moving tables from one server to another

Hi all,
I'm new to MSSql server and need some advice on this. I need to move a few
tables from a database in server A to another database in server B. How
should I do this?
Thanks,
TLUse DTS in SQL Server 2000
or
Use Integration Service in SQL Server 2005
Regards
Amish Shah|||TL
As an alternative you can use SELECT * INTO TableName
FROM Database.dbo.TableName WHERE 1=2
--In above script SQL Server will move only structure's table ( it has some
drawbacks (does not move constraints,keys))
WHERE 1=1 the actual data will be moved as well
"TL" <TL@.discussions.microsoft.com> wrote in message
news:8A3E6BD1-8FFE-4258-8273-BBD15781F950@.microsoft.com...
> Hi all,
> I'm new to MSSql server and need some advice on this. I need to move a few
> tables from a database in server A to another database in server B. How
> should I do this?
> Thanks,
> TL

Moving tables from one filegroup to other

Hi
In sql2000, what are ways to move tables from one Filegroup to other.
and what is the best way to move the tables with least of impact on system
and least of downtime.
Thanks
Mangesh
You can move a table by re-creating the clustered index on the other
filegroup using CREATE INDEX ... WITH DROP_EXISTING and specifying the new
filegroup in the ON clause. This will probably be the fastest way to move
the table, but amount of downtime will depend on how big the table is and
how fast your disks are! It may be quite slow on a large table...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:074875FA-F5EB-4312-91DD-EBB06FBE487C@.microsoft.com...
> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||Moving the clustered index using "create index ... with DROP_EXISTING on
filegroup".
AMB
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||A fairly simple way is to right click on the table and select design table.
In the table area of the pane that appears right click and select properties.
There should be a drop down that says "Table Filegroup". Use the drop-down
and select another filegroup. Re-save the table and you're done.
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh
|||Cathy,
It is simple indeed, but not optimal. Try adding a filegroup to northwind db
and adding a file to this new filegroup, then go and modify table [orders] to
move it to this new filegroup. Before saving the changes, click "save change
script" button (third from left to right in the toolbar) and you will see the
script that EM will use to move the table.
AMB
"Cathy Soloway" wrote:
[vbcol=seagreen]
> A fairly simple way is to right click on the table and select design table.
> In the table area of the pane that appears right click and select properties.
> There should be a drop down that says "Table Filegroup". Use the drop-down
> and select another filegroup. Re-save the table and you're done.
> "Mangesh Deshpande" wrote:
|||Thanks.
On similar lines is it possible to move the datafile from one filegroup to
other file
group.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Cathy,
> It is simple indeed, but not optimal. Try adding a filegroup to northwind db
> and adding a file to this new filegroup, then go and modify table [orders] to
> move it to this new filegroup. Before saving the changes, click "save change
> script" button (third from left to right in the toolbar) and you will see the
> script that EM will use to move the table.
>
> AMB
> "Cathy Soloway" wrote:
|||No. The data file potentially already have pages allocated to the objects belonging to the original
filegroup. There's no command to move those pages to other files so file becomes empty and
re-allocate the file to another file group. You need to do this manually. First shrink using
EMPTYFILE option. Then remove the file (ALTER DATABASE). Then add another file to the other
filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:57C71DB8-6291-44BE-A900-E2057DACEF07@.microsoft.com...[vbcol=seagreen]
> Thanks.
> On similar lines is it possible to move the datafile from one filegroup to
> other file
> group.
>
> "Alejandro Mesa" wrote:
|||I wrote a thorough script that allow you to move table to a new filegroup.
The script user can specify whether only data is moved, or also other indexes
and constraints.
It's a fairly long script, but a very robust one. You can find it on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql

Moving tables from one filegroup to other

Hi
In sql2000, what are ways to move tables from one Filegroup to other.
and what is the best way to move the tables with least of impact on system
and least of downtime.
Thanks
MangeshYou can move a table by re-creating the clustered index on the other
filegroup using CREATE INDEX ... WITH DROP_EXISTING and specifying the new
filegroup in the ON clause. This will probably be the fastest way to move
the table, but amount of downtime will depend on how big the table is and
how fast your disks are! It may be quite slow on a large table...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:074875FA-F5EB-4312-91DD-EBB06FBE487C@.microsoft.com...
> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh|||Moving the clustered index using "create index ... with DROP_EXISTING on
filegroup".
AMB
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh|||A fairly simple way is to right click on the table and select design table.
In the table area of the pane that appears right click and select properties
.
There should be a drop down that says "Table Filegroup". Use the drop-down
and select another filegroup. Re-save the table and you're done.
"Mangesh Deshpande" wrote:

> Hi
> In sql2000, what are ways to move tables from one Filegroup to other.
> and what is the best way to move the tables with least of impact on system
> and least of downtime.
> Thanks
> Mangesh|||Cathy,
It is simple indeed, but not optimal. Try adding a filegroup to northwind db
and adding a file to this new filegroup, then go and modify table [order
s] to
move it to this new filegroup. Before saving the changes, click "save change
script" button (third from left to right in the toolbar) and you will see th
e
script that EM will use to move the table.
AMB
"Cathy Soloway" wrote:
[vbcol=seagreen]
> A fairly simple way is to right click on the table and select design table
.
> In the table area of the pane that appears right click and select properti
es.
> There should be a drop down that says "Table Filegroup". Use the drop-do
wn
> and select another filegroup. Re-save the table and you're done.
> "Mangesh Deshpande" wrote:
>|||Thanks.
On similar lines is it possible to move the datafile from one filegroup to
other file
group.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Cathy,
> It is simple indeed, but not optimal. Try adding a filegroup to northwind
db
> and adding a file to this new filegroup, then go and modify table [ord
ers] to
> move it to this new filegroup. Before saving the changes, click "save chan
ge
> script" button (third from left to right in the toolbar) and you will see
the
> script that EM will use to move the table.
>
> AMB
> "Cathy Soloway" wrote:
>|||No. The data file potentially already have pages allocated to the objects be
longing to the original
filegroup. There's no command to move those pages to other files so file bec
omes empty and
re-allocate the file to another file group. You need to do this manually. Fi
rst shrink using
EMPTYFILE option. Then remove the file (ALTER DATABASE). Then add another fi
le to the other
filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:57C71DB8-6291-44BE-A900-E2057DACEF07@.microsoft.com...[vbcol=seagreen]
> Thanks.
> On similar lines is it possible to move the datafile from one filegroup to
> other file
> group.
>
> "Alejandro Mesa" wrote:
>|||I wrote a thorough script that allow you to move table to a new filegroup.
The script user can specify whether only data is moved, or also other indexe
s
and constraints.
It's a fairly long script, but a very robust one. You can find it on
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com