Windows Authentication for SQL Server Connection
The Talend Studio expects a SQL Server user for connection details. An extra configuration step is required using the NTLMAuth library to use Windows Authentication (Active Directory).
SQL Server
using temporary tables inside if block with a procedure
In a procedure I wrote, I wanted to allow the flexibility to support various types for a flat-file. This required an IF…ELSE block. Inside the block, the data would be imported, scrubbed, and loaded into a temporary table.
sheet name formatting for openrowset excel access
I had difficulty finding any documentation for formatting the sheet name when using the OLEDB ACE engine to access an Excel file. I had originally attempted the following query: SELECT * FROM OPENROWSET(’Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;HDR=YES;Database=C:\Report Output.xlsx’, sheet1$)SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,… Continue Reading
Importing CSV with optional double quote text qualifier
In this post, I hope to show how to load in a CSV (comma-separated values) flat-file with an optional double quote (“) text qualifier and a XML format file using BULK INSERT and OPENROWSET.
The case for ISNULL
Recently I wrote a function for a report with a CASE block to take in a start and end date, and if the end date was NULL, to use the start date. WHERE theDate BETWEEN @dateStart AND CASE WHEN @dateEnd… Continue Reading
Why can’t I use my aliased column in the WHERE clause?
A query will fail when a column is aliased and then referenced in the WHERE clause. SELECT col1, col2 AS c2 FROM tbl1 WHERE c2 BETWEEN 2 AND 6SELECT col1, col2 AS c2 FROM tbl1 WHERE c2 BETWEEN 2 AND… Continue Reading
Temporary tables and table variables in SQL Server
I will briefly explain the differences between temporary tables and table variables. Temporary Tables (#) CREATE TABLE #temp (col1 …)CREATE TABLE #temp (col1 …) Table Variables (@) DECLARE @temp TABLE (col1 …)DECLARE @temp TABLE (col1 …) What do the signs… Continue Reading
Rows to column header using dynamic T-SQL
There may be instances where you need to prepare columns for a SELECT statement or for a SELECT … PIVOT.
Running total
This post will show an example of how to join a table against itself to return a running total.
Last second of future date
The following query is useful when trying to select an inclusive range of records using DATETIME. For this example, we will use GETDATE() which returns the current date & time. You can modify this at any instance in the code… Continue Reading