Creating a view with Select * - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Creating a view with Select *

Author

Topic

MightyAC
Starting Member

5 Posts

Posted - 2009-07-08 : 16:21:30

I have 2 related databases running on the same server originally created in SQL Server 2000. A web app connects to one and a desktop app connects to the other. On occaision data needs to be pushed from one database to the other.

To do this I created a view in DB1 that links to DB2. For example Select * From DB2.dbo.Tablename

This allowed me to access the required table in the second db from my web app. Using Select * also meant that any table changes would automatically be reflected in the view so I wouldn't have to bother changing it.

I've now moved the DBs to SQL 2008. It seems that Select * in views is no longer accepted. Instead SQL pulls all of the field names in for me. Table changes are not automatically reflected in the view. Instead I have to design the view and re-save it.

Is there a way to create a view that does what I'm trying to do?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-09 : 00:01:18

Any time the source table(s) are modified, you'll need to recompile the view.

Another good reason not to select *

MightyAC
Starting Member

5 Posts

Posted - 2009-07-09 : 08:40:53

That's unfortunate because it worked fine in SQL 2000. It was a convenient way to relate tables from multiple databases in an application connected to just one of the DBs.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 09:06:02

or

EXEC sp_refreshview view_name

Madhivanan

Failing to plan is Planning to fail

MightyAC
Starting Member

5 Posts

Posted - 2009-07-09 : 09:47:26

Help states that sp_RefreshView "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."

In DB1 I have VIEW1 that looks like this:
Select * From DB2.dbo.TableName

If I add TestField1 to DB2.TableName
then in DB1 I run exec sp_refreshview VIEW1, TestField1 does not become part of the view. If I remove a field from a table then refresh the view I just get an error message.

Again this works fine in SQL 2000 but not in 2008.

MightyAC
Starting Member

5 Posts

Posted - 2009-07-09 : 12:31:02

I just created a table with the list of Views that need to be recreated. Then in a SP I loop through, drop them and recreate them.

I'll just run the SP anytime I issue a script that changes the db.

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-09 : 12:53:57

quote:
Originally posted by MightyAC

That's unfortunate because it worked fine in SQL 2000. It was a convenient way to relate tables from multiple databases in an application connected to just one of the DBs.

I don't see how that's true at all

And SELECT * is just bad practice

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-09 : 13:55:27

>>And SELECT * is just bad practice
Agreed!

if you have a script to re-define your views why not have your script generate all the columns from information_schema.columns?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources