How to access SQL Server on Windows through a secure connection from Perl on Linux

Scenario Have some perl code that wants to access Microsoft SQL Server 2005.   The SQL Server is configured to only allow SSL connections for security reasons. Solution  Here are the parts you will need: Perl on Linux (given) DBI … Unix ODBC (or equivalent like iODBC) … FreeTDS (or equivalent ODBC driver) ……


How to do logic OR AND NOT across rows in SQL

Scenario You have a normalized table that has values in rows.   Let’s take an example that has a history table with an approved flag:  Date  Approved  DollarValue  Customer1/1/2008  Y    13.5    A2/1/2008  N    12      A3/1/2008  Y    14      A 1/1/2008  Y    11.5    B2/1/2008  Y    9        B3/1/2008  Y    17      B 1/1/2008  Y    13.5    C2/1/2008  Y    29        C3/1/2008  Y    18      C  You…


Custom Security and SQL Job with SSIS Script Task

Scenario Recently I ran into a security issue that has a simple fix.  Here is the situation: SSIS Package that contains a Script Task Run SSIS package as a SQL Job Use a special credential/proxy for the step of executing the SSIS package Results In the SQL Job History Log you may find something like…


Optimizing queries with joins and over statements in SQL 2005

I was working on a common scenario and found an interesting side effect. Applications typically have a listing page that get search criteria from a UI and passes to a stored procedure. In the procedure you often find statements like: SELECT a,b,c,d FROM MyTableWHERE c = @SearchC AND d = @SearchD Often times you need…


SQL Server 2005 UNPIVOT Command – changings columns to rows (normalizing)

ScenarioA table that contains several columns that you need to take and change into rows in order to normalize the data.   With SQL Server 2005, a new T-SQL command UNPIVOT can help.   An example table: **Table called MyTableFieldID FieldOne FieldTwo FieldThree1       abc      3.40     2008-03-03 00:00:00.0002       def      4.00     2008-01-02 00:00:00.000 **Table DesignFieldID is an integerFieldOne is char(10) FieldTwo is decimal(14,2)FieldThree…


How to write SQL to get a record to process in a cluster of processing computers

Working with a friend at work, we were discussing how to create some SQL to go after records in a table and process in a farm of computers (processing cluster).   After few minutes of thinking I came up with the following.  BEGIN TRAN MyTranUPDATE TOP 1 [dbo].[SomeTable] WITH (READPAST,UPDLOCK)   SET [IsCompleted] = 1, @RowId = ID  …


How to backup and truncate all log files in a database

Some things to consider …  Make sure you can recover your server and database prior to issuing these commands. Since I am generically calling a rountine you will receive errors on the system databases.   Your database and log files must follow the convention of name=database name_log=log file name. This will force the reduction of your…


Unable to delete or disable SQL Server job due to error regarding MSX server

Have you ever run across this error before when trying to delete or disable a SQL server job (or edit one):Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.   This sometimes can happen if you rename a server to another name after you have created your jobs…


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. I was wondering if anyone else has had any issues related to this or found alternatives.