database in SQL2K need help in upading/adding 2 memo columns

I have database in SQL2K. I have a memo column called memo1. I want to
update memo with "dicount % "+memo1.
I want to insert "discount %" in front of memo1.
This is going to be just one time shot. There are more 20,000 records need
to be updated.
I would appreciate if someone can help me.
I can run VB program and script so if anyone can help me to create something
in VB, if it is not possible in SQL2K,
I would really appreciate.
ThanksHi,
IN SQL 2000 you could write a simple Update statement
Update Tablename
set memo1 = 'discount % ' + memo1
This will update all the 20K records in a single shot.
Did I answered your question?
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:u9x4e.14059$Fh2.5711@.trnddc04...
>I have database in SQL2K. I have a memo column called memo1. I want to
>update memo with "dicount % "+memo1.
> I want to insert "discount %" in front of memo1.
> This is going to be just one time shot. There are more 20,000 records
> need to be updated.
> I would appreciate if someone can help me.
> I can run VB program and script so if anyone can help me to create
> something in VB, if it is not possible in SQL2K,
> I would really appreciate.
> Thanks
>|||Hari:
Thanks for your quick response.
I already tried this and I am getting data type error.
Thx
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
> Hi,
> IN SQL 2000 you could write a simple Update statement
> Update Tablename
> set memo1 = 'discount % ' + memo1
> This will update all the 20K records in a single shot.
> Did I answered your question?
> Thanks
> Hari
> SQL Server MVP
> "Mac" <mac@.hotmail.com> wrote in message
> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>|||Mac,
Looks like your datatype is TEXT. Inthat case you need to use UPDATETEXT
command to update.
See the below archive posts:-
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/11549ff4637eff45
Thanks
Hari
SQL Server MVP
"Mac" <mac@.hotmail.com> wrote in message
news:3Jx4e.25741$k66.15806@.trnddc03...
> Hari:
> Thanks for your quick response.
> I already tried this and I am getting data type error.
> Thx
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%234kWKteOFHA.3444@.tk2msftngp13.phx.gbl...
>> Hi,
>> IN SQL 2000 you could write a simple Update statement
>> Update Tablename
>> set memo1 = 'discount % ' + memo1
>> This will update all the 20K records in a single shot.
>> Did I answered your question?
>> Thanks
>> Hari
>> SQL Server MVP
>> "Mac" <mac@.hotmail.com> wrote in message
>> news:u9x4e.14059$Fh2.5711@.trnddc04...
>>I have database in SQL2K. I have a memo column called memo1. I want to
>>update memo with "dicount % "+memo1.
>> I want to insert "discount %" in front of memo1.
>> This is going to be just one time shot. There are more 20,000 records
>> need to be updated.
>> I would appreciate if someone can help me.
>> I can run VB program and script so if anyone can help me to create
>> something in VB, if it is not possible in SQL2K,
>> I would really appreciate.
>> Thanks
>>
>

Database in 'In recovery' mode

Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
- Jesse
Jesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
--
Herbert
"Jesse A" wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working
'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
Herbert
"Jesse A" wrote:

> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Database
s
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'worki
ng'
> on that database.
> I don't really need to complete the update (partial complete would be fine
).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jessesql

Database differences

We have a database that when an update is released (and this is very often) the release notes don't cover most of the actual changes. Every time groups of our custom queries and reports get broken due to database changes. Does anyone know how to compare two databases and get a report of the differences between them? I can either have the two versions on the same server or on different servers if that makes a difference.

I'm hoping for something where you input @.oldversion, @.newversion

and return is

@.oldversion, tblname, fieldname, char(8)
@.newversion, tblname, fieldname, varchar(8)
@.oldversion, tblname, [Null], [Null]
@.newversion, tblname, fieldname, int
@.oldversion, [Null]
@.newversion, tblname

also any changes in dependancies

Thanks
Brent--sp_addlinkedserver @.server = 'NJROS1D98'
--sp_addlinkedserver @.server = 'NJROS1A144'
--select * from sysservers

--select * from njros1a144.vigncontent.dbo.TBL_EDG_PAGE

--sp_addlinkedserver
-- 'NJEPWA103',
-- 'Oracle',
-- 'MSDAORA',
-- 'ORC1'

--Select * from NJEPWA103.ORC1.dbo.sysobjects

/* Objects in Pru Missing in NetCo */
Select 'Table Objects in Pru but are not in NetCo'
select Left(a.name,30), a.refdate from sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From njros1a144.vigncontent.dbo.sysobjects b where a.name = b.name)

/* Objects in NetCo Missing in Pru */
Select 'Table Objects in NetCo but are not in Pru'
select Left(a.name,30), a.refdate from njros1a144.vigncontent.dbo.sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)

/* Column Differences */

Select 'Column Differences between like named tables'

select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
, Left(x.DataType,15) as PruDataType, x.length as PruLength, x.refdate as PruRefDate
, Left(y.DataType,15) as NetCoDataType, y.length As NetCoLength, y.refdate as NetCoRefDate
from
( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U' and a.name like 'TBL%') As x
, ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from njros1a144.vigncontent.dbo.sysobjects a, njros1a144.vigncontent.dbo.syscolumns b, njros1a144.vigncontent.dbo.systypes c
where a.id = b.id and a.xtype = 'U'
and b.xusertype = c.xusertype
and a.name like 'TBL%') As y
Where x.TabName = y.TabName
and x.ColName = y.ColName
and (x.length <> y.length or x.DataType <> y.DataType)

/* Column Differences */
Select 'Column in Pru.com not in NetCo'

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from njros1a144.vigncontent.dbo.sysobjects d, njros1a144.vigncontent.dbo.syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2

/* Column Differences */
Select 'Column in NetCo not in Pru.com'

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from njros1a144.vigncontent.dbo.sysobjects a, njros1a144.vigncontent.dbo.syscolumns b, njros1a144.vigncontent.dbo.systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from sysobjects d, syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2

--Select 'Table Objects that are still in use in both NetCo and Pru'
--select Left(a.name,30), a.refdate from sysobjects a, njros1a144.vigncontent.dbo.sysobjects b where a.name = b.name and a.xtype = 'U'

Gotta change this to use the INFORMATION_SCHEMA views...|||Do you mean replace sysobj with Information_schema?

Not clear on where the modification you mention at the end takes place, can clearly see where I have to replace your server.db names with my own.

Thanks,

Brent|||Do you mean replace sysobj with Information_schema?

Not clear on where the modification you mention at the end takes place, can clearly see where I have to replace your server.db names with my own.

Thanks,

Brent|||No...for now, just replace

njros1a144.vigncontent.dbo

Change the Server.database.owner...that you want to compare one to the other

And run the code on the database that needs to be compared...

just cut and paste the code in to a query analyser window...

good luck...|||I just wanted to change the code so it uses the schemas, because the damn ccatalog is sooooo convaluted...I wrote this awhile back when I didn't know about the views...

You gotta do a sp_helptext against sp_help...

very funny comments in there...|||I was just prepping a response for this:

Changing the names for our names looks to work great, thank you very much. One further question, is there a way to use variable for the dbname/s as follows:

Declare
@.newver
@.oldver

Set @.newver = newverion
set @.oldver = oldserver.oldversion

use @.newver

Below is what I'm running now. For the above example I would like to replace all of the 'oldserver.oldversion' entries to use the @.oldver variable, but when I try it query analyser reads the path as @.oldver instead of the set value. Any ideas?

Again thanks a ton

Brent

Select 'Table Objects in ' + (Select lkpversion.version from lkpversion) + ' but are not in ' +(Select version from oldserver.oldversionname.dbo.lkpversion)
select Left(a.name,30) as TableName, a.refdate from sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From oldserver.oldversionname.dbo.sysobjects b where a.name = b.name)

/* Objects in old Version Missing in New Version */
Select 'Table Objects in ' + (select version from oldserver.oldversionname.dbo.lkpversion) +' but are not in '+ (select lkpversion.version from lkpversion)
select Left(a.name,30) as TableName, a.refdate from oldserver.oldversionname.dbo.sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)

/* Column Differences */

Select 'Column Differences between like named tables'

select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
, Left(x.DataType,15) as NewVerType, x.length as NewVerLength, x.refdate as NewVerRefDate
, Left(y.DataType,15) as OldVerType, y.length As OldVerLength, y.refdate as OldVerRefDate
from
( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U' and a.name like 'TBL%') As x
, ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from oldserver.oldversionname.dbo.sysobjects a, oldserver.oldversionname.dbo.syscolumns b, oldserver.oldversionname.dbo.systypes c
where a.id = b.id and a.xtype = 'U'
and b.xusertype = c.xusertype
and a.name like 'TBL%') As y
Where x.TabName = y.TabName
and x.ColName = y.ColName
and (x.length <> y.length or x.DataType <> y.DataType)

/* Column Differences */
Select 'Column in ' + (Select lkpversion.version from lkpversion) +' not in ' + (Select version from oldserver.oldversionname.dbo.lkpversion)

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from oldserver.oldversionname.dbo.sysobjects d, oldserver.oldversionname.dbo.syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2

/* Column Differences */
Select 'Column in '+ (Select version from oldserver.oldversionname.dbo.lkpversion) + ' but are not in ' +(Select lkpversion.version from lkpversion)

Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from oldserver.oldversionname.dbo.sysobjects a, oldserver.oldversionname.dbo.syscolumns b, oldserver.oldversionname.dbo.systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from sysobjects d, syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2|||You'd need to do dynamic SQL with an EXEC(@.sql)

What is lkpversion?

Those have to stay as the system tables I coded...|||Originally posted by Brett Kaiser
You'd need to do dynamic SQL with an EXEC(@.sql)

What is lkpversion?

Those have to stay as the system tables I coded...

lkpversion is a table that contains the version number of the software.

So what I get is a descriptive field lik:

tables in version 3.8 not in version 3.7

database design for periodic update

Hi. I have an application running on local machines at different geographic locations, that need to update data (and pick some info back) to a central server at the end of the day (receiving data from all locations).
Data volume can be 500 records / day, nearly 2 MB.

I am not sure if this can be called distributed database.

I plan to use SQL Server on the central server and am undecided on whether to use MS Access / SQL server. I will be using VB for the application.

What application technology and database technology (and data transfer) would be ideal?

Thanks.I would think that SQL Server would be the most ideal. You could set up separate ACCESS DSN (for your various clients to upload their MS Access database updates to), then use DTS within SQL Server to pull data from these various datasources.

What I would recommend is the following (in terms of the DTS structure, easiest, not the most jazzy or seamless approach, but it may work, of course, the other option is writing a stored procedure to either UPDATE or INSERT data into your centralized database):

1) Delete all data from central database.
2) Append data from client#1
3) Append data from client#2
4) Append data from client#3

etc...