Server Name Change

Aug 13, 2007

Forgive my newbness, but I need help with a couple of scripts. Computer name changes happen alot where I work and I'm trying to make a simple batch file that when run, will automatically update the SQL server name. I believe this will work, but I am having problems with the text formatting of the output file. Here is the 'code':

-------------start of batch file------------------

CLS

REM THIS BATCH FILE WILL UPDATE THE INSTANCE NAME ON A SERVER, WHERE THE PC NAME HAS BEEN CHANGED AFTER THE INSTALLATION OF SQL

"%ProgramFiles%Microsoft SQL Server90ToolsBinnSQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q "select @@servername" -o C:OLDSERVERNAME.TXT"


"%ProgramFiles%Microsoft SQL Server90ToolsBinnSQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q EXEC sp_dropserver -i C:OLDSERVERNAME.TXT"


"%ProgramFiles%Microsoft SQL Server90ToolsBinnSQLCMD" -S "127.0.0.1" -U sa -P mySApassword -Q EXEC sp_addserver '127.0.0.1', 'local'"



REM NET STOP MSSQLSERVER

REM NET START MSSQLSERVER

PAUSE

-------------end of batch file------------------

I guess I could also edit the loopback address to be the current PC Name, but this should work for my purposes I also know that I may have to change the authentication mode from SQL to Windows Authentication.

Also, is there any way to enable Named Pipes and TCP/IP connections via script, without having to use SQL Configuration Manager?

Thanks.

View 3 Replies


ADVERTISEMENT

SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions

May 12, 2015

Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data

View 1 Replies View Related

DB Engine :: Collation Change And DDL Change In Same Script

Nov 18, 2015

We ran into weird/interesting issue with below details.

Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):

We are using SQLCMD to run DDL script on our product database in below order. That script has below content.

step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)

When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.

View 7 Replies View Related

SQL Server Admin 2014 :: Change IP Address Of Server Which Is Locally Hosted And Is Not On Cluster?

Sep 3, 2014

I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.

View 3 Replies View Related

Server 2003 Running Sql Server 2005. Regional Settings Are Different From All Others We Have; Can We Safely Change It?

Oct 18, 2007

Hi
We have a bunch of servers running server 2000 & 2003 along with many sql server (versions 2000 & 2005) databases in a production environment pulling transactions then doing warehousing & reporting.

An audit has shown up 1 production server using English_US 'mmddyyyy' system date formats, all others being English_Australian 'ddmmyyyy'.

Is it safe to simply change the regional settings on this English_US server to English_Australian or will it mess up the data in our SQL 2005 databases? I've not been able to get a definitive answer from anyone yet!

many thanks in advance; GaryP

View 1 Replies View Related

SQL Server 2000 Migration To SQL Server 2005 Collation Change - Method?

Jan 24, 2008

Scenario
Recently moved a SQL2000 database to SQL2005.
Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS

After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.

What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -
1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each varchar/char column

or

2) For each table
BCP out data
DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)
BCP in data

3) Other methods?

Thanks in advance
David.

View 4 Replies View Related

SQL Server Admin 2014 :: Server Configuration Change Events?

Nov 3, 2015

I'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA.

View 7 Replies View Related

Change Server Name.

Aug 2, 2000

Hello,

I need to change server name on my WinNT Server. But if I change the server name on NT Server, SqlServer does not connect. Can I change registered server name on SQLServer ? What do I need to do ?

Thank you.
Hugo Venturini

View 1 Replies View Related

NT Server Name Change

Dec 7, 2000

Hi,

If I change the name of the NT server, mssqlserver NT service does not start up.
Is there a way around this? URGENT.

View 1 Replies View Related

Change Sql Server Name

Jun 20, 2003

Hi!
I need to know how to change the sql server 2000 name ? It shows as
local (Windows NT)


Thanks

View 5 Replies View Related

How To Change Sql Server Name

Aug 29, 2006

im trying to change sql server name. how to rename my server?

View 2 Replies View Related

How To Change SQL Server Name

Jul 17, 2007

Hi all,

I have a server with machine name as ABC with SQL Server 2000 in it.

Executing Select @@Servername gave me ABC before but after i changed the Machine name to XYZ @@Servername is still showing ABC. can somebody tell me how to change the SQL Server name to That of the machine i.e XYZ.



View 3 Replies View Related

Change SQL Server Registration Under SQL Server Group

Apr 3, 2007

Hi

I have a .bat script for changing a server's IP address and a .vbs script for changing the server's computer name. The problem is that the registered SQL Server is the old computer name. Is it possible to change the SQL Server Registration under SQL Server Group via a script? Can anyone give an example of such a script?

Best
/H

View 1 Replies View Related

Change Database With A Different DB On Server X That Is Being Replicated To Server Y

Nov 16, 2015

I have Transaction Replication setup on a Server X. Server X is publisher and distributor. Server Y is the Subscriber.Everything works perfectly.  But I need to change the Database with a different DB on Server X that is being replicated to Server Y. I have following questions:
  
  1-  Do I create a new distributor on Server X and delete the current distributor?
   2- Create a new Replication Setup on Server X and Y ? 
   3- Delete the subscriber DB on Server Y (Subscriber) ?
   4- Can I just disable the current setup on Server X and Server Y instead of deleting it

View 5 Replies View Related

I Want To Change Sql 2005 Server Name

May 2, 2008

Now i have sql2005 express edition from VS.net 2005.Server name is given by WINZONESQLEXPRESS.So, i want to change WINZONESQLEXPRESS to MMTSVR. How can i change sql 2005 server name 

View 1 Replies View Related

Ip Address Change On SQL 7.0 Server

Aug 21, 2002

Currently running SQL Server 7.0 on numerous Windows NT 4.0 SP6 servers.

Need to change the IP address of these servers to a private scheme.

What effect will this have on SQL Server 7.0


Thanks

View 1 Replies View Related

Time Change On SQL Server

Jun 28, 2001

Hello Everyone,

This may seem a simple question, but are there specific precautions that should be fundamentally taken when resetting the time between two SQL servers that replicate?

Thank you in Advance

View 1 Replies View Related

Need To Change The Server Name In DTS Packages

Feb 13, 2001

I have approximately 250 dts packages. I moved all of the packages over to the new server, using dts. But now I need to change the server name to point to the new server and keep the transformations. is there a better way than having to open every package and editing?

thanks
Irfan

View 1 Replies View Related

Change Server Name In Sql2000

Nov 14, 2002

Hi,

I'am running Sql2000 Enterprise edition
on Windows 2000

When I re-name the Server for example
from TST_SVR To TST_SVR1, Sqlserver comes backup fine with the new name (TST_SVR1) , BUT I'am still able to connect to the SqlServer even with the old Server name (TSR_SVR). I do not want to connect using the old server name.

I have dropped and added server using
Sp_addserver and Sp_Dropserver, My
@@servername command returns the new server name

Any help in this matter is really appreciated

Thanks
Sanjeev

View 2 Replies View Related

Change Publisher Server

May 11, 2004

I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??

ThanX.

View 2 Replies View Related

SQL Server 2012 :: How To Get Row Of First Change

May 12, 2015

I would like to get extract the first row of some time based data where any of the other values changes. For example, in the following 10 rows

ABDTE
112015-01-01
112015-01-02
112015-01-03
212015-01-04
112015-01-05
1NULL2015-01-06
1NULL2015-01-07
212015-01-08
212015-01-09
222015-01-10

I want to remove the rows where A and B are the same as on the previous row. So rows 2, 3, 7 and 9 should be eliminated. Note that A and B can have the same values multiple times, just not in succession in the extract. I've tried ranking but I can't figure out how to keep it from lumping all the values of A and B in the same group. The following incorrectly eliminates rows 5 and 8:

;with data as (
select 1 as A, 1 as B, '2015-01-01' as DTE union
select 1 as A, 1 as B, '2015-01-02' as DTE union
select 1 as A, 1 as B, '2015-01-03' as DTE union
select 2 as A, 1 as B, '2015-01-04' as DTE union

[Code] .....

Of course the real data has many columns and multiple data types that can have nulls. I just want get the row when anything changes. Is there a slick way to do this in SQL?

View 9 Replies View Related

How To Change SQL Server Setting So That It Allows

Oct 24, 2006

How to change SQL Server setting so that it allows remote control?

View 4 Replies View Related

How To Change Server Name In SQL 2005?

Jan 10, 2007

Hi Folks,

How do I change the computer name of a server running SQL Server 2005?
I would appreciate any help. Thanks.


Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

View 6 Replies View Related

Change 'SA' Password At Sql Server

Mar 11, 2008

Hi All,

we plan to change SA password and there are 2 servers running on replication.

what are the precautions that i have to consider?

what is the fallback plan should changing SA password causing other error/ bugs?

can somebody tell me the exact steps to change SA password for this kind of environment?

thanks in advance.

-Nonie

Nonie

View 6 Replies View Related

How Do I Change The SQL Server For Access ADP?

Jul 23, 2005

I have MS SQL Server 2000 evaluation version on my desktop PC. I havethe SQL Server client tools (only) on the laptop. The two computers arenetworked. I had an Access MDB database on my laptop, but I convertedit with the Access upsizing wizard to an Access ADP database with theAccess front-end on the laptop connecting to the SQL Server on thedesktop. Now I want to move from this test environment to the "real"server. How do I go about this? Do I make the change from the laptop orthe desktop? Do I use the Access drop-down menu, Tools > DatabaseUtilities > Transfer Database? Or maybe from the desktop with: SQLServer Enterprise Manager > Copy Database Wizard?

View 1 Replies View Related

Need To Change IP Address Of Server

Jul 23, 2005

I don't know much about MS SQL-Server. I'm having SQL server 2000 andgoing to change IP address of the server. What need to be done?

View 1 Replies View Related

Change SQL Server Registration?

Jul 20, 2005

I am trying to set up my machine for replication and have recieved thefollowing message"SQL Server replication does not support nicknames, such as "." or"(local)" as server names.Delete the SQL Server registration for this server and register itusing the actual server name"Well, I am not sure how to do that, I can't seem to find any info onit. Can anyone help out?Thanks.

View 1 Replies View Related

Change Tracking For SQL Server

Jul 20, 2005

Has anyone come across a change tracking tool for SQL Server. Specificallythe scenario I want is the following :A Production DB needs some modifications to its content.This tool will copy the DB to a dev environment.The Dev environment will be 2 copies of the DB, 1 as a control set and theother the change DB.The developer makes their changes in the Change DB. They test them out andthen when they decide the changes are ready to pushto production they hitthe "go" button on the tool.The tool calculates the delta between the Control and the Change DB and thenpushes the changes to producitonAny ideasThankss

View 2 Replies View Related

SQL Server 2000 - How Do I Change The Name?

Jul 20, 2005

I need to change the name of the SQL Server on my development machine. Myclient's server name is "Server1". My development server name is"MachineName1ServerTest".I am writing a Visual Basic application. Everything is working great on mydevelopment system, but I have to go in a change the connection strings.Each time I change one, it takes about a couple minutes for the prompt tocome back saying it timed out trying to find the server. I want to renamemy server to be the same as my client's server so I won't have this time outannoyance in my development environment.I did a sp_dropserver and sp_addserver, but that does not change the machinename of the server. Thanks in advance for any help.

View 3 Replies View Related

How Can I Change Sql Server Name 2000

Jun 6, 2006

How can I change sql server name 2000? It was accidently installed using "local" as name

Thank you

View 1 Replies View Related

SQL Server Port Change

Jan 30, 2008



Hello All,

We have default instance "MSSQLSERVER" with Port number 1433. We Installed another Instance "MGMT" on the same windows server, it came as same Port number "1433".

We decided to Change the Port number for Instance "MGMT".

1. We changed the port number for "MGMT" to like "1234"

2. After the change we did telnet to "MGMT" Instance with "1433", it worked.

3. Then we changed the Port number for Instance "MSSQLSERVER" also to like "4321".

4. After change of these setting and followed with rules framed in link as said: http://www.databasejournal.com/features/mssql/article.php/3689846


5. Now we did telnet with changed port number, it works fine.

6. We can not do telnet with Port number 1433 for both Instances "MSSQLSERVER" and "MGMT".. This is fine, what we want, and what we intent to do...

Question to MVP OR SQL Gurus:

A) If you want to change port number to Second Instance, shall we need to change for Default Instance?

B) How the port numbers will be configured? please advice.

Thanks, Jay

View 1 Replies View Related

Server And Domain Name Change

May 5, 2008



Within our Exchange Environment we use Blackberry. Our Blackberry Server is using SQL Server 2005 Express. We're migrating from Exch 5.5 to Exch 2003 (new server for 2003). Now the new Exch Server is in our new Active Directory Domain to which is not named the same as our NT Domain for obvious reasons.

Anyway, after I decomission the 5.5 Exch Server, I want to rename the Blackberry Server and move it to the new domain.
Will SQL Server 2005 Express squak at me for doing this??

Thanks All

View 1 Replies View Related

Change Sql 2005 Server Name

Oct 2, 2006


I have Sql Server 2005 on my computer and i want to change the name of the server. I found this procedure but i didn't understand how it worked. So the actual name of my server is STATIE4 and i want to change it.
I executed this procedure, but the name of the server is still STATIE4. If i write sp_helpserver STATIE4 it doesn't find STATIE4. What have changed after using this procedure?sp_dropserver old_name
GO
sp_addserver new_name, local
GO

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved