I have a developer that has databound datagrids as follows:
Datagrid.datasource= DataSet (yes, the compiler allows this)
The dataset is returned from stored procedures. Each of the sps return
a single recordset, but periodically we are seeing some
nondeterministic behavior where the code for the datasource above is
causing a runtime error. More than one recordset is being returned for
some reason.
Of course, the easiest way to fix this is to have the developer change
code to
Datagrid.datasource = DataSet.Table("Some Table or Index"),
but is there are anything that can be done from a SQL Server
standpoint, that will only return one recordset ?
TIAMJKulangara wrote:
> I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
The usual cause of extra resultsets is the lack of a "SET NOCOUNT ON"
statement in the stored procedure. This statement tells the query engine to
suppress the informational "x rows affected" messages that you see when
running queries in QA. These messages get sent as extra resultsets.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob. I checked each of the sps called and they all have "SET
NOCOUNT ON". The strange thing here is that we don't always get an
error. Most of the time (90%) applications rnus without a problem.|||"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1140627492.706939.31610@.z14g2000cwz.googlegroups.com...
>I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
Run the stored procedure in Query Analyser (with parameters that cause the
error in your page).
Check if any messages are generated.
I had a similar problem with a stored procedure and found that it was
because of null values in aggregate functions.
Adding SET ANSI_WARNINGS OFF to the procedure solved the problem.
Note: make sure ANSI_WARNINGS is ON in QA options before doing the test.
Showing posts with label databound. Show all posts
Showing posts with label databound. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
Databound Image to header...export issue
I followed the instructions from this post...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384621&SiteID=1
on how to add databound images into a report header.
The issue I am encountering is exporting to PDF. If the report spans multiple pages, in PDF the image only shows up on the first page..and broken on all other pages.
I have tied the hidden image to the table that spans multiple pages. Shows ok in HTML but breaks when exported.
Thanks for any help.How did tie the hidden image to the table? Through RepeatWith? If so, the issue here is probably that RepeatWith is not supported in PDF. You can try adding a row to the table, mark it repeat on every page, and place the hidden image in the table row to make sure it gets repeated on each page.|||
try this
u can do that with the help of parameters
1) add a calculated field in dataset named imagebase64 in that add the following expression
Convert.ToBase64String(Fields!Photo.value)
2)Add report Parameter named image
set the datatype as string.
in the default value select the dataset and value field as imagebase64(calculated field)
set allow blank-true
set prompt-image
set hidden-true|||
I am having the same issue. The page header image shows in "Preview" but when exported to PDF it does not appear on any page, IE, the "red X" appears. The image works fine when exported to Excel.
Did you ever resolve this issue?
Thanks,
John
Databound Image to header...export issue
I followed the instructions from this post...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384621&SiteID=1
on how to add databound images into a report header.
The issue I am encountering is exporting to PDF. If the report spans multiple pages, in PDF the image only shows up on the first page..and broken on all other pages.
I have tied the hidden image to the table that spans multiple pages. Shows ok in HTML but breaks when exported.
Thanks for any help.How did tie the hidden image to the table? Through RepeatWith? If so, the issue here is probably that RepeatWith is not supported in PDF. You can try adding a row to the table, mark it repeat on every page, and place the hidden image in the table row to make sure it gets repeated on each page.|||
try this
u can do that with the help of parameters
1) add a calculated field in dataset named imagebase64 in that add the following expression
Convert.ToBase64String(Fields!Photo.value)
2)Add report Parameter named image
set the datatype as string.
in the default value select the dataset and value field as imagebase64(calculated field)
set allow blank-true
set prompt-image
set hidden-true|||
I am having the same issue. The page header image shows in "Preview" but when exported to PDF it does not appear on any page, IE, the "red X" appears. The image works fine when exported to Excel.
Did you ever resolve this issue?
Thanks,
John
Databound Image to header...export issue
I followed the instructions from this post...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384621&SiteID=1
on how to add databound images into a report header.
The issue I am encountering is exporting to PDF. If the report spans multiple pages, in PDF the image only shows up on the first page..and broken on all other pages.
I have tied the hidden image to the table that spans multiple pages. Shows ok in HTML but breaks when exported.
Thanks for any help.
How did tie the hidden image to the table? Through RepeatWith? If so, the issue here is probably that RepeatWith is not supported in PDF. You can try adding a row to the table, mark it repeat on every page, and place the hidden image in the table row to make sure it gets repeated on each page.|||
try this
u can do that with the help of parameters
1) add a calculated field in dataset named imagebase64 in that add the following expression
Convert.ToBase64String(Fields!Photo.value)
2)Add report Parameter named image
set the datatype as string. in the default value select the dataset and value field as imagebase64(calculated field) set allow blank-true set prompt-image set hidden-true|||I am having the same issue. The page header image shows in "Preview" but when exported to PDF it does not appear on any page, IE, the "red X" appears. The image works fine when exported to Excel.
Did you ever resolve this issue?
Thanks,
John
Databound image in report header
After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.
Does anybody have any advice? Is there a solution to this problem?
One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.Private m_headerImage As Object
Public Function GetHeaderImage() As object
' be sure to add appropriate error handling
If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If
Return m_headerImage
End Function
Set the image Value property to
=Code.GetHeaderImage()
Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.
More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx
Ian|||
Mark, which version of SSRS are you using?
Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.
-- Robert
|||Robert,I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.
-Mark
|||Robert,
How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.
Thanks,
Mark
|||
Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.
Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Robert,
I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?
Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.
Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.
Also, do you still have the sample report?
Thanks,
John
Databound image in report header
After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.
Does anybody have any advice? Is there a solution to this problem?
One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.Private m_headerImage As Object
Public Function GetHeaderImage() As object
' be sure to add appropriate error handling
If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If
Return m_headerImage
End Function
Set the image Value property to
=Code.GetHeaderImage()
Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.
More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx
Ian|||
Mark, which version of SSRS are you using?
Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.
-- Robert
|||Robert,I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.
-Mark
|||Robert,
How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.
Thanks,
Mark
|||
Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.
Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Robert,
I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?
Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.
Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.
Also, do you still have the sample report?
Thanks,
John
Subscribe to:
Posts (Atom)