MS SQL Server 2005 Express / Management Studio Express

Pls me with the 2, possibly simple, problems.

************

I've got the following table in a test sql script. Complains that
'Base.PK_T' cannot be found. Have checked that the correct database is
referred. I know the the script works because when I change the types
to the SQL-Server types (PK_T -> int, Char1_t -> char(1)) the script
works fine. So does this mean that User Defined Types *cannot* be used
in temp tables? What could i be doing wrong?

create table #setting (
ID Base.PK_T,
SType Base.Char1_T,
SKey Base.SKey_T,
SValue Base.SValue_T
)
insert into #setting execute base.settingsearch 's'
if @@rowcount <> 1 print 'Error: SettingSearch: 1'

************

I have setup Database Mail using SPs (GUI not available in Express).
Try to send a test mail and gets the msg 'Mail Queued'. However, it
stays unsent. I have sent email from my mail client with the same
settings (smtp server)

I took a look at msdb.dbo.sysmail_event_log.
EventType: information, Description: mail queued
EventType: error, Description: Activation Failure

**************

> So does this mean that User Defined Types *cannot* be used
> in temp tables? What could i be doing wrong?

The UDT need to be available in the tempdb database (since this is where temp tables lives).
Typically, you create the UDTs in the model database as well, since tempdb is re-create on each
startup - based on model.



> I took a look at msdb.dbo.sysmail_event_log.
> EventType: information, Description: mail queued
> EventType: error, Description: Activation Failure


I don't know, but you might want to post this to the Service Broker group, since this is what DB
Mail is using internally and the error seem to be SB related.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Riyaz Mansoor" <riyaz.mansoor@gmail.com> wrote in message
news:bf1f0cb5-d556-41d3-a7ff-792a32676379@w7g2000hsa.googlegroups.com...
>
> MS SQL Server 2005 Express / Management Studio Express
>
> Pls me with the 2, possibly simple, problems.
>
> ************
>
> I've got the following table in a test sql script. Complains that
> 'Base.PK_T' cannot be found. Have checked that the correct database is
> referred. I know the the script works because when I change the types
> to the SQL-Server types (PK_T -> int, Char1_t -> char(1)) the script
> works fine. So does this mean that User Defined Types *cannot* be used
> in temp tables? What could i be doing wrong?
>
> create table #setting (
> ID Base.PK_T,
> SType Base.Char1_T,
> SKey Base.SKey_T,
> SValue Base.SValue_T
> )
> insert into #setting execute base.settingsearch 's'
> if @@rowcount <> 1 print 'Error: SettingSearch: 1'
>
> ************
>
> I have setup Database Mail using SPs (GUI not available in Express).
> Try to send a test mail and gets the msg 'Mail Queued'. However, it
> stays unsent. I have sent email from my mail client with the same
> settings (smtp server)
>
> I took a look at msdb.dbo.sysmail_event_log.
> EventType: information, Description: mail queued
> EventType: error, Description: Activation Failure
>
> **************

For Info. Though I did not get an answer in the service broker
newsgroup the answer seems to be that service broker is NOT supported
in MS SQL Server 2005 Express. Hence, mail just stays unsent. Hope
this s someone else.

http://www.microsoft.com/sql/prodinf...-features.mspx


On Jun 16, 7:27pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> > I took a look at msdb.dbo.sysmail_event_log.
> > EventType: information, Description: mail queued
> > EventType: error, Description: Activation Failure
>
> I don't know, but you might want to post this to the Service Broker group, since this is what DB
> Mail is using internally and the error seem to be SB related.