custom transformation and broken FK

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

Custom Assembly config file and named instances of Reporting Services

We have a custom assembly that is using a config file. We park the config
file in the instance folder of RS along with the dll. We look in the
registry to find the instance. When there are multiple instances of RS I am
not identifying the correct instance to locate the correct custom assembly
config file. I think what I need to do is use Reflection or Process
libraries to identify the running instance, but haven't been able to figure
this out yet. Anyone using config files for their custom assemblies? We
are using a resource file and could place the config information in there
but that seems wrong. I could use the RS web.config...maybe but don't know
how to do that yet and what the security issues will be.
Thanks for helping,
Steve MunLeeuwI could do two things that I can think of.
1. bad: Pass in the ReportServerURL and loop through the RS instances to
find the one the custom assembly is loaded in and use that to find the
config file.
2. good: Just add the settings to the RS web.config instance. I still need
to test if this will work when running reports from rs.exe utility.
Add the following to web.config:
<appSettings>
<add key="connString" value="connection string" />
</appSettings>
Add the following to custom assembly:
using System.Configuration;
ConfigurationSettings.AppSettings["connString"]
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:e$ys4gs9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> We have a custom assembly that is using a config file. We park the config
> file in the instance folder of RS along with the dll. We look in the
> registry to find the instance. When there are multiple instances of RS I
> am not identifying the correct instance to locate the correct custom
> assembly config file. I think what I need to do is use Reflection or
> Process libraries to identify the running instance, but haven't been able
> to figure this out yet. Anyone using config files for their custom
> assemblies? We are using a resource file and could place the config
> information in there but that seems wrong. I could use the RS
> web.config...maybe but don't know how to do that yet and what the security
> issues will be.
> Thanks for helping,
> Steve MunLeeuw
>|||Ooops, used deprecated method. Here's the updated code:
http://smunson.blogspot.com/
Steve MunLeeuw
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:%23QsUD%23t9GHA.4408@.TK2MSFTNGP02.phx.gbl...
>I could do two things that I can think of.
> 1. bad: Pass in the ReportServerURL and loop through the RS instances to
> find the one the custom assembly is loaded in and use that to find the
> config file.
> 2. good: Just add the settings to the RS web.config instance. I still
> need to test if this will work when running reports from rs.exe utility.
> Add the following to web.config:
> <appSettings>
> <add key="connString" value="connection string" />
> </appSettings>
> Add the following to custom assembly:
> using System.Configuration;
> ConfigurationSettings.AppSettings["connString"]
>
> Steve MunLeeuw
>
> "Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
> news:e$ys4gs9GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> We have a custom assembly that is using a config file. We park the
>> config file in the instance folder of RS along with the dll. We look in
>> the registry to find the instance. When there are multiple instances of
>> RS I am not identifying the correct instance to locate the correct custom
>> assembly config file. I think what I need to do is use Reflection or
>> Process libraries to identify the running instance, but haven't been able
>> to figure this out yet. Anyone using config files for their custom
>> assemblies? We are using a resource file and could place the config
>> information in there but that seems wrong. I could use the RS
>> web.config...maybe but don't know how to do that yet and what the
>> security issues will be.
>> Thanks for helping,
>> Steve MunLeeuw
>