moving and renaming a file

I have a DTS package that I currently execute manually. This DTS package
reads a text file and imports it into a table.
I would like to have it run automaticly.
For that to work correctly, it would need to check to see if the txt file is
there (the name is always teh same), if it is not there, then to quit. If
it is there, to read and import it as normal, then once completed, for it to
rename teh file and move it to another location on teh computer.
How do I do that?I'd use ActiveXScipt and the FileSystemObject. Here is some sample code:
Function Main()
dim fso, source, dest, oldFile
set fso = CreateObject("Scripting.FileSystemObject")
source = "\\myBox\FAAIVR\BENEFIT_GROUP_INFO.txt"
oldFile = replace(source, ".txt", "_" & replace(date - 7, "/","_") & ".txt")
if (fso.FileExists(source)) then
dest = replace(source, ".txt", "_" & replace(date, "/","_") & ".txt")
fso.MoveFile source, dest
end if
if (fso.FileExists(oldFile)) then
fso.DeleteFile (oldFile)
"Johnfli" wrote:
> I have a DTS package that I currently execute manually. This DTS package
> reads a text file and imports it into a table.
> I would like to have it run automaticly.
> For that to work correctly, it would need to check to see if the txt file is
> there (the name is always teh same), if it is not there, then to quit. If
> it is there, to read and import it as normal, then once completed, for it to
> rename teh file and move it to another location on teh computer.
> How do I do that?
>
>

Moving all DTS packages from server to server

Hi,
Is there any way of copying ALL DTS Packages from server to server
apart from direct copy of sysdtspackages table and saving them one by
one?
ThanksThis tool can copy all packages or individual packages, and it also has
a command line version, so you can automate copying:

http://www.sqldts.com/default.aspx?242

Simonsql

Moving a view from Table into Views category?

A bit bizarre we have a view called uvw_rev_rec_rpt_detail that is showing under tables in Enterprise Manager. It should be under views, I am over thinking this but what is the best way to move the view over?
Can I do a drop table command via TSQL or simply delete from Enterprise Manager?
Thanks
Strange. Could you run sp_help on that object and post the output here?
EXEC sp_help uvw_rev_rec_rpt_detail
GO
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jeffrey Sheldon" <jsheldon@.projecthope.org> wrote in message
news:2CF8A2CA-6A12-4C93-8235-154930A5B795@.microsoft.com...
A bit bizarre we have a view called uvw_rev_rec_rpt_detail that is showing
under tables in Enterprise Manager. It should be under views, I am over
thinking this but what is the best way to move the view over?
Can I do a drop table command via TSQL or simply delete from Enterprise
Manager?
Thanks

Moving a view from Table into Views category?

A bit bizarre we have a view called uvw_rev_rec_rpt_detail that is showing u
nder tables in Enterprise Manager. It should be under views, I am over thin
king this but what is the best way to move the view over?
Can I do a drop table command via TSQL or simply delete from Enterprise Mana
ger?
ThanksStrange. Could you run sp_help on that object and post the output here?
EXEC sp_help uvw_rev_rec_rpt_detail
GO
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jeffrey Sheldon" <jsheldon@.projecthope.org> wrote in message
news:2CF8A2CA-6A12-4C93-8235-154930A5B795@.microsoft.com...
A bit bizarre we have a view called uvw_rev_rec_rpt_detail that is showing
under tables in Enterprise Manager. It should be under views, I am over
thinking this but what is the best way to move the view over?
Can I do a drop table command via TSQL or simply delete from Enterprise
Manager?
Thanks

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks Waldo
If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>

moving a table to another filegroup in T-SQL

Hi
I want to move a table to another filegroup using T-SQL (alter table ...).
In BOL i do not find an example of this case. I trried something like
alter <table>
move to <filegroup>
but it does not work
Can somebody help me please. Thanks WaldoDoes the table currently have a clustered index?
Do you want the table to have a clustered index after you have moved it?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Waldo" <Waldo@.discussions.microsoft.com> wrote in message
news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
> Hi
> I want to move a table to another filegroup using T-SQL (alter table ...).
> In BOL i do not find an example of this case. I trried something like
> alter <table>
> move to <filegroup>
> but it does not work
> Can somebody help me please. Thanks Waldo|||If your answer to Tibors post is Yes then,
Easiest method is to re-create the clustered index in new file group. This
will move the table to the new file group automatically.
This means data will be moved to new file group once you move the clustered
Index
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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uROKsdrRHHA.2252@.TK2MSFTNGP02.phx.gbl...
> Does the table currently have a clustered index?
> Do you want the table to have a clustered index after you have moved it?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Waldo" <Waldo@.discussions.microsoft.com> wrote in message
> news:76EDDDC2-171D-42DA-843F-11B4D058BEF7@.microsoft.com...
>> Hi
>> I want to move a table to another filegroup using T-SQL (alter table
>> ...).
>> In BOL i do not find an example of this case. I trried something like
>> alter <table>
>> move to <filegroup>
>> but it does not work
>> Can somebody help me please. Thanks Waldo
>sql

Moving a logic loop to SQL Server

I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following:

WebpageId WebpageAddress Handler
1 /company/about ~/about.aspx
2 /blog ~/blog.aspx

As you can guess, one of my queries will be a SELECT command where WebpageAddress = @.address.

The hiccup I have is with a friendly URL such as the following:

/blog/2005/10/6

The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.)

What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this:

~/blog.aspx?parameters=2005/10/6

While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server?

My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.

SELECT TOP 1 Handler
FROM MyTable
WHERE WebpageAddress=SUBSTRING(@.address,1,LEN(WebpageAddress))
ORDER BY LEN(WebpageAddress) DESC

I think will give you what you want. However, to answer your question, yes, you can loop as well, but it'd be slower.

Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only on
e
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:

> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VMVM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:
> VM wrote:
> > Hi,
> >
> > We have a production database of size 40GB and growing at the rate 1.5
> > GB/month and one table is occupying about 20GB and all other tables
> > are of normal size. Till now every table is in the PRIMARY group and
> > it has only one datafile. Now we would like to split that database
> > by moving that large table out.
> >
> > I was wondering if anyone could tell me what would be the best option
> > in terms of performance of SQLServer of the following options:
> >
> > 1. Create a new Filegroup and move the table into the newfile group.
> > 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> > 3. Create a new database and move the table to the new database, and
> > create a view of the table in the first database pointing to the
> > table in the new database (Assuming the foreign key constraints are
> > taken care with in application).
> >
> > Thankyou,
> > VM
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

Moving a Large Table into New datafile vs. New database

Hi,
We have a production database of size 40GB and growing at the rate 1.5
GB/month and one table is occupying about 20GB and all other tables are of
normal size. Till now every table is in the PRIMARY group and it has only one
datafile. Now we would like to split that database by moving that large
table out.
I was wondering if anyone could tell me what would be the best option in
terms of performance of SQLServer of the following options:
1. Create a new Filegroup and move the table into the newfile group.
2. Simply add a new datafile to existing PRIMARY group and do nothing.
3. Create a new database and move the table to the new database, and create
a view of the table in the first database pointing to the table in the new
database (Assuming the foreign key constraints are taken care with in
application).
Thankyou,
VM
VM wrote:
> Hi,
> We have a production database of size 40GB and growing at the rate 1.5
> GB/month and one table is occupying about 20GB and all other tables
> are of normal size. Till now every table is in the PRIMARY group and
> it has only one datafile. Now we would like to split that database
> by moving that large table out.
> I was wondering if anyone could tell me what would be the best option
> in terms of performance of SQLServer of the following options:
> 1. Create a new Filegroup and move the table into the newfile group.
> 2. Simply add a new datafile to existing PRIMARY group and do nothing.
> 3. Create a new database and move the table to the new database, and
> create a view of the table in the first database pointing to the
> table in the new database (Assuming the foreign key constraints are
> taken care with in application).
> Thankyou,
> VM
First we would want to understand why you feel you need to move this
table out of the primary filegroup. The size is not a concern for me,
but it may be for you. Are you having backup issues? Also, if you create
a second filegroup, it would really need to be on a different array to
provide performance benefits. If the new filegroup is on the same drive
array, it will not help (backup flexibility aside).
If you do decide to move the table to another filegroup, the easiest way
to do this is to recreate the clustered index for the table on the new
filegroup (off hours). That will cause the table to move to the new
location. Non-clustered indexes will stay put, however. You can move
them as well by manually recreating them, but having the non-clustered
indexes on a separate array can help performance if the table is
accessed/updated frequently.
David Gugick
Quest Software
www.quest.com
|||Hi David,
Thankyou for the reply. We have been observing some performace degradation
of our database recently including full database backup time taken. After
brainstorming the ways we can split one large database file into multiple
physical files and we came up with below given options.
I think we might create a new filegroup (as you said on a different array)
and move some of the data. Either we would move all the indexes on to the
new group, or, Since one of our table is half of the size of total database
and is frequently inserted and updated, we may move that one table onto new
filegroup. Creation of clustered index is also a great idea.
Thanks again,
VM
"David Gugick" wrote:

