SQL Server Naming - No, No

MS Access: Check Catalog Msg 3853 Problem

Recently, we had a SQL Server 2005 table we couldn't connect to from MS Access. It turned out the problem was due to an index having a '.' in the indexes name. MS Access didn't recognize this index due to its naming convention.

To work around the connection issue, we created a view and were able to connect to the view which was a SELECT * FROM [PO.TABLE].

The vendor for the software package we were working in is now determining what it will take to rename the index. Undoubtedly, the index is hard coded with that name in many places.

Lesson Learned: don't use periods in your sql object naming convention, period.

Google MySql Ad

SELECT you FROM company JOIN Google ON company.title = Google.SWAT_Team

If that doesn't get your attention as a SQL programmer, I don't know what will.

SSIS SQL 2005

If you get an error while publishing a step that involves SSIS; check to see if SSIS is installed. You would think SQL server would warn you before giving you an error of 14234.

Update Table From Column In Table

=== QUESTION ===
How do I update a field in a table from another field in the same table?

=== RESPONSE ===
You can perform a SET for a field from another field in the same table.

- UPDATE [Northwind].[dbo].[Employees]SET [TitleOfCourtesy]=([Firstname])
- SELECT TITLE,TITLEOFCOURTESYFROM EMPLOYEES

SQL Server 2000 - Search Stored Procedures

SELECT *
FROM NORTHWIND.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%ITEM%'

ISNULL Concatenation on SQL Server

When concatenating multiple fields, the concatenated field will be returned nil by default if any of the concatenated fields are NULL.

To work around this and get a value for the concatenated fields with values, you can use the ISNULL function on each field being concatenated.

SELECT dbo.ITEMS.ITEMNO, dbo.BRAND.DESCRIPT AS BrandDescript, dbo.ITEMS.MODEL, dbo.ITEMS.DESCRIPT AS ItemDescript, dbo.CATEGORY.DESCRIPT AS CatDescript
, ISNULL(dbo.BRAND.DESCRIPT,'') + ' ' + ISNULL(dbo.ITEMS.MODEL,'') + '' + ISNULL(dbo.ITEMS.DESCRIPT,'') + ' ' +ISNULL(dbo.CATEGORY.DESCRIPT,'') AS FullDescript, ISNULL(dbo.BRAND.DESCRIPT,'') + ' ' + ISNULL(dbo.ITEMS.MODEL,'') + '' + ISNULL(dbo.CATEGORY.DESCRIPT,'') AS BMC
FROM dbo.ITEMS, dbo.CATEGORY, dbo.BRAND
WHERE dbo.ITEMS.CATEGORY = dbo.CATEGORY.CATEGORYAND dbo.ITEMS.BRAND = dbo.BRAND.CODE

Min / Max / Group By

==== SQL QUESTION ====

Let's say I've got a table with 2 columns. 1 is a primary key, the other is another identifier that might be shared among multiple rows. Example:

id refkey
--------------------
1 ABC
2 DEF
3 ABC
4 GHI
5 ABC
6 DEF

As you can see rows 1,3 and 5 all share the same refkey "ABC", and rows 2 and 6 share refkey "DEF".

How would you go about getting a result set that listed all of the distinct refkey's and also only one of the associated primary keys? Doesn't matter which id, but it can only be one, so that your result set would look like:

id refkey
--------------------
1 ABC
2 DEF
4 GHI

Here's the kicker... you can't use a cursor. I can't figure this out for the life of me....


==== SQL ANSWER ====
SELECT DISTINCT MIN([ID]) AS [ID]
, [REFKEY]
FROM [TestDB].[dbo].[utbl_REF_KEY]
GROUP BY [REFKEY]
ORDER BY 1

ID REFKEY
1 ABC
2 DEF
4 GHI
(3 row(s) affected)

Formatting Current Date SQL Server

SELECT CONVERT(VARCHAR,GETDATE(),111)
FROM YOUR.TABLE

2 Ways to Perform Inner Joins

These 2 queries return the same data. They would return all players that are Chicago Bulls players.

SELECT PLAYER,TEAM
FROM dbo.PLAYERS
INNER JOIN dbo.TEAM on PLAYER.TEAMID = TEAM.ID
WHERE TEAM.TEAM = "CHICAGO BULLS"

SELECT PLAYER,TEAM
FROM dbo.PLAYERS
, dbo.TEAM
WHERE PLAYER.TEAMID = TEAM.ID
AND TEAM.TEAM = "CHICAGO BULLS"

Change Control

Change control; instead of making a backup and performing a change in LIVE; do this.

1) Make the backup.
2) Make a copy to a test database.
3) Perform then change / update in the test database.
4) Review the test data.
5) Test your restore capability to the TEST database.
6) Make the update in LIVE.