Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Wednesday, 24 October 2012

Could not find data type X.Y when creating a temporary table

Just a reminder: I was trying to create a temporary table in SQLServer 2k8 using a user-defined data type, a bit like:

use MyDB;

create table #myTempTable
(
    MyField udt.MyType_t not null
)

but hit

Could not find data type udt.MyType_t

This is because the type is only defined in my database MyDB – it’s not defined in TEMPDB. When you try to use it when creating a temporary table the database can’t find it.

The workaround is to use the underlying data type instead.

Thursday, 27 September 2012

State 1, Line 1 Incorrect syntax near ‘go’.

This stumped me for a short while. I had the following straightforward SQLServer batch:

if object_id( N'util.AllBatchProgress' ) is not null
    drop procedure util.AllBatchProgress;
go

and was taken aback when I hit this error when running it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'go'.

It looked absolutely fine to me; Go needs to stand apart from your SQL batch, on its own line, and here it does. 

But what’s this about line 1? How could this look like a 1 line? Ah, maybe if I'd copied it from Notepad++ (as I did) without realising the mode I was in, I'd have something like:

error-near-go

(this time with all symbols shown). I guess SSMS is not parsing those CR carriage returns -- should be CRLF. Converting the line endings to Windows format (again in Notepad++) got it working.