> VM wrote:
> First we would want to understand why you feel you need to move this
> table out of the primary filegroup. The size is not a concern for me,
> but it may be for you. Are you having backup issues? Also, if you create
> a second filegroup, it would really need to be on a different array to
> provide performance benefits. If the new filegroup is on the same drive
> array, it will not help (backup flexibility aside).
> If you do decide to move the table to another filegroup, the easiest way
> to do this is to recreate the clustered index for the table on the new
> filegroup (off hours). That will cause the table to move to the new
> location. Non-clustered indexes will stay put, however. You can move
> them as well by manually recreating them, but having the non-clustered
> indexes on a separate array can help performance if the table is
> accessed/updated frequently.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

moving a heap to another filegroup

I have a big table (heap)... well, not so big, I have a small server
and I want to spread access to it across several new disks dedicated
only to that table.

I known its possible to do that creating a clustered index with "ON
filegroup" option but I want to maintain it as a heap, is there any
way to do this without dropping indexes/references - bulk unload -
create table - bulk load - create indexes?."el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407291227.11f42272@.posting.google.c om...
> I have a big table (heap)... well, not so big, I have a small server
> and I want to spread access to it across several new disks dedicated
> only to that table.
> I known its possible to do that creating a clustered index with "ON
> filegroup" option but I want to maintain it as a heap, is there any
> way to do this without dropping indexes/references - bulk unload -
> create table - bulk load - create indexes?.

As far as I know, you have to drop everything and recreate it, as you have
described. But in general, in MSSQL it's a good idea to have a clustered
index on all tables, so it would be interesting to know why you prefer to
maintain a heap table.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<410999b5$1_3@.news.bluewin.ch>...
> "el emperador" <1492a2001@.terra.es> wrote in message
> news:dc979468.0407291227.11f42272@.posting.google.c om...
> > I have a big table (heap)... well, not so big, I have a small server
> > and I want to spread access to it across several new disks dedicated
> > only to that table.
> > I known its possible to do that creating a clustered index with "ON
> > filegroup" option but I want to maintain it as a heap, is there any
> > way to do this without dropping indexes/references - bulk unload -
> > create table - bulk load - create indexes?.
> As far as I know, you have to drop everything and recreate it, as you have
> described. But in general, in MSSQL it's a good idea to have a clustered
> index on all tables, so it would be interesting to know why you prefer to

I agree.

> maintain a heap table.

Well, I have found that after define a clustered index some queries
changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
Lookup" to a "Clustered Index Seek") and surprisingly got a little
slower, that's suggar for the query optimizer so it thinks its better
to use it, I have to investigate why this happens before define "the
correct" clustered index, a not easy task as you know. Right now I
only want to improve "bookmark lookup" on this table that is 60%-80%
of the weigth of the queries.

> Simon

Thanks|||Hi

Adding the clustered index in the new filegroup and then dropping it will
leave the data in the new filegroup. Your other indexes will be rebuilt
twice though, so it may be quicker to drop and re-create them yourself.

John

"el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407301415.28c2c4@.posting.google.com ...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
news:<410999b5$1_3@.news.bluewin.ch>...
> > "el emperador" <1492a2001@.terra.es> wrote in message
> > news:dc979468.0407291227.11f42272@.posting.google.c om...
> > > I have a big table (heap)... well, not so big, I have a small server
> > > and I want to spread access to it across several new disks dedicated
> > > only to that table.
> > > > I known its possible to do that creating a clustered index with "ON
> > > filegroup" option but I want to maintain it as a heap, is there any
> > > way to do this without dropping indexes/references - bulk unload -
> > > create table - bulk load - create indexes?.
> > As far as I know, you have to drop everything and recreate it, as you
have
> > described. But in general, in MSSQL it's a good idea to have a clustered
> > index on all tables, so it would be interesting to know why you prefer
to
> I agree.
> > maintain a heap table.
> Well, I have found that after define a clustered index some queries
> changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
> Lookup" to a "Clustered Index Seek") and surprisingly got a little
> slower, that's suggar for the query optimizer so it thinks its better
> to use it, I have to investigate why this happens before define "the
> correct" clustered index, a not easy task as you know. Right now I
> only want to improve "bookmark lookup" on this table that is 60%-80%
> of the weigth of the queries.
> > Simon
> Thanks|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<dBPOc.12029$vm4.122553507@.news-text.cableinet.net>...
> Hi
> Adding the clustered index in the new filegroup and then dropping it will
> leave the data in the new filegroup. Your other indexes will be rebuilt
> twice though, so it may be quicker to drop and re-create them yourself.
> John

Thanks John, that's perfect.

moved stock minus in item table to stock in itemmoment table

helo all...,

i want to make procedure like:
example

i have table:
item (itemid,itemname,stock)
orderdetail(no_order,itemid,quantity)
itemmoment(itemid,itemname,stock)
item table
itemid itemname stock
c1 coconut 2
p1 peanut 2

orderdetail table
no_order itemid quantity
1 c1 5

itemmoment table
itemid itemname stock
c1 coconut 0
p1 peanut 0

when customer paid, his quantity in orderdetail decrease stock in item table..
so stock in item table became:
itemid itemname stock
c1 coconut -3
p1 peanut 2

it's not good, because stock may not minus...
so i want to move -3 to itemmoment table..
so stock in item table became:
itemid itemname stock
c1 coconut 0
p1 peanut 2

and in itemmoment table became:
itemid itemname stock
c1 coconut 3
p1 peanut 0

my store procedure like:
ALTER PROCEDURE [dbo].[orders]
(
@.no_order as integer,
@.itemid AS varchar(50),
@.quantity AS INT
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.currentStock AS INT

SET @.currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @.itemid)

UPDATE [item]
SET
[Stock] = @.currentStock - @.quantity
WHERE
[itemid] = @.itemid

COMMIT TRANSACTION
END

it's only decrease stock with quantity. i want move stock minus from item to itemmoment..
can anyone add code to my store procedure?

plss.. helpp.
thxx...

Here's how you would tackle the issue.

Declare @.AvailableQtySelect @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemIdIF @.AvailableQty >= @.QuantityBegin-- We have enough quantityUpdate ItemSet Stock = Stock - @.QuantityWhere ItemId = @.ItemIdEndElseBegin-- We have less quantityUpdate ItemSet Stock = 0Where ItemId = @.ItemIdInsert into ItemMoment (itemid,itemname, stock )Select @.ItemId, ItemName, (@.Quantity - @.AvailableQty )From ItemWhere Itemid = @.ItemIdEnd

|||

thx ndinakar...

it's one problem in ur store procedure, but i have fix it.

Select @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemId

change to

set @.AvailableQty = (select stock FROM item Where itemid = @.itemid) 

it have working...

ok, thx...

Move/Copy Table(s) Between Databases

I need to learn how to move or copy a couple of tables from one database to another. The tables are defined but contain no data.Quick and easy an INSERT INTO statement but avoid SELECT INTO. Run a search for both in the BOL(books online). Hope this helps.|||Thanks Caddre but I've learned I can use the 'Export Data' DTS Wizard which is fast and easy.

Move the contents of a table from local server to remote server(main server)

