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.
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 RelatedWe 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.
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 RelatedHi
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
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.
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 RelatedHello,
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
Hi,
If I change the name of the NT server, mssqlserver NT service does not start up.
Is there a way around this? URGENT.
Hi!
I need to know how to change the sql server 2000 name ? It shows as
local (Windows NT)
Thanks
im trying to change sql server name. how to rename my server?
View 2 Replies View RelatedForgive 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.
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
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
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 RelatedCurrently 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
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
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
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
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.
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?
How to change SQL Server setting so that it allows remote control?
View 4 Replies View RelatedHi 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.
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
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 RelatedI 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 RelatedI 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 RelatedHas 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 RelatedI 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 RelatedHow can I change sql server name 2000? It was accidently installed using "local" as name
Thank you
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
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
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