Geek In A Cube

SQL Server Error: Recover [DBNAME] From Suspect Mode

2012-01-15T09:40:00.000-08:00

This error can occur when the error log file does not match the database file or when the .mdb file is detected as being corrupt.

There are multiple ways to fix the problem and the solution depends on whether or not your database has data that is critical for you to keep. For me, I only get this error on my local box where I'm conducting allot of experiments and tests so that there is no need for me to worry about critical data. This makes the restoration task much more simple.

First, I try the following canned stored procedure : sp_resetstatus. If this fails then the following always works:

Stop the SQL Server database & services

Rename or move the "msdblog.ldf" & "msdbdata.mdf" files

Copy "msdblog.ldf & msdbdata.mdf" files from any other working database installation to the same path

Start the SQL Server database

Setting Up Your Home Wireless Network: Part I

2011-12-12T01:01:00.001-08:00

NETGEAR - N300 Wireless-N Router with 4-port Ethernet Switch

To those of us already immersed in technology, setting up a wireless network will seem simple. However, we often forget how confusing it can be for someone with no experience. Here is a very simple summary of what needs to happen to make your home wireless.

First, make sure you have all the equipment you need:

1. Broadband internet connection (DSL or Cable)

2. A modem (If you're already wired to the internet then you have a modem)

3. A wireless router. A regular router is a piece of equipment that will, basically, allow you to daisy-chain multiple network cables to one internet connection. For example, I have my cable, Xbox and computer all going through my router. So, of course, a wireless router also allows you to connect your devices wirelessly. I've had good experience with Netgear products as shown above.

4. Lastly, you need to make sure your device has a Wireless Network Adapter. Most laptops come with these included. If you want to double check your Windows device, then do the following:

  A.Click the "Start" button --> select Control Panel --> select System and Security

  B.Under System, click Device Manager.‌

  C.Double-click Network adapters.

  D.Look for a network adapter that includes "wireless" in the name (see picture below).

Once you have your hardware, then you are ready to get the network up and running. I'll explain that in Part II.

Intro to XML and Serialization

2011-12-11T18:30:00.000-08:00

XML (Extensible Markup Language) is a set of rules for encoding documents sent across the internet. In simpler terms, I tend to view XML as more of a format for passing data. Let's say you were online shopping for a calculator. The website would collect your data. For example,

Name: Master Chief
Address: 50 Forerunner Lane 98356
Item: Calculator
Price 76.98

The data above is easy for you to read if you are human, speak English, and live in America; however, if you are in France then it would need to look different for you to understand. That is one reason we have internet standards, formats, languages, etc. -- we want computers "anywhere to be able to read your data and translate it to human verbatum.

If we were to translate your data into read-able XML, then it would first look something like this:

<name>Master Chief</name>
<address>50 Forerunner Lane 98356</address>
<item>Calculator</item>
<price>76.98</price>

Now, for the complicated part -- serializing and de-serializing the data. Once your computer is ready to actually send the data to it's destination, it must be serialized. Serialization is the process of converting the data into a format that can be easily sent across the internet. My definition of serialization is the breaking up of data into small parts so that it can be sent more quickly and efficiently. Once the data reaches it recipient, then it must be deserialized back into the format above.

The data above may seem pretty simple to send so why bother with serialization? Most data is not so simple. What if you want to send a whole book or an entire database? It could take forever! Also, serialization can implement encryption of your private data so that it is more secure and not easily seen by anyone else -- handy for credit card information !

And that's XML and Serialization simplified.

St. Louis Day Of .NET

2011-09-13T19:09:00.000-07:00

This is late coming, but I wanted to make sure I comment on this year's St. Louis Day of Dot Net . It was awesome! Microsoft "Lightswitch" was introduced. I caught up to HTML5 technology, as well as many others, and it was great.

The yearly event was started in 2008 by the St Louis .NET user group. Many props to this group and the generous sponsors. This year, 800 people attended the conference -- ranging from developers, architects, quality assurance, managers, designers, DBAs and other technology positions.

It seems that most went away happy, satisfied, with a better understanding and encouragement to learn more about new technologies in .NET, databases, website development, architecture principles and software development in general.

Also, thanks to my company for letting me attend these types of events. They are short enough to attend but provide enough information that I get my motivation re-charged.

Must Have Been A Rough Weekend

2011-09-12T06:35:00.000-07:00

SQL: Maintaining Custom User Data Via Publishing Wizard

2011-09-11T09:15:00.000-07:00

We refresh our testing databases periodically with no problems. It is a logical protocol to make sure to alert all users ahead of time so that they may backup any needed custom data that they have entered themselves; however, when I did a data refresh this week something went awry and before I knew it there was a line to my desk, the phone was ringing, and email started piling up.

Fortunately, we did have a backup copy of this database so I was able to query and recover this data. However, I needed a quick and simple way to have the users re-create this data without my involvement. I could set up some type of job to restore the data after each refresh , but the data would only need to exist for a few months and I did not have time to go through the red tape that would be required for this. Given this particular scenario, it was decided to create a SQL script that would do INSERTS for the data.

Creating an INSERT script should not be difficult but it was too timely. Exporting and Importing the data was not ideal. I decided to investigate tools that would create the script for me. And there it was -- on my machine all along -- the SQL Server Publishing Wizard. I had forgotten all about it. I had just needed to locate it since there was no icon or link; therefore, the entire purpose of this way too-long-winded post :

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe

Yay, now I have a reference and reminder!

Anyway, it is worth mentioning that this tool is way inappropriate for large data. The data needed was a very small portion compared to the millions of rows that exists. I had to query the needed data to insert into a temp table and this made the wizard fast. Ideally, it would have been better to have the ability to query a table for data from within the wizard but I'm sure it is just a matter of time.

Error: Cannot resolve the collation conflict

2011-08-17T18:16:00.000-07:00

This error started occurring after one of my SQL Server 2008 databases had been restored:

Msg 468, Level 16, State 9 ...
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

Supposedly, this type of error can occur when the tempdb collation does not match the current database collation -- especially if using temp tables.

The easiest way to deal with this is to cast one of the field's collation so it matches the other, or you can cast both if you need. I'm not sure if this is best practice in the long run; however, if you're just doing some ad-hoc data mining then this is definitely a quick fix:

INNER JOIN [TABLE1].FIELD1 AS EA
ON EA.SERIAL_NBR COLLATE database_default = E.SERIAL_NBR COLLATE database_default

XBox Game Review: Your Shape -- Fitness Evolved

2011-08-14T08:44:00.000-07:00

My Rating: 7/10

I got this program last year for the Kinect and still have not finished it. Even though the graphics are obviously first-generation Kinect, the content is good -- if you've got the motivation. Unfortunately, the game is not very motivating. I wouldn't even call this a game since there is no fun-factor. I'm sure I burned many more calories in Kinect Adventures. After all, white-water rafting is much more enjoyable than squats.

Locate Stored Procedures Related To a Table

2011-08-14T08:30:00.000-07:00

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE LOWER(sc.TEXT) LIKE '%name%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments sc
INNER JOIN sysobjects o ON sc.id=o.id
WHERE LOWER(sc.TEXT) LIKE '%name%'

Visual Basic Operators: 'AndAlso' & 'OrElse'

2011-04-11T18:23:00.000-07:00

Since I've mostly been using C# for the past few years, I sometimes get confused when I come across a Visual Basic application and have to distinguish between the newer operators (OrElse, AndALso vs Or, And)

Operator: OrElse

This example uses the OrElse operator to perform logical disjunction on two expressions. The result is a Boolean value that represents whether either of the two expressions is true. If the first expression is True, the second is not evaluated. If the first expression is false then the second expression is evaluated.

Dim A As Integer = 10
Dim B As Integer = 8
Dim C As Integer = 6
Dim myCheck As Boolean
myCheck = A > B OrElse B > C ' True. Second expression is not evaluated.
myCheck = B > A OrElse B > C ' True. Second expression is evaluated.
myCheck = B > A OrElse C > B ' False.

Operator: Or (if either expression evaluates to true then true will be returned)

Dim A As Integer = 10
Dim B As Integer = 8
Dim C As Integer = 6
Dim myCheck As Boolean
myCheck = A > B Or B > C ' Returns True.
myCheck = B > A Or B > C ' Returns True.
myCheck = B > A Or C > B ' Returns False.

Operator: AndAlso (if first expression evaluates to false then second expression is not evaluated)

Dim a As Integer = 10
Dim b As Integer = 8
Dim c As Integer = 6
Dim firstCheck, secondCheck, thirdCheck As Boolean
firstCheck = a > b AndAlso b > c ' Returns True.
secondCheck = b > a AndAlso b > c ' Returns False
thirdCheck = a > b AndAlso c > b ' Returns False

Operator: And (both expressions are evaluated)

Dim a As Integer = 10
Dim b As Integer = 8
Dim c As Integer = 6
Dim firstCheck, secondCheck As Boolean
firstCheck = a > b And b > c ' Returns True.
secondCheck = b > a And b > c ' Returns False

Ideally, the short circuit operations, you're saving on the extra condition check when the first one is satisfied (OrElse) or not satisfied (AndAlso). So these should give you better performance in general.

OrElse: http://msdn.microsoft.com/en-us/library/ea1sssb2.aspx
AndAlso: http://msdn.microsoft.com/en-us/library/cb8x3kfz.aspx

XBox Game Review: Homefront

2011-04-10T07:43:00.001-07:00

My Rating: 6.5/10

Despite the nagging voice in the back of my head that this game would be all hype, I was looking forward to this game so much that I pre-ordered it anyway. Bad idea.

The story was interesting -- Both North and South Korea join up with Japan to make a super-army that invades the United States. Having grown up in the Red Dawn era, the story inspired a touch of fear in me; however, this was quickly flamed out.

Game-play was rather sloppy. Auto-aim only worked for enemies that were really close so perhaps the PC version is better.

The graphics were not worth mentioning. Many of the same objects were re-used throughout the entire game.

The one good surprise was the multi-player mode. Though not terribly extensive, the game-play was enjoyable. Regardless, I decided not to invest in multi-player mode and also did not play this game back through for the achievements. Instead, I took it to GameStop to trade in for Crysis 2 for as much money as possible before the inevitable price-drop that is soon to arrive.

SQL: UNION vs. UNION ALL

2011-04-03T09:46:00.000-07:00

I try to avoid UNION clauses in most SQL that I write. Historically, they have taken too much of a toll on performance. Now we have a speedier alternative to the UNION clause -- UNION ALL.

In a nutshell, a UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

This difference is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

UNION Definition:
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL Definition:
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

A great reference can be found in Pinal Dave's blog.

Xbox Game Review: Call of Duty - Black Ops

2011-04-03T08:56:00.000-07:00

My Rating: 8/10

I was looking forward to the story-line in this game but ended up hating it. I switched the game to "Easy" mode just so I could get away from the repetitive, screeching, nails-on-the-chalkboard What do the numbers mean!!!" It seems that whenever Activision gets involved, then the Call Of Duty series goes downhill a ways.

The game-play is good and the the graphics are superb. Out of the majority of the other games, it should be a top-ranker in my book ... but it is not. Like I mentioned, the campaign became so annoying that I had to force myself to finish it. My intentions have been to play it back through to get some achievement points out of it but this has not happened.

On the other hand, multi-player is excellent. For those non-competitive types who want to just run around with friends and shoot, "Combat Training" mode is excellent as well. For multi-player alone, I'll hold on to this game for a while.

Using FileIOPermissions With StreamWriter

2011-04-03T08:44:00.000-07:00

--The following exemplifies writing to a text file in a scenario where
--permissions need to be temporarily altered:

string tempPath = @"c:/file.txt";
if (File.Exists(tempPath))
{
FileIOPermission fio =
   new FileIOPermission(FileIOPermissionAccess.Write, tempPath);
fio.AddPathList(FileIOPermissionAccess.Write |
   FileIOPermissionAccess.Read, tempPath);

fio.Demand();  --use the .Demand method to obtain permission

StreamReader reader = new StreamReader(tempPath);
string content = reader.ReadToEnd();
reader.Close();

for (int j = 0; j < stringToReplace.Length; j++)
{
  content = Regex.Replace(content, stringToReplace[j],
      replacementString, RegexOptions.IgnoreCase);
} 
StreamWriter writer = new StreamWriter(tempPath);
writer.Write(content); writer.Close();
}

Chief Practicing His Modeling Skills

2011-03-28T18:51:00.001-07:00

Deprecated 'center' Tag In HTML 5

2011-03-27T08:16:00.000-07:00

Ok, so if your web site is no longer centered in FireFox but still works in Internet Explorer, then remember back to when the 'center' tag was deprecated back in HTML 5. FireFox simply will not tolerate this tag any longer and pushes to the standard of having tags DESCRIBE its contents and not DEFINE them.

Anyway, you might get away with replacing your 'center' tags with:

'div style="text-align: center;"'

However, it would probably be better to change your architecture to reference an "auto" property in your .css :

"margin: auto;"
"margin-left: auto;"
"margin-right: auto;"

Rapid Application Development (RAD) With The DataGridVIew

2011-03-24T16:50:00.000-07:00

Earlier this week, I came across a new developer who was trying to loop through his DataGridView to get the data out of it. Usually, RAD development with the DataGridView just does not work for most of my business scenarios but here was a clear case where it worked well so I will paste my instructions here:

1. Create your form.

2. Drag a DataGridView to your form. A pop-up window entitled "DataGridView Tasks" should appear. If not, then there should be a small arrow in the top right-hand corner of the DataGrid.

3. From here, select "Choose Data Source --> Add Project Data Source." This should trigger a wizard that will assist you in setting up your database connection. Select "Database", then select your table, and continue to walk through the wizard.

4. If you return to your form, you should now see a DataSet, a BindingSource, and a TableAdapter. These are the tools that you will use to get and save your DataGrid data.

5. Note that in your Form_Load event, there should be a like of code similar to the following:

this.tableAdapter.Fill(this.dataSet1.table);

6. Now, add a button to your form and create a _Click event. Here you should be able to save your data by entering your version of the following lines of code:

this.Validate();
this.tableBindingSource.EndEdit();
this.tableAdapter.Update(dataSet1.table);

7. The data should now be updated in your DataGrid and database.

This code will fill your DataGridView upon load. You can modify the SQL in the DataSet to pull only select data if you wish.

http://msdn.microsoft.com/en-us/library/ms171933(v=vs.80).aspx

Setting DateTime Value to Zero

2011-03-23T17:35:00.000-07:00

--using DATEDIFF can set the time portion of your DateTime value to zeros --Handy!

DECLARE @Day DATETIME

SET @Day = DATEADD(DAY, -7, DATEDIFF(Day, 0, GETDATE()))

Moving Files In SharePoint

2011-03-20T09:34:00.000-07:00

SharePoint's SiteManager is a great tool to manipulate folders and files; however, it can sometimes fall short at times when a massive amount files need to be relocated. With proper permissions, you can get around this by using Windows Explorer instead. PLEASE NOTE: When moving a mass of files around in this manner, you can lose certain file details -- such as history data.

Moving Files in SharePoint Via Windows Explorer:

1. Open up your SharePoint page in internet explorer and copy the path (ie, http://dime/sites/yoursite).

2. Open up windows explorer and navigate to your web folders.

3. Right click on ‘Web Folders’ and select ‘New’.

4. Enter your path from step 1 and, like magic, you can drag and drop your files! :D

Ignore Casing When Searching For A String

2011-03-07T17:41:00.000-08:00

--use regex to search for string regardless of casing:
content = Regex.Replace(content, stringToReplace, replacementString, RegexOptions.IgnoreCase);

Convert Comma-Delimited String To Array & Loop

2011-03-06T09:10:00.000-08:00

--set whatever delimiter you want to use:
char delimiter = ',';

--Get the string you want to turn to an array (in this case, I use the app.config)
string s = ConfigurationManager.AppSettings["StringToReplace"];

--Declare an array for your string
string[] stringToReplace = s.Split(delimiter);

--Finally, if you want to loop through your new array:
string content = String.Empty;

for (int j = 0; j <> 0; j++)
{
content = stringToReplace[j];
}

Error: Option Strict On disallows operands of type Object for operator '<>'.

2011-02-07T18:01:00.000-08:00

This is a fairly easy error to fix :

Error 55 Option Strict On disallows operands of type Object for operator '<>'. Use the 'Is' operator to test for object identity.

Chances are, you have a line of code like this:

If (Me._item <> Value) Then

Change it to this:

If Not (Me._item Is Value) Then

Of course, you could always try setting Option Strict off, but that is usually not the best practice.

Master Snowmen Builder

2011-02-02T14:38:00.000-08:00

SQL Error Handling

2011-01-20T18:20:00.000-08:00

During the pre-SQL Server 2005 days, we had to handle errors via transactions. At times, this could prove challenging when dealing with specific errors. Some might fall through making debugging difficult.

Fortunately, the team at Microsoft copied the try/catch approach from Visual Studio to SQL Server.

Here is the deprecated approach:

BEGIN TRANSACTION

DELETE FROM tblBlah
WHERE DATESENT <= @DATE IF @@ERROR = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

Here is the current best practice:

BEGIN TRY
BEGIN TRANSACTION

DELETE FROM dbo.tblBlah
WHERE DATESENT <= @DATE COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

END CATCH

Shhh! Secret Procedures!

2011-01-18T16:27:00.000-08:00

I don't have to tell you that the folks at Microsoft can be quite sneaky so, in case you don't already know, there are a number of unofficially documented SQL stored procedures that can make your life not only better, but often cursor-free.

The one that is most helpful to me is entitled "sp_msforeachtable." Using this stored procedure you can accomplish the same tasks on each table.

You can Google it for more information but a short and simple article can be found at DatabaseJournal.com:

"Beginning with version 6.5 of SQL Server, Microsoft provides a stored procedure called sp_MSreachfortable. Using the question mark as a place holder for all table names, the procedure will do the same as the above query in a single line. You can replace the above cursor with this :

sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"

For more handy secrets, do a search on "undocument sql server stored procedures" or http://lmgtfy.com/?q=undocumented+sql+server+stored+procedures .