Tuesday, November 19, 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

Tuesday, November 5, 2013

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>


Monday, November 4, 2013

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('\',reverse(@file_name)))+'\data_file.csv'

set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@header_file+'" -c -C RAW -t, -S localhost -T'
print @sql

set @sql = 'type "'+@file_name+'" >> "'+@header_file+'"'
print @sql

set @sql = 'del "'+@file_name+'"'
print @sql

set @filename_short = reverse(substring(reverse(@file_name),1,charindex('\',reverse(@file_name)) -1 ) )

set @sql = 'rename "'+@header_file+'" '+'"'+@filename_short+'"'
print @sql

GO




STEP 2: Run the following command which will produce sql commands for all the views and tables in your database.  You can add where clauses to the statements to limit which views/tables commands are created for.  Note: Make sure to update the MIGRATION_DB string to the name of your database

USE MIGRATION_DB

SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.views

UNION

SELECT 'exec GenerateBCPforSSIS ''MIGRATION_DB'',''' + name + ''',''E:\SSIS Output\' + name + '.csv''' AS CommandName
FROM sys.tables



STEP 3: Copy the output from the command in step 2 and paste it into a new SQL query window.


STEP 4: Copy the output from step 3 and past it into a batch file.


STEP 5: Run the batch file, it will open your SSIS csv file and insert a header row with the column names. 


Notes:



  • The CVS file output from SSIS must match the names of your Views/Tables

  • If you change, add, or remove any of the columns in your table/view you will have to run steps 2-5 again.

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
END IF LEN(@OutputString) = 0 RETURN NULL
RETURN @OutputString
END
GO

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