copying views

I have created several views via Enterprise Manager in a
SQL database that supports software provided by an outside
source. These views will be effected during software
version upgrades and may have some nagative impact on the
execution of upgrades.
Is there some simple way to preserve these views during a
software upgrade?
I hoped that I would just be able to cut and paste the
views (perhaps into another database where they didn't
really belong) temporarily during an upgrade and then move
them back. I don't see any way to do this. I can right
click on the views and copy them, but I don't see any way
to paste it anywhere for temporary storage.
I would prefer not to copy the query text from each view,
save it somewhere temporarily, and then recreate each view
and paste the query text back into it. I have a lot of
views created and this would be a time consuming solution.
Thanks.generate scripts and run those after the update
"allen" <anonymous@.discussions.microsoft.com> wrote in message
news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
> I have created several views via Enterprise Manager in a
> SQL database that supports software provided by an outside
> source. These views will be effected during software
> version upgrades and may have some nagative impact on the
> execution of upgrades.
> Is there some simple way to preserve these views during a
> software upgrade?
> I hoped that I would just be able to cut and paste the
> views (perhaps into another database where they didn't
> really belong) temporarily during an upgrade and then move
> them back. I don't see any way to do this. I can right
> click on the views and copy them, but I don't see any way
> to paste it anywhere for temporary storage.
> I would prefer not to copy the query text from each view,
> save it somewhere temporarily, and then recreate each view
> and paste the query text back into it. I have a lot of
> views created and this would be a time consuming solution.
> Thanks.|||Thanks!
>--Original Message--
>generate scripts and run those after the update
>"allen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b601c3c4e2$08ce32d0$a601280a@.phx.gbl...
>> I have created several views via Enterprise Manager in a
>> SQL database that supports software provided by an
outside
>> source. These views will be effected during software
>> version upgrades and may have some nagative impact on
the
>> execution of upgrades.
>> Is there some simple way to preserve these views during
a
>> software upgrade?
>> I hoped that I would just be able to cut and paste the
>> views (perhaps into another database where they didn't
>> really belong) temporarily during an upgrade and then
move
>> them back. I don't see any way to do this. I can right
>> click on the views and copy them, but I don't see any
way
>> to paste it anywhere for temporary storage.
>> I would prefer not to copy the query text from each
view,
>> save it somewhere temporarily, and then recreate each
view
>> and paste the query text back into it. I have a lot of
>> views created and this would be a time consuming
solution.
>> Thanks.
>
>.
>sql

Copying Text Field

I am refreshing records on a target table from a similar source table (two
databases, same server) and having problems with the Text fields. I am usin
g
INSERTas follows:
delete from Trackpad4..Archived
set identity_insert Trackpad4..Archived on
insert into Trackpad4..Archived
(... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
from Trackpad..Archived
set identity_insert Trackpad4..Archived off
The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
is that a simple SELECT would return three rows but a rowcount of four. A
modification of the query picked up all the rows, but the DEL_RECIP_SIG fiel
d
was blank and none of the following fields displayed. Using null at least
allows the SELECTs to perform as expected and the later columns to be handle
d
properly.
My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
But the test displayed below, while it did populate the target text field,
again rendered the later columns apparently non-existent. Also, the
gobbledygook (some sort of encoded representation of a person's signature) i
n
the source and target DEL_RECIP_SIG fields don't match.
DECLARE @.ptrval_source varbinary(16)
, @.ptrval_target varbinary(16)
, @.Length_Source int
, @.Length_Target int
SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Source = DEL_RECIP_LEN
FROM trackpad..old_data
where PKG_NUM = 'W44287093174'
print 'Source'
print @.ptrval_source
print @.Length_Source
/*
update Trackpad4..Old_Data
set DEL_RECIP_SIG = 'placeholder'
where PKG_NUM = 'W44287093174'
*/
select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
, @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
FROM trackpad4..old_data
where PKG_NUM = 'W44287093174'
print 'Target'
print @.ptrval_target
print @.Length_Target
print 'source'
readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
print 'update'
UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
@.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
print 'target'
readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
I tried copying the source table over through DTS, but target text fields
were empty. I was able to restore from a backup. All I can think is that
I'm not properly delineating the field going into the target record and its
violating SQL's internal rules for terminating fields and records.
Thanks for any insight,
KevinHi Kevin
You don't say what SQL Server versions you are using?
This was a problem with DTS!
http://support.microsoft.com/defaul...kb;en-us;257425
If you set the value for specific DEL_RECIP_SIG to NULL you will find out
the one that is causing this issue.
John
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>I am refreshing records on a target table from a similar source table (two
> databases, same server) and having problems with the Text fields. I am
> using
> INSERTas follows:
>
> delete from Trackpad4..Archived
> set identity_insert Trackpad4..Archived on
> insert into Trackpad4..Archived
> (... DEL_RECIP_NAME, DEL_RECIP_LEN, DEL_RECIP_SIG, ...)
> select ... DEL_RECIP_NAME, DEL_RECIP_LEN, null, ...
> from Trackpad..Archived
> set identity_insert Trackpad4..Archived off
>
> The problem we noticed (and why I've got null populating in DEL_RECIP_SIG)
> is that a simple SELECT would return three rows but a rowcount of four. A
> modification of the query picked up all the rows, but the DEL_RECIP_SIG
> field
> was blank and none of the following fields displayed. Using null at least
> allows the SELECTs to perform as expected and the later columns to be
> handled
> properly.
> My thought was to use a UPDATETEXT wrapped in a cursor to finish the job.
> But the test displayed below, while it did populate the target text field,
> again rendered the later columns apparently non-existent. Also, the
> gobbledygook (some sort of encoded representation of a person's signature)
> in
> the source and target DEL_RECIP_SIG fields don't match.
>
> DECLARE @.ptrval_source varbinary(16)
> , @.ptrval_target varbinary(16)
> , @.Length_Source int
> , @.Length_Target int
> SELECT @.ptrval_source = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Source = DEL_RECIP_LEN
> FROM trackpad..old_data
> where PKG_NUM = 'W44287093174'
> print 'Source'
> print @.ptrval_source
> print @.Length_Source
> /*
> update Trackpad4..Old_Data
> set DEL_RECIP_SIG = 'placeholder'
> where PKG_NUM = 'W44287093174'
> */
> select @.ptrval_target = TEXTPTR(DEL_RECIP_SIG)
> , @.Length_Target = len(cast(DEL_RECIP_SIG as varchar(8000)))
> FROM trackpad4..old_data
> where PKG_NUM = 'W44287093174'
> print 'Target'
> print @.ptrval_target
> print @.Length_Target
> print 'source'
> readtext trackpad..old_data.DEL_RECIP_SIG @.ptrval_source 0 @.Length_Source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
> print 'update'
> UPDATETEXT trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0
> @.Length_Target trackpad..old_data.DEL_RECIP_SIG @.ptrval_source
> print 'target'
> readtext trackpad4..old_data.DEL_RECIP_SIG @.ptrval_target 0 @.Length_Target
>
> I tried copying the source table over through DTS, but target text fields
> were empty. I was able to restore from a backup. All I can think is that
> I'm not properly delineating the field going into the target record and
> its
> violating SQL's internal rules for terminating fields and records.
> Thanks for any insight,
> Kevin|||Hi, John,
Thanks for the reply. I'm using SQL 2000, SP3. I've tried this operation
in TSQL and DTS - same results. The UpdateText operation also didn't work -
seemingly the stored text data wasn't terminated properly, and following
fields in the record were not rendered at all. In any event, the target tex
t
did not resemble the source text.
If you can think of anything else, thanks in advance.
- Kevin
"John Bell" wrote:

> Hi Kevin
> You don't say what SQL Server versions you are using?
> This was a problem with DTS!
> http://support.microsoft.com/defaul...kb;en-us;257425
> If you set the value for specific DEL_RECIP_SIG to NULL you will find out
> the one that is causing this issue.
> John
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:08CADCB0-8B74-4987-B78F-7F375C1BB7C8@.microsoft.com...
>
>

Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source a

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

What defect are you referring to in "Transfer Objects" task?|||

Check this out for the defect:

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609

MShah

|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.

So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||

Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.

sql

Copying a cube but using a different data source

We have a cube that we've been using for a while. I need to make several more cubes with the exact same schema and roles and everything else...just with different data sources. The other data sources have the same schema as the database the original cube is using. I know that when you copy a cube and paste it, the data source is copied over too. And I've read that you can't change the data source of a cube.

My question is what is the easiest way to create the new cubes? Or do I have to create every new cube from scratch (pain in the butt)?

Thanks in advance for your advice.
After you made a copy of your project, create a new DataSource to point to your new relational DB. Open DSV in Xml Mode, change the tag <DataSourceID>...</DataSourceID> to point to your new data source ID.|||Thanks Ken. We're using AS2000. Is there a DSV equivalent?
|||Oh AS2000 is total different story then. There is no DSV concept in AS2000. You can try changing the connection string in the same data source rather than switching the data source on the cube.|||Yeah, I thought about that but we have another cube using the data source so that option is out. Looks like I will need to create the each new cube from scratch. : (
|||Go to every partition of this cube, edit it, and then change the data source. This should do a job.|||Thanks Sasha!

copy wizard gives error creating share

When I run the database copy wizard I'm getting an error creating shared omwwize.
The source server is 7.0. The destination server is 2000. I'm invoking the wizard from the 2000 machine.
Anybody know what this error means?
Thanks
SharonIf the destination SQL Server is started under a domain account that does not have local administrator privileges on the source computer then the following error message may be generated by the wizard:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error

Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Clicking on the "MORE INFO >>>" button, you will find that permissions to create the remote share are not present.

Failed to create the share OMWWIZD

Copy Wizard Failure when coping a database on the same machine

I am trying to copy the a database on the same machine (source and destination are the same). The wizard fails using the SMO method. I have attemped to turn on the "Save Transfer logs", but it does not appear to save a file: I have looked in C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\SMO for all the users on the server. None of them have the SMO directory in the that path nor can I find a SMO directory on the server.

The SQL Server agent account is set to Local System.

I have made the Local Administrator a Sysadmin in SQL server.

Job History after failure:

Date 6/15/2007 8:25:10 PM
Log Job History (CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9)

Step ID 1
Server YOUR-BQEHG1XTVQ
Job Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9
Step Name CDW_YOUR-BQEHG1XTVQ_YOUR-BQEHG1XTVQ_9_Step
Duration 00:00:48
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: YOUR-BQEHG1XTVQ\SYSTEM. ...GUE SQL USE [Tango_QA] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[strpcd_firewall_lan_objects_to_add]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[strpcd_firewall_lan_objects_to_add] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_policies]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_policies] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_call_view_your_account_firewall_objects]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_call_view_your_account_firewall_objects] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stpcd_GetSIPNetworkLocation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[stpcd_GetSIPNetworkLocation] GO IF EXISTS (SELECT * FROM sys.objects WHERE obje... The package execution fa... The step failed.

Stumped as what what to try next or what the issue is?

What is the level of service pack on SQL Server?

BOL refers:
The destination server must be running SQL Server 2005 Service Pack 2 or a later version. The computer on which the Copy Database Wizard runs may be the source or destination server, or a separate computer. This computer must also be running SQL Server 2005 Service Pack 2 or a later version to use all the features of the wizard.

To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source and destination servers. To transfer databases by using the detach-and-attach method, you must have file system access to the file-system share that contains the source database files.

|||The source and the destination server are one and the same. It is running SQL 2005 SP2. I am using SA to make a copy of the database. The wizard starts to copy the database but then fails before coping all of the tables.|||Ensure SA login is not disabled, you might try using SQL SErver service account in this case.

Copy Wizard failed?

I tried to use Copy Database wizard but it keeps returning this error:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
How do I resolve this?
Both the source database and destination database have valid
authentications.It's likely related to permissions. For permissions
requirements, service account requirements, refer to the
following article:
INF: Understanding and Troubleshooting the Copy Database
Wizard in SQL Server 2000
http://support.microsoft.com/?id=274463
-Sue
On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
<eddiemarino@.hotmail.com> wrote:
>I tried to use Copy Database wizard but it keeps returning this error:
>Step Error Source: Microsoft Data Transformation Services (DTS) Package
>Step Error Description:Unspecified error
>Step Error code: 80004005
>Step Error Help File:sqldts80.hlp
>Step Error Help Context ID:1100
>
>How do I resolve this?
>Both the source database and destination database have valid
>authentications.
>|||Thanks, Sue.
Could a database be copied without using this wizard?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ii01tvo98f6qoe1rspo94u3i2lrm3mssne@.4ax.com...
> It's likely related to permissions. For permissions
> requirements, service account requirements, refer to the
> following article:
> INF: Understanding and Troubleshooting the Copy Database
> Wizard in SQL Server 2000
> http://support.microsoft.com/?id=274463
> -Sue
> On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
> <eddiemarino@.hotmail.com> wrote:
> >I tried to use Copy Database wizard but it keeps returning this error:
> >
> >Step Error Source: Microsoft Data Transformation Services (DTS) Package
> >Step Error Description:Unspecified error
> >
> >Step Error code: 80004005
> >Step Error Help File:sqldts80.hlp
> >Step Error Help Context ID:1100
> >
> >
> >How do I resolve this?
> >
> >Both the source database and destination database have valid
> >authentications.
> >
>|||Hi
It is not clear why you want to use this but maybe the way around it is to
use a different method to copy the database:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
John
"Ed" <eddiemarino@.hotmail.com> wrote in message
news:%23weAzjnuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> I tried to use Copy Database wizard but it keeps returning this error:
> Step Error Source: Microsoft Data Transformation Services (DTS) Package
> Step Error Description:Unspecified error
> Step Error code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
>
> How do I resolve this?
> Both the source database and destination database have valid
> authentications.
>|||Yes...you can copy a backup file to the destination server
and restore the backup. You can use sp_detach_db and
sp_attach_db.
-Sue
On Fri, 5 Dec 2003 11:00:32 -0500, "Ed"
<eddiemarino@.hotmail.com> wrote:
>Thanks, Sue.
>Could a database be copied without using this wizard?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:ii01tvo98f6qoe1rspo94u3i2lrm3mssne@.4ax.com...
>> It's likely related to permissions. For permissions
>> requirements, service account requirements, refer to the
>> following article:
>> INF: Understanding and Troubleshooting the Copy Database
>> Wizard in SQL Server 2000
>> http://support.microsoft.com/?id=274463
>> -Sue
>> On Wed, 3 Dec 2003 18:21:41 -0500, "Ed"
>> <eddiemarino@.hotmail.com> wrote:
>> >I tried to use Copy Database wizard but it keeps returning this error:
>> >
>> >Step Error Source: Microsoft Data Transformation Services (DTS) Package
>> >Step Error Description:Unspecified error
>> >
>> >Step Error code: 80004005
>> >Step Error Help File:sqldts80.hlp
>> >Step Error Help Context ID:1100
>> >
>> >
>> >How do I resolve this?
>> >
>> >Both the source database and destination database have valid
>> >authentications.
>> >
>

Copy tables between databases using variables

I am trying to find the best way to copy specific tables from one database
to another when the source and target database names are not always the
same. Can you use variables to specify (or prompt the user) to provide
source and target databases? The target database will exist with the the
same tables as the source. The tables to copy will always be the same.

Example:
UserA wants to copy 10 tables from Data1 to Data2
UserB wants to copy 10 tables from Data4 to Data5

I'm sure a script can do this in Query Analyzer but is there a more user
friendly method when the user has ony standard SQL tools?

Thanks in advance.Hi

You could use DTS and change the source/destination tables:
http://www.sqldts.com/default.aspx?213

You could use dynamic SQL if you don't open yourself to SQL injection:
http://www.sommarskog.se/dynamic_sql.html

John

<rdraider@.sbcglobal.net> wrote in message
news:5GRWb.23042$V57.1004@.newssvr27.news.prodigy.c om...
> I am trying to find the best way to copy specific tables from one database
> to another when the source and target database names are not always the
> same. Can you use variables to specify (or prompt the user) to provide
> source and target databases? The target database will exist with the the
> same tables as the source. The tables to copy will always be the same.
> Example:
> UserA wants to copy 10 tables from Data1 to Data2
> UserB wants to copy 10 tables from Data4 to Data5
> I'm sure a script can do this in Query Analyzer but is there a more user
> friendly method when the user has ony standard SQL tools?
> Thanks in advance.

Copy Table and Data from one database to another

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.

CREATE TABLE [dbo].[tbPRO_Package](
[PRO_PackageID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()),
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()),
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()),
CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED
(
[PRO_PackageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.

If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] (
[PRO_PackageID] int NOT NULL,
[PackageCode] varchar(8) NOT NULL,
[LKU_VatRateCode] nchar(10),
[CancellationCharge] decimal(18,2),
[CancellationMultiplier] decimal(18,2),
[CreatedDate] datetime NOT NULL,
[CreatedBy] varchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL,
[ModifiedBy] varchar(50) NOT NULL,
[GUID] uniqueidentifier NOT NULL
)

We've lost the identity, the PK,the constraints and the defaults.

If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?"If

I use the SSIS Transfer SQL Server Objects Task, then the primary key

is kept, but the identity is not, nor are the constraints or defaults."

This behavior is by design to maintain backward compatibility with the Copy SQL Server Objects task in SQL Server 2000. Only two constraints (Primary key and foreign key constraints) are expected to be copied by Transfer Objects task.

Sorry for the inconvenience.

If transferring the entire database (as opposed to transferring only the tables) is okay, you can use "Transfer Database" task.

If you want to transfer only tables and preserve the table definition, I can think of two ways it can be done using SMO. You can implement C# code using SMO API to copy the tables or you can implement the same (in VB) inside a script task in SSIS. Both of these approaches should be pretty easy.

Hope this helps.|||so - it is not possible to accurately move a table and its data from one database to another, without resorting to writing code?

DESPITE there being two SQL tools that say they do it?

what is the purpose of these SQL tools then? For those oocassions where you want to move tables and data inaccurately?

you have stated this reponse before, in my post here.

Another poster asked in this thread:

MehtaR wrote:

Kaarthik,

I am not sure why you say "This behavior is by design

to maintain backward compatibility with the Copy SQL Server Objects

task in SQL Server 2000."
In SQL 2000 you can use DTS to transfer tables from one database to another preserving the primary as well as identity columns.
Rahul

edit: added smiley to ensure sarcasm detectors are activated |||I have two issues with this:

1) It's *not* transferringg foreign key constraints. That is what it's complaining about.

2) The "transfer database" task is a joke becuase it doesn't transfer the entire database, just the bits and pieces it chooses to transfer.

