conversion tools for sql7 databases

is there any tool / utility , online service / websites that will help me convert a MSSQL 7 database to be compatible to MSSQL2k ?
i have enterprise manager for 7 , but when i try to connect to server which has sql2000 ... i cant .
thanx .You need to uninstall Client Tools for SQL 7.0 and install Client Tools for 2K.

Conventional rename mdf techniques failed not sure why

I'm trying to rename my mdf files and I have tried
1. Detach db, rename files and attach from Enterprise Manager. I direct it
to the rename MDF file and it lists the original filename then lists the
current file location with the old names and two red X's indicating a
problem. When I hit ok it comes up with error "the physical filename '['
may
be incorrect"
2. Restore database from disk with move results in error
Logical file 'mynewdbname_Data' is not part of database
'mynewdbname_v1'. Use RESTORE FILELISTONLY to list the logical file names.
Restoring filelistonly obviously just shows the old db names and phyical
file names
Any ideas what to do next ?
This sql server 2000 on winxp machines
ThanksWhen you try to attach from Enterprise Manager, change the names/paths
listed until they match the new physical files. The red X's should then go
away.
"Mike" <Mike@.mike.com> wrote in message
news:O2ibv9raGHA.3328@.TK2MSFTNGP02.phx.gbl...
> I'm trying to rename my mdf files and I have tried
>
> 1. Detach db, rename files and attach from Enterprise Manager. I direct it
> to the rename MDF file and it lists the original filename then lists the
> current file location with the old names and two red X's indicating a
> problem. When I hit ok it comes up with error "the physical filename '[
;'
> may be incorrect"
> 2. Restore database from disk with move results in error
> Logical file 'mynewdbname_Data' is not part of database
> 'mynewdbname_v1'. Use RESTORE FILELISTONLY to list the logical file names.
> Restoring filelistonly obviously just shows the old db names and phyical
> file names
> Any ideas what to do next ?
> This sql server 2000 on winxp machines
> Thanks
>|||ok cool that worked!!! It didn't work when I tried that earlier this am but
this time it worked.
Thanks !!
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uwbaegsaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> When you try to attach from Enterprise Manager, change the names/paths
> listed until they match the new physical files. The red X's should then
> go away.
> "Mike" <Mike@.mike.com> wrote in message
> news:O2ibv9raGHA.3328@.TK2MSFTNGP02.phx.gbl...
>

Conventional rename mdf techniques failed not sure why

I'm trying to rename my mdf files and I have tried
1. Detach db, rename files and attach from Enterprise Manager. I direct it
to the rename MDF file and it lists the original filename then lists the
current file location with the old names and two red X's indicating a
problem. When I hit ok it comes up with error "the physical filename '[' may
be incorrect"
2. Restore database from disk with move results in error
Logical file 'mynewdbname_Data' is not part of database
'mynewdbname_v1'. Use RESTORE FILELISTONLY to list the logical file names.
Restoring filelistonly obviously just shows the old db names and phyical
file names
Any ideas what to do next ?
This sql server 2000 on winxp machines
ThanksWhen you try to attach from Enterprise Manager, change the names/paths
listed until they match the new physical files. The red X's should then go
away.
"Mike" <Mike@.mike.com> wrote in message
news:O2ibv9raGHA.3328@.TK2MSFTNGP02.phx.gbl...
> I'm trying to rename my mdf files and I have tried
>
> 1. Detach db, rename files and attach from Enterprise Manager. I direct it
> to the rename MDF file and it lists the original filename then lists the
> current file location with the old names and two red X's indicating a
> problem. When I hit ok it comes up with error "the physical filename '['
> may be incorrect"
> 2. Restore database from disk with move results in error
> Logical file 'mynewdbname_Data' is not part of database
> 'mynewdbname_v1'. Use RESTORE FILELISTONLY to list the logical file names.
> Restoring filelistonly obviously just shows the old db names and phyical
> file names
> Any ideas what to do next ?
> This sql server 2000 on winxp machines
> Thanks
>|||ok cool that worked!!! It didn't work when I tried that earlier this am but
this time it worked.
Thanks !!
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uwbaegsaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> When you try to attach from Enterprise Manager, change the names/paths
> listed until they match the new physical files. The red X's should then
> go away.
> "Mike" <Mike@.mike.com> wrote in message
> news:O2ibv9raGHA.3328@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to rename my mdf files and I have tried
>>
>> 1. Detach db, rename files and attach from Enterprise Manager. I direct
>> it to the rename MDF file and it lists the original filename then lists
>> the current file location with the old names and two red X's indicating a
>> problem. When I hit ok it comes up with error "the physical filename '['
>> may be incorrect"
>> 2. Restore database from disk with move results in error
>> Logical file 'mynewdbname_Data' is not part of database
>> 'mynewdbname_v1'. Use RESTORE FILELISTONLY to list the logical file
>> names.
>> Restoring filelistonly obviously just shows the old db names and phyical
>> file names
>> Any ideas what to do next ?
>> This sql server 2000 on winxp machines
>> Thanks
>

