Thursday, June 01, 2006

Wednesday, May 31, 2006

Unit Testing For The DB, Visual Studio Team Edition for Database Professionals Is Here

Microsoft Corp. today announced Visual Studio® Team Edition for Database Professionals, expanding Visual Studio 2005 Team System to include tools that enable software development organizations to collaborate more effectively when creating reliable, data-driven applications. The new product will be available as a Community Technology Preview (CTP) at Tech•Ed 2006 in Boston and is expected to ship by the end of 2006. This addition to Microsoft® Visual Studio 2005 Team System is the next milestone en route to the release of Visual Studio code-named “Orcas,” which will provide capabilities for building compelling applications that target Microsoft SQL Server™ 2005, Windows Vista™, the 2007 Microsoft Office system, and the next generation of Web technologies

Bringing Database Professionals Into the Application Life Cycle

Visual Studio Team Edition for Database Professionals delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams.

“All too frequently, schisms exist between database teams and development staff, leading to ineffective collaboration, inadequate project and code management, poor quality, and cost increases,” said Melinda Ballou, program director at IDC’s Application Life Cycle Management service. “Avoidable problems occur due to these gaps in communication. Users on both sides need tools and processes to facilitate coordination and management across groups and across skill sets, to increase efficiency, and to improve software quality earlier in the life cycle.”

This new member of the Team System family fulfills increasing demand in the market for more advanced tools for managing database changes by providing a foundation to reduce risk, ensure quality and speed deployment. Database architects, developers, administrators and other database professionals can now employ integrated change-management functionality to streamline changes to their databases and reduce the risk of catastrophic failure related to the alteration of database schemas. In addition, database professionals may now drive better quality earlier in the development process through integrated database testing, including support for database unit tests, complex test authoring and automatic generation of meaningful test data.

read the press-release: Microsoft Enhances Collaboration for Database Professionals With New Addition to Visual Studio Product Line

Import Dates, Skip Bad Data By Using ISDATE() And CASE

You have a table with a 'date' column and it's stored as varchar, the problem is that you also have bad data in there
You want to import this data into another table, but if the data can not be converted into a date you want to make it NULL
The way to handle this is by using the ISDATE() function together with CASE

--Let's create our table with bad and good data
CREATE TABLE SomeFakeDateTable (FakeDate VARCHAR(23))
INSERT SomeFakeDateTable VALUES ('ababababa')
INSERT SomeFakeDateTable VALUES ('20060101')
INSERT SomeFakeDateTable VALUES ('20060299')
INSERT SomeFakeDateTable VALUES (NULL)
INSERT SomeFakeDateTable VALUES ('20060401')
INSERT SomeFakeDateTable VALUES ('20050331')

--Here is the query
SELECT FakeDate,
CASE
WHEN ISDATE(FakeDate) = 1 THEN CONVERT(DATETIME,FakeDate)
ELSE NULL
END TheRealDate
FROM SomeFakeDateTable

Tuesday, May 30, 2006

red-gate SQL Prompt: Intellisense for SQL Server

SQL Prompt™
Intellisense for SQL Server, plus other features
There was a question on the SQL Server programming forum about intellisense and SQL server. it turns out that red-gate has such a product and better yet it's free until September 2006. So test it out (I will) and maybe it will be usefull for your development.

What does SQL Prompt offer (this info is from their site)?

Code completion for fast, accurate script building
Discoverability in SQL Server query creation
Keyword formatting, code snippet integration other extended features
FREE until 1st September 2006
No time-bombs, no restrictions

SQL Prompt provides Intellisense® style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements. SQL Prompt improves the productivity of all SQL script creation. SQL Prompt can be downloaded free until 1st September 2006 and we are providing forum support on the SQL Prompt support forum. A web help file is also available.

SQL Prompt simply sits behind the scenes and provides unobtrusive help when you press Ctrl-Space or when you type "." after a table/view/alias name.

Features include :

Table/View name completion
Column name completion
Stored procedure name completion
USE completion
JOIN/JOIN ON completion
Auto-uppercasing of keywords
Auto-popup after keywords
SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32

Download it here

Monday, May 29, 2006

Chaos Isolation Level In SQL Server

The other day I posted SQL Server Teaser: Isolation Level
So either no one reads this blog or no one knew he answer, or maybe you are too shy to make a comment
Anyway the answer is Chaos and you can find it in DTS, when you right click in the DTS designer then select Package Properties and after that click on the Advanced tab.
you can select it from the Transaction Isolation dropdown (see pic)

Thursday, May 25, 2006

Populating A Dropdown With Weekly Dates

Let's assume you have a website and the user can pick from a date range, You want to be a nice guy/girl (* reason for the asterisk is at the end) so instead of having a dropdown that looks like

Week1
Week2
Week3

You want it to have start and end dates for the week
The start date should be a Sunday and the end date should be a Saturday
The output should be something like this

2006-05-07 - 2006-05-13
2006-05-14 - 2006-05-20
2006-05-21 - 2006-05-27

What do you do? Do you store all these dates in a table, you can but you will have to maintain this.
Another way is to use a number table
You would create the number table only once. Then you can use DATEADD and DATEPART with the number table to return the desired results

-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000 BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO

--And here is the query

--I started with a Sunday
DECLARE @StartDate DATETIME
SELECT @StartDate ='05/07/2006'

--How many rows to return
DECLARE @RowsToReturn INT
SELECT @RowsToReturn = 50

SELECT DATEADD(wk,numberID,@StartDate) AS WeekStart,
DATEADD(dd,-1,(DATEADD(wk,numberID+1,@StartDate))) AS WeekEnd
FROM dbo.NumberPivot
WHERE NumberID <= @RowsToReturn -1 -- 1 since the number table starts from 0
AND DATEADD(wk,numberID,@StartDate) >=@StartDate
ORDER BY 1

* At my previous job we were doing file exchanges with some state agencies. One of the fields in the file was Sex and the values could be either F or M,. fair enough right? Well guess what, there was one person who worked there and had a sex-operation done and did not want to be classified as male or female So we then added other ( O ) We had no choice this was specifically written for the state and they were the major user of the program…oh well, nothing is black or white is it?

Wednesday, May 24, 2006

Spatial Indexes In SQL Server 2005

Maybe you work with Oracle as well as SQL server 2005 and you use Oracle Spatial and Oracle Locator. Well there is a way to implement this in SQL Server 2005.
Read the article "Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library" on MSDN.

This article explains how to add spatial search functions (point-near-point and point in polygon) to Microsoft SQL Server 2005 using C# and table-valued functions. It is possible to use this library to add spatial search to your application without writing any special code. The library implements the public-domain C# Hierarchical Triangular Mesh (HTM) algorithms from Johns Hopkins University. That C# library is connected to SQL Server 2005 using a set of scalar-valued and table-valued functions. These functions act as a spatial index.

SQL Server Pics From Version 1.0 On

Euan Garden has posted pictures of the SQL server product boxes since version 1.0
Did you know version 1.0 came out on Floppy's for OS/2?
Check out the pictures here
http://blogs.msdn.com/euanga/archive/2006/05/23/597677.aspx

Tuesday, May 23, 2006

SQL Server Teaser: Isolation Level

In SQL server 2000 there are 4 isolation levels

Read uncommitted which can produce a dirty read, a repeatable read and a phantom read
Read committed which can produce a repeatable read and a phantom read
Repeatable read which can produce a phantom read
Serializable

What if I tell you that there is a 5th one, no it’s not snapshot isolation we are dealing with SQL Server 2000 here

The question is how do you set it and what is the (appropriate) name of this isolation level
I will post the answer on Memorial Day if no nobody answers by then

Monday, May 22, 2006

Cumulative Hotfix Package (build 2153) For SQL Server 2005 Available

So just after sp1 we already have a hotfix
The following has been fixed

SQL Server 2005 Post-SP1 hotfixes that are included in SQL Server 2005 build 9.0.2153
SQL Bug number Description
410 Dimension security does not support visual totals on a parent attribute that is in a parent-child dimension.
433 If you execute an ALTER DDL statement that removes dimension hierarchies from the database without removing the hierarchies from the cubes, an access violation may occur.
447 Processing performance on multiprocessor computers is somewhat slower than expected.
459 Under very specific and rare circumstances, a deadlock can occur between two or more sessions in SQL Server 2005. In this case, the internal deadlock monitoring mechanism does not detect the deadlock between the sessions.
461 Page breaks may not be respected when a table is rendered in Microsoft Office Excel if that report contains tables with the conditions of NoRows=true and NoRowsMessage=null, and the table contains a table header or footer.
464 The leaf page of a non-clustered index may have only one index row.
491 On a SQL Server 2005 installation that uses Turkish collation, you cannot view job steps that are of SSIS type from SQL Server Management Studio. If a job has SSIS job steps only, you receive an "Index was outside the bounds of the array. (SqlManagerUI)" error message when you click the Steps tab.
499 A Multidimensional Expressions (MDX) query that involves non-trivial custom rollups when it runs complex sum-like aggregate expressions runs much slower on SQL Server 2005 than on SQL Server 2000.
531 When you run a query or a stored procedure in SQL Server 2005, certain conditions can cause the SQL Server optimizer not to be able to produce an execution plan.
541 An ADOMD.NET application that runs through an IXMLA provider is limited to 32 maximum user sessions.
554 When you query the IS_PLACEHOLDERMEMBER member property for a dimension member by using the .Properties("IS_PLACEHOLDERMEMBER") function in MDX, the value that is returned is always FALSE. This behavior occurs even for members that are placeholders in the dimension.
584 When a report contains an image that is external or is a resource in Reporting Services and the report uses the interactive sorting functionality, the image is replaced by a red x.
606 The MDSCHEMA_PROPERTIES row set returns the wrong data type for some user-defined properties.
608 If you include a subreport in a group footer and you enable the HideDuplicates property in a detail row on a grouping item, SQL Server 2005 Reporting Services raises an internal error when you try to export the report. The error also occurs when you click Print Preview on the Preview tab in Report Designer.
614 SQL Server 2005 Reporting Services may intermittently display a System.NullReferenceException exception in the ReportSnapshot.EnsureAllStreamsAreClosed procedure. This behavior generates a mini-dump.
615 The SQL Server 2005 Analysis Services service does not start when you use Japanese_Unicode_CI_AS as a collation for the instance of SQL Server 2005 Analysis Services.
617 When you try to run a linked report that uses the User!UserID variable and the execution properties are set to use Snapshots, you receive the following error message:
3wp!library!1!1/27/2006-13:10:27:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
627 In the Japanese version of SQL Server 2005, the fulltext schedule creation in SQL Server Management Studio fails, and you receive the following error message:
Specified @category_name ('フルテキスト'localized string) not found (Microsoft SQL Server, Error: 14262)
636 A memory leak may occur in the common language runtime (CLR) if you pass a CLR user-defined data type as a parameter by using a stored procedure or the sp_executesql procedure.
652 If Y-axis margins are enabled and the minimum Y-value of all data points is a "nice" integer value, the Y-axis minimum value will be adjusted to a lower value. For example, the chart control rounds 4.0 to 4 to create a "nice" integer value. However, the generated Y-axis labels are incorrect.
664 You run SQL Server 2005 Analysis Services builds 9.00.1520 through 9.00.1539 or SQL Server 2005 SP1. In this case, an MDX query that includes a distinct count measure may not return results. This behavior only occurs if the measure group that is queried contains multiple partitions.
708 The SQL Server Integration Services (SSIS) service does not start when you install SQL Server 2005 SP1
752 The MSMQTask.dll and dependencies are deleted when the fix that is described in the Microsoft Knowledge Base article 910070 is applied.
40000100 If the log reader agent and the DBCC DBREINDEX or ALTER INDEX ...REBUILD commands run at the same time, the log reader agent may miss some transactions to the distributor.
40000102 When you configure Log Shipping and add two or more secondary servers to Log Shipping, only the secondary server that you added last is monitored by the monitor server and the other secondary servers are not.
40000108 The SQL Server 2005 Database Engine Tuning Advisor may unexpectedly exit if indexed views are part of the workload that is tuned.
40000110 The YTD calculation in cubes that use parent-child dimensions may be incorrect.
50000104 The SqlCommandBuilder.DeriveParameters(SqlCommand) method returns an exception when you execute a stored procedure with an input parameter that is a typed XML type.
50000112 A member of the SQL Server 2005 Analysis Services database administrator role cannot add or delete perspectives. Only a member of the SQL Server 2005 Analysis Services server role can perform such tasks.
50000115 A Report Server Model may use an Analysis Services data source. MDX queries are generated from Report Builder based on the Report Server Model. Dates in the MDX queries have the month and day parts transposed.
50000116 A FETCH operation that uses a KEYSET cursor may be slow if the target table for the cursor has a clustered index and non-clustered indexes. This behavior occurs if the clustered index is the best choice for the FETCH operation and if the SELECT query for the cursor is very simple.
50000120 In SQL Server 2005 Analysis Services, calculated members that are defined in the cube MDX script are resolved before any session or query defined calculations. The only exceptions are certain scenarios with the Aggregate and VisualTotals functions. This behavior is a change from the SQL Server 2000 Analysis Services behavior. In SQL Server 2000 Analysis Services, the Solve Order could be used explicitly to insert a session or a query-defined calculation between two cube level calculations. This change in behavior may cause the query or session scope calculated members not to return the results that you want in SQL Server 2005 Analysis Services. Cumulative hotfix package for SQL Server 2005 (build 9.0.2153) introduces new syntax that allows for control over the scope in which a calculated member is evaluated.
50000151 When you run a SELECT query to return any duplicate rows in a table, incorrect results are returned if the query plan contains a hash aggregation and if one or more of the columns in the table contain a combination of empty strings and undefined characters.
50000154 You may receive a variablelock list error message when you run multiple instances of a package.

You can download the hotfix here

Friday, May 19, 2006

Customers who bought this item also bought...

Customers who bought this item also bought....by now you probably know that I am talking about Amazon.com
So let's try to implement our own (simple) Amazon system
We will call it People who bought this song also bought these songs
So basically we will create 3 tables (ArtistTable, Songtable and SongPaid)
These are very basic tables and they are missing a lot of info, I created them so that the query is nice and simple
So if person A buys song 3 then we want to return all the songs (except song 3) from customers who also bought song 3 (except customer A)
we we also list how many times a song was bought, so that we can see what the most popular songs were that people bought who also bought the same song that we bought
Let's create our tables first

CREATE TABLE ArtistTable (ID INT PRIMARY KEY, ArtistName VARCHAR(500))
INSERT INTO ArtistTable VALUES(1,'Red Hot Chilli Peppers')
INSERT INTO ArtistTable VALUES(2,'Pearl Jam')
INSERT INTO ArtistTable VALUES(3,'Astral Projection')
INSERT INTO ArtistTable VALUES(4,'Chemical Brothers')

CREATE TABLE Songtable (ID INT PRIMARY KEY , SongName VARCHAR(500),ArtistID INT)
INSERT INTO Songtable VALUES(1,'Under The Bridge',1)
INSERT INTO Songtable VALUES(2,'Black',2)
INSERT INTO Songtable VALUES(3,'Mahadeva',3)
INSERT INTO Songtable VALUES(4,'Block Rockin Beats',4)
INSERT INTO Songtable VALUES(5,'Setting Sun',4)
INSERT INTO Songtable VALUES(6,'Alive',1)
INSERT INTO Songtable VALUES(7,'Give It Away Now',2)

CREATE TABLE SongPaid (SongID INT, CustID INT)
INSERT INTO SongPaid VALUES(1, 1)
INSERT INTO SongPaid VALUES(2, 1)
INSERT INTO SongPaid VALUES(1, 2)
INSERT INTO SongPaid VALUES(3, 3) -- Current Buyer (3)
INSERT INTO SongPaid VALUES(3, 4) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(4, 4) -- Also bought by 4 ('Block Rockin Beats')
INSERT INTO SongPaid VALUES(5, 4) -- Also bought by 4 ('Setting Sun')
INSERT INTO SongPaid VALUES(4, 5)
INSERT INTO SongPaid VALUES(4, 6)
INSERT INTO SongPaid VALUES(5, 6)
INSERT INTO SongPaid VALUES(3, 7) -- Another Buyer (4)
INSERT INTO SongPaid VALUES(7, 7) -- Also bought by 7 ('Give It Away Now')
INSERT INTO SongPaid VALUES(4, 7) -- Also bought by 7 ('Block Rockin Beats')

CREATE INDEX ix_SongPaid ON SongPaid(SongID,CustID)

/*Now let's do the query with person 3 and song 3
our result set will be

SongID,SongName,ArtisName,SongCount
4,Block Rockin Beats,Chemical Brothers,2
5,Setting Sun Chemical Brothers,1
7,Give It Away Now,Pearl Jam,1

*/

DECLARE @SongId INT, @CustId INT
SELECT @SongId = 3,@CustId =3

SELECT s1.SongID ,st.Songname,a.ArtistName, Count(*) AS SongCount
FROM SongPaid s1
JOIN Songtable st on s1.SongID = st.ID
JOIN ArtistTable a on st.ArtistID = a.ID
WHERE EXISTS (
SELECT custid FROM SongPaid
WHERE SongID =@SongId AND CustID =s1.CustID )
AND SongID <> @SongId --Don't return the song we bought
AND CustID <> @CustId --Skip other songs we bought by skipping our customerid
GROUP BY s1.SongID ,st.Songname,a.ArtistName

The songs and bands I used in the tables are real so in case you don't know them here is some additional info with an Amazon link to the CD
'Under The Bridge' and 'Give It Away Now' are from the CD Blood Sugar Sex Magik by the Red Hot Chilli Peppers
'Black' and 'Alive' are from the CD Ten by Pearl Jam
'Mahadeva' is from the CD In The Mix by Astral Projection
'Setting Sun' and 'Block Rockin Beats'is from the CD Singles 93-03 by the Chemical Brothers

Wednesday, May 17, 2006

List All Tables Without An Index

Here are 2 quick ways to list all tables in your database without an index
The first query is using sysobjects and the second query is using the INFORMATION_SCHEMA.TABLES view

SELECT name AS TableName
FROM sysobjects s
WHERE type = 'U'
AND OBJECTPROPERTY(id , 'TableHasIndex' ) = 0
ORDER BY TableName

SELECT TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME) , 'TableHasIndex' ) = 0
ORDER BY TableName

Tuesday, May 16, 2006

SQL Compare Without The Price Tag

You want to know the differences between 2 tables but don’t have the money to pay for SQL Compare by red-gate software and your trial version has expired ;-(
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)

In order to see what rows are in table1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…

--let's copy over 20 rows to a table named authors2
SELECT TOP 20 * INTO tempdb..authors2
FROM pubs..authors

--update 5 records by appending X to the au_fname
SET ROWCOUNT 5

UPDATE tempdb..authors2
SET au_fname =au_fname +'X'

--Set rowcount back to 0
SET ROWCOUNT 0

--let's insert a row that doesn't exist in pubs
INSERT INTO tempdb..authors2
SELECT '666-66-6666', au_lname, au_fname, phone, address, city, state, zip, contract
FROM tempdb..authors2
WHERE au_id ='172-32-1176'

--*** The BIG SELECT QUERY --***

--Not in Pubs
SELECT 'Does Not Exist On Production',t2.au_id
FROM pubs..authors t1
RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
UNION ALL
--Not in Temp
SELECT 'Does Not Exist In Staging',t1.au_id
FROM pubs..authors t1
LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
WHERE t2.au_id IS NULL
UNION ALL
--Data Mismatch
SELECT 'Data Mismatch', t1.au_id
FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
WHERE CheckSum1 <> CheckSum2

--Clean up
DROP TABLE tempdb..authors2
GO

You can run this whole script in 1 shot

Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare

I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation

[Edit]
SQL server MVP Louis Davidson has posted a comment and this was his approach
Basically a FULL OUTER JOIN instead of a UNION
So here is his query

SELECT CASE WHEN t1.au_id IS NULL
AND t2.au_id IS NOT NULL
THEN 'Does Not Exist On Production'
WHEN t1.au_id IS NOT NULL
AND t2.au_id IS NULL
THEN 'Does Not Exist In Staging'
ELSE 'Data Mismatch' END,
COALESCE(t1.au_id, t2.au_id) AS au_id
FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
ON t1.au_id =t2.au_id
WHERE t1.au_id IS NULL
OR t2.au_id IS NULL
OR t1.bc <> t2.bc

Louis is also the author of the just released Pro SQL Server 2005 Database Design and Optimization book

[/edit]

Monday, May 15, 2006

Convert Millisecond To "hh:mm:ss" Format

This question was posted in the Transact-SQL MSDN Forum
You have a column in your table that stores duration in milliseconds
You would like to output this in the following format ‘hh:mm:ss’
So for example 11354013 would be 03:09:14:013 and 86399998 milliseconds would be 23:59:59:997 But be careful because 86399998 will return 23:59:59:997 but 86399999 will return 00:00:00:000 (it will round up to 1 day, 0 hours etc etc)

So let’s see how to do that. Basically all you have to do is add the milliseconds to a zero date and then convert that to varchar with a style of 114 (hh:mi:ss:mmm(24h))

DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =11354013

SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--03:09:14:013
GO

DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399998

SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--23:59:59:997
GO

DECLARE @SomeMilliSecondsNumber INT
SELECT @SomeMilliSecondsNumber =86399999

SELECT CONVERT(VARCHAR,DATEADD(ms,@SomeMilliSecondsNumber,0),114)
--00:00:00:000
GO

Friday, May 12, 2006

Syntax Error Converting The Varchar Value To A Column Of Data Type Int

There was a question today on the microsoft.public.sqlserver.programming forum where a person had a problem with an in predicate
The error that was raised was 'Syntax error converting the varchar value '33333A' to a column of data type int'
The error is a little bit misleading
Let's say you have the following query

SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)

And you get that error, you might think I am not converting anything why am I getting this error?
If the query was the one below then it would make sense

SELECT CONVERT(INT,SomeColumn) FROM SomeTable...

Now let's see what causes this error

--Create the test table and insert 3 rows of data
CREATE TABLE SomeTable (id INT,SomeColumn VARCHAR(49))
INSERT INTO SomeTable VALUES(1,'12345')
INSERT INTO SomeTable VALUES(1,'22222')
INSERT INTO SomeTable VALUES(1,'33333')

--let's select from that table
--no problem
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)

--Let's insert something with a non numeric value
INSERT INTO SomeTable VALUES(1,'33333A')

--Let's try again
SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)
--Oops error
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '33333A' to a column of data type int.

--The trick is to put quotes around the values
--And now it works
SELECT * FROM SomeTable WHERE SomeColumn in ('12345','22222')

--Clean up
DROP TABLE SomeTable

Changing The Schema Of A Table In SQL Server 2005

Since I am still in unpacking and putting up light-fixtures mode I decided to link to a cool post on the SQL Doctor's blog
It covers the process of changing the schema for a table
All you need to do is this
ALTER SCHEMA [schemaName] TRANSFER [otherSchemaName].[objectName]

Here is the link (http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!1041.entry)

And don't let Fred, Barney, Wilma or Betty fool you

Thursday, May 11, 2006

String Or Binary Data Would Be Truncated

I have seen a couple of Google searches hitting this blog with the search 'String or binary data would be truncated'
Basically what the error message 'String or binary data would be truncated' means is that the data that you are trying to fit into a column inside a table won't fit because the column isn't wide enough
A lot of times this occurs when you are inserting data into 1 table from another table

For example you have a table on some database server and you will need to import that data into a new table
You will have to store Unicode in the future so you make one of the columns nvarchar, you don't bother to check for the maximum length of the data in the original table and just make you column nvarchar(4000) (the max in SQL server 2000)
Now let's test a couple of these cases

--Create the 'original' table
CREATE TABLE TestData (ID int,
SomeValue VARCHAR(5000),
SomeOtherValue VARCHAR(6))

--add some data
INSERT INTO TestData VALUES (1,REPLICATE('A',4002),'abcdef')
INSERT INTO TestData VALUES (2,'123','abcde')
INSERT INTO TestData VALUES (3,'123','abcdef')

--Create the 'new' table
CREATE TABLE TestDataTruncate2 (ID INT,
SomeValue NVARCHAR(4000),
SomeOtherValue VARCHAR(5)) --Oops 1 less than in the original table

--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =1
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Fails because nvarchar can only hold 4000 characters
*/

--No Problem
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =2

--Fails
INSERT INTO TestDataTruncate
SELECT * FROM TestData
WHERE ID =3
/*
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Fails because SomeOtherValue has 6 charaters of data
but the column is only 5 characters in the new table
*/

--Instead of opening up 2 windows and comparing the tables column by column
--you can use the query below to return all that info in a resultset

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestDataTruncate'
UNION ALL
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TestData'
ORDER BY ORDINAL_POSITION,TABLE_NAME

--you can also create a self join and return only the columns
--where the name is the same but the column size is different
SELECT t1.COLUMN_NAME,t1.DATA_TYPE,
t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH,t1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS t1
JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t1.COLUMN_NAME =t2.COLUMN_NAME AND t1.TABLE_NAME <> t2.TABLE_NAME
WHERE t1.TABLE_NAME ='TestDataTruncate'
AND t2.TABLE_NAME ='TestData'
AND t1.CHARACTER_MAXIMUM_LENGTH <> t2.CHARACTER_MAXIMUM_LENGTH

--Strangely enough when you try to assign values to a variable
--it will truncate it without an error message
DECLARE @chvCity VARCHAR(8)
SELECT @chvCity ='Princeton'
SELECT @chvCity

--Clean up this mess ;-)
DROP TABLE TestDataTruncate,TestData

Tuesday, May 09, 2006

Moved Into A New House

I have moved into a new house over the last week. If you are thinking of selling your house and buying a new one at the same time then either wait until you hit the jackpot/win the lotto or wait until your old house is paid off. This has been the most stressful experience of my life. The buyer of my house was giving me a hard time, then she couldn’t get a mortgage commitment and after that she decided to switch mortgage companies. Nothing was certain until the last millisecond. And the fact that my wife is 7 months pregnant with twins didn’t help either with this ordeal. I won’t have an internet connection until this weekend so I probably won’t post a lot this week
This post and the one that I will do after the twins are born are probably the only two non SQL related posts that will ever be on this blog, hopefully you don’t mind

SQL Server Code,Tips and Tricks, Performance Tuning