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.

Replace Access with a .NET front end and SQL Backend solution.

My company currently has about 20-25 Ms Access Database that they want to
replace the FE with .net and the BE on SQL.

This will be done using Visual Studio 2005. Once the FE is converted to .net
and the BE is SQL they all will be accessed through our intranet (sharepoint).

I work in Ms Access and intermediate at VBA and just learing SQL through the
ENTERPRISE MANAGER SCREEN.
I am just now looking at what Visual Studio 2005 is, but can some one tell me
how this will all connect?

What is the typical route for this process?

Ms access to SQL - upsizing wizard or SQL importing???

Ms Access FE to .net - summarize how this is done in visual studio (user face
rebuilt) then placed on sharepoint?

Can anyone sum this up?

--
Message posted via http://www.sqlmonster.comAnns via SQLMonster.com (u22580@.uwe) writes:

Quote:

Originally Posted by

I work in Ms Access and intermediate at VBA and just learing SQL through
the ENTERPRISE MANAGER SCREEN.

Enterprise Manager is a tool that exists only in SQL 2000, and I assume
- and hope! - that you will implement your new solution on SQL 2005. The
tool in SQL 2005 is SQL Server Management Studio which replaces both
Enterprise Manager and Query Analyzer. As long as you work with SQL 2000,
QA should be your main tool, because it is from this tool you most easily
can run queries. EM may look a little like Access, so you may feel com-
fortable with that. However, SQL Server is very different from Access,
so best is to use a tool that forces you to unlearn a lot things.

Quote:

Originally Posted by

I am just now looking at what Visual Studio 2005 is, but can some one
tell me how this will all connect?
>
What is the typical route for this process?
>
>
Ms access to SQL - upsizing wizard or SQL importing???

Yes, there is an upsizing wizard. And the word that I have from people
who have worked extensively with both Access and SQL Server is that you
should stay away from it. Access and SQL Server are very different and
build on different mindset. You should more or less redesign your
application for SQL Server and .Net. This may seem like a lot more work
that you had expected, but it is likely to pay off in the long run with
lower maintenance costs, if you do it right from the start.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005 - SQL Server Managment Studio, thank you, when I said I was
currently learning I am learning unfortunately on 2000 b/c that is the study
material I bought and cannot afford to upgrade right now.

You are right with the SQL 2000 Enterprise edition, it is so user friendly
and that edition is all I know and still learning at best with that.

Yes, my company will be doing this on 2005 but for now, my learning is on
2000.
You see I work in a another department besides IT and between that job/baby
and small buisness, I am trying to get my DBA (2000) certification, I would
like the upgrade (2005) but do not have the funds to upgrade.
I have an opportunity to help with this conversion process and learn in
between, but I am trying to get a better grasp of this before sinking.

Does the 2005 Managment Studio look alot like Enterprise manager?
Answer:

Ms Access to SQL - I am assuming your answer would then be exporting/dts tool
from SQL to pull over all these db's?
Answer:

Erland, tell me once the db's are pull over to SQL, now I have Visual Studio
2005 software open - what steps are taken in this software to rebuilt the
user faces and then how is it then put on SHAREPOINT????
ANSWER:

Erland Sommarskog wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I work in Ms Access and intermediate at VBA and just learing SQL through
>the ENTERPRISE MANAGER SCREEN.

>
>Enterprise Manager is a tool that exists only in SQL 2000, and I assume
>- and hope! - that you will implement your new solution on SQL 2005. The
>tool in SQL 2005 is SQL Server Management Studio which replaces both
>Enterprise Manager and Query Analyzer. As long as you work with SQL 2000,
>QA should be your main tool, because it is from this tool you most easily
>can run queries. EM may look a little like Access, so you may feel com-
>fortable with that. However, SQL Server is very different from Access,
>so best is to use a tool that forces you to unlearn a lot things.
>

Quote:

Originally Posted by

>I am just now looking at what Visual Studio 2005 is, but can some one
>tell me how this will all connect?
>>
>What is the typical route for this process?
>>
>Ms access to SQL - upsizing wizard or SQL importing???

>
>Yes, there is an upsizing wizard. And the word that I have from people
>who have worked extensively with both Access and SQL Server is that you
>should stay away from it. Access and SQL Server are very different and
>build on different mindset. You should more or less redesign your
>application for SQL Server and .Net. This may seem like a lot more work
>that you had expected, but it is likely to pay off in the long run with
>lower maintenance costs, if you do it right from the start.
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200607/1|||Anns via SQLMonster.com (u22580@.uwe) writes:

Quote:

Originally Posted by

SQL 2005 - SQL Server Managment Studio, thank you, when I said I was
currently learning I am learning unfortunately on 2000 b/c that is the
study material I bought and cannot afford to upgrade right now.

You can download the Evaluation Edition of SQL 2005 from
http://www.microsoft.com/downloads/...FA7F-C094-49A2-
A050-2D07993566EC&displaylang=en.

Even if your study material only covers SQL 2000, I still think you
should work with SQL 2005.

Quote:

Originally Posted by

You are right with the SQL 2000 Enterprise edition, it is so user friendly
and that edition is all I know and still learning at best with that.

Enterprise Manager != Enterprise Edition. Enterprise Manager is available
in all editions of SQL 2000, except for the desktop version MSDE. The
difference between the editions lies in what features that are available,
and what purpose the edition is licensed for.

Quote:

Originally Posted by

Does the 2005 Managment Studio look alot like Enterprise manager?

It's not completely different, but there are also considerable differences.
In the end, you will only do yourself a disservice by learning a tool
that you will not work with.

But Enterprise Manager and SQL Server Management Studio are just the
tools. These are not what you should focus your learning on. You should
learn how to create databases, tables and indexes, and how to write
queries. You may think that you know SQL from Access, but as I said,
there are considerable differences. In general, SQL Server offers much
more powerful constructs, but there also features from Access that
are completely missing. A classic example is dynamic crosstabs.

Quote:

Originally Posted by

Ms Access to SQL - I am assuming your answer would then be exporting/dts
tool from SQL to pull over all these db's?

Maybe. As I said, I think you should consider a major redesign, at
least in places where you have some quirks in Access, or in places
where SQL Server offers better solutions. Then again, even if you go
for a new schema, it may of course be a good idea bring over the
Access databases to SQL Server, so that you can work with them locally.
And for that end SQL Server Integration Services (which is the successor
to DTS in SQL 2005) may be a good choice. (I've never worked with
neither DTS nor SSIS, so I don't really know.)

Quote:

Originally Posted by

Erland, tell me once the db's are pull over to SQL, now I have Visual
Studio 2005 software open - what steps are taken in this software to
rebuilt the user faces and then how is it then put on
SHAREPOINT????

Since you are migrating from Access to .Net, I would assume that you
more or less rewrite the front-end code entirely, but you should ask in a
..Net forum about that.

Sharepoint? I know there is a product with that name, but I have very
little idea what it's good for. So I cannot answer that question.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||A great walk thru / how to on the MS Access to SQL Server 2005 was
blogged. I tried it and made my life much easier.

http://cfpayne.wordpress.com/2006/0...ql-server-2005/
Precia

Anns via SQLMonster.com wrote:

Quote:

Originally Posted by

My company currently has about 20-25 Ms Access Database that they want to
replace the FE with .net and the BE on SQL.
>
>
This will be done using Visual Studio 2005. Once the FE is converted to .net
and the BE is SQL they all will be accessed through our intranet (sharepoint).
>
>
I work in Ms Access and intermediate at VBA and just learing SQL through the
ENTERPRISE MANAGER SCREEN.
I am just now looking at what Visual Studio 2005 is, but can some one tell me
how this will all connect?
>
What is the typical route for this process?
>
>
Ms access to SQL - upsizing wizard or SQL importing???
>
>
>
Ms Access FE to .net - summarize how this is done in visual studio (user face
rebuilt) then placed on sharepoint?
>
>
Can anyone sum this up?
>
--
Message posted via http://www.sqlmonster.com

repeated corruption

Our company create's software with 3-4 Gb databases underneath. When a client has a corrupt database, 80-90% of the time the corruption occurs in the same index on the same table. (By corruption I mean DBCC CheckDB shows consistency or allocation e
rrors) The index itself is for only a single field but the table is one of our larger ones (1 million records) and the data inside the table does get changed a lot. Our solution is to drop and recreate the index which works most of the time.
Does anyone have any ideas on why this one index is always corrupting?
Is this index on a separate drive array than most? If it gets updated a lot
then it is more likely to see corruption if it is going to occur. Usually
repeated corruption is due to faulty hardware. You most likely have a drive
or controller getting ready to go.
Andrew J. Kelly SQL MVP
"Rod Harten" <Rod Harten@.discussions.microsoft.com> wrote in message
news:62407700-DE8F-4A5B-BC66-F69AA884F580@.microsoft.com...
> Our company create's software with 3-4 Gb databases underneath. When
a client has a corrupt database, 80-90% of the time the corruption occurs in
the same index on the same table. (By corruption I mean DBCC CheckDB shows
consistency or allocation errors) The index itself is for only a single
field but the table is one of our larger ones (1 million records) and the
data inside the table does get changed a lot. Our solution is to drop and
recreate the index which works most of the time.
> Does anyone have any ideas on why this one index is always
corrupting?
|||We set up the data server so the entire database runs on the same drive.
The table holding the index has more changes than any other table in our database - say 50% of the activity. However, the index itself is for a single int field that doesn't get updated very often after the data is added.
Another point is that copies of our software and the database are installed at our client sites. (Our clients are radio and TV stations.) Each site has its own hardware and database. At a guess, we have had 20 different sites (and different sets of har
dware) get corruption in this index. The problem is not recurring - once we drop and recreate the index at a site the problem goes away. I think we have had two sites that had the index go corrupt more than once in the span of 2 years.
Because the index is getting corrupt on different hardware, I assume it is not a hardware problem in most instances. Also, the fact that the problem goes away once it is fixed implies that it is not a hardware issue.
The only thing distinct about the index itself (and I admit this is grasping at straws) is that it's name appears alphabetically first in the list of 11 indexes on that table.
Thank you for you efforts!
Rod Harten
"Andrew J. Kelly" wrote:

> Is this index on a separate drive array than most? If it gets updated a lot
> then it is more likely to see corruption if it is going to occur. Usually
> repeated corruption is due to faulty hardware. You most likely have a drive
> or controller getting ready to go.
> --
> Andrew J. Kelly SQL MVP
>
> "Rod Harten" <Rod Harten@.discussions.microsoft.com> wrote in message
> news:62407700-DE8F-4A5B-BC66-F69AA884F580@.microsoft.com...
> a client has a corrupt database, 80-90% of the time the corruption occurs in
> the same index on the same table. (By corruption I mean DBCC CheckDB shows
> consistency or allocation errors) The index itself is for only a single
> field but the table is one of our larger ones (1 million records) and the
> data inside the table does get changed a lot. Our solution is to drop and
> recreate the index which works most of the time.
> corrupting?
>
>
|||From your original post and this comment:[vbcol=seagreen]
corrupting?
it sounded as if you had a single index that keeps getting corrupted. Is it
possible that each of the db's were created from a restored copy of the same
bases database? In that case the corruption could have been there in the
first place. Other than that I have not heard of a situation where an index
gets corrupted due to it's name type etc. Sorry not sure what else to say as
these days corruption is almost always due to some sort of hardware failure,
power glitch etc.
Andrew J. Kelly SQL MVP
"Rod Harten" <RodHarten@.discussions.microsoft.com> wrote in message
news:51AF73C7-F271-4A36-AEF2-DB7333081FD6@.microsoft.com...
> We set up the data server so the entire database runs on the same drive.
> The table holding the index has more changes than any other table in our
database - say 50% of the activity. However, the index itself is for a
single int field that doesn't get updated very often after the data is
added.
> Another point is that copies of our software and the database are
installed at our client sites. (Our clients are radio and TV stations.)
Each site has its own hardware and database. At a guess, we have had 20
different sites (and different sets of hardware) get corruption in this
index. The problem is not recurring - once we drop and recreate the index
at a site the problem goes away. I think we have had two sites that had the
index go corrupt more than once in the span of 2 years.
> Because the index is getting corrupt on different hardware, I assume it is
not a hardware problem in most instances. Also, the fact that the problem
goes away once it is fixed implies that it is not a hardware issue.
> The only thing distinct about the index itself (and I admit this is
grasping at straws) is that it's name appears alphabetically first in the
list of 11 indexes on that table.[vbcol=seagreen]
> Thank you for you efforts!
> Rod Harten
> "Andrew J. Kelly" wrote:
lot[vbcol=seagreen]
Usually[vbcol=seagreen]
drive[vbcol=seagreen]
When[vbcol=seagreen]
occurs in[vbcol=seagreen]
shows[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]

Repeate group values in Matrix

Hi,

I am using matrix to make a report. I have company name in the row group and user name in the details field. When I run the report I only see the company name once and each user of that company is displayed in a separate row. Then next company name and its users... I wanted to know if there is a way to display company name next to the user name? I know it can be done by using "table" but I can't use table because in this report there are other fields can only be done in a matrix. So, please let me know if there are any settings in matrix which I can use to display my company name next to each user of that company. Please ask me any thing if the question is not clear. Thanks a lot for the help.

-Rohit

If someone have any idea about this problem please let me know. I would really appreciate that. Thanks.|||

Hi Rohit,

it sounds like you have a row group based on CompanyName, and a nested row group based on UserName. You could do the following steps:

1. Clear out the expression in the matrix header of the company group (which would display the company name) and resize that header to be relatively small
2. Modify the matrix header expression of the user name group to e.g.: =Fields!CompanyName.Value & " - " & Fields!UserName.Value

This should result in the company name being shown for every user name.

-- Robert

|||

Hi Robert,

This sounds like a solution that I had also been looking for but my users want to export to excel and do some things from there. If I merge my two values in that cell won't it create problems for them?

|||Thanks a lot Robert.|||

I figured out how to do it w/o having to concatinate the two into one column. Follow all of the directions that Robert gave us--take expression out of first column and minimize as small as possible. Only put the company name in the second grouping column expression. Then go into view datasets and drag your user id directly after the second column. It will appear in a column by itsself. That way they are still in their own columns and not concatinated-yet you still have the same results.

Repair SQL database that has been marked suspect

I woke up this morning to find my company's SQL database inaccessible and marked suspect.

I have used various t-sql commands to bring it into Emergency mode.

Now it is labelled Loading\Offline\Read-Only\Emergency Mode.

I'm using SQL Server 2000

I do not have a backup and I need to get the database up and running as soon as yesterday.

Help

first of all what version of SQL Server are u using?

Second ..There can be many reason for database being suspect.Please see in to the SQL Server error log and send if you see something unusual/error .It is very importent to look in to error log before anything can be done

run this on master

select status from sysdatabases and send it.

Thanks

|||

Recommended actions for corrupt or suspect databases

http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp

AMB

|||

it is very tough to get a suspect db back. try this link http://www.myitforum.com/articles/18/view.asp?id=7381

Madhu

Rendering to PDF format

Hi everyone!
I have a problem with rendering report to PDF format. I created report using
my own report designer, developed for our company commercial product. There
is no problem to render report to some other format like HTML or IMAGE. But
when I render report to PDF format some of information simply disappears from
result report. There are empty spaces instead of printed values. Are there
any special definitions I have to make to each object, and what are the
different between, for example, HTML and PDF format from SQL reporting
services point of view.
Thanks!
Igor.The same thing is happening to me when I render a report with an embedded
image to pdf. I can either open or save the pdf, but upon reviewing the file
pieces of the report simply disappear. When I click to print, it is
completely blank.
"Igor" wrote:
> Hi everyone!
> I have a problem with rendering report to PDF format. I created report using
> my own report designer, developed for our company commercial product. There
> is no problem to render report to some other format like HTML or IMAGE. But
> when I render report to PDF format some of information simply disappears from
> result report. There are empty spaces instead of printed values. Are there
> any special definitions I have to make to each object, and what are the
> different between, for example, HTML and PDF format from SQL reporting
> services point of view.
> Thanks!
> Igor.

Rendering reports to Word format

Currently my company inserts graphs from Excel into a Word document. They
then update the Word document with explanations about their analysis.
The requirement is for the graphs in this Word document to come from
Reporting Services 2000. Analysts then need to be able to update the text in
the document.
The SoftArtisans product appears to provide the required functionality,
however, my company has very strict guidelines about introducing additional
software into the organisation.
Do you have any ideas about how to render the Word document, including
graphs and sub-reports using Reporting Services 2000?
My thanks in advanceI understand your dilemma, but I strongly suggest you attempt to leverage
commercial software that already does the job before trying to roll your
own.
If you have no other choice, get your hands on Office 2003 Interop
Assemblies. You can then render the report(s) through web service calls
(take a look at the Render() method). There are other methods that allow
you to get a list of the image streams for the report. You'll need to get
to the image streams and pull the chart image from the web service. Then
you can write the image(s) to the word document. This is one way to do it.
-Tim
"SuzyM" <SuzyM@.discussions.microsoft.com> wrote in message
news:5FA762D2-BAE6-4471-A28A-F01B3D0FA44E@.microsoft.com...
> Currently my company inserts graphs from Excel into a Word document. They
> then update the Word document with explanations about their analysis.
> The requirement is for the graphs in this Word document to come from
> Reporting Services 2000. Analysts then need to be able to update the text
> in
> the document.
> The SoftArtisans product appears to provide the required functionality,
> however, my company has very strict guidelines about introducing
> additional
> software into the organisation.
> Do you have any ideas about how to render the Word document, including
> graphs and sub-reports using Reporting Services 2000?
> My thanks in advance