I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
John
update DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
Russel Loski, MCSD.Net
|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>
|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>
|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>
Showing posts with label apologies. Show all posts
Showing posts with label apologies. Show all posts
Wednesday, March 7, 2012
HELP: Updating column with data from another column
I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
--
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
> > Thanks for the quick reply, Russell! That makes better sense. I ran the
> > query and am now getting this error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 2
> > Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> > the server to sysservers.
> >
> > Since DB2 is a database on the same server as DB1, and not a server, could I
> > be referring to the database incorrectly?
> >
> > Thanks,
> > John
> >
>
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
--
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
> > Thanks for the quick reply, Russell! That makes better sense. I ran the
> > query and am now getting this error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 2
> > Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> > the server to sysservers.
> >
> > Since DB2 is a database on the same server as DB1, and not a server, could I
> > be referring to the database incorrectly?
> >
> > Thanks,
> > John
> >
>
HELP: Updating column with data from another column
I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textst
ring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to a
dd
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could
I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>
>
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textst
ring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to a
dd
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could
I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>
>
Monday, February 27, 2012
Help: SQL Server 2K draginng system down?
Hello, apologies if this is the wrong place to post this.
We've got a dev server which is supporting multiple services for
testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
Although I cannot be certain, I believe since SQL Server 2000 was
installed these symptoms begain happening. The server it resides on
after a period of several days to a week becomes unresponsive. File
sharing throughput becomes slowed down, terminal services ceases
accepting connections, the server itself when logged into at the console
is very sluggish with responding to input. The mouse(USB) pointer is
lagged even. OS is Win2K Server.
The server is not under high load, as it is a development box but we are
concerned it may happen elsewhere if deployed.
Has anyone else had this happen and have they found a solution to it?
Thanks
JasonFirst question is do you have service pack 3 on the box? If not you may be
the recipient of the slammer.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||Yeah make sure you have SP3... I've got a small server at home, Active
Directory, Domain Controller, DNS, DHCP, Internet Portal, SQL Server, you
name it... - all fine for a limited number of users.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||In article <#8QuHZriDHA.1940@.TK2MSFTNGP09.phx.gbl>,
rayhigdon@.higdonconsulting.com says...
> First question is do you have service pack 3 on the box? If not you may be
> the recipient of the slammer.
>
Sorry.
Yep, Windows is patched to SP4, SQL Server is patched to 3. Of course I
don't have any solid evidence that SQL Server is actually what caused
the problem either. I just suspect it is.
Thanks,
Jason
We've got a dev server which is supporting multiple services for
testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
Although I cannot be certain, I believe since SQL Server 2000 was
installed these symptoms begain happening. The server it resides on
after a period of several days to a week becomes unresponsive. File
sharing throughput becomes slowed down, terminal services ceases
accepting connections, the server itself when logged into at the console
is very sluggish with responding to input. The mouse(USB) pointer is
lagged even. OS is Win2K Server.
The server is not under high load, as it is a development box but we are
concerned it may happen elsewhere if deployed.
Has anyone else had this happen and have they found a solution to it?
Thanks
JasonFirst question is do you have service pack 3 on the box? If not you may be
the recipient of the slammer.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||Yeah make sure you have SP3... I've got a small server at home, Active
Directory, Domain Controller, DNS, DHCP, Internet Portal, SQL Server, you
name it... - all fine for a limited number of users.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jason Coleman" <jcoleman2004@.comcast.net> wrote in message
news:MPG.19e8c1841bd78037989683@.news.microsoft.com...
> Hello, apologies if this is the wrong place to post this.
> We've got a dev server which is supporting multiple services for
> testing. It is primarily doing AD, DNS, and recently SQL Server 2000.
> Although I cannot be certain, I believe since SQL Server 2000 was
> installed these symptoms begain happening. The server it resides on
> after a period of several days to a week becomes unresponsive. File
> sharing throughput becomes slowed down, terminal services ceases
> accepting connections, the server itself when logged into at the console
> is very sluggish with responding to input. The mouse(USB) pointer is
> lagged even. OS is Win2K Server.
> The server is not under high load, as it is a development box but we are
> concerned it may happen elsewhere if deployed.
> Has anyone else had this happen and have they found a solution to it?
> Thanks
> Jason|||In article <#8QuHZriDHA.1940@.TK2MSFTNGP09.phx.gbl>,
rayhigdon@.higdonconsulting.com says...
> First question is do you have service pack 3 on the box? If not you may be
> the recipient of the slammer.
>
Sorry.
Yep, Windows is patched to SP4, SQL Server is patched to 3. Of course I
don't have any solid evidence that SQL Server is actually what caused
the problem either. I just suspect it is.
Thanks,
Jason
Subscribe to:
Posts (Atom)