Database Overwritten: level

Showing posts with label level. Show all posts

Tuesday, March 27, 2012

Database Mirroring Transport is disabled in the endpoint configuration.

Hi.

I am getting this error.

Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.

Someone please help me.It's urgent.

I am using same PC with 2 different intsances.

This is how i have done it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@.#56'

-- CREATE CERTIFICATE PRINCIPAL_CERT

CREATE CERTIFICATE PRINCIPAL_CERT

WITH SUBJECT = 'PRINCIPAL CERTIFICATE',

START_DATE = '03/07/2006',

EXPIRY_DATE = '01/01/2010'

-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM

CREATE ENDPOINT EPMIRRORING

STATE = STARTED

AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,

AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)

Here is the of Event Log.

Date,Source,Severity,Message
08/03/2006 15:54:35,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 15:54:35,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 15:52:57,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 15:52:55,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:14:54,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:14:54,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 14:14:37,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 14:14:35,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:46,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:43,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:41,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:38,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:36,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:34,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:33,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:32,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:30,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:15,spid54,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:03:15,spid54,Unknown,Server is listening on [ 'any' <ipv4> 5022].

Any solution ?
Thanks
Mihir

Are you using SQL Server 2005 RTM or SP1?|||I am having the same problem and I running sql server 2005 sp1|||

ascetic wrote:

08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]

This indicates either that one host is configured to use CERTIFICATE authentication, the other is configured to use WINDOWS authentication, either one is configured to use WINDOWS KERBEROS and the other is using WINDOWS NTLM.

HTH,
~ Remus

|||I'm having this issue too, though mine says "State 22" at the end.

Remus, how would I know which the hosts are configured for and how can we change this? All of my servers are running as the same user in an AD domain so they should be able to talk fine?

thanks
Pete

Database Mirroring Transport is disabled in the endpoint configuration.

Hi.

I am getting this error.

Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.

Someone please help me.It's urgent.

I am using same PC with 2 different intsances.

This is how i have done it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@.#56'

-- CREATE CERTIFICATE PRINCIPAL_CERT

CREATE CERTIFICATE PRINCIPAL_CERT

WITH SUBJECT = 'PRINCIPAL CERTIFICATE',

START_DATE = '03/07/2006',

EXPIRY_DATE = '01/01/2010'

-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM

CREATE ENDPOINT EPMIRRORING

STATE = STARTED

AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,

AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)

Here is the of Event Log.

Any solution ?
Thanks
Mihir

Are you using SQL Server 2005 RTM or SP1?|||I am having the same problem and I running sql server 2005 sp1|||

ascetic wrote:

08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]

HTH,
~ Remus

|||I'm having this issue too, though mine says "State 22" at the end.

Remus, how would I know which the hosts are configured for and how can we change this? All of my servers are running as the same user in an AD domain so they should be able to talk fine?

thanks
Pete
sql

Thursday, March 22, 2012

Database Mirroring - Multiple databases

I know database mirroirng is on the database level but is it possible to fail
all the databses in the server if one databses fails.
If I want to mirror db1 and db2 serverA to db1 and db2 in serverb. If db1 in
servera fails I want db2 in servera fails therefore both database will
rollover to serverb.No, first off database mirroring is not scalable to a large number of
databases. Secondly what will happen is each mirroring pair will failover on
its own schedule. All of them should fail over within several seconds
though.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bashir" <bashir@.discussions.microsoft.com> wrote in message
news:1379FB0F-C815-4DEB-B0B0-CD25F03693ED@.microsoft.com...
>I know database mirroirng is on the database level but is it possible to
>fail
> all the databses in the server if one databses fails.
> If I want to mirror db1 and db2 serverA to db1 and db2 in serverb. If db1
> in
> servera fails I want db2 in servera fails therefore both database will
> rollover to serverb.
>

Database Mirroring - Multiple databases

I know database mirroirng is on the database level but is it possible to fai
l
all the databses in the server if one databses fails.
If I want to mirror db1 and DB2 serverA to db1 and DB2 in serverb. If db1 in
servera fails I want DB2 in servera fails therefore both database will
rollover to serverb.No, first off database mirroring is not scalable to a large number of
databases. Secondly what will happen is each mirroring pair will failover on
its own schedule. All of them should fail over within several seconds
though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bashir" <bashir@.discussions.microsoft.com> wrote in message
news:1379FB0F-C815-4DEB-B0B0-CD25F03693ED@.microsoft.com...
>I know database mirroirng is on the database level but is it possible to
>fail
> all the databses in the server if one databses fails.
> If I want to mirror db1 and DB2 serverA to db1 and DB2 in serverb. If db1
> in
> servera fails I want DB2 in servera fails therefore both database will
> rollover to serverb.
>sql

Wednesday, March 21, 2012

