Using T-SQL to remove non-printable characters

 We frequently have a need to remove non-printable characters from text fields for export or printing.  Most often, this is the chars 9,10,or 13, but can frequently consist of other unicode characters.

Before I go on, let me say that I understand the whole idea of "printable" is dependant on what you mean by "print".  For simplicity, I am defining "printable" as anything in the base ASCII set (<128) that will actually display in the standard SQL Management Studio query results.  Therefore, everything else is "non-printable".  I realize there may be an exception or two, so I made sure to write code that was easily modifiable to include exceptions.

I first tried PATINDEX, but the pseudo-regex patterns can return somE wacky results, based on which default collation is being used.  It would also make the code harder to read and modify for someone not familiar with the PATINDEX flavor of regex.

So, here it is.  It is not the most efficient way, but certainly adequete, while being easy to modify.


DECLARE @I INT,
        @TEST VARCHAR(100)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
       @I=0 --start at zero


WHILE @I<256 --check entire extended ascii set
BEGIN
   IF @I=33 SELECT @I=127 --this jumps over the range that I want to keep
   SELECT @TEST=REPLACE(@TEST, CHAR(@I), ' ') --this replaces the current char with a space
   SELECT @I=@I+1
END

SELECT @TEST


Another way to do this which allows better granular control over which characters you allow is this:


DECLARE @GOOD VARCHAR(256),
        @TEST VARCHAR(100),
@I INT

SELECT @GOOD='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()-_=+/?.>,<;:|~ ' + char(13) + char(10) + char(9)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
@I=0
SELECT @TEST


WHILE @IBEGIN
IF PATINDEX('%' + SUBSTRING(@TEST,@I,1) + '%',@GOOD)=0
BEGIN
SELECT @TEST=REPLACE(@TEST,SUBSTRING(@TEST,@I,1),' ')
END

   SELECT @I=@I+1
END

SELECT @TEST

Did this help you?  If so, please leave a comment!

More adventures in T-SQL

I often see overly complicated where clauses in procedures or views that could have been simplified with a little trick I have been using for years.

Here is an example of a where clause which is hard to read, and therefore hard to debug and modify:

WHERE (SV00700.Service_Call_ID = @Service_Call_ID OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (SV00700.Call_Invoice_Number = @Call_Invoice_Number OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (@DocumentNumber = '' OR
@DocumentNumber = rtrim(SV00700.Call_Invoice_Number)) AND (@BatchNum = '' OR
SV00700.BACHNUMB = @BatchNum)

The intent here was to filter based on a combination of parameters.
The trick I like to use is simple:  Assume all parameters are empty and temporarily replace them with the values.  If the statement still looks complicated, you need to refactor.

In this case:
WHERE (SV00700.Service_Call_ID = '' OR

'' <> '' OR
'' <> '') AND (SV00700.Call_Invoice_Number = '' OR
'' <> '' OR
'' <> '') AND '' = '' OR
'' = rtrim(SV00700.Call_Invoice_Number)) AND '' = '' OR
SV00700.BACHNUMB = '' ) AND remit2.INTERID = rtrim(db_name())

Notice all those instances of '' <> ''.  That is what SQL is actually seeing at run-time if the parameter is an empty string. 

We can make this much easier to read by figuring out how to evaluate each parameter individually, rather than trying to evaluate all possible combinations.

Consider this:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM(@Service_Call_ID) WHEN '' THEN '%' ELSE RTRIM(@Service_Call_ID) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@Call_Invoice_Number) WHEN '' THEN '%' ELSE RTRIM(@Call_Invoice_Number) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@DocumentNumber) WHEN '' THEN '%' ELSE RTRIM(@DocumentNumber) END
  AND SV00700.BACHNUMB LIKE CASE RTRIM(@BatchNum) WHEN '' THEN '%' ELSE RTRIM(@BatchNum) END
It accomplishes the same thing, but is much easier to read.  Further, each parameter can be easily removed without reworking the entire statement.
 
When we apply the trick to it:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.BACHNUMB LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

It is still easy to read. 

I hopes this helps someone else to go forth and simplify.



Did this help you?  If so, please leave a comment!

Wentity tip

I was working with a wentity object (a custom data object).  I bound it to some controls on my form.
Later, when coding the logic to open the form, I passed in values that were set on some of these fields to pull up an initial record.  When testing, however, I found that when I entered and then left one of these fields, the values in the other 2 were cleared.  I could not find any of my code that was the culprit.  Then I began wondering if the wentity object was the culprit.

