PackageID in Logging Provider

Is there a technical reason (I imagine the real reason is time) that PackageID wasn't included as a Logging Provider column to log? While you can still track this by doing some fun things like event handlers or self-joining, I would think from a usability perspective, you should avoid that altogether by just adding the column.

I added a suggestion here to vote on if anyone else agrees:

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=e58141d5-859f-4941-a675-cb9352d85575

-- Brian

I'd rather have PackageName!!!

Mind you, one thing I do is dynamically set up the name of my log file using the following expression:

REPLACE(@.[System::PackageName], " ", ".") + (DT_STR, 4, 1252) DATEPART( "yyyy", @.[System::StartTime] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "mm", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "dd", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "hh", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "mi", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "ss", @.[System::StartTime] ), 2) + ".log"

Which gives a package logfilename of MyPackageName20060111142153.log

i.e. Something that contains the package name, and the added benefit that you get a new file for each execution

-Jamie

package with a custom component (log provider) runs in BIDS, but doesn't run where deployed.

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls? I'm really stuck.

Many thanks in advance,

Tamim.

Please search this forum...

You'll need to set the package protection level to "DontSaveSensitive" and then issue a password via the /SET command line switch. OR, you could try to promote to the server using SQL Server users/role level security, maybe....|||

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

|||

Tamim Sadikali wrote:

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

What do you mean that didn't work? That error message states that you are using indicates to me that the protection level is set to EncryptSensitiveWithUserKey, which means that ONLY the user who built the package can execute it.

package fails after OnPreValidate (but not in BIDS)

I have all the package logging tickboxes checked but in the log file I only get this:
OnPreValidate,<machinename>,<account>,<packagename>,{7741AD7F-1941-4F4C-AE9D-08068C8856E4},{F6924552-600A-450B-995F-C24AB5C49FC3},6/25/2007 5:21:12 PM,6/25/2007 5:21:12 PM,0,0x,(null)

Then nothing.

The package fails.

When I run it in BIDS I do not get an error.

Anyone know why?If you're not running the package in BIDS, how are you running it? Agent?|||it is running as an all powerful system user|||Right, but is DTEXEC being called from Agent, from a command prompt, or are you using a custom app to run the packages?|||

think this was similar problem to the other issue I was having

we changed the security level to use password, deploy and it works fine now

shame the error couldn't have been more informative

|||

adolf garlic wrote:

think this was similar problem to the other issue I was having

we changed the security level to use password, deploy and it works fine now

shame the error couldn't have been more informative

Well, that's why John and I asked the question that you never answered.... We already knew what the problem was, but needed you to confirm and walk through the problem on your own.

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
Leon
You can't if you are using a single device. It is all or nothing with your
only options being to use INIT or NOINIT. INIT will remove ALL files in the
device and NOINIT will simply append. Don't use a logical device and
instead backup to a different file name each time. Then you can delete what
you want.
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> I'm backing up my transaction log on backup device every 15 minutes. I
> want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon
>
|||Thank you, Andrew, I hope this is something that is going to be addressed in
Yukon.
Thanks again for your help!
"Andrew J. Kelly" wrote:

> You can't if you are using a single device. It is all or nothing with your
> only options being to use INIT or NOINIT. INIT will remove ALL files in the
> device and NOINIT will simply append. Don't use a logical device and
> instead backup to a different file name each time. Then you can delete what
> you want.
> --
> Andrew J. Kelly SQL MVP
>
> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
>
>
|||Not that I know of. This isn't a bug it is simply the way it works. There
is alreay a viable way to handle this. That is to use multiple devices as I
already mentioned. Here is a simple example of how to do this with tsql:
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:176BA8ED-9759-4D19-BD95-25086540C399@.microsoft.com...[vbcol=seagreen]
> Thank you, Andrew, I hope this is something that is going to be addressed
> in
> Yukon.
> Thanks again for your help!
> "Andrew J. Kelly" wrote:

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
Leon
Hi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
Thanks
Yogish
"Leon Shargorodsky" wrote:

> I'm backing up my transaction log on backup device every 15 minutes. I want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon
|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
[vbcol=seagreen]
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup section,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
LeonYou can't if you are using a single device. It is all or nothing with your
only options being to use INIT or NOINIT. INIT will remove ALL files in the
device and NOINIT will simply append. Don't use a logical device and
instead backup to a different file name each time. Then you can delete what
you want.
--
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> I'm backing up my transaction log on backup device every 15 minutes. I
> want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon
>|||Thank you, Andrew, I hope this is something that is going to be addressed in
Yukon.
Thanks again for your help!
"Andrew J. Kelly" wrote:
> You can't if you are using a single device. It is all or nothing with your
> only options being to use INIT or NOINIT. INIT will remove ALL files in the
> device and NOINIT will simply append. Don't use a logical device and
> instead backup to a different file name each time. Then you can delete what
> you want.
> --
> Andrew J. Kelly SQL MVP
>
> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> > I'm backing up my transaction log on backup device every 15 minutes. I
> > want
> > every backup expire in 1 hour so only 4 sequential transaction log backups
> > reside on my backup device. How do I do that? Thank you in advance
> >
> > Leon
> >
>
>|||Not that I know of. This isn't a bug it is simply the way it works. There
is alreay a viable way to handle this. That is to use multiple devices as I
already mentioned. Here is a simple example of how to do this with tsql:
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:176BA8ED-9759-4D19-BD95-25086540C399@.microsoft.com...
> Thank you, Andrew, I hope this is something that is going to be addressed
> in
> Yukon.
> Thanks again for your help!
> "Andrew J. Kelly" wrote:
>> You can't if you are using a single device. It is all or nothing with
>> your
>> only options being to use INIT or NOINIT. INIT will remove ALL files in
>> the
>> device and NOINIT will simply append. Don't use a logical device and
>> instead backup to a different file name each time. Then you can delete
>> what
>> you want.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote
>> in
>> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
>> > I'm backing up my transaction log on backup device every 15 minutes. I
>> > want
>> > every backup expire in 1 hour so only 4 sequential transaction log
>> > backups
>> > reside on my backup device. How do I do that? Thank you in advance
>> >
>> > Leon
>> >
>>

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
LeonHi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
--
Thanks
Yogish
"Leon Shargorodsky" wrote:
> I'm backing up my transaction log on backup device every 15 minutes. I want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup section,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:
> > I'm backing up my transaction log on backup device every 15 minutes. I want
> > every backup expire in 1 hour so only 4 sequential transaction log backups
> > reside on my backup device. How do I do that? Thank you in advance
> >
> > Leon

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
LeonYou can't if you are using a single device. It is all or nothing with your
only options being to use INIT or NOINIT. INIT will remove ALL files in the
device and NOINIT will simply append. Don't use a logical device and
instead backup to a different file name each time. Then you can delete what
you want.
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> I'm backing up my transaction log on backup device every 15 minutes. I
> want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon
>|||Thank you, Andrew, I hope this is something that is going to be addressed in
Yukon.
Thanks again for your help!
"Andrew J. Kelly" wrote:

> You can't if you are using a single device. It is all or nothing with you
r
> only options being to use INIT or NOINIT. INIT will remove ALL files in t
he
> device and NOINIT will simply append. Don't use a logical device and
> instead backup to a different file name each time. Then you can delete wh
at
> you want.
> --
> Andrew J. Kelly SQL MVP
>
> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
>
>|||Not that I know of. This isn't a bug it is simply the way it works. There
is alreay a viable way to handle this. That is to use multiple devices as I
already mentioned. Here is a simple example of how to do this with tsql:
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:176BA8ED-9759-4D19-BD95-25086540C399@.microsoft.com...[vbcol=seagreen]
> Thank you, Andrew, I hope this is something that is going to be addressed
> in
> Yukon.
> Thanks again for your help!
> "Andrew J. Kelly" wrote:
>

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
LeonHi Leon,
If you are using DB Maintenance plan, in the transaction log backup section,
"Remove file older than" provides the functionality you are looking for.
And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
Check out for BACKUP in BOL.
Thanks
Yogish
"Leon Shargorodsky" wrote:

> I'm backing up my transaction log on backup device every 15 minutes. I wan
t
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon|||Thanks for your reply, Yogish, but this is not what I'm asking for.
The key-word here is BACKUP DEVICE, Maintenance Plan does not have option to
backup to a DEVICE.
"Yogish" wrote:
[vbcol=seagreen]
> Hi Leon,
> If you are using DB Maintenance plan, in the transaction log backup sectio
n,
> "Remove file older than" provides the functionality you are looking for.
> And if you are using, T-SQL you can specify "RETAINDAYS" parameter.
> Check out for BACKUP in BOL.
> --
> Thanks
> Yogish
> "Leon Shargorodsky" wrote:
>

overwrite backup don't overwrite the backup device

I created a plan for a backup. This first truncates the log and after backups the data with overwrite mode and after backups the transaction log with overwrite mode.
The process is using different backup devices for the data and log backups.
The first 2 step is success (truncate and data backup) but in the last step the backup process don't overwrite the backup device. Why ?It had been set to Full databases, after i set to specified database...it was success.
So if I'm setting to full databases, it's can't overwrite the log backup device (only append)...
sneaky |||

Hi Molnarcs,

Just FYI

The backup device can be overwritten even when doing the log backups.

Jag

|||when the backup was set to full databases then in the form the mode was overwrite but if i checked the sql statement (plan's history log- view T-SQL)...in the script was the "backup log model... NOINIT...backup log db_log....NOINIT"...
after when i set to specified database and i choose the db in the script was "backup log db_log INIT" so it didn't have inconsistency between the form and the ran sql script.
(sorry my english)|||

Hi Molnarcs,

you r right you have to secify INIT option with backup log.

Jag

Override a foreign key constraint

A website that I'm working on has users sign in and keeps a log of the pages they go to. The log table has a foreign key in it that links to the username is the users table. I need to update the username for one of the users but the foreign key is preventing me from doing so. What is the benefit of having a foreign key like this? Can I delete it to update the username or is there a better way?

Foreign Key constraints ensure the integrity of the data is maintained. If there was no FK in place. then it would be easy to get orphaned and inconsistent data. In your example, if you just update a username to be Bob from Terry then all the records associated with Terry in the log table will now have no link back to the users record ie the Terry records will be orphaned.

Soooooo, in answer to your question, you can drop it make your update but it will throw an error when you try and recreate the FK unless you update the log records too.

You can create these constraints with a cascading updates/deletes which will filter your changes down to the child tables but in this case, it looks like its turned off.

I'd suggest just doing the following

Psuedo code:

UPDATE Log set Username = Bob WHERE username = Terry

UPDATE user set username = Bob WHERE username = Terry.

HTH!