Database mirror error Msg 1447, Level 16, State 21, Line 1

we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.

ALTER DATABASE R3P
SET PARTNER resume

the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.

Hi David,

Can you please take a look at the SQL Server errorlog (on both the principal and mirror) and post the errors that are reported there at the time the command was executed.

Also, what does "SELECT * FROM sys.database_mirroring" return on each partner?

Thanks,

Kaloian.

|||Hi Kaloian,

thank you for your reply.

now the mirrow can running normally after restart the primary server.i guess that the server's issue caused the mirror can't send log to the mirror server.

when i run the following query in both server:
select * from sys.dm_db_mirroring_connections
two rows return in primary server and one row return in mirror server.so i restart the primary server after work time,then the mirror resume and about 50G log has been resent successfully.

in the error log,i can't find the ralted error except:
The alert for 'unrecovered log' has been raised. The current value of '30' surpasses the threshold '10'.
and
An I/O operation initiated by the Registry failed unrecoverably. The Registry could not read in, or write out, or flush, one of the files that contain the system's image of the Registry.

Server information:
OS:win 2003 enterprise SP1
SQL:SQL 2005 enterprise SP1
CPU:16
Memory:16G
disk:about 16T

Thanks.

Tuesday, February 14, 2012

Database Level Triggers in SQL 2000? File Writing?

Hello,

Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?

Thanks.

No, you need to do it individually table by table.

The same in SQL 2005.

Also think how your 'log' table will look like as layout of all tables is different

|||

bmains:
s there any ability to do database-level triggers in SQL 2000?

I'm afraid the database level triggers are introduced in SQL 2005 only. So, you can't create a database level trigger in SQL 2000. See if below link can be useful.

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

Hope this will help.

Database Level trigger is possible in SQL 2000?

Dear Freinds,

we are having an requirement to log the unauthorized backend update on database.Is there any other way ,apart from going and creating the triggers at each table to log the backend update.

Note :Is Database base level trigger is possible on SQL Server 2000?

Looking for your reply.

With Cheers :beer: ,
Sathesh.MNo, database-level triggers are not implemented in 2000.

And the way to handle unauthorized backend updates is with proper security.|||Dear Blindman,

We are having all level of database security,there are 40 support people
working on this pro.,we want to track there updation also.

With Regards,
Sathesh.m|||You'll need to put triggers on the individual tables.

Every table I create contains columns for MODIFIED and MODIFIER, with an update trigger to set them to GETDATE() and SUSER_SNAME().

Database level setting : Allow user input chinese character~

Dear all,

To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?

I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..

Any Idea?

Thanks.

You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.

There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.

There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.

|||

Hi,

you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)

SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Database Level Security Doubt in SQL6.5

In our SQL Server6.5, I drop a user from in one database using ‘sp_dropuse
r’
command, still with that specific login; I could able to access the database
.
It’s working fine in other database, but its not working only in one datab
ase.
For this login, there is no entries in Sysusers table of the respective
database.
Please help usWhat does 'SELECT USER' return? It may be that the login is mapped to a
different user in that database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dineshkumar" <Dineshkumar@.discussions.microsoft.com> wrote in message
news:0B35489E-175D-439D-8DF1-295B0B51AEE1@.microsoft.com...
> In our SQL Server6.5, I drop a user from in one database using
> 'sp_dropuser'
> command, still with that specific login; I could able to access the
> database.
> It's working fine in other database, but its not working only in one
> database.
> For this login, there is no entries in Sysusers table of the respective
> database.
> Please help us

Database level lock

Hi,

How do I lock entire database? I want an exclusive lock on the db by

a user
who is the dbo of that database only (not sa

user).

The

scenario is we have a web application and each week we need to do data

uploads (with etl). During this upload, the users accessing the website

should not be able to read data. This is why I want database lock.

Now

the catch here is, the application access the data using user say abc.

Abc is dbo for that database and the etl is also done by abc login. So

will db locking help in this case as the website can also read the data

being a abc user?

Thanks,
Tanmaya

You can use sp_dboption system stored procedure to put database in dbo use mode.

dbo use only

When true, only the database owner can use the database.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/92d4714b-95bf-46af-b05e-759a7164f937.htm

Thanks,

Deepak Kumar

|||You can use the ALTER DATABASE Name SET RESTRICTED_USER command to switch the database to administrative mode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database level lock

Hi,

How do I lock entire database? I want an exclusive lock on the db by

a user
who is the dbo of that database only (not sa

user).

The scenario is we have a web application and each week we need to do data uploads (with etl). During this upload, the users accessing the website should not be able to read data. This is why I want database lock.

Now the catch here is, the application access the data using user say abc. Abc is dbo for that database and the etl is also done by abc login. So will db locking help in this case as the website can also read the data being a abc user?

Thanks,
TanmayaYou can use the ALTER DATABASE Name SET RESTRICTED_USER command to switch the database to administrative mode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database level lock

Actually, the scenario is we have a web application and each week we need to
do data uploads (with etl). During this upload, the users accessing the
website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So will
db locking help in this case as the website can also read the data being a
abc user?
What can be done here?How do you do data upload? DTS,Bulk Insert what?
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:elzYtFttGHA.3568@.TK2MSFTNGP05.phx.gbl...
> Actually, the scenario is we have a web application and each week we need
> to do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So
> will db locking help in this case as the website can also read the data
> being a abc user?
> What can be done here?
>|||Tanmaya Kulkarni wrote:
> Actually, the scenario is we have a web application and each week we need to
> do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So will
> db locking help in this case as the website can also read the data being a
> abc user?
> What can be done here?
>
One option would be to have the web app connect as user 'abc', and the
ETL process as user 'xyz'. When starting the ETL process, revoke access
from user 'abc', and restore it after ETL finishes.
Another option would be to create a new database, do the ETL into that
database, then detach/reattach the "new" database as the "live" database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database level lock

Yes with DTSHave you tried to issue
ALTER DATABASE dbname
SET RESTRICTED_USER
and then run the DTS Package
When the user reads the data it does not block writes ,sql server obtains a
share lock ,so anyway try running the above commad and see what si going on
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:%23oazDUttGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Yes with DTS
>

Database level lock

If I understand you correctly, you =could do the following to limit the database to dbo (and sa) roles only.
ALTER DATABASE =MyDatabase
SET RESTRICTED_USER
And then when finished, return to =regular use.
ALTER DATABASE =MyDatabase
SET MULTI_USER
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"Tanmaya Kulkarni" wrote in message news:%23bdnwHstGHA.3264@.TK2MSFTNGP03.phx.gbl...> Hi,> => How do I lock entire database? I want an exclusive lock on the db by a user => who is the dbo of that database only (not sa user).> > Thanks,> Tanmaya > >

--=_NextPart_000_09A5_01C6B68C.705E4220--

Database level lock

Hi,
How do I lock entire database? I want an exclusive lock on the db by a user
who is the dbo of that database only (not sa user).
Thanks,
TanmayaHmm, take a look at
ALTER DATABASE ...SET command along with SINGLE_USER | RESTRICTED_USER |
MULTI_USER
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:%23bdnwHstGHA.3264@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How do I lock entire database? I want an exclusive lock on the db by a
> user who is the dbo of that database only (not sa user).
> Thanks,
> Tanmaya
>|||If I understand you correctly, you could do the following to limit the datab
ase to dbo (and sa) roles only.
ALTER DATABASE MyDatabase
SET RESTRICTED_USER
And then when finished, return to regular use.
ALTER DATABASE MyDatabase
SET MULTI_USER
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message news:%23bdnwHstGHA.3
264@.TK2MSFTNGP03.phx.gbl...
> Hi,
>
> How do I lock entire database? I want an exclusive lock on the db by a use
r
> who is the dbo of that database only (not sa user).
>
> Thanks,
> Tanmaya
>
>|||Actually, the scenario is we have a web application and each week we need to
do data uploads (with etl). During this upload, the users accessing the
website should not be able to read data. This is why I want database lock.
Now the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So will
db locking help in this case as the website can also read the data being a
abc user?
What can be done here?|||How do you do data upload? DTS,Bulk Insert what?
"Tanmaya Kulkarni" <tanmaya_kulkarni@.persistent.co.in> wrote in message
news:elzYtFttGHA.3568@.TK2MSFTNGP05.phx.gbl...
> Actually, the scenario is we have a web application and each week we need
> to do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So
> will db locking help in this case as the website can also read the data
> being a abc user?
> What can be done here?
>|||Tanmaya Kulkarni wrote:
> Actually, the scenario is we have a web application and each week we need
to
> do data uploads (with etl). During this upload, the users accessing the
> website should not be able to read data. This is why I want database lock.
> Now the catch here is, the application access the data using user say abc.
> Abc is dbo for that database and the etl is also done by abc login. So wil
l
> db locking help in this case as the website can also read the data being a
> abc user?
> What can be done here?
>
One option would be to have the web app connect as user 'abc', and the
ETL process as user 'xyz'. When starting the ETL process, revoke access
from user 'abc', and restore it after ETL finishes.
Another option would be to create a new database, do the ETL into that
database, then detach/reattach the "new" database as the "live" database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Database level lock

Database level change

I have many tables and in those i require to change some data. Say
from ARCA to ARCAEX. I am sure that the string is unique in the sense
there will be no ARCAABC. So what do i do change by not manually
needing to search in each table and the whole database and still can
be sure that the changes have taken place. Please help
Regards,
RajeshIf that, what I think you problem is, is true, then i think you can
try the next:
Make a cursor loop trough the tables in de sysobjects and make in
every loop a text string that contains a update statement for the
current table and execute that string. That works only when every
table has the same column names ofcourse...

For SQL Server 2000 something like:
DECLARE @.Table_name sysname
DECLARE @.cmd1 varchar(1000)
DECLARE c_table CURSOR
FOR SELECT [Name], FROM sysobjects WHERE [Name] LIKE ('ARC%') ORDER BY
[Name]
OPEN c_table
FETCH NEXT FROM c_table INTO @.Table_name
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
SELECT @.cmd1 = 'UPDATE ' + @.Table_name + ' SET columnname =
''value'''
EXEC (@.cmd1)
FETCH NEXT FROM c_table INTO @.Table_name
END

CLOSE c_table
DEALLOCATE c_table

raj_chins@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.posting.google.com>...
> I have many tables and in those i require to change some data. Say
> from ARCA to ARCAEX. I am sure that the string is unique in the sense
> there will be no ARCAABC. So what do i do change by not manually
> needing to search in each table and the whole database and still can
> be sure that the changes have taken place. Please help
> Regards,
> Rajesh|||hi
i have come accross a better solution....we can write a stored proc
and run it whenever required. it may take more time for really big
databases.

*******************************
the stored proc is written
******************************
CREATE PROC SearchAndReplace
(
@.SearchStr nvarchar(100),
@.ReplaceStr nvarchar(100)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128),
@.SearchStr2 nvarchar(110), @.SQL nvarchar(4000), @.RCTR int
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
SET @.RCTR = 0

WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) >
@.TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@.TableName, 2)
ANDTABLE_NAME= PARSENAME(@.TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @.ColumnName
)

IF @.ColumnName IS NOT NULL
BEGIN
SET @.SQL='UPDATE ' + @.TableName +
' SET ' + @.ColumnName
+ ' = REPLACE(' + @.ColumnName + ', '
+ QUOTENAME(@.SearchStr, '''') + ', ' + QUOTENAME(@.ReplaceStr,
'''') +
') WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
EXEC (@.SQL)
SET @.RCTR = @.RCTR + @.@.ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@.RCTR AS varchar) + ' occurence(s)' AS
'Outcome'
END

************************************************
the stored can be simply called like this
*************************************************

--To replace all occurences of 'ABC' with 'XYZ':
EXEC SearchAndReplace 'ABC', 'XYZ'
GO

napel25@.hotmail.com (EtN) wrote in message news:<ddaf11e.0308050435.21c5971a@.posting.google.com>...
> If that, what I think you problem is, is true, then i think you can
> try the next:
> Make a cursor loop trough the tables in de sysobjects and make in
> every loop a text string that contains a update statement for the
> current table and execute that string. That works only when every
> table has the same column names ofcourse...
> For SQL Server 2000 something like:
> DECLARE @.Table_name sysname
> DECLARE @.cmd1 varchar(1000)
> DECLARE c_table CURSOR
> FOR SELECT [Name], FROM sysobjects WHERE [Name] LIKE ('ARC%') ORDER BY
> [Name]
> OPEN c_table
> FETCH NEXT FROM c_table INTO @.Table_name
> WHILE(@.@.FETCH_STATUS <> -1)
> BEGIN
> SELECT @.cmd1 = 'UPDATE ' + @.Table_name + ' SET columnname =
> ''value'''
> EXEC (@.cmd1)
> FETCH NEXT FROM c_table INTO @.Table_name
> END
> CLOSE c_table
> DEALLOCATE c_table
>
> raj_chins@.rediffmail.com (Rajesh Garg) wrote in message news:<14215add.0308042113.10c410e5@.posting.google.com>...
> > I have many tables and in those i require to change some data. Say
> > from ARCA to ARCAEX. I am sure that the string is unique in the sense
> > there will be no ARCAABC. So what do i do change by not manually
> > needing to search in each table and the whole database and still can
> > be sure that the changes have taken place. Please help
> > Regards,
> > Rajesh

Tuesday, March 27, 2012

Database Mirroring Transport is disabled in the endpoint configuration.

Database Mirroring Transport is disabled in the endpoint configuration.

Thursday, March 22, 2012

Database Mirroring - Multiple databases

Database Mirroring - Multiple databases

Wednesday, March 21, 2012

Database mirror error Msg 1447, Level 16, State 21, Line 1

Tuesday, February 14, 2012

Database Level Triggers in SQL 2000? File Writing?

Database Level trigger is possible in SQL 2000?

Database level setting : Allow user input chinese character~

Database Level Security Doubt in SQL6.5

Database level lock

Database level lock

Database level lock

Database level lock

Database level lock

Database level lock

Database level lock

Database level change

Database Overwritten

Blog Archive

About Me