Database Import/Export Question

I tried to export a database which includes tables and stored procedures from development environment to a production environment. For unknown reasons only the tables were copied whereas stored procedures were not copied and no error message shown what happened. Does anybody have a clue? I did the same export to an XP machine and it went through properly.How did you export the data? Did you use DTS?
Maybe you will find my previous post listing the steps I use to export objects to be helpful:http://forums.asp.net/1037418/showpost.aspx
|||Thanks. I got it. Don't know why the import/export said 100% complete but nothing showed up even I refreshed the stroed procedures in Enterprise Manager. And after a while (actually I went to pull someone to see the situation) the SP shown. Weird?|||

vljw8202 wrote:

Thanks. I got it. Don't know why theimport/export said 100% complete but nothing showed up even I refreshedthe stroed procedures in Enterprise Manager. And after a while(actually I went to pull someone to see the situation) the SPshown. Weird?

Enterprise Manager is often annoyingly unaware of schema changes. What I typically do is right-click on the server name and chooseRefresh, do the same for the Database node, the specific database, andthen the Tables node and any other node which should berefreshed. The new objects will then show up.
|||I see. Thanks for your advice.

Database deign problem

Hello all,
I have a datbase design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryr
Hi,
Typically, if I want to design a hierarchy that has more than 2 levels, I
use a parent-child relationship like that:
[OBJECT]
OBJECT_ID int auto
OBJECT_CODE nvarchar(50)
OBJECT_NAME nvarchar(255)
PARENT_OBJECT_ID int
[OBJECT_PERMISSION]
OBJECT_ID int
USER_ID int
Tomasz B.
"Cymryr" wrote:

> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>
>
|||parent child have too many problems :
1/Must implement recursivity (bad performance)
2/hard to know the level of the member
3/ impossible to have different columns at each level
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...[vbcol=seagreen]
> Hi,
> Typically, if I want to design a hierarchy that has more than 2 levels, I
> use a parent-child relationship like that:
> [OBJECT]
> OBJECT_ID int auto
> OBJECT_CODE nvarchar(50)
> OBJECT_NAME nvarchar(255)
> PARENT_OBJECT_ID int
> [OBJECT_PERMISSION]
> OBJECT_ID int
> USER_ID int
> Tomasz B.
> "Cymryr" wrote:
levels[vbcol=seagreen]
|||Hi,
Of course, you have right, but please remember that, this is a dimension
table and usually it contains much less records than a fact table, so
performance is not an issue – SQL Server can load whole table into memory,
also you should expect minimal insert, update and delete activities.
If you really new a level number, you can define a column called LEVEL and
maintenance this information by trigger
If you want different columns at each level, you can define a view.
Tomasz B.
"Cymryr" wrote:

> parent child have too many problems :
> 1/Must implement recursivity (bad performance)
> 2/hard to know the level of the member
> 3/ impossible to have different columns at each level
> "Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
> message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...
> levels
>
>
|||Rather than a seperate table for each table, consider one self-referencing
table.
"Cymryr" <Cymryr@.hotmail.com> wrote in message
news:evfi5ucDFHA.3888@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>

Database deign problem

Hello all,
I have a datbase design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrHi,
Typically, if I want to design a hierarchy that has more than 2 levels, I
use a parent-child relationship like that:
[OBJECT]
OBJECT_ID int auto
OBJECT_CODE nvarchar(50)
OBJECT_NAME nvarchar(255)
PARENT_OBJECT_ID int
[OBJECT_PERMISSION]
OBJECT_ID int
USER_ID int
Tomasz B.
"Cymryr" wrote:

> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>
>|||parent child have too many problems :
1/Must implement recursivity (bad performance)
2/hard to know the level of the member
3/ impossible to have different columns at each level
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...[vbcol=seagreen]
> Hi,
> Typically, if I want to design a hierarchy that has more than 2 levels, I
> use a parent-child relationship like that:
> [OBJECT]
> OBJECT_ID int auto
> OBJECT_CODE nvarchar(50)
> OBJECT_NAME nvarchar(255)
> PARENT_OBJECT_ID int
> [OBJECT_PERMISSION]
> OBJECT_ID int
> USER_ID int
> Tomasz B.
> "Cymryr" wrote:
>
levels[vbcol=seagreen]|||Hi,
Of course, you have right, but please remember that, this is a dimension
table and usually it contains much less records than a fact table, so
performance is not an issue – SQL Server can load whole table into memory,
also you should expect minimal insert, update and delete activities.
If you really new a level number, you can define a column called LEVEL and
maintenance this information by trigger
If you want different columns at each level, you can define a view.
Tomasz B.
"Cymryr" wrote:

> parent child have too many problems :
> 1/Must implement recursivity (bad performance)
> 2/hard to know the level of the member
> 3/ impossible to have different columns at each level
> "Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
> message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...
> levels
>
>|||Rather than a seperate table for each table, consider one self-referencing
table.
"Cymryr" <Cymryr@.hotmail.com> wrote in message
news:evfi5ucDFHA.3888@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>