All I need is a simple, exact, bit for bit transfer from a 2000 to a 2K5 DB. This appears to be an impossibility in SSIS.|||

You can use Redgate SQL Packager to do what you are doing, since i have worked recently on a similar project. You can download their trial verison to play around with it I htink. SQL Packager will allow you to pick and choose the tables or any objects in your source db, and then package it. Then all you gotta do is to move the package(which is an exe file) to the server where you want to migrate it to. When you execute the package, it will cleanly create the db, with the chosen tables. If you are doing an upgrade, and then choose destination db when preparing the package, and it will create an upgrade package, to again move data/tables both(any that you choose). Thanks

MA

|||exactly!

surely a means to do this is fundamental requirement of a product such as SSIS?

I dont think this is too much to expect.|||I have several Redgate tools, such as packager, sql compare and data compare, and they are fantastically useful - I honest couldn't do my job without sql compare now.

But - we shouldn't need a third party tool to do such a basic task.|||There is a "wizard" that supposedly does this. I've not yet tested it and the way it works is quite bass-ackwards. It also only works for 2K/2K5 --> 2K5. What you have to do is go to your 2K5 DB, right click on the desired DB, go to tasks and down at the very bottom you'll see "Copy database". This theoretically creates a SSIS package which I am hoping I can them tweak if necessary.

