Posts

Posted on

Creating a System Admin Login for SQL Server Using the Command Line

SQL Server logo

This past week I installed Microsoft SQL Server 2014 Developer Edition on my dev box, and immediately ran into a problem I had never seen before.

I’ve installed various versions of SQL Server countless times, and it is usually a painless procedure. Install the server, run the Management Console, it’s that simple. However, after completing this installation, when I tried to log in to the server using SSMS, I got an error like the one below:

SQL Server login error 18456
“Login failed for user… (Microsoft SQL Server, Error: 18456)”

I’m used to seeing this error if I typed the wrong password when logging in – but that’s only if I’m using mixed mode (Windows and SQL Authentication). In this case, the server was set up with Windows Authentication only, and the user account was my own. I’m still not sure why it didn’t add my user to the SYSADMIN role during setup; perhaps I missed a step and forgot to add it. At any rate, not all hope was lost.

The way to fix this, if you cannot log on with any other account to SQL Server, is to add your network login through a command line interface. For this to work, you need to be an Administrator on Windows for the PC that you’re logged onto.

1. Stop the MSSQL service.

2. Open a Command Prompt using Run As Administrator.

3. Change to the folder that holds the SQL Server EXE file; the default for SQL Server 2014 is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

4. Run the following command: “sqlservr.exe –m”. This will start SQL Server in single-user mode.

5. While leaving this Command Prompt open, open another one, repeating steps 2 and 3.

6. In the second Command Prompt window, run “SQLCMD –S Server_Name\Instance_Name”

In this window, run the following lines, pressing Enter after each one:

>CREATE LOGIN [domainName\loginName] FROM WINDOWS 
>GO 
>SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN' 
>GO

7. Use CTRL+C to end both processes in the Command Prompt windows; you will be prompted to press Y to end the SQL Server process.

8. Restart the MSSQL service.

That’s it! You should now be able to log in using your network login.

Posted on

Errors in Pasting Data into Datasheet View in SharePoint 2010

Microsoft SharePoint logo

As a part of my project to convert a Lotus Notes system to something else, I am making good use of SharePoint Lists to replicate the functionality of some of the less complex Notes applications.

For some of these databases, I am exporting the documents into Structured Text, then converting the structured text to Excel spreadsheets.

A particularly troublesome error occurred when I was trying to copy a large number of rows from an XLSX file into the Datasheet View of a SharePoint 2010 List. I had originally copied several thousand rows into the List, only to realize that I had accidentally selected the wrong range of columns (which made for some interesting looking data when the data types were dissimilar). To fix this problem, I deleted the rows from the Datasheet View, and then attempted to copy-and-paste them again – this time using the correct range.

At this point, SharePoint gave me an several different errors indicating that an incorrect number of columns had been selected, that some cells were read-only, and that pasted data would go off the sheet.

After verifying multiple times that I had indeed selected the right number of columns and the correct columns in the Excel file, I finally decided to reload the Datasheet View in the browser with F5.

Voila, no more error when trying to paste the data. I do not know if this is a “known issue” or not, but it seems that reloading the Web page after deleting data from the Datasheet View is necessary prior to attempting to load new data.

I did find a good site for tips about copying from Excel into a Datasheet View.

Posted on

The Key to Solving CodeEval’s Bay Bridges Challenge

CodeEval logo

The Bay Bridges Challenge is one of the more difficult challenges on CodeEval (or at least it was for me!), primarily due to the fact that you’re not just looking for any set of bridges that can be built without intersecting, but that you’re looking for the largest possible set of bridges that do not intersect.

Not only do you have to have an algorithm that works for determining if two bridges (which could be thought of as line segments) intersect, but also one that will return the highest number of non-intersecting bridges.

The easy part (which I thought would have been the more difficult one) was how to get the optimal number of bridges. Basically, once you determine which bridges intersect each other and how many intersections there are, you eliminate the ones with the highest number of intersections first, working your way through the set of bridges until all the ones remaining do not intersect.

The part that I had trouble with was determining whether or not they intersected at all. The algorithm I started with was based on simple algebra. First, given the latitudes and longitudes of the endpoints of the bridges, I was able to determine the slopes and intercepts of the bridges. Then, by solving for the intersection point and determining that it fell within the “box” that could by formed by the endpoints, I would declare that the bridges did, in fact, intersect.

This worked fine for the test case given in the problem which only had six bridges, but for larger sets of numbers it did not work – probably due to a flaw somewhere in my equation that allowed for some tolerance to error.

After doing some more research, I discovered an algorithm for determining the intersection of line segments, and decided to implement that. It worked the first time, 100%! The code in Ruby for implementing this algorithm for line segments AB and CD could look something like this:

def ccw(A,B,C)
    (C.y-A.y)*(B.x-A.x) > (B.y-A.y)*(C.x-A.x)
end

def intersect(A,B,C,D)
    ccw(A,C,D) != ccw(B,C,D) & ccw(A,B,C) != ccw(A,B,D)
end