Renaming a 2000 SQL Server

Hi
I have a client who has a SQL 2000 Server. He also has
another server that he would like to use as his SQL 2000
Server. I would like to take his current server, Wonder
and take it offline. Then take the new server, Menthal,
and rename it Wonder, then take Wonder and rename it
Menthal. The SQL server will still be named Wonder,
evnthough it is a completely different server. Is there
anything I need to consider when attempting this change?
thank you.These links should help:
http://vyaskn.tripod.com/administration_faq.htm#q5
http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Bonnie" <bonnie.millar@.gems9.gov.bc.ca> wrote in message
news:2c0801c3fcaf$b967a680$a301280a@.phx.gbl...
Hi
I have a client who has a SQL 2000 Server. He also has
another server that he would like to use as his SQL 2000
Server. I would like to take his current server, Wonder
and take it offline. Then take the new server, Menthal,
and rename it Wonder, then take Wonder and rename it
Menthal. The SQL server will still be named Wonder,
evnthough it is a completely different server. Is there
anything I need to consider when attempting this change?
thank you.

renamed table [need to quickly update 200 SPs]

Hi
I have had to rename one of my tables (sp_Rename []) I now need to update
all my stored procedures, I want to some how use syscomments (maybe) and
loop through my SPs using REPLACE command to change the references to the
old table name
Any help / advise appreciated
LukeHi,
One solution is, you can generate a script with all the SPs available.
Open some text editor and use Replace All option giving your old and new
File Name.
Run the script again in Query Analyser
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Luke Ward" wrote:

> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>
>|||Don't fiddle with system tables ever! I would hope you have some
sort of code mgmt (ie VSS) in place where your master copies of
all procs are kept. That's where your changes should be made, tested
against a copy of your production data, and then applied to the
production database when you're satisfied with the results.
I think your best bet is to manually change the table name within
the procs. If you know a scripting language (like Perl), you could
write a script to blow through the procs and make the change and then
compile them back into the database.. That's what I would do.
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:e6GC%23xsWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>

Rename Table name

Hi
I don't want to change datas in a table which I use, but want to change only the table's name with T_SQL statments.

As you know there are SPs for rename SP or view, but I can not find such SP for table.

If you know about it,
or have the another good method to rename table,
tell me please.

Thanks for reading.sp_rename should work

eg
EXEC sp_rename 'customers', 'custs' (taken out of sp_rename in BOL)|||Now I do it as you said, then I can!
thanks.

rename file using cmdexec in SQL Server Agent job.

Hi:
I am using the cmdexec step in the SQL Server agent to rename a file. Below
is the command I use to rename the file in the step.
ren c:\test1.bak test2.bak
However when I run the job the job fails with the following error.
The process could not be created for step 1 of job
0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the file
specified). The step failed.
What is wrong with the command?. the command works perfectly from dos prompt
but refuses to work from the job. I also tried
ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
xp_cmdshell and hence want to use the cmdexec command. Can anyone please let
me know what I am doing wrong and what is\are the correct commands when
executed from a SQL Server Agent job step?.
Thanks
MWhen you run it from the command prompt is it on your local machine or the
server? xp_cmdshell is always executed on the server not the client.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Hi:
> I am using the cmdexec step in the SQL Server agent to rename a file.
> Below is the command I use to rename the file in the step.
> ren c:\test1.bak test2.bak
> However when I run the job the job fails with the following error.
> The process could not be created for step 1 of job
> 0xDD5759D5B7103041B7F0BD736B442D1D (reason: The system cannot find the
> file specified). The step failed.
> What is wrong with the command?. the command works perfectly from dos
> prompt but refuses to work from the job. I also tried
> ren "c:\test1.bak test2.bak" and this also fails. I dont want to use
> xp_cmdshell and hence want to use the cmdexec command. Can anyone please
> let me know what I am doing wrong and what is\are the correct commands
> when executed from a SQL Server Agent job step?.
> Thanks
> M
>|||It is on my local machine which is running SQL Server 2000.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
> When you run it from the command prompt is it on your local machine or the
> server? xp_cmdshell is always executed on the server not the client.
> --
> Andrew J. Kelly SQL MVP
>
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eJriWN7SGHA.5656@.TK2MSFTNGP11.phx.gbl...
>|||Andrew:
I figured it out. Thanks for making me think. Here is the correct command.
ren "c:\test20.bak" test1.bak
Thats why I adore SQL MVPs. They make SQL guys think logically.
Best Regards
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
> It is on my local machine which is running SQL Server 2000.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTMlOI8SGHA.4264@.TK2MSFTNGP11.phx.gbl...
>|||The double quotes usually are not required unless you have spaces in the
path or file name. In either case I am glad you fixed it.
Andrew J. Kelly SQL MVP
"Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
news:eJTtLc8SGHA.736@.TK2MSFTNGP12.phx.gbl...
> Andrew:
> I figured it out. Thanks for making me think. Here is the correct command.
> ren "c:\test20.bak" test1.bak
> Thats why I adore SQL MVPs. They make SQL guys think logically.
> Best Regards
> "Meher" <NOSPAM_mmsagar@.hotmail.com> wrote in message
> news:eUC6IY8SGHA.4952@.TK2MSFTNGP09.phx.gbl...
>

removing rows from a replicated table

Hi
I have a subscription which has a table and some rows which I cannot
delete - they don't have a rowguid value set for them - when I try and
delete I get a message about 'cannot insert a null value into column 'guid'.
How do I tidy tihss up ?
thanks
Is this a merge subscription? Or Immediate Updating?
It sounds like the triggers are not executing correctly.
The best way for you to solve this would be to reinitialize and regenerate
and distribute your snapshot.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:%23yyb9VNaEHA.4048@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a subscription which has a table and some rows which I cannot
> delete - they don't have a rowguid value set for them - when I try and
> delete I get a message about 'cannot insert a null value into column
'guid'.
> How do I tidy tihss up ?
> thanks
>
|||Bruce,
presumably you are using merge, and the insert failure is the insertion of a
row into MSmerge_tombstone.
I don't know how these rows have managed to be entered without having a GUID
as this is a default value. Either they were entered with an explicit null
or they were already there.
To remove them, it depends on if they exist on other servers or not.
The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername then
remove the rows then reenable the trigger. Obviously you must ensure there
are no other changes going on while you do this type of manipulation.
HTH,
Paul Ibison
|||Great idea but it says I can't alter the table 'cause it is being published
for replication....
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:etvoroNaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Bruce,
> presumably you are using merge, and the insert failure is the insertion of
a
> row into MSmerge_tombstone.
> I don't know how these rows have managed to be entered without having a
GUID
> as this is a default value. Either they were entered with an explicit null
> or they were already there.
> To remove them, it depends on if they exist on other servers or not.
> The easiest way is to use ALTER TABLE DISABLE TRIGGER deletetriggername
then
> remove the rows then reenable the trigger. Obviously you must ensure there
> are no other changes going on while you do this type of manipulation.
> HTH,
> Paul Ibison
>
|||I altered the trigger so it didn't do anything, deleted the rows and altered
the trigger back to how it was. Thanks for the tip...
Bruce
|||Ah...then try editing the trigger to comment out the part that inserts into
the MSmerge_tombstone table. Can't test it here so please post back if there
are any problems.
Regards,
Paul Ibison

Removing MDAC

Hi!!!!
I need to remove the MDAC in order to remove ODBC drivers.
How can I do this?
I have Windows XP SP1 which comes with MDAC 2.7 but the third party
application installs an older version of MDAC (it seems to be MDAC 2.1) and
now another third party applicattion claims to install a MDAC 2.5 but this
application not update the MDAC. The installation process needs MDAC 2.1 be
removed.
How can I do this?
Thanks for any informationYou can change MDAC versions but you can't totally remove
MDAC on XP. I'm not sure what all you have done with your
current installation but try reinstalling the latest MDAC.
You can also use the component checker to check your MDAC
installation and version. You can download the recent MDAC
versions from:
http://msdn.microsoft.com/data/down...es/default.aspx
You can download the component checker from:
http://msdn.microsoft.com/data/down...ls/default.aspx
-Sue
On Fri, 27 Feb 2004 15:24:31 -0300, "The Desperate Newbie"
<desperate.newbie@.hotmail.com> wrote:

>Hi!!!!
>I need to remove the MDAC in order to remove ODBC drivers.
>How can I do this?
>I have Windows XP SP1 which comes with MDAC 2.7 but the third party
>application installs an older version of MDAC (it seems to be MDAC 2.1) and
>now another third party applicattion claims to install a MDAC 2.5 but this
>application not update the MDAC. The installation process needs MDAC 2.1 be
>removed.
>How can I do this?
>
>Thanks for any information
>

Removing duplicates

Hi

I have inherited a web app with the following table structure, and need to
produce a table without any duplicates. Email seems like the best unique
identifier - so only one of each e-mail address should be in the table.

Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able to
get a duplicate count working:

select Email, count(*) as UserCount
from dbo.Members
group by Email
having count(*) > 1
order by UserCount desc

But the methods for create a new table without duplicates fail. My code for
the 2nd method is:

sp_rename 'Members', 'temp_Members'

select distinct *
into Members
from temp_Members

Table...

CREATE TABLE [dbo].[Members] (
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[SendMail] [smallint] NOT NULL
) ON [PRIMARY]
GO

Thanks B.Can I assume that memberid is unique? If so, try this:

INSERT INTO NewTable
(username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail)
SELECT username, password, email, title, firstname, surname, address1,
address2, city, country, profession, publication, dateadded, sendmail
FROM Members AS M1
JOIN
(SELECT MIN(memberid) AS memberid
FROM Members
GROUP BY email) AS M2
ON M1.memberid = M2.memberid

--
David Portas
----
Please reply only to the newsgroup
--|||Microsoft has a great article on this at
http://support.microsoft.com/defaul...4&Product=sql2k

I use a code block for doing this that I load as required, I only run
it when I have to. Once you've cleaned up your data, put a unique
constraint to prevent dupes from getting back in.

This is the code that I use for cleaning up dupes, you'll have to
modify the table and field names obviously. This config assumes you
are allowing Select/Into's.

/*
--
--WARNING! EXECUTE THIS SCRIPT WITH EXTREME CAUTION!,
--AND THEN ONLY ONE STEP AT A TIME!
--
--In the TotalTransit (DDS) database, this needs to run against
--ddsTrip and ttVoucher_Trip. Since the two tables have different
--layouts, the table that holds dupe rows must be dropped and
--recreated every time this process is run.
--
--There are now two seperate scripts, one for each table.
--
--Run the process one step at a time by highlighting each step.
--
--Wayne West, 10/13/03
-- WW 10/17/03 -- split into two procs, added more comments
--
------------------------
--
--this is here to help find if there are any dupes
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

select tripid, count(*)
from ttVoucher_Trip
group by tripid
having count(*) > 1
--
------------------------
*/
/*
--Step 1 -- Verify there are dupes in the table
select tripid, count(*)
from ddsTrip
group by tripid
having count(*) > 1

--Step 2a -- Drop table that will hold dupe key values
drop table zzzholdkey

--Step 2b -- Collect key values, save in zzzHoldKey
select RecKey = tripid, KeyCount = count(*)
into zzzHoldKey
from ddsTrip
group by tripid
having count(*) > 1

--Step 3a -- Drop table that will hold one instance of duplicate rows
drop table zzzholddupes

--Step 3b -- Collect one instance of duplicate rows
select DISTINCT t.*
into zzzHoldDupes
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 4 -- See if more than one field in addition to TripID is
duplicated
--This will indicate additional steps must be taken to clean up the
table.
select count(*), tripid
from zzzholddupes
group by tripid
having count(*) > 1

--Step 5 -- Delete ALL rows based on key value of duplicate records
delete ddsTrip
from ddsTrip t, zzzholdkey hld
where t.tripid = hld.reckey

--Step 6 -- Reinsert the row captured in Step 3b
insert ddsTrip
select *
from zzzholddupes

--You probably ought to rerun Step 1 to make sure the file is clean.
*/

Removing duplicate dat

Hi
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>

Removing delimiters

Hi

I have a database in which the
bankno is 29 and
acctno is 01-001-0000002

I need to write a query to get the data as "29010010000002" that is to concatenate both the fields and take out the delimiters in the second field. I know how to concatenate both
i.e.

select bankno + acctno as acctnos from table;

But that will give me 2901-001-0000002. I need to modify the query so that I can remove the delimiters in the second column.
Can anyone give me an idea.

Thanx in advance.select
replace(bankno + acctno,'-','')
as acctnos from table;