I need to change the logo on my reports according to a parameter that I pass from VB.NET.
What is the best way to load the image into the report?
I have attempted to use a dataset:
I created a logo field in my dataset
I loaded the correct image in VB.NET
I then assigned the 'logo' field in the dataset to this image
My problem is that I don't know what the data type of the logo field should be - I have created the dataset manually and the images are not in a database.
This is my code:
'Load image into ByteArray
Dim fs As New System.IO.FileStream("C:\temp\test.jpg", IO.FileMode.Open)
Dim nBytes As Integer = fs.Length
Dim ByteArray(nBytes) As Byte
Dim nBytesRead As Integer = fs.Read(ByteArray, 0, nBytes)
fs.Read(ByteArray, 0, Convert.ToInt32(fs.Length.ToString(), 10) - 1)
'Set logo field to this image
Dim tbl As New mySchemas.myDataTable
Dim rw As DataRow = tbl.NewRow
rw("logo") = ByteArray 'AN ERROR OCCURS ON THIS LINE
tbl.Rows.Add(rw)
Dim ds As New DataSet
ds.Tables.Add(tbl)
Is this the best way to do load an image into crystal? If so what should the datatype of the logo field be in the dataset? I tried Byte but that did not work.
Thanks for any help
ElaineElaine,
I'll preface this by saying: I don't use CR10, and don't use .NET with CR.
From your code you are declaring a row object to hold the picture.
Shouldn't you be using a "field" object, or whatever the .NET equivalent is?
Field type should be OLE Object/BLOB(BinaryLargeOBject)/BitStream or similar.
Dave|||Thanks for your reply Dave. All sorted now.
The data type I needed turned out to be 'hexbinary'
Incase anyone has the same problem this is the solution:
Dim data As New DataSet()
Dim row As DataRow
data.Tables.Add("Images")
data.Tables(0).Columns.Add("img", System.Type.GetType("System.Byte[]"))
Dim fs As New FileStream("C:\test.jpg", FileMode.Open)
Dim br As New BinaryReader(fs)
row = data.Tables(0).NewRow()
row(0) = br.ReadBytes(br.BaseStream.Length)
data.Tables(0).Rows.Add(row)
br.close
fs.close
br = Nothing
fs = Nothing
' CrystalReport1 is a strongly typed report created in Visual Studio.
Dim cr As New CrystalReport1()
cr.SetDataSource(data)
CrystalReportViewer1.ReportSource = cr|||Are you using the version of crystal reports that came with visual net??
I create the typed dataset with a field hexBinary and load the pictur there but how can I put that picture in the report it shows as a number and no image apearssql
Showing posts with label reports. Show all posts
Showing posts with label reports. Show all posts
Tuesday, March 27, 2012
Change from Crystal Reports to SQL Server 2005 Reporting Services?
Hi all,
We have a web application that generates statistical reports in PDF format
based on customer data and we're thinking of migrating to SQL Server 2005
Reporting Services but I'd like to know if what we are doing is possible.
Basically, the customer logs onto the system via a regular browser, this
creates a folder on the backend based on their session info. Then they
select a couple of different reports, enter the required parameters and hit
print. The web app runs a VB program that loads the RPT file, modifies the
connections so it's getting the data from the correct customer database,
then exports the report as a PDF into the previously created folder. The
user is then redirected to that file so they can download it.
Whether the report is shown in a browser window or not is all dependent on
the customers settings for PDF documents. We don't need interactive
web-based reports, we don't need to drill-down, we don't need OLAP. It's a
simple "I want a report", "Here it is" scenario, but there is the definate
potential of having several hundred customers all requesting reports at the
same time, which is something our current solution can't handle.
Comments and suggestions are welcome.2005 has the ability to change the datasource at runtime. You do not need to
do any of this creating a folder in the background etc. Everything that you
want is really very simple.When your web app renders it can request it as
PDF. Also, you can send the report a parameter and based on that parameter
pick the appropriate database.
Implementing your security is the biggest issue (and what comes up the most
with an internet application). You will need to do forms based
authentication (which I have not done and do not know much about).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> We have a web application that generates statistical reports in PDF format
> based on customer data and we're thinking of migrating to SQL Server 2005
> Reporting Services but I'd like to know if what we are doing is possible.
> Basically, the customer logs onto the system via a regular browser, this
> creates a folder on the backend based on their session info. Then they
> select a couple of different reports, enter the required parameters and
> hit print. The web app runs a VB program that loads the RPT file, modifies
> the connections so it's getting the data from the correct customer
> database, then exports the report as a PDF into the previously created
> folder. The user is then redirected to that file so they can download it.
> Whether the report is shown in a browser window or not is all dependent on
> the customers settings for PDF documents. We don't need interactive
> web-based reports, we don't need to drill-down, we don't need OLAP. It's a
> simple "I want a report", "Here it is" scenario, but there is the definate
> potential of having several hundred customers all requesting reports at
> the same time, which is something our current solution can't handle.
> Comments and suggestions are welcome.
>|||Thanks for the reply Bruce. Application security is already handled.
Maybe you can guide me through my understanding of how SSRS would handle
this?
a) User opens web-page.
b) User selects report from drop-down list, and the postback shows the
correct parameter options.
c) User enters the parameter values he needs.
d) User clicks on print.
e) Pop-up appears asking the user to wait while their report is prepared.
f) A few minutes later, the pop-up disappears to be replaced by the usual
File Download dialog.
g) User clicks Save and the PDF is saved to their PC.
We can do this in Crystal Reports without any fuss, but when more than 30
users are performing the same actions at the same time, the queue of report
requests ends up being pretty large as it takes quite some time to produce
each report. We're hoping that SSRS will let us "farm" out the report
generation to multiple servers.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> 2005 has the ability to change the datasource at runtime. You do not need
> to do any of this creating a folder in the background etc. Everything that
> you want is really very simple.When your web app renders it can request it
> as PDF. Also, you can send the report a parameter and based on that
> parameter pick the appropriate database.
> Implementing your security is the biggest issue (and what comes up the
> most with an internet application). You will need to do forms based
> authentication (which I have not done and do not know much about).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>|||Although implemented in a different way, the scenario described below is no
problem. RS has support for web farms. The Reporting Services app (which is
a 2.0 dotnet application) can run on a web farm. They all use the same
backend database for object/metadata caching. You do have to purchase a SQL
Server 2005 server license for each box running
PDF processing is more resource intensive than html or csv but RS 2005 has
improved this over RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:eqzqBohpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply Bruce. Application security is already handled.
> Maybe you can guide me through my understanding of how SSRS would handle
> this?
> a) User opens web-page.
> b) User selects report from drop-down list, and the postback shows the
> correct parameter options.
> c) User enters the parameter values he needs.
> d) User clicks on print.
> e) Pop-up appears asking the user to wait while their report is prepared.
> f) A few minutes later, the pop-up disappears to be replaced by the usual
> File Download dialog.
> g) User clicks Save and the PDF is saved to their PC.
> We can do this in Crystal Reports without any fuss, but when more than 30
> users are performing the same actions at the same time, the queue of
> report requests ends up being pretty large as it takes quite some time to
> produce each report. We're hoping that SSRS will let us "farm" out the
> report generation to multiple servers.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> 2005 has the ability to change the datasource at runtime. You do not need
>> to do any of this creating a folder in the background etc. Everything
>> that you want is really very simple.When your web app renders it can
>> request it as PDF. Also, you can send the report a parameter and based on
>> that parameter pick the appropriate database.
>> Implementing your security is the biggest issue (and what comes up the
>> most with an internet application). You will need to do forms based
>> authentication (which I have not done and do not know much about).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
>> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>>
>
We have a web application that generates statistical reports in PDF format
based on customer data and we're thinking of migrating to SQL Server 2005
Reporting Services but I'd like to know if what we are doing is possible.
Basically, the customer logs onto the system via a regular browser, this
creates a folder on the backend based on their session info. Then they
select a couple of different reports, enter the required parameters and hit
print. The web app runs a VB program that loads the RPT file, modifies the
connections so it's getting the data from the correct customer database,
then exports the report as a PDF into the previously created folder. The
user is then redirected to that file so they can download it.
Whether the report is shown in a browser window or not is all dependent on
the customers settings for PDF documents. We don't need interactive
web-based reports, we don't need to drill-down, we don't need OLAP. It's a
simple "I want a report", "Here it is" scenario, but there is the definate
potential of having several hundred customers all requesting reports at the
same time, which is something our current solution can't handle.
Comments and suggestions are welcome.2005 has the ability to change the datasource at runtime. You do not need to
do any of this creating a folder in the background etc. Everything that you
want is really very simple.When your web app renders it can request it as
PDF. Also, you can send the report a parameter and based on that parameter
pick the appropriate database.
Implementing your security is the biggest issue (and what comes up the most
with an internet application). You will need to do forms based
authentication (which I have not done and do not know much about).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> We have a web application that generates statistical reports in PDF format
> based on customer data and we're thinking of migrating to SQL Server 2005
> Reporting Services but I'd like to know if what we are doing is possible.
> Basically, the customer logs onto the system via a regular browser, this
> creates a folder on the backend based on their session info. Then they
> select a couple of different reports, enter the required parameters and
> hit print. The web app runs a VB program that loads the RPT file, modifies
> the connections so it's getting the data from the correct customer
> database, then exports the report as a PDF into the previously created
> folder. The user is then redirected to that file so they can download it.
> Whether the report is shown in a browser window or not is all dependent on
> the customers settings for PDF documents. We don't need interactive
> web-based reports, we don't need to drill-down, we don't need OLAP. It's a
> simple "I want a report", "Here it is" scenario, but there is the definate
> potential of having several hundred customers all requesting reports at
> the same time, which is something our current solution can't handle.
> Comments and suggestions are welcome.
>|||Thanks for the reply Bruce. Application security is already handled.
Maybe you can guide me through my understanding of how SSRS would handle
this?
a) User opens web-page.
b) User selects report from drop-down list, and the postback shows the
correct parameter options.
c) User enters the parameter values he needs.
d) User clicks on print.
e) Pop-up appears asking the user to wait while their report is prepared.
f) A few minutes later, the pop-up disappears to be replaced by the usual
File Download dialog.
g) User clicks Save and the PDF is saved to their PC.
We can do this in Crystal Reports without any fuss, but when more than 30
users are performing the same actions at the same time, the queue of report
requests ends up being pretty large as it takes quite some time to produce
each report. We're hoping that SSRS will let us "farm" out the report
generation to multiple servers.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> 2005 has the ability to change the datasource at runtime. You do not need
> to do any of this creating a folder in the background etc. Everything that
> you want is really very simple.When your web app renders it can request it
> as PDF. Also, you can send the report a parameter and based on that
> parameter pick the appropriate database.
> Implementing your security is the biggest issue (and what comes up the
> most with an internet application). You will need to do forms based
> authentication (which I have not done and do not know much about).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>|||Although implemented in a different way, the scenario described below is no
problem. RS has support for web farms. The Reporting Services app (which is
a 2.0 dotnet application) can run on a web farm. They all use the same
backend database for object/metadata caching. You do have to purchase a SQL
Server 2005 server license for each box running
PDF processing is more resource intensive than html or csv but RS 2005 has
improved this over RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:eqzqBohpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply Bruce. Application security is already handled.
> Maybe you can guide me through my understanding of how SSRS would handle
> this?
> a) User opens web-page.
> b) User selects report from drop-down list, and the postback shows the
> correct parameter options.
> c) User enters the parameter values he needs.
> d) User clicks on print.
> e) Pop-up appears asking the user to wait while their report is prepared.
> f) A few minutes later, the pop-up disappears to be replaced by the usual
> File Download dialog.
> g) User clicks Save and the PDF is saved to their PC.
> We can do this in Crystal Reports without any fuss, but when more than 30
> users are performing the same actions at the same time, the queue of
> report requests ends up being pretty large as it takes quite some time to
> produce each report. We're hoping that SSRS will let us "farm" out the
> report generation to multiple servers.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJlmwOcpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>> 2005 has the ability to change the datasource at runtime. You do not need
>> to do any of this creating a folder in the background etc. Everything
>> that you want is really very simple.When your web app renders it can
>> request it as PDF. Also, you can send the report a parameter and based on
>> that parameter pick the appropriate database.
>> Implementing your security is the biggest issue (and what comes up the
>> most with an internet application). You will need to do forms based
>> authentication (which I have not done and do not know much about).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
>> news:OOzh8DapGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> We have a web application that generates statistical reports in PDF
>> format based on customer data and we're thinking of migrating to SQL
>> Server 2005 Reporting Services but I'd like to know if what we are doing
>> is possible.
>> Basically, the customer logs onto the system via a regular browser, this
>> creates a folder on the backend based on their session info. Then they
>> select a couple of different reports, enter the required parameters and
>> hit print. The web app runs a VB program that loads the RPT file,
>> modifies the connections so it's getting the data from the correct
>> customer database, then exports the report as a PDF into the previously
>> created folder. The user is then redirected to that file so they can
>> download it.
>> Whether the report is shown in a browser window or not is all dependent
>> on the customers settings for PDF documents. We don't need interactive
>> web-based reports, we don't need to drill-down, we don't need OLAP. It's
>> a simple "I want a report", "Here it is" scenario, but there is the
>> definate potential of having several hundred customers all requesting
>> reports at the same time, which is something our current solution can't
>> handle.
>> Comments and suggestions are welcome.
>>
>>
>
Sunday, March 25, 2012
change fonts existing reports
Is it possible to change the font, for example to font-family Arial, for all
the existing reports made in reporting services.
--
regards, pakamSorry, this is not supported.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pakam" <pakam@.discussions.microsoft.com> wrote in message
news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> Is it possible to change the font, for example to font-family Arial, for
> all
> the existing reports made in reporting services.
> --
> regards, pakam|||You could do it dynamically, by using getreportdefinition to get one of the
existing reports as xml, then use SelectNodeList to get all the <FontFamily>
nodes.
Then foreach node in the node list, change the InnerText to the new font &
republish the report
"Robert Bruckner [MSFT]" wrote:
> Sorry, this is not supported.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "pakam" <pakam@.discussions.microsoft.com> wrote in message
> news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > Is it possible to change the font, for example to font-family Arial, for
> > all
> > the existing reports made in reporting services.
> > --
> > regards, pakam
>
>|||Hi mark-s,
Thank you for your reaction. For your solution I have to write code ? I did
find the GetReportDefinition but not the SelectNodeList. Can you explain more
how to do this ? .
I did find out that it is possible to use Edit -> Find and Replace ->
Replace in Files. This is not perfect but it helps to avoid changing every
item. Then you can change all reports in the project to the font-family arial
for example.
Regards pakam
"mark-s" wrote:
> You could do it dynamically, by using getreportdefinition to get one of the
> existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> nodes.
> Then foreach node in the node list, change the InnerText to the new font &
> republish the report
> "Robert Bruckner [MSFT]" wrote:
> > Sorry, this is not supported.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > Is it possible to change the font, for example to font-family Arial, for
> > > all
> > > the existing reports made in reporting services.
> > > --
> > > regards, pakam
> >
> >
> >|||Hi pakam
Yes, you will have to write code. SelectNodeList is in the System.Xml.XPath
namespace.
However, if you only want to change the font once, then Find/Replace might
be easier. I was thinking that you may need to change the font regularly.
"pakam" wrote:
> Hi mark-s,
> Thank you for your reaction. For your solution I have to write code ? I did
> find the GetReportDefinition but not the SelectNodeList. Can you explain more
> how to do this ? .
> I did find out that it is possible to use Edit -> Find and Replace ->
> Replace in Files. This is not perfect but it helps to avoid changing every
> item. Then you can change all reports in the project to the font-family arial
> for example.
> Regards pakam
>
> "mark-s" wrote:
> > You could do it dynamically, by using getreportdefinition to get one of the
> > existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> > nodes.
> > Then foreach node in the node list, change the InnerText to the new font &
> > republish the report
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Sorry, this is not supported.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > > Is it possible to change the font, for example to font-family Arial, for
> > > > all
> > > > the existing reports made in reporting services.
> > > > --
> > > > regards, pakam
> > >
> > >
> > >
the existing reports made in reporting services.
--
regards, pakamSorry, this is not supported.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pakam" <pakam@.discussions.microsoft.com> wrote in message
news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> Is it possible to change the font, for example to font-family Arial, for
> all
> the existing reports made in reporting services.
> --
> regards, pakam|||You could do it dynamically, by using getreportdefinition to get one of the
existing reports as xml, then use SelectNodeList to get all the <FontFamily>
nodes.
Then foreach node in the node list, change the InnerText to the new font &
republish the report
"Robert Bruckner [MSFT]" wrote:
> Sorry, this is not supported.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "pakam" <pakam@.discussions.microsoft.com> wrote in message
> news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > Is it possible to change the font, for example to font-family Arial, for
> > all
> > the existing reports made in reporting services.
> > --
> > regards, pakam
>
>|||Hi mark-s,
Thank you for your reaction. For your solution I have to write code ? I did
find the GetReportDefinition but not the SelectNodeList. Can you explain more
how to do this ? .
I did find out that it is possible to use Edit -> Find and Replace ->
Replace in Files. This is not perfect but it helps to avoid changing every
item. Then you can change all reports in the project to the font-family arial
for example.
Regards pakam
"mark-s" wrote:
> You could do it dynamically, by using getreportdefinition to get one of the
> existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> nodes.
> Then foreach node in the node list, change the InnerText to the new font &
> republish the report
> "Robert Bruckner [MSFT]" wrote:
> > Sorry, this is not supported.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > Is it possible to change the font, for example to font-family Arial, for
> > > all
> > > the existing reports made in reporting services.
> > > --
> > > regards, pakam
> >
> >
> >|||Hi pakam
Yes, you will have to write code. SelectNodeList is in the System.Xml.XPath
namespace.
However, if you only want to change the font once, then Find/Replace might
be easier. I was thinking that you may need to change the font regularly.
"pakam" wrote:
> Hi mark-s,
> Thank you for your reaction. For your solution I have to write code ? I did
> find the GetReportDefinition but not the SelectNodeList. Can you explain more
> how to do this ? .
> I did find out that it is possible to use Edit -> Find and Replace ->
> Replace in Files. This is not perfect but it helps to avoid changing every
> item. Then you can change all reports in the project to the font-family arial
> for example.
> Regards pakam
>
> "mark-s" wrote:
> > You could do it dynamically, by using getreportdefinition to get one of the
> > existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> > nodes.
> > Then foreach node in the node list, change the InnerText to the new font &
> > republish the report
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Sorry, this is not supported.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > > Is it possible to change the font, for example to font-family Arial, for
> > > > all
> > > > the existing reports made in reporting services.
> > > > --
> > > > regards, pakam
> > >
> > >
> > >
Tuesday, March 20, 2012
Change datasource location
All my reports are using odbc connection to connect to database.
Anytime database server is changed I have to manually update the
datasource in each report. My question is there a better way to do
this?
Thanks in Advance,
Bhavnawhat version of crystal u r using?
Using front end interface u can set datasource..|||I am using crystal 10. I know how to update datasource in the report. What I would like to know how to update all the reports datasource at one time instead of opening up each report and changing the datasource.
Thanks!sql
Anytime database server is changed I have to manually update the
datasource in each report. My question is there a better way to do
this?
Thanks in Advance,
Bhavnawhat version of crystal u r using?
Using front end interface u can set datasource..|||I am using crystal 10. I know how to update datasource in the report. What I would like to know how to update all the reports datasource at one time instead of opening up each report and changing the datasource.
Thanks!sql
Change DataSource between Development and Production
I have some existing .NET app, and so I'd like to integrate the RS w/
them.
Reports created by RS is very impressive, now I need to think about
integration. Two choices for me:
1. URL Access
2. Web Services
Option 1 is the fastest and efficient way, has the followings:
1) DataSource in dev and prod are different, doesn't seem like easy to
change them w/ modifying in the project.
2) SessionID, UserName and Password and other parameters are exposed in
URL unless using SSL, otherwise not secure.
Option 2 needs more coding just like a normal .NET projects but w/o
security concerns, still have to worry about changing DataSource stuff.
For example for .rdl
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
Our existing .NET has its own web.config file works like a .ini, we put
in all data source connection there, when move to production,
Production Release team needs to change SQL box name, UserID and
password inside of the web.config. Now, how do they change them in
.rdl?
Thanks in advance.Couple of things to consider. First, datasource should be a non-problem if
you use shared datasources (which I recommend). The shared datasource has to
have the same name in both production and dev but that is it. The default
when you deploy is to not overwrite the datasource so once you set it up in
production it will be undisturbed. Second, I suggest using a specific
username and password for retrieving the data from wherever you are getting
it. I create a special readonly user that is used by RS only. This is great
for security since it is readonly and you benefit from connection polling
which you would not if the user account of the user requesting the report is
used. If you report needs to include the user in the where clause (or you
need to use a filter with the username) then user the global variable
User!UserID.
As I said, rdl does not change at all for the datasource.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hifchan@.yahoo.com> wrote in message
news:1108586517.346073.261520@.c13g2000cwb.googlegroups.com...
> I have some existing .NET app, and so I'd like to integrate the RS w/
> them.
> Reports created by RS is very impressive, now I need to think about
> integration. Two choices for me:
> 1. URL Access
> 2. Web Services
> Option 1 is the fastest and efficient way, has the followings:
> 1) DataSource in dev and prod are different, doesn't seem like easy to
> change them w/ modifying in the project.
> 2) SessionID, UserName and Password and other parameters are exposed in
> URL unless using SSL, otherwise not secure.
> Option 2 needs more coding just like a normal .NET projects but w/o
> security concerns, still have to worry about changing DataSource stuff.
> For example for .rdl
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
> Our existing .NET has its own web.config file works like a .ini, we put
> in all data source connection there, when move to production,
> Production Release team needs to change SQL box name, UserID and
> password inside of the web.config. Now, how do they change them in
> .rdl?
> Thanks in advance.
>|||Bruce,
You meant
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
not matter as long as the .rdl reference the same <Name> only?|||Yes this is correct. The data source ID is just an internal id used by
report designer only. That is also the reason why the DataSourceID element
is in the report designer namespace: <rd:DataSourceID>. The report server
ignores these ids.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108657866.193950.180150@.g14g2000cwa.googlegroups.com...
> Bruce,
> You meant
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
> not matter as long as the .rdl reference the same <Name> only?
>
them.
Reports created by RS is very impressive, now I need to think about
integration. Two choices for me:
1. URL Access
2. Web Services
Option 1 is the fastest and efficient way, has the followings:
1) DataSource in dev and prod are different, doesn't seem like easy to
change them w/ modifying in the project.
2) SessionID, UserName and Password and other parameters are exposed in
URL unless using SSL, otherwise not secure.
Option 2 needs more coding just like a normal .NET projects but w/o
security concerns, still have to worry about changing DataSource stuff.
For example for .rdl
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
Our existing .NET has its own web.config file works like a .ini, we put
in all data source connection there, when move to production,
Production Release team needs to change SQL box name, UserID and
password inside of the web.config. Now, how do they change them in
.rdl?
Thanks in advance.Couple of things to consider. First, datasource should be a non-problem if
you use shared datasources (which I recommend). The shared datasource has to
have the same name in both production and dev but that is it. The default
when you deploy is to not overwrite the datasource so once you set it up in
production it will be undisturbed. Second, I suggest using a specific
username and password for retrieving the data from wherever you are getting
it. I create a special readonly user that is used by RS only. This is great
for security since it is readonly and you benefit from connection polling
which you would not if the user account of the user requesting the report is
used. If you report needs to include the user in the where clause (or you
need to use a filter with the username) then user the global variable
User!UserID.
As I said, rdl does not change at all for the datasource.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hifchan@.yahoo.com> wrote in message
news:1108586517.346073.261520@.c13g2000cwb.googlegroups.com...
> I have some existing .NET app, and so I'd like to integrate the RS w/
> them.
> Reports created by RS is very impressive, now I need to think about
> integration. Two choices for me:
> 1. URL Access
> 2. Web Services
> Option 1 is the fastest and efficient way, has the followings:
> 1) DataSource in dev and prod are different, doesn't seem like easy to
> change them w/ modifying in the project.
> 2) SessionID, UserName and Password and other parameters are exposed in
> URL unless using SSL, otherwise not secure.
> Option 2 needs more coding just like a normal .NET projects but w/o
> security concerns, still have to worry about changing DataSource stuff.
> For example for .rdl
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
> Our existing .NET has its own web.config file works like a .ini, we put
> in all data source connection there, when move to production,
> Production Release team needs to change SQL box name, UserID and
> password inside of the web.config. Now, how do they change them in
> .rdl?
> Thanks in advance.
>|||Bruce,
You meant
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
not matter as long as the .rdl reference the same <Name> only?|||Yes this is correct. The data source ID is just an internal id used by
report designer only. That is also the reason why the DataSourceID element
is in the report designer namespace: <rd:DataSourceID>. The report server
ignores these ids.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108657866.193950.180150@.g14g2000cwa.googlegroups.com...
> Bruce,
> You meant
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
> not matter as long as the .rdl reference the same <Name> only?
>
Change Dataset on existing reports
Hi All,
I have a set of reports already built. I want to use the same reports using a different dataset(different server). I have tried to add a new datasource and data set, then set the data tab of the report to reflect the new dataset. All seems fine, but when I deploy the report, it is deployed with the new dataset into the new location, but the data displayed is from the original dataset. Any pointers?
regards
Have you tried getting out of report manager and going back in? Refreshing. Something sounds like it's pointing to the wrong server. The report is doing what you have told it to do. Check your data sources and check your report on the report manager to see where the data source is pointing. It may be pointing somewhere else.|||I have done what you suggested. Still not working. Odd thing is, if I go to the report properties in the RS website and look at the data sources, both data sources are listed. Let me be clear on what I am looking for here, so I dont waste anyones time. I have 2databases I want to access and have 1 RS web server. I want to set up 2 folders on the RS and in each of the folders are the same reports , pointing at different data sources. So in the report designer I created a report and deployed it for datasource#1. Then I added a new datasource (#2) and created a new dataset with the same query from #1, but used #2 as the datasource. This is now selectable from the dropdown on the data tab in the designer. When I run the query from the data tab in the designer, the results are correct. They reflect the data from the selected datasource. But in deployed and preview mode, the report is reporting the data from the original datasource.|||Did you delete the original dataset from this. if not delete the original data set and keep only the new dataset you added.
Then select the layout tab and select the table properties and change the Datasetname to the new dataset name.
|||Problem is, I am trying to d this and keep both datasets. Any time I have to deploy report updates, I have to push it for both datasources. I would rather not delete and creaqte for each deployment. But, if thats the way it is... thats the way it is.|||Often the report properties persist from old versions. You may want to delete the report on report server using the manager webpage, and then repost the report. As long as you are using two different named datasources, you should be ok. If the datasources have the same name, make sure the deploy path for the second one is going to a different folder. Remember that the deploy path for the report and datasource are separate properties of the VS2005 project.
Hope that helps.
sqlMonday, March 19, 2012
Change data source name
Hi all,
I have created my reports using MDB file. Now I have changed my DB to SQL Server.
I can go and change my Datasource loaction on my machine and the reports work fine. But I need to distribute these reports to all my clients.
And all have different server names.
I am using CRX and VB.NET.
TnxYou can change the location by using Datasource property(or its equivalent) in vb.net
I have created my reports using MDB file. Now I have changed my DB to SQL Server.
I can go and change my Datasource loaction on my machine and the reports work fine. But I need to distribute these reports to all my clients.
And all have different server names.
I am using CRX and VB.NET.
TnxYou can change the location by using Datasource property(or its equivalent) in vb.net
Change Data Source at run time
Hi,
I am using a web Report viewer control for viewing reports on a SRS 2005
report server.
Is it possible to change the data source of the report at run-time?
e.g. I have 2 identical databases - the live database and a test
database. I want to be able to set the data source to point to one of
these depending whether I am testing or not.
This was easily achieved with Crystal, but I cannot work out how to do
it using SQL Reporting Services.
Any help apprectiated.
Thanks.
PaulIf you are using the VS 2005 viewer control then I think you can set it in
there. Another option might be to base the report on a stored procedure
that returns records based on a parameter sent. For example, if parameter =1 then select from live database and if = 0 select from test database.
David
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul|||see ths 2005 BOL at
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul
I am using a web Report viewer control for viewing reports on a SRS 2005
report server.
Is it possible to change the data source of the report at run-time?
e.g. I have 2 identical databases - the live database and a test
database. I want to be able to set the data source to point to one of
these depending whether I am testing or not.
This was easily achieved with Crystal, but I cannot work out how to do
it using SQL Reporting Services.
Any help apprectiated.
Thanks.
PaulIf you are using the VS 2005 viewer control then I think you can set it in
there. Another option might be to base the report on a stored procedure
that returns records based on a parameter sent. For example, if parameter =1 then select from live database and if = 0 select from test database.
David
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul|||see ths 2005 BOL at
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
"Paul Cheetham" <PAC.News@.dsl.pipex.com> wrote in message
news:ODJVQZm2HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am using a web Report viewer control for viewing reports on a SRS 2005
> report server.
> Is it possible to change the data source of the report at run-time?
> e.g. I have 2 identical databases - the live database and a test database.
> I want to be able to set the data source to point to one of these
> depending whether I am testing or not.
> This was easily achieved with Crystal, but I cannot work out how to do it
> using SQL Reporting Services.
> Any help apprectiated.
> Thanks.
>
> Paul
Change CSV file to XLS
Hi,
I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.
thanksOriginally posted by krishna
Hi,
I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.
thanks
Why can you not import the csv file? What advantages does importing a .XLS file provide?|||because, it is easy to import excel file than csv format.|||DTS can handle .CSV files just as easy as it can handle .XLS files that is why I was askng for advantages. Sorry I couldn't help.
Originally posted by krishna
because, it is easy to import excel file than csv format.|||I don't see any issues in handling .CSV or .XLS by Excel and SQL server DTS.|||what datasouce i should use ? Microsoft Text-Treiber(*.txt,*.csv)?
If i use this one as datasource for to import CSV file into table it is not converting datetime column. I was thinking of using Microsoft Excel for the datasource thats why i asked about converting csv to xls
I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.
thanksOriginally posted by krishna
Hi,
I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.
thanks
Why can you not import the csv file? What advantages does importing a .XLS file provide?|||because, it is easy to import excel file than csv format.|||DTS can handle .CSV files just as easy as it can handle .XLS files that is why I was askng for advantages. Sorry I couldn't help.
Originally posted by krishna
because, it is easy to import excel file than csv format.|||I don't see any issues in handling .CSV or .XLS by Excel and SQL server DTS.|||what datasouce i should use ? Microsoft Text-Treiber(*.txt,*.csv)?
If i use this one as datasource for to import CSV file into table it is not converting datetime column. I was thinking of using Microsoft Excel for the datasource thats why i asked about converting csv to xls
Sunday, March 11, 2012
Change color of alternating rows
Please help. I need to distinguish blocks of information on some reports and
I need to be able to altenate the colors.
Please advise.
Thank you.Use RowNumber(scope) function to determine the report row is odd or even and
then set its backcolor property accordingly (e.g. set report detail row's
BackColor property to an Expression, like:
=IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
Note, if your report has different groups, you need to pass a correct value
to the "scope" argument of RowNumber() function.
"assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> Please help. I need to distinguish blocks of information on some reports
> and
> I need to be able to altenate the colors.
> Please advise.
> Thank you.|||Thanks so very much.
"Norman Yuan" wrote:
> Use RowNumber(scope) function to determine the report row is odd or even and
> then set its backcolor property accordingly (e.g. set report detail row's
> BackColor property to an Expression, like:
> =IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
> Note, if your report has different groups, you need to pass a correct value
> to the "scope" argument of RowNumber() function.
>
> "assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
> wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> > Please help. I need to distinguish blocks of information on some reports
> > and
> > I need to be able to altenate the colors.
> > Please advise.
> >
> > Thank you.
>
>
I need to be able to altenate the colors.
Please advise.
Thank you.Use RowNumber(scope) function to determine the report row is odd or even and
then set its backcolor property accordingly (e.g. set report detail row's
BackColor property to an Expression, like:
=IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
Note, if your report has different groups, you need to pass a correct value
to the "scope" argument of RowNumber() function.
"assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> Please help. I need to distinguish blocks of information on some reports
> and
> I need to be able to altenate the colors.
> Please advise.
> Thank you.|||Thanks so very much.
"Norman Yuan" wrote:
> Use RowNumber(scope) function to determine the report row is odd or even and
> then set its backcolor property accordingly (e.g. set report detail row's
> BackColor property to an Expression, like:
> =IIF(RowNumber(Nothing) Mod 2=0, "Silver","White")
> Note, if your report has different groups, you need to pass a correct value
> to the "scope" argument of RowNumber() function.
>
> "assistanceappreciated" <assistanceappreciated@.discussions.microsoft.com>
> wrote in message news:E63FD05E-86E1-486A-A0FF-5B90AAB8DFED@.microsoft.com...
> > Please help. I need to distinguish blocks of information on some reports
> > and
> > I need to be able to altenate the colors.
> > Please advise.
> >
> > Thank you.
>
>
Thursday, March 8, 2012
Change Background Color
Hi,
I am using toggle visibility feature in one of my reports and I am trying to change the background color of some cells when I toggle the view. Please help, any help will be appreciated.
Thanks,
-Rohit
To be more precise, there is a parent and it has several children. At first children are hidden, but when I click parent then children become visible. I would like to change the color of the parent when children are visible and go back to transparent when children are hidden. I hope I clearly discribe my question. Thanks.Wednesday, March 7, 2012
Change "Report is being generated" text
Is there a way to modify or suppress the "Report is being generated" text and
image? I am trying to setup reports in the Report Viewer webpart on a
Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
Sharepoint integrated mode), but do not want the user to see that message.
Any suggestions?
Thanks!
dawI think that I answered my own question in this scenario. There is an
Asynchronous Rendering checkbox in the properties of the webpart. That seems
to solve it.
I would be interested to know if there is a way to do this outside of
Sharepoint. I'm not a programmer, so it may be something obvious.
Thanks!
daw
"daw" wrote:
> Is there a way to modify or suppress the "Report is being generated" text and
> image? I am trying to setup reports in the Report Viewer webpart on a
> Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
> Sharepoint integrated mode), but do not want the user to see that message.
> Any suggestions?
> Thanks!
> daw
image? I am trying to setup reports in the Report Viewer webpart on a
Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
Sharepoint integrated mode), but do not want the user to see that message.
Any suggestions?
Thanks!
dawI think that I answered my own question in this scenario. There is an
Asynchronous Rendering checkbox in the properties of the webpart. That seems
to solve it.
I would be interested to know if there is a way to do this outside of
Sharepoint. I'm not a programmer, so it may be something obvious.
Thanks!
daw
"daw" wrote:
> Is there a way to modify or suppress the "Report is being generated" text and
> image? I am trying to setup reports in the Report Viewer webpart on a
> Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
> Sharepoint integrated mode), but do not want the user to see that message.
> Any suggestions?
> Thanks!
> daw
Friday, February 24, 2012
Centralized reports logging
We are using sql server 2000 reporting services. Management needs to know the
list of reports actually used, their frequency, and all the statistics along
with each one of them such as who, when, where, how long etc.
One way to that is to create a table in sql server and log all the facts
related to these attributes when the report(s) are launched at the front end
(asp.net) side. I am thinking out loud that SSRS must be logging this
information some where.
Is there any way to get to it? If yes, then how. I will google and look at
BOL to see if this could be done. In the mean time, any hints, pointers are
greatly appreciated.
Thank you in advance...RS has a series of reports that include packages you schedule (from SQL
Server). I haven't done this yet (I am doing it for RS 2005 but it uses
Integration Services instead of DTS).
RS 2000 steps
1.Reports you can download
2. Create table
3. Create DTS and schedule. This loads the table with info on usage
4. Run or schedule to run the management reports
You definitely do not need to roll you own with this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sqlster" <nospam@.nospam.com> wrote in message
news:9A9FF399-FF68-45D7-B60D-C1A1BF21242F@.microsoft.com...
> We are using sql server 2000 reporting services. Management needs to know
> the
> list of reports actually used, their frequency, and all the statistics
> along
> with each one of them such as who, when, where, how long etc.
> One way to that is to create a table in sql server and log all the facts
> related to these attributes when the report(s) are launched at the front
> end
> (asp.net) side. I am thinking out loud that SSRS must be logging this
> information some where.
> Is there any way to get to it? If yes, then how. I will google and look at
> BOL to see if this could be done. In the mean time, any hints, pointers
> are
> greatly appreciated.
> Thank you in advance...
list of reports actually used, their frequency, and all the statistics along
with each one of them such as who, when, where, how long etc.
One way to that is to create a table in sql server and log all the facts
related to these attributes when the report(s) are launched at the front end
(asp.net) side. I am thinking out loud that SSRS must be logging this
information some where.
Is there any way to get to it? If yes, then how. I will google and look at
BOL to see if this could be done. In the mean time, any hints, pointers are
greatly appreciated.
Thank you in advance...RS has a series of reports that include packages you schedule (from SQL
Server). I haven't done this yet (I am doing it for RS 2005 but it uses
Integration Services instead of DTS).
RS 2000 steps
1.Reports you can download
2. Create table
3. Create DTS and schedule. This loads the table with info on usage
4. Run or schedule to run the management reports
You definitely do not need to roll you own with this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sqlster" <nospam@.nospam.com> wrote in message
news:9A9FF399-FF68-45D7-B60D-C1A1BF21242F@.microsoft.com...
> We are using sql server 2000 reporting services. Management needs to know
> the
> list of reports actually used, their frequency, and all the statistics
> along
> with each one of them such as who, when, where, how long etc.
> One way to that is to create a table in sql server and log all the facts
> related to these attributes when the report(s) are launched at the front
> end
> (asp.net) side. I am thinking out loud that SSRS must be logging this
> information some where.
> Is there any way to get to it? If yes, then how. I will google and look at
> BOL to see if this could be done. In the mean time, any hints, pointers
> are
> greatly appreciated.
> Thank you in advance...
Cell reference in SQL Report
Hi,
Is it possible to use Cell reference in the calculations, in SQL reports.
Like Excel allows "=A1 * B1", where A1 and B1 are two cells with some numeric value. Is it possible to use Cell reference in SQL reports? Like "textbox1 * textbox 2"... or something like that, with textbox1 and textbox2 containing some numeric values.
Thanks
Perm
Yes. You can name a text box and then reference it using ReportItems!. If you have 2 text boxes named txtA and txtB and you want to add the two and put in a third box you can put ReportItems!txtA.value+ReportItems!txtB.value in the third textbox to get the total of the two cells.|||Hi Kim,
Thank you very much for the quick reply!! It works.
Now, the next item I'm stuck on, is the running total of this calculated field. Here is the detail of my RDL:
In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:
"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."
Any suggestions? I appreciate you help on this.
Thanks
Perm
Thursday, February 16, 2012
Causes of allocation errors in tempdb
Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>
Causes of allocation errors in tempdb
Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>
Causes of allocation errors in tempdb
Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
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.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>
Catching report exceptions
Instead of using the Report Manager that comes with Reporting Services, I
have built a custom ASP.NET report interface that launches reports using URL
access. When there is a problem displaying a report, I get the usual
reporting services error, but I would like to redirect to a custom report
page. How do I do that ?
Thanks, Craig"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A040A3CA-2A55-4D2A-8410-75E2B353566D@.microsoft.com...
> Instead of using the Report Manager that comes with Reporting Services, I
> have built a custom ASP.NET report interface that launches reports using
> URL
> access. When there is a problem displaying a report, I get the usual
> reporting services error, but I would like to redirect to a custom report
> page. How do I do that ?
> Thanks, Craig
You can use web service Render method to catch an error, but if you want to
use URL approach, than
grab your's url output by using System.Net.HttpWebRequest ( or WebRequest)
class.
If the output is text based (HTML, CSV) - just convert returned by
WebResponse byte array to - char - string and check it for specific error
messages. If the output has to be binary (PDF, EXCEL, etc.) -just check
ContentType of your System.Net.WebResponse class and if it is "text/html" -
the error has occured ( you can always parse byte array to be sure as I
proposed above).
have built a custom ASP.NET report interface that launches reports using URL
access. When there is a problem displaying a report, I get the usual
reporting services error, but I would like to redirect to a custom report
page. How do I do that ?
Thanks, Craig"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A040A3CA-2A55-4D2A-8410-75E2B353566D@.microsoft.com...
> Instead of using the Report Manager that comes with Reporting Services, I
> have built a custom ASP.NET report interface that launches reports using
> URL
> access. When there is a problem displaying a report, I get the usual
> reporting services error, but I would like to redirect to a custom report
> page. How do I do that ?
> Thanks, Craig
You can use web service Render method to catch an error, but if you want to
use URL approach, than
grab your's url output by using System.Net.HttpWebRequest ( or WebRequest)
class.
If the output is text based (HTML, CSV) - just convert returned by
WebResponse byte array to - char - string and check it for specific error
messages. If the output has to be binary (PDF, EXCEL, etc.) -just check
ContentType of your System.Net.WebResponse class and if it is "text/html" -
the error has occured ( you can always parse byte array to be sure as I
proposed above).
Tuesday, February 14, 2012
Catastrophic Failure, MSDASQL, Linked Servers and MySQL
When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".
This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).
Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.
I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.
I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine
I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.
Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||
Hello Cybertoast:
Can you resolve this problem ?
I have the same error.
Thanks a lot
Daniel Hefez
Catastrophic Failure, MSDASQL, Linked Servers and MySQL
When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".
This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).
Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.
I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.
I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine
I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.
Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||
Hello Cybertoast:
Can you resolve this problem ?
I have the same error.
Thanks a lot
Daniel Hefez
Subscribe to:
Posts (Atom)