Help with SQL backup verification, unable to verify date appended

Hi,
I need help with my SQL script for appending the current date to my backup
and then being ale to verify the daily changing DB backup name. Here is my
script, i realize that the convert part is probably part of teh problem but
are there any commands to take it place in order for the verify to happen.
please help it is sort of urgent. Thanks - Nik
USE [master]
/* points to database that will be used */
GO
DBCC SHRINKDATABASE(N'master' )
/* This line shrinks database and log file*/
GO
USE [master]
/* points to database that will be used */
GO
DBCC CHECKDB(N'master', NOINDEX)
/* This line does integrity check of database*/
GO
declare @.f sysname
set @.f=N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* This line backups named database to location specified*/
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'master' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'master' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GO
Hi,
I did not test your script but I can give you some comments here:
1) The part to append the current date works fine
2) Store the filename in a variable instead of runing getdate() twice as the
date could be different the first and the second time (like just after
midnight)
3) Why do you need to verify data from msdb?
4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
Hope this helps,
Ben Nevarez
"lca1630" wrote:

> Hi,
> I need help with my SQL script for appending the current date to my backup
> and then being ale to verify the daily changing DB backup name. Here is my
> script, i realize that the convert part is probably part of teh problem but
> are there any commands to take it place in order for the verify to happen.
> please help it is sort of urgent. Thanks - Nik
> USE [master]
> /* points to database that will be used */
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
> GO
> USE [master]
> /* points to database that will be used */
> GO
> DBCC CHECKDB(N'master', NOINDEX)
> /* This line does integrity check of database*/
> GO
> declare @.f sysname
> set @.f=N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME =
> N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> /* This line backups named database to location specified*/
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'master' )
> if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> for database ''master'' not found.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getda te(),112)+N'.bak'
> WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
>
> GO
|||Thanks for looking at this Ben. I am using SQL Express therefore the need to
come up with a workaround maintanence plan etc. The verify portion is
basically me manually setting up the backup and then having the SQL
management studio express script it for me. This is what it comes up with.
The variable part I am not sure of what to set that too, if you have any
suggestions please let me know. Thanks - Nik
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi,
> I did not test your script but I can give you some comments here:
> 1) The part to append the current date works fine
> 2) Store the filename in a variable instead of runing getdate() twice as the
> date could be different the first and the second time (like just after
> midnight)
> 3) Why do you need to verify data from msdb?
> 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> Hope this helps,
> Ben Nevarez
>
> "lca1630" wrote:
|||What I mean is to use a variable for the date or the filename to make sure
that both BACKUP and RESTORE VERIFYONLY use the same file. For example when
you run the BACKUP getdate() may return 20080109 but by the time the job is
about to run RESTORE VERIFYONLY getdate may return 20080110 and will fail
because there is no such file. Something like
set @.filename = ... getdate(), 112 ...
...
backup database ... to disk = @.filename
...
restore verifyonly from disk = @.filename
Hope this helps,
Ben Nevarez
"lca1630" wrote:
[vbcol=seagreen]
> Thanks for looking at this Ben. I am using SQL Express therefore the need to
> come up with a workaround maintanence plan etc. The verify portion is
> basically me manually setting up the backup and then having the SQL
> management studio express script it for me. This is what it comes up with.
> The variable part I am not sure of what to set that too, if you have any
> suggestions please let me know. Thanks - Nik
> "Ben Nevarez" wrote:
|||"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:C6063C2C-D9F0-4082-BBDA-DE010216638E@.microsoft.com...
> Hi,
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
Another cmment: Don't do the above step.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help with SQL backup verification, unable to verify date appended

Hi,
I need help with my SQL script for appending the current date to my backup
and then being ale to verify the daily changing DB backup name. Here is my
script, i realize that the convert part is probably part of teh problem but
are there any commands to take it place in order for the verify to happen.
please help it is sort of urgent. Thanks - Nik
USE [master]
/* points to database that will be used */
GO
DBCC SHRINKDATABASE(N'master' )
/* This line shrinks database and log file*/
GO
USE [master]
/* points to database that will be used */
GO
DBCC CHECKDB(N'master', NOINDEX)
/* This line does integrity check of database*/
GO
declare @.f sysname
set @.f=N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* This line backups named database to location specified*/
GO
declare @.backupSetId as int
select @.backupSetId = position from msdb..backupset where
database_name=N'master' and backup_set_id=(select max(backup_set_id) from
msdb..backupset where database_name=N'master' )
if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\SQL
Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
GOHi,
I did not test your script but I can give you some comments here:
1) The part to append the current date works fine
2) Store the filename in a variable instead of runing getdate() twice as the
date could be different the first and the second time (like just after
midnight)
3) Why do you need to verify data from msdb?
4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
Hope this helps,
Ben Nevarez
"lca1630" wrote:
> Hi,
> I need help with my SQL script for appending the current date to my backup
> and then being ale to verify the daily changing DB backup name. Here is my
> script, i realize that the convert part is probably part of teh problem but
> are there any commands to take it place in order for the verify to happen.
> please help it is sort of urgent. Thanks - Nik
> USE [master]
> /* points to database that will be used */
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
> GO
> USE [master]
> /* points to database that will be used */
> GO
> DBCC CHECKDB(N'master', NOINDEX)
> /* This line does integrity check of database*/
> GO
> declare @.f sysname
> set @.f=N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> /* This line backups named database to location specified*/
> GO
> declare @.backupSetId as int
> select @.backupSetId = position from msdb..backupset where
> database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset where database_name=N'master' )
> if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> for database ''master'' not found.', 16, 1) end
> RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
>
> GO|||Thanks for looking at this Ben. I am using SQL Express therefore the need to
come up with a workaround maintanence plan etc. The verify portion is
basically me manually setting up the backup and then having the SQL
management studio express script it for me. This is what it comes up with.
The variable part I am not sure of what to set that too, if you have any
suggestions please let me know. Thanks - Nik
"Ben Nevarez" wrote:
> Hi,
> I did not test your script but I can give you some comments here:
> 1) The part to append the current date works fine
> 2) Store the filename in a variable instead of runing getdate() twice as the
> date could be different the first and the second time (like just after
> midnight)
> 3) Why do you need to verify data from msdb?
> 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> Hope this helps,
> Ben Nevarez
>
> "lca1630" wrote:
> > Hi,
> >
> > I need help with my SQL script for appending the current date to my backup
> > and then being ale to verify the daily changing DB backup name. Here is my
> > script, i realize that the convert part is probably part of teh problem but
> > are there any commands to take it place in order for the verify to happen.
> > please help it is sort of urgent. Thanks - Nik
> >
> > USE [master]
> > /* points to database that will be used */
> > GO
> > DBCC SHRINKDATABASE(N'master' )
> > /* This line shrinks database and log file*/
> > GO
> > USE [master]
> > /* points to database that will be used */
> > GO
> > DBCC CHECKDB(N'master', NOINDEX)
> > /* This line does integrity check of database*/
> > GO
> > declare @.f sysname
> > set @.f=N'C:\SQL
> > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => > N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> > /* This line backups named database to location specified*/
> > GO
> > declare @.backupSetId as int
> > select @.backupSetId = position from msdb..backupset where
> > database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset where database_name=N'master' )
> > if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> > for database ''master'' not found.', 16, 1) end
> > RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
> >
> >
> > GO|||What I mean is to use a variable for the date or the filename to make sure
that both BACKUP and RESTORE VERIFYONLY use the same file. For example when
you run the BACKUP getdate() may return 20080109 but by the time the job is
about to run RESTORE VERIFYONLY getdate may return 20080110 and will fail
because there is no such file. Something like
set @.filename = ... getdate(), 112 ...
...
backup database ... to disk = @.filename
...
restore verifyonly from disk = @.filename
Hope this helps,
Ben Nevarez
"lca1630" wrote:
> Thanks for looking at this Ben. I am using SQL Express therefore the need to
> come up with a workaround maintanence plan etc. The verify portion is
> basically me manually setting up the backup and then having the SQL
> management studio express script it for me. This is what it comes up with.
> The variable part I am not sure of what to set that too, if you have any
> suggestions please let me know. Thanks - Nik
> "Ben Nevarez" wrote:
> >
> > Hi,
> >
> > I did not test your script but I can give you some comments here:
> >
> > 1) The part to append the current date works fine
> > 2) Store the filename in a variable instead of runing getdate() twice as the
> > date could be different the first and the second time (like just after
> > midnight)
> > 3) Why do you need to verify data from msdb?
> > 4) If you are using SQL Server 2005 also consider BACKUP WITH CHECKSUM.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "lca1630" wrote:
> >
> > > Hi,
> > >
> > > I need help with my SQL script for appending the current date to my backup
> > > and then being ale to verify the daily changing DB backup name. Here is my
> > > script, i realize that the convert part is probably part of teh problem but
> > > are there any commands to take it place in order for the verify to happen.
> > > please help it is sort of urgent. Thanks - Nik
> > >
> > > USE [master]
> > > /* points to database that will be used */
> > > GO
> > > DBCC SHRINKDATABASE(N'master' )
> > > /* This line shrinks database and log file*/
> > > GO
> > > USE [master]
> > > /* points to database that will be used */
> > > GO
> > > DBCC CHECKDB(N'master', NOINDEX)
> > > /* This line does integrity check of database*/
> > > GO
> > > declare @.f sysname
> > > set @.f=N'C:\SQL
> > > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > > BACKUP DATABASE [master] TO DISK = @.f WITH NOFORMAT, NOINIT, NAME => > > N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> > > /* This line backups named database to location specified*/
> > > GO
> > > declare @.backupSetId as int
> > > select @.backupSetId = position from msdb..backupset where
> > > database_name=N'master' and backup_set_id=(select max(backup_set_id) from
> > > msdb..backupset where database_name=N'master' )
> > > if @.backupSetId is null begin raiserror(N'Verify failed. Backup information
> > > for database ''master'' not found.', 16, 1) end
> > > RESTORE VERIFYONLY FROM DISK = N'C:\SQL
> > > Backups\master_fullbackup_'+convert(nvarchar,getdate(),112)+N'.bak'
> > > WITH FILE = @.backupSetId, NOUNLOAD, NOREWIND
> > >
> > >
> > > GO|||"lca1630" <lca1630@.discussions.microsoft.com> wrote in message
news:C6063C2C-D9F0-4082-BBDA-DE010216638E@.microsoft.com...
> Hi,
> GO
> DBCC SHRINKDATABASE(N'master' )
> /* This line shrinks database and log file*/
Another cmment: Don't do the above step.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Help with script and variables

Hi If I try to run the code below I get the following errors

Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'view'.
Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near '@.month1'.

I am not sure why it does not like the keyword view ? also I am trying to use the variables in the column name of the create table but again it does not like this.

-- declare all variables!

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money,
@.month2 money,
@.month3 money
)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

