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 TablesSELECT'DROP TABLE [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOTLIKE'CRM%'
AND TABLE_TYPE = 'BASE TABLE'


Drop ViewsSELECT'DROP VIEW [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOTLIKE'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') ISNOTNULL
DROPPROC GenerateBCPforSSIS
GO

CREATEPROCEDURE GenerateBCPforSSIS
(
@db_name varchar(1000),
@table_name varchar(1000),
@file_name varchar(1000)
) AS

Declare @Headers varchar(MAX),@HeadersRaw varchar(MAX),@sqlvarchar(MAX), @header_file varchar(MAX), @filename_short varchar(MAX)

--Generate columnnamesas a recordset
Select @Headers = IsNull(@Headers + ',', '') + '""' + Column_Name + '""'
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDERBY ORDINAL_POSITION ASC

--Create a dummyfileto have header data
select @header_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

set @sql = 'bcp "sel…

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') ISNOTNULL
DROPFUNCTION dbo.RemoveSpecialChars;
GO
CREATEFUNCTION dbo.RemoveSpecialChars
( @InputString VARCHAR(max)
)
RETURNSVARCHAR(8000) BEGIN
IF @InputString ISNULLRETURNNULL

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
ENDIF LEN(@OutputString) = 0 RETURNNULL
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') ISNOTNULL
DROPFUNCTION dbo.RemoveLineBreak;
GO
CREATEFUNCTION dbo.RemoveLineBreak
(@TEXT VARCHAR(MAX))
RETURNSVARCHAR(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') ISNOTNULL
DROPFUNCTION dbo.GetNthWord;
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEFUNCTION dbo.GetNthWord
(
@string varchar(max),
@n int
)
RETURNSvarchar(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( orderby (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