Convert ADP to MDB

My company uses Microsoft ADP to manage and display data. I am the
Business Analyst for the division, my manager has asked me to import
the ADP and convert it to MDB, so that we can modify queries to
generate more reports.
To give a little background on the issue, we have a "Form" that
performs a search in the "Tables" using the queries, and outputs that
results into and calendar format in excel. All this has been hard-coded
into the VB code. Basically, the "Form" runs the code, which in turn
runs a "Module".
Some of the queries were encrypted so I had to go in and make sense
out of it, and build similar queries in MDB. I have imported all the
necessary tables, queries, modules, macros, and forms. Since it was
custom built, I am having difficulty debugging the errors... Is there
any basic stuff I should look at before I start troubleshooting'
Please help!!You'd probably want to post this to an Access forum.
-oj
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||I am bit confused.
ADP is merely a front-end app of SQL Server/MSDE and does not contain
tables, queries. It only has forms, reports, macros/vba code modules. If you
want to modify/create queries in ADP (they are actually Views, SPs, UDF in a
SQL Server database), you are actually directly working in a SQL Server
database.
If your goal is to switch the front end app from ADP to an mdb with linked
table (to the SQL Server back end), because of not cleared future of ADP,
then converting ADP to MDB may make some sense, otherwise, I do not see why
to do it.
Also, the way ADP connects to SQL Server (bound controls, especially) is a
bit different from the way mbd linked table. And it may also be quite
different how the server side queries (views, sps...) are used. IMO, one
must be good at both Access programming and SQL Server programming in order
to fulfill the converting, if it has to be converted.
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||Thanks for your reponse. I am not an expert on VB and SQL programming.
So I'm taking a different approach. To keep things simple, I have three
tables (in MDB), so I created a query that draws required information
from the tables and puts the data in a calendar format in 'Access
Report' (I made a calendar for July, with Text Boxes for each day of
the month).
Problem: The calendar displays only one record per page, it was
supposed to display all the records corresponding to the days of the
month in one page. Also, is there a way to display more than one record
in the 'Text box'?, as of now it only displays one record in each 'text
box' in each page.
Please help!!!!!!!!!!|||jyesbrown@.gmail.com wrote:
> Thanks for your reponse. I am not an expert on VB and SQL programming.
> So I'm taking a different approach. To keep things simple, I have three
> tables (in MDB), so I created a query that draws required information
> from the tables and puts the data in a calendar format in 'Access
> Report' (I made a calendar for July, with Text Boxes for each day of
> the month).
> Problem: The calendar displays only one record per page, it was
> supposed to display all the records corresponding to the days of the
> month in one page. Also, is there a way to display more than one record
> in the 'Text box'?, as of now it only displays one record in each 'text
> box' in each page.
> Please help!!!!!!!!!!
>
I think you need to seek help in one of the Access groups, these sound
like questions specific to Access, not SQL Server. It also sounds like
you're in over your head, why did you agree to do something like this
without having a clue how?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Convert ADP to MDB

My company uses Microsoft ADP to manage and display data. I am the
Business Analyst for the division, my manager has asked me to import
the ADP and convert it to MDB, so that we can modify queries to
generate more reports.
To give a little background on the issue, we have a "Form" that
performs a search in the "Tables" using the queries, and outputs that
results into and calendar format in excel. All this has been hard-coded
into the VB code. Basically, the "Form" runs the code, which in turn
runs a "Module".
Some of the queries were encrypted so I had to go in and make sense
out of it, and build similar queries in MDB. I have imported all the
necessary tables, queries, modules, macros, and forms. Since it was
custom built, I am having difficulty debugging the errors... Is there
any basic stuff I should look at before I start troubleshooting'
Please help!!You'd probably want to post this to an Access forum.
-oj
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||I am bit confused.
ADP is merely a front-end app of SQL Server/MSDE and does not contain
tables, queries. It only has forms, reports, macros/vba code modules. If you
want to modify/create queries in ADP (they are actually Views, SPs, UDF in a
SQL Server database), you are actually directly working in a SQL Server
database.
If your goal is to switch the front end app from ADP to an mdb with linked
table (to the SQL Server back end), because of not cleared future of ADP,
then converting ADP to MDB may make some sense, otherwise, I do not see why
to do it.
Also, the way ADP connects to SQL Server (bound controls, especially) is a
bit different from the way mbd linked table. And it may also be quite
different how the server side queries (views, sps...) are used. IMO, one
must be good at both Access programming and SQL Server programming in order
to fulfill the converting, if it has to be converted.
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||Thanks for your reponse. I am not an expert on VB and SQL programming.
So I'm taking a different approach. To keep things simple, I have three
tables (in MDB), so I created a query that draws required information
from the tables and puts the data in a calendar format in 'Access
Report' (I made a calendar for July, with Text Boxes for each day of
the month).
Problem: The calendar displays only one record per page, it was
supposed to display all the records corresponding to the days of the
month in one page. Also, is there a way to display more than one record
in the 'Text box'?, as of now it only displays one record in each 'text
box' in each page.
Please help!!!!!!!!!!|||jyesbrown@.gmail.com wrote:
> Thanks for your reponse. I am not an expert on VB and SQL programming.
> So I'm taking a different approach. To keep things simple, I have three
> tables (in MDB), so I created a query that draws required information
> from the tables and puts the data in a calendar format in 'Access
> Report' (I made a calendar for July, with Text Boxes for each day of
> the month).
> Problem: The calendar displays only one record per page, it was
> supposed to display all the records corresponding to the days of the
> month in one page. Also, is there a way to display more than one record
> in the 'Text box'?, as of now it only displays one record in each 'text
> box' in each page.
> Please help!!!!!!!!!!
>
I think you need to seek help in one of the Access groups, these sound
like questions specific to Access, not SQL Server. It also sounds like
you're in over your head, why did you agree to do something like this
without having a clue how?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsqlsql

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
>

Content Manager Security

I have a user I have given permission to folder and reports as a
Content Manager, but when the user clicks on the security tab for a
folder or report they get an error of:
The permissions granted to user 'DOMAIN\USER_ID' are insufficient for
performing this operation. (rsAccessDenied)
I am an administrator on the machine and fall into the
BUILTIN\Administrators who are Content Managers, and have no problems
whatsoever in viewing and setting security.
What can I do to get this user permission short of making them an
administrator on the machine? Any help would be greatly appreciated.jdcamp,
Did you try wiggling the power cord on the server? Another one that
works for me is to open and close the CD-ROM drive 5 or 6 times then
restart the machine.
Hope I could help!
Mickey|||Are you kidding me? Does anyone have a serious answer on this matter?

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