How to show the current datasource in a report?

I'm using Reporting Services 2000 on SQL Server 2000. I want to show the currently used datasource to avoid troubles when switching between test data and productive data. Is it possible to show the name and/or the description of the used datasource?

Thanks for your answer,
AndreasYou could try selecting @.@.servername, db_name()
along with your query.|||

Unfortunalty servername and database name ist not helpful for the user and works only on MS Sql Server. Isn't there a possibility to get the abstract name and description of the current configured datasource?
Thanks for your answer,
Andreas

|||Is there really no possibility?

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
=B7 I have verified that yes; 'Database Mail' is started in surface
area configuration.
=B7 I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
=B7 I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id =3D 2
principal_name =3D guest
profile_id =3D 2
profile_name =3D TestMailProfile
is_default =3D 1
Why does;
principal_name =3D guest
instead of my user name?
=B7 I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type =3D 'mail' ;
Which showed the state =3D INACTIVE
Why? Shouldn't it be active?
=B7 I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
Which showed 'sent_status' column as "FAILED"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which also shows the same error as above...
=B7 I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
=B7 I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name =3D 'msdb' ;
Which shows that the broker is enabled.
=B7 I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
--
There are no operators with email addresses defined on this server.
--
BUTTONS: OK
--
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:

> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I think this is to do with your exchange server's SMTP settings rather tha
n
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
>

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
I have verified that yes; 'Database Mail' is started in surface
area configuration.
I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id = 2
principal_name = guest
profile_id = 2
profile_name = TestMailProfile
is_default = 1
Why does;
principal_name = guest
instead of my user name?
I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
Which showed the state = INACTIVE
Why? Shouldn't it be active?
I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 8 ;
Which showed 'sent_status' column as "FAILED"
I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = 8 ;
Which also shows the same error as above...
I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb' ;
Which shows that the broker is enabled.
I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
There are no operators with email addresses defined on this server.
BUTTONS: OK
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?
Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:

> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>
|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I think this is to do with your exchange server's SMTP settings rather than
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:

How to setup Database Mail for SQLS 2005

Hello,
I have attempted to send test email from SQL Server Management Studio -
Management - Database Mail, without success.
Current setup information...
System information:
The OS is Windows XP SP2.
SQL Server 2005 Developer Edition (9.0.2047)
IIS is installed and IIS admin service is started
SMTP service is installed and SMTP service is started
I am logged in as the administrator.
The environment:
This computer is on a network that has exchange running. This computer
also has MS Outlook installed. I know that Database Mail does not need
either, but those are the facts.
This is my personal network. I am trying to learn how to setup Database
Mail. I am trying to set up mail to send email feedback for processes
such as database maintenance plans and SSIS processes.
These are the steps I have taken to troubleshoot my test email problem:
=B7 I have verified that yes; 'Database Mail' is started in surface
area configuration.
=B7 I ran:
USE msdb
GO
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
which showed the 'sent_status' column showing "Failed" and the
'last_mod_user' column showing "This is a test e-mail sent from
Database Mail on KEPLER"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which showed the column 'description' showing "The mail could not
be sent to the recipients because of the mail server failure. (Sending
Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
send mails to mail server. Mailbox unavailable. The server response
was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
=B7 I ran:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
Which showed that yes I am a member of DatabaseMailUserRole.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Which showed the following values for each column in the one profile;
principal_id =3D 2
principal_name =3D guest
profile_id =3D 2
profile_name =3D TestMailProfile
is_default =3D 1
Why does;
principal_name =3D guest
instead of my user name?
=B7 I ran:
EXEC msdb.dbo.sysmail_start_sp
Which showed that mail is started.
=B7 I ran:
EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type =3D 'mail' ;
Which showed the state =3D INACTIVE
Why? Shouldn't it be active?
=B7 I ran:
SELECT sent_account_id, sent_date
FROM msdb.dbo.sysmail_sentitems ;
Which returned no records.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log ;
Which showed many errors for the many times I tried to send a test
message.
The 'description' colums for each of these errors has the same
error as shown above.
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id =3D 8 ;
Which showed 'sent_status' column as "FAILED"
=B7 I ran:
SELECT *
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id =3D 8 ;
Which also shows the same error as above...
=B7 I verified that I am a user in the msdb database, tha I am a member
of the DatabaseMailUserRole database role in the msdb database, and
that I am a member of sysadmin in the Security Server Roles.
=B7 I ran:
SELECT is_broker_enabled
FROM sys.databases
WHERE name =3D 'msdb' ;
Which shows that the broker is enabled.
=B7 I ran:
USE msdb
go
EXECUTE dbo.sysmail_help_status_sp ;
Which shows that the status is started.
When I tried to setup logging for a Maintenance Plan I got the
following error:
TITLE: Microsoft SQL Server Management Studio
--
There are no operators with email addresses defined on this server.
--
BUTTONS: OK
--
What does this mean?
I also set up a SSIS project where I tried to have mail inform of
success or failure. I got the same error message as above.
I trying to trouble shoot this problem I read and follow all I could
find on troubleshooting this problem.
What am I missing? What should I do next?Hi
I think this is to do with your exchange server's SMTP settings rather than
database mail itself, or possibly who you are setting as the sender and
recipient.
John
"dbuchanan" wrote:
> Hello,
> I have attempted to send test email from SQL Server Management Studio -
> Management - Database Mail, without success.
> Current setup information...
> System information:
> The OS is Windows XP SP2.
> SQL Server 2005 Developer Edition (9.0.2047)
> IIS is installed and IIS admin service is started
> SMTP service is installed and SMTP service is started
> I am logged in as the administrator.
> The environment:
> This computer is on a network that has exchange running. This computer
> also has MS Outlook installed. I know that Database Mail does not need
> either, but those are the facts.
> This is my personal network. I am trying to learn how to setup Database
> Mail. I am trying to set up mail to send email feedback for processes
> such as database maintenance plans and SSIS processes.
> These are the steps I have taken to troubleshoot my test email problem:
> · I have verified that yes; 'Database Mail' is started in surface
> area configuration.
> · I ran:
> USE msdb
> GO
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> which showed the 'sent_status' column showing "Failed" and the
> 'last_mod_user' column showing "This is a test e-mail sent from
> Database Mail on KEPLER"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which showed the column 'description' showing "The mail could not
> be sent to the recipients because of the mail server failure. (Sending
> Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> send mails to mail server. Mailbox unavailable. The server response
> was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> · I ran:
> EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> Which showed that yes I am a member of DatabaseMailUserRole.
> · I ran:
> EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> Which showed the following values for each column in the one profile;
> principal_id = 2
> principal_name = guest
> profile_id = 2
> profile_name = TestMailProfile
> is_default = 1
> Why does;
> principal_name = guest
> instead of my user name?
> · I ran:
> EXEC msdb.dbo.sysmail_start_sp
> Which showed that mail is started.
> · I ran:
> EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> Which showed the state = INACTIVE
> Why? Shouldn't it be active?
> · I ran:
> SELECT sent_account_id, sent_date
> FROM msdb.dbo.sysmail_sentitems ;
> Which returned no records.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log ;
> Which showed many errors for the many times I tried to send a test
> message.
> The 'description' colums for each of these errors has the same
> error as shown above.
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_allitems
> WHERE mailitem_id = 8 ;
> Which showed 'sent_status' column as "FAILED"
> · I ran:
> SELECT *
> FROM msdb.dbo.sysmail_event_log
> WHERE mailitem_id = 8 ;
> Which also shows the same error as above...
> · I verified that I am a user in the msdb database, tha I am a member
> of the DatabaseMailUserRole database role in the msdb database, and
> that I am a member of sysadmin in the Security Server Roles.
> · I ran:
> SELECT is_broker_enabled
> FROM sys.databases
> WHERE name = 'msdb' ;
> Which shows that the broker is enabled.
> · I ran:
> USE msdb
> go
> EXECUTE dbo.sysmail_help_status_sp ;
> Which shows that the status is started.
> When I tried to setup logging for a Maintenance Plan I got the
> following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> There are no operators with email addresses defined on this server.
> --
> BUTTONS: OK
> --
> What does this mean?
> I also set up a SSIS project where I tried to have mail inform of
> success or failure. I got the same error message as above.
> I trying to trouble shoot this problem I read and follow all I could
> find on troubleshooting this problem.
> What am I missing? What should I do next?
>|||check your port to make sure you can send out via port 25. Anti Virus
sofatware blocks that port
stoney
"John Bell" wrote:
> Hi
> I think this is to do with your exchange server's SMTP settings rather than
> database mail itself, or possibly who you are setting as the sender and
> recipient.
> John
> "dbuchanan" wrote:
> > Hello,
> >
> > I have attempted to send test email from SQL Server Management Studio -
> > Management - Database Mail, without success.
> >
> > Current setup information...
> >
> > System information:
> > The OS is Windows XP SP2.
> > SQL Server 2005 Developer Edition (9.0.2047)
> > IIS is installed and IIS admin service is started
> > SMTP service is installed and SMTP service is started
> > I am logged in as the administrator.
> >
> > The environment:
> > This computer is on a network that has exchange running. This computer
> > also has MS Outlook installed. I know that Database Mail does not need
> > either, but those are the facts.
> >
> > This is my personal network. I am trying to learn how to setup Database
> > Mail. I am trying to set up mail to send email feedback for processes
> > such as database maintenance plans and SSIS processes.
> >
> > These are the steps I have taken to troubleshoot my test email problem:
> >
> > · I have verified that yes; 'Database Mail' is started in surface
> > area configuration.
> >
> > · I ran:
> >
> > USE msdb
> > GO
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > which showed the 'sent_status' column showing "Failed" and the
> > 'last_mod_user' column showing "This is a test e-mail sent from
> > Database Mail on KEPLER"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which showed the column 'description' showing "The mail could not
> > be sent to the recipients because of the mail server failure. (Sending
> > Mail using Account 1 (2006-11-03T17:56:16). Exception Message: Cannot
> > send mails to mail server. Mailbox unavailable. The server response
> > was: 5.7.1 Unable to relay for dbuchanan75@.comcast.net). )
> >
> > · I ran:
> >
> > EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' ;
> >
> > Which showed that yes I am a member of DatabaseMailUserRole.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
> >
> > Which showed the following values for each column in the one profile;
> >
> > principal_id = 2
> > principal_name = guest
> > profile_id = 2
> > profile_name = TestMailProfile
> > is_default = 1
> >
> > Why does;
> > principal_name = guest
> > instead of my user name?
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_start_sp
> >
> > Which showed that mail is started.
> >
> > · I ran:
> >
> > EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail' ;
> >
> > Which showed the state = INACTIVE
> > Why? Shouldn't it be active?
> >
> > · I ran:
> >
> > SELECT sent_account_id, sent_date
> > FROM msdb.dbo.sysmail_sentitems ;
> >
> > Which returned no records.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log ;
> >
> > Which showed many errors for the many times I tried to send a test
> > message.
> >
> > The 'description' colums for each of these errors has the same
> > error as shown above.
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_allitems
> > WHERE mailitem_id = 8 ;
> >
> > Which showed 'sent_status' column as "FAILED"
> >
> > · I ran:
> >
> > SELECT *
> > FROM msdb.dbo.sysmail_event_log
> > WHERE mailitem_id = 8 ;
> >
> > Which also shows the same error as above...
> >
> > · I verified that I am a user in the msdb database, tha I am a member
> > of the DatabaseMailUserRole database role in the msdb database, and
> > that I am a member of sysadmin in the Security Server Roles.
> >
> > · I ran:
> >
> > SELECT is_broker_enabled
> > FROM sys.databases
> > WHERE name = 'msdb' ;
> >
> > Which shows that the broker is enabled.
> >
> > · I ran:
> >
> > USE msdb
> > go
> > EXECUTE dbo.sysmail_help_status_sp ;
> >
> > Which shows that the status is started.
> >
> > When I tried to setup logging for a Maintenance Plan I got the
> > following error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> > There are no operators with email addresses defined on this server.
> > --
> > BUTTONS: OK
> > --
> >
> > What does this mean?
> >
> > I also set up a SSIS project where I tried to have mail inform of
> > success or failure. I got the same error message as above.
> >
> > I trying to trouble shoot this problem I read and follow all I could
> > find on troubleshooting this problem.
> >
> > What am I missing? What should I do next?
> >
> >sql

How to set the current connectionString user as a defualt value?

Hi,

I'm using username & password in my connectionString to connect my program (C#.net) with Sql Server 2005..

I put in defualt value in one feild: user_name()

it replace the user: dbo, not the user which I used in my connectionString.

so.. how to get this user?

I tried to put: currnet_user

but I get the same result, "dbo" not the user in my connectionString..

I tried SESSION_USER, it gave the same result :(|||

This is my connectionString:

connectionString="Provider=SQLOLEDB;Data Source=LEWEHSBF\LEWEENG;Persist Security Info=True;Password=HsBfL4w4;User ID=Lewe;Initial Catalog=MyDatabase"

|||

I think the resone is the user I which I used in myConnectionString, is Admin user, I tried with "dataread, datawrite" user and its worked..

Thank you myself

How to SET STATISTICS ON

Hi Folks,
Under Current Activities > Process Info, the cpu information is 0 when
Set Statistics is off, so I want to set it to be on at the database
level so that I am able to capture all in the cpu info?
Thanks
Ata JohnThe Current Activities > Process Info has nothing to do with SET STATISTICS
on/off. The Process Info is just a GUI display of the information in
master..sysprocesses.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Ata John" <ataJohn@.ata1.com> wrote in message
news:3F6F62CB.C3F2E6FB@.ata1.com...
> Hi Folks,
> Under Current Activities > Process Info, the cpu information is 0 when
> Set Statistics is off, so I want to set it to be on at the database
> level so that I am able to capture all in the cpu info?
> Thanks
> Ata John
>

How to set default date

Hi Guys,

I have a time standard dimension used as a filter to my report.

How can I set the parameter to get the current month and year to be used by the time standard filter.

Select the report, and go to Report->Parameters, select the parameter, in the default values sectoin, set the value there. You can use function too, like get current date, use =Today, and DatePart() to get year, month, date, whatever.

How To Set Column Default Value to include Text AND GetDate() ?

GETDATE() works alone as default in a DateTime column, but:

1) How do you include current Date concatenated with text like:

- Column default Value = 'Submitted 2007-07-30 13:15:54.953'

2) is it possible to truncate that DateTime value (deleting the seconds 54.953)

- Final Column default Value = 'Submitted 2007-07-30 13:15'

Thinking Cast or convert but unsuccessful to date:

- 'Submitted ' + Cast(CHAR(16),GETDATE()) ?

I think the short answer here is, 'Yes'; it is just a matter of getting the format that you want. Here is an example:

Code Snippet

declare @.what table (aVc varchar(30) default('Submitted ' + left(convert(varchar, getdate(),120), 16)) )
insert into @.what default values

select * from @.what

/*
aVc
Submitted 2007-07-30 13:15
*/

Giving it another look you might want to try making your default

default('Submitted ' + convert(varchar(16), getdate(),120))

Might simplify the expression a bit.

|||Thanks Kent!