Archive for the ‘Programming’ Category

Visual Studio Team Suite 2005 on Vista 64 bit

Wednesday, April 21st, 2010

This post may simply be a product of my own ignorance, but hopefully it can help someone else out there in a similar situation.

So recently I was tasked with setting up a Vista Business 64-bit laptop for a developer in our office.  The needed applications are as follows:

SQL 2005 – full reporting and management tools

SQL 2008 – full reporting and management tools

VS Pro 2005

VS Pro 2008

VSTS 2005

VSTS 2005 add-on for database developers

Cisco VPN Client to connect to PIX

Office Suite

Adobe Fireworks CS4

I was told that the VSTS 2005 would not install on 64 bit Vista.  Well that right there made me want to do it.  I wasn’t about to let a new machine with 8GB of RAM go to waste by putting a 32-bit OS on it.  I started by installing the following applications in this order(they can be picky, so please be sure to do in this order):

Vista SP1

Vista SP2

SQL 2005

SQL 2008

VS Pro 2005

VS 2005 Team Explorer – (may not have been necessary, but it didn’t hurt anything and I didn’t try the process without)

VS Pro 2008

VS Pro 2008 SP1

VSTS 2005

VSTS 2005 SP1

VSTS 2005 SP1 Update for Vista – *

VSTS add-on for database developers

* – This is critical.  Without this, the VSS cannot connect to TFS server and Team Suite will not work.

Now, first off, UAC is a pain.  Unless you have modified your local security policy to run all administrators with elevated privelages, and turned off UAC, you will likely have VS 2005(any version) and SQL 2005 fail to install.  The error given can be anything from XP SP2 is not installed to permissions issues.  To avoid this, do two things.  I think you only need to do one or the other, but I did both, just to be safe.

Copy the contents of the install disks to the hard drive before running setup

Right-click and run setup as administrator

You should have no issues now with the initial installers going though.  At this point, I was confused, as the developer had copied over a project and all the related files to his C: drive and was attempting to launch it, but it kept giving an error

Unable to connect to server.

Then it prompts me to remove source control data or temporarily work offline.

Neither of these were viable options.  The strangest part was that I could goto Tools>Connect to TFS and see the projects on the server.

After speaking with someone on the MSDN forums, I realized that the issue was that there was no local path specified for source control to download files to.  If you go to the Source Control node in the Team Exploere window, find the path for the solution and open it, it will prompt you for a location to download the source files to locally and proceed to download them and open your solution.  The problem all along was that we were trying to access the solution that was copied over thinking it would find the TFS server and not actually downloading the source from TFS.

Like I said, possibly my own ignorance, but it was annoying enough that hopefully I save someone else the headache.  Let me know if you have any questions or issues and I will do my best to answer them.  I will try to post my solution for getting the Cisco Pix VPN to work on Vista x64 here in the next week.

Tags: , , , , , , , ,
Posted in Programming, Visual Studio, Windows | 1 Comment »

Find Tables Missing Indexes and Create Clustered Indexes for Them

Wednesday, February 24th, 2010

**Update at bottom**

OK, so we had a SQL 2005 database that we migrated from another company.  The application that uses it as a backend was having some terrible performance issues.  We had limited information on the previous configuration, so we bumped the Application up to a newer server since it seemed most of the load issues were with that part.  Afterwards, there were still performance issues and timeouts.  SQL was queuing up commands and taking too long to process them.  So we got the senior DBA involved to help us see what kind of performance increases we could get on the SQL server.  Unfortunately, I did not note all of the changes, but this was the biggest improvement.

We found out that the previous company also had performance issues.  When looking at the tables, we noticed many did not have indexes.  This little query was a lifesaver.  It shows you which tables are without an index, how many reads/writes and if things are queuing.

SELECT

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

‘CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']‘

+ ‘ ON ‘ + mid.statement

+ ‘ (’ + ISNULL (mid.equality_columns,”)

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END

+ ISNULL (mid.inequality_columns, ”)

+ ‘)’

+ ISNULL (’ INCLUDE (’ + mid.included_columns + ‘)’, ”) AS create_index_statement,

migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

The second column is a query string generated by the first query that will create indexes based on what is needed.  A note though, that query only creates standard indexes.  I recommend to change it to create clustered indexes.  They are considerably faster.  Here are the commands I used to create our indexes.

CREATE CLUSTERED INDEX cix_MASTER1_HISTORY on MASTER1_HISTORY(mrID)

CREATE CLUSTERED INDEX cix_MASTER1_DESCRIPTIONS on MASTER1_DESCRIPTIONS(mrID)

CREATE CLUSTERED INDEX cix_MASTER1_TIMETRACKING on MASTER1_TIMETRACKING(mrID)

CREATE CLUSTERED INDEX cix_MASTER2_HISTORY on MASTER2_HISTORY(mrID)

Hopefully this helps some of you out there

**Updated 3/1/2010

This query to find indexes is dynamic.  it doesn’t actually find all tables missing indexes, it finds tables that are CURRENTLY being searched without indexes and ranks them based on the performance problems they are causing.  So run this query when your SQL starts getting backed up.

Tags: , , , , ,
Posted in Programming, SQL, Windows | No Comments »

Error Logging in VB and C#

Thursday, December 17th, 2009

Alright, so lately I have been working on a couple of ASP.NET(C#) projects.  One thing I have seen is nothing is as useful as having a good error logger.  Now, since I am still in the learning phases of .NET programming, I have been looking to a coworker, Joe Young, sort of as a mentor.  He provided me with some code that has just immensely helped me.  I have attached 2 files.  one in VB, on in C#, they are Joe’s code.  These are your App_Code files.  They will create a detailed log of any errors in a file in a directory you specify.  You specify the file in the attached files.  To use them, you will insert the following code. (For the below VB examples, I used a website to convert it from my C#, as I was being lazy)

C#:

private void LogError(Exception errMessage)
{
errorlogger objLog = new errorlogger();
objLog.logerror(errMessage);
}

VB:

Private Sub LogError(ByVal errMessage As Exception)
Dim objLog As New errorlogger()
objLog.logerror(errMessage)
End Sub

This allows you to call the external errorlogger.

Now, to call it. The most useful place I have put these are in SQL connections/procedure calls. Here is an example from one of my programs.

C#

try
{
string strSQLSetCheckedOut = “exec CP_setCheckedOutTrue ” + HttpContext.Current.Session["sName"].ToString();
SqlCommand conSQLCommand = new SqlCommand(strSQLSetCheckedOut, conSQLConnection);
conSQLCommand.CommandTimeout = 120;
conSQLConnection.Open();
conSQLCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
LogError(ex);
}
finally
{
conSQLConnection.Close();
}

VB:

Try
Dim strSQLSetCheckedOut As String = “exec CP_setCheckedOutTrue ” & HttpContext.Current.Session(”sName”).ToString()
Dim conSQLCommand As New SqlCommand(strSQLSetCheckedOut, conSQLConnection)
conSQLCommand.CommandTimeout = 120
conSQLConnection.Open()
conSQLCommand.ExecuteNonQuery()
Catch ex As Exception
LogError(ex)
Finally
conSQLConnection.Close()
End Try

Hopefully this little bit of code out there will help someone else out.

C# ErrorLogger Code

VB ErrorLogger Code

Tags: , , , , , , , , ,
Posted in .NET, C#, Programming | No Comments »