Automatically Generating PDFs With Reports Requiring Parameters

Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql

Automatically generating PDF with ReportViewer

Hi, I've been using the Report viewer control for a bit, and was wondering how I can automatically run the report as a PDF. I can run it and prompt the user to open or save the PDF, but I'm not sure how to auto generate it. This is the code which prompts the user:

ProtectedSub Page_SaveStateComplete(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.SaveStateComplete

Dim pdfContentAsByte() =Me.ReportViewer1.LocalReport.Render("pdf",Nothing,Nothing,Nothing,Nothing,Nothing,Nothing)

Me.Response.Clear()

Me.Response.ContentType ="application/pdf"

Dim modCodeAsString = Request.QueryString("modCode")

Me.Response.AddHeader("Content-disposition","attachment; filename=ModuleCode-" & modCode &".pdf")

Me.Response.BinaryWrite(pdfContent)

Me.Response.End()

EndSub

Has anybody done this?

Thanks

Hi,

Do you want to save the PDF file which generates by the reporting service automatically, right?

Based on my understanding, I think when you have generate the byte[] content for your PDF file, what you should do is to use FileStream class and FileInfo class to create the corresponding file, and write the content into files, save it onto your hard disk.

If you want the method be called automatically, you can invoke the method when page loads.
For more reference on FileStream and FileInfo, see:

http://msdn2.microsoft.com/en-us/library/system.io.fileinfo.aspx
http://msdn2.microsoft.com/en-us/library/system.io.filestream.aspx

Thanks.

|||

Thanks for that, I'll have a look. Also, when I ask the user to save as .xls or .pdf for example, how can I specify that the file will be saved on the D drive, rather than the C drive as the C drive is full?

Thanks

|||

Hi,

When you are creating and storing the file, you can specify the actual disk drive where to store the file. Also, you can import the "System.Runtime.InteropServices" namespace to check the free disk volume, so that you can determine which disk drive to store the file.

Thanks.

Automate "Week Ending" Date

How do i automate, by stored procedure, generating the "week ending" date using a field data.

------------------

CREATE TABLE [dbo].[t_Work_Hours] (
[WorkHoursID_PK] [int] IDENTITY (1, 1) NOT NULL ,
[PeopleID_FK] [int] NOT NULL ,
[JobID_FK] [int] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Title] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekEnding] [datetime] NULL ,
CONSTRAINT [PK_t_Work_Hours] PRIMARY KEY CLUSTERED
(
[WorkHoursID_PK]
) ON [PRIMARY] ,
CONSTRAINT [FK_t_Work_Hours_t_Work_People] FOREIGN KEY
(
[PeopleID_FK]
) REFERENCES [dbo].[t_Work_People] (
[PeopleID_PK]
)
) ON [PRIMARY]
GO
-----------------

What i have tried has'nt even been close to a solution.
UPDATE dbo.t_Work_Hours
SET WeekEnding = DATETIME ( ? , StartTime) . I have no idea where to go!

--------------------
WorkHoursID_PK, PeopleID_FK, JobID_FK, StartTime, EndTime, Title, WeekEnding
7, 40, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, J, (NULL)
8, 43, 3, 10/31/2005 6:00:00 AM, 10/31/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
9, 43, 3, 11/1/2005 6:00:00 AM, 11/1/2005 4:30:00 PM, F, 11/6/2005 11:59:00 PM
------------------If you insist on performing an UPDATE on the field, then some variation of the statement below should suffice:


...SET WeekEnding =dateadd(day, 8-datepart(dw, StartTime), StartTime)...


I would make WeekEnd a computed column like this:

alter table add Weekend as dateadd(day, 8-datepart(dw, StartTime), StartTime)|||Should be in here somewhere

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx|||thanks a bunch

Autogenerate Primary Key

Hi all
How do you autogenerate your own primary key in SQL.
Instead of SQL generating an IDENTIY number which would be 1, 2 ,3..etc
I was wanting to give it my own sequence of numbers, how exactly do I do that can anyone help??why? what do these sequence of numbers look like? do they have special meanings?|||Humour me for a second - why?|||why? what do these sequence of numbers look like? do they have special meanings?Humour him too ^^^^^^^ :)|||The number would look like this 07-0000

The first two digits are the year, the others are in numerical secquence.. like 07-0001, 07-0002, 07-0003 (JP IR#)and so on, they normally log this in a journal but now they want a database to log this in where the database would generate these numbers and they have to do is put in the type, the amount and whether its a hold or not. its for our surveillance department and they have IR numbers that help in their reports|||smart numbers are stupid for many many reasons. I would just store those 2 parts in seperate fields and bring them together in the UI so they can see the number they want.|||Sorry I didnt realize they would be stupid, I just thought I would ask. Doesnt hurt to ask. I wasnt sure how to create something like|||Heh. The problem with semi intelligent (and also dumb) bespoke keys is when it comes to serialisation. This is not much of an issue if you add one row at a tme and do not have high concurrency but if either of these are not true then performance really suffers. Do you need to reset the count each year? SQL Server does not support this internally. I think other RDBMSs (like MySQL) do but that is not much help :)

Auto Generating Phantom drives

I'm running SQL on a clustering configuration. I recently started noticing
these phantom drives appearing under my computer. They are all relatively
close in size and it appears to only create one phantom drive each Sunday. I
currently have the SQL Server backing up it's files to the real Local drive
and from there it goes to tape. I do not only back up on Sunday I backup the
same way each day. The phantom drives do not show up under Disk Manager only
under My Computer.
Anyone every experience these phantom drives appearing.
Peter
Disks or drives? They are different.
Disks could be from your SAN solution, managing snapshot copies and other
head-in components.
Drives are one of two possible scenarios:
Have you been presenting replacement LUNs to your cluster? If so, you could
be seeing remnants of the old definitions. You might want to rescan the
disks and then reboot the servers to remove the old ones.
The more likely scenario is the use of the OTM (Open Transaction Manager) in
conjunction with the Veritas NetBackup or BackupExec. OTM can create
temporary shares that look like drives that it uses for snapshot copies
while doing backup acceleration.
They go away once the tape archive has completed successfully. If they
remain, then you probably had a backup failure and Veritas failed to remove
the share.
Sincerely,
Anthony Thomas

"Peter" <peter@.discussions.microsoft.com> wrote in message
news:373FDF72-009C-44A6-AF0D-E09B4D0710BE@.microsoft.com...
> I'm running SQL on a clustering configuration. I recently started noticing
> these phantom drives appearing under my computer. They are all relatively
> close in size and it appears to only create one phantom drive each Sunday.
I
> currently have the SQL Server backing up it's files to the real Local
drive
> and from there it goes to tape. I do not only back up on Sunday I backup
the
> same way each day. The phantom drives do not show up under Disk Manager
only
> under My Computer.
> Anyone every experience these phantom drives appearing.
> --
> Peter

Auto generating numeric column

Hi There,

I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.

AutoRow
1
2
3
4 --- reset
1
2
3
4 ---reset

Any help would be great,

many thanks

Stuart

An indentity field is going to require unique values, so reseting it isnt going to work, your going to throw an SQL Exception.

Are you trying to accomplish this just on the SQL server, or can you do it as part of your application?

|||Unfortuanatley it needs to be done on the server|||

Check this sample to see whether it helps:

DROP TABLE tbl_testIden
go

CREATE TABLE tbl_testIden (id int identity(1,1), name sysname)
GO

CREATE TRIGGER trg_tsetIden ON tbl_testIden FOR INSERT,UPDATE
AS
IF (IDENT_CURRENT('tbl_testIden')>=4)
DBCC CHECKIDENT('tbl_testIden',RESEED,0)
GO

DECLARE @.i INT
SET @.i=1
WHILE (@.i<100)
BEGIN
INSERT INTO tbl_testIden(name) SELECT 'Person#'+CONVERT(VARCHAR(8),@.i)
SET @.i=@.i+1
END
go
SELECT * FROM tbl_testIden

|||

Hi There,

I managed to get it working using the Row_Number function. It has some very useul derivetives also.

Row_Number simply creates and incremental list which is reset when thepartiation by value changes.

ROW_NUMBER()OVER(PARTITIONBY ReqPointIdORDERBY ReqPointIdDESC)AS RowGroup

I'll look at your approach as it alows me to specify and upper limit.

Many thanks for your time

Regards

Stuart

|||

You are looking for a combination of ROW_NUMBER and %, but without more of the query, I can't help you. Generically...

SELECT (RowGroup % 4)+1 As AutoGen, more columns here

FROM (

SELECT ROW_NUMBER() OVER (something) AS RowGroup, more columns here

) t1

You may even be able to combine the two, but I'm not familiar enough with ROW_NUMBER to say that would work, but if it does... Then...

SELECT (ROW_NUMBER() OVER (something) % 4)+1 AS RowGroup, more columns here

FROM somewhere

should work as well. I think most people (myself included) were confused because you said you wanted an autogenerated column. That tends to lead people to believe you are speaking of a table column, not a field in a resultset.

Auto generating an email

Hi All,

I need to opening up a browser page everyday at 6am. (What I am trying to do is to send an email with some formatted output everyday at 6am)

Is there anyway to achive this via SQL 05 Express?

Thanks,

Mal.

Hi,

why would you need to open a webpage every day? SQL Server's capable of sending emails itself. Another option is to create a console application that can send the mails and have it scheduled with windows task schedular to run at 6am every day.

http://support.microsoft.com/kb/312839/en-us

Grz, Kris.

|||

Hi,

The reason I'd like to open up a web page is because, the template of the data is already there. With little bit of improvisation I would get it to be sending an email.

Actually, I did know (didn't know the code) it is possible to send emails from SQL 2K but not 05 Express.

My intension now is to open up the web page with a process and later kill the process. (with the help of schedular). It is not the best methods around as far as I can see, but hopefully I would be able to implement that quickly.

Thanks for the tip and the link.

Mal.

|||

Hi,

suhash:

My intension now is to open up the web page with a process and later kill the process. (with the help of schedular). It is not the best methods around as far as I can see, but hopefully I would be able to implement that quickly.

create a console application and use theHttpServerUtility.Execute method to execute the webform and capture the rendered html. No need to open a browser etc.

Grz, Kris.

|||

Oh, thanks a lot.

I was thinking to open up a page...

Problem solves thanks to you again,

Cheers.