Image problem in Report

Hi,
I copied an image on report and call this report from VB. But when i export it in Excel, data properly exported but not the image. How can it be possible that when we export report image exported with it.
ThanksDid you try exporting to Word? Which version of CR are you using?

Image Problem

I have a web application which invokes MS reporting service, gets the result
of a render call and writes the result out to the Response object. Everything
is fine except when the rendered format is html and the report contains an
â'Externalâ' image (image in Report Project) - here the browser asks for the
report server login credentials before serving the image. If the login is not
correct, the image is not served. Is there any way the image can be made
available to the browser in this case?Take a look at the SP1 ReadMe item
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> I have a web application which invokes MS reporting service, gets the
result
> of a render call and writes the result out to the Response object.
Everything
> is fine except when the rendered format is html and the report contains an
> â'Externalâ' image (image in Report Project) - here the browser asks for
the
> report server login credentials before serving the image. If the login is
not
> correct, the image is not served. Is there any way the image can be made
> available to the browser in this case?
>
>|||"Ravi Mumulla (Microsoft)" wrote:
> Take a look at the SP1 ReadMe item
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > I have a web application which invokes MS reporting service, gets the
> result
> > of a render call and writes the result out to the Response object.
> Everything
> > is fine except when the rendered format is html and the report contains an
> > ââ?¬Ë?Externalââ?¬â?¢ image (image in Report Project) - here the browser asks for
> the
> > report server login credentials before serving the image. If the login is
> not
> > correct, the image is not served. Is there any way the image can be made
> > available to the browser in this case?
> >
> >
> >
> >
>
>|||i have installed SP1 on the SERVER and still get this error.
does sp1 need to be installed on the Client'
i have not seen this work ever!!!
"Ravi Mumulla (Microsoft)" wrote:
> Take a look at the SP1 ReadMe item
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > I have a web application which invokes MS reporting service, gets the
> result
> > of a render call and writes the result out to the Response object.
> Everything
> > is fine except when the rendered format is html and the report contains an
> > ââ?¬Ë?Externalââ?¬â?¢ image (image in Report Project) - here the browser asks for
> the
> > report server login credentials before serving the image. If the login is
> not
> > correct, the image is not served. Is there any way the image can be made
> > available to the browser in this case?
> >
> >
> >
> >
>
>|||SP1 should be installed on the Server and on all Development Workstations
where you develop reports. There is nothing to install on the clients.
Now your problems are directly the result of you not setting the Unattended
Execution Account up properly. When you have set that up you'll find that it
works.
From the link that Ravi sent you:
When the report is run on the report server, the report server uses the
unattended execution account to retrieve the image. If the unattended
execution account is not specified, the image is retrieved using no
credentials (anonymous user account). If either of these accounts have
insufficient rights to access the image, the image will not be displayed in
the report. For more information about setting the unattended execution
account on the report server, see "Configuring an Account for Unattended
Report Processing" in Reporting Services Books Online.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"luzer" <luzer@.discussions.microsoft.com> wrote in message
news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
>i have installed SP1 on the SERVER and still get this error.
> does sp1 need to be installed on the Client'
> i have not seen this work ever!!!
> "Ravi Mumulla (Microsoft)" wrote:
>> Take a look at the SP1 ReadMe item
>> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
>> > I have a web application which invokes MS reporting service, gets the
>> result
>> > of a render call and writes the result out to the Response object.
>> Everything
>> > is fine except when the rendered format is html and the report contains
>> > an
>> > â?~Externalâ?T image (image in Report Project) - here the browser asks
>> > for
>> the
>> > report server login credentials before serving the image. If the login
>> > is
>> not
>> > correct, the image is not served. Is there any way the image can be
>> > made
>> > available to the browser in this case?
>> >
>> >
>> >
>> >
>>|||the problem was related to Anonymous access on IIS6 messed up.
thanks
"Peter Blackburn (www.sqlreportingservice" wrote:
> SP1 should be installed on the Server and on all Development Workstations
> where you develop reports. There is nothing to install on the clients.
> Now your problems are directly the result of you not setting the Unattended
> Execution Account up properly. When you have set that up you'll find that it
> works.
> From the link that Ravi sent you:
> When the report is run on the report server, the report server uses the
> unattended execution account to retrieve the image. If the unattended
> execution account is not specified, the image is retrieved using no
> credentials (anonymous user account). If either of these accounts have
> insufficient rights to access the image, the image will not be displayed in
> the report. For more information about setting the unattended execution
> account on the report server, see "Configuring an Account for Unattended
> Report Processing" in Reporting Services Books Online.
>
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
> "luzer" <luzer@.discussions.microsoft.com> wrote in message
> news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> >i have installed SP1 on the SERVER and still get this error.
> > does sp1 need to be installed on the Client'
> >
> > i have not seen this work ever!!!
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> >> Take a look at the SP1 ReadMe item
> >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> >>
> >> --
> >> Ravi Mumulla (Microsoft)
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> >> > I have a web application which invokes MS reporting service, gets the
> >> result
> >> > of a render call and writes the result out to the Response object.
> >> Everything
> >> > is fine except when the rendered format is html and the report contains
> >> > an
> >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> >> > for
> >> the
> >> > report server login credentials before serving the image. If the login
> >> > is
> >> not
> >> > correct, the image is not served. Is there any way the image can be
> >> > made
> >> > available to the browser in this case?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Hi,
What are you doing about the anonymous access on IIS6?
maybe it will resolved my problem...
thanks
"luzer" wrote:
> the problem was related to Anonymous access on IIS6 messed up.
> thanks
>
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > SP1 should be installed on the Server and on all Development Workstations
> > where you develop reports. There is nothing to install on the clients.
> >
> > Now your problems are directly the result of you not setting the Unattended
> > Execution Account up properly. When you have set that up you'll find that it
> > works.
> >
> > From the link that Ravi sent you:
> >
> > When the report is run on the report server, the report server uses the
> > unattended execution account to retrieve the image. If the unattended
> > execution account is not specified, the image is retrieved using no
> > credentials (anonymous user account). If either of these accounts have
> > insufficient rights to access the image, the image will not be displayed in
> > the report. For more information about setting the unattended execution
> > account on the report server, see "Configuring an Account for Unattended
> > Report Processing" in Reporting Services Books Online.
> >
> >
> > Peter Blackburn
> > Hitchhiker's Guide to SQL Server 2000 Reporting Services
> > http://www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> >
> > "luzer" <luzer@.discussions.microsoft.com> wrote in message
> > news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> > >i have installed SP1 on the SERVER and still get this error.
> > > does sp1 need to be installed on the Client'
> > >
> > > i have not seen this work ever!!!
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > >> Take a look at the SP1 ReadMe item
> > >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> > >>
> > >> --
> > >> Ravi Mumulla (Microsoft)
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > >> > I have a web application which invokes MS reporting service, gets the
> > >> result
> > >> > of a render call and writes the result out to the Response object.
> > >> Everything
> > >> > is fine except when the rendered format is html and the report contains
> > >> > an
> > >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> > >> > for
> > >> the
> > >> > report server login credentials before serving the image. If the login
> > >> > is
> > >> not
> > >> > correct, the image is not served. Is there any way the image can be
> > >> > made
> > >> > available to the browser in this case?
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Other strange thing,
If the image is located in the same server that IIS, the image is right
displayed.
someone can tell me what is happen?
"luzer" wrote:
> the problem was related to Anonymous access on IIS6 messed up.
> thanks
>
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > SP1 should be installed on the Server and on all Development Workstations
> > where you develop reports. There is nothing to install on the clients.
> >
> > Now your problems are directly the result of you not setting the Unattended
> > Execution Account up properly. When you have set that up you'll find that it
> > works.
> >
> > From the link that Ravi sent you:
> >
> > When the report is run on the report server, the report server uses the
> > unattended execution account to retrieve the image. If the unattended
> > execution account is not specified, the image is retrieved using no
> > credentials (anonymous user account). If either of these accounts have
> > insufficient rights to access the image, the image will not be displayed in
> > the report. For more information about setting the unattended execution
> > account on the report server, see "Configuring an Account for Unattended
> > Report Processing" in Reporting Services Books Online.
> >
> >
> > Peter Blackburn
> > Hitchhiker's Guide to SQL Server 2000 Reporting Services
> > http://www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> >
> > "luzer" <luzer@.discussions.microsoft.com> wrote in message
> > news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> > >i have installed SP1 on the SERVER and still get this error.
> > > does sp1 need to be installed on the Client'
> > >
> > > i have not seen this work ever!!!
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > >> Take a look at the SP1 ReadMe item
> > >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> > >>
> > >> --
> > >> Ravi Mumulla (Microsoft)
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > >> > I have a web application which invokes MS reporting service, gets the
> > >> result
> > >> > of a render call and writes the result out to the Response object.
> > >> Everything
> > >> > is fine except when the rendered format is html and the report contains
> > >> > an
> > >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> > >> > for
> > >> the
> > >> > report server login credentials before serving the image. If the login
> > >> > is
> > >> not
> > >> > correct, the image is not served. Is there any way the image can be
> > >> > made
> > >> > available to the browser in this case?
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >sql

Image And Expression

hi there,
i using an expression at the data value with the IIF statement to call the
image which i have embeded 2 images and wanna to display them on the table
textbox when they they satify the IIF statement. and i encounter this error;
The value expression for the image â'image6â' contains an error: [BC30451] Name
'check' is not declared.
how can i resolve this problem or is there any other alternative for mi to
this issue.because wat i want is that within the IIF statement i wan to
display the different image.
thanks in advance
Angelacan you post the expression
"Angela" wrote:
> hi there,
> i using an expression at the data value with the IIF statement to call the
> image which i have embeded 2 images and wanna to display them on the table
> textbox when they they satify the IIF statement. and i encounter this error;
> The value expression for the image â'image6â' contains an error: [BC30451] Name
> 'check' is not declared.
> how can i resolve this problem or is there any other alternative for mi to
> this issue.because wat i want is that within the IIF statement i wan to
> display the different image.
> thanks in advance
> Angela
>|||=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
check , checked )
(the image name is check and checked)
"Antoon" wrote:
> can you post the expression
> "Angela" wrote:
> > hi there,
> > i using an expression at the data value with the IIF statement to call the
> > image which i have embeded 2 images and wanna to display them on the table
> > textbox when they they satify the IIF statement. and i encounter this error;
> > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > 'check' is not declared.
> > how can i resolve this problem or is there any other alternative for mi to
> > this issue.because wat i want is that within the IIF statement i wan to
> > display the different image.
> >
> > thanks in advance
> > Angela
> >|||ok,
- The name of the image has to be between quotes.
- You can not put this in a textbox, you can put the expression in an
image-box. So first put an image-box on you report (or in a table cell) and
than change the value of the image to the expression.
- In the expression you've given the Code-value of each row of the table is
compared to the "First" allergyRout_Code-value. This migth be correct, but if
you want to check against every value of allergyRout_Code, you should ommit
the "First"- function (if they are in the same dataset)
=iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
"check" , "checked" )
"Angela" wrote:
> =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> check , checked )
> (the image name is check and checked)
> "Antoon" wrote:
> > can you post the expression
> >
> > "Angela" wrote:
> >
> > > hi there,
> > > i using an expression at the data value with the IIF statement to call the
> > > image which i have embeded 2 images and wanna to display them on the table
> > > textbox when they they satify the IIF statement. and i encounter this error;
> > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > 'check' is not declared.
> > > how can i resolve this problem or is there any other alternative for mi to
> > > this issue.because wat i want is that within the IIF statement i wan to
> > > display the different image.
> > >
> > > thanks in advance
> > > Angela
> > >|||hi Antoon,
for the value to compare, they are from 2 different dataset. bcause i want
to compare the actual value with the master values. so if the answer matches
then i will display the correct image. And is there another other way apart
from making them to be the same dataset?
ur regards
Angela
"Antoon" wrote:
> ok,
> - The name of the image has to be between quotes.
> - You can not put this in a textbox, you can put the expression in an
> image-box. So first put an image-box on you report (or in a table cell) and
> than change the value of the image to the expression.
> - In the expression you've given the Code-value of each row of the table is
> compared to the "First" allergyRout_Code-value. This migth be correct, but if
> you want to check against every value of allergyRout_Code, you should ommit
> the "First"- function (if they are in the same dataset)
> =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> "check" , "checked" )
>
> "Angela" wrote:
> > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > check , checked )
> >
> > (the image name is check and checked)
> >
> > "Antoon" wrote:
> >
> > > can you post the expression
> > >
> > > "Angela" wrote:
> > >
> > > > hi there,
> > > > i using an expression at the data value with the IIF statement to call the
> > > > image which i have embeded 2 images and wanna to display them on the table
> > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > 'check' is not declared.
> > > > how can i resolve this problem or is there any other alternative for mi to
> > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > display the different image.
> > > >
> > > > thanks in advance
> > > > Angela
> > > >|||Yes there are, but if you really wan't to compare
First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value
then
=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
"check" , "checked" )
should work fine (in an image-box-in-a-table-field)
"Angela" wrote:
> hi Antoon,
> for the value to compare, they are from 2 different dataset. bcause i want
> to compare the actual value with the master values. so if the answer matches
> then i will display the correct image. And is there another other way apart
> from making them to be the same dataset?
> ur regards
> Angela
> "Antoon" wrote:
> > ok,
> > - The name of the image has to be between quotes.
> > - You can not put this in a textbox, you can put the expression in an
> > image-box. So first put an image-box on you report (or in a table cell) and
> > than change the value of the image to the expression.
> > - In the expression you've given the Code-value of each row of the table is
> > compared to the "First" allergyRout_Code-value. This migth be correct, but if
> > you want to check against every value of allergyRout_Code, you should ommit
> > the "First"- function (if they are in the same dataset)
> > =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> > "check" , "checked" )
> >
> >
> > "Angela" wrote:
> >
> > > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > > check , checked )
> > >
> > > (the image name is check and checked)
> > >
> > > "Antoon" wrote:
> > >
> > > > can you post the expression
> > > >
> > > > "Angela" wrote:
> > > >
> > > > > hi there,
> > > > > i using an expression at the data value with the IIF statement to call the
> > > > > image which i have embeded 2 images and wanna to display them on the table
> > > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > > 'check' is not declared.
> > > > > how can i resolve this problem or is there any other alternative for mi to
> > > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > > display the different image.
> > > > >
> > > > > thanks in advance
> > > > > Angela
> > > > >

Im sure this is an easy one...Error trap to skip over a "bad" object.

Hello, I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it. It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed. Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped. I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.

DECLARE @.DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
Print @.DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO(rod.weir@.gmail.com) writes:
> Hello, I have the following code to iterate through each view in a SQL
> Server and call the "sp_refreshview" command against it. It works
> great until it finds a view that is damaged, or otherwise cannot be
> refreshed. Then the whole routine stops working.
> Can someone please help me re-write this code so that any views that
> fail the "sp_refreshview" command get skipped. I'm sure it's just a
> matter of putting some basic error trapping into the loop, but I've had
> a few goes at it and failed.

If you are on SQL 2005, lookup TRY-CATCH in Books Online.

If you are on SQL 2000, you could possibly do the linked-server trick:
http://www.sommarskog.se/error-hand...#linked-servers.

I'm not into refreshing views myself, but I can't think of a way to
detect this condition before-hand.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I need to refresh all views because there are some views that have
embedded views within them, using a Select * statement. When the
underlying view changes (new column etc), the parent view does not pick
up the new column in the embedded view that it references.

Using SQL Server 2000. Surely there must be a simple way to trap the
error and skip over it right?

Perhaps just after the following line...

EXEC sp_refreshview @.DatabaseObject

...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
primarily a VB developer, so this TSQL has got me a little puzzled.

I'll give your website a read. Thanks again.|||> ...you examine @.@.Error and ignore or continue in the loop?

Some errors will abort the batch so you are SOL after the error. If the
linked server doesn't work for you, you might try preceeding the
sp_refreshview with a select statement with SET FMTONLY ON. That will allow
you detect the error and skip the sp_refreshview for problem views.

DECLARE @.DatabaseObject nvarchar(261)
DECLARE ObjectCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW'

OPEN ObjectCursor
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
IF @.@.FETCH_STATUS = -1 BREAK
PRINT 'Refreshing view ' + @.DatabaseObject
EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject)
IF @.@.ERROR = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
PRINT 'View ' + @.DatabaseObject + ' refreshed'
END
ELSE
BEGIN
PRINT 'Error refreshing view ' + @.DatabaseObject
END
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rod.weir@.gmail.com> wrote in message
news:1143705825.644611.51750@.u72g2000cwu.googlegro ups.com...
> Thanks Erland,
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement. When the
> underlying view changes (new column etc), the parent view does not pick
> up the new column in the embedded view that it references.
> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.
> I'll give your website a read. Thanks again.|||[My newsserver had an outage, and my original post got lost. Now that it's
back, I'm reposting]

(rod.weir@.gmail.com) writes:
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement.

Did anyone tell you that this is bad practice? :-)

> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.

The problem is that there are quite few errors that abort the batch, and
those you cannot trap easily in SQL 2000. I seem to recall that refreshview
errors belongs to this group. The linked-server trick is a serious kludge,
but for this case it could be worth the pain.

Then again, if you are a VB developer, just code the loop in a VB program
or in VB script. That's probably easier than setting up linked servers for
this task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Dan,

Many thanks for your response. This code does exactly what I'm after.
It skipped over the bad queries and kept refreshing the good ones. I
modified the following line to speed it up a little bit.

EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject + 'Where 1=0')

Notice the Where 1 = 0 clause? Much quicker now.

Thanks Dan and Erland. Problem solved.

p.s. Erland. I am going to start another thread on the evils of
embedded queries. I have heard a lot of people say that this is a bad
practice, however I've never heard any really compelling evidence to
say why.