Thursday, May 11, 2017

Don't Cast Numerics to Varchars In SQL Joins

Had a performance issue with a stored procedure recently and discovered why the thing was choking, taking minutes to run. It had a most unfortunate join factor among the several joins in a complex query. The join was a varchar field matched to an integer field. For whatever reason long lost in time, the writer decided to cast the integer as a varchar to map it against the crazy varchar field, whose values could be anything from numbers to symbols. That field's data content was out of our control, coming from an outside source and possibly needing to be preserved as is for auditing. The only values in that field however that meant anything to us were the numeric ones. What fixed the ridiculous runtime of this proc was to reverse the casting. I changed the join to instead cast the varchar as an int to match against the real int. To do that I had to make sure the varchar was numeric inside the cast. join dbo.StupidTableWithIdioticVarcharField s on cast(case when isnumeric(s.StupidField) = 1 then s.StupidField else 0 end as int) = NormalTable.NormalIntField The speedup was breathtaking. It would've been more breathtaking if the original data was designed, or at least redesigned differently, but sometimes you just gotta work with what you got.

Naming Temp Tables in Stored Procedures called by Others

Beware the temp table names you use when naming such tables in a stored procedure that in turn gets executed inside other stored procedures. If both the executing and executed procedures have temp tables with the same name, you might be in for a nightmarish episode of tearing your hair roots out. If using the same #TableName in both procedures, they must have the same exact column structure. You later add a column to the executor and don't do the same to the executed, you will end up tempted to execute yourself before you figure out why you keep getting insert errors in the child proc. Bottom line, keep temp table names different in procs called by other procs.

Saturday, September 19, 2015

Very happy with reconditioned Dewalt drill set from CPO


Originally submitted at CPO Dewalt

20V MAX Cordless Lithium-Ion Premium 3-Speed Drill Driver - DCD980M2R. (2) 20V MAX 4.0 Ah Lithium-Ion Batteries. Charger. Side Handle. Carrying Case

Very happy & surprised. Didn't expect new XR batteries
By Jeff from Charlotte on 9/19/2015

5out of 5
Pros: Powerful, Easy To Use, Good Battery Power
Bought kit basically for the 4ah batteries and charger to use with my Dewalt saws. The drill was like a freebie for the price and is a great drill, more powerful that the other one we own and that stabilizer handle came in handy very fast as I drilled into tree roots I'm trying to destroy. I didn't expect to get the new XR batteries with this and that made me very happy! They shipped very fast too. I'm usually loath to order refurbished items, but this one turned out great!

Friday, January 23, 2015

Selecting Recordset from SQL Server with Same Name Columns

This applies to SQL Server 2008 R2 and VB.Net.
Thought I'd share what I discovered inside a VB.Net project converted from old VB6 code. We have a recordset that used to be brought in from SQL Server 2008 R2 via ADODB that had 2 fields from different tables with same name.
The old VB6 project apparently ignored the one from the first table and used the second, as the program was designed to do.
I wasn't aware of this right away. Following the .Net conversion I changed this database call to use System.Data.SqlClient.SQlCommand, replacing the original recordset with a datatable fill.
Suddenly, I wasn't getting the value I expected when this field was read for each row.
After going back and seeing what the stored procedure was set up to do (SIGH!!!) I learned that the insert into my datatable appended a "1" to the name of the second of the same-named fields.
The first SAMEFIELDNAME was left as is, the second became SAMEFIELDNAME1.
Not able for various reasons to alias the column names in the proc, which needed to remain as is for other users, I just recoded the program to read field SAMEFIELDNAME1.

I also posted this on VB Forum at:
http://www.vbforums.com/showthread.php?627485-How-to-distinguish-between-2-fields-with-the-same-name-(after-a-table-join)-in-vb6

Friday, November 28, 2014

What Garmin, or Should I Call Them GRRRRRRRman Doesn't Tell You

We have a Nuvi 40LM Garmin GPS that's supposed to have lifetime map replacement. Well, that ain't worth to much if you can't get their Garmin Express, or should I call it Garmin Exasperating, device syncing software to recognize your device.
I tried plugging the GPS into 3 different PCs, with Windows 8.1, 7 and XP, using 3 different USB cables, including a brand new official Garmin cable out of desperation.
I was always able to get both the device drive and MicroSD card inside of it recognized as drives in Windows Explorer, but the Garmin Express would never open up as it was supposed to, the moment we plugged the device in, nor would it ever find the device when we opened it manually.
We tried everything, including older Garmin Map Updaters and the MyGarmin interface on their site.
It wasn't until I wrote their customer service for help (Being they don't deign to offer after hours phone support).
I was shocked to get an email back a few hours later and DAD GUM if they didn't actually give me workable advice, so since it worked for us, here it be for you.

Personally, I think you can get it to work just by removing that GarminDevice xml file in the Garmin folder. I'd try that first.
I also first backed up all the files I removed from the device folders. It was about 1.7GB.

The following is quote/unquote verbatim from their email to me:


When the device is connected to the computer what do you see on the screen of your device? If the device displays the Garmin logo with a picture of the device connected to the computer tower, you should be able to go into the nuvi drive and remove unnecessary files from the device. I have included these steps below. 
1. Connect your GPS to the computer and allow the computer time to find the device
2. Open the Garmin drive, (PC: under "Computer" or "My Computer" Mac: On the Garmin Drive mounted on the desktop)
3. Inside the Garmin drive you will find several folders, you will want to go into each and remove any files you will not be using.
          Help - Remove any language that you will not be using - You will want to keep American_English file
          Keyboards - Remove any keyboards other then keyboards you will be using as well as the EN_US file
          Text - Remove any text files that you will not be using, please keep any that begin with EN_GB
          Voice - Remove any voice files that you will not be using, please keep any American English Voices
          Garmin - inside of the Garmin Folder, delete the GarminDevice or GarminDevice.XML Folder
4. Once the files have been removed, disconnect the GPS from the computer and allow it to power on.
5. When the device is loaded to the main 'Where To/View Map" screen allow it to wait there for appx. 30 seconds 
6. After 30 seconds, reconnect the unit to the computer and Garmin Express should find the device. 

If your device is not displaying the Garmin logo with the picture of the device connected to the computer tower, please connect the device to a different USB port on the computer. If you are using a desktop, please connect the device to the USB port on the back of the computer. If the device is still not connecting to the computer please check the cable to make sure it is the Garmin branded cable. If you look at the end that connects to the computer, below the USB plug the Garmin logo should be embossed on the casing below. If you do not see the Garmin logo on this cable, please try to use a different USB cable. Although these cables look similar and fit they can often transfer data differently. 
If you are still having trouble with your device connecting to the computer, please follow the steps below to perform a soft reset. 
1. Connect the device to the computer or vehicle power cable
2. Press and hold on the power button for 10-15 seconds


3. Release the power button and the device should power back on.

Friday, June 6, 2014

SSIS Overwriting Excel file destination and Fixing 64 bit Run Issue

I had a problem getting an SSIS package to write correctly to an Excel destination.
The first issue was that the Excel Connection Manager couldn't work in 64 bit mode, which my operating system and the SQL server running the package are.
This required a little fix in both the dtsx package, and the SQL server agent job running it.

The first step of fixing the dtsx package came from:
http://merlecarr.wordpress.com/2011/02/12/the-excel-connection-manager-is-not-supported-in-the-64-bit-version-of-ssis/

1: Open the project properties window (Not the package properties, but its parent project).
2: Go to Debugging beneath Configuration Properties.
3: Set Run64BitRuntime to False.

Then in your Server Agent job's properties, open to edit the step(s) running any such package and:
1: Open Advanced beneath the "Select a page" menu on the left.
2: Choose the "Exectution options" tab.
3: Check "Use 32 bit runtime".

Now that this worked, the new problem was that by default, the Excel destination just gets appended to, not overwritten. If you need to always refresh the destination file data, you have to first run a task to wipe the slate clean before importing the new data.

Ultimately this thread provided most of the answer:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26692b8-8044-4813-b643-0c181a1ea4fe/deleting-records-in-an-excel-sheet-using-ssis

1: Create a blank template of your destination Excel file to preserve your column setup.
2: Place a File System Task ahead of your Data Flow Task that imports the destination data.
3: In that file system task, set Operation to "Copy File".
4: Set your Destination Connection to the destination file, with OverwriteDestination set to True.
4: Set Source Connection to your template file.

That should be all it takes, but wouldn't it be amazing if MicroSoftInTheHead would update their @#$%^&* Jet Engine to provide a simple checkbox for overwriting existing data in the Excel Destination data flow? Yeah, right, like we'll ever see that in our lifetime.

Thursday, May 22, 2014

C# class with Methods to Get Json or Xml Data Into List

Below is a class of columns returned by a web service API, with methods to get that data in either Json or Xml form, and send that data into a List of the class properties.

The controller ActionResult (Site is .Net MVC) using this class either uses:

BusInfo bi = new Models.BusInfo();
            IEnumerable model = bi.getDataX();//gets data via xml from web service
            ViewData["PageTitle"] = "Business Information";
            return View(model);

or

BusInfo bi = new Models.BusInfo();
            IEnumerable model = bi.getDataJ();//gets data in json form from web service
             ViewData["PageTitle"] = "Business Information";
            return View(model);

depending on whether I want to get Json or Xml.

I have 3 Xml retrieval methods. The default one used above places the Xml data in a DataSet, then to the list.
The 2nd, getDataX2, has no middleman. Data goes from Xml to list, via XmlReader.
The 3rd, getDataX3, converts Xml to Json, thence to list.

The class is below:

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Web;
using System.Web.Mvc;
using System.Net;
using System.Runtime.Serialization.Json;//used in getDataJ
using System.Xml;
using System.Xml.Linq;

