Replacing a text globally

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

and replace all of them with "abc" without affecting the databases an application.

Can u please help me with step by step guidance?

muralidaran rYou want to change the names of things without affecting an application that uses this data?
Slow down and think about what effects this will have.|||hi

thanks for your reply

the only change expected from application is the change of connection string.

Muralidaran r|||Your application doesn't do anything like

SELECT Field1 FROM xyzTable

?|||My application code never uses table name or column name.

it uses only stored procedure names.

muralidaran r|||I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures

I rest my case.|||If your application calls stored procedure names have "xyz" in them, then there is no solution to your problem... When you rename the called procedures, the application will fail (because it will still try to use the old names that contain "xyz" instead of "abc").

-PatP|||due to company/product name change,

Can u please help me with step by step guidance?

Sure, learn how to code correctly|||Thank you guys. Now I will retype the question. try to understand better. Actually the coding was done by someone else and now i have to manage this. OK. try to give a solution.

I have a instance with many databases in it.

due to company/product name change,

I want to search for a string "xyz" in
database name,
table name,
column name,
content of all stored procedures

and replace all of them with "abc"

Can u please help me with step by step guidance?

muralidaran r|||It shouldn't be done IMO.
Users don't see database name(s), table names, column names or content of stored procedures. Users should only see data that you let them see.|||How to search for a string "xyz" in

database name,

table name,

column name,

content of all stored procedures

muralidaran|||I want to search for a string "xyz" in
...
and replace all of them with "abc"

I did listen and I responded appropriately. My advice is don't do it, simple as that.

As always you can chose to ignore my advice but I can assure you that others will respond in a similar fashion.|||Hi

Already i started changing the string by some manuall method which is tedious and hard.

at each and every stage i am checking the applications performance and accuracy.

I need help to speed up this.|||Speaking from a SQL2000 perspective, you need to check "name" in sysobjects for tablenames, view names, proc names, etc. You need to check text in syscomments for sproc content, etc, you need to check syscolumns for column names, etc.

I'm sure for every place you catch, there will be 2 you don't catch.

I'm with GeorgeV, DO NOT DO IT. Let sleeping dogs lay.

Have fun.|||Dear muralidaran_r,

Have you understood what the users here are telling you?
If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name.

Or are you asking how to search for and change data stored in the tables?|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.|||muralidaran,

Script your entire database to a single text file. Do a search and replace in that text file, and then run the script against a new empty database, as I am sure there will be many errors that occur and that will need to be fixed.

....or, find another career|||"If you change a table name from xyzMyTable to abcMyTable, then all views, triggers, procedures functions, client side calls etc must be modified to point to the new name"

Yes to modify all, is there any automated method, tool, procedure available.

Muralidaran r|||No, not that I know of.

Replacement CD

Hi,
I had to reinstall the PC due to an earlier crash - Apparently my customer
lost the original SQL Server Personal Edition CDROM - I do have the license
Key and other details - Is there a way I can order replacement CDROMs from
Microsoft for this product that I purchased and if so, how/where to call?
Anyone had a similar experience and can share the wisdom?
Thanks very much,
Raj
> I had to reinstall the PC due to an earlier crash - Apparently my customer
> lost the original SQL Server Personal Edition CDROM - I do have the
license
> Key and other details - Is there a way I can order replacement CDROMs from
> Microsoft for this product that I purchased and if so, how/where to call?
> Anyone had a similar experience and can share the wisdom?
Check if this is helpful:
http://support.microsoft.com/default...;en-us;171931.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Replacement CD

Hi,
I had to reinstall the PC due to an earlier crash - Apparently my customer
lost the original SQL Server Personal Edition CDROM - I do have the license
Key and other details - Is there a way I can order replacement CDROMs from
Microsoft for this product that I purchased and if so, how/where to call?
Anyone had a similar experience and can share the wisdom?
Thanks very much,
Raj> I had to reinstall the PC due to an earlier crash - Apparently my customer
> lost the original SQL Server Personal Edition CDROM - I do have the
license
> Key and other details - Is there a way I can order replacement CDROMs from
> Microsoft for this product that I purchased and if so, how/where to call?
> Anyone had a similar experience and can share the wisdom?
Check if this is helpful:
http://support.microsoft.com/defaul...b;en-us;171931.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Replacement CD

Hi,
I had to reinstall the PC due to an earlier crash - Apparently my customer
lost the original SQL Server Personal Edition CDROM - I do have the license
Key and other details - Is there a way I can order replacement CDROMs from
Microsoft for this product that I purchased and if so, how/where to call?
Anyone had a similar experience and can share the wisdom?
Thanks very much,
Raj> I had to reinstall the PC due to an earlier crash - Apparently my customer
> lost the original SQL Server Personal Edition CDROM - I do have the
license
> Key and other details - Is there a way I can order replacement CDROMs from
> Microsoft for this product that I purchased and if so, how/where to call?
> Anyone had a similar experience and can share the wisdom?
Check if this is helpful:
http://support.microsoft.com/default.aspx?scid=kb;en-us;171931.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Repeating Information

Hi

I am trying to make a letter in Crystal Reports 10 that pulls up details for all our customers whose technical support contracts are due to expire between two set dates.

The letter is populated with Address and Contact details of the customer from a table called Profile and the contract expiry date is pulled from another table linked to the customer called Config.

I have managed to set this up and it pulls up all our customers who are due to expire and places their info into an individual letter.

The only problem is that each letter is duplicated another 5 times so that I am getting 6 letters for each customer.

Does anyone know what is going wrong?

The code I am using is quite simple.

In the select expert I have:
{@.Expiry date ( as a date)} in {?Date Range} and
{Config.SupportExpDate} <> " "

I have created a formula for the expiry date:
If
(
Numerictext({Config.SupportExpDate}[1 to 4])
and
Numerictext({Config.SupportExpDate}[6 to 7])
and
Numerictext({Config.SupportExpDate}[9 to 10])
)

then

Date ( toNumber({Config.SupportExpDate}[1 to 4]),
toNumber({Config.SupportExpDate}[6 to 7]),
toNumber({Config.SupportExpDate}[9 to 10]))

The date range is created using 2 formulas for start and finish:

Maximum({?Date Range})

&

Minimum({?Date Range})

Anybody got any ideas where I'm going wrong?Assuming that u execute the report from VBasic, the below logic shd work out..

Say the Customer table has a Unique field, for instance field "Cust_id" is unique in yr table, then we can pull only unique of Cust_id into report. This can be done by using the below qry...

CrystalReport1.DiscardSavedData = True
CrystalReport1.SQLQuery = "Select distinct(cust_id),......"
CrystalReport1.Action = 1|||Another thing to check for is to see if, in your Datasource, you haven't linked your fields properly (if using more than one table).

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>|||Greg
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors
Thanks
Dic
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. H
might not even have designed the database
Compacting ID columns can be an important maintenance task, particularly t
avoid overloading ID columns that use smaller int types, such as smallint
Simple example
(1) You've got a table that uses smallint for the identity datatype. (3r
party app & you can't change this
(2) You have only 1 row, but they identity value is 32766, so you're runnin
out of space - the ID column will only accept one more row. (This happens i
cases where you archive data.
(3) You need to compact the data so that you can fit more rows in the tabl
by re-assigning keys to smaller values
(4) The only solution in this case is to do what Keith is asking.
This occurs often in apps that use identity, acquire lots of data an
perform regular archiving, leaving massive gaps in the ID ranges. It's als
not only common on SQL Server, it happens on all major DBMS
Regards
Greg Linwoo
SQL Server MV
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in messag
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl..
> this implies that you are placing meaning on the numbers
>> this is bad design
>> (For what it's worth
>> Greg Jackso
> PDX, Orego
>>|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> > this implies that you are placing meaning on the numbers.
> >> this is bad design.
> >> (For what it's worth)
> >> Greg Jackson
> > PDX, Oregon
> >>