Monday, December 30, 2013

Queries to figure out which columns are in multiple tables

I have two tables, TableA and TableB, and I want to know which columns belong in each table.

Here's two queries that get me that info

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME IN ('TableA', 'TableB')
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1

--To add more tables, add the table name in the IN() clause, and increment the COUNT(*) > #

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableB'

--To add more tables, add another INTERSECT + SELECT


Tuesday, December 24, 2013

Move data quickly using linked server

I have lots of testing data on several virtual servers. Being lazy, I did not want to have to install the sample program (which then installs the sample data) on my local SQL Server. To save time I used a linked server + INSERT INTO tableName SELECT Col1, Col2 FROM [linkedservername].[databasename].[tablename]

Here's how to do it:
1. I had to add a pointer in the hosts file to the virtual machine's IP address.
2. In SSMS > Server Objects > Linked Servers
3. Right-click, New Linked Server
4.
Linked server: <server\instance>
Change server type to SQL Server
Click on Security, choose whatever for local login, then a valid login in Remote User / Remote Password
Click on Server Options, make sure Data Access = true
5. OK
6. Test Connection

Now you can start moving data quickly from the other server to your local instance. For example, this is moving records from the Dynamics GP SOP Payments Line table.

INSERT INTO TWO..SOP10103
SELECT [SOPTYPE]
      ,[SOPNUMBE]
      ,[SEQNUMBR]
      ,[PYMTTYPE]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[CHEKBKID]
      ,[CHEKNMBR]
      ,[CARDNAME]
      ,[RCTNCCRD]
      ,[AUTHCODE]
      ,[AMNTPAID]
      ,[OAMTPAID]
      ,[AMNTREMA]
      ,[OAMNTREM]
      ,[DOCDATE]
      ,[EXPNDATE]
      ,[CURNCYID]
      ,[CURRNIDX]
      ,[TRXSORCE]
      ,[DEPSTATS]
      ,[DELETE1]
      ,[GLPOSTDT]
      ,[CASHINDEX]
      ,[DEPINDEX]
      ,[EFTFLAG]
  FROM [NEWHURA\GP2010].[GPDAT].[dbo].[SOP10103]

Using Generics for maintainability

Generics are a great way to keep code maintainable. While it does add some overhead the maintainability pays off greatly.

Here's the Generics in C# guide from Microsoft: http://msdn.microsoft.com/en-us/library/512aeb7t.aspx

Here's a real example. In this specific case I am working on a Dynamics GP eConnect integration. I have to parse a whole bunch of lines into nasty looking eConnect objects like the sweet looking one below in the example. _parseSOPPaymentLine() maps SqlDataReader results to the eConnect object fields.


        private T[] ParseLines<T>(SqlDataReader reader)
        {
           
            List<T> lines = new List<T>();
            Type typeOfT = typeof(T);
            object o = null;
            while (reader.Read())
            {
                if(typeOfT ==                     typeof(taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord))
                    o = _parseSOPPaymentLine(reader);
                /* check for other types */


                if(o != null)
                    lines.Add((T)o);
            }
            if (lines.Count == 0)
                return null;
            else
                return lines.ToArray();
        }

To call this
ParseLines<taCreateSopPaymentInsertRecord_ItemsTaCreateSopPaymentInsertRecord>(reader);


There are many benefits to using generics. In the example above my primary goal is to maximize maintainability, since i'm dealing with alot of types. Maintainability is a really important part of coding, it's not all about speed etc...

The alternative to using a generic function in this case would be to something like:
private object[] ParseLines(SqlDataReader reader, Type type)
{
//Basically replace "T" from the function above with "object", then do explicit casting
//Since we end up with a switch on the Type anyways, the first way makes the code much more clean, and
//therefore easier to maintain and extend
}

Convert data types between SQL, C#, and an XML doc

Convert data types between SQL, C#, and an XML doc, and produce a param list for a stored proc, and insert values for an INSERT INTO statement. I'm sure there's tools out there that will do this for me, but this query + an excel spreadsheet will do just fine, especially since I have no time to find YET ANOTHER TOOL.

SELECT        COLUMN_NAME,
'@'+COLUMN_NAME+ ' ' + DATA_TYPE +
(CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(NVARCHAR, CHARACTER_MAXIMUM_LENGTH) + ')'
ELSE
''
END)+
',' as [StoredProc Param],
'@'+COLUMN_NAME+',' as [Values List],
DATA_TYPE,
CASE DATA_TYPE
WHEN 'smallint' THEN 'short'
WHEN 'char' THEN 'string'
WHEN 'tinyint' THEN 'byte'
WHEN 'datetime' THEN 'string'
WHEN 'int' THEN 'int'
WHEN 'numeric' THEN 'decimal'
END as [XML Data Type],
CASE DATA_TYPE
WHEN 'smallint' THEN 'Convert.ToInt16('
WHEN 'char' THEN 'Convert.ToString('
WHEN 'tinyint' THEN 'Convert.ToSByte('
WHEN 'datetime' THEN 'Convert.ToString('
WHEN 'int' THEN 'Convert.ToInt32('
WHEN 'numeric' THEN 'Convert.ToDecimal('
END as [C# Data Type]
FROM            INFORMATION_SCHEMA.COLUMNS
WHERE        (TABLE_NAME = 'CHANGE THIS TO YOUR TABLE NAME')

Four steps to extend eConnect

I followed this guide: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/12/02/creating-a-custom-serialization-assembly-for-econnect-for-microsoft-dynamics-gp-2010.aspx
step 0 - set VS Command Prompt to your project folder, to make step one easier.
Right-click VS Command Prompt, in Start-In change it to your project folder, click OK. 
Step 1 – Extend the schema
  • Open C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\XML Sample Documents\Incoming XSD Individual Schemas\SOPTransaction.xsd
  •    In the SOPTransactionType node add <xs:element minOccurs="0" maxOccurs="1" name="eConnectCustomProcedure" nillable="true" type="eConnectCustomProcedure" />   
  • Note: If you forget the above step, then xsd will not add the class for new type, even if you put the new type complexType info. This drove me mad for a few hours when i forgot this.   
  •    At the bottom of the file, right above </xs:schema> put the following:
 <xs:complexType name="eConnectCustomProcedure">
    <xs:sequence>
      <xs:element minOccurs="1" maxOccurs="1" name="CUSTNMBR" type="xs:string" />
    </xs:sequence>
  </xs:complexType>
  •           For array of objects use

  <xs:complexType name="eConnectCustomProcedure_Items">
    <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="unbounded" name="Item" nillable="true">
        <xs:complexType>
          <xs:sequence>
             
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:sequence>
  </xs:complexType

Step 2 – Use xsd.exe to create the class
1.       Open Visual Studio Command Prompt
2.       Execute xsd “Path To SOPTransaction.xsd (or whatever file you modified” /c
Step 3 – Use the class
  •         Add the class generated by xsd to the project
  •   .  Create the custom node like this

               salesOrder.eConnectCustomProcedure = new eConnectCustomProcedure();
               salesOrder.eConnectCustomProcedure.CUSTNMBR = "CUSTOMER1";

Step 4 – Create the stored proc
create procedure dbo.eConnectCustomProcedure
@I_vCUSTNMBR char(15), /* Customer Number - only required field */
@O_iErrorState int output, /* Return value: 0 = No Errors, Any Errors > 0 */
@oErrString varchar(255) output /* Return Error Code List */
as
declare
    @CUSTBLNC int,
    @O_oErrorState int,
    @iError int,
    @iStatus smallint,@iAddCodeErrState int
/*********************** Initialize locals ******************************/
select
    @O_iErrorState = 0,
    @oErrString = '',
    @iStatus = 0,
    @iAddCodeErrState = 0

INSERT INTO TestEConnect (CustomerName) VALUES (@I_vCUSTNMBR)

return (@O_iErrorState)
go
grant execute on dbo.eConnectCustomProcedure to DYNGRP

go

Processing multiple result sets with SqlDataReader

According to
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult(v=vs.110).aspx
use NextResult() to advance the reader to the next result set


pseudo-code:
//I have one stored proc called sp_GetAllResults that calls three other stored procs
SqlCommand cmd = new SqlCommand("sp_GetAllResults");
cmd.CommandType = CommandType.StoredProcedure;
//i am actually using one stored proc, which calls several stored procs, so it's more like

SqlDataReader reader = cmd.ExecuteReader();
//Process result set 1
while (reader.Read())
{
//do stuff
}

//Move to next result set
reader.NextResult();
//Process result set 2
while (reader.Read())
{
//do stuff
}

//Move to the third result set
reader.NextResult();
//Process result set 3
while (reader.Read())
{
//do stuff
}

Friday, December 20, 2013

Dynamics GP SOP Type actual values



Visual Position                   Integer Value
----------------------------------
Quote                                                   1
Order                                                    2
Fulfillment Order                                 6
Invoice                                                 3
Return                                                  4
Back Order                                          5

Wednesday, December 18, 2013

TransSubscription.SubscriberSecurity will not update!

Problem
I noticed that I forgot to set the SubscriberSecurity to use SQL Authentication. This popped up as a "Could not login to the subscriber" type of error in the Replication Monitor. So i thought, ok, this should be simple, just update the SubscriberSecurity.UseWindowsAuthentication = false, then specify the username and password. According to this article that should work: http://technet.microsoft.com/en-us/library/ms151761.aspx#RMOProcedure

Solution
The documentation is missing the fact that you have to call SubscriberSecurity.Update() after setting the values. In fact, the documentation on "Update()" makes little to no sense. It says "Updates the schedule fields for the connection object". What are the schedule fields? I don't know, but calling Update() finally pushed the changes through.

Friday, December 13, 2013

Current exercise routine

I am going to a new gym and they have better and different equipment than my old gym.
Here's my new routine...

1. Boxing intervals
I have handwraps and punch a punching bag for about 3 minutes at a time. Then I take a minute or so break and throw a basketball against a wall, or shoot it, or whatever, just keep moving.

2. Stronglifts (http://stronglifts.com/)
 I do squats and power clean push each time, and then rotate between day A and day B

A days:
Deadlift, Overhead press

B days:
Bench, Rows

3. Interval cardio
I typically use the elliptical and do 2 minutes intense, 1 minute slower, for 10 minutes at a time. I usually do a few sets of these between lifting.

Thursday, December 5, 2013

Install / uninstall windows service during build for quick debugging

1. Right-click the Windows Service project, choose Properties
2. Click Build Events
3. In pre-build event put:
net stop "PutServiceNameHere"
$(FrameworkDir)\installutil.exe /u $(TargetPath)
Exit /b 0

4. In post-build event put:
$(FrameworkDir)\installutil.exe  $(TargetPath)
net start "PutServiceNameHere"

Now when you build the project it will first stop and uninstall the service, build the exe, then install and start the service. If an error occurs during the build in the post or pre build events look in the Event Viewer > Application log for info on the error.

This works in .NET 3.5.
There was an error in this gadget