Posts

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. http://www.microsoft.com/en-us/download/details.aspx?id=30653

Repeat Column Headers for Tablix in SSRS 2008

Image
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)-charindex( '\&

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) SET @p = @p + 1

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