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…

0

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…

1

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…

1

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…

3

TFS History via command line for a date range

I know several people have blogged about this and it is available in the help files somewhat.   I thought I would just capture a quick note on what I sometimes use. Scenario: Show me all the changes (history) for all files within a location in a team project on a server for a specified date…

2

How to use or setup Sharepoint or Reporting Services on a separate port or machine with TFS

You can use the following to move your sharepoint location on your existing TFS Sharepoint sites or Repritng Services to another port or to another server.   Note that you must have the Sharepoint site or Reporting Services already configured. TFS 2005/2008: During Setup Copy msiproperty.ini from the install media from AT or ATDT folder to…

0

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  …

1

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…

4

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…

2

SOAP Toolkit + Vista + Excel 2007 + Call Web Services = Impossible

Well I wouldn’t say impossible … just not supported. I had a conversation with a client that asked “Where do I get the latest SOAP toolkit for Vista?”.   I had to politely say “We don’t support the SOAP Toolkit anymore.”  Which was followed by “Well how do I call a web service from VBA in…

3