Help needed for SSRS 2005(Regarding html code)

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in this.
We are using an ASP.net application in which user fills a form. Some
fields contain text fields and we are entering all the fields in our
database through application.
But when we are entering values from application then our database
contains some html code in some fields with data.
Now I want to show those fields in my report, Values coming from
database in my report but they are in html format. So it is not
readable in report.
Now you people tell me how to solve this issue in my reports. I want
that data in my report as user entered in the application form.
Any help will be appreciated.
Regards
DineshDinesh,
The only thing I can think of is writing a function which strips out the
HTML tags and then call that function for each field in your report. You
could make this function a Report Code Block or even better, put it in an
assembly referenced by the Report.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1176467678.190725.177080@.y80g2000hsf.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005, and I am facing a problem in this.
> We are using an ASP.net application in which user fills a form. Some
> fields contain text fields and we are entering all the fields in our
> database through application.
> But when we are entering values from application then our database
> contains some html code in some fields with data.
> Now I want to show those fields in my report, Values coming from
> database in my report but they are in html format. So it is not
> readable in report.
> Now you people tell me how to solve this issue in my reports. I want
> that data in my report as user entered in the application form.
> Any help will be appreciated.
> Regards
> Dinesh
>

Help Needed For reporting service error(An internal error occurred on the report server)

Hi experts
I am working on sql server reporting services 2005. And now a days i
am facing a problem.
Some times when i want to run a report the at that time i am geting
the following error msg
"An internal error occurred on the report server. See the error log
for more details."
This eroor not every time come when i run the reports and if i agian
pres the view report button then in 2nd or 3rd attemt
same report run and shows the correct data.
But it is not good to see any error msg during running the report. I
have checked the log file but there no eoor msg is
listed.
So if any one have any idea abt this how to solve this problem Please
help me.
Becuase now a days it is comming more freqently.
Any help will be gratefull.
Regards
DineshIt sounds like you have the PerfCounters issue on an AMD Processor server.
If you check the SRS error logs in C:\Program Files\Microsoft SQL
Server\MSSQL.#\Reporting Services\LogFiles you will probably find an
"ASSERT" message in there.
Edit your boot.ini file and add the following switch to your boot.ini and
see if that fixes the issue /usepmtimer
More information regarding this can be found here:
http://support.microsoft.com/kb/895980/en-us
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Dinesh <dinesht15@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Help Needed For reporting service error(An internal error
occurred on the report server)
> Date: Thu, 04 Oct 2007 02:23:20 -0700
> Hi experts
> I am working on sql server reporting services 2005. And now a days i
> am facing a problem.
> Some times when i want to run a report the at that time i am geting
> the following error msg
> "An internal error occurred on the report server. See the error log
> for more details."
> This eroor not every time come when i run the reports and if i agian
> pres the view report button then in 2nd or 3rd attemt
> same report run and shows the correct data.
> But it is not good to see any error msg during running the report. I
> have checked the log file but there no eoor msg is
> listed.
> So if any one have any idea abt this how to solve this problem Please
> help me.
> Becuase now a days it is comming more freqently.
> Any help will be gratefull.
> Regards
> Dinesh
>

Help needed for datediff function for SQL query

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.

I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."

select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Any help will be appriciated.
Regards
DineshWithout having your table structure and sample data, it is a bit difficult
to troubleshoot, but a few notes that can help you:

- Based on the style 103 that you use for the CONVERT function, seems you
are converting date stored as string in format "dd/mm/yyyy" to a datetime
type. The error that you get indicates that the date string cannot be
converted, because the day, month, or year portion is out of the allowed
range. You can easily simulate the error if you run something like this:
SELECT CONVERT(datetime, '23/13/2007', 103). The month cannot be 13 so it
fails with the error you get. You can use the LEFT, RIGHT and SUBSTRING
functions to extract the day, month, and year portion of both columns
(Placement_Date and Discharge_Date) and check for invalid values, then clean
your data. For year the down side value is 1753 (that is the lower limit for
datetime data type).

- It is always best to keep date values in columns of datetime data type.
That way you do not have to worry about the format and can benefit of using
all datetime functions with no need to convert.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then

This does not really make sense. A datetime value cannot be the
empty string. By the default the empty string will convert to the
datetime value 1900-01-01 00:00:00.000, but I don't think that is
what you want.

Assuming a reasonably designed database, the test would be

WHEN Discharge_Date IS NULL THEN

Then again, since you seem to store dates in character values, this
may not be a reasonably designed database. :-)

Anyway, the problem appears to be that you have junk in your character
columns. As Plamen said, you should use the datetime data type to store
your dates instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dinesh (dinesht15@.gmail.com) writes:

Quote:

Originally Posted by

I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
>
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
>
select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?

Oh, by the way, this SELECT should give you the rows with bad dates:

SET DATEFORMAT DMY
go
SELECT Discharge_Date, Placement_Date
FROM Placement_Details
WHERE isdate(Discharge_Date) = 0 OR
isdate(Placement_Date) = 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

HELP ME TO OPTIMIZE QUERY

Hello friends,

I'm facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like "'26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @.Units variable. But it takes too much time and I don't get output. Table is having around5 Million records.

Query:

SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM tblUnit INNER JOIN

tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID

WHERE tblUnitHistory.UnitHistoryDate

IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ') GROUP BY tblUnitHistory.UnitID)

AND tblUnit.UnitID in (' + @.Units + ')

ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID int Primary Key

UnitID int

Location varchar(200)

Latitude decimal 9

Longitude decimal 9

Speed decimal5

BatteryVoltage decimal5

ReasonCode int

DistanceFromLastLocation decimal9

UnitHistoryDate datetime

Indexes:

1. Clustered Index on ColumnUnitID

2. Non-clustered Index on ColumnUnitHistoryDate

3. Non-clustered Index on ColumnUnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,

Sandeep

try to limit processed records by:

'SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM (SELECT * FROM tblUnit

where

AND tblUnit.UnitID in (' + @.Units + '))tblUnit

INNER JOIN

(SELECT tblUnitHistory.UnitID , MAX(UnitHistoryDate) UnitHistoryDate FROM (SELECT * FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ')) tblUnitHistory GROUP BY tblUnitHistory.UnitID) tblUnitHistory

ON tblUnit.UnitID = tblUnitHistory.UnitID

ORDER BY tblUnit.UnitID'

I hope that it will work, The Idea is to limit number of records used in join to minimum.

help me Please!

Hi There!!

I have a code that runs from a different machine and a different site and i am facing a problem that i can not log in to the database.I installed SQL Server on my machine and I created a user "fadila" and SQL Server Authentication and the password is "fadil1977" and the database is "otters" and it is installed as tables and stored procedures but there is not data on these tables.

I used the method provided in the code so, i only change the "connStr" in one place rather than in 20 places and my code is as below. Can you please Help me to connect to the database. I Really..Really appreciate it if you help me to solve it as it causeing me a big head-ache and still get the error message "SQL Server does not exist or access denied" .. please help!!!!

 Friend Shared ReadOnly Property connStr() As String
 Get
 Return String.Format( _
 "Data Source={0};Initial Catalog=Otters;User ID=fadila;Password=fadil1977", _
 DatabaseMachine)
 End Get
 End Property

your help is appreciated!!!!!!Sure you can connect to the sql server?

Possible reasons:

* Firewall in between.
* Stupid admin. One who did not read that syou need a special service pack (SP3?) to run on 2003, and Winwodows Server 2003 actively blocks SQL Server ports unless this sp is installewd (at least).
* Bad setup for the network, not allowing name resolution.
* Bad database machine name?

Do you homework - there is nothing we can do here to help you, simply because we have no clue what the error is, simply because you do not provide enough info.|||Hi Thona!!

Thank you very much for your help and answering my question. You just said there is no enough information provided, can you please tell me what information is needed so i can post up for you. I just want to make one point, I used before in the previous project the dataset and connect to the database without any problem so, is that what you told me, it does not apply to the dataset and SqlAdaptor as it is a different way toconnect so, i will not have this problem.

I will speak to the IT admin about the you told me and get back to you so, please keep looking to this thread until you solve it for me or you get fed up :)

Thanks again

HELP me in this datediff() function....

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

You have to try this: datediff(Minute,'23:00:00','01:00:00')|||

suigion:

Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.

My code :

datediff(Minute,'01:00:00','23:00:00')

The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)...

Can anybody help ?

Thanks in advance...

Try the link below for how to get correct hours from the SQL Server DateDiff function. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html

Help me getting started

I am new to ssrs,

Iam facing the following problem with reportviewer parameter

1) I have a dataset added to the project under app_code which has the following querry select * from emp

2) In the rdlc i have two columns empname,deptid,i have added a parameter called deptid with type as int

3) I have a webpage with reportviewer i have mapped the reportviewer to the rdlc file and datasource

4)on click of a button i want to display emp details from a particular deptid alone , so i added these lines on click of the button

ReportViewer1.ShowReportBody =true;

ReportParameter rpt =newReportParameter("deptid", Text1.Text);

this.ReportViewer1.LocalReport.SetParameters(newReportParameter[] { rpt });

But stil it is showing all the emp details

HI,dareprasanna:

After you setting the filter parameter to the ReportView, you should make it refresh to see the result.

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Even after setting , ReportViewer1.LocalReport.Refresh();

I get all the details instead of getting those employee who are from the corresponding department,

Should i write a seperate method to fetch the employees of the corresponding dept or the reportviewer will automatically fetch the corresponding employees,

If i have not made my question clear in the previous question i will make myself clear now,

1) I added a dataset with the querry select empname,deptid from emp

2) In the rdlc i have added two details column (empname and deptid)
I have added one parameter by right clicking out of rdlc and added a parameter called 'deptid'

3) In the aspx page i have added reportviewer Choosed the rdlc i have created just above this point
I have not done any changes with ObjectDatasource1 which is created on associating reportviewer with rdlc

4) I have added the following piece of code in page load

ReportViewer1.ShowReportBody = true;
ReportParameter rpt = new ReportParameter("deptid", "39");
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rpt });
ReportViewer1.LocalReport.Refresh();

5) Still i get all the employees instead of getting employee from deptid = 39

|||

HI,dareprasanna

Please double check your sql statement and

I'd rather do it in that way:

SELECT LastName, FirstName, EmailAddress, Phone, ContactID
FROM Person.Contact
WHERE (LastName LIKE @.LastName ) AND (Phone LIKE @.Area )

aspx code:

ReportViewer1.ShowReportBody = true;
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;

strLastName = txtLastName.Text.ToString().Trim();
strArea = txtArea.Text.ToString().Trim();

ReportParameter[] param = new ReportParameter[2];
param[0] = new ReportParameter("LastName", strLastName + "%", false);
param[1] = new ReportParameter("Area", strArea + "%", false);
ReportViewer1.LocalReport.SetParameters(param);

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Thank you very much for your kind help,

This discussion has definetly given me some idea about ssrs and report viewer control,

Again Thanks a lot

Regards

prasanna

|||

You are welcome, prasanna.

It's my pleasure!