In deed it was.  Each time I refreshed the window, I newed up the wentity object.  This cleared the fields in the object, but not on the window.  However, whenever I did anything that called validation logic from the wentity object, the values were refreshed (and cleared)!

The fix was to manually set the values for these three variables in the wentity object each time I newed it.


Did this help you?  If so, please leave a comment!

Office 2010

I love these new ribbons in Office.

I created a custom ribbon and moved it to the top of the tab list show it is the default tab that shows.
It has my most commonly used tasks in outlook (some of which normally require significant digging to get to).

I exported it in case anyone else is interested in it.

The true beauty is that the exported file is XML..woot!

So I can post it here.  Just save it to a notepad file and give it the extension .exportedUI
Then right click on any tab in outlook and choose customize.  At the bottom of the window, choose Import/Export, then Import.  Browse to the file and import it.


Here is the xml for it:

<mso:cmd app="olkexplorer" dt="1" />
<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat/>
<mso:tabs>
<mso:tab id="mso_c1.27D5463" label="David's Tab" insertBeforeQ="mso:TabMail">
<mso:group id="mso_c2.27D5463" label=" " autoScale="true">
<mso:control idQ="mso:NewMailMessage" visible="true"/>
<mso:control idQ="mso:NewAppointment" visible="true"/>
<mso:control idQ="mso:RulesAndAlerts" visible="true"/>
<mso:control idQ="mso:OutOfOffice" visible="true"/>
<mso:control idQ="mso:AllModuleNameItems" visible="true"/>
</mso:group>
<mso:group id="mso_c1.288DC28" label=" " autoScale="true">
<mso:control idQ="mso:SendReceiveAll" visible="true"/>
<mso:control idQ="mso:Reply" visible="true"/>
<mso:control idQ="mso:ReplyAll" visible="true"/>
<mso:control idQ="mso:Forward" visible="true"/>
<mso:control idQ="mso:Delete" visible="true"/>
</mso:group>
<mso:group id="mso_c1.287D94B" label=" " autoScale="true">
<mso:control idQ="mso:MarkAsRead" visible="true"/>
<mso:control idQ="mso:MarkAsUnread" visible="true"/>
</mso:group>
<mso:group id="mso_c2.288638F" label=" " autoScale="true">
<mso:control idQ="mso:CheckForUpdates" imageMso="GreenBall" visible="true"/>
<mso:control idQ="mso:ShowInConversations" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>
</mso:ribbon>
</mso:customUI>


Did this help you?  If so, please leave a comment!

More fun with SSRS

One of the nice things about Visual Studio is that you can set a default font for the controls and then as you drag them onto the form, the font is applied.

But what happens when you later want to adjust the default font size for the entire page, for instance?
Changes to the default do not flow down to the existing controls (Hint to MS, this would be a awesome feature to add!)

To handle this, you can open the report and choose View > Code, then do a find/replace on the elements I am targeting.  Don't try to scroll down and find the elements, since they are not necessarily listed in any logical order.

Someone should write a mass re-formatting tool for SRS that uses regex to find certain nodes and alter the structure for the new version.



Did this help you?  If so, please leave a comment!

Windows 7 DVD driver issue

After a windows update, my DVD drive suddenly went missing from explorer.
Device Manager shows a driver problem, but windows said the driver was fine.  Uninstall and reinstall had no effect.  Reboot had no effect.  I reseated the drive several times with no effect.

I downloaded the Optical Disk drive firmware update from IMB's site, but it couldn't even see the drive.

Then I came across this KB
http://support.microsoft.com/gp/cd_dvd_drive_problems

Apparently, the  UpperFilters and LowerFilters registry values can sometimes get screwed up when installing updates.  The program at the link above fixed the problem right away.



Did this help you?  If so, please leave a comment!

The new face of SSRS

The report builder in Visual Studio got a face lift in 2008.  I haven't run into anything significant that I can do in 2008 that I couldn't do in 2005, so I must assume this is yet another attempt to drive coders mad.

The first change I noticed was that the data tab was gone from the report.  Yes, they included a menu item called Report Data waaaaaay down at the bottom of the view menu in an entirely successful attempt to hide it.  Luckily, the GUI designers are powerless to totally foil our productivity, because the guys who actually code this stuff could care less about the interface. Enter the control key!  I soon discovered that CTRL + ALT + D pops that sucker right up.  It is now a dockable window with all the data objects in it and the ability to drag and drop to the report.  You have to create a datasource over here before you can create the datasets (what is the shared datasources in the solution explorer for again?) but once you figure out the redundancies, it is pretty smooth sailing.

You may ask yourself, "Self...why didn't they just add the functionality to the server explorer?"  Well, that would be too logicial.  However, if you answered yourself you might want to take a crack at GUI design :^))



Did this help you?  If so, please leave a comment!

SQL vs. CLR vs. Standalone

I recently had some friends who are just starting out in the world of development ask a common question.  "Can I do a while loop in SQL?"  This, of course, sparked a lengthy conversation about the dangers of cursors, merits of CLR procs, and logic flow in general.  When thinking about it later, I realized that I get this question in one form or another from time to time and it might be helpful to have an example to point them to.

So here it is.  Examples of how to perform the same actions in:
SQL Procs
vs
CLR Procs (C#)
vs
Windows App (C# and VB) 
Eventually I will add LINQ examples for windows apps.

Code samples are color coded for easy identification.
I decided to give examples of how to perform the following actions:
1. Mass Delete of all records in two tables
2. Mass Insert of records from one table to another
3. Mass update of all records in one table
4. Mass update of the records in one table by criteria
5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table


So first we need to create these tables we will play with.  Run the following in SQL Management Studio.

USE [master]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table1]') AND type in (N'U'))
DROP TABLE [dbo].[test_table1]
GO


CREATE TABLE [dbo].[test_table1] (
id int,
description varchar(10),
status varchar(10)
)
GO


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table2]') AND type in (N'U'))
DROP TABLE [dbo].[test_table2]
GO


CREATE TABLE [dbo].[test_table2] (
id int,
description varchar(10),
status varchar(10)
)

OK, so lets get started.  You can run these objects after creation and see that the results are the same.  Before running CLR Procs, you must install the dll.  Details on that and on building windows applications are outside the scope of this post.  I am strictly concentrating on what the code looks like in the various technologies.

1. Mass Delete of all records in two tables

SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassDelete]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassDelete] GO create procedure SP_MassDelete AS BEGIN delete from test_table1 delete from test_table2 END GO

CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassDelete() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for deleting the records SqlCommand cmd = new SqlCommand("delete from test_table1", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the statements cmd.ExecuteNonQuery(); cmd.CommandText = "delete from test_table2"; cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassDelete: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } };

C# function: SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;"); private void btnMassDelete_Click(object sender, EventArgs e) { try { //this is the command for deleting the records SqlCommand cmd = new SqlCommand("delete from test_table1", conn); conn.Open(); //now we run the statements cmd.ExecuteNonQuery(); cmd.CommandText = "delete from test_table2"; cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } }

VB function: Private conn As SqlConnection = New SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;") Private Sub btnMassDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassDelete.Click Try 'this is the command for deleting the records Dim cmd As SqlCommand = New SqlCommand("delete from test_table1", conn) conn.Open() 'now we run the statements cmd.ExecuteNonQuery() cmd.CommandText = "delete from test_table2" cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub

2. Mass Insert of records from one table to another

SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassInsert] GO create procedure SP_MassInsert AS BEGIN insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects END

CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassInsert() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for inserting the records SqlCommand cmd = new SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassInsert: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } };

C# function: private void btnMassInsert_Click(object sender, EventArgs e) { try { //this is the command for inserting the records SqlCommand cmd = new SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } }

VB function: Private Sub btnMassInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassInsert.Click Try 'this is the command for inserting the records Dim cmd As SqlCommand = New SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub

3. Mass Update of all records in one table

SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdate]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassUpdate] GO create procedure SP_MassUpdate AS BEGIN update test_table1 set status='Updated' END GO

CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassUpdate() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Updated'", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassUpdate: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } };

C# function: private void btnMassUpdate_Click(object sender, EventArgs e) { try { //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Updated'", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } }

VB function: Private Sub btnMassUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdate.Click Try 'this is the command for updating the records Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Updated'", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub

4. Mass Update of the records in one table by criteria

SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdatebyCriteria]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassUpdatebyCriteria] GO create procedure SP_MassUpdatebyCriteria AS BEGIN update test_table1 set status='Big' where id>100 END GO

CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassUpdatebyCriteria() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Big' where id>100", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassUpdatebyCriteria: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } };

C# function: private void btnMassUpdatebyCriteria_Click(object sender, EventArgs e) { try { //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Big' where id>100", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } }

VB function: Private Sub btnMassUpdatebyCriteria_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdatebyCriteria.Click Try 'this is the command for updating the records Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Big' where id>100", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub

5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table

SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Loop]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_Loop] GO create procedure SP_Loop AS BEGIN declare @id int, @desc varchar(10), @status varchar(10) declare c_loop cursor for select id,description,status from test_table1 where status='Big' for update of status open c_loop fetch next from c_loop into @id,@desc,@status while @@fetch_status=0 BEGIN insert into test_table2 select (@id/2),@desc,convert(varchar(10),@id) update test_table1 set status='Archived' where current of c_loop fetch next from c_loop into @id,@desc,@status END close c_loop deallocate c_loop END GO

CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_Loop() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for getting the records and inserting and updating SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //we can't use a data reader, since we can only have one command active on the connection at a time //so we will use a data adapter to get the records and store them in a datatable in memory so we can work with them SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); //now we will loop through each record and work on it foreach (DataRow dr in dt.Rows) { //first we will insert a new records elsewhere cmd.CommandText = "insert into test_table2 select (" + dr["id"].ToString() + "/2),'" + dr["description"].ToString() + "',convert(varchar(10),'" + dr["id"].ToString() + "')"; cmd.ExecuteNonQuery(); //then we will update the existing record cmd.CommandText = "update test_table1 set status='Archived' where id=" + dr["id"].ToString(); cmd.ExecuteNonQuery(); } } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_Loop: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } };

C# function: private void btnLoop_Click(object sender, EventArgs e) { SqlDataReader rdr = null; SqlConnection conn2=new SqlConnection(conn.ConnectionString); try { //this is the command for getting the records SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn); //this is the command for inserting and updating SqlCommand cmd2 = new SqlCommand("",conn2 ); conn.Open(); conn2.Open(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { //first we will insert a new records elsewhere cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')"; cmd2.ExecuteNonQuery(); //then we will update the existing record cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString(); cmd2.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show("Error in btnLoop_Click: " + ex.ToString()); } finally { //remember to close the reader and connection regardless of errors if (rdr != null) { rdr.Close(); } conn.Close(); conn2.Close(); } }

VB function: Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click Dim rdr As SqlDataReader = Nothing Dim conn2 As SqlConnection = New SqlConnection(conn.ConnectionString) Try 'this is the command for getting the records Dim cmd As SqlCommand = New SqlCommand("select id,description,status from test_table1 where status='Big'", conn) 'this is the command for inserting and updating Dim cmd2 As SqlCommand = New SqlCommand("", conn2) conn.Open() conn2.Open() rdr = cmd.ExecuteReader() Do While rdr.Read() 'first we will insert a new records elsewhere cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')" cmd2.ExecuteNonQuery() 'then we will update the existing record cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString() cmd2.ExecuteNonQuery() Loop Catch ex As Exception MessageBox.Show("Error in btnLoop_Click: " + ex.ToString()) Finally 'remember to close the reader and connection regardless of errors If Not rdr Is Nothing Then rdr.Close() End If conn.Close() conn2.Close() End Try End Sub

 
One thing you will notice is that the SQL code is always smallest.  But that doesn't mean it is necessarily the best way to do something.

Enjoy!


Did this help you?  If so, please leave a comment!

Fun with sysobjects

Here is a handy script that will give you all of the tabls and views that a particular field resides in:

SELECT

   CASE sysobjects.xtype
      WHEN 'V' THEN 'VIEW'
      WHEN 'U' THEN 'USER TABLE'
      WHEN 'S' THEN 'SYSTEM TABLE'
      ELSE sysobjects.xtype END AS OBJ_TYPE,
   sysobjects.name OBJ_NAME
FROM syscolumns
   LEFT JOIN sysobjects ON sysobjects.id = syscolumns.id
WHERE syscolumns.name = 'yourcolumnname'
ORDER BY 1,2

Happy scripting!


Did this help you?  If so, please leave a comment!

Installing Visual Studio 2010 on an x64 system

When trying to install Visual Studio 2010 RC1 on my Windows 7 x64 system, I kept getting an error that the license key was not valid.

Now, even more confusing than the fact that this install did not require a license key was the glaring problem that it didn't actually ASK for a license key.

After much digging for an answer to this issue, I wrote it off to bad error handling and made the assumption that this version of studio was not compantible with the x64 system.

After a little more downloading and testing, I found that Visual Studio 2010 Premium installed just fine on x64.

I hope this helps others looking to upgrade.


Did this help you?  If so, please leave a comment!

Adventures in Replicating a Dynamics GP 9 install on SQL2008

I had a client yesterday that took a backup of their GP9 system and was trying to create a test system.

They installed SQL 2008 and restored DYNAMICS and a company database to the new server.  For simplicity, I will refer to the old SQL instance as SQL1 and the new sql instance as SQL2.

They already had the GP9 client installed on their local machine (pointing to SQL1), but there was no GP9 client on the server, since it was Win Server 2008 64bit and the GP9 client was not compatible.

They followed the steps in KB:878449

They ran the Capture_logins.sql script on SQL1 and then ran the generated script on SQL2. 
They ran sp_changdbowner 'DYNSA'
They ran dex_req.sql and Grant.sql

When they tried to set up an ODBC connection to SQL2 on their client, they could not connect as sa.

We did the following to correct this:
1. Reset the sa password on SQL2
2. Started the SQL Browser service on SQL2
3. Started the DTS service on SQL2
4. Set the firewall on SQL2 to allow DTS
5. Rebooted SQL2

Now we could connect with the ODBC connection, but we got errors when logging into GP as sa.

We re-ran the scripts on SQL2 and I noticed that Grant.sql was throwing an error that it could not find a view.  The view was a custom object in the database with a name exceeding 64 chars.  Once we shortened the name, Grant.sql ran successfully.

Now we could log into GP as sa.

The point of all this was to migrate the users from SQL1 to SQL2 along with their encrypted passwords.  But we could not log in as any user other than sa.

The answer to this is found in a Note on Step 11 of KB:878449
"If the old server was running Microsoft Dynamics GP and does not have the same name as the new server, the passwords for the users will no longer be valid."

We that sucks with a capital "S".  The whole point here was to be able to migrate the users with their encrypted passwords.  So why?  It seems the answer is that the encryption algorithm includes the server name.  Since there is no way GP is handing out the encryption algorithm to the likes of us,  once again, security trumps common sense, but I digress.

So we are relegated to logging into GP as sa, opening security, pulling up the users one at a time, and changing their password to something simple.  We then check the box to force the user to change the password on next login and save. 

Now when we try to log in as the user, GP prompts us to change the password.  But when we try to we get the following error:
"The password change failed for an unknown reason. Enter a different password or contact your system administrator."

Google pops up lots of hits that address the Group Policies, like this one.
Basically, it looks like most of the time this error pops up because the Minimum password age is set to some amount other than zero.  Most admins have enough headaches getting their users to change their passwords before they expires.  Why in the world would anyone want to restrict them from changing it too often?  But I digress again.

In our case, this is not the issue.  Once again, we will be saved by a post from Dave Musgrave.  The issue is the ODBC connection itself.

When they set up the ODBC connection on then client, they chose SQL Server for the driver.  However, the user password change function will only work using the SQL Native Client driver.
(When in doubt, always try the newest driver first)

So we changed the ODBC to use SNAC and ....Presto!  The user password change now works.

Hope this saves someone else some reading.


Did this help you?  If so, please leave a comment!

Insufficient data error in SSIS package

When multiple developers are working on a group of SSIS packages and one of them tries to run a package created by another user, they could receive an error saying there is insufficient data to connect to the data source.


After a little googling and a lot of trial and errors, I think I have narrowed down the issue and how to fix it.

As far as I can tell, the error has nothing to do with the driver or ODBC, it has to do with how SSIS packages handles sensitive data such as passwords.

In the package properties, there is a parameter called ProtectionLevel. By default this is set to EncryptSensitiveWithUserKey. That means that the login info on the data sources will be encrypted in the package file with a key generated from the user who was logged in when they were created. So later, when I log in and try to run it, the decryption returns the wrong value based on my user key and the connection bombs.

You can change this value to any of the other choices, however, you can't use DontSaveSensitive or ServerStorage if you are working with local package files that are not stored in SQL. Leaving the value alone will also work, you just need to add data sources to the project (especially ODBC) and make sure that all of the tasks and data flows reference those data sources.

Now the bad news. Since you are working with local files, Visual Studio does not expect a different user to access them while local. So it over-protects them to the point that they are almost un-usable.

In order for a different user to run the package, they need to

1. Open each data source
1. Edit the connection string
2. Enter the user and password data (it will be missing)
3. Test the connection
2. Open each task that accesses a connection
1. Choose the connection property
2. Choose new connection and map to the data source defined in the project
3. Parse the query to make sure the connection is good
4. Click OK
3. Open each data flow object
1. Click New and map to the existing data source
2. You will probably need to entirely rebuild each data flow object, so pay attention to the other properties before you change the data source.

The package should now run.

There is one saving grace. Once both users have followed this process, their individual data source will show up in the drop downs above (rather than having to use new).

Update:
However, I just couldn;t let this one go.  On a hunch I started exploring configuration files in SSIS.  Success!

This is by far the best solution to this problem.  It streamlines moving packages from one server to another, and almost totally eliminates the multi-developer issue above.

Basically, if you enable configuration files on a package, the package will override any properties listed in the config file with the values from the config file. 

This link gives a great alkthrough on how to use configuration packages with SSIS:
http://decipherinfosys.wordpress.com/2008/08/15/ssis-creating-package-configurations/

Follow those instructions, then just choose the connection object property for each object that has one, and choose the username and password for the connection manager connections.

Then add the config file to the project and open it.  Edit the password entries and type in the password and save.  Now when a another developers opens the package, they may get a couple of load warnings and errors related to the encryption.  Ignore them and load anyway.  Once you run the package, the errors will go away, since the object properties will get overwritten by the config properties.



I hope this keeps someone else from chasing their tail.




Did this help you?  If so, please leave a comment!

WennSoft Products - Product Indicator Drop Down is empty

If the Product Indicator field is empty, try this:
Open Security for Alternate windows, select all products and windows
Select Purchasing
Check each purchasing window and make sure that the WennSoft Products version is selected.
Apparently, the product indicator field will only be populated if ALL the purchasing alternates for WennSoft are enabled.

I ran into this issue after installing another 3rd party, which installed some of the same alternates as WennSoft and automatically set security to the 3rd party window.


Did this help you?  If so, please leave a comment!

Visual Studio Tools for Dynamics Templates not showing up in IDE

Again, we are back with a work-around to a Windows 7 / Visual Studio issue.

In the original version of Visual Studio Tools for Dynamics GP 10, the installer only includes Visual Studio 2005 templates. It flatly refuses to install these templates on Visual Studio 2008, so you have to uncheck it to install.

Now what is the point of installing the Visual Studio Tools if you must choose NOT to install the main part of the program (and only interface) to continue??????

In order to get them to show up, I went into the control panel, selected "Visual Studio Tools for Dynamics GP 10.0 SDK" and clicked Change. I then chose Add/Remove Components and checked the Visual Studio 2008 Templates. After the install completed, I had the appropriate objects in Visual Studio.

After some poking around, I discovered that SP2 and later of the toolkit includes the templates for Visual Studio 2008. When you run the SP installs, there is a minimal install interface (Just continue and done). So you would expect the templates to be there after the patch, right? Wrong! Since you had to uncheck the templates during the initial install, they remain unchecked after the patch.

To recap, the steps are:
1. Install the Visual Studio Toolkit with the 2005 Templates unchecked
2. Install Visual Studio Toolkit SP2 or greater.
3. Use Add/Remove programs to add the 2008 templates.


There may be a better way to do this, and I can't find any documentation on what Microsoft actually intends for you to do about it, but this seems to work fine.


Happy Friday!


Did this help you?  If so, please leave a comment!

Getting SharePoint to use multi-line text fields for calculated fields

Requirement: Trim, truncate, or otherwise modify the text from one SharePoint list field to make it appear in another.
Solution: Make the new field a calculated field and enter the formula, referencing the original field in [brackets].
Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.

The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.

So how do you trick it?
1. Create Field1, single line text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)
    (this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
    (this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
     a. Delete Field1
     b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
     (they will still appear in the item view, but not in the edit window since they are calculated fields)

There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic.  Now how to we trick those MS Devs into removing that erroneous validation logic?

Enjoy.


Did this help you?  If so, please leave a comment!

Tweak Windows 7

Windows 7 treats minimized apps differently than previous versions.  This can be nice if the program was written for Windows 7, but what if it wasn't?  Chances are, you will need to put the smack down on Windows 7 for these apps.

Basically, Windows 7 allows you to change the way an application behaves, fooling it into thinking it is on an earlier version of Windows.  It just doesn't make it very obvious while the application is running.

So first, close that application.  Then find the executable file for it (you may need to look at the properties of the shortcut for this).  Navigate to the executable in windows explorer, right click on the file, and choose properties.  Click the Compatibility tab and check the "Run this program in compatibility mode" option, then choose the version of windows that made you happy.

That's all there is to it.  The application should now behave as it did in that previous version of Windows.

Notable examples that this fix works great for:
 - Live Messenger does not minimize to the tray in Windows 7.



Did this help you?  If so, please leave a comment!

The blog begins.