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

     }
  
}

Using XmlReader and XmlReaderSettings in C3 Asp.Net MVC

I already had methods for retrieving both Json and Xml formatted data from an API web service.
What I still couldn't get to work was getting the data in Xml and converting it cleanly to Json.
Problem thus far are the child nodes and the fact that the conversion methods kept leaving escape slashes in the Json string, despite running it through Regex.Unescape.

And in truth, there may be no logical reason why I should need to do such a conversion, since my other 2 methods cleanly placed data from both formats into my webgrid's class.

My Xml method did it by first reading all the data into a dataset, from whence the data went into my class List feeding the webgrid.

I wanted another way of dealing with the Xml, for better, worse, or just for variety and learning sake.
Thanks to this great blog article by Saiful Alam, at http://asp-net-example.blogspot.com/2009/05/xmlreadersettings-how-to-use.html, I got my 2nd Xml to List method.


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


One issue I didn't consider while making this method, was the bugaboo when I attempted to keep reusing an instance of my BusInfo class, to add to my List during the reader's iterations.
As those of you who are already laughing at me already knew (however many "those-of-you"s there are), within the scope of this code, setting properties of a BusInfo instance for each iteration of the reader, in order to then add to the list, eg: BusInfoList.Add(BusInfoInstance), ends up changing all previous items in the list to the latest, and not so greatest, property values added to the newest item.
To fix this, I gave the class a constructor override with the 5 class properties as parameters, hence giving me the list.Add line "bi.Add(new BusInfo(btitle,burl,bdescription,bkeywords,bcategory));"

The constructors I added to the class:

public BusInfo()
            : base()
        {
        }
       
        public BusInfo(string t, string u, string d, string k, string c)
        {
            title = t;
            url = u;
            description = d;
            keywords = k;
            category = c;
        }


Friday, December 6, 2013

RowCount is now a No-Account - Alternative for Setting Dynamic Top Rows

Had an issue come up in a couple of old stored procedures we needed to update, to replace the deprecated 'Set ROWCOUNT' with 'Top' for passing the number of rows we wanted selected.

ROWCOUNT was great for this for one reason, that being it provided an easy way to pass in a request for either some rows, or ALL rows, without knowing how many rows were in the table(s) beforehand.
All you needed to do was pass in 0 to get all rows. Any other amount got up to that many rows.

Well, I know ROWCOUNT has been deprecated and don't want to wait to find we're moving the databases to a newer server where it won't work, so I figure I'd just convert all such instances to use 'Top' instead.
In ignorance I thought I'd be able to set Top to 100% whenever the parameter passes in 0. NO GO, because Top followed by parentheses requires an integer, and Top followed by the @Setting variable without parentheses won't work.
Seemed my only solution was one of the following:
1: Use the variable setting and convert the query to dynamic sql:

eg:

declare @p int = 4 --pretend this is the incoming parameter
declare @v nvarchar(10)
declare @sql nvarchar(max)

if @p > 0
        set @v = ' Top '+ cast(@p as nvarchar) --to limit rows

else        set @v = ''--to draw all rows
 set @sql = N'select '+ @v + ' * from table'
exec sp_executesql @sql

With variable holding either the string ' Top [a number] ' or a blank string to allow all rows to be selected, depending beforehand on whatever the parameter held.

That wasn't a problem to me for a simple query, but we have complex queries with loads of joins, specific select fields, subqueries, function calls, where conditions involving both local variables and table columns, etc. To have to place all of that in an nvarchar @sql string stinks.

2: Second thought was just having 2 versions of the query, one with the Top included to take the integer value of the parameter when it was above 0, and the second version of the query with out 'Top'.
Again, easy enough if the query is simple, but unworkable when it is complex, with a ridiculous amount of repeated code, and 2 sets of everything to have to maintain when changes are made.

3: Perhaps the best solution in the end for simplicity, but it felt dirty to me.
If parameter is 0, reset it arbitrarily to the maximum value its datatype would hold, eg: 9223372036854775808 for a bigint, 2147483648  for an int, etc.
Then regardless of parameter value, you'll use 'Top (parameter value) ' in the query.
You'll know if your table might have more rows than this arbitrary ceiling, and not too many needing this solution would, but this solution just smelled to me. I don't like having to pull numbers out of a hat like that and employ them in a jillion procs. Just doesn't feel clean to me.

4: This method uses a CTE:

declare @rc int --rowcount value
set @rc = 3 --if set to 0 all rows get returned, otherwise the number of @rc rows
  
;with #CTE as (select * from table where whatever ='whatever')
 select * from #CTE where @rc = 0 --means all rows
 union select top (@rc) * from #CTE where @rc > 0 --means limited rows

This way, the real query is only typed in once, as the (select) for the CTE.
Using the union gets around the CTE limitation of only working with 1 select statement.
Only one part of the union will matter, so it's like being able to set 'Top' using an if/else. No need to set arbitrary limit set on rows.
It resulted, in my case anyway, in 2 index scans on the table in my proc, accounting for 78% of the work, and the union merge took 21%. I don't know yet the performance implication this has on a humongous query.
Worse comes to worse, I resort to choice #3.
Anybody out there have a better way to do this?

Thursday, November 14, 2013

Always Make Sure it's Plugged into the Wall! SQL Remote Connection into a Named Instance

Getting started using SAP BODS (Business Object Data Services). My testing playground are my 2 local SQL servers, the main SQL Server 2008 R2, and its stepbrother, SQLEXPRESS.
The jobs run on SAP don't run locally from my repository, but from a central network server.
Didn't realize that at first; had to learn the hard way.
Couldn't figure out for the life of me why executing jobs worked fine connecting with the my main SQL Server 2008 R2, which doesn't depend on a named instance, while the Express server could never be found.
Even after realizing, upon combing through the job's error output, that these jobs were not being locally executed, it didn't dawn on me right away that the Named Instance nature of the express server was the real issue.
I'm kinda dense that way sometimes.
Anyhoo, after combing back and forth like an ocean tide over all the configuration and security settings of the express server and my PC, looking for the magic bullet or best suspect to arrest, I finally received the epiphany.
The remote server executing the jobs only knew my local machine's name, which by default pointed to the main SQL server without needing a named instance. Adding the express's name to that was the equivalent of speaking in tongues to the job server, with it being an atheist.
No named instance, fine, let's get to it and work.
Named instance, "like, DUH??? Which way do I go George, which way do I go...?"
Supposedly there's always got to be one, but when Googling tech issues, you're lucky to even find one real solution, or even a mere clue to one.
Turned out, all I needed to do was turn the SQL Server Browser service on, along with making SQLEXPRESS's TCP/IP enabled (in SQL Server Configuration Manager under the server's protocols), which I'd already done further back in the process of futility.
Bang, boom! Named instance found! Job complete.
One last little thing, in express's TCP/IP protocol settings, I had to make sure what was listed under IPAll for dynamic ports, was set as THEE port for it, because the normal default SQL port, 1433, was already taken by 2008 R2.
 I just cut the port number from Dynamic ports and pasted into 'TCP Port'.

Tuesday, November 12, 2013

Proxy Fight and How to Be Credential

We had a devil of a time trying to figure out how to get an SQL Server Agent Job to run 3 DTSX packages that needed to collect their source data from 2 outside database servers.
The Server Agent by default executes all its jobs with its own service account as the user. Well, that didn't sit well with the other 2 databases, who didn't give a rats butt about that user - they weren't letting him/her/it in.
Finally figured out we could swap that default user with one that was a recognized user on the other 2 servers.
We had to set that user up as a Credential. In object Explorer, go to Security, and then right click to set up a new Credential. I just put the acceptable user id in as both the Credential name and Identity, along with that user's network pw. I left Encryption provider unchecked.
Then I set up the same as a proxy under SQL Server Agent.
Then I changed the default 'Run As' on each job step to the new proxy, which now came up as a drop menu choice.
Badda bing, bang, boom, the job now worked.
Once we were able to get our server's service account set up as a user on the other 2 servers, we switched the 'run-as' back.