Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zippy
Yak Posting Veteran
69 Posts |
 Posted - 2002-01-07 : 05:28:28
|
| Hi,I am trying to make a temp table using User Defined Data Types however it allways brings up an error saying that the data type cannot be found!I know it is there because I am using it in a regular table.Is it possible to use them in a temp table or am I just trying to access them wrong?Here is my code (the abridged version anyway):CREATE TABLE #TempItems( ID INT IDENTITY, ProductName dtProductName,)Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-07 : 06:09:02
|
| Hey ZippyI haven't tried to do this before, but, according to BOL, the Data Type is stored in the current database. Since the temp table is in tempdb, the data type is not available to it.You have a few possibilities, once again I am kinda guessing here becuase I haven't done it.1. Create the data type in the tempdb as well.2. Create it in model DB, so it is in every new database (including tempdb as it's created at startup)3. Possibly use a table data type instead of a temp table (if using SQL 2000)There are some things you might have a play around with. Let us all know how you go with it.Good luckDamianEdited by - merkin on 01/07/2002 06:09:42 |
 |
|
|
zippy
Yak Posting Veteran
69 Posts |
Posted - 2002-01-07 : 06:12:57
|
Yip that makes perfect sense now, the temp table being in a different database...Yes I am using 2000 and I was just reading about the Table data type today!I will give that a go,The script is actually to page through results and I got that off this site, so I guess it is only fitting that I get the solution to the problem it created here as well  Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
Rafiq
Starting Member
25 Posts |
Posted - 2002-01-07 : 06:18:16
|
| Hi Zippy,Yes its absolutely correct. I will find out what is proplem of it? If you need urgend solution,do the following methods,CREATE TABLE TempItems(ID INT IDENTITY,ProductName dtProductName,)SELECT * INTO #TempItems FROM TempItems(Temporary table created successfully.)DROP TABLE TempItems(Table droped)you can use that temporary table asusal.If i find the correct answer, i will tell you.regards,Rafiq------------------------------------If you think, you can do anything... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-07 : 06:24:15
|
| HiRafiq, that won't work. If you have two users trying to execute the concurrently it will crash out as it will be trying to create two objects with the same name.Damian |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 06:28:43
|
| but you can maybeselect id = convert(int,0), ProductName = convert(dtProductName,'')into #TempItemsfrom sysobjectswhere 1 = 0==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
User Defined Data Types and Temp Tables