hi, I am having a problem in exporting or importind data into a table from
one server to another .Actually some time the connection breaks or the
server gives error message as primary pane full.and the process is
termineted.Can u please suggest me some method to transfer the table and its
content from one table to another in lesser time and with more accuracy.
Thanks and regard,
Irshad Ahmad.Hello Irshad,
Which method are you using to Tranfer the Data ?
Is it between SQL Servers ?
Could you post the exact error message reported by the tool ?
Thanks & Regards
Vishal|||yah i am transferring the data between sqlservers and the error generated is
"Primary File group Full" after something around 30 -40 thousand rows have
been transferred.
"Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
> Hello Irshad,
> Which method are you using to Tranfer the Data ?
> Is it between SQL Servers ?
> Could you post the exact error message reported by the tool ?
> Thanks & Regards
> Vishal
>|||Increase the size of the database file which is full (probably the mdf file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Irshad Ahmad" <efextra@.newsgroups.nospam> wrote in message
news:efNZGR9gGHA.896@.TK2MSFTNGP02.phx.gbl...
> yah i am transferring the data between sqlservers and the error generated is "Primary File group
> Full" after something around 30 -40 thousand rows have been transferred.
> "Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
> news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
>> Hello Irshad,
>> Which method are you using to Tranfer the Data ?
>> Is it between SQL Servers ?
>> Could you post the exact error message reported by the tool ?
>> Thanks & Regards
>> Vishal
>

Move the contents of a table from local server to remote server(main server)

hi, I am having a problem in exporting or importind data into a table from
one server to another .Actually some time the connection breaks or the
server gives error message as primary pane full.and the process is
termineted.Can u please suggest me some method to transfer the table and its
content from one table to another in lesser time and with more accuracy.
Thanks and regard,
Irshad Ahmad.Hello Irshad,
Which method are you using to Tranfer the Data ?
Is it between SQL Servers ?
Could you post the exact error message reported by the tool ?
Thanks & Regards
Vishal|||yah i am transferring the data between sqlservers and the error generated is
"Primary File group Full" after something around 30 -40 thousand rows have
been transferred.
"Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
> Hello Irshad,
> Which method are you using to Tranfer the Data ?
> Is it between SQL Servers ?
> Could you post the exact error message reported by the tool ?
> Thanks & Regards
> Vishal
>|||Increase the size of the database file which is full (probably the mdf file)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Irshad Ahmad" <efextra@.newsgroups.nospam> wrote in message
news:efNZGR9gGHA.896@.TK2MSFTNGP02.phx.gbl...
> yah i am transferring the data between sqlservers and the error generated
is "Primary File group
> Full" after something around 30 -40 thousand rows have been transferred.
> "Vishal Gandhi" <vishalg@.microsoft.com> wrote in message
> news:KSsMFAxgGHA.4896@.TK2MSFTNGXA01.phx.gbl...
>

Move Text Data

Does anyone have a proven way to directly move text data (text/image data
type) from a field in one table to a field in another table?
I need to update (not insert) text data in one table with data text data
that exists in another table.
I can't use local variables to process the transaction...
ThanksTake a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||I have looked at these functions... but can't make them work.
the following code gets this error: < Data stream missing from
WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext ppd_object_version.script_text @.ptr1 readtext
ppd_setup_log.script_text @.ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext ppd_object_version.script_text @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKRzmBWFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books
Online.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
data
>|||Another possibility is to use VBScript or some other language. For example,
this is pretty trivial if it is a one-time thing:
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("SELECT primary_key,text_column FROM table")
do while not rs.eof
pk = rs(0) : tc = replace(rs(1), "'", "''")
sql = "UPDATE other_table SET text_column = '" & tc & _
"' WHERE primary_key = " & pk
conn.execute sql,,129
rs.movenext
loop
rs.close: set rs = nothing: conn.close: set conn = nothing
Now, depending on the size of your table, it may take a while, so you may
have to play with commandTimeout. But that took 30 seconds to throw
together, versus who knows how long it will take to develop something using
READTEXT/WRITETEXT etc.
A
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||here's a clearer version of the code:
I have looked at these functions... but can't make them work.
the following code gets this error:
< Data stream missing from WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext table1.text_field1 @.ptr1 readtext table2.text_field2 ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext table1.text_field1 @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eKRzmBWFGH
A.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online
.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:
> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do with
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
> >I have table with several million rows, which I would like to move to
> > partition schema.
> >
> > I've found this option is present in ALTER TABLE command. However, this
> > command
> >
> > alter table TaleName move to ps_Schema ( PartitionColumnName)
> >
> > resulted in error:
> >
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near 'move'.
> >
> > I assume the syntax is wrong, but how to accomplish this task ? What is
> > the
> > correct syntax?
> >
> > Thank you very much.
> >
>
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:

> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do wit
h
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>
>

Move Table to other filegroup?

Hi every body,
Is there anyone know the way to move a table with data to other file group?Use 'Alter Database' statement. You may have to do the 'Alter' to add new filegroups, then do the moving tables and cleaning up old filegroups. To remove it, of course, it's another 'Alter'. Remember to remove a filegroup the file has to be empty. Good luck!|||I believe that you will have to:

1. Add the new file group
2. Create an identical table on the new file group with a different name
3. Copy the data from the existing table to the new table
4. Drop the old table
5. Rename the new table

If you want to retain indexes, constraints, foreign keys and defaults with the same names, then you will have to re-create these after dropping the current ones.

Regards,

hmscott|||Actually, you can virtually move the data from the table to a different filegroup by creating a clustered index and placing it wherever you want.