Automatic Email

Dear Freind,
Its all boutt Dts.I have already created a DTSpackage and Activex script in VBscript to retrieve some particular names ...and i want to send those names through E mail.But the email should be automatic(using sql Sheduler ).Real Problem is...How can i write the code to access the DTS object from ASP.Net with VB? and How can shedule...please help me...i am hopefully waiting..........thanks in advanceIf you just want to send the mails queried from a table you can use the regular mail sending procedures from SQL Server like xp_sendmail (depends on your SQL Server version which to use)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Moving to the "SQL Server Integration Services" forum.

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert LieHi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D_UMP.BAK
Script to Backup all databases
--
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no_rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNAM
E+'_'+@.DBNAME+'_'+ltr_im(rtrim(conve
rt
(char,getdate(),105)))+'Dump.b_ak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
--
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:

> Hi,
> Compile the below stored procedure in Master database and define the folde
r
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D_UMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','no_rthwind'
)
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNA
ME+'_'+@.DBNAME+'_'+ltr_im(rtrim(c
onvert
> (char,getdate(),105)))+'Dump.b_ak'
> BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
> @.DBNAME, NOSKIP , STATS = 10, NOFORMAT
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> END
> CLOSE BACKUP_CUR
> DEALLOCATE BACKUP_CUR
> end
>
> How to schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
>

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert Lie
Hi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
Script to Backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','noXrthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DBN AME+'_'+ltrXim(rtrim(convert
(char,getdate(),105)))+'Dump.bXak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie
|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:

> Hi,
> Compile the below stored procedure in Master database and define the folder
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_DXUMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','noXrthwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@.XSERVERNAME+'_'+@.DB NAME+'_'+ltrXim(rtrim(convert
> (char,getdate(),105)))+'Dump.bXak'
> BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
> @.DBNAME, NOSKIP , STATS = 10, NOFORMAT
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> END
> CLOSE BACKUP_CUR
> DEALLOCATE BACKUP_CUR
> end
>
> How to schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
>
>

Automate backup method

Dear All,
I plan my backup strategy during the week as follow:
Mon | Differential database backup
Tue | Differential database backup
Wed | Differential database backup
Thu | Differential database backup
Fri | Differential database backup
Sat | Differential database backup
Sun | Full database backup
All days Transaction log backups four times.
I want the backup files will follow the format below:
dbname_backuptype_YYYYMMDDHHMM.bak
Does anyone knows how to automate this task?
Thanks
Robert LieHi,
Compile the below stored procedure in Master database and define the folder
in which backup needs to be taken. schedule this procedure using SQL
Agent -- Jobs. This procedure will backup all the databases with a unique
name place it in the folder your are passing.
Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D­UMP.BAK
Script to Backup all databases
--
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no­rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@.­SERVERNAME+'_'+@.DBNAME+'_'+ltr­im(rtrim(convert
(char,getdate(),105)))+'Dump.b­ak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =@.DBNAME, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and scdule the job to be executed based on
requirement
Note:
You could change the script to do a differential or log backup.
--
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> I plan my backup strategy during the week as follow:
> Mon | Differential database backup
> Tue | Differential database backup
> Wed | Differential database backup
> Thu | Differential database backup
> Fri | Differential database backup
> Sat | Differential database backup
> Sun | Full database backup
> All days Transaction log backups four times.
> I want the backup files will follow the format below:
> dbname_backuptype_YYYYMMDDHHMM.bak
>
> Does anyone knows how to automate this task?
> Thanks
> Robert Lie|||Hi there, search for RE: Backup schedule (Differential backups) in this
group.
Andrew J. Kelly was kind to provide the solution.
"Hari Prasad" wrote:
> Hi,
> Compile the below stored procedure in Master database and define the folder
> in which backup needs to be taken. schedule this procedure using SQL
> Agent -- Jobs. This procedure will backup all the databases with a unique
> name place it in the folder your are passing.
>
> Unique name will be: SERVERNAME_DBNAME_DD_MM_YYYY_D­UMP.BAK
>
> Script to Backup all databases
> --
> CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
> AS
> begin
> DECLARE @.NAME VARCHAR(100),
> @.DBNAME VARCHAR(100)
> DECLARE BACKUP_CUR CURSOR FOR
> SELECT name FROM sysdatabases where name not
> in('model','pubs','tempdb','no­rthwind')
> OPEN BACKUP_CUR
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> SELECT
> @.NAME=ltrim(rtrim(@.folder))+@.@.­SERVERNAME+'_'+@.DBNAME+'_'+ltr­im(rtrim(convert
> (char,getdate(),105)))+'Dump.b­ak'
> BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME => @.DBNAME, NOSKIP , STATS = 10, NOFORMAT
> FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
> END
> CLOSE BACKUP_CUR
> DEALLOCATE BACKUP_CUR
> end
>
> How to schedule
> --
> Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
> create new job.
> Give a name to the Job and in Job step menthon this procedure with
> foldername as parameter and scdule the job to be executed based on
> requirement
> Note:
> You could change the script to do a differential or log backup.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:%230FZVEJeFHA.3032@.TK2MSFTNGP10.phx.gbl...
> > Dear All,
> >
> > I plan my backup strategy during the week as follow:
> >
> > Mon | Differential database backup
> > Tue | Differential database backup
> > Wed | Differential database backup
> > Thu | Differential database backup
> > Fri | Differential database backup
> > Sat | Differential database backup
> > Sun | Full database backup
> > All days Transaction log backups four times.
> >
> > I want the backup files will follow the format below:
> > dbname_backuptype_YYYYMMDDHHMM.bak
> >
> >
> > Does anyone knows how to automate this task?
> >
> > Thanks
> >
> > Robert Lie
>
>

autocontained selects

Dear gurus,
select 'insert into cargafrecuencias([id],tipo,horas) values(' +
convert(char(2),[id]) + ',
''' + rtrim(ltrim(tipo)) + ''',
' + convert(char(5), horas) + ')'
from cargafrecuencias
That query returns fine INSERTS and ready for be executed:
insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
168 )
insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
720 )
insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
24 )
insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
el fichero', 8640 )
It's easy and useful without a doubt. But can you imagine do the same with a
table with 80 columns?
I would like do something like that (it would be gorgeous):
select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
from table
On the other hand is very annonying.
Does anyone have any ideas or thoughts?
Thanks in advance and regards,CREATE PROCEDURE SPInserttest
(
@.Test varchar(50)
)
AS
BEGIN
PRINT 'Do something with the test varchar(50)'
Select 'Jens'
UNION
SELECT 'BOB'
END
CREATE TABLE #Tabname
(
Names varchar(50)
)
INSERT INTo #Tabname
EXEC('SPInserttest ''Test''')
Select * from #Tabname
DROP Procedure SPInserttest
DROP Table #Tabname
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Try this one.
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
> Dear gurus,
> select 'insert into cargafrecuencias([id],tipo,horas) values(' +
> convert(char(2),[id]) + ',
> ''' + rtrim(ltrim(tipo)) + ''',
> ' + convert(char(5), horas) + ')'
> from cargafrecuencias
>
> That query returns fine INSERTS and ready for be executed:
> insert into cargafrecuencias([id],tipo,horas) values(1 , 'Semanal',
> 168 )
> insert into cargafrecuencias([id],tipo,horas) values(2 , 'Mensual',
> 720 )
> insert into cargafrecuencias([id],tipo,horas) values(3 , 'Diaria',
> 24 )
> insert into cargafrecuencias([id],tipo,horas) values(4 , 'Cuando
> llegue
> el fichero', 8640 )
>
> It's easy and useful without a doubt. But can you imagine do the same with
> a
> table with 80 columns?
> I would like do something like that (it would be gorgeous):
> select 'insert into table(f1,f2,f3... fn) values (sp_help table)'
> from table
> On the other hand is very annonying.
> Does anyone have any ideas or thoughts?
> Thanks in advance and regards,
>|||Great, thanks a lot, it works very well
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>|||XXXXing life. That doesn't works with my table, it is too large in terms of
columns.
"Roji. P. Thomas" wrote:

> Try this one.
> http://vyaskn.tripod.com/code/generate_inserts.txt
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:1BD06558-758F-490C-B228-7090641DA685@.microsoft.com...
>
>

Autocomplete combobox

Dear all,

I have a parametrized report which displays the available values for the parameter in a combobox (dropdownlist). I need to make that combobox autocomplete what the user types.

Is there any way for that?!

Thank you.Here is some code in c#. You will need to use a custom application to display the report & parameters.

|||There is no way out of the box to do this. You would have to write your own code to accomplish this. You could use the .Net 2.0 ReportViewer control to display the report and then handle the parameters yourself.

Hope that helps.
-Daniel

Autocomplete combobox

Dear all,

I have a parametrized report which displays the available values for the parameter in a combobox (dropdownlist). I need to make that combobox autocomplete what the user types.

Is there any way for that?!

Thank you.Here is some code in c#. You will need to use a custom application to display the report & parameters.|||There is no way out of the box to do this. You would have to write your own code to accomplish this. You could use the .Net 2.0 ReportViewer control to display the report and then handle the parameters yourself.

Hope that helps.
-Daniel