Hi,
I need to do the following through Enterprise Manager (EM).
I need to copy the structure of all the tables along with their PK, FK
etc and need to copy the data of only select set of tables (not all of
them). I tried doing this in EM but lost the relationship between
tables in the target database.
Here is the example:
TablePK has an indexed column. TableFK has a column which is the
Foreign key to TablePK.
- I create ine DTS package1 in which I transfer both tables as objects
along with their PK & FK, and without data.
- I ceate a second package2 in which TablePK is only transferred, this
time with Data.
- At the end I notice that the relationship between TablePK and TableFK
is broken in the target database.
Does anyone have a clue how do I go about this problem. I am a newbie.
TIA.
You could use the copy objects task in DTS seeing as this is one of the NGs
from which you have asked for a reply.
You could try using SQLDMO to script all the details you require from the
tables you want as well.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||try DB Ghost http://www.dbghost.com you can copy over schema and select those
tables where you want to copy data and it takes care to propagate objects and
data in the correct order so relationships aren't broken.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"aamirghanchi@.yahoo.com" wrote:
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||When I copy all tables as you have done, I script it using SEM...
Select your source database -> All Tasks-> Generate SQL Script... One of the
last tabs gives you the option of taking FK relationships and indexes etc.
It is NOT checked by default..
Make sure you select it and then run the script in QA to create the tables
in the new DB..
For a small number of tables I usually select INTO to move the data, For a
larger number of tables I use DTS...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
sql
Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts
Thursday, March 22, 2012
custom system proc
Hello, I would like to create a stored procedure, which would manipulate dat
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
a
in different databases, where all my databases have the same structure.
I would expect , when this proc is called in DB1 to use tables from DB1,
when called in DB2 .. tables from DB2 .. and so on.
However, when I created my sample stored proc in master..
create proc sp_sample as select * from table1
and then I tried to call this proc in DB1.. even that DB1 had also table1 ,
stored procedure was selecting data from master database. I was expecting it
to use DB1..table1.
I hope I am clear on what I am going to achieve, I would like to have one
copy for each of my stored procedures, stored in master database instead of
10' copies stored across all different databases.
I would appreciate any tips on how this could be done.
LucjanThere's no supported or documented way to achieve what you want to do. Sugge
sted method is to have
the same proc in all databases and use some sw to manage versions. You *can*
achieve what you want
by marking the proc as a system proc using sp_MS_marksystemobject (Google fo
r usage), but again, it
is not supported or documented.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate d
ata
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
,
> stored procedure was selecting data from master database. I was expecting
it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||The supported behavior of special procedures (sp_ in master) is to resolve
only system tables in the current database, but user tables in master.
There's an undocumented "feature" that causes the proc to resolve user
tables in the current database as well. To achieve this, you run:
EXEC dbo.sp_MS_marksystemobject 'dbo.sp_procname'
Tough I'd be careful from relying on such undocumented behavior in
production systems. You never know when support for such a "feature" will be
dropped.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Lucjan" <Lucjan@.discussions.microsoft.com> wrote in message
news:BB44926A-3624-4F56-B47C-299338E2F222@.microsoft.com...
> Hello, I would like to create a stored procedure, which would manipulate
> data
> in different databases, where all my databases have the same structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also table1
> ,
> stored procedure was selecting data from master database. I was expecting
> it
> to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have one
> copy for each of my stored procedures, stored in master database instead
> of
> 10' copies stored across all different databases.
> I would appreciate any tips on how this could be done.
> Lucjan
>|||Lucjan (Lucjan@.discussions.microsoft.com) writes:
> Hello, I would like to create a stored procedure, which would manipulate
> data in different databases, where all my databases have the same
> structure.
> I would expect , when this proc is called in DB1 to use tables from DB1,
> when called in DB2 .. tables from DB2 .. and so on.
> However, when I created my sample stored proc in master..
> create proc sp_sample as select * from table1
> and then I tried to call this proc in DB1.. even that DB1 had also
> table1 , stored procedure was selecting data from master database. I was
> expecting it to use DB1..table1.
> I hope I am clear on what I am going to achieve, I would like to have
> one copy for each of my stored procedures, stored in master database
> instead of 10' copies stored across all different databases.
You can do this in SQL 2000, but it is not supported. And I don't think
you can do it at all in SQL 2005, since there is a radical change how
stored procedures, system tables etc are stored in SQL 2005.
Keep your code under version control and write a script that can update
one or more databases. The script can be Perl, VB, VBscript or even a
BAT file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, March 11, 2012
Custom object or DataSet ?
Hello
I'm using SQL 2000 server and I have question about logining to server. Is it possible to include Active Directory structure? I know I can define SQL user or use Windows Based Account, but I havent read anywhere how to integrate Active Directory. If there is solution or if anybody has experience with this problem, I would be grateful forhelp or reference (btw, I'm using win 2003 server).
Thanks
MatjazCheck this link (http://search.win2000mag.net/query.html) and corresponding links.
I'm using SQL 2000 server and I have question about logining to server. Is it possible to include Active Directory structure? I know I can define SQL user or use Windows Based Account, but I havent read anywhere how to integrate Active Directory. If there is solution or if anybody has experience with this problem, I would be grateful forhelp or reference (btw, I'm using win 2003 server).
Thanks
MatjazCheck this link (http://search.win2000mag.net/query.html) and corresponding links.
Subscribe to:
Posts (Atom)