September 2009 Entries

conditional page break inside the repeater on a dynamically created web user control

Business Scenario: The client wants to build a few reports (only in HTML format) that basically have the same format, but needs paging when the report is sent to the printer. Solution: I built the report inside a user control to reuse the formatting, but had to solve the problem where hard-coding the page break at the UI side will give me an extra page in printing. Code: Inside ASCX: <%# InsertPageBreakIfNotLastPag... "ItemIndex")) %> Code behind (C #): public string ......

Posted On Friday, September 25, 2009 12:27 PM | Comments (0)

BizTalk nsoftware SFTP adapter VS file encoding

We run into this problem time and time again, and just want to put out the blog and set the record straight. BizTalk nsoftware SFTP adapter v2 does not have an option to change the encoding of the file. The only possible way that I can see if to upgrade to v3 and use the "After Get" to simulate a FTP command to change the encoding of the file. For another project, we used a sort of "hack" to ftp to and from an internal location to change the encoding, but that only made sense because it's a monthly ......

Posted On Thursday, September 24, 2009 3:32 PM | Comments (0)

Create synonym for all tables from another server or linked server object

I created the following into a stored procedure so that I can run it again and again: DECLARE @dynsqlcmd VARCHAR(250) DECLARE @tblname VARCHAR(150) DECLARE @synname VARCHAR(150) DECLARE @cnter INT -- Drop all synonyms DECLARE dbcursor CURSOR FOR SELECT AS 'table name' FROM sysobjects WHERE sysobjects.type = 'sn' OPEN dbcursor FETCH NEXT FROM dbcursor INTO @synname WHILE (@@FETCH_STATUS =0) BEGIN SET @dynsqlcmd = 'DROP SYNONYM ' + @synname EXEC (@dynsqlcmd) FETCH NEXT FROM dbcursor ......

Posted On Thursday, September 24, 2009 1:48 PM | Comments (1)

Turn off Server Message Block 2 feature

We need to turn off this feature in Windows 7, Vista, Server 2008.  Server 2008 should fall back to SMB automatically.


Posted On Wednesday, September 23, 2009 3:14 PM | Comments (0)

Using xp_cmdshell to execute SSIS in a stored procedure

This is the code: DECLARE @SQLCommand AS VARCHAR(1000) SET @SQLCommand = '("dtexec location" /F "package location and name" ' + '/SET \package.Variables[User::your variable name].Value;' + CONVERT(VARCHAR(2), MONTH(@Date)) + '/' + CONVERT(VARCHAR(2), DAY(@Date)) + '/' + CONVERT(VARCHAR(4), YEAR(@Date)) + ')' EXEC xp_cmdshell @SQLCommand Some notes: I tried to pass in the date without formatting it, I get a "Option "1" is not valid" message. I had to convert the date into a string to include in the ......

Posted On Wednesday, September 23, 2009 12:41 PM | Comments (0)

Different syntax to access variables in SSIS

In a Script Task (in control flow), it is Dts.Variables("variable_name")

In a Script Component (in dataflow), it is Me.Variables.variable_name

Posted On Wednesday, September 23, 2009 10:41 AM | Comments (0)

An awesome website on SQL code

Sometimes you have to resort to slang to express yourself, like on this ocasion there is nothing else to say except: "This dude is awesome".

I used his stored procedure to script out that data in a table.  Used to do this in TextPad with Regular expression.  Can't imagine how much time this saves me.

Posted On Wednesday, September 23, 2009 10:27 AM | Comments (0)

To capture all errors in an SSIS package

Script task to catch all SSIS errors. I then used an email task to send out the error report. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Run... Public Class ScriptMain 'Capture Error Public Sub Main() Dim msgs As Collections.ArrayList Try msgs = CType(Dts.Variables("ErrorC... Collections.ArrayList) Catch ex As Exception msgs = New Collections.ArrayList End Try msgs.Add(Dts.Variables("Err... Dts.Variables("ErrorCollect... ......

Posted On Friday, September 11, 2009 10:50 AM | Comments (2)

Using sql update statement instead of cursor

I've seen too many instances where a cursor can be simply rewritten as an update statement. I was working at a client site where one of their process calls a stored procedure that uses a cursor do some updates. That stored procedure would run over the weekend to update about 2000 records and bog down the production SQL (You'd think I was joking). We re-write the stored procedure. Granted, the update itself is about 50 lines with 5 tables, subqueries, and left outer joins, but we got the stored procedure ......

Posted On Friday, September 11, 2009 10:36 AM | Comments (0)

How to add leading zero's on a numeric value to a fixed width flat file field

Code: Man, I looked at this code and was wondering what the heck I was doing. Then I remember this was done to convert a MONEY datatype to a number with 2 decimal places. If you are working with an integer, the code is simple: REPLICATE("0",(5 - LEN(TRIM((DT_WSTR,4)[field_... + TRIM((DT_WSTR,4)[field_name]) The code above returns a 5-character string with leading 0's. I believe the following code returns a 15-character long string, can't be 100% sure at this moment because I can't remember ......

Posted On Friday, September 11, 2009 10:23 AM | Comments (0)

.NET corrupt???

I was installing SQL 2008 on my local box and ran into this issue. I even tried this: But after several different approaches (rebuild .net from standalone install; remove all visual studio apps, etc.), I had to give up. The only explanation I have left is that this is a computer that was built from an image. And somehow those machines never work quite right for me ......

Posted On Thursday, September 10, 2009 11:37 AM | Comments (0)

Google patent analysis

Even though this is 4 years later, it should be of some help to put away some of the SEO myths: I think something very interesting to me (surprising, but ingenious) is that Google even tracks how long your site is up and the change of link (how many links to your sites are added each day) and use that to calculate page rank. It's something a lot of SEO are overlooking (maybe purposely) and something that just can't be manufactured (well, considering ......

Posted On Thursday, September 10, 2009 11:29 AM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski