Some TSQL command to identify the version of SQL you are running:

SQL Server 6.5
SELECT @@VERSION

SQL Server 7.0
SELECT @@VERSION

SQL 2000
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

SQL 2005
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

SQL 2008
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

We had a problem with executing a SQL Job which runs a SSIS Package. Whenever the job started we would get the following error:

Executed as user: <domain>\<user>. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  09:13:01  Error: 2009-08-05 09:13:02.85     Code: 0xC002F304     Source: <source> from SFTP Execute Process Task     Description: An error occurred with the following error message: "Access is denied".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  09:13:01  Finished: 09:13:03  Elapsed:  1.843 seconds.  The package execution failed.  The step failed.

The package was executing a batch file which in his turn starts an SFTP connection and downloads some files.
It seems that it has all to do with the fact that we are running on a x64 bits server with x64 SQL Server.

To resolve the problem open the job and then edit the job step. Next click on the Execution Options Tab. Finally enable the “Use 32 bit runtime” option. This should resolve your problem.

Weird thing is, that when the owner of the job is a local administrator, the job will not fail and will execute properly.

More information can be found here and here.

I created a SQL User named UserA which was DB_Owner of 1 database. When I connected to this database with SQL Authentication I received the following error:

The server principal “User A” is not able to access the database “DatabaseA” under the current security context. (Microsoft SQL Server, error:916)

To resolve this error I logged in as SQL User UserA, and open theVObject Explorer Details View (press F7). I then right Clicked on the column header and de-selected ‘Collation’. Refresh your databases again and now it should work.