Changing a table will break a view?

The other day I updated a table to add a few columns using Enterprise
Manager. When I closed the table, it said it would update several other
database items as a result (very good, I thought).
However, subsequent to this change, even the most basic view is broken, e.g.
,
CREATE VIEW [v_table] AS select * from [table]
Recreating (or altering) the view clear up the problem. But, I was surprised
!
What is the canonical way to avoid views getting out of sync with tables? Is
there another way to modify table structure that will automatically recompil
e
other database objects?
David> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
The best practice is to specify a column list rather than '*'. You can
execute sp_refreshview to refresh view meta data after changes to underlying
tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
> e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
> surprised!
> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
> David
>|||Create your views WITH SCHEMABINDING to avoid this problem. That will
"bind" the schema -- meaning that none of the underlying tables will be able
to change unless you drop the view.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
surprised!
> What is the canonical way to avoid views getting out of sync with tables?
Is
> there another way to modify table structure that will automatically
recompile
> other database objects?
> David
>|||And you can automate this refresh using something like:
-- Cycle through all view in the current database and refresh their metadata
-- to take into account any changes to the underlying objects.
DECLARE @.ViewName sysname
DECLARE views_to_refresh CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN views_to_refresh
FETCH NEXT FROM views_to_refresh INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
print 'Refreshing ' + @.ViewName
exec sp_refreshview @.ViewName
FETCH NEXT FROM views_to_refresh INTO @.ViewName
END
CLOSE views_to_refresh
DEALLOCATE views_to_refresh
Thanks!
David
"Dan Guzman" wrote:

> The best practice is to specify a column list rather than '*'. You can
> execute sp_refreshview to refresh view meta data after changes to underlyi
ng
> tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in messag
e
> news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
>
>sql

change the sheet name while exporting sql data to excel

i have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)...

it is not possible to change the sheet name but have a label (Label property) for each table and that will give you document map in the excel sheet.

Shyam

|||

T hank you brother...

|||

Hi,

Thank you for the Tip.

Another question in the Document Map it creates an entry for the report Name and adds all the labels for that report.

Is it possible to change the report name in the document map without chainging it for the file?

Thank you,

Paul

Just found That http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=207848&SiteID=17

change the sheet name while exporting sql data to excel

i have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)...

Sorry, but this isn't supported yet.

Jarret

change the sheet name while exporting sql data to excel

i have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)...

it is not possible to change the sheet name but have a label (Label property) for each table and that will give you document map in the excel sheet.

Shyam

|||

T hank you brother...

|||

Hi,

Thank you for the Tip.

Another question in the Document Map it creates an entry for the report Name and adds all the labels for that report.

Is it possible to change the report name in the document map without chainging it for the file?

Thank you,

Paul

Just found That http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=207848&SiteID=17

change password on sql service account

For security, I need to change the password of my sql service account. Will
this break anything? Is there a kb article on this?
It shouldn't break anything but if you are using that account for some other
things
and hardcoded the password rememeber them because it will cause account
lockout.
this is my 2 cents..
Thanks,
"Tim" wrote:

> For security, I need to change the password of my sql service account. Will
> this break anything? Is there a kb article on this?
|||Refer to this article.
http://msdn2.microsoft.com/en-us/library/ms188615(SQL.90).aspx
MCDBA 2003, Sybase Certified Professional DBA (AA115, SD115, AA12, AP12)
"Tim" wrote:

> For security, I need to change the password of my sql service account. Will
> this break anything? Is there a kb article on this?

change password on sql service account

For security, I need to change the password of my sql service account. Will
this break anything? Is there a kb article on this?It shouldn't break anything but if you are using that account for some other
things
and hardcoded the password rememeber them because it will cause account
lockout.
this is my 2 cents..
Thanks,
"Tim" wrote:

> For security, I need to change the password of my sql service account. Wi
ll
> this break anything? Is there a kb article on this?|||Refer to this article.
http://msdn2.microsoft.com/en-us/library/ms188615(SQL.90).aspx
MCDBA 2003, Sybase Certified Professional DBA (AA115, SD115, AA12, AP12)
"Tim" wrote:

> For security, I need to change the password of my sql service account. Wi
ll
> this break anything? Is there a kb article on this?

change password on sql service account

For security, I need to change the password of my sql service account. Will
this break anything? Is there a kb article on this?It shouldn't break anything but if you are using that account for some other
things
and hardcoded the password rememeber them because it will cause account
lockout.
this is my 2 cents..
Thanks,
"Tim" wrote:
> For security, I need to change the password of my sql service account. Will
> this break anything? Is there a kb article on this?|||Refer to this article.
http://msdn2.microsoft.com/en-us/library/ms188615(SQL.90).aspx
--
MCDBA 2003, Sybase Certified Professional DBA (AA115, SD115, AA12, AP12)
"Tim" wrote:
> For security, I need to change the password of my sql service account. Will
> this break anything? Is there a kb article on this?