renaming domain

We have a small site where we want to rename the domain and bring it into
our domain.
current names are as
domainx.local -holds SQL server cluster
domainx.net -main domain
we want to bring domainx.local into the domainx.net AD.
will will rename and migrate. Are there any tools for SQL2005?
we were planning on using the microsoft domain rename tool and ADMT3.
Any other issues with SQL?
Regards
CC
SQL itself doesn't really care what domain it is in, as long as all of the
resources and servers it has to access are still accessible the way they
were before. The only thing you will have to update is the windows service
accounts if any of them are from AD and not the local machine. Will it be
renamed or are you moving to a new domain while the old domain remains up
for a short period of time? If the latter I would recommend setting up a
trust and switching the accounts, or understanding that you might have blips
in uptime/availability.
"Ned" <Cal@.obded.org> wrote in message
news:%231UNGh2ZIHA.2000@.TK2MSFTNGP05.phx.gbl...
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
|||Hi
I assume that the machine names for the SQL Servers are not changing?
If you have granted logins to domain accounts or groups then you may need to
change them see http://support.microsoft.com/kb/298897/
John
"Ned" wrote:

> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
>

renaming domain

We have a small site where we want to rename the domain and bring it into
our domain.
current names are as
domainx.local -holds SQL server cluster
domainx.net -main domain
we want to bring domainx.local into the domainx.net AD.
will will rename and migrate. Are there any tools for SQL2005?
we were planning on using the microsoft domain rename tool and ADMT3.
Any other issues with SQL?
Regards
CCSQL itself doesn't really care what domain it is in, as long as all of the
resources and servers it has to access are still accessible the way they
were before. The only thing you will have to update is the windows service
accounts if any of them are from AD and not the local machine. Will it be
renamed or are you moving to a new domain while the old domain remains up
for a short period of time? If the latter I would recommend setting up a
trust and switching the accounts, or understanding that you might have blips
in uptime/availability.
"Ned" <Cal@.obded.org> wrote in message
news:%231UNGh2ZIHA.2000@.TK2MSFTNGP05.phx.gbl...
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>|||Hi
I assume that the machine names for the SQL Servers are not changing?
If you have granted logins to domain accounts or groups then you may need to
change them see http://support.microsoft.com/kb/298897/
John
"Ned" wrote:
> We have a small site where we want to rename the domain and bring it into
> our domain.
> current names are as
> domainx.local -holds SQL server cluster
> domainx.net -main domain
> we want to bring domainx.local into the domainx.net AD.
> will will rename and migrate. Are there any tools for SQL2005?
> we were planning on using the microsoft domain rename tool and ADMT3.
> Any other issues with SQL?
> Regards
> CC
>
>

Renaming a Database Physical and logical file names

I have a situation where I need to change a database's name both the logical
name, AND the physical filenames.
I have found several methods that change the Logical name only but keep the
old filenames which will not work in my situation because there is a high
probability a new database might be created that will use the old name.
Thanks,
RonHi Ron,
You can take the database offline by detaching the database. Rename the
physical files and you use sp_attachdb command to attach the new files.
Regards
Shri.DBA
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
logical
> name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
the
> old filenames which will not work in my situation because there is a high
> probability a new database might be created that will use the old name.
> Thanks,
> Ron
>|||You can change the Logical name with ALTER DATABASE but not the physical
unless it is tempdb. But I believe you can detach it, change the name and
reattach it again specifying the new name.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
> logical name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
> the old filenames which will not work in my situation because there is a
> high probability a new database might be created that will use the old
> name.
> Thanks,
> Ron
>|||I tried this code I wrote...but I get the error:
Run-time error '55555':
Microsoft ODBC SQL Server Driver SQL Server To connect to this server you
must use SQL Server Management Studio or SQL Server Management Objects.
-- CODE:
Option Explicit
Public strSQLServerLogin As String 'In ModCommon
Public strSQLServerPassword As String 'In ModCommon
Public strSQLServerHostName As String 'In ModCommon
Public strADOConnTypeSQL As String 'In ModCommon
Public strSQL As String 'In ModCommon
Public strDBToCopy As String
Public strNewDBName As String
Private Sub Command1_Click()
strSQLServerHostName = "HOST"
strSQLServerLogin = "sa"
strSQLServerPassword = "PASS"
strADOConnTypeSQL = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=" & strSQLServerLogin & ";Password=" &
strSQLServerPassword & ";Data Source=" & strSQLServerHostName & ";Database="
Dim strCurrentDatabase As String
Dim strNewDatabase As String
strCurrentDatabase = Form1.CurrentDB.Text
strNewDatabase = Form1.NewDB.Text
Call RenameSQLDatabase(strCurrentDatabase, strNewDatabase)
End Sub
Public Sub RenameSQLDatabase(strCurrentDatabase As String, strNewDatabase As
String)
Dim oSQL As Object
Dim oSQLDB As Object
Dim strMDFfilePath As String
Dim strMDFfileName As String
Dim strLOGfile As String
Dim strSQL As String
Dim iCnt As Integer
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
' Kill any existing connections to the Database that will be copied
strSQL = "SELECT spid, DB_NAME(dbid) AS dbname FROM
master.dbo.sysprocesses WHERE DB_NAME(dbid)='" & strCurrentDatabase & "'"
oConn.Open strADOConnTypeSQL
iCnt = 0
With oRs
.Open strSQL, oConn, adOpenStatic, adLockOptimistic
If Not .EOF Then
oSQL.KillProcess .Fields("spid").Value
iCnt = iCnt + 1
End If
.Close
End With
oConn.Close
Set oRs = Nothing
Set oConn = Nothing
' Use SQLDMO to Attach, Copy & rename new DB, and reattach target and
source DB
Set oSQL = CreateObject("SQLDMO.SQLServer")
Set oSQLDB = CreateObject("SQLDMO.Database")
oSQL.Connect strSQLServerHostName, strSQLServerLogin,
strSQLServerPassword
Set oSQLDB = oSQL.Databases(strCurrentDatabase, "dbo")
strMDFfilePath = oSQLDB.PrimaryFilePath
strMDFfileName = _
Trim(oSQLDB.FileGroups.Item(1).DBFiles.Item(1).PhysicalName)
strLOGfile = Trim(oSQLDB.TransactionLog.LogFiles(1).PhysicalName)
Set oSQLDB = Nothing
' Detach DB
oSQL.DetachDB (strDBToCopy)
'Copy database files to new names
FileCopy strMDFfileName, strMDFfilePath & strNewDatabase & ".mdf"
FileCopy strLOGfile, strMDFfilePath & strNewDatabase & "_log.ldf"
'Attach original database and new database
oSQL.AttachDB strCurrentDatabase, "[" & strMDFfileName & "],[" &
strLOGfile & "]"
oSQL.AttachDB strNewDatabase, "[" & strMDFfilePath & strNewDatabase &
".mdf]" & ",[" & strMDFfilePath & strNewDatabase & "_log.ldf]"
Set oSQLDB = oSQL.Databases(strNewDatabase, "dbo")
Debug.Print "Database Created"
End Sub

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Os6S9mLrFHA.464@.TK2MSFTNGP15.phx.gbl...
> You can change the Logical name with ALTER DATABASE but not the physical
> unless it is tempdb. But I believe you can detach it, change the name and
> reattach it again specifying the new name.
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
>|||
I also tried this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spRenameDatabase]
@.SourceDB varchar(200),
@.BackupPath varchar(2000),
@.DestinationDB varchar(200),
@.DataPath varchar(2000)
AS
Declare @.BackupFile varchar(2000)
SET @.BackupFile = @.BackupPath + @.SourceDB + '.dat'
Declare @.Datafile varchar(200)
SET @.Datafile = @.Datapath + @.DestinationDB + '.mdf'
Declare @.LogName varchar(200)
SET @.LogName = @.SourceDB + '_log'
Declare @.Logfile varchar(200)
SET @.Logfile = @.Datapath + @.DestinationDB + '_log.ldf'
BACKUP DATABASE @.SourceDB
TO DISK = @.BackupFile
RESTORE FILELISTONLY
FROM DISK = @.BackupFile
RESTORE DATABASE @.DestinationDB
FROM DISK = @.BackupFile
WITH MOVE @.SourceDB TO @.DataFile,
MOVE @.LogName TO @.Logfile
IF @.@.ERROR <> 0
RETURN 1
ELSE
RETURN 0
ERROR::::
Msg 3234, Level 16, State 2, Procedure spRenameDatabase, Line 31
Logical file 'RSHTestRename_2' is not part of database 'RenamedDatabase'.
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Procedure spRenameDatabase, Line 31
RESTORE DATABASE is terminating abnormally.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZK5RRLrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> I have a situation where I need to change a database's name both the
> logical name, AND the physical filenames.
> I have found several methods that change the Logical name only but keep
> the old filenames which will not work in my situation because there is a
> high probability a new database might be created that will use the old
> name.
> Thanks,
> Ron
>|||Use EM...
1. Detach your database
2. Rename your physical files
3. Re-'Attach' your database by navigating to your data file (MDF) and
selecting it
4. Change the 'Current File(s) Location' to reflect the new physical
names
5. Change the 'Attach as' to reflect you new logical name|||I would love to but this is a dynamic script that has to be accessible to
users in remote locations.
"MySQLServer" <naka55n@.hotmail.com> wrote in message
news:1125342469.302763.179680@.g49g2000cwa.googlegroups.com...
> Use EM...
> 1. Detach your database
> 2. Rename your physical files
> 3. Re-'Attach' your database by navigating to your data file (MDF) and
> selecting it
> 4. Change the 'Current File(s) Location' to reflect the new physical
> names
> 5. Change the 'Attach as' to reflect you new logical name
>|||Try this then:
sp_detach_db 'saiko'
GO
xp_cmdshell 'rename c:\mg\mssql\saiko1_Data.MDF saiko_Data.MDF'
GO
xp_cmdshell 'rename c:\mg\mssql\saiko1_Log.LDF saiko_Log.LDF'
GO
sp_attach_db @.dbname = 'saiko1',
@.filename1='c:\mg\mssql\saiko_Data.MDF',
@.filename2='c:\mg\mssql\saiko_Log.LDF'
GO|||I keep getting a "The system cannot find the file specified" Error...even
though I am looking right at the files in the server's directory. I copied
and pasted the path and it is Exactly where the file is.
When I run this as a query on the server this path should match up with the
Path that appears in the Properties panel under Files correct? In other
words this is the path on the sever, not my local box right?
Thanks for the help...this looks like the best solution of all.
Ron
"MySQLServer" <naka55n@.hotmail.com> wrote in message
news:1125345464.950289.161550@.g44g2000cwa.googlegroups.com...
> Try this then:
> sp_detach_db 'saiko'
> GO
> xp_cmdshell 'rename c:\mg\mssql\saiko1_Data.MDF saiko_Data.MDF'
> GO
> xp_cmdshell 'rename c:\mg\mssql\saiko1_Log.LDF saiko_Log.LDF'
> GO
> sp_attach_db @.dbname = 'saiko1',
> @.filename1='c:\mg\mssql\saiko_Data.MDF',
> @.filename2='c:\mg\mssql\saiko_Log.LDF'
> GO
>|||do this:
xp_cmdshell 'dir c:\mg\mssql\*.*'
do you see your datafiles?
Make sure the paths you are specifying are relative to the location on
the server.
You can also be sure be looking in sysfiles
select * from sysfiles
Let us know how you make out.

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my database server changed names and now I have jobs I cannot delete out of the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just delete the ones not belonging t
o this server?
You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent/...ent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?
sql

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my database server changed names and now I have jobs I cannot delete out of the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just delete the ones not belonging to this server?You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?

renamed server

does anyone have a suggestion for this problem: somewhere along the line, my
database server changed names and now I have jobs I cannot delete out of th
e Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just dele
te the ones not belonging t
o this server?You need to update the originating_server column in sysjobs to reflect the
new server name, You can use this proc to do that
http://sqldev.net/download/sqlagent...gent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:437DE682-C216-4327-B318-85F47F64CD56@.microsoft.com...
> does anyone have a suggestion for this problem: somewhere along the line,
my database server changed names and now I have jobs I cannot delete out of
the Agent. Can I go to the table the jobs are in (msdb.sysjobs) and just
delete the ones not belonging to this server?

Rename SQL Agent Jobs for Replication

I have SEVERAL replication publications/subscriptions on a server.
The names that are assigned to these jobs are vague and cryptic at best. I
went ahead and renamed them to something a little more telling. I noe get the
following error:
TITLE: Replication Monitor
Replication Monitor could not start the job
'100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28'.
The specified @.job_name
('100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28') does
not exist. (Microsoft SQL Server, Error: 14262)
Is there some other place I need to update the names of these jobs? Or do I
just need to go back and name them what they were before?
Thanks!
have a look at MSsnapshot_agents
MSlogreader_agents
MSdistribution_agents
MSmerge_agents
MSqreader_agents
in the distribution database,
Hilary Cotter
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
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D4EE3A17-FEE2-40C4-809F-9AD799E52E27@.microsoft.com...
>I have SEVERAL replication publications/subscriptions on a server.
> The names that are assigned to these jobs are vague and cryptic at best. I
> went ahead and renamed them to something a little more telling. I noe get
> the
> following error:
>
> TITLE: Replication Monitor
> --
> Replication Monitor could not start the job
> '100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28'.
> The specified @.job_name
> ('100602-DB4-NavigatorSuiteReplicate-NavigatorSuiteReplicate_TPA3-28')
> does
> not exist. (Microsoft SQL Server, Error: 14262)
> Is there some other place I need to update the names of these jobs? Or do
> I
> just need to go back and name them what they were before?
> Thanks!
>
sql

Rename server names in Enterprise Manager?

Hi there.

Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")

Can someone give me a hint, please?

Thanx.Not mentioned the version of SQL server used>

If its SQL 2K then you can use SP_DROPSERVER & SP_ADDSERVER which will take affect at the restart of SQL services.

If its version 7 then you've to run the setup again to take new changes affected.

Refer to books online about Renaming Servers topic.|||Originally posted by LuckyLights
Hi there.

Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")

Can someone give me a hint, please?

Thanx. It sounds to me like you want to register a server, but have a completely different name show up on the screen in the Server collection. IF I understood what you wanted correctly, according to How to register a server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_7jua.asp), that can't be done.

-PatP

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> > rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> > affect anything on the server?
> >
> >
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files
> > named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > to
> > rename it as CC_Data and CC_Log to make it consistent. Would this
> > renaming
> > affect anything on the server?
> >
> >
>|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:
> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
> > No. It goes quickly.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> > Hi,
> >
> > Would it affect anything / any process on the production server?
> >
> > "Tom Moreau" wrote:
> >
> > > You can change the logical file names, using ALTER DATABASE, without
> > > taking
> > > the DB's offline.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > > Hi,
> > >
> > > I have 3 databases running on Production server. All the databases have
> > > names like AA, BB and CC with their Logical Data Files and Log files
> > > named
> > > as
> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > > to
> > > rename it as CC_Data and CC_Log to make it consistent. Would this
> > > renaming
> > > affect anything on the server?
> > >
> > >
> >
> >
>|||> Does changing the logical name also change the physical (file) name?
No.
> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>> The logical filename is just an easy way to refer to it when using ALTER
>> DATABASE. They are unique to the DB - not the server - so you can have a
>> number of DB's with the same logical filenames, but with different physical
>> filenames. This is convenient is a scenario where you have one DB per
>> client and what all of your DB maintenance scripts to do the same thing to
>> each customer DB.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
>> Hi Tom,
>> Can you please explain to me what exactly is the purpose of a logical
>> Filename? When we have more than one databases on one SQL Server, can we
>> have
>> the same logical name for all the databases? Thanks in advance.
>> "Tom Moreau" wrote:
>> > No. It goes quickly.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> >
>> >
>> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
>> > Hi,
>> >
>> > Would it affect anything / any process on the production server?
>> >
>> > "Tom Moreau" wrote:
>> >
>> > > You can change the logical file names, using ALTER DATABASE, without
>> > > taking
>> > > the DB's offline.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
>> > > Hi,
>> > >
>> > > I have 3 databases running on Production server. All the databases have
>> > > names like AA, BB and CC with their Logical Data Files and Log files
>> > > named
>> > > as
>> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
>> > > to
>> > > rename it as CC_Data and CC_Log to make it consistent. Would this
>> > > renaming
>> > > affect anything on the server?
>> > >
>> > >
>> >
>> >
>>|||Thank you.
"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||I normally drop the db, then copy it to the new location - data in the
data directory, logs to their directory. Rename the files I copied my
test.mdf and test.ldf to test2.mdf and test2.ldf
Then in SMS do a attach database and browse to the mdf file. Select the
new one. If necessary do the same for the log (you'll have to do this
if you moved it).
* In the top window check the "Attach AS" and change it to whatever you
want - for me test2
* In the bottom window under "Current File Path" browse to the new file
and select the one you renamed.- do for mdf and ldf files. For mdf I
selected test2.mdf and for ldf test2.ldf
* In top window change owner if necessary.
* Click OK
Now go to the database in object explorer and right click->properties.
Check the files window. You'll see the new files selected and if you
want you can change the logical names.
Yabe wrote:
> "Tibor Karaszi" wrote:
>> Does changing the logical name also change the physical (file) name?
>> No.
>>
>> If not,
>> is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>|||Probably just the SSMS GUI that is messing with you. I suggest you read about sp_attach_db in Books
Online. Since the mdf has the path and name of the original ldf (and other db files), you need to
specify all file names, like:
EXEC sp_attach_db 'NewDbName', 'C:\NewPath\NewFileName.mdf', 'C:\NewPath\NewFileName.ldf'
Above from memory, so please check Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:23B5D429-5E6A-4209-BCC2-CB9C20C0C94B@.microsoft.com...
>
> "Tibor Karaszi" wrote:
>> > Does changing the logical name also change the physical (file) name?
>> No.
>>
>> > If not,
>> > is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to
>> change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this
>> very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

removing duplicate entries in SQL Field

Hi All,

Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.

Below is the SQL and the results from the returned page.

PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

Hi,

if you are using denormalized stored values, you will first have denormalized them to use SQL Server functions for grouping / distinct them. You will have to separate the values first using a separator and using a function like this one here:

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de