What info I found on this is here:
http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

Copy Table and Data from one database to another

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.

CREATE TABLE [dbo].[tbPRO_Package](
[PRO_PackageID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()),
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()),
[ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()),
[GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()),
CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED
(
[PRO_PackageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.

If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] (
[PRO_PackageID] int NOT NULL,
[PackageCode] varchar(8) NOT NULL,
[LKU_VatRateCode] nchar(10),
[CancellationCharge] decimal(18,2),
[CancellationMultiplier] decimal(18,2),
[CreatedDate] datetime NOT NULL,
[CreatedBy] varchar(50) NOT NULL,
[ModifiedDate] datetime NOT NULL,
[ModifiedBy] varchar(50) NOT NULL,
[GUID] uniqueidentifier NOT NULL
)

We've lost the identity, the PK,the constraints and the defaults.

If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?"If

I use the SSIS Transfer SQL Server Objects Task, then the primary key

is kept, but the identity is not, nor are the constraints or defaults."

This behavior is by design to maintain backward compatibility with the Copy SQL Server Objects task in SQL Server 2000. Only two constraints (Primary key and foreign key constraints) are expected to be copied by Transfer Objects task.

Sorry for the inconvenience.

If transferring the entire database (as opposed to transferring only the tables) is okay, you can use "Transfer Database" task.

If you want to transfer only tables and preserve the table definition, I can think of two ways it can be done using SMO. You can implement C# code using SMO API to copy the tables or you can implement the same (in VB) inside a script task in SSIS. Both of these approaches should be pretty easy.

Hope this helps.|||so - it is not possible to accurately move a table and its data from one database to another, without resorting to writing code?

DESPITE there being two SQL tools that say they do it?

what is the purpose of these SQL tools then? For those oocassions where you want to move tables and data inaccurately?

you have stated this reponse before, in my post here.

Another poster asked in this thread:

MehtaR wrote:

Kaarthik,

I am not sure why you say "This behavior is by design

to maintain backward compatibility with the Copy SQL Server Objects

task in SQL Server 2000."
In SQL 2000 you can use DTS to transfer tables from one database to another preserving the primary as well as identity columns.
Rahul

edit: added smiley to ensure sarcasm detectors are activated |||I have two issues with this:

1) It's *not* transferringg foreign key constraints. That is what it's complaining about.

2) The "transfer database" task is a joke becuase it doesn't transfer the entire database, just the bits and pieces it chooses to transfer.

All I need is a simple, exact, bit for bit transfer from a 2000 to a 2K5 DB. This appears to be an impossibility in SSIS.|||

You can use Redgate SQL Packager to do what you are doing, since i have worked recently on a similar project. You can download their trial verison to play around with it I htink. SQL Packager will allow you to pick and choose the tables or any objects in your source db, and then package it. Then all you gotta do is to move the package(which is an exe file) to the server where you want to migrate it to. When you execute the package, it will cleanly create the db, with the chosen tables. If you are doing an upgrade, and then choose destination db when preparing the package, and it will create an upgrade package, to again move data/tables both(any that you choose). Thanks

MA

|||exactly!

surely a means to do this is fundamental requirement of a product such as SSIS?

I dont think this is too much to expect.|||I have several Redgate tools, such as packager, sql compare and data compare, and they are fantastically useful - I honest couldn't do my job without sql compare now.

But - we shouldn't need a third party tool to do such a basic task.|||There is a "wizard" that supposedly does this. I've not yet tested it and the way it works is quite bass-ackwards. It also only works for 2K/2K5 --> 2K5. What you have to do is go to your 2K5 DB, right click on the desired DB, go to tasks and down at the very bottom you'll see "Copy database". This theoretically creates a SSIS package which I am hoping I can them tweak if necessary.

What info I found on this is here:
http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx