Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Browsing "gotcha"

1

When should you store @@ROWCOUNT into a variable?

There was a question I answered the other day where someone complained that the rowcount was always 0. Below is a simplified version of the query, can you tell why @SomeCount will be 0? T-SQL1 2 3 4 5 6 7 8 9 10 declare @SomeCount int   select 1 union all select 2   […]

Read More...
6

Dealing with The multi-part identifier “dbo.Table.Column” could not be bound. error in an update statement

One of the best ways to improve your skills is by helping other people in forums and newsgroups. I was doing just that tonight and I stumbled on this piece of code here: http://stackoverflow.com/questions/3622685/transfer-column-data-from-one-database-to-another T-SQL1 2 3 4 5 update [DB1].[dbo].[Table1] set [DB1].[dbo].[Table1].[Column1] = [DB2].[dbo].[Table1].[Column1] from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias where db1Alias.TeamId = db2Alias.TeamId and […]

Read More...
2

Interesting UNION with ORDER BY behavior

Here is something interesting to think about Create this table and insert these 2 rows T-SQL1 2 3 create table TableName(id int, name varchar(50)) insert TableName values(1,’bla’) insert TableName values(2,’bla2’) create table TableName(id int, name varchar(50)) insert TableName values(1,’bla’) insert TableName values(2,’bla2′) Now if you try to do something like this T-SQL1 2 3 4 […]

Read More...
4

Dealing with Cannot resolve collation conflict for equal to operation errors

This was asked on twitter recently and I gave the answer there. I decided to write a blog post about this because I can use over 140 charaters here instead. You will see the Cannot resolve collation conflict for equal to operation error when you try to join 2 tables. let’s take a look at […]

Read More...
2

When Books On Line is not really correct

A question was posted in our SQL Server programming forum today. A person had this stored procedure T-SQL1 2 3 4 5 6 7 CREATE PROCEDURE TestStuff @id INT, @Val1 VARCHAR(20), @Val2 VARCHAR(20) AS   SELECT @id,@Val1,@Val2 CREATE PROCEDURE TestStuff @id INT, @Val1 VARCHAR(20), @Val2 VARCHAR(20) AS SELECT @id,@Val1,@Val2 Executing it like this works T-SQL1 […]

Read More...
0

Dealing with the could not allocate new page for database ‘TEMPDB’. There are no more pages available in filegroup DEFAULT error message

Someone was writing some queries that brought back a lot of data (and I mean a LOT!!) and after a while he got the following message Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate new page for database ‘TEMPDB’. There are no more pages available in filegroup DEFAULT. Space can be created by dropping […]

Read More...
8

The differences between LEN and DATALENGTH in SQL Server

The differences between LEN and DATALENGTH in SQL Server! I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion. First let’s take a look what Books On Line has to say about these two functions LEN Returns the number of characters, rather than […]

Read More...
5

Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008

I have seen enough questions about this lately and this means that it is time for a blogpost. SQL Server 2008 has a bunch of new data types and one of them is the date datatype. If you don’t care for the time portion of the date you can now use the date data type […]

Read More...
4

What is deferred name resolution and why do you need to care?

So I posted a teaser in the puzzles forum. Without running this, try to guess what will happen? T-SQL1 2 3 4 5 6 7 8 9 10 11 12 13 14 DECLARE @x INT   SET @x = 1   IF (@x = 0) BEGIN     SELECT 1 AS VALUE INTO #temptable END […]

Read More...
« Previous Page