Showing posts from November, 2013

IE11 __dopostback undefined

After attempting to access my web app in IE11 I noticed all the links were no longer working.  Looking through the JavaScript debugger I saw the error "__dopostback undefined" when attempting to click a link.  Solution turned out to be installing .NET 4.5 on the server.

Repeat Column Headers for Tablix in SSRS 2008

If you are having trouble getting the column headings to repeat on every page for a table/tablix in SSRS 2008 try this. Open the file in a text editor and look for the Tablixrowheirarchy->tablixMembers->TablixMember node. Then add the following elements. <keepwithgroup>After</keepwithgroup> <repeatonnewpage> true </repeatonnewpage> <keeptogether> true </keeptogether>

SQL - Generate Scripts to Drop Tables/Views

You can use the commands below to create SQL commands which will drop tables/views.  the were clause can be used to limit which tables/views are included in the statement. Drop Tables SELECT 'DROP TABLE [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'CRM%' AND TABLE_TYPE = 'BASE TABLE' Drop Views SELECT 'DROP VIEW [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME NOT LIKE 'CRM%'

SQL - Add Header Row to SSIS Generated CSV Files

After generating a flat file in SSIS I needed to update that file with the column headers as the first row.  In order to do this I used a SQL stored procedure. STEP 1 : Run the code below to create the stored procedure. USE MIGRATION_DB IF OBJECT_ID( 'GenerateBCPforSSIS' ) IS NOT NULL DROP PROC GenerateBCPforSSIS GO CREATE PROCEDURE GenerateBCPforSSIS ( @db_name varchar (1000), @table_name varchar (1000), @file_name varchar (1000) ) AS Declare @Headers varchar ( MAX ),@HeadersRaw varchar ( MAX ),@ sql varchar ( MAX ), @header_file varchar ( MAX ), @filename_short varchar ( MAX ) --Generate column names as a recordset Select @Headers = IsNull(@Headers + ',' , '' ) + '""' + Column_Name + '""' From INFORMATION_SCHEMA.COLUMNS Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC -- Create a dummy file to have header data select @header_file= substring (@file_name,1,len(@file_name)-charind

SQL - Remove Special Characters

Removes all special characters from an input string. USE MIGRATION_DB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID (N 'dbo.RemoveSpecialChars' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveSpecialChars; GO CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR ( max ) ) RETURNS VARCHAR (8000) BEGIN IF @InputString IS NULL RETURN NULL DECLARE @OutputString VARCHAR ( max ) SET @OutputString = '' DECLARE @l INT SET @l = LEN(@InputString) DECLARE @p INT SET @p = 1 WHILE @p <= @l BEGIN DECLARE @c INT SET @c = ASCII( SUBSTRING (@InputString, @p, 1)) IF @c BETWEEN 48 AND 57 OR @c BETWEEN 65 AND 90 OR @c BETWEEN 97 AND 122 -- OR @c = 32 SET @OutputString = @OutputString + CHAR (@c)

SQL - Remove Non-Alpha Characters

This function will remove all non-alphanumeric characters. USE MIGRATION_DB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID (N 'dbo.RemoveNonAlphaCharacters' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveNonAlphaCharacters; GO Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(max)) Returns VarChar(max) AS BEGIN DECLARE @KeepValues as varchar(50) = '%[^a-z]%' WHILE PatIndex(@KeepValues, @Temp) > 0 SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '' ) RETURN @Temp END GO

SQL - Remove Line Breaks From Text

This function will attempt to remove line breaks from text in SQL. USE MIGRATION_DB GO IF OBJECT_ID (N 'dbo.RemoveLineBreak' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveLineBreak; GO CREATE FUNCTION dbo.RemoveLineBreak (@TEXT VARCHAR ( MAX )) RETURNS VARCHAR ( MAX ) AS BEGIN DECLARE @fixedTEXT VARCHAR ( MAX ) = @TEXT SET @fixedTEXT = REPLACE(REPLACE(REPLACE(REPLACE(@TEXT, CHAR (10), '' ), CHAR (13), '' ), '\r' , ' ' ), '\n' , ' ' ) RETURN @fixedTEXT END GO

SQL - Get Nth Word

Need to find the Nth word in a sentence, try this function. USE MIGRATION_DB GO IF OBJECT_ID (N 'dbo.GetNthWord' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.GetNthWord; GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION dbo.GetNthWord ( @string varchar ( max ), @n int ) RETURNS varchar ( max ) AS BEGIN DECLARE @xml xml, @s nvarchar( max ), @wd varchar (100) set @xml = '<root><record>' + REPLACE(@string, ' ' , '</record><record>' ) + '</record></root>' ; With rs(id, word) AS ( select ROW_NUMBER () over ( order by ( select 1))id , * from ( select t. value ( '.' , 'varchar(150)' ) as [items] from @xml.nodes( '//root/record' ) as a(t)) data where len([items]) > 0 ) select @wd=word from rs where id=@n return (@wd) END