Move Text Data

Does anyone have a proven way to directly move text data (text/image data
type) from a field in one table to a field in another table?
I need to update (not insert) text data in one table with data text data
that exists in another table.
I can't use local variables to process the transaction...
ThanksTake a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||I have looked at these functions... but can't make them work.
the following code gets this error: < Data stream missing from
WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext ppd_object_version.script_text @.ptr1 readtext
ppd_setup_log.script_text @.ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext ppd_object_version.script_text @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKRzmBWFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books
Online.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
data
>|||Another possibility is to use VBScript or some other language. For example,
this is pretty trivial if it is a one-time thing:
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("SELECT primary_key,text_column FROM table")
do while not rs.eof
pk = rs(0) : tc = replace(rs(1), "'", "''")
sql = "UPDATE other_table SET text_column = '" & tc & _
"' WHERE primary_key = " & pk
conn.execute sql,,129
rs.movenext
loop
rs.close: set rs = nothing: conn.close: set conn = nothing
Now, depending on the size of your table, it may take a while, so you may
have to play with commandTimeout. But that took 30 seconds to throw
together, versus who knows how long it will take to develop something using
READTEXT/WRITETEXT etc.
A
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||here's a clearer version of the code:
I have looked at these functions... but can't make them work.
the following code gets this error:
< Data stream missing from WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext table1.text_field1 @.ptr1 readtext table2.text_field2 ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext table1.text_field1 @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eKRzmBWFGH
A.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online
.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
>

Move test data to production SQL Server

Hi All,
Could someone point me to a white paper/best practices on how to move/update
data from test sql server to a production sql server?
Both of the server have the same schema.
I would like to know if there is a way to synchronize the two databases.
Would SQL Server Replication be a good idea?
Many thanks,See http://www.red-gate.com for the SQL Data Compare product. We use Red
Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
production prep -- not transitioning to production). SQL Data Compare
quickly compares and allows you to synchronize two databases.
A more robust solution that accounts for "production data" being preserved
while merging in updated lookup tables, et. al., simply requires analyzing
your needs (which takes _time_) and then, in our case, maintaining a central
script for the lookup tables (which also takes _time_). Our script is
simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
unexpected happened!)
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> Hi All,
> Could someone point me to a white paper/best practices on how to
move/update
> data from test sql server to a production sql server?
> Both of the server have the same schema.
> I would like to know if there is a way to synchronize the two databases.
> Would SQL Server Replication be a good idea?
> Many thanks,
>
>|||Mike:
What do you mean by lookup tables?
"Mike Jansen" wrote:
> See http://www.red-gate.com for the SQL Data Compare product. We use Red
> Gate's SQL Compare and SQL Data Compare (mostly in development, testing, and
> production prep -- not transitioning to production). SQL Data Compare
> quickly compares and allows you to synchronize two databases.
> A more robust solution that accounts for "production data" being preserved
> while merging in updated lookup tables, et. al., simply requires analyzing
> your needs (which takes _time_) and then, in our case, maintaining a central
> script for the lookup tables (which also takes _time_). Our script is
> simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure nothing
> unexpected happened!)
> "Amine" <Amine@.discussions.microsoft.com> wrote in message
> news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > Hi All,
> > Could someone point me to a white paper/best practices on how to
> move/update
> > data from test sql server to a production sql server?
> >
> > Both of the server have the same schema.
> > I would like to know if there is a way to synchronize the two databases.
> > Would SQL Server Replication be a good idea?
> > Many thanks,
> >
> >
> >
> >
>
>|||By lookup tables I simply meant tables that are read-only, that the
application doesn't update.
I've got to run, but if you need more info on what I mean, let me know and
I'll try to be more detailed.
"Amine" <Amine@.discussions.microsoft.com> wrote in message
news:6C29CC4B-9B2C-4930-8269-2D3B371D28B9@.microsoft.com...
> Mike:
> What do you mean by lookup tables?
>
> "Mike Jansen" wrote:
> > See http://www.red-gate.com for the SQL Data Compare product. We use
Red
> > Gate's SQL Compare and SQL Data Compare (mostly in development, testing,
and
> > production prep -- not transitioning to production). SQL Data Compare
> > quickly compares and allows you to synchronize two databases.
> >
> > A more robust solution that accounts for "production data" being
preserved
> > while merging in updated lookup tables, et. al., simply requires
analyzing
> > your needs (which takes _time_) and then, in our case, maintaining a
central
> > script for the lookup tables (which also takes _time_). Our script is
> > simply a 1) disable constraints 2) delete, repopulate lookup tables, 3)
> > re-enable constraints (and run DBCC CHECKCONSTRAINTS to make sure
nothing
> > unexpected happened!)
> >
> > "Amine" <Amine@.discussions.microsoft.com> wrote in message
> > news:D92C4BF2-4EE9-4B64-BA32-D39D4BC21D15@.microsoft.com...
> > > Hi All,
> > > Could someone point me to a white paper/best practices on how to
> > move/update
> > > data from test sql server to a production sql server?
> > >
> > > Both of the server have the same schema.
> > > I would like to know if there is a way to synchronize the two
databases.
> > > Would SQL Server Replication be a good idea?
> > > Many thanks,
> > >
> > >
> > >
> > >
> >
> >
> >sql

move db Win2000 SQL2000sp2 to Server 2003 sp3a

On my current install of Win 2000 Sql Server sp2 I tried to install sp3a. A
fter repeated attempts the server did not update. I got no errors and it lo
oked like it had installed on the default database but every time I check th
e version, I was on sp2. S
o now I have a new server with Server 2003 installed and will be moving my d
atabases over there. So I have installed Sql Server 2000 sp2. I will then
be installing sp3a. Will there be any problems with restoring the master, t
emp, and all over databases
from Win 2000 sp2 server TO Server 2003 sp3a server?
Thanks for any guidance you can provide.I would have thought doing this would be a bad idea, as the restored master,
temp, msdb db's etc would be a different version to most of the server.
You are probably better off trying to update it after restoring your dbs.
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Can not really do that because Server 2003 needs to have Sql Server 2000 sp3
a installed.

move db Win2000 SQL2000sp2 to Server 2003 sp3a

On my current install of Win 2000 Sql Server sp2 I tried to install sp3a. After repeated attempts the server did not update. I got no errors and it looked like it had installed on the default database but every time I check the version, I was on sp2. So now I have a new server with Server 2003 installed and will be moving my databases over there. So I have installed Sql Server 2000 sp2. I will then be installing sp3a. Will there be any problems with restoring the master, temp, and all over databases from Win 2000 sp2 server TO Server 2003 sp3a server?
Thanks for any guidance you can provide.I would have thought doing this would be a bad idea, as the restored master,
temp, msdb db's etc would be a different version to most of the server.
You are probably better off trying to update it after restoring your dbs.
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Can not really do that because Server 2003 needs to have Sql Server 2000 sp3a installed.