SQL Server; MongoDB and Redis Development

C#: SqlException Was Unhandled–Incorrect Syntax Near ‘en’ …

noreply@blogger.com (Tim Smith) — Tue, 15 Oct 2013 11:22:00 +0000

Cannot use a BREAK statement outside the scope of a WHILE statement. Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicoln. Incorrect syntax near ‘Word’.

This morning, I downloaded data through a new aggregator and tied it to a string. Using RegEx, I stripped the HTML and some of the Javascript (though I noticed more Javascript in it, so I’ll need to work on this shredder). However, when trying to move the data into the SQL Server database, I received the above error and for a second I thought it was an encoding issue (I’ve done this before now when trying to download data from a Microsoft Exchange folder and encoding attachments). Then it hit me: any apostrophe character will break TSQL when we’re trying to insert a string into a VARCHAR field because apostrophes go around the string.

After shredding all the HTML and Javascript, the only simple line of code needed was …

tsqldata = tsqldata.Replace("'", "");

… so that the string value was inserted into SQL Server. That eliminated the problems. One thing to note (especially before we’ve had caffeine) is that Twitter readers or Web Page readers may catch apostrophes and eliminating them works as one possible solution.

BCP: Unable To Open BCP Host Data-File

noreply@blogger.com (Tim Smith) — Thu, 26 Sep 2013 10:38:00 +0000

This morning I testing exporting a created JSON procedure from SQL Server (the query outputs JSON from a specific table format) and ran across and interesting error:

Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file

After checking a few items (server and procedure), I reviewed the error again to make sure that I wasn’t missing something obvious. In addition, the error indicates a problem with the actual file, not the server (the previous error related to local host gave this away). So I moved the directory around to other locations based on an SO post (for instance, the Download folder) and finally tried it on my third drive and it succeeded. Apparently the location matters; in addition, after reviewing some comments, it also seems like where it’s being executed also matters, though I never had success trying the same location as SQL Server (this may work for others, though). If you’re getting this error:

1. Try a different location.
2. Try the SQL Server location.
3. Try a second/third/fourth/etc drive.

As a note: BCP exported the JSON document perfectly, though I intend to side-step the utility by automating it in C#, as this morning was just to test this approach with how the document appeared. While JSON isn’t the easiest output for SQL Server (depending on the table structure or data, we’ll need some fairly advanced code to do so), the results and usefulness of the exported data work well.

TSQL: Bulk Insert ERRORFILE

noreply@blogger.com (Tim Smith) — Wed, 18 Sep 2013 21:37:00 +0000

When debugging a Bulk Insert in TSQL, one handy trick is to use an error file in the code. For instance, suppose we wanted to import a file off the C drive called “File”, we could use the following syntax to log the errors in a file:

BULK INSERT TableName
FROM 'C:\File.txt'
WITH (
	FIELDTERMINATOR = ','
	,ROWTERMINATOR = '0x0a'
	,ERRORFILE='C:\logfile.log')
GO

This log file will provide some specifics as to why our Bulk Insert failed and from there, we can adjust as needed (sometimes the columns need to be expanded, or the last line of the file actually contains no data, but trashes the entire process).

TSQL: Using Common Table Expressions For Aggregating

noreply@blogger.com (Tim Smith) — Tue, 17 Sep 2013 23:09:00 +0000

I prefer to use CTEs over temporary tables in most situations where data are used once as opposed to over and over again (for instance, in a stored procedure). If I need to use a data set thirty or forty different times, with JOINS and a WHERE clause that’s SARGable, I will totally use a temporary table and make use of the index. But what about a one time query, or a procedure that is called once a month? If I need to grab the data quickly, by far, a common table expression works better.

The below example looks at the difference between the 200 simple moving day average of a stock and the price of the stock. It then looks at the price of the stock at certain amount of days later (30, 60, 90, etc). The expression may look likes it consumes a lot of time, yet performs within a second (even with all the calculations). Considering that this may be used once a month (at most), I don’t find it effective to build a temporary table to solve it with indexes (though, if it were used more frequently, the indexes could come in handy on the JOIN and WHERE clause). This simple common table expression and query shows how easy it is to build these and query useful data.

WITH CTE AS(
	SELECT SOID
		, Price - TwoHundredDaySMA [PriceAbove]
		, (((Price - TwoHundredDaySMA)/TwoHundredDaySMA)*100) [PercentAbove]
	FROM SoHistoricalData
	WHERE TwoHundredDaySMA IS NOT NULL
)
SELECT s2.[Date]
	, s2.[Price]
	, s2.[TwoHundredDaySMA]
	, s1.[PriceAbove]
	, s1.[PercentAbove]
	, s2.[Price] PriceCompare
	, s7.[Price] FiveDaysLater
	, (((s7.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInFive
	, s30.[Price] ThirtyDaysLater
	, (((s30.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInThirtyDays
	, s60.[Price] SixtyDaysLater
	, (((s60.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInSixtyDays
	, s90.[Price] NinetyDaysLater
	, (((s90.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInNinetyDays
	, s120.[Price] OneHundredTwentyDaysLater
	, (((s120.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInOneHundredTwentyDays
	, s150.[Price] OneHundredFiftyDaysLater
	, (((s150.[Price] - s2.[Price])/s2.[Price])*100) PercentChangeInOneHundredFiftyDays
FROM CTE s1
	INNER JOIN SoHistoricalData s2 ON s1.SOID = s2.SOID
	INNER JOIN SoHistoricalData s7 ON s2.SOID = (s7.SOID - 5)
	INNER JOIN SoHistoricalData s30 ON s2.SOID = (s30.SOID - 30)
	INNER JOIN SoHistoricalData s60 ON s2.SOID = (s60.SOID - 60)
	INNER JOIN SoHistoricalData s90 ON s2.SOID = (s90.SOID - 90)
	INNER JOIN SoHistoricalData s120 ON s2.SOID = (s120.SOID - 120)
	INNER JOIN SoHistoricalData s150 ON s2.SOID = (s150.SOID - 150)
WHERE s1.[PercentAbove] < -7
ORDER BY s1.[PercentAbove] DESC

TSQL: Group Items By Data Separate By Comma

noreply@blogger.com (Tim Smith) — Tue, 17 Sep 2013 23:00:00 +0000

One of the most popular questions I’ve seen. Suppose that we have various stocks that pay different dividend yields and we want to group the stocks by the dividend yield they pay separated by a comma. The below example shows how (we’ll be using XML with the STUFF function). Note that this example comes up many times because we tend to use this logic on a regular basis.

CREATE TABLE ##finance(
  Stock VARCHAR(50),
	DividendYield VARCHAR(50)
)
INSERT INTO ##finance
VALUES ('XYZ','10 Percent')
	, ('YZA','10 Percent')
	, ('ZAB','5 Percent')
	, ('ABC','2 Percent')
	, ('BCD','5 Percent')
	, ('CDE','5 Percent')
	, ('DEF','5 Percent')
	, ('EFG','10 Percent')
	, ('FGH','2 Percent')
	, ('GHI','15 Percent')
SELECT f1.DividendYield
	, STUFF((SELECT ', ' + f2.Stock AS [text()] 
	FROM ##finance f2 
	WHERE f1.DividendYield = f2.DividendYield 
	ORDER BY f2.Stock 
	FOR XML PATH('')),1,1,'') AS "Stocks"
FROM ##finance f1
GROUP BY DividendYield

TSQL: Compare Tables

noreply@blogger.com (Tim Smith) — Sun, 01 Sep 2013 22:51:00 +0000

Woah! Been a while.

One way to quickly compare two different tables, using TSQL, especially when we have a few amount of columns and a field we can use to JOIN, is the a LEFT JOIN. I added this example to my GitHub account and it shows a few ways in which you can play around with this for a quick comparison.

/* Values don't match */
DECLARE @tablea TABLE(
	ID INT IDENTITY(1,1),
	ColOne VARCHAR(1)
)
DECLARE @tableb TABLE(
	ID INT IDENTITY(1,1),
	ColOne VARCHAR(1)
)
INSERT INTO @tablea (ColOne)
VALUES ('A')
	, ('B')
	, ('C')
	, ('D')
INSERT INTO @tableb (ColOne)
VALUES ('A')
	, ('B')
	, ('E')
	, ('D')
SELECT b.*
FROM @tablea a
	LEFT JOIN @tableb b ON a.ID = b.ID
WHERE a.ColOne <> b.ColOne
/* Row doesn't exist */
DECLARE @tablea TABLE(
	ID INT IDENTITY(1,1),
	ColOne VARCHAR(1)
)
DECLARE @tableb TABLE(
	ID INT IDENTITY(1,1),
	ColOne VARCHAR(1)
)
INSERT INTO @tablea (ColOne)
VALUES ('A')
	, ('B')
	, ('C')
	, ('D')
INSERT INTO @tableb (ColOne)
VALUES ('A')
	, ('B')
	, ('E')
SELECT a.*
FROM @tablea a
	LEFT JOIN @tableb b ON a.ID = b.ID
WHERE b.ID IS NULL
	AND b.ColOne IS NULL

Note, of course, we can compare tables with other methods, but I’ve found this to be fast and effective when the tables are small and only one or two values may not match.

TSQL Error: "@Schedule_uid Is Not A Parameter For Procedure Sp_add_jobschedule"

noreply@blogger.com (Tim Smith) — Wed, 24 Jul 2013 22:55:00 +0000

Problem: Suppose we're scripting jobs between two SQL Server environments (one a 2005 development environment and the other a 2005 production environment). When scripting these jobs and moving them from production (Enterprise Edition) to development (Developer Edition), we see the below error:

Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0
@schedule_uid is not a parameter for procedure sp_add_jobschedule.

Solution: in the production environment (Enterprise) for this example, schedule_uid exists as a unique identifier for the procedure sp_add_jobschedule, whereas in the development environment (Developer), it does not. Instead, schedule_id (notice the parameter change) exists as an integer. We can alter our script by replacing the @schedule_uid with @schedule_id and setting the @schedule_id equal to an integer value that is not found in the below script:

SELECT schedule_id 
FROM msdb..sysjobschedules 
ORDER BY schedule_id

For instance, if the above script shows schedule_ids between 1 and 100, then we would set our @schedule_id of the job to 101, like below this:

@schedule_id=101

Android: Remove Left Blue Tab

noreply@blogger.com (Tim Smith) — Sun, 26 May 2013 14:00:00 +0000

If a small blue left tab appears on our phone after we feel as if we pressed ten thousand buttons, and it looks like the below picture, here’s the fix for us to remove it.

Hold down the back button until it disappears. To make it reappear, you can hold down the back button as well.

SQL Server and Indexes: Clustered and Non-Clustered

noreply@blogger.com (Tim Smith) — Fri, 15 Feb 2013 17:29:00 +0000

“He who has a why to live for can bear almost any how.” ~ Nietzsche

In discussions with colleagues, I’ve noted numerous misconceptions about indexes, specifically the why. When we discuss technical information, we may feel tempted to use other technical information to support our ideas, when in reality, this can create confusion for some people who either are new to the industry, or find it difficult to picture what indexing accomplishes in the world of databases. To an extent, no analogy is perfect, and for the case of indexing, I’ll use some incomplete analogies to demonstrate what we’re trying to do when we index columns in SQL Server.

Imagine that we have a fitness book with various exercises in a random order. The book possesses no chapters, no table of contents, no headings, no page numbers, and no glossary or index. Suppose that we were fitness trainers and one of our clients asked us, “Can you show me the process of doing deadlifts?” How will we find this in the book?

Answer: Outside of reading through the entire book until we find the information, we can’t. Think of this as analogous to a table scan on a heap of data in SQL Server.

Of course, books like this typically don’t exist (or if they do, I have yet to stumble across one). A book like this would prevent users from finding information quickly and each time we tried to locate something, we would be facing a challenge of reading through the book until we found it.

Exceptions might exist, but generally, SQL Server doesn’t know where the data are when we query a table; it makes assumptions and in some cases, SQL Server’s assumptions are correct. Sometimes, it’s assumptions cause major performance problems. Like the book example, a heap of data forces SQL Server to scan the entire table until it finds what we’re looking for – a very bad idea, depending on the table.

Now, imagine that we have a fitness book with page numbers, a table of contents, and an alphabetical organization structure by fitness routine. Our same client comes to us and asks, “Can you show me the process of doing deadlifts?” How will we find this in the book this time?

Answer: More than likely, we will look at the table of contents and try to find deadlifts. Or, we will try to find the D-starting fitness routines. This would be like a last name index on a SQL Server table.

We find books like this all the time and, most of the time, we can identify information in them quickly. The reason that we have tables of contents, glossaries, indexes, page numbers and the like is because it makes it easier for us to find information. In a sense, databases are the new books, and indexes help us find information quickly.

Now, SQL Server can begin making more accurate assumptions, like we can, when indexes exist. Notice that we could try finding the information by the table of contents or by the alphabetical structure. These are like two indexes on the book – we can drastically minimize the time it takes to find information by using these indexes. The same holds true for SQL Server; does it have to go through an entire table to find a last name starting with L when we have an index on last name and it can just find the L and go through it? Of course not! SQL Server can save time and look through the Ls.

Note: this also brings up a point of SARGability on indexing. Although I dislike the term SARG when we’re discussing querying and efficient use of indexing, this is the popular term, so I’ll stick with it. In our example of finding a last name in SQL Server is it effective to find a last name of Johnson by doing WHERE LastName LIKE ‘%nson%’ or WHERE LastName LIKE ‘John%’? The first doesn’t use indexes effectively at all, the second does.

Another book analogy to this: imagine trying to find deadlifts in a book by searching for all use of the word “lift” in a fitness book. The word lift could appear ten thousand times, while only 10 times would relate to it! Plus, since any word could lead “lift”, we can’t assume that the word will start with L – it could start with any letter, meaning that we’re not using our indexes effectively. By having the right starting point, D, we know how we can limit our search.

Points like these demonstrate what a data scientist is – the intersection between a data analyst and a developer. Just because we throw indexes on our tables doesn’t mean we’re effectively using them. We can poorly use indexes.

So, now we know why we need an index. And, at this point, we know that we need to be using our indexes effectively. However, what about the times when we don’t need an index?

It is true that we can over-index a table in SQL Server. Let’s go back to our fitness book analogy. Imagine that we had a book that had a table of contents, a glossary, page numbers, chapters and verses. We can probably all think of a book like this: The Holy Bible and many other religious texts do this. While these texts are well organized (no disputing that), imagine the time it took for these books to be organized (before we had mass printing, of course)? In fact, religious texts like the Bible probably encouraged mass printing more than any other phenomena due to their complexity (I would adduce the same is true for the rise of NoSQL databases – because data are becoming so complex, a simple SQL database can’t always handle data, though in many cases it can).

Our problem here is maintenance. Sure, we can index ever column on a table in SQL Server and welcome some major problems when it comes to rebuilding them due to the increased time. Likewise, not every column needs an index anyway. If we seldom (or never) use it in where clauses, joins or queries, why are we indexing it (note: foreign key columns should be indexed in almost all cases – a few exceptions might exist here).

Hopefully, this post sheds some light on indexing in SQL Server, specifically the why of what we’re doing.

TSQL: Find the Column Names, Data Types With the Length

noreply@blogger.com (Tim Smith) — Fri, 15 Feb 2013 16:56:00 +0000

When we receive those warnings and errors about overflow or data types not matching, this script will help use quickly identify the discrepancy and code appropriately:

USE OurDatabaseName
GO
DECLARE @info TABLE (
	[Column Name] VARCHAR(250),
	[Data Type] VARCHAR(50),
	[Character Length] VARCHAR(50)
)
INSERT INTO @info
SELECT COLUMN_NAME
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'OurTableName'
UPDATE @info
SET [Character Length] = 'N/A  ' + [Data Type]
WHERE [Character Length] IS NULL
SELECT *
FROM @info

This code is on GitHub. Note, that in the case where there is no length, it will spit out N/A and the data type.

ActiveRecord::StatementInvalid in NAMEController#index Could not find table 'NAME'

noreply@blogger.com (Tim Smith) — Fri, 15 Feb 2013 15:35:00 +0000

Since I’ve deployed several RoR applications from one computer to another, I’ve made one goof several times that I’ve had to Google and thought it’s time to note it down for easy access. I immediately recognize that it’s related to the database, but always run the wrong command (usually, rake routes). The result is that I think the problem is solved, and yet it isn't. It’s simple: on the command line run

rake db:migrate

Basically, this means you begin your database schema in this context (it updates the database to the schema changes, which in this case, is for schema deployment). Of course, as I learn more about the intuitive language that is RoR, I might have a better explanation for this process.

Create An ERB File (or any other) Hack

noreply@blogger.com (Tim Smith) — Sun, 10 Feb 2013 00:52:00 +0000

The other day, I tried to save a text file as a .html.erb and it wouldn’t recognize the file. I tried using Notepad, Notepad++ and Textpad and nothing worked. My temporary work around was to copy an already existing .html.erb file and rename it and use it (worked well). However, I found a better hack:

1. Go to the command prompt (cmd in the search bar under Windows start if you’re not in the Ruby prompt).
2. When you’re in the directory you want to be in (or, if you’re in the Ruby prompt), type echo > thenameofourfile.html.erb and hit Enter.

3. The new file will be created and you can start editing it.

From what I can tell, this works with any file formats. This weekend, I created several CSS and Javascript files (note that you can input data into the files as well by using something like the below image):

Ruby, RoR: Install Spreadsheet Gem

noreply@blogger.com (Tim Smith) — Wed, 30 Jan 2013 20:50:00 +0000

Sometimes, SSIS doesn’t allow us to have as much control over Excel as we’d like (this is humorous only when you consider the company behind both products). In these situations, using a heavy-duty programming language might work better. For reference sake, and to make it easy on those looking for ways to handle this, we can use Ruby and Ruby on Rails (if it’s web based).

One gem we’ll want is the spreadsheet gem. On your application path in the command prompt window, type the following:

gem install spreadsheet

This will fetch and install the appropriate files. Following it, in the command prompt window, type the following:

irb

When it loads, type:

require 'spreadsheet'

3 Ways To Prevent Table Deletion In SQL Server

noreply@blogger.com (Tim Smith) — Tue, 29 Jan 2013 20:52:00 +0000

Suppose that we built a temporary table to hold data and then deleted it. If we do this process on a regular basis, we might become a little “overconfident” in our ability to create and drop tables. However, accidental drops do happen (though, hopefully, not every often) when building and eliminating temporary tables. Here are three methods to prevent error:

1. Use one or more unusual characters in the name.

If our main table is Accounts and we decide to build a table that we’ll drop called Eccounts, see the potential problem? One tiny mistake, and we’ll drop the wrong table and lose data (yes, we can always do point in time recoveries and such, but we want to minimize those situations). Instead if we choose zzzAcctszzz, even if we mistyped many letters, we’d still wouldn’t be close to dropping the main table. Z is an awesome letter because it’s seldom used, but any letter (or character) that’s seldom used will prevent accidental drops.

2. Build an unusual database database.

USE zzTestzz
GO

If that’s the beginning line and every reference to the table includes zzTestzz..OurTable, we’ve further reduced the odds we’ll drop the wrong table. We can easily transfer data from one database to another, so having a database solely for temporary tables can be useful to prevent errors. We can populate our tables on that database, clean the data, and drop the table without worrying.

3. Use @variable tables.

DECLARE @acct TABLE(
	Values INT
)

Hands down, my favorite and preferred method. Variable tables don’t need to be dropped when we’re doing data cleaning and they can do most of the cleaning that other methods can do and when the query finishes, they cease to exist.

Of Procedures and Processes

noreply@blogger.com (Tim Smith) — Mon, 28 Jan 2013 21:04:00 +0000

When troubleshooting a database error, we often look through the stored procedures to track the data flow. In many cases, errors are made in the “filters” (JOINS, WHERE clauses, etc). In a few instances, we may have an ASP.NET application that’s inputting bad data (and our procedures might not be catching these errors). However, regardless of where the error is located, we make troubleshooting easy for ourselves and others when we make comments about what we’re doing.

CREATE PROCEDURE OurBeginning
AS
BEGIN
/* 
Somewhere in the beginning of the procedure, we should add our name, the data and what our procedure does
*/
SELECT *
FROM OurTable t
	INNER JOIN OurOtherTable o ON t.OurValue = o.OurValue
-- Ideally, each filter point should have a note explaining why we are filtering out data, like ...
WHERE 
-- We need OurFirstValue because X reason 
OurFirstValue = FilterOne
-- We need OurSecondValue because Y reason 
AND OurSecondValue = FilterTwo
-- We need OurThirdValue because Z reason 
AND OurThirdValue = FilterThree
-- We need OurFourthValue because A reason 
AND OurFourthValue = FilterFour
END

This makes tracking data flow and data validation much easier, and it takes five minutes to do. Some good guidelines about commenting:

1. We should write as if we’re explaining the process to our Grandmother. Even we may forget someday!
2. Note the filters; it’s great to include a name, date and sentence description, but when we also add note on the filters, we can immediately remember why we wanted/didn’t want certain data.

SSIS: Compare File Data With Table and Update Table In SQL Server

noreply@blogger.com (Tim Smith) — Mon, 28 Jan 2013 18:16:00 +0000

This is a good question on SSIS and SQL Server; if we have frequent data files being sent to us, this is actually a common issue.

The solution, as far as the SSIS flow: an execute SQL task to build a table, a data flow task to input the data into the new table, then an execute SQL task to perform a TSQL MERGE (this is key). The MERGE will seek a match (on Mno in this example) and then update the appropriate data. Once the MERGE is complete, the same execute SQL task will drop the built table.

If we have multiple Excel files, the “Input data” task would be placed inside of a loop; this shows an SSIS loop through multiple Excel files.

SQL Server: Concurrency Models and Problems

noreply@blogger.com (Tim Smith) — Mon, 28 Jan 2013 15:44:00 +0000

I’ve been reading an excellent article about concurrency in SQL Server at Pinal Dave’s blog by Vinod Kumar (Blog | Twitter); and noted the Concurrency Models. We tend to say “it depends” frequently when asked questions about a database approach, and several examples pop to mind when thinking about concurrency. Consider a major national bank where thousands of transactions are happening by the millisecond and where having the most up-to-date information is necessary. Then consider a library, where an occasional update will happen, let’s say once a week, to its book selections and where only a hundred or so readers a day access the database to find books.

These examples demonstrate why one approach doesn’t work: in the bank’s case, customers thinking they have more money than they do (based on them reading dirty data) could result in a major lawsuit (note: this may provide a reason why many banks have a time-processing in order to verify the funds, but also to cover them when dealing with database issues). On the other hand, in our library example, we only have one hundred readers looking through the database a day and one weekly update. Statistically, dirty data reads are a low probability and an even lower probability exists that a library will be sued by its customers.

TSQL: Even Safer Update

noreply@blogger.com (Tim Smith) — Thu, 24 Jan 2013 22:48:00 +0000

When updating data, we can never be too safe. I first learned this lesson a few years ago when I accidentally updated 10 rows of data, when it should have only been 7. Luckily, I had a screenshot so that I could manually roll back my mistake. However, I immediately learned the value of a WHERE clause, as well getting the count right. When we do automatic updating through tools like SSIS, we can implement something similar to the following:

BEGIN TRAN
DECLARE @check INT
SELECT @check = COUNT(*) FROM OurTable WHERE OurColumn = Criteria
UPDATE OurTable
SET OurColumn = NEWVALUE
WHERE OurColumn = Criteria
IF @@ROWCOUNT <> @check
BEGIN
	ROLLBACK TRAN
END
ELSE 
	COMMIT TRAN

Normally, an automated tool will just perform inserts, updates or deletes, and we have to hope the WHERE clause performs correctly. The above method takes an extra step to ensuring we update what we want: a variable will store the number of rows that should be updated, and if the @@ROWCOUNT fails to match it, it will automatically rollback the transaction.

Again, it may seem like overkill, but one mistake shows the value of why code like this exists. In addition, when we automate processes with certain tools, we always want to make sure that even the slightest glitch will cause a rollback.

Example:

/* EXAMPLE */
-- Test table with test values
CREATE TABLE #t(
	Number INT
)
INSERT INTO #t VALUES (1)
INSERT INTO #t VALUES (2)
INSERT INTO #t VALUES (5)
INSERT INTO #t VALUES (4)
SELECT * FROM #t
-- Begin the safe update
BEGIN TRAN
DECLARE @check INT
SELECT @check = COUNT(*) FROM #t WHERE Number = 5
UPDATE #t
SET Number = 3
WHERE Number = 5
IF @@ROWCOUNT <> @check
BEGIN
	ROLLBACK TRAN
END
ELSE 
	COMMIT TRAN
SELECT * FROM #t

SQL Server: Test Table and Data

noreply@blogger.com (Tim Smith) — Thu, 24 Jan 2013 17:44:00 +0000

Sometimes we want to experiment with code, database design, structuring or the like before implementing it. To make things simple, the code below allows us to build a simple table and populate it with data, as well as keeping things customizable for us, in the case that we want to change things. If you have some good ideas, feel free to post them and I’ll upload them to GitHub with your name commented in the code, as this is useful stuff for any developer when it comes to experimenting with data, structure or administration.

/* 
Experiment table with easy data populate for playing with code
*/
CREATE TABLE zztestzz (
	FirstName VARCHAR(10) NULL,
	LastName VARCHAR(10) NULL,
	Phone VARCHAR(10) NULL,
	EmailAddress VARCHAR(200) NULL
)
DECLARE @cnt INT
SET @cnt = 0
-- Determine how many rows of each you want here
WHILE @cnt < 10
BEGIN
	INSERT INTO zztestzz
	-- Copy and past the values below to do insert after insert in this zztestzz table
	VALUES ('Jane','Doe','8005552121','emailettedoesnotexist@ournonexistentdomain.com')
	SET @cnt = @cnt + 1
END
/* VALUES List:
VALUES ('John','Doe','8005551212','emaildoesnotexist@ournonexistentdomain.com')
VALUES ('Jane','Doe','8005552121','emailettedoesnotexist@ournonexistentdomain.com')
VALUES ('John','Smith','8001112222','john.smith@ournonexistentdomain.com')
VALUES ('Jane','Smith','8002223333','jane.smith@ournonexistentdomain.com')
VALUES ('Kacy','Johnson','8001234567','kacy.johnson@ournonexistentdomain.com')
VALUES ('David','Johnson','8004445555','wildmanofalcatrass@ournonexistentdomain.com')
VALUES ('Joe','Joe','8000000000','ofcoursecommajoe@ournonexistentdomain.com')
VALUES ('Batman','NoLastName','Unlisted','batman@ournonexistentdomain.com')
VALUES ('RealBatman','4Realsies',NULL,'iamtherealbatman@ournonexistentdomain.com')
VALUES ('Scott','Adams','8001001000','dilbertdude@ournonexistentdomain.com')
VALUES ('Mark','Bill','8002002000','firstnameandfirstname@ournonexistentdomain.com')
Or add custom ones:
VALUES ('','','','@ournonexistentdomain.com')
*/
-- Check it
SELECT *
FROM zztestzz
-- Clean up
DROP TABLE zztestzz

Note: the use of zztestzz as a table name ensures that it is unlikely to match any other table name in the database, ensuring safety when dropping it.

TSQL: Populate A Random Number

noreply@blogger.com (Tim Smith) — Thu, 24 Jan 2013 15:13:00 +0000

Useful if we need some randomness involving integers (add more to 100 if wanting larger integers):

-- On its own this builds a random integer
DECLARE @num AS INT
SET @num = 100
SELECT CAST(@num * RAND() AS INT)

TSQL: Coalesce For Phone Numbers

noreply@blogger.com (Tim Smith) — Wed, 23 Jan 2013 16:20:00 +0000

When we have customers who have multiple phones (or only one), COALESCE provides one of the best tools to address this. For instance, if our company prefers to list home phones on contact before business or cell phones, we can use COALESCE to return the first value of the columns home, business and cell (phone number columns) so that home returns first. If no home number exists, and our company wants the business to be next in line, the business phone will return. Finally, if the customer has neither a home or business, it will return the cell phone.

Code example:

-- Build test table for returning preferred method of phone contact
DECLARE @phone TABLE(
	Home VARCHAR(15),
	Business VARCHAR(15),
	Cell VARCHAR(15)
)
-- Insert phone values
INSERT INTO @phone VALUES ('8005551212',NULL,NULL)
INSERT INTO @phone VALUES (NULL,'8005552121',NULL)
INSERT INTO @phone VALUES (NULL,NULL,'8005551122')
INSERT INTO @phone VALUES (NULL,'8005552121','8005551122')
INSERT INTO @phone VALUES ('8005551212','8005552121',NULL)
INSERT INTO @phone VALUES ('8005551212','8005552121','8005551122')
-- Select from phone values
SELECT *
FROM @phone
-- Business Logic: first Home, then Business, finally Cell
SELECT COALESCE(Home,Business,Cell) AS "Preferred Phone"
FROM @phone

TSQL: Get A File Name Only From a File Path

noreply@blogger.com (Tim Smith) — Wed, 23 Jan 2013 14:52:00 +0000

When we only need the file name instead of the full file path:

-- Build test table
DECLARE @FileName TABLE(
	FlNm VARCHAR(300)
)
-- Insert test values
INSERT INTO @FileName VALUES ('C:\OurFolder\OurNormalFile.csv')
INSERT INTO @FileName VALUES ('C:\OurServerName\OurFolderName\OurServerFile.txt')
INSERT INTO @FileName VALUES ('\\OurSharedFolder\OurSharedFile.csv')
INSERT INTO @FileName VALUES ('D:\OurBackUpDrive\OurBackUpFolder\OurBackUpFile.txt')
-- View the full paths with file names at the end
SELECT *
FROM @FileName
-- View the file names only
SELECT REPLACE(REVERSE(SUBSTRING(REVERSE(FlNm),1,CHARINDEX('\',REVERSE(FlNm)))),'\','') AS FlNm
FROM @FileName

TSQL: Find the Row Counts of all the Tables In A Database

noreply@blogger.com (Tim Smith) — Tue, 22 Jan 2013 22:07:00 +0000

This definitely helps us when we have a massive database in front of us and many of the tables have no rows of data at all. From here, we can then search for the columns in the most populated tables:

USE OurDatabaseName 
GO
/* 
Quickly find the rowcounts of all the tables in a database
Very useful if searching an unknown database 
*/
-- Create the table that will hold the values
CREATE TABLE #TableNameRowCount(
   TableName sysname,
   [RowCount] int
)
-- Insert the table names and how many rows they have
EXEC sp_MSForEachTable 'INSERT #TableNameRowCount (TableName, [RowCount]) SELECT ''?'', COUNT(*) FROM ?'
-- Obtain the table and row information
SELECT * 
FROM #TableNameRowCount
WHERE [RowCount] <> 0
ORDER BY [RowCount] DESC

TSQL: Eliminate Duplicates With LEFT OUTER JOINS

noreply@blogger.com (Tim Smith) — Mon, 21 Jan 2013 15:42:00 +0000

Another option, on top of a common table expression, is to use a LEFT OUTER JOIN to remove duplicates:

-- Build experiment table
DECLARE @dup TABLE(
	ColumnOne VARCHAR(1),
	ColumnTwo INT
)
-- Insert experiment values
INSERT INTO @dup VALUES('A',1)
INSERT INTO @dup VALUES('A',2)
INSERT INTO @dup VALUES('B',3)
INSERT INTO @dup VALUES('C',4)
INSERT INTO @dup VALUES('D',5)
INSERT INTO @dup VALUES('E',6)
INSERT INTO @dup VALUES('E',7)
INSERT INTO @dup VALUES('E',8)
INSERT INTO @dup VALUES('E',9)
-- See the duplicates
SELECT *
FROM @dup
-- What should exist in the table
SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) 
FROM @dup 
GROUP BY ColumnOne

In this example, we want the minimum values of ColumnTwo, but in the case of A and E in ColumnOne we have duplicates. Using the LEFT OUTER JOIN, we can return every row from the left table (where it receives it’s name) even if no match exists in the right table. When there’s no match in the right table, we’ll receive a NULL. Therefore, if we want to remove duplicates, we can remove anything in the right table that’s a null – thus preserving our good values:

-- What should be removed	
SELECT *
FROM @dup A
	LEFT OUTER JOIN (SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) FROM @dup GROUP BY ColumnOne) B ON A.ColumnTwo = B.ColumnTwo
WHERE B.ColumnTwo IS NULL
-- Remove the duplicates
DELETE @dup
FROM @dup A
	LEFT OUTER JOIN (SELECT ColumnOne, ColumnTwo = MIN(ColumnTwo) FROM @dup GROUP BY ColumnOne) B ON A.ColumnTwo = B.ColumnTwo
WHERE B.ColumnTwo IS NULL
-- See the results
SELECT *
FROM @dup

Here’s another way to remove duplicates using a common table expression.

ERROR JAVA INSTALLER; C:/Users/[USER]/AppData/Locallow/Sun/Java/jre1.7.0_06/Java_sp.dll is corrupt.

noreply@blogger.com (Tim Smith) — Mon, 21 Jan 2013 01:54:00 +0000

Fix:

1. Go to the download file (usually in downloads).
2. Right click on the file and select properties (it’s called chromeinstall-7u11).
3. Click the Compatibility tab.
4. Select “Run this program in compatibility mode for:” and choose “Windows XP (Service Pack 3)” because Windows 7 results in an error.
5. On the same tab, under “Privilege Level” select “Run this program as an administrator” to ensure everything works accurately.
6. Run the installer.

Screenshot below this: