First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>