Friday, January 31, 2014

TF30063: You are not authorized to access

When trying to install a NuGet package I'm getting an error message, "TF30063: You are not authorized to access <server\projectname>", but I am definitely authorized to access that.

This person figured it out

1. Open IE
2. Click the gear in the top right
3. Internet Options > Security > Local Intranet
4. Click Sites button
5. Click Advanced button
6. Put in your TFS url, for example, http://tfs

That's it, it works.

Saturday, January 25, 2014

Pragmatic programmer tip 22 - use a single editor very well

Right now I use visual studio for C#, SSMS for SQL, Dex for sanScript, Word/Excel for documentation, notepad++ for text based tasks, Winmerge for comparing files, IE for managing sharepoint, powershell for scripting. And I may even be missing one.

So my goal is to start merging all of these things into one editor, Visual Studio.

Wednesday, January 22, 2014

Auto-increment build numbers in Visual Studio

Future reference for me

Drop a TT file in the VS project
<#@ template language="C#" #>
// This code was generated by a tool. Any changes made manually will be lost
// the next time this code is regenerated.

using System.Reflection;

[assembly: AssemblyVersion("<#= this.Major #>.<#= this.Minor #>.<#= this.RevisionNumber #>.*")]
int Major = 1;
int Minor = 0;
    int RevisionNumber = (int)((DateTime.UtcNow - new DateTime(2013, 11, 22)).TotalDays);

This will update the AssemblyVersion to 1.0.(Number of Days since 11/22/2013).(the last part is the autoincrementer already in there)

Comparing hashtables in powershell

I have two sets of strings specifying tablenames coming from two sources; one in an excel spreadsheet, the other in the code. I'm basically verifying that the tables match. This checks if the keys in one hashtable belong in the other hashtable, and vice versa. It will output whatever tables don't match.

$excelHash = @{"Table1"=1; "Table2"=1;}
$codeHash = @{"Table1"=1;"Table3"=1;}

foreach ($h in $excelHash.GetEnumerator())
    if (!$codeHash.ContainsKey($h.key))
     "Table in excel but not in the code: $($h.key)" | ft
foreach($h in $codeHash.GetEnumerator())
    if (!$excelHash.ContainsKey($h.key))
     "Table in code but not in the excel: $($h.key)" | ft

Tuesday, January 21, 2014

Get file version in powershell

$myBuildDirectory = "C:\AwesomeProject\"

dir $myBuildDirectory -filter *.dll | select Name, LastWriteTime, @{label="FileVersion";expression={$_.versioninfo.fileversion}}

This outputs

Friday, January 17, 2014

Unable to delete replication publication


I'm trying to delete a publication and I'm getting the error message above.

Execute this SQL statement:

use [DatabaseName] EXEC sp_changedbowner 'sa'

I had copied this database from a test server and restored it in my dev environment. It changed the db owner to the user I was logged into when I did that. Thus running that sql statement changes the database owner back to the correct owner (in my case, it is 'sa')

Wednesday, January 15, 2014

Learning Powershell - End a process

UI Way:
1. Ctrl-alt-del
2. Open Task Manager
3. Find the Process
4. Right-click -> End Process

Powershell way:
$name = "comm*"
get-process | where-object {$_.Name -like $name} | kill -name {$_.Name}

This is ending all processes with a name comm*. My specific goal here was to shut down all Lync 2010 processes. For some reason it opens several processes, and none of them actually show the Lync client. This happens sometimes, not sure why. But before, i had to go through and kill ALL of the "communicator.exe" processes. Now this one line script does it.

Monday, January 13, 2014

Learning Powershell - Get all files changed since a certain date

This gets all files from a specified folder, where that file has changed since a certain date and has type .cs (C# source file) or .sql (SQL script file), and outputs the name of the files in ascending order

$projectpath = "D:\Projects\AwesomeProduct"
$date = '12/18/2013'
$files = @(Get-ChildItem $projectpath -r | Where-Object { !$_IsPSContainer -and ($_.LastWriteTime -gt $date) -and ($_.Extension -eq ".cs" -or $_.Extension -eq ".sql") })
$files | select-object "Name" | Sort-Object "Name"

Saturday, January 11, 2014

Learning Windows PowerShell

Here's a PDF on learning Windows PowerShell. I love free knowledge like this.

Friday, January 10, 2014

Pragmatic Programmer tip - learn to use the shell

Ok, so the Pragmatic Programmer book slapped me with some important info right at the time i needed it.

Right now I am going through 20 files and making the same modification to them all. Unfortunately, i am not skilled at shell scripting (and i'm using Windows.. so..). This is something the book recommends, to learn shell scripting. I had to use Bash in college when i was forced to use Linux (gasp!), but i googled my way through it and never really learned.

So I'm going to start learning how to use Windows PowerShell!

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I am installing stored procs with a service and when I load and execute the query strings I get the error in the title. The query string is in format below...

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcName]') AND type in (N'P'))
DROP PROC dbo.StoredProcName

CREATE PROCEDURE [dbo].StoredProcName
grant execute on dbo.StoredProcName to SECURITYGROUPNAME

Wrap the stored proc in a exec('') statement. So it would need to be modified as such:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProcName]') AND type in (N'P'))
DROP PROC dbo.StoredProcName

CREATE PROCEDURE [dbo].StoredProcName
grant execute on dbo.StoredProcName to SECURITYGROUPNAME

Make sure to escape single-quote marks, or the exec will fail.
For example,
exec('SELECT * FROM TableName WHERE Name = 'Test'')
This would fail, you need to change that to this:
exec('SELECT * FROM TableName WHERE Name = ''Test''')


Thursday, January 9, 2014

Get embedded SQL Script at runtime

using System.IO;
using System.Reflection;

A sql script called Query.sql embedded in project ER. Embed the sql script by changing the Build Action = Embedded Resource

This loads an embedded sql script and outputs it to a textbox
using System.IO;
using System.Reflection;

            Assembly asm = Assembly.GetExecutingAssembly();
            StreamReader reader = new StreamReader(asm.GetManifestResourceStream("ER.Query.sql"));
            txtOutput.Text = reader.ReadToEnd();    

This can clearly be used for better purposes, such as executing the script.

Embedding resources in .NET:

Wednesday, January 8, 2014

Parse eConnectException error number

I'm using RegEx to parse the eConnectionException Message field, so that I can handle the error numbers as needed. In the code below the sample string comes from trying to insert a doc with a duplicate SOP Number. LogError() looks up the error info in the DYNAMICS..taErrorCodes table, then inserts into a logging table. HandleError() tries to gracefully handle the error. Error Number 2221 =  Duplicate document number. In this case I would use GetNextDocNum GetNextSOPNumber() to get a valid SOP Number, and retry the insertion.

            string sample = @"dummy"; //Look below the code snippet to see the actual sample string
            Regex regex = new Regex(@"Error Number = (?<ErrNum>[\d]*)");
            MatchCollection matches = regex.Matches(input);
            int errorCode = 0;
            foreach (Match m in matches)
                if(Int32.TryParse(m.Groups["ErrNum"].Value, out errorCode))
                    switch (errorCode)
                        case 2221:

Sample string
"Sql procedure error codes returned:

                    Error Number = 450  Stored Procedure= taSopLineIvcInsert  Error Description = Duplicate LNITMSEQ in SOP10200
                    Node Identifier Parameters: taSopLineIvcInsert
                    SOPNUMBE = TEST0044            
                    SOPTYPE = 3
                    LNITMSEQ = 1024
                    Related Error Code Parameters for Node : taSopLineIvcInsert
                    SOPNUMBE = TEST0044            
                    LNITMSEQ = 1024
                    SOPTYPE = 3

                    Error Number = 2221  Stored Procedure= taSopLineIvcInsert  Error Description = Duplicate document number. If adding or updating lines to an existing document, UpdateIfExists must = 1
                    Node Identifier Parameters: taSopLineIvcInsert
                    SOPNUMBE = TEST0044            
                    SOPTYPE = 3
                    LNITMSEQ = 1024
                    Related Error Code Parameters for Node : taSopLineIvcInsert
                    UpdateIfExists = Note: This parameter was not passed in, no value for the parameter will be returned.

                      <SOPNUMBE>TEST0044             </SOPNUMBE>
                      <CUSTNMBR>2B             </CUSTNMBR>
                      <DOCDATE>12/15/99 12:00:00 AM</DOCDATE>
                      <LOCNCODE>RUGBY      </LOCNCODE>
                      <ITEMNMBR>DOLLY                          </ITEMNMBR>
                      <PRCLEVEL>RETAIL     </PRCLEVEL>
                      <ITEMDESC>Appliance Dolly                                                                                      </ITEMDESC>
                      <ITMTSHID>ITEM-ALL       </ITMTSHID>
                      <TAXSCHID>S-N-NO-%AD-%S  </TAXSCHID>
                      <SHIPMTHD>EXPRESS MAIL   </SHIPMTHD>
                      <CURNCYID>USA            </CURNCYID>
                      <UOFM>Hour     </UOFM>

Tuesday, January 7, 2014

Code-trolling - sorting by spawning threads

I came across this while searching for something else. I saw an obvious homework-type question in the right-side links. This is a hilarious solution.

Basically the question is, user inputs a list of doubles. I want to return it sorted to the user. There are various "trolling" answers, but I found this one to be the funniest.


for each input i
  spawn thread with data i
  thread.sleep(i seconds)

thread callback
 put into output

XML Formatter

I am developing eConnect extensions. For testing I am outputting raw XML strings to a multiline textbox. I'm using this tool: to quickly format the output and check the values. 

"broken windows" theory

I've seen this "broken windows" theory pop up in multiple books now, so that's probably a good sign that i need to take it seriously. Unimportantly, i read it in a book called Pragmatic Programmers, and a second book called A Pentagon's New Map. They both talked about it, and applied it to technical problems and international security problems, respectively. More importantly, the theory is that if you leave a broken window on a building or car, then in short time it becomes a crimefest. The car gets stripped of all its parts, the building becomes vandalized, and eventually abandoned. Fixing a broken window prevents these much worse problems from happening. Therefore, it makes sense to put in the time and effort to fix small problems, because otherwise they are a catalyst for much bigger problems, so you're effectively preventing big problems by fixing seemingly unimportant ones.

This can be applied to any field really. For example, sometimes I tend to ignore bugs on my company website, unless they are big ones. This is because I would rather spend my time coding products, and not the website. However, applying the broken windows theory, it makes sense to take the time to fix the problems as they pop one.  These small unfixed problems have a psychological impact on people when they report bugs and the bug doesn't get fixed. It gives them a sense of despair, and they lose hope in the situation overall improving.

Monday, January 6, 2014

ParameterName is not a parameter in stored procedure StoredProcName in econnect

I updated the parameter list in a stored proc, let's called it DoStuff, then was getting an error "This @ParameterName is not a parameter in the stored proc DoStuff"

I had to restart the eConnect service before it would start working with the updated param list. Apparently eConnect caches param lists for stored procs? I guess that's OK if you assume your stored procs are rarely going to change. Since I'm doing development with a ton of customizations this is rather a pain in the ass. I'm glad I discovered this early.

Fields not getting serialized in econnect

Found these out the hard way.

Reason 1 - no default 
If you have numeric fields, such as int, short, decimal, etc.. you need default values otherwise they won't get serialized.

For example:
<xs:element minOccurs="0" maxOccurs="1" default="0"  name="SEQNUMBR" type="xs:int"/>

Before putting that in that field was simply not getting serialized and thus didn't end up in the XML Doc submitted to eConnect

Reason 2 - first field has minOccurs = 0. 

Apparently the first field in the XML node needs to have minOccurs = 1. It is simply ignoring fields before the first occurence of "minOccurs=1"

Empty parameters passed into custom stored proc in eConnect

1. Procedure or function 'MyExtension' expects parameter '@ParamName', which was not supplied.   
2. From SQL Profiler i can see it's passing in 'default' for every parameter, when I can clearly see there are values in the XML Doc  

I came across this

And tried what this person said to do. I removed the eConnect login from my database, uninstalled eConnect then reinstalled. The problem went away, and I could see it was passing in values for the parameters.

The article mentions Direct Document Sender. I couldn't find any download for this, but realized it was already on my computer. It's here: C:\Program Files (x86)\Microsoft Dynamics\eConnect 12.0\eConnect Samples\CSHARP DirectDocSender\

You can simply copy the Program.cs code and put it into a Windows Form, voila!