For starters, the CREATE VIEW statement has to be the first statement in a batch so it can't be used in this way.

You could dynamically create your CREATE VIEW statement then use sp_executesql to execute the command.

Chris

|||

Hi thanks for the reply

I am quite new to this what do you mean by dynamically create the CREATE VIEW statement ?

|||

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate -- you cant have variabl inside view
and date_and_time <= @.enddate1 -- you cant have variabl inside view

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money, -- Remove @.
@.month2 money, -- Remove @.
@.month3 money -- Remove @.

)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

and also tell us what u are intended to do... there are couple of wrong sysntax in the script... you can not use variable inside a view definition...

Madhu

|||

Hi

First of I am trying to create a view of data between a specific date range. This is the last three months. So if it was to run today then the view would contain data from 1st nov 2006 to 28th feb 2007.

When it runs on the 1st Apr the view would contain data from 1st Dec to 31st march and so on. This is why I am trying to drop the view first then create it. I am then running a query on the view and inserting the results of this into the table i create in the script.

Secondly as I am working with a rolling 3 months of data I need to drop the table I insert the data into an create it again with the correct column headings ie NOV,DEC,JAN. This is why I have the variables in the create view and create table statements.

If this is not possible is their an other way of doing this ?

Hope that makes sense

|||

I wouldn't bother creating a View for temporary purposes, you can use the SELECT statement as is to directly insert data into tables.

Try the code below to drop and create your table - I see no need for a cursor in the scenario you have presented.

Chris

DECLARE @.startdate DATETIME

DECLARE @.enddate DATETIME

DECLARE @.enddate1 DATETIME

DECLARE @.month1 NVARCHAR(10)

DECLARE @.month2 NVARCHAR(10)

DECLARE @.month3 NVARCHAR(10)

DECLARE @.TableName NVARCHAR(100)

DECLARE @.SQLString NVARCHAR(4000)

SELECT @.startdate = dbo.removetime(DATEADD(month, -3, getdate())),

@.enddate = dbo.removetime(DATEADD(month, -2, getdate())-1),

@.enddate1 = dbo.removetime(DATEADD(month, 0, getdate())-1),

@.month1 = left(dbo.removetime(DATEADD(month, -3, getdate())),5),

@.month2 = left( dbo.removetime(DATEADD(month, -2, getdate())-1),4),

@.month3 = left(dbo.removetime(DATEADD(month, 0, getdate())-1),4)

--The name of the new table

SELECT @.TableName = N'Temp_Table'

--Build the string to drop the table

SET @.SQLString =

N'IF OBJECT_ID(' + QUOTENAME(@.TableName, '''') + ') IS NOT NULL DROP TABLE [' + @.TableName + '];'

--Build the string to create the table

SET @.SQLString = @.SQLString +

N'CREATE TABLE [' + @.TableName + ']

(

[account_no] int,

[account_holder_surname] varchar(80),

[account_holder_forename] varchar(80),

[' + @.month1 + '] money,

[' + @.month2 + '] money,

[' + @.month3 + '] money

)'

--Execute the statements

EXEC(@.SQLString)

--Prove that the new table exists

--EXEC sp_help 'Temp_Table'

/*

--Don't create a 'temporary' view - just use the query directly like this...

INSERT INTO.... / UPDATE etc...

select column1,column2

from some_table

where date_and_time >= @.startdate

and date_and_time <= @.enddate1

*/

|||

Hi Chris

Thanks so much. It was the syntax around the create table I could not get my head around !

Thanks

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>sql

Help with modifying a data source''s query at runtime

Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

'

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I think (but I'm not positive) that you need to call ReinitializeMetaData on the destination component after updating the path, so it has a chance to correct any changed columns. Actually, if you call that, you might not have to remove and add the path at all.

|||

Before running the AcquireConnections you need to re-link the connection with the source component. It has the connection ID persisted and you need to get the connection object (use the ID to find it) and assign it to the component's runtime connection.

HTH,

Bob

Help with modifying a data source''s query at runtime

Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

'

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I think (but I'm not positive) that you need to call ReinitializeMetaData on the destination component after updating the path, so it has a chance to correct any changed columns. Actually, if you call that, you might not have to remove and add the path at all.

|||

Before running the AcquireConnections you need to re-link the connection with the source component. It has the connection ID persisted and you need to get the connection object (use the ID to find it) and assign it to the component's runtime connection.

HTH,

Bob

Help with modifying a data source''s query at runtime

Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Public Class ScriptMain

Public Sub Main()

'

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx", Nothing)

Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I think (but I'm not positive) that you need to call ReinitializeMetaData on the destination component after updating the path, so it has a chance to correct any changed columns. Actually, if you call that, you might not have to remove and add the path at all.

|||

Before running the AcquireConnections you need to re-link the connection with the source component. It has the connection ID persisted and you need to get the connection object (use the ID to find it) and assign it to the component's runtime connection.

HTH,

Bob

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail