two databases...same name...same instance...is it psbl?
-
just to give bckgrnd about my process...we are inserting data into different environments with the same db...so we need to have multiple instances with same db name....i am looking is there a way where i can have same db name under one instance or atleast point to same db name...or gng to multiple instances is the only option...pllease suggest...thanks
-
Database names within an instance must be unique.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Different instance names with same database name on same machine is possible with different database file locations.
- Win.
Cheers,
- Win." Have a great day "
-
winslet (2/25/2010)
Different instance names with same database name on same machine is possible with different database file locations.- Win.
Or different instances with same file location but same database file names.;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
You cant host 2 DB's with the same name in the same SQL instance, even if you have different filenames.
Each DB name has to be unique.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle -
thanks everyone ...i know i cant have two dbs with same name under same instance...i was looking is there a way setup some alias..or something else..
-
Reminds me of the Monty Python "Is your name not Bruce" skit, where everyone was named Bruce, and they renamed newcomers to avoid confusion. Can't have a "Michael" running around in the middle of a bunch of Bruces...might cause some confusion.
I'd like to hear exactly why you think you need multiple databases named the same; i'm having trouble visualizing any need for it.
as an example, all my applications get the db info from a connection string built from values in a .config file, or an ini file, or from some registry entry.
they do not care about the name of the database the connect to, just that they have a valid connection.
why would you need to have a specific db name?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
iqtedar (2/26/2010)
thanks everyone ...i know i cant have two dbs with same name under same instance...i was looking is there a way setup some alias..or something else..Synonyms, pass through views, linked servers, OPENROWSET... which way do you want to skin this cat? 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (2/26/2010)
iqtedar (2/26/2010)
...which way do you want to skin this cat? 😉Sounds like a monty python leadin ;-);-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events - Henrico Bekker - Friday, February 26, 2010 12:48 AM
How abt this ???? - saurabh.x.sinha - Tuesday, July 24, 2018 3:57 AMHenrico Bekker - Friday, February 26, 2010 12:48 AM How abt this ????
They aren't the same same. "SSISDB" and "SSSISDB"; there's an extra S.
SQL Server is quite happy to accept "stupid" names for objects. For example, if I run the following:
USE master;
GODECLARE @sql nvarchar(MAX);
SET @sql = N'CREATE DATABASE [Foolishness] ON
PRIMARY (NAME = ''Foolishness'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish1.mdf'')
LOG ON (NAME = N''Foolishness_log'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish1_log.ldf'');';
EXEC sp_executesql @sql;SET @sql = N'CREATE DATABASE [Foolish' + NCHAR(10) + N'ness] ON
PRIMARY (NAME = ''Foolishness'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish2.mdf'')
LOG ON (NAME = N''Foolishness_log'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish2_log.ldf'')';
EXEC sp_executesql @sql;
GO
If you then refresh the Object Explorer, you'll see 2 "same" named databases:
Of course, they are not the same, as one has a line break in it.--Clean up
DECLARE @sql nvarchar(MAX);
SET @sql = N'DROP DATABASE Foolishness;'
EXEC sp_executesql @sql;SET @sql = N'DROP DATABASE [Foolish' + NCHAR(10) + N'ness];'
EXEC sp_executesql @sql;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, July 24, 2018 5:06 AMsaurabh.x.sinha - Tuesday, July 24, 2018 3:57 AMHenrico Bekker - Friday, February 26, 2010 12:48 AM How abt this ????
I suspect one of those databases has a special character in there. I bet if you ran the following SQL you would get different value for BinaryName:
SELECT [Name], CONVERT(varbinary,[name]) AS BinaryName
FROM sys.databases;SQL Server is quite happy to accept "stupid" names for objects. For example, if I run the following:
USE master;
GODECLARE @sql nvarchar(MAX);
SET @sql = N'CREATE DATABASE [Foolishness] ON PRIMARY (NAME = ''Foolishness'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish1.mdf'')
LOG ON
(NAME = N''Foolishness_log'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish1_log.ldf'');';
EXEC sp_executesql @sql;SET @sql = N'CREATE DATABASE [Foolish' + NCHAR(10) + N'ness]
ON PRIMARY (NAME = ''Foolishness'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish2.mdf'')
LOG ON
( NAME = N''Foolishness_log'', FILENAME = N''C:\Program Files\Microsoft SQL Server\MSSQL11.SANDBOX\MSSQL\DATA\Foolish2_log.ldf'')';
EXEC sp_executesql @sql;
GO
If you then refresh the Object Explorer, you'll see 2 "same" named databases:
Of course, they are not the same, as one has a line break in it.--Clean up
DECLARE @sql nvarchar(MAX);
SET @sql = N'DROP DATABASE Foolishness;'
EXEC sp_executesql @sql;SET @sql = N'DROP DATABASE [Foolish' + NCHAR(10) + N'ness];'
EXEC sp_executesql @sql;Hi
ran suggested query and found binary name is different. how can i understand correct differences. - saurabh.x.sinha - Tuesday, July 24, 2018 5:16 AMHi
ran suggested query and found binary name is different. how can i understand correct differences.I've editted my post since then, as I didn't initially notice. There's an extra S there.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, July 24, 2018 5:25 AMsaurabh.x.sinha - Tuesday, July 24, 2018 5:16 AMHi
ran suggested query and found binary name is different. how can i understand correct differences.I've editted my post since then, as I didn't initially notice. There's an extra S there.
Hahahahah
My bad guys spelling mistake 🙂But thanks for quick around
How abt this ????
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply