Image Property from Business object in Reporting Service 2005

Hi

I have a question from Reporting service 2005
I use Business Objects in My Report
and one of My object's property's type is "Image" or "BitMap"

when i Drop that property in my report and set value in "Fields!MyPicture.Value" and Run that then i see "#Error"

when i drop an "Image" from Toolbar and set that in "Database" and set value in "Fields!MyPicture.Value" and Run that then i see a red "X"

how can i show my image property from business object in my Report (.rdlc)

Thanks alot

Please read my answer in your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=467270&SiteID=1

-- Robert

Image Property from Business object in Reporting Service 2005

Hi

I have a question from Reporting service 2005
I use Business Objects in My Report
and one of My object's property's type is "Image" or "BitMap"

when i Drop that property in my report and set value in "Fields!MyPicture.Value" and Run that then i see "#Error"

when i drop an "Image" from Toolbar and set that in "Database" and set value in "Fields!MyPicture.Value" and Run that then i see a red "X"

how can i show my image property from business object in my Report (.rdlc)

thanks alotI have not used RS2005, but in Rs2000 that you need drop and drag a image component to your report and set database filed to this image, and then drag the image to you grid or matrix.
hope this can help you

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 from Business Object property?

Hello!
I'm using the local reporting services engine within a WinForm
application. I have a Business Object that contains a field which returns a
Bitmap. The Bitmap is created from other values within the same business
object. An example of the properties are below.
I'm binding my report to my BusinessObject and everything is going quite
well with the exception of displaying the graph bitmap within an Image
ReportItem. I've tried different Source types for the Image properties -
Database, Embedded, External but none seem to like the Bitmap within a
property on the Object Data Source.
I saw a few ways to develop a CustomerReportItem and will head down that
road but it seems like I'm missing something really simple. Any thoughts or
helpful tips are welcome.
Thanx,
Mike Q
public class MyBusinessObject
{
public decimal IndexValue
{
get
{
some calculations;
}
}
public Bitmap IndexGraph
{
get
{
Bitmap Result = Bitmap.FromFile("GraphTemplate.bmp");
Graphics g = Graphics.FromImage(Result);
Pen RedPen = new Pen(Brushes.Red, 5);
g.DrawLine(RedPen, 40, 0, 40, 50); // These values are calculated based on
the Index value.
return Result;
}
}
}I found the answer - instead of returning a Bitmap - return a byte[] from
the business object property
Thanx,
-q
"Mike Q" <MQuinn_q@.Yahoo.com> wrote in message
news:OMHy2SVrIHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hello!
> I'm using the local reporting services engine within a WinForm
> application. I have a Business Object that contains a field which returns
> a Bitmap. The Bitmap is created from other values within the same
> business object. An example of the properties are below.
> I'm binding my report to my BusinessObject and everything is going quite
> well with the exception of displaying the graph bitmap within an Image
> ReportItem. I've tried different Source types for the Image properties -
> Database, Embedded, External but none seem to like the Bitmap within a
> property on the Object Data Source.
> I saw a few ways to develop a CustomerReportItem and will head down that
> road but it seems like I'm missing something really simple. Any thoughts
> or helpful tips are welcome.
> Thanx,
> Mike Q
>
> public class MyBusinessObject
> {
> public decimal IndexValue
> {
> get
> {
> some calculations;
> }
> }
> public Bitmap IndexGraph
> {
> get
> {
> Bitmap Result = Bitmap.FromFile("GraphTemplate.bmp");
> Graphics g = Graphics.FromImage(Result);
> Pen RedPen = new Pen(Brushes.Red, 5);
> g.DrawLine(RedPen, 40, 0, 40, 50); // These values are calculated based
> on the Index value.
> return Result;
> }
> }
> }

Image data type

I have been asked to write a piece of code that will insert an image object into a database using a stored procedure and the Microsoft Enterprise Library. Has anyone done this before? Do you have any code examples about how to update a database with an image datatype that needs to be chunked, etc...

In this instance, I need to open up a word document and save the contents as an image in a database.

basically you want to get the uploaded file into a byte array and then you can assign it to a sql param of type image, no chunking needed

here's a snip from one of my projects. I'm using business objects so its not showing the actual sql code but behind the scenes it is just assigning it to a param of type image

byte[] fileBytes = new byte[replacementFileInput.ContentLength];
Stream contentStream = replacementFileInput.FileContent;

contentStream.Read(fileBytes, 0, (int) replacementFileInput.ContentLength);
contentStream.Close();

DocumentFile newFile = new DocumentFile(true);
newFile.DocumentID = originalDocument.ID;
newFile.DocumentType = documentType.Name;
newFile.DocumentImage = fileBytes;
newFile.Save();

I should note that in my example I'm usingNeatUpload, so replacementFileInput is the NeatUpload file input and has a little different syntax then the regular .NET file input

NeatUpload can handle large file uploads gracefully with a progress bar and is free and open source

Hope it helps,

Joe|||

I downloaded the NeatUpload. This does look pretty cool. However, in the instance I'm currently in, I have a Word Document already on the server that I need to convert to a binary object and upload to SQL. The answer is probably right in front of me, so... following your initial tip.

How do I convert an existingWord Document into a byte array?

|||Yes, you can easily do this. In my previous example I was using the contentStream from the uploaded file, but any subclass of stream could be used, so in your case FileStream which you can get with something like this:

FileStream fileStream = File.Open("pathtoyourfile", FileMode.Open);
byte[] fileBytes = new byte[fileStream.Length];
fileStream.Read(fileBytes, 0, (int)fileStream.Length);

now fileBytes has the file and you can assign it to your sql image param

Hope it helps,

Joe|||

This is great, thanks! I think I got the record in there... Now I just have to read it!

Thanks for your help, Joe.

-Scott

|||

Okay, I have been struggling to convert the following code from C# to VB. Particularly at the point of creating the New Byte. Can anyone help?

SqlCommand cmdSelect=new SqlCommand("select Picture" +
" from tblImgData whereID=@.ID",this.sqlConnection1);
cmdSelect.Parameters.Add("@.ID",SqlDbType.Int,4);
cmdSelect.Parameters["@.ID"].Value=this.editID.Text;

this.sqlConnection1.Open();
byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
string strfn=Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs=new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg,0,barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image=Image.FromFile(strfn);

Image - Paste into table vs. Insert Object linked or unlinked

I have a form in which one of the columns will show a picture. Is there any
difference when using the form if I paste in the image or use "Insert
Object".
When looking at the underlying table after closing the form:
If the image is pasted, it shows "Picture" as the value in the field
If the image is put in via Insert Object and it is unlinked, it shows
"Package"
When I look at the "Bound Object Frame", I see no difference in values. I
see identical images when viewing the form.
My initial take on it is if the image won't change, go ahead and paste it
in. If the document may change, then it should be linked.
Is there a preferred method?
What program are you using?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dkline" <Dkline001@.comcast.net> wrote in message
news:%23v1xUqpHFHA.1860@.TK2MSFTNGP15.phx.gbl...
>I have a form in which one of the columns will show a picture. Is there any
>difference when using the form if I paste in the image or use "Insert
>Object".
> When looking at the underlying table after closing the form:
> If the image is pasted, it shows "Picture" as the value in the field
> If the image is put in via Insert Object and it is unlinked, it shows
> "Package"
> When I look at the "Bound Object Frame", I see no difference in values. I
> see identical images when viewing the form.
> My initial take on it is if the image won't change, go ahead and paste it
> in. If the document may change, then it should be linked.
> Is there a preferred method?
>

Image - Paste into table vs. Insert Object linked or unlinked

I have a form in which one of the columns will show a picture. Is there any
difference when using the form if I paste in the image or use "Insert
Object".
When looking at the underlying table after closing the form:
If the image is pasted, it shows "Picture" as the value in the field
If the image is put in via Insert Object and it is unlinked, it shows
"Package"
When I look at the "Bound Object Frame", I see no difference in values. I
see identical images when viewing the form.
My initial take on it is if the image won't change, go ahead and paste it
in. If the document may change, then it should be linked.
Is there a preferred method?What program are you using?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dkline" <Dkline001@.comcast.net> wrote in message
news:%23v1xUqpHFHA.1860@.TK2MSFTNGP15.phx.gbl...
>I have a form in which one of the columns will show a picture. Is there any
>difference when using the form if I paste in the image or use "Insert
>Object".
> When looking at the underlying table after closing the form:
> If the image is pasted, it shows "Picture" as the value in the field
> If the image is put in via Insert Object and it is unlinked, it shows
> "Package"
> When I look at the "Bound Object Frame", I see no difference in values. I
> see identical images when viewing the form.
> My initial take on it is if the image won't change, go ahead and paste it
> in. If the document may change, then it should be linked.
> Is there a preferred method?
>

Image - Paste into table vs. Insert Object linked or unlinked

I have a form in which one of the columns will show a picture. Is there any
difference when using the form if I paste in the image or use "Insert
Object".
When looking at the underlying table after closing the form:
If the image is pasted, it shows "Picture" as the value in the field
If the image is put in via Insert Object and it is unlinked, it shows
"Package"
When I look at the "Bound Object Frame", I see no difference in values. I
see identical images when viewing the form.
My initial take on it is if the image won't change, go ahead and paste it
in. If the document may change, then it should be linked.
Is there a preferred method?What program are you using?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dkline" <Dkline001@.comcast.net> wrote in message
news:%23v1xUqpHFHA.1860@.TK2MSFTNGP15.phx.gbl...
>I have a form in which one of the columns will show a picture. Is there any
>difference when using the form if I paste in the image or use "Insert
>Object".
> When looking at the underlying table after closing the form:
> If the image is pasted, it shows "Picture" as the value in the field
> If the image is put in via Insert Object and it is unlinked, it shows
> "Package"
> When I look at the "Bound Object Frame", I see no difference in values. I
> see identical images when viewing the form.
> My initial take on it is if the image won't change, go ahead and paste it
> in. If the document may change, then it should be linked.
> Is there a preferred method?
>

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.