Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP

Renaming Data Source view giving error

Hi,

After I renamed my data source view in BIDS (SSAS ServicePack1), I now get an error when I try to build my SSAS project:

Error 1 Dimension 'Date' : The 'Source' property references unknown 'Data Source View' with ID='XXXX'. 0 0

I tried to change this directly in the XMLA for Date.dim file, but it still doesn't work.

Any pointer\workarounds anyone? I would hate to have to recreate by dims again, just for this.

Thanks,

JGP

Anyone from MSFT can comment on this?

Thanks,

JGP

Renaming a view

I learned, the hard way, that renaming a view in E.M. doesn't really rename
the view. How can I rename a view without disturbing existing permissions?
thanks,
GThis is a multi-part message in MIME format.
--=_NextPart_000_0240_01C3C884.D985F8E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Check out sp_rename in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gary" <gb@.nospam.com> wrote in message
news:eb#ZuwKyDHA.2452@.tk2msftngp13.phx.gbl...
I learned, the hard way, that renaming a view in E.M. doesn't really rename
the view. How can I rename a view without disturbing existing permissions?
thanks,
G
--=_NextPart_000_0240_01C3C884.D985F8E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_rename in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Gary" =wrote in message news:eb#ZuwKyDHA.2452=@.tk2msftngp13.phx.gbl...I learned, the hard way, that renaming a view in E.M. doesn't really =renamethe view. How can I rename a view without disturbing existing permissions?thanks,G

---

--=_NextPart_000_0240_01C3C884.D985F8E0--|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
As our MVP, TOM said, the system sp_rename can change the name of the
view. You can refer to the Books Online for detailed information. The
example code are as follows:
use pubs
go
create view a
as select * from authors
go
exec sp_rename a, b
select * from b
If you still have questions, please feel free to post new message here and
I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong
Yes , you are right.
But how do you explain that when I went to EM (after running your script)
selected the desire view and when I opened it by right click on the
properties I still see CREATE VIEW a and NOT b.
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:xCThiKPyDHA.2604@.cpmsftngxa07.phx.gbl...
> Hi Gary,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> As our MVP, TOM said, the system sp_rename can change the name of the
> view. You can refer to the Books Online for detailed information. The
> example code are as follows:
> use pubs
> go
> create view a
> as select * from authors
> go
> exec sp_rename a, b
> select * from b
> If you still have questions, please feel free to post new message here and
> I am ready to help!
>
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Your prudential is really impressive. You even notice some detailed
information of our pruduct. Here is my explanation to these.
When a view is created, the name of the view is stored in the sysobjects
table. Information about the columns defined in a view is added to the
syscolumns table, and information about the view dependencies is added to
the sysdepends table. In addition, the text of the CREATE VIEW statement is
added to the syscomments table.
use pubs
SELECT text FROM syscomments WHERE id = object_id('b')
you will get 'create view a as select * from authors'.
However, this is just a comment of this object, that is view 'b' , the
object name is saved in sysobjects and the definition of it is 'select *
from authors'. When using the 'sp_rename', it will not change the content
of the syscomments. This will not affect any application of this view. It
just because to keep the high performance of the system SPs.
If you still have questions, please feel free to post new message here and
I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Renameing a table

If the name of a database table no longer makes sense is there a way to
refer to it by another name, like an alias? Or would this be a view?
The database has been around awhile and there is lots of data and code
refering to the table, but it is confusing always having to translate the
table name to the new meaning.
ThanksYou can rename it with sp_rename. If you have a lot of code that refers to
the old name, though, this will break your code. In that case, you can
create a view with the new name and then you have both.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Craig" <NoSpam@.hotmail.com> wrote in message
news:eboL23C0FHA.3720@.TK2MSFTNGP14.phx.gbl...
If the name of a database table no longer makes sense is there a way to
refer to it by another name, like an alias? Or would this be a view?
The database has been around awhile and there is lots of data and code
refering to the table, but it is confusing always having to translate the
table name to the new meaning.
Thanks|||... and in SQL Server 2005 you will have synonyms.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23HnJ98C0FHA.3300@.TK2MSFTNGP15.phx.gbl...
> You can rename it with sp_rename. If you have a lot of code that refers to
> the old name, though, this will break your code. In that case, you can
> create a view with the new name and then you have both.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Craig" <NoSpam@.hotmail.com> wrote in message
> news:eboL23C0FHA.3720@.TK2MSFTNGP14.phx.gbl...
> If the name of a database table no longer makes sense is there a way to
> refer to it by another name, like an alias? Or would this be a view?
> The database has been around awhile and there is lots of data and code
> refering to the table, but it is confusing always having to translate the
> table name to the new meaning.
>
> Thanks
>
>|||Long overdue, IMHO. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23g7NIxI0FHA.2924@.TK2MSFTNGP15.phx.gbl...
... and in SQL Server 2005 you will have synonyms.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23HnJ98C0FHA.3300@.TK2MSFTNGP15.phx.gbl...
> You can rename it with sp_rename. If you have a lot of code that refers
> to
> the old name, though, this will break your code. In that case, you can
> create a view with the new name and then you have both.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Craig" <NoSpam@.hotmail.com> wrote in message
> news:eboL23C0FHA.3720@.TK2MSFTNGP14.phx.gbl...
> If the name of a database table no longer makes sense is there a way to
> refer to it by another name, like an alias? Or would this be a view?
> The database has been around awhile and there is lots of data and code
> refering to the table, but it is confusing always having to translate the
> table name to the new meaning.
>
> Thanks
>
>

rename NULL column outputs

I have this view:
CREATE VIEW CautionDescriptions AS
SELECT TOP 100 PERCENT
Oils.OilName AS [Oil name], Cautions.Description AS Caution
FROM Cautions
INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
ORDER BY Oils.OilName
I want to return all rows even those with NULLs in the description key. Is
there a way to replace the <NULL>s in the description column with more
attractive text?
like
if OilCautions.OilID ISNULL 'No description available'
endif
Where could I put this in the SELECT or FROM clause?SELECT COALESCE(column_name, 'No description available')
You can also use ISNULL. Funny, I just wrote an article about this earlier
today:
http://www.aspfaq.com/2532
http://www.aspfaq.com/
(Reverse address to reply.)
"Patrick" <psully@.nospam.eatel.net> wrote in message
news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
> I have this view:
> CREATE VIEW CautionDescriptions AS
> SELECT TOP 100 PERCENT
> Oils.OilName AS [Oil name], Cautions.Description AS Caution
> FROM Cautions
> INNER JOIN OilCautions ON Cautions.CautionID = OilCautions.CautionID
> RIGHT OUTER JOIN Oils ON OilCautions.OilID = Oils.OilID
> ORDER BY Oils.OilName
> I want to return all rows even those with NULLs in the description key. Is
> there a way to replace the <NULL>s in the description column with more
> attractive text?
> like
> if OilCautions.OilID ISNULL 'No description available'
> endif
> Where could I put this in the SELECT or FROM clause?
>
>|||Thanks Aaron! That was a fast answer. I never heard of coalesce. I am going
to read your article too, thanks again!
BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
Programming. The book name is certainly a misnomer, the book is mostly about
using Enterprise Manager.
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||Read your article. **** rating!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#rYiovYAFHA.3744@.TK2MSFTNGP15.phx.gbl...
> SELECT COALESCE(column_name, 'No description available')
> You can also use ISNULL. Funny, I just wrote an article about this
earlier
> today:
> http://www.aspfaq.com/2532
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Patrick" <psully@.nospam.eatel.net> wrote in message
> news:-NadnTQqguVUl2ncRVn-ow@.eatel.net...
Is
>|||> BTW, the tables and excercises are from MS's Step by Step SQL Server 2000
> Programming. The book name is certainly a misnomer, the book is mostly
about
> using Enterprise Manager.
Then you might want to read this article too:
http://www.aspfaq.com/2455
:-)
PS I suggest creating your views from scratch, as opposed to letting
Enterprise Manager write the code for you ... because of the limitations in
the view designer, as well as its funky quirks like putting TOP 100 PERCENT
and ORDER BY in views for some reason.|||Oops, I think I hit reply too soon (may be an empty reply here soon).
Anyway, that was an interesting and informative article. I've taken a few
SQL Server courses, and they all start of with EM. But Step by Step does
cover the QA, too. I remember vaguely when I learned how to use it a little
last year, I was impressed a lot by QA. I mainy use EM for experimenting
with joins really fast, and if it works, copy the script and edit it in QA.
When are you going to turn all these "tips" into a book? lol, thanks!
Patrick
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ur6ou3YAFHA.4072@.TK2MSFTNGP10.phx.gbl...
2000
> about
> Then you might want to read this article too:
> http://www.aspfaq.com/2455
> :-)
> PS I suggest creating your views from scratch, as opposed to letting
> Enterprise Manager write the code for you ... because of the limitations
in
> the view designer, as well as its funky quirks like putting TOP 100
PERCENT
> and ORDER BY in views for some reason.
>

Rename Columns via Query

I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use
a view to display the ID column,and only one other, which will vary depending
on my requirements. I have another table that contains only one row (and is
updated regularly) with the column name (ColID) I'm trying to retieve.
Can anyone tell me how I can pass the value of ColID into the column name in
my view?
I hope this is clear. Thanks in anticipation...
Take a look at this example:
USE Northwind
CREATE TABLE QueryColumn
(qc sysname)
GO
INSERT INTO QueryColumn VALUES(N'CompanyName')
DECLARE @.q varchar(8000)
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
UPDATE QueryColumn SET qc=N'Phone'
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
GO
DROP TABLE QueryColumn
GO
Do please also read the article at
http://www.sommarskog.se/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MillionChads" <MillionChads@.discussions.microsoft.com> wrote in message
news:ABCE5205-361F-4BB8-8DAC-DCBEF6DACB4C@.microsoft.com...
> I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to
use
> a view to display the ID column,and only one other, which will vary
depending
> on my requirements. I have another table that contains only one row (and
is
> updated regularly) with the column name (ColID) I'm trying to retieve.
> Can anyone tell me how I can pass the value of ColID into the column name
in
> my view?
> I hope this is clear. Thanks in anticipation...

Rename a view that has dependent views?

SQL 2000 server: I want to standardize my naming scheme. I have views that
have other views dependent on them. I would like to rename some of these
without breaking the dependent views. It appears that sp_rename doesn't do
the trick.
Also, I'm confused about this comment in BOL: "Important After renaming
stored procedures and views, flush the procedure cache to ensure all
dependent stored procedures and views are recompiled."
I ran some tests, and it looks like sp_rename doesn't fix dependent views
that use a view that gets renamed, so what is this comment about?
Thanks. Any comments and suggestions will be appreciated.
David WalkerDavid
No, sp_rename does not go through all the dependent procedures, views and triggers and change the old view name to the new view name. You need to do that yourself. If you are relying on the info in sysdepends, DO NOT. You can create a parent stored procedure that references a child/sub procedure before the child is created. SQL will yell at you, but it will do it. You can create the child next and all will be fine
You can search for objects that reference your old view name with
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%oldviewname%
in every database that may reference the old view
The procedure cache is an area of the SQL Server memory that hold the execution plans of stored procedures and views. You can just try your changes after you implement them to see if it works, which you probably should. If some erro occurs run DBCC FREEPROCCACHE
Norman

Rename a view that has dependent views?

SQL 2000 server: I want to standardize my naming scheme. I have views that
have other views dependent on them. I would like to rename some of these
without breaking the dependent views. It appears that sp_rename doesn't do
the trick.
Also, I'm confused about this comment in BOL: "Important After renaming
stored procedures and views, flush the procedure cache to ensure all
dependent stored procedures and views are recompiled."
I ran some tests, and it looks like sp_rename doesn't fix dependent views
that use a view that gets renamed, so what is this comment about?
Thanks. Any comments and suggestions will be appreciated.
David WalkerDavid,
No, sp_rename does not go through all the dependent procedures, views and tr
iggers and change the old view name to the new view name. You need to do tha
t yourself. If you are relying on the info in sysdepends, DO NOT. You can cr
eate a parent stored proced
ure that references a child/sub procedure before the child is created. SQL w
ill yell at you, but it will do it. You can create the child next and all wi
ll be fine.
You can search for objects that reference your old view name with:
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%oldviewname%'
in every database that may reference the old view.
The procedure cache is an area of the SQL Server memory that hold the execut
ion plans of stored procedures and views. You can just try your changes afte
r you implement them to see if it works, which you probably should. If some
erro occurs run DBCC FREEPR
OCCACHE.
Norman

Removing Nulls

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

Quote:

Originally Posted by jschmidt

Hello Everyone, I have a View that is returning null values. This is not a problem except that I don't want the user to see "NULL" in the data. I want to replace "NULL" with a blank. The sql that I have is shown below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.CUSVW_SCHEMATIC_DETAILS_VENDOR
AS
SELECT [DIVISION], [DIVISION NAME], [STOCKING SECTION #], [STOCKING SECTION NAME],
[FIXTURE], [SIZE], [STORE #], [AISLE NUMBER], [AISLE SIDE],
[LEFT STOCKING SECTION #], [LEFT STOCKING SECTION NAME],
[LEFT STOCKING SECTION SIZE], [RIGHT STOCKING SECTION #],
[RIGHT STOCKING SECTION NAME], [RIGHT STOCKING SECTION SIZE]
FROM [AVTP].[dbo].[CUSVW_SCHEMATIC_DETAILS]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

How can I get the nulls to be blanks instead of "NULL"??

Thanks!

try:

SELECT isnull(DIVISION,' ') as division...and so on...|||Try using case statements

case when division is null then '' esle division end

sort of thins, this need to be in the select statement