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

Moved SQL DB to SQL2005 and it is slow

Hello,
I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
The same DB on a slower machine running 2000 runs mutch faster.
For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
seconds, 20 times slower.
I reindexed and that did not help.
Any Ideas?
Why the same DB with same indexes behaving that way, what Am I missing'
This is the release version of sql2005, Stardard edition, RTM...
Thanks
SAAre you accessing the database from a .Net 1.1 application using the standar
d
SqlConnection / related classes? I found that my .NET 1.1 apps would not
connect to a SQL Server 2005 instance using a shared memory connection.
Recompiling the same code with the .NET 2.0 framework resolved the issue -
the application again connected using shared memory and was sigificantly
faster. I don't know how to go about determining what mode (tcp / names
pipes / shared memory) a given connection is using, but I'm sure a quick
search will answer that.
Ross
"MSDN" wrote:

> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I missing'
'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>
>|||Have you run UPDATE TATISTICS with FULLSCAN option?
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>|||Sorry
UPDATE STATISTICS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMQ9gf3NGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Have you run UPDATE TATISTICS with FULLSCAN option?
>
>
> "MSDN" <sql_agentman@.hotmail.com> wrote in message
> news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
>|||MSDN (sql_agentman@.hotmail.com) writes:
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
As Uri pointed you must run UPDATE STATISTICS WITH FULLSCAN on all your
tables. The statistics from SQL 2000 are invalidated when you upgrade.
There may be more to it than that, but start there.
If you need further assistence, please be more specific of what is slow.
Is it certain queries, or is it slower overall? If you run queries from
Query Analyzer, is there still any differences (to rule out connection
issues as suggested in Ross's post). If you run from the local machine
(to exclude network issues)?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perhaps it's not the database, but configuration of the SQL Server
installation. Also, if this database is running on a new server box, it may
be related hardware or OS configuration.
The following was copied from the MSDN article titled: Checklist: SQL Server
Performance
Use default server configuration settings for most applications.
Locate logs and the tempdb database on separate devices from the data.
Provide separate devices for heavily accessed tables and indexes.
Use the correct RAID configuration.
Use multiple disk controllers.
Pre-grow databases and logs to avoid automatic growth and fragmentation
performance impact.
Maximize available memory.
Manage index fragmentation.
Keep database administrator tasks in mind.
http://msdn.microsoft.com/SQL/2000/...enetcheck08.asp
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>sql

Move SQL 7.0 to SQL 7.0

Hi,
moving all databases, users, logins DTS-jobs and so on from Server A to
(new) Server B.
What is the most efficient way of doing this? One example I have seen is to
copy all data and transaction-files for all databases.
Another way could be to perform Backup on Server A, and restore on Server B
(including msdb, master and so on) .
Any suggestions? Is there a way to make it simple ?
/BJI have not tryed it but try it yourself.
Install SQL Server on target machine in same path (and Service Packs).
Stop it.
Copy the SQL Server directory from source directory to target directory.
Copy all databases from source to destination keeping same location. If
databases need to be moved - you can detach it (from all tasks), move files
and attach them again.
Restart. Have fun
HTH
Bojidar Alexandrov|||I have also seen the "Server Upgrade Wizard".
Can this one be used for moving SQL7 to SQL7?
/BJ
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:ukHpDi1BEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have not tryed it but try it yourself.
> Install SQL Server on target machine in same path (and Service Packs).
> Stop it.
> Copy the SQL Server directory from source directory to target directory.
> Copy all databases from source to destination keeping same location. If
> databases need to be moved - you can detach it (from all tasks), move
files
> and attach them again.
> Restart. Have fun
> HTH
> Bojidar Alexandrov
>|||> I have also seen the "Server Upgrade Wizard".
> Can this one be used for moving SQL7 to SQL7?
>
no|||OK,
you said that you have not tried this way, but what should your way be to
make it?
/BJ
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message
news:OFpoAB2BEHA.2404@.TK2MSFTNGP11.phx.gbl...
> no
>|||As far as I know SQL Server this must works.
Try it on test machine and see for yourself - I personally do not have time
now|||here a article
http://www.databasejournal.com/feat...cle.php/1479841

MS Reporting Services and Passing Credentials from .NET App

Expert Needed,
Is it possible to pass credentials from a .NET application into MS reporting
services?
For example we want to filter our data for our reports based on the user's
login into the .NET app.
Thank you in advance.
RichI am trying to do the very same thing from a .Net windows application and if
get it will post my results.
Dan
"Rich" <rwatson@.brick.net> wrote in message
news:eNq3unf9FHA.3664@.TK2MSFTNGP10.phx.gbl...
> Expert Needed,
> Is it possible to pass credentials from a .NET application into MS
> reporting
> services?
> For example we want to filter our data for our reports based on the user's
> login into the .NET app.
> Thank you in advance.
> Rich
>|||The recommended approach will be the use RS custom security if you need to
pass the user credentials. Alternatively, once the application
authenticates, it can use a single "trusted" Wi ndows account to connect and
authenticates against RS.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Rich" <rwatson@.brick.net> wrote in message
news:eNq3unf9FHA.3664@.TK2MSFTNGP10.phx.gbl...
> Expert Needed,
> Is it possible to pass credentials from a .NET application into MS
> reporting
> services?
> For example we want to filter our data for our reports based on the user's
> login into the .NET app.
> Thank you in advance.
> Rich
>|||Is possible to pass credentials, you need to include in your .NET
application a reference to Web Services that is installed on Server that had
Reporting Services. Then, you can use all the method that this web services
expose, as
ReportingService rs = new ReportingService();
System.Net.NetworkCredential cr = new System.Net.NetworkCredential(UserName,
Password, Domain);
rs.Credentials = cr;
I hope that this help you.
"Rich" <rwatson@.brick.net> wrote in message
news:eNq3unf9FHA.3664@.TK2MSFTNGP10.phx.gbl...
> Expert Needed,
> Is it possible to pass credentials from a .NET application into MS
reporting
> services?
> For example we want to filter our data for our reports based on the user's
> login into the .NET app.
> Thank you in advance.
> Rich
>