Context Switches Greater 10,000

I have a server that has SQL Server 2000 Enterprise
Edition with SP3A and Windows 2000 Advanced Server as the
operating system.
This is a new system and I checked my context switches
they average 9600 Context Switches /sec over the was 3
weeks. During the busy part of the day they average
14,000 Context Switches / sec.
Should I enable the fiber-based schedule in SQL Server by
setting the lightweight pooling configuration option to 1?
Please help me with this task.
Thanks,
MikeThat's most likely not the answer. You need to determine why you get rates
such as those. It's quite possible you have other issues that youcan address
to cut this down. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:cd0301c43943$9824b0f0$a101280a@.phx.gbl...
> I have a server that has SQL Server 2000 Enterprise
> Edition with SP3A and Windows 2000 Advanced Server as the
> operating system.
> This is a new system and I checked my context switches
> they average 9600 Context Switches /sec over the was 3
> weeks. During the busy part of the day they average
> 14,000 Context Switches / sec.
> Should I enable the fiber-based schedule in SQL Server by
> setting the lightweight pooling configuration option to 1?
> Please help me with this task.
> Thanks,
> Mike
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL
|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>
|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enab
le
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data.
I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
>
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:
> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
> >
> > Isn't there a way, other then Enterprise Manager, to disable and / or enable
> > constraints, in particular primary and foreign keys? I am migrating data
> > daily from one system to SQL and to disable and enable manually is
> > inconvenient and combersome. I looked through BOL and cannot find a direct
> > answer on how to create a process to automatically disable and / or enable
> > constraints. Thanks.
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:
> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> > Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> > constraints, in particular primary and foreign keys?
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

constraints

Hiya peops
im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??
Thanksscript it out of EM and it'll give you the syntax

constraint ddl

When I see desing table option in enterprise manager of a table I don't see any constraints, but when I extract ddl I can see all 6 of them. They are all unique constraints not the check constraints. Is this normal. I am new to SQL Server and would appreciate some explanation.
ThanksCould these be unique indexes you are talking about? They are different from field level constraints.|||This is the part of the DDL generated...

ALTER TABLE [EMCSdbuser].[doc2] WITH NOCHECK ADD
CONSTRAINT [DF__doc2__archive_st__623A9EC6] DEFAULT ('UAR') FOR [archive_status_cd],
CONSTRAINT [DF__doc2__viewed_cd__632EC2FF] DEFAULT ('UNV') FOR [viewed_cd],
CONSTRAINT [DF__doc2__review_typ__6422E738] DEFAULT ('NEW') FOR [review_type_cd],
CONSTRAINT [DF__doc2__pending_st__7FAD6821] DEFAULT (0) FOR [pending_state_cd],
CONSTRAINT [DF_doc2_lastaccessdate] DEFAULT (getdate()) FOR [lastaccessdate],
CONSTRAINT [DF__doc2__Message_Si__07C4568C] DEFAULT (0) FOR [Message_Size],
CONSTRAINT [AK_DOC2_EXTERNAL_IDENTIFICATION] UNIQUE NONCLUSTERED
(
[external_identification]
) ON [FG_INDEX] ,
CONSTRAINT [AK_doc2_process_date] UNIQUE NONCLUSTERED
(
[processdate],
[status_cd],
[direction_cd],
[doc2_id]
) WITH FILLFACTOR = 80 ON [FG_INDEX] ,
CONSTRAINT [AK_doc2_processdate] UNIQUE NONCLUSTERED
(
[processdate],
[doc2_id]
) WITH FILLFACTOR = 80 ON [FG_INDEX] ,
CONSTRAINT [ak_doc2_sender_address_id] UNIQUE NONCLUSTERED
(
[sender_address_id],
[doc2_id]
) WITH FILLFACTOR = 80 ON [FG_INDEX] ,
CONSTRAINT [AK_doc2_status_cd] UNIQUE NONCLUSTERED
(
[status_cd],
[processdate],
[review_type_cd],
[doc2_id]
) WITH FILLFACTOR = 80 ON [FG_INDEX] ,
CONSTRAINT [AK_doc2_subject] UNIQUE NONCLUSTERED
(
[subject],
[doc2_id]
) WITH FILLFACTOR = 80 ON [FG_INDEX]
GO