Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run th
e
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want t
o
script the objects using the scripting options in Enterprise Manager or usin
g
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx o
r
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:

> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run
the
> exact same actions on the live server when the time comes.

Combining two database - best way to transfer objects and data

Hi,
I have two databases that I need to combine into one.
What is the best way to transfer all objects, data, constraints,
relationships, stored procedures, triggers, etc., from one to the other?
I'd like to set it up in a way where I can first run it on a development
environment where I can make all the necessary code changes, and then run the
exact same actions on the live server when the time comes.Hi
It sounds like you are not using a version control system to stored your
code? If so you would be able to extract the ddl from your version control
system and create the tables/views/stored procedures... in the new database
and it would only need a method to transfer the data. Instead you may want to
script the objects using the scripting options in Enterprise Manager or using
DMO, make the changes and then load the data (say using BCP or DTS). You
could also use tools such as DBGhost http://www.innovartis.co.uk/home.aspx or
Red Gate's SQL compare http://www.red-gate.com/ to create your scripts.
Before you start you may want to make sure that the structure of your
development and live systems are the same (say using the above tools)
John
"Gal Steinitz" wrote:
> Hi,
> I have two databases that I need to combine into one.
> What is the best way to transfer all objects, data, constraints,
> relationships, stored procedures, triggers, etc., from one to the other?
> I'd like to set it up in a way where I can first run it on a development
> environment where I can make all the necessary code changes, and then run the
> exact same actions on the live server when the time comes.sqlsql

COM objects under 64-bit?

Hi everyone,

Primary platform is Framework 2.0 under 32-bit

Production platform will be Framework 2.0 under 64-bit.

I'm concerned about what object should I use in order to launch DTS 2000/7.0 from our .Net service.

Currently it tested fine from a 32-bit environment (sql server 2005 as backend).

Reference: "Microsoft DTSPackage Object Library"

Physical file: dtspkg.dll

My wonder, is there any limitation when it's gonna to work on 64-bit?

Generally speaking, any limitation/issue/drawback for COM objects under 64 bit?

In my project properties appears Any CPU, x86, x64. I've got now Any CPU.

Thanks for your advices,

Well, anyway, in any case. I assume that it'd provoke lots of pitfalls, drawbacks and so on.

So then, could I use assemblies in order to launch DTS 2000?

(Microsoft.SqlServer.Dts.Runtime.Application)

Thanks again,

|||Do you mean Microsoft.SqlServer.Dts.Runtime namespace from SQL Server 2005? If yes, it can't run DTS 2000 packages, it can only run SSIS packages.

The DTS 2000 only exists in 32-bit version, you can use it on 64-bit OS from 32-bit processes run in WOW64.|||

Hi Michael,

Thanks a lot for your information.

So that, could I deduce that such COM component will work fine on our Window Server 2003 Enterprise x64 Edition?

Thanks again.

|||

hi,

is it so?

|||They will work on x64, but your application has to be 32-bit.

So instead of Any CPU, compile it for x86 - it will then run in WOW64 and be able to access DTS COM objects (provided they are installed of course).|||

No, my project got a "ANYCPU". Which is the problem?

Upload dtspkg.dll to the cluster. Or anything else?

|||If you compile for AnyCPU, the executable will run as 64-bit executable on x64 machine. You can only access 32-bit DLLs from 32-bit processes, so you would not have access to DTS objects if executable is compiled for AnyCPU. Your executable has to be compiled for x86 - then it will run in WOW64 on x64 machine (i.e. as 32-bit process, with access to 32-bit COM objects).
I think it is more than one DLL, see redistributable docs in SQL 2000 docs for list of files that are might and should be copied, and proper installation instructions.|||

Thanks for that. I'll keep on mind.

So that, leaving as 'Any CPU' it'd caused a pitfall..

But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...

What a pity!! What could I do in order to fix that?

AFAIK is just a dtspkg.dll, at least when I run my project from VS. VS take dtspkg.dll and it's created (suppose) a RCW called

as "Interop.DTS.dll"

Thanks again.

|||

enric vives wrote:

But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...

What a pity!! What could I do in order to fix that?

Move to SSIS of course!

|||

Ok, Michael, I'm totally agree with you, but listen to me:

We've got around 500 dts "live" up and just ten SSIS running on-production in order to cover our business as usual. As time goes by dts will be transformed into .DTSX, of course!

Currently have two services: one which throws exclusively dts 2000 and the new one which is that we're discussing at (both ssis and dts 2000)

But, thanks indeed.

|||

DTS comming from SQL Server 2000, never mind 7.0, has never been supported on 64-bit. The initial engine support for 64-bit (Liberty?) only came along after the SQL Server 2000 had been relased, and clearly decsion was made that only certain parts of the product would be ported. Forward compatability on hardware is rather a challenge I'd imagine, and going back to add new hardware support for old components like DTS would clearly have impacted the delivery of SQL 2005. MS have been clear about this strategy, so that seems fair to me, you cannot expect old products to be constantly updated to keep pace with new hardware or operating systems. I like my Windows 2003 64-bit over NT4 thanks.

Also think about what adding 64-bit support means to something like a data engine, you need to do some fairly major work around memory managent to allow it to scale over all that nice memory 64-bit gives us. They did that work, and found there was no realistic way forward to make the orginal code base scale, so they wrote the SSIS pipeline. You will often hear variations on that point when speaking to team members at conferences and the like.

Every few genartions in software refresh cycles you get a step change like this. The best thing that has happend is that DTS is still there and supported, so DTS just keeps on working. If your DTS solutions no longer scale, then it seems fair to accept that some re-work will be required, although I would agree that this step change is quite significant, but then so are the benefits and that seems like the trade off.

|||

hi Darren,

No, no I'm not complaining about Microsoft's philosophy or regarding ssis at all. I'm complaining about tight dates and about the fact that I'll have to run my service as 32 bit only for keep in mind old ETL.

SSIS is a very good tool!

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
I just answered my own question about 5 minutes after posting. The sp_OAxxx stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?
|||Look up OLE Automation in BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?
|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored procedure and call methods, etc, but now I can't remember how I did it. Can someone point me in the right direction?Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison|||Look up OLE Automation in BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?

COM Objects in Stored Procedures?

I seem to recall at one point I was able to create a COM object in a stored
procedure and call methods, etc, but now I can't remember how I did it. Can
someone point me in the right direction?I just answered my own question about 5 minutes after posting. The sp_OAxxx
stored procedures provide an interface to automation objects.
"Ken" wrote:

> I seem to recall at one point I was able to create a COM object in a stored proced
ure and call methods, etc, but now I can't remember how I did it. Can someone point
me in the right direction?|||Look up OLE Automation in BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:A196B75F-B712-4945-8AA8-2E26DA9AF38A@.microsoft.com...
> I seem to recall at one point I was able to create a COM object in a
stored procedure and call methods, etc, but now I can't remember how I did
it. Can someone point me in the right direction?|||Ken,
you can use the sp_OA... extended stored procedures in master. Have a look
at sp_OACreate in BOL where there is an example using SQLDMO.
Alternatively I have done the whole thing in VBScript in DTS packages, and
called the packages from stored procedures. It is not ideal as variables are
declared as variants, but debugging is supported which can help a lot.
HTH,
Paul Ibison

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.