SQL Server 2005 SP2 – Access Denied on MSSQLData directory

This was a strange occurance I found recently.   If you encounter the error SQL Server Setup failed to modify security permissions on file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ you might want to look at the permissions on every file (sometimes ownership can be damaged).   Don’t just set it at the folder level to push the child…


Issue with SSIS Package calling stored procedure OUTPUT datetime parameter type

Symptom The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (“@parameter”): Data type 0xE7 has an invalid data length or metadata length. Solution Installing SP2 my help.  http://msdn2.microsoft.com/en-us/sql/bb426877.aspx I was wondering if anyone else has had any issues related to this or found alternatives.


SQL Server 2005 Job, SSIS Step, Data Sources Access Denied

This particular issue was interesting.  I was troubleshooting an issue where an individual had rights to run their SSIS Step in a SQL Job.  They could also change variables being passed, add/remove steps in job, almost everything except modify the Data Sources.   When attempting to change the data source you would get an access denied.  If you want to…


Selecting the Most Current Record in a table (SQL2000 and 2005 examples)

My friend Alfredo helped with some SQL code and I thought it would be interesting to share.   I was trying to select the most current record based on a unique id and a date within a single table.   I was familar with the SQL 2000 way of doing things (a sub select) but he helped…


Secure SSIS step execution when running as a SQL Job

Here is the scenario: SQL Agent account (NETWORK SERVICE or some DOMAIN ACCOUNT) does not have access to database activities in a SSIS package.   You want to run a job but need to “proxy” a security account when a SSIS package runs.   The following script sets up the information required to run a specific SQL…


Programatic way to determine space used on all database tables

Here is one way I have found in order to get all tables space used.   Effectively it queries the sp_tables and sp_spacedused in a loop to return the data. Declare@Table_Name varchar(255),@SQL varchar(max) —————————————————————————— Create a temporary table for storing result of sp_tables————————————————————————— Create Table #tServerTables(Table_Cat varchar(255),Table_schem varchar(255),Table_Name varchar(255),Table_Type varchar(255),Remarks varchar(255)) CREATE TABLE #tSpaceUsed([name] varchar(255),rows varchar(255),reserved…


Ensure best Excel export formating from SQL Server Reporting Services

When designing reports it is important to be aware that the report may be exported to Excel.   This is most apparent when data manipulation must be done to satisfy the reporting requirements.   SSRS exports to excel using the underlying data source and not the report formating itself.   For example if you have stored in the…


Launch VPC without VPC Console via command line

Ever wanted to launch a VPC without going through the console?   You can through the command-line: “C:\Program Files\Microsoft Virtual PC\Virtual PC.exe” -pc <VPCNAME> -singlepc


Login Failed for User ” – SQL Server

Periodically, I have seen issues with connecting to a remote SQL Server from SQL Server Management Studio, Asp.Net applications, ODBC connections, etc.   Often times this is specific to a machine and not a complete on/off issue with SQL Server security.   Often you can connect to other SQL servers but not particular ones.  The issue could stem…


eSCRUM template from Microsoft Released

Microsoft eScrum Version 1.0eScrum is a Web-based, end-to-end project management tool for Scrum built on the Microsoft Visual Studio Team Foundation Server platform. It provides multiple ways to interact with your Scrum project: eScrum Web-based UI, Team Explorer, and Excel or Project, via Team Foundation Office Integration. In addition, it provides a single place for…