May 11, 2012 at 7:23 pm
I have a view something like:
Create VIEW SomeView
AS
SELECT * from Retail.dbo.Employees
But now since I have a RetailDev, RetailQA and Retail, I can read a table to get the databasename and then use it. But I can't seem to figure out how to do it in a view.
DECLARE @sql varchar(100)
SET @sql = 'SELECT * FROM ' + (Select DBName FROM CurrenDB) + ''
EXEC (@Sql)
Don' think that works.
I can't pass the database in as you can't pass parameters in.
Is this possible with a view?
Thanks,
Tom
May 11, 2012 at 9:18 pm
I'm pretty sure parameters cannot be passed into views. A view is a virtual table and allowing parameters would change its definition and it would become more like a stored procedure. If your goal is to simplify the maintenance of three Views in separate databases, I would suggest the following
Remove the DB name from the select statement. Make your select statements independent of a database and then reference the database name as the beginning of your script with Use "dbname" Go
Use Retail
Go
Create View ViewRetail as
Select * from dbo.Employees
Go
Use RetailDev
Go
Create View ViewRetail as
Select * from dbo.Employees
May 13, 2012 at 9:06 am
CELKO (5/13/2012)
>> I have a VIEW something like:CREATE VIEW SomeView
AS
SELECT *
FROM Retail.dbo.Employees; <<
We never use SELECT * in production code. The correct name for the base table would be Personnel and not Employees; you still do not think in sets (completed whole) and are stuck in sequential files (separate units).
>> But now since I have a RetailDev, RetailQA and Retail, I can read a table to get the databasename and then use it. But I can't seem to figure out how to do it in a VIEW. <<
NO! You just do not have any concept of RDBMS/ A schema is its own little model universe of discourse. You still think a database is like a mag tape which gets a name from the tape drive upon which you have mounted it.
But those names imply another serious conceptual problem; attribute splitting. Why is Retail spread all over those schemas? You are putting entities in separate universes. Would you split Personnel into Male_Personnel and Female_Personnel (split on sex code)?
Get a copy of THINKING IN SETS and see if that helps you.
Joe, Did you ever think to ask questions before going off on one of your tirads? Ever think that RetailDev, RetailQA, and Retail are in fact the Development, QA, and Production databases?
Get off you high horse and lose your arrogant attititude, it isn't wanted here. If you want to help, try helping. If not, please, just go away.
May 14, 2012 at 11:57 am
Are you serious??????
This is why you aren't taken seriously.
You don't seem to ever want to address the issue.
There wasn't one thing you said that addressed the issue. The answer shows you cannot understand a sample/test query from a real one.
CREATE VIEW SomeView
AS
SELECT *
FROM Retail.dbo.Employees; <<
SomeView should have told you this is not a real View. Just a quick example to illustrate what I am asking. Obviously, way over your head.
"We never use SELECT * in production code."
Actually, I might use it as:
IF EXISTS (SELECT * FROM SomeTable).
But what has this got to do with the question. I could have listed out the 50 columns that this view might return. How does that help???
"But those names imply another serious conceptual problem; attribute splitting. Why is Retail spread all over those schemas?"
As Lynn, correctly stated, the databases/schemas are the same, just in are different environments. I would think that would be pretty clearto someone like you who apparently stands head and shoulders above everyone else. As a development company, we separate our databases into environment for testing (do you do that in your world). We don't test on our production databases. But is a real world concept - possibly foreign to the intellectual world.
"NO! You just do not have any concept of RDBMS"
An interesting statement from this little sample code.
"NO! You just do not have any concept of RDBMS/ A schema is its own little model universe of discourse. You still think a database is like a mag tape which gets a name from the tape drive upon which you have mounted it."
You can derive my schema from this????
Man, you are good.
"The correct name for the base table would be Personnel and not Employees". Now that is a little arrogant, now knowing how the schema - My way is the only way true way. I could have a Persons table, a Users Table - depending business logic.
And you can tell I don't think in sets by this. Even if I didn't, what has that got to do with the question?
The problem is that you have a stock answer for any question.
I ask about using a different database names in a query and you rail on the concept of Sets?
Interesting answer.
Too bad it in now way addresses the question.
May 14, 2012 at 12:09 pm
tshad:
Dynamic SQL can use Views, but AFAIK, there is no way(*) for a View to use Dynamic SQL. Not even indirectly through a Table-Valued Function, as they don't allow Dynamic SQL either.
What I've done when I've been in similar situations, is to set up Synonyms to point to one database or another. Then when I needed to, I run an automated procedure, that switches all of the synonyms to the other database.
(*- except for the OPEN*() functions, which personally, I don't recommend)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 12:27 pm
Just want to clarify that I never use select * expeically not in a view. I was just focused on answering the specific question. I did see several design issues that could be addresses/debated but I chose not to.
May 14, 2012 at 12:31 pm
a suggestion i have seen else where on the forums is to use a view similar to the one below:
CREATE VIEW blah
AS
SELECT 'db1' AS DBName, * FROM db1..tbl1
UNION ALL
SELECT 'db2', * FROM db2..tbl1
UNION ALL
SELECT 'db3', * FROM db3..tbl1
and any query you run add a where clause to it
SELECT * FROM blah WHERE DBName = 'db1' --change the db name to match your union all query
may work for you it may not. but its more suggestive than celko
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 14, 2012 at 1:27 pm
I had thought about doing it that way, but I really didn't want to do the unions where any changes would have to be done multiple times, thus increases chances of errors.
Since this is coming from different environments with different names for the same databases in our office and the same at the clients office, I created a database a display column , which is used in a dropdown, and the actual database name in another column. The display column would have the same name in all the database (i.e. Retail) and each database would have the actual database name (RetailDev, for instance).
And you better be careful. You used the dreaded "SELECT *" in your example - which apparently is taboo for samples and examples 🙂
Thanks,
Tom
May 14, 2012 at 8:15 pm
I would expect the view to be "SELECT * FROM dbo.Employees", without the database prefix. When connecting to the Retail or RetailDev database, the correct table will always be selected because the view would be part of the same database.
May 15, 2012 at 5:21 pm
That would normally be true.
But in this case the view is being called from a different environment and it wouldn't be a problem except that I don't know if I am in the Production, QA or Dev environment, otherwise Retail.dbo.Employees would be fine.
but if I am calling it from a Dev Database, it would be RetailDev.dbo.Employees. If in the QA environment, it would be RetailQA.dbo.Employees.
May 15, 2012 at 5:36 pm
tshad (5/15/2012)
That would normally be true.But in this case the view is being called from a different environment and it wouldn't be a problem except that I don't know if I am in the Production, QA or Dev environment, otherwise Retail.dbo.Employees would be fine.
but if I am calling it from a Dev Database, it would be RetailDev.dbo.Employees. If in the QA environment, it would be RetailQA.dbo.Employees.
I guess I am confused. First, what is the name of the view. Second, is this view defined in each of the databases (Dev, QA, and Production). Third, is the table being accessed based on wether you are connected to Dev, QA, or Production?
May 16, 2012 at 6:39 am
My answer assumed that there were three databases on the server ("RetailDev", "RetailQA", and "Retail"), and that each was a self-contained database, containing both an "Employee" table and a "MyView" view. I also assumed that, when connecting to the server, the required database was specified as part of the connection string, so that when connecting in the developement environment, the connection string would be something like "server=MyServer;database=RetailDev;uid=foo;pwd=bar", and when connecting in the production environment, the connection string would look something like "server=MyServer;database=Retail;uid=foo;pwd=bar".
Following on from Lynn's questions... Fourth, what connection string are you using to connect to the server when you are in each of the Dev, QA, or Prod environments?
Given your remarks, it appears that the view may be in a different database, not in "RetailDev", "RetailQA", or "Retail".
Fifth, a) Are there any other databases in use besides the three we've discussed? b) Is the view in the Retail/Dev/QA databases or another database?
May 16, 2012 at 10:12 am
These are all on one server.
The view is only on one of the databases in the environment. It is called from another database and works fine when used from a stored procedure with dynamic SQL.
The only reason it is an issue is because of the different environments. I could just go in each environment and in each call its respective database.
For example:
In the production system I could change the view to say:
SELECT *
FROM Retail.dbo.Employees
In the Development database, I could change it to:
SELECT *
FROM RetailDev.dbo.Employees
In the QA Database, I could change it to:
SELECT *
FROM RetailQA.dbo.Employees
I am trying to set it up so that it can use the same view in each environment.
In a stored procedure I would do something like:
************************
SELECT @DatabaseName
FROM MyDatabases
WHERE DisplayName = 'Retail'
SET @sql = '
SELECT *
FROM ' + @DatabaseName + '.dbo.Employees'
************************************
@DatabaseName would have the actual database name (RetailDev, for example).
This works fine in an SP.
I was trying to do this in a View, somehow.
I will probably change the view to a stored procedure to accomplish what I want and put all the possible filters (where clauses) in the stored procedure instead of the code.
Thanks,
Tom
May 16, 2012 at 12:49 pm
Tom: You did not answer any of the questions that were asked. It would be helpful to have those answers to be able to more effectively help you.
The basic answer to your problem is to have the same view on all three databases. In the production system, it would be:
SELECT *
FROM dbo.Employees
In the Development database, it would be:
SELECT *
FROM dbo.Employees
In the QA Database, it would be:
SELECT *
FROM dbo.Employees
Removing the database name from the SELECT statement should solve your problem.
May 22, 2012 at 9:00 pm
No, that is true.
But in my case, I have to use the database prefix because it will be called from another database.
Thanks,
Tom
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply