Error Message

Unable to start execution of step 1 (reason: line(1): Syntax error).  The step failed.

The Problem

SQL renames or re-saves the package job after editing causing the package location to be inaccessible.

The Solution

Append a backslash ‘\’ to the package name

1. Navigate to your job  (Database Server – >SQL Server Agent -> Jobs -> Your Job) and right click, choose properties

2. Click on steps in the job properties, choose the first step in the job step list, click edit below the job list.

3. Add a backslash ‘\’ to the package name and save

HAPPY CODING!

SQL Transaction Logging

By default, any operations in SQL Server such as inserts, updates, and deletes are stored in the transaction log. The principle behind the transaction log is to revert the operation if an error occurs and to ship the logs for backup purposes. When you have a huge table and you initiate a delete command on all of the rows, SQL will delete each row after logging it into its transaction log.

The Problem

Logging takes a long time and the log file will grow as each record is deleted from the database. If an error is encountered, even at the last row to be deleted, all of the transactions will need to be reverted. The process of reverting the delete operations in itself takes a significant amount of time.

The Solution

SQL Server 2005 and grater enhanced the functionality of the TOP operator. It can be used in conjunction with insert, update, and delete statements. By leveraging the top function and deleting a limited set at a time, we can minimize the amount of records to be reverted in case of an error, issue less locks on the table making it more usable while the operation is taking place, and allowing the log file space to be re-used.

The following code performs a batch deletion of records 10,000 at a time:

While (select count(*) from TABLENAME) > 0
Begin
Delete Top(10000) from TABLENAME
If @@rowcount == 0 break
End

Just replace TABLENAME with the name of the table you are deleting and run the statement against your database.

HAPPY CODING!

What is collation?

Collation is a set of rules that determine how data is stored and compared in SQL server. These rules define characteristics such as how data is sorted with the correct character sequence, case sensitivity specifications, how accent marks are treated, and character widths.

How can collation cause trouble in SQL Server Management Studio in a limited security environment?

By default, when you click the ‘Databases’ node within the SQL server management studio under the Object Explorer after connecting to a server, a list of databases on that server is enumerated and shown. In a shared environment, such as a hosted SQL server account solution, each log-in only has access to its own database. This means that collation information is only available for the database that the log-in has access to.

SQL server management studio, by default, tries to grab collation information for every database that is enumerated on the server. The trouble occurs when a database that has a collation other then the default is enumerated and the log-in used to enumerate the database does not have access to the collation information. This usually results in a complete error when opening the ‘Database’ node and prevents any databases from being shown.

The error usually states:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
–> An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
–> The server principay “login” is not able to access the database “db with non default collation” under the current security context. (Microsoft SQL Server, Error:916)

How can the problem be fixed?

The quickest way to mitigate this issue is to turn off database collation by default. To do this, follow the steps below.

1. Click on the ‘Databases’ folder under the SQL server in the SQL Server Management Studio.

2. Press F7 or click the ‘View’ toolbar menu item then select ‘Object Explorer Details’

3. Right click the headers in the ‘Object Explorer Details’ data grid and deselect ‘Collation’

That’s it! If you try to re-enumerate the databases, the blocking operation will no longer be executed by default.

Happy Coding!