namespace SiteName.Models
{
    public class BusInfo
    {
        public string title { get; set; }
        public string url { get; set; }
        public string description { get; set; }
        public string keywords { get; set; }
        public string category { get; set; }

        readonly string baseUrij = "http://api.sba.gov/rec_sites/all_sites/keywords.json";//json
        readonly string baseUrix = "http://api.sba.gov/rec_sites/all_sites/keywords.xml";//xml

        public BusInfo()
            : base()
        {

        }

        //this constructor is needed if using getDataX2 because:
        //we need an alternative while looping through the reader, to setting a BusInfo instance for each group of properties
        //in order to Add to List because the list ends up with all members set to the value of the last iteration
        //ie, bi.Add(b) just puts a reference to 'b' in each row when set inside the reader. change b, change every member of bi
        //instead we're setting local variables that are fed directly into bi.Add as a new BusInfo() for each iteration
        public BusInfo(string t, string u, string d, string k, string c)
        {
            title = t;
            url = u;
            description = d;
            keywords = k;
            category = c;
        }
        
        public List getDataJ()//for json source, json to list
        {

            string uri = baseUrij;
            WebRequest wr = WebRequest.Create(uri);
            WebResponse wrs = wr.GetResponse();
            DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(List));
            List bi = (List)jsonSerializer.ReadObject(wrs.GetResponseStream());
            return bi;

        }//end getDataJ

        public List getDataX()//for xml source xml to dataset to list
        {
            //parent node in dataset becomes 'sites_id' in place of 'sites site'
            string uri = baseUrix;
            HttpWebRequest wr = (HttpWebRequest) WebRequest.Create(uri);
            wr.Method = "GET";
            List bi = new List();
            DataSet ds = new DataSet();
            ds.ReadXml(uri);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            BusInfo b = new BusInfo();                            
                            foreach (DataColumn dc in dr.Table.Columns)
                            {
                                switch (dc.ColumnName)
                                {
                                    case "title":
                                        b.title = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "url":
                                        b.url = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "description":
                                        b.description = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "keywords":
                                        b.keywords = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    case "category":
                                        b.category = dr[dc.ColumnName].ToString().Trim();
                                        break;
                                    default:
                                        break;
                                }                               
  
                            }
                            bi.Add(b);
                            
                        }
                    }
                }
            }
            return bi;

        }//end getDataX

        public List getDataX2()//for xml source xml to list direct - no middleman
        {
            List bi = new List();
            string uri = baseUrix;
            string em = "";
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.DtdProcessing = DtdProcessing.Parse;
            int bc = 0;
            bool runBiAdd = false;
                
            try
            {
                using (XmlReader reader = XmlReader.Create(uri, settings))
                {
                    //cannot use an instance of BusInfo inside reader - must use local variables
                    string btitle = "";
                    string burl = "";
                    string bdescription = "";
                    string bkeywords = "";
                    string bcategory = "";
                    while (reader.Read())
                    {                       
            
                        if (reader.NodeType == XmlNodeType.Element)
                        {
                            runBiAdd = false;
                            switch (reader.Name)
                            {
                                case "title":
                                    bc++;
                                    btitle = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "url":
                                    bc++;
                                    burl = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "description":
                                    bc++;
                                    bdescription = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "keywords":
                                    bc++;
                                    bkeywords = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                case "category":
                                    bc++;
                                    bcategory = reader.ReadString().ToString();
                                    runBiAdd = true;
                                    break;
                                default:
                                    break;
                            }
                            if (runBiAdd = true && bc == 5)
                            {
                                bi.Add(new BusInfo(btitle,burl,bdescription,bkeywords,bcategory));//see notes by constructor above
                                bc = 0;
                            }                                
                        }                                
                    }                                
                }                                 
            }
            catch (Exception ex)
            {
                em = "An Error Occured: " + ex.Message;
                bi.Clear();
            } 
            
            return bi;
        }//end getDataX2

        //gets xml into json, then into list
        //have to know structure of xml schema
        public List getDataX3()
        {
            List bi = new List();
            string uri = baseUrix;
            bi.Clear();
            string em = "";
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.IgnoreComments = true;
            settings.DtdProcessing = DtdProcessing.Parse;
            XmlDocument doc = new XmlDocument();
            try
            {
                using (XmlReader reader = XmlReader.Create(uri, settings))
                {
                    while (reader.Read())
                    {
                        doc.Load(reader);
                    }
                }
                
                string json = XMLToJson.XmlToJSON(doc);
                
                JObject jo = JObject.Parse(json);
                foreach (var v in jo["sites"]["site"])
                {
                    BusInfo b = new BusInfo();
                    b.title = v["title"].ToString();
                    b.url = v["url"].ToString();
                    b.description = v["description"].ToString();
                    b.keywords = v["keywords"].ToString();
                    b.category = v["category"].ToString();
                    bi.Add(b);

                }
            }
            catch (Exception ex)
            {
                em = "An Error Occured: " + ex.Message;              
            } 
            return bi;
        }//end getDataX3

     }
  
}