Automating report execution - saving to separate files

I'm trying to do something which I hope can be accomplished relatively simply.

I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later.

I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files.

I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML.

Thanks in advance,

H

I actually figured out an answer to my own question which I'll post here in case others need to do the same thing. If anyone else has an alternative suggestion please feel free to let me know.

There's a VBA function called "URLDownloadToFile". If I use the reporting services URL access and specify type XLS or PDF, I can pass this as the URL and save the file to a specific folder. Kind of nice actually - and very simple.

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi

Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:

Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

Automating db Backups

Is there a way to automate (schedule) backups of the databases in SQL Express? Similar to Maintenance Plans in SQL2000....Hi

Mark McFarlane,

If you come accross anything reagrding automating a schedule backup,Pls let me know,
Have been on the look out for this some time now.
Will do likewise.
email : papali4@.hotmail.com
Tnx

|||

hi,

not directly as SQL Server Agent is not provided, but you can workarond that using the OS provided native scheduler (AT or SCHTASKS)..

you can write down a cmd file like

<backup.cmd>

REM scheduled backup

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup executions started at - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'backup database [db_name]'; PRINT '';" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"BACKUP DATABASE [db_name] TO DISK = N'D:\BackupFolder\db_name.Bak' WITH FORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10" >>d:\YourCheckFolder\ScheduledBCK.txt

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Backup terminated at - ' + CONVERT(varchar, GETDATE());" >>d:\YourCheckFolder\ScheduledBCK.txt
</backup.cmd>

then you can schedule it as desired... the script will backup the db as required and will output the result of the task to a text file, d:\YourCheckFolder\ScheduledBCK.txt, you can later review to verify the performed operation...

in my own scenarios, I do add another "features".. I wrote a CLR assmbly exporting a stored procedure to "mimic" database mail feature (not present in SQLExpress) so that the "d:\YourCheckFolder\ScheduledBCK.txt" will be automatically sent to a defined list of recipients (sysadmins, dba or myself as well).. the component (amDBObj) is free and can be downloaded from http://www.asql.biz/en/Download2005.aspx .. feedback is apprecieted

the resulting script can be modified adding the

<add this>

REM adding SMTP mail to sysadmins, dba, etc of the backup operation result..

SqlCmd-E -S(Local) -Q"SET NOCOUNT ON; SELECT 'Mailing backup result of - ' + CONVERT(varchar, GETDATE());" >d:\YourCheckFolder\ScheduledBCK-mailing.txt

SqlCmd -E -S(Local) -Q"SET NOCOUNT ON; DECLARE @.ret int;EXEC @.ret = [db_hosting_the_CLR_assembly].[dbo].[amSMTPmail] @.Server = N'your_mail_server', @.Sender = N'the_SQLsender@.sender.com', @.AddressesTO = N'me@.me.com', @.AddressesCC = N'further_recipients@.domain.com', @.AddressesCCN = NULL, @.AttachFiles = N'd:\YourCheckFolder\ScheduledBCK.txt', @.Subject = N'Backup performed', @.MessageBody = N'Backup performed'; SELECT @.ret AS [Execution result];" >>d:\YourCheckFolder\ScheduledBCK-mailing.txt

</add this>

or whatever required change to the original cmd file...

regards

|||

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs. Also, what about SSIS in express edition. Do have to install them seperately. Thanks

|||

UMAR DAR wrote:

hi Andrea Montanari ,

That was very informative. Is there any other way to run and schedule jobs.

hy, perhaps you can have a look at a great artice (and tool) by Jasper Smith, SQL Server MVP, at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29, based on WinNT native scheduler as well... but all these are not SQL Server jobs, as SQLExpress does not provide SQL Server Agent...

UMAR DAR wrote:

Also, what about SSIS in express edition. Do have to install them seperately. Thanks

SSIS are not available as well, in SQLEpress edition.. http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

|||

I have a similar solution posted here that's not incredibly elegant but it works just fine. It goes through and backs up all databases in a given instance and there is an included batch file to schedule it. The video deployment instructions can be seen here:

http://www.jumpstarttv.com/Media.aspx?vid=30

or written instructions here:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

-- Brian

Auto numbers

How can I make my form to create an autonumber to my table when I insert a new record. Is there any field in the table similar to autonumber field in MSACCESS. Please help!You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks I am going to try it.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks you really help me. Now I find another problem. When I am in my main page I got links for administrator, clients, ect... this links are for entering data, delete and update. If I am in the clients area for example, if I view a record information and then go to anoter record the page does not displays, I got to go to my home page and then go to the page that I was looking and it displays the information. Hope you can help me whis this one.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||well, you have to tell us how you wrote the code to your application,
else we will be firing blank gueses to your question.

Although I do that often :)

What programing language are u using? ASP vbscript? and how do you query your database ? via ADO ?

Things like this will help us help u.

Auto numbering field similar to

I've been using MS Access 2000 for a while and have recently switched to MS
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com

Auto key generation

Hi,

Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)

make the column an identity

CREATE TABLE test(id INT IDENTITY, SomeOtherVAlue varchar(50))

INSERT test VALUES( 'A' )
INSERT test VALUES( 'B' )
INSERT test VALUES( 'C' )
INSERT test VALUES( 'D' )
INSERT test VALUES( 'E' )
INSERT test VALUES( 'F' )
GO

SELECT * FROM Test

id SomeOtherVAlue
-- --
1 A
2 B
3 C
4 D
5 E
6 F

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i have to got the answer

Use Identity Specification property of column