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.

Monday, November 11, 2013

"Sharing failed due to network issues" - "Not Implemented" in the lync client log

Problem
"Sharing failed due to network issues"
when a user tries to share with anyone.

Diagnostics
This appears to only happen when the user logs into lync without being connected to the VPN. If they connect to the VPN then everything is fine.

I had the user run a client side log and send it to me.

I saw the following entry:
SIP/2.0 501 Not Implemented

Ms-diagnostics: 9008;source="edgeserver.domain.com";reason="Version not supported";component="MediaRelayEdge"
Ms-diagnostics-public: 9008;source="edgeserver.domain.com";reason="Version not supported";component="MediaRelayEdge"


<?xml version="1.0"?> <response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" requestID="522043072" version="2.0" serverVersion="2.0" to="sip:lyncedge.domain.com@domain.com;gruu;opaque=srvr:MRAS:if1-iWfHUV-Iz_Qv9m5atgAA" from="sip:username@domain.com" reasonPhrase="Version Mismatch" xmlns="http://schemas.microsoft.com/2006/09/sip/mrasp" />

When i look at the call before this error i see

<request xmlns="http://schemas.microsoft.com/2006/09/sip/mrasp" requestID="1138340784" version="3.0" 

 It's expecting version 2.0 but the client is using version 3.0 in the call.

Solution
The user is using the Lync 2013 client, whereas the server is Lync Server 2010. This seems to indicate the 2013 client does not work in this situation with 2010. The resolution, unless Microsoft releases a patch, is to uninstall the 2013 client and have them use 2010 client instead.

Sunday, November 10, 2013

Lost connection to the Web Conferencing Edge Server

Problem
Error messages repeatedly logged, "Lost connection to the Web Conferencing Edge Server" from LS Data MCU.

Solution
I followed the advice here: http://msunified.net/2011/02/09/lync-server-front-end-lost-connection-to-the-web-conferencing-edge-server/ and disabled IPv6, but that didn't solve the problem.

It turns out this had to do with a previous OCS 2007 installation which left orphaned records in the Active Directory. This OCS 2007 installation was abandoned and never used in production, and therefore when Lync 2010 was installed it was not a migration. This is because the person installing Lync 2010 (me) had no idea that OCS 2007 was attempted to be installed on our system.

So i followed the advice here: http://blog.ucmadeeasy.com/2010/11/09/lync-server-2010-active-directory-references-and-how-to-remove-them/

and removed all references to the objects created during the OCS 2007 installation. This has gotten rid of the rampant error messages, and most likely has solved some other painful issues we've had that, which have been quite baffling.

Lync Application Host failed to start -- Audio Test Service

Problem
The audio test service won't start.

Solution
This is because domain is wrong. So my Audio Test Service is trying to use address@mydomain, notice the .com is missing? Yes, this is a bug in lync, because i definitely updated the SIP address to be mydomain.com, but it didn't update this setting. So here's how to update the domain address for the Audio Test Service

1. If you don't have ADSIEdit then you'll need to install it on a server in your domain.  This article explains how to get it =  Adsiedit.

2. On your Lync Server open Lync Server Management Shell and execute Get-CsAudioTestServiceApplication

3. Use the information in the "Identity" to navigate to the proper spot in ADSIEdit.
For example, mine was located at MyCompany.local > System > Microsoft > RTC Service > Application Contacts > {GUID}

4. Fix the attributes "msRTCSIP-PrimaryUserAddress" and "proxyAddresses".  To save some sanity copy and paste the value from the msRTCSIP_PrimaryUserAddress after fixing it, and pasting it into proxyAddresses, add it, then remove the old one, this is so you don't have to manually type it in.

5. Start the Audio Test Service

Note: I noticed the same error in these Call Park Service, RGS Presence Watcher, Announcement Service too.


Reference
1. http://lyncdup.com/2011/07/lync-troubleshooting-audio-test-service-csaudiotestserviceapplication-will-not-start/

2. http://technet.microsoft.com/en-us/library/cc773354%28v=ws.10%29.aspx#BKMK_InstallingADSIEdit

Thursday, October 24, 2013

Exchange backup failing due to ESE 474, how to move mailboxes

Problem
I am using Windows Server Backup to backup my Exchange Server database and noticed that the backup job has an error saying "the application will not be available for recovery. The consistency check failed for component Microsoft Exchange Server...", and I also noticed that transaction logs were not getting flushed.

Troubleshooting and Solution
Looking in the Event Viewer I found event 474, ESE, which states that a page level verification failed due to a page checksum mismatch. Googling lead me to this: http://technet.microsoft.com/en-us/library/bb397387%28EXCHG.80%29.aspx

First, I ran eseutil /K against the mailbox database. This showed me that there were indeed consistency check failures.

Following the advice in the article I chose to try moving mailboxes to a new database, being sure to choose the option to not move mailboxes if they had errors. If they had errors I would then run eseutil /P against the mailbox database that had corrupt mailboxes in it.

Step 1 - Create a new mailbox database. This can be done in the Exchange Management Console is straightforward.

Step 2 Prep the server to speed up the mailbox migration process
a. using the advice here: http://edmckinzie.wordpress.com/2011/12/12/how-to-speed-up-a-large-exchange-2010-migration/
b. Turn on circular logging

 Step 3 - Move the mailboxes in bulk, using the advice here: http://www.msexchange.org/articles-tutorials/exchange-server-2010/management-administration/moving-mailboxes-exchange-2010-part1.html

Step 4  -   Check to see if any mailboxes failed to move.

a. If they did, run eseutil /K on the old database, then eseutil /P.
b. After repairing try to move the mailboxes that failed

Step 5 - Dismount the new DB and run eseutil /K on it
If there's problems it probably means there's a hardware failure. Please read more on here about the ESE 474 problem: http://technet.microsoft.com/en-us/library/bb397387%28EXCHG.80%29.aspx They say sometimes it's a hardware failure and sometimes it's a logical file problem.

Step 6 (if no problems in step 5) - Disable circular logging. Then remount the DB.

Step 7 - Run a full backup on the new DB and check for problems.

Tuesday, October 22, 2013

C# code for removing a third party product from Dynamics GP

Problem
We have a Dynamics GP third party product that  has been decommissioned and needs to be removed. The manual process of removing a third party product is explained here:   https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;872087

However, I didn't want to place the burden on the customer/partner for removing it so I created a C# method that does this automatically.

Note: This code is called in a Console Application that's run by my installers. It's called in try catch block and errors are logged. This means errors are suppressed, but logged.


Code
 private static void RemoveProduct()
        {
            /*
             * Manual method for removing a third party product located here: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;872087
             *
             * Check if the dic exists in the directory.
             *  If it does then
             *  1. Remove references to it from the Dynamics.set file
             *  2. Remove the file
             * 
             */
            string dic = "Product9999.DIC";
            string fFile = "F9999.DIC";
            string rFile = "R9999.DIC";
            string prodName = "My Product Name";
            string prodNum = "9999";

            string setPath = Path.Combine(Environment.CurrentDirectory, "Dynamics.set");
            string dicPath = Path.Combine(Environment.CurrentDirectory, dic);


            //Only execute if the dic is present
            if (File.Exists(dicPath))
            {

                //Only execute if the Dynamics.set file exists
                if (File.Exists(setPath))
                {
                    //use a string instead of the StringBuilder below, because otherwise we'd be removing from a StringBuilder and that's inefficient
                    string setFileString = "";
                    using (StreamReader reader = new StreamReader(setPath))
                    {
                        setFileString = reader.ReadToEnd();
                    }
                    if (!string.IsNullOrEmpty(setFileString))
                    {
                        StringBuilder sb = new StringBuilder(setFileString.Length);
                        string[] lines = setFileString.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);

                        //Decrement the first line by 1
                        sb = sb.AppendLine((int.Parse(lines[0]) - 1).ToString());

                        int removedCounter = 0;
                        for (int i = 1; i < lines.Length; i++)
                        {
                            //Once the 5 lines that specify a product have been removed we know we can stop looking,
                            //so append the rest of the lines

                            if (removedCounter < 5)
                            {
                                //If the line contains what we want removed, don't append to the StringBuilder
                                if (lines[i] == prodNum || lines[i] == prodName || lines[i].Contains(dic) || lines[i].Contains(fFile) || lines[i].Contains(rFile))
                                {
                                    removedCounter++;
                                    continue;
                                }
                            }
                            sb.AppendLine(lines[i]);
                        }


                        //Now write the StringBuilder to the Dynamics.set file
                        using (StreamWriter writer = new StreamWriter(setPath, false))
                        {
                            writer.Write(sb.ToString());
                        }


                    }
                }
                //Now delete the dic file
                File.Delete(dicPath);
            }
        }

Monday, October 7, 2013

Lync sharing doesn't work internally for some users

Problem
*Most* users have no problem with sharing their screen in lync, but for others it fails repeatedly.

Solution
This is most likely a problem the user's router settings. We have several remote workers, and so i am not in control of their routers (since it's in their house, not an office). The best thing to do is get the model of the router and search "RouterName lync traffic", for example,
in my particular case the user has a dlink router, and i found this:
http://superuser.com/questions/321424/why-does-my-d-link-router-keeps-dropping-my-outlook-and-lync-connection

I had them change that setting in their router and voila, it worked!

Monday, September 30, 2013

One-way audio dropping on Lync Server

Problem
I have Lync Server 2010 with a SIP Trunk provided by Intelepeer and a SonicWall firewall. When calls are made between internal users to the outside (i.e. Lync calls to PSTN), sometimes the audio drops going to the PSTN user. It is a one-way audio problem, because the Lync user can still hear the other person. We have struggled with this problem for the past year (mostly because i haven't had any time to focus on it).


Solution
1. Add a NIC to the server
2. In the Topology Builder I'm going to differentiate between the Primary IP and the PSTN IP

In my 1 NIC environment i have the IP as 15.1.1.82, so this is what i should put as the Primary IP. In my 2 NIC environment my new IP, which is on a different subnet, 16.1.1.1 instead of 15.1.1.1, is going to 16.1.1.16 and assigned as the PSTN IP
  • Edit the Front-End Server properties
  • Select Limit service usage to selected IP addresses. 
  • For the Primary IP i'm putting in 15.1.1.82
  • For the PSTN IP i'm putting in 16.1.1.16
  • Publish the change
3.  On the firewall, in my case it's a SonicWall PRO 2040 Standard, first I'm going to map my PSTN IP to my public IP, then I'm going to add access rules allowing traffic to/from my PSTN SIP Trunk to my Mediation Server's PSTN IP (internal and public). This is basically making the firewall wide open to SIP/RTP traffic from my SIP Trunk provider, essentially putting the Meditation Server outside of the firewall.
  • In Network > One-to-One NAT replace the primary IP (i.e. 15.1.1.82) with the PSTN IP (i.e. 16.1.1.16). 
  • In Firewall > Access Rules add the following rule
    • Allow
    • Service = Any
    • Source = put in the range of your PSTN IP addresses given to you by your provider. For me this is from 66.x.x.59 - 66.x.x.62
    • Destination = put in the public IP to your Mediation Server
    • Repeat this for the internal IP of the Mediation Server
4. Test calls to make sure it works


UPDATE
The problem resurfaced after several weeks of not happening. I'm noticing alot of errors in the event log on the frontend server saying that it had disconnected from the conferencing service on the edge server.
I followed the advice in this article: http://msunified.net/2011/02/09/lync-server-front-end-lost-connection-to-the-web-conferencing-edge-server/ and disabled IPv6 on the Edge (it was already disabled on the front end). Awaiting results. 

Windows Firewall verification
Another issue I was seeing is that we missed alot of ports that need to be opened in Windows Firewall. I was seeing events in the Lync Logging tool such as "The server actively refused the connection." In my experience with our own networking software that either means
1) The port hasn't be opened in Windows Firewall
I went through this list: http://technet.microsoft.com/en-us/library/gg398833%28v=ocs.14%29.aspx and double checked everything. I was missing several ports in the "Front End Servers that also run a Collocated Mediation Server" section. Pretty much every row in that table where i didn't have the port opened was in a section that I was having intermittent problems with, so that's nice to know what was causing the problem.

-or-

2) The port isn't being listened to.
You can use tools like Port Query to figure out if a port is being listened to. This is most likely a configuration problem or as simple as the service not running.

Update 2
We still experienced this problem after all of the steps above. So i ran Wireshark on all endpoints. I saw that packets were leaving the server even when audio was dropped on the other end of the call. Then I looked in SonicWall. First of all, we had the factory firmware on there, 2.2.x. That SonicWall PRO 2040 is EOL so that means they no longer release firmware updates for it. I did find 3.2.x though. After upgrading to that I was able to use the packet trace diagnostic tool. They must have upgraded it between the versions because i finally was able to see something wrong: packets were not being sent from the public IP to Intelepeer. I could see they were being sent from the private IP. But there was no indication of why it was dropping. "Firwall consuming packets without logging it" sort of situation here. So luckily we had a TZ210 being used at an office. That is a 5th gen SonicWall, compared to the Sonicwall PRO 2040 it's much much better, and the best part is the latest firmware is from Oct 2013 (last month as of this writing).

So i went into the office and replaced the TZ210 with a dlink. It's kinda funny how the office had a better firewall than our server network. After hours i then replaced the PRO 2040 with the TZ210. I haven't ran into this problem yet, but if it does happen at least the SonicOS version (5.9.x) will show what's happening, so I can take action.

Note: It took me awhile to learn how to do One-to-One NAT in 5.9, because i was used to the ridiculously simplified version in 2.2. The trick is to configure the inbound rule first, then check the "create reflexive rule". The documentation has you create an outbound rule first, which doesn't create the reflexive rule for you. I also had a difficult time figuring out how to add a second subnet. But there's plenty of documentation for the newest versions of SonicOS, so it turned out to be easy. 
  
Tools
1. WireShark - http://www.wireshark.org/download.html : I used this to discover that I was getting traffic from an IP from Intelepeer (SIP Trunk Provider) that i didn't have explicitly unblocked in my SonicWall (external firewall). The awesome part is you can filter the network traffic for VoIP traffic, so it's pretty easy to track down relevant traffic.

2. Lync Logging Tools - http://www.microsoft.com/en-us/download/details.aspx?id=21165 : I used this to discover that some of the ports weren't opened in the Windows Firewall on the frontend server. I have Deployment, InboundRouting, ManagementCore, MCUInfra, MediaStack_RTP, MediationServer, OutboundRouting, S4, ServerConfiguration, SIPStack, and TranslationApplication checked with all flags.
Leave comments!
Since this problem took me such a long time to solve, and was painful, I'd like to know if it helps anyone else, or if you need help with it. Please feel free to ask questions if you're having the same problem. I've had at least 3 support cases opened with Microsoft, 2 opened with Intelepeer, and multiple questions on support forums, and of course lots and lots of Googling. 


References
1. http://social.technet.microsoft.com/Forums/lync/en-US/dee64e0e-c343-4e60-83d7-9a899bc84b2f/need-recommendations-for-firewall-that-will-work-100-with-lync-voice?forum=ocsvoice

2. http://technet.microsoft.com/library(OCS.14)/ms.lync.tbdep.FrontEndGeneralSettingsExpander.aspx 

3. Lync Firewall Rules Viewer: http://blogs.technet.com/b/nexthop/archive/2012/07/03/lync-firewall-rules-viewer.aspx

4. Frontend Server lost connection to the web conferencing edge http://msunified.net/2011/02/09/lync-server-front-end-lost-connection-to-the-web-conferencing-edge-server/

Monday, September 23, 2013

Error generating short file name for file error code -6015

Problem
In InstallShield 2010 I specified a location using a forward slash, i.e. [INSTALLDIR]Reports/Product instead of using a backslash. This results in the error "Error generating short file name for file..."
I updated the reference to use a backslash, i.e. [INSTALLDIR]Reports\Product, but it's still getting this error. It seems to be caching the string with the forward string somewhere.

Solution
1. Additional Tools > Direct Editor
2. Directory
3. Locate the string containing the forward slash, right-click the row and Drop the Row

I found this by doing a Ctrl-F and entering Reports/Product. This found it in the Directory table, so i navigated there and dropped the row.

Set default installation directory for Dynamics GP in installscript

Installscript function
prototype setINSTALLDIR();
function setINSTALLDIR()   
string result;
string key1, key2;
string value;
number size;
number type;
string version;
begin
    //Change version to whatever version the installer is for  
    version = "v11.0";        

//this finds it in a 32-bit environment
    key1 = "SOFTWARE\\Microsoft\\Business Solutions\\Great Plains\\" + version + "\\1033\\DEFAULT\\SETUP";

//this finds it in a 64-bit environment
    key2 = "SOFTWARE\\Wow6432Node\\Microsoft\\Business Solutions\\Great Plains\\" + version + "\\1033\\DEFAULT\\SETUP";
    value = "AppPath";  
   
    RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);
    if(RegDBGetKeyValueEx(key1, value, type,result,size) = 0)then
        INSTALLDIR = result;
    elseif(RegDBGetKeyValueEx(key2, value, type,result,size) = 0)then
        INSTALLDIR = result;
    endif;
end;

Call it from OnFirstUIBefore, for example:
function OnFirstUIBefore( )

    NUMBER nResult, nSetupType;
    STRING szTitle, szMsg, svName, svCompany, svSerial;
    STRING szDir, strNum;
    BOOL bLicenseAccepted;

begin

    nSetupType = TYPICAL;  
    setINSTALLDIR();
    szDir = INSTALLDIR;
    svName    = "";
    svCompany = "";
...

Sunday, September 22, 2013

Optimizing SRS report performance

Gather information
1. In SSMS navigate to ReportServer database
2. Execute the following query

DECLARE @path varchar(50) = '/<Folder>/<ReportName>'
SELECT CAST(convert(varchar(40),timestart,101) AS datetime) dt,
       avg(DATEDIFF(SECOND,timestart,timeend)) run_time_seconds,
       AVG( CASE TimeDataRetrieval WHEN -1 THEN NULL ELSE TimeDataRetrieval END ) / 1000 data_retrieval_seconds,
       AVG( CASE TimeProcessing WHEN -1 THEN NULL ELSE TimeProcessing END ) / 1000 processing_seconds,
       AVG( CASE TimeRendering WHEN -1 THEN NULL ELSE TimeRendering END ) / 1000 rendering_seconds,
       COUNT(*) num_runs
FROM ExecutionLog2
WHERE timestart > getdate()-30
  AND Reportpath=@path
GROUP BY CAST(convert(varchar(40),timestart,101) AS datetime)
ORDER BY dt DESC

Optimize based on the results
In my case my data retrieval is taking much alot of the time. Based on the tips from "My data takes too long to retreive in the link below" i did the following:
1. Take sorting out of the report and move it to the query

Resources
My data takes too long to retrieve
My report takes too long to process
My report takes too long to render
Design Tips for Optimizing Report Processing

References
1. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ef426ee-2111-49db-8d88-ba2b809ef2e1/ssrs-report-performance-optimization

Tuesday, August 20, 2013

Friday, August 16, 2013

"Conversion failed when converting date and/or time from character string." when ORDER BY CASE

Problem
My query looks like this
ORDER BY
CASE @OrderBy
       WHEN 'A' THEN A
       WHEN 'B' THEN B
      etc...
END
DESC

I was getting the "Conversion failed when converting date and/or time from character string."when @OrderBy was set to certain values.

Solution
You need to use a separate case for each possible OrderBy. So I had to turn the query above into

ORDER BY 
    CASE WHEN @OrderBy = 'A' THEN A END DESC,
    CASE WHEN @OrderBy = 'B' THEN B END DESC


Reference
1. http://stackoverflow.com/questions/10002401/conversion-failed-when-converting-date-and-or-time-from-character-string 

There was an error in this gadget