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...
>
>

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

Copy SPROCs to different database

Is there a manual or code way to copy stored procedures from 1one sql db to
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:

> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>