Web Design & Development

DTS error:

The log file for database ‘dtsPackageName’ is full. Back up the transaction log for the database to free up some log space.

Solution:

backup log epack_dev with TRUNCATE_ONLY

exec msdb..sp_enum_dtspackages

‘ Rename File from Connection
Option Explicit

Function Main()

Dim oFSO
Dim sFileName

sFilename = DTSGlobalVariables.Parent.Connections(”Text File (Source)”).DataSource

Set oFSO = CreateObject(”Scripting.FileSystemObject”)

‘ Check for file and return appropriate result

If oFSO.FileExists(sFilename) Then

‘Main = DTSTaskExecResult_Success
Main = DTSStepScriptResult_ExecuteTask

Else

‘Main = DTSTaskExecResult_Failure
Main = DTSStepScriptResult_DontExecuteTask

End If

Set oFSO = Nothing

End Function

‘ Rename File from Connection
Option Explicit

Function Main()

Dim oPkg
Dim oConn
Dim oFSO
Dim oFile
Dim sFilename
Dim sFileDestination
Dim sMonth
Dim sDay
Dim sYear
Dim sHour
Dim sMinute
Dim sSecond

‘ Get reference to the current Package object
Set oPkg = DTSGlobalVariables.Parent

‘ Get reference to the named connection
Set oConn = oPkg.Connections(”Text File (Source)”)

‘ Get the filename from the connection
sFilename = oConn.DataSource

sMonth = CSTR( MONTH(Now()))
sDay = CSTR(DAY(Now()))
sYear = CSTR(YEAR(Now()))
sHour = CSTR(HOUR(Now()))
sMinute = CSTR(MINUTE(Now()))
sSecond = CSTR(SECOND(Now()))

sFileDestination = DTSGlobalVariables(”dts_ArchiveLocation”).Value & DTSGlobalVariables(”dts_FileName”)

‘MsgBox sFileDestination & sMonth & sDay & sYear & “.TXT”

‘ Rename the file
Set oFSO = CreateObject(”Scripting.FileSystemObject”)
‘oFSO.MoveFile sFilename, sFilename & “.bak”
oFSO.MoveFile sFilename, sFileDestination & sYear & sMonth & sDay & “_” & sHour & sMinute & sSecond & “.TXT”

‘ Clean Up
Set oConn = Nothing
Set oPkg = Nothing
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

sp_help_job returns information about jobs that are used by SQLServerAgent service to perform automated activities in SQL Server.

EXEC MSDB.DBO.SP_HELP_JOB

List all the database on SQL Servers.

SQL SERVER 2005 System Procedures

EXEC sp_databases
EXEC sp_helpdb

SQL 2000 Method still works in SQL Server 2005

SELECT name FROM sys.databases
SELECT name FROM sys.sysdatabases

The db_datareader role

The db_datareader role has the ability to run a SELECT statement against any table or view in the database. This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. A better practice would be to create a user-defined database role with the proper permissions.

One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give a user-defined role permission to access a database object such as a table or view, that role can’t do so. Not only does it have access to user tables but also system tables. That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned).

SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112)

This is a way to query and get the date in the following format:

YYYYMMDD

I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar. An additional problem I had to conquer was to make the year minus one year, but I haven’t found that answer yet.

View Standard Date Formats

(Source)

Error: Back up the transaction log for the database to free up some log space.

To recover from a full transaction log

If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use:

DUMP TRANSACTION WITH NO_LOG

And for version 7 or later:

BACKUP LOG WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.