[Tips] Create a comma-separated list in one query using COALESCE

Clever, clever. Wish I could take credit for it.

-----------------------------------
--Creating a comma-separated list--
-----------------------------------
 
DECLARE @EmployeeList varchar(100)
 
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') 
+ CAST(id AS varchar(15))
FROM temp_td_200704

SELECT @EmployeeList

[SSAS] Quick notes on logging queries run against the cube

There's a lot of details out there about OlapQueryLog, but here are the 3 things I ran into setting it up:


Tips and tricks from setting this up:

1. Do it on whatever server hosts the cube, since it will determine what version of the SQL driver it needs.
   
2. Event Viewer will tell you the errors you’re having, be they permissions, bad version of the SQL driver, etc.
   
3. The key settings:
   
   • Log\QueryLog\CreateQueryLogTable = true
     
   • QueryLogConnectionString (click and set it)
     
   • QueryLogSampling is "every X queries, save the query to table". So the default means every 10th query gets saved.
     

[Trick] Eliminating "arithmetic overflow error" that aren't in the result set.

Ran into a problem where the SP would always kick out the standard overflow message:

Arithmetic overflow error converting numeric to data type varchar

As it turns out, the problem was data that exists in the data set, but not in the results set - the WHERE clause eliminated it. What made it even harder to troubleshoot was that because that was the issue, we could duplicate it by running the SP - but not by copying and pasting the code. No matter what options you used - ARITHABORT, ANSI_WARNINGs, etc, it would run successfully, skipping over the bad record (since it was excluded via the WHERE clause).

As is pointed out in "Defensive Database Programming with SQL Server" by Alex Kuznetsov (WELL worth getting, and Red-gate offers a free PDF), you can't guarantee the order things get evaluated in.

So one way around it: take your query that is failing in the SELECT clause. Find a unique key that you can pull from your data set. Copy/paste the entirety of the FROM/WHERE, and SELECT only this key into a temp table. Now go to your full query and INNER JOIN this temp table.

Another potential way around it: remove everything from the where clause and keep it in the JOINs.

[Code] Stripping low-ascii out of a table, slow way

I'm working on a CLR to do this considerably faster, but here's what I have for now. The purpose of this is to remove dirty data from an upstream feed. We can't make XML with it - the code that creates the XML chokes on it. Note that this isn't terribly fast, because we're brute-forcing it and having to loop through (31 * number-of-fields) times. The CLR should do it in one pass per field.

--CREATE PROCEDURE [dbo].[strip_low_ascii]  
--as  
DECLARE @columns TABLE (id INT IDENTITY, column_name sysname)  
DECLARE @odd_ascii CHAR(1),
  @ascii_value int,
  @column_start smallint, 
  @column_end SMALLINT, 
  @sql NVARCHAR(4000), 
  @field_name sysname, --to allow us to loop through all fields  
  @table_schema sysname, 
  @table_name sysname
SET @ascii_value = 31   --31 aka 0x1F.  Space is 32/0x20.  0-30 is our "low-ascii" range
SET @table_schema = 'dbo'
SET @table_name = 'yourtablename'

CREATE TABLE #list_of_id (id bigint primary key)
  
INSERT INTO @columns  
      SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS   
      WHERE TABLE_SCHEMA = @table_schema 
  AND TABLE_NAME = @table_name
  AND DATA_TYPE IN ('char','varchar','nchar','nvarchar')  
  
SELECT @column_start = MIN(id), @column_end = MAX(id) FROM @columns  
  
WHILE @ascii_value >= 0 --look for all low ascii  
BEGIN  
      SET @odd_ascii = NULL  
      SET @column_start = 1  
      SELECT @odd_ascii = CHAR(@ascii_value)  
  
      WHILE @column_start <= @column_end  
      BEGIN  
   TRUNCATE TABLE #list_of_id
            SET @field_name = NULL  
            SET @sql = NULL   
            SELECT @field_name = column_name FROM @columns WHERE id = @column_start  
            SELECT @sql = 'insert into #list_of_id 
                  SELECT id FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + '
                  WHERE insert_datetime >=CONVERT(CHAR(8),GETDATE(),112)  
                  AND ' + @field_name + ' LIKE ''%'' +  @oddascii  + ''%''  
      IF @@rowcount >0   --( select count from @list_of_id )
            update ' + @table_name + ' set ' + @field_name + ' =  REPLACE(' + @field_name + ',  @oddascii, '''')   
            WHERE insert_datetime >=CONVERT(CHAR(8),GETDATE(),112)  
   and id in (select id from #list_of_id)
            AND ' + @field_name + ' LIKE ''%'' + @oddascii + ''%'''  
            
  
--PRINT @sql  
  
            EXECUTE sp_executesql @sql, N'@oddascii char(1)', @oddascii = @odd_ascii  
  
            SET @column_start = @column_start + 1  
      END  
  
      SET @ascii_value = @ascii_value-1  
END