Problem with sqlexpress

hi:

I set up a DSN via Adim tools, then I specify the connection string as "DSN=UserAppSample;Trusted_Connection=True".

When I run my aspx page, it says:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQLServer]Cannot open database "UserAppSample" requested by the login. Thelogin failed.

It asks for login, but I use windows authentication. So what is wrong with this? :(

This is a SQL permission issue. You need to check the SQL Server logins, make sure the account which runs the application has proper permission on the UserAppSample database.

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpHi Andrew,
I am not sure I understood your issue.
Could you place DDL + Sample Data?
What I did not understand is where are the queries writen? In Access Queries
? In Delphi code? In Stored Procedures?
I hope I can help you if I get a better idea of the issue.
Bye,|||you must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregonsql

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpyou must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregon

Problem with SQL Query Analyzer

Hi,
I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the response time and I get something around 400 ms (including reading the data from the result set, a couple of MoveNext operations).
Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until I can see the results! Does anybody know why?
Is the Query Analyzer so badly implemented when it reads the results and displays them?
Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still takes about 7 seconds within the Query Analyzer.
My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on the results, because the same queries run differently once they are called from the application.
It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
Any hints/tips? Did anyone experience that before?
Thanks in advance,
Florin Micle
Execution plans can differ for several reasons. Different SET option is one potential reason. The first thing
to do is to compare the execution plans and see if they are the same or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"fmicle" <fmicle@.hotmail.com> wrote in message news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program (via COM+/OLE DB) I measure the
response time and I get something around 400 ms (including reading the data from the result set, a couple of
MoveNext operations).
> Now if I call the very same stored procedure within the SQL Query Analyzer, it takes almost 8 seconds until
I can see the results! Does anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and displays them?
> Even if I eliminate the results (by adding select top 0 in my final query in the stored procedure) it still
takes about 7 seconds within the Query Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my application and it seems I can't rely on
the results, because the same queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in the Profiler! At the beginning I
thought that the Query Analyzer adds some overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Perhaps your data happened to be cached in some instances. Try clearing the
cache using DBCC DROPCLEANBUFFERS before testing the queries using.
As Tibor mentioned, different execution plans may be another cause, which
you can also capture using Profiler.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:824E53F3-AC7C-4C1C-AFD5-06D4609F8714@.microsoft.com...
> Hi,
> I have the following problem: if I call a stored procedure from a program
(via COM+/OLE DB) I measure the response time and I get something around 400
ms (including reading the data from the result set, a couple of MoveNext
operations).
> Now if I call the very same stored procedure within the SQL Query
Analyzer, it takes almost 8 seconds until I can see the results! Does
anybody know why?
> Is the Query Analyzer so badly implemented when it reads the results and
displays them?
> Even if I eliminate the results (by adding select top 0 in my final query
in the stored procedure) it still takes about 7 seconds within the Query
Analyzer.
> My bigger problem is that I use the query analyzer to fine-tune my
application and it seems I can't rely on the results, because the same
queries run differently once they are called from the application.
> It's weird because I also get these confusing response times displayed in
the Profiler! At the beginning I thought that the Query Analyzer adds some
overhead, but why do I get different times in the Profiler?
> Any hints/tips? Did anyone experience that before?
> Thanks in advance,
> Florin Micle
|||Hi, when you look at profiler do you to capture exactly what it being run when the program calls the sp? I ask because unless the inputs are exactly the same you could get differing results. I've seen this with datetime inputs where query plans have been
different depending on the format of the date input. As Tibor says, did the query plan show anything different? I assume they must have done? You've tried the TOP 0 which should eliminate the displaying of the results and as Ray says you can drop the data
from cache (and run CHECKPOINT) to give the two approaches a level playing field. I feel the answer will be in your execution plan and I'd expect either as Tibor says, your SET options will be the root, or that it's perhaps something to do with one or mo
re of your sp inputs.
Alicia
Http://www.sqlporn.co.uk
|||Well, how am I suppose to know how exactly the query is executed and which query plan is taken?
I can see in the Profiler, that the exact same query is launched when I call it from my application and when I call it from the Query Analyzer. The query plan I can only see in the Query Analyzer, if I run my query in it, so I'll never know what plan is a
ctually taken when my query runs from the application.
That's exactly my problem, I can't tune my application properly, since the queries work differently in the Query Analyzer and from my app.
By the way, to answer your questions, the plan is always the same for my query.
I also think there are some bugs in the Profiler.
My stored procedure, let's name it SP1 calls 2 other SP's, which are very fast, let's call them SP2 and SP3.
So SP1 looks something like:
...
EXEC SP2
EXEC SP3
Do some complex queries
...
In the profiler, every time I call SP1, I get 3 SP Completed events, like this (two consecutive calls, you can see the effects of the cache, which is ok):
EventClass TextData Duration
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 453
SP:Completedexec SP1 0
SP:Completedexec SP1 0
SP:Completedexec SP1 313
Now, I call the same SP two times from the Query Analyzer, this is what I get in the profiler:
EventClass TextData Duration
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 1926
SP:Completedexec SP2 0
SP:Completedexec SP3 0
SP:Completedexec SP1 2005
The profiling mechanisms seem to be different, if the SP1 is called from the Query Analyzer it looks OK, but from my app, there seems to be a bug somwhere in the event handling in the Profiler.
Any ideas?
|||I can't believe this, if I delete the proccache (dbcc freeproccache) I get almost the same response times within QA like from my application!
About 600 ms, which is much better than 2 seconds!
What can make QA generate such a bad query plan?
Another thing: my application (C++) runs as a COM object in COM+. I wrote a small C++ program which does the exact same thing (calls the same stored procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to connect to the DB in both cas
es.
I can only imagine, that there must be some magic in COM+, which sets some parameters differently, thus making the SQL Server run faster/better when called from COM+.
Confusing...
|||> What can make QA generate such a bad query plan?
The cached plan may have been generated for a query that uses parameter
values that do not fall into the same distribution pattern that the later
query has. However, this is unlikely in your case since you mentioned that
they are both using the same query.
I suggest checking the execution plan from both sides. Use Profiler to
capture the execution plan, using the Show Plan Statistics event under the
Performance heading.
Regards
Ray Mond
"fmicle" <fmicle@.hotmail.com> wrote in message
news:D42860F2-6C23-48C0-AA90-9301F2697191@.microsoft.com...
> I can't believe this, if I delete the proccache (dbcc freeproccache) I get
almost the same response times within QA like from my application!
> About 600 ms, which is much better than 2 seconds!
> What can make QA generate such a bad query plan?
> Another thing: my application (C++) runs as a COM object in COM+. I wrote
a small C++ program which does the exact same thing (calls the same stored
procedure) and this is slower than when it runs in COM+. I use ATL OLE DB to
connect to the DB in both cases.
> I can only imagine, that there must be some magic in COM+, which sets some
parameters differently, thus making the SQL Server run faster/better when
called from COM+.
> Confusing...
|||No, you're right, it makes sense. When I call the SP from COM+, I use placeholders and Accessors to bind my parameters.
But I get the exact same query text in the profiler.
On the other hand, if I do the same thing outside COM+, even using the parameters, I still get the bad plan (WITH PREFETCH).
I can only reach almost the same speed outside COM+ if I delete the PROCCACHE.

problem with SQL 2000 Enterprise Manager display

Hello,
I'm having problems when using SQL 2000 Enterprise manager and viewing
the Distribution Agents via Enterprise Manager. I have this SQL server setup
for replication with a local a distribution database. When I click on the
'Distribution Agents' folder, under 'Replication Monitor' I can't see the
agents. It doesn't matter which Enterprise Manager from what SQL server that
I use, I still can't see the distribution agents for this one server. So,
that leads me to think there is some configuration setting for that server
that is not allowing any Enterprise Mananger's replication monitor to view
those agents.
Is this correct?
If so, how do I fix it?
thanks,
Hi,
Does your replication works swimmingly as expected?
What kind of replication you are creating?
What's the startup account for your SQL Server Agent?
If you create another new transactional replication, is it possible for you
to reproduce this behavior?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Morning Michael,
Yes, replication works as it should. We are doing transactional replication
at the database level. SQL Server agent runs as a Domain account which also
runs the SQL Server service. Each and every new database replication has the
same problems with in the 'Distribution Agents' folder. Just to repeat my
self, this is the only folder that does not show the agents. All other
folders shows the differents agents.
So, any other thoughts as to why this one folder does not show it's agents?
thanks,
JD
"Michael Cheng [MSFT]" wrote:

> Hi,
> Does your replication works swimmingly as expected?
> What kind of replication you are creating?
> What's the startup account for your SQL Server Agent?
> If you create another new transactional replication, is it possible for you
> to reproduce this behavior?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hi John,
Really strange issue since other folder displays correctly.
If you register the SQL Server in another machine, will it show the Agent
correctly?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Michael,
No unfortuanly it doesn't. I can register that one server from any other
server but it still won't display that one folder. And it is only the one
folder that won't display.
Any thoughts? A possible registry entry that is messed up? A bit turned off
somewhere?
Thanks,
JD
"Michael Cheng [MSFT]" wrote:

> Hi John,
> Really strange issue since other folder displays correctly.
> If you register the SQL Server in another machine, will it show the Agent
> correctly?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi John,
For now, I am afraid we will have to debug the application internal and
this job had to be done by contacting Microsoft Customer Service and
Support. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If the issue is urgent, I recommend that you open a
Support incident with Microsoft Customer Service and Support so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael,
Thanks for your help. I'll have to log a support call then to get it
resolved.
Joe
"Michael Cheng [MSFT]" wrote:

> Hi John,
> For now, I am afraid we will have to debug the application internal and
> this job had to be done by contacting Microsoft Customer Service and
> Support. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If the issue is urgent, I recommend that you open a
> Support incident with Microsoft Customer Service and Support so that a
> dedicated Support Professional can assist with this case. If you need any
> help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Joe,
Thanks for your understanding, once more. Your cooperation is appreciated,
and will result in a more efficient troubleshooting process.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

Hi

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @.mailserver_name or @.port parameters passed to sysmail_add_account_sp.

HTH,
~ Remus

|||

Hi

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)
"

and when I execute sysmail_help_queue_sp this would be the resuls:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

Hi

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

--

I just today installed SMTP services!!!!

and the type of error changed to this form:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)

"

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@.abc.com

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

Hi

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @.mailserver_name or @.port parameters passed to sysmail_add_account_sp.

HTH,
~ Remus

|||

Hi

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)
"

and when I execute sysmail_help_queue_sp this would be the resuls:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

Hi

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

--

I just today installed SMTP services!!!!

and the type of error changed to this form:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)

"

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@.abc.com

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

Hi

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @.mailserver_name or @.port parameters passed to sysmail_add_account_sp.

HTH,
~ Remus

|||

Hi

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)
"

and when I execute sysmail_help_queue_sp this would be the resuls:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

Hi

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

--

I just today installed SMTP services!!!!

and the type of error changed to this form:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)

"

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@.abc.com

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with sending mail via Database Mail

Hi every body

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.

I've defined a public profile.
I've enabled Database Mail stored procedures through the Surface Area Configuration .

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."

The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?

please help me.

POUYAN

Hi POUYAN,

you must have enabled the Service Broker in the msdb database.

BOL: How to: Activate Service Broker Message Delivery in Databases (Transact-SQL)

To see if the message was sent successfully, you can run a query on the sysmail_allitems system view.

SELECT * FROM sysmail_allitems|||

Hi

By default service broker is enabled in each database except 'master' & 'tempdb'

any way again I enabled service broker.

where as DatabaseMail90.exe is triggerd whene I execute sp_send_dbmail,the mail should be given to the queue.but when I execute

sysmail_help_queue_sp to see the queue the 'length' column is 0 which says there is no mail in the queue.I'm confused.

More over I think another problem is that DatabaseMail90.exe can not communicate with the machine(server) .

do u know how I can check communication settings between SQL SERVER and the server to see what's going on?

thanks.

POUYAN.

|||

Hi POUYAN,

plz check:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole': check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; check status of Database Mail

EXECUTE dbo.sysmail_start_sp: start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @.queue_type = 'mail'

|||

Pouyan wrote:

Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?

You do not need an endpoint for database mail.

Pouyan wrote:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it).

The problem has nothing to do with Service Broker. Your mailer process gets the mail to send and attempt to deliver, but ot cannot. The problems seems to be with yoyr @.mailserver_name or @.port parameters passed to sysmail_add_account_sp.

HTH,
~ Remus

|||

Hi

I 've just installed SMTP services from the "add windows component"

(I hadn't done !!!!)

but this time I'm facing a new error type:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)
"

and when I execute sysmail_help_queue_sp this would be the resuls:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

*database mail is enabled

*service broker is ebabed(is_broker_enabled=1 for msdb in the sys.databases)

you said this may be because off port or server name:

in the account I use to send the mail I have set these vause:

server name:pouyan(computer name in the system properties)

port =25

should they be any thing else?

thanks

pouyan.

|||

Hi

I checked every thing just as u said every thinh was ok

here is the result of executin sysmail_help_queue_sp:

queue_type length state last_empty_rowset_time last_activated_time
- -- -
mail 0 RECEIVES_OCCURRING 2007-03-14 12:44:29.713 2007-03-14 12:38:48.463
status 0 INACTIVE 2007-03-14 12:44:29.763 2007-03-14 12:44:29.713

--

I just today installed SMTP services!!!!

and the type of error changed to this form:

"

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2007-03-14T03:55:26).
Exception Message: Cannot send mails to mail server
. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for pooyan_pdm@.yahoo.com).
)

"

And there is another thing to tell : starting the database mail external(databasemail90.exe) is not mentioned in sysmail_event_log or Windows Application event log.

but I can see databasemail90.exe running in the prosses tab of the Task Manager.

I'm extremly confused.

|||You need not only install SMTP service, but you also need to configure it properly. See http://support.microsoft.com/kb/304897 for how to test your SMTP relay.|||

Every thing is ok

thanks

|||

Pouyan,

This may also be happeneing because of any antivirus installed on your machine. BCoz Antivirus softwares ususally block the ports. For example, Macfee blocks the port no 25 which is used by SQL server to send mails.

|||

I have to set up database mail to send emails using .

I am also getting the same error. I tested for SMTP relay service also.

am still getting this error :

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-04-09T15:24:10). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for abc@.abc.com

can u please let know where I can find the errors.

|||

Hi,

can you pls. explain to me in short how did you solve this problem?

I'm getting the same error message....

Thnx a lot in advance.

BR,

Bozo

|||

This problem was caused for ME because of Anti-Virus.

AV blocks services from sending on TCP-25 (like an email worm). Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Problem with report DataSource

Hello,
I am deploying reports via the web service. I create a folder, then create
a data source. Once I create a report, I make a reference to the data source
created and change the reports invalid data source to the reference.
When I did this with a data source with credentials stored on the report
server, it worked with no problems. When I changed this to prompt for
crendentials, accessing the report via web services cannot find the data
source associated with the report (the one defined in the rdl does not exist
in the folder fyi). Is this because a reference has no name in the database?
It does work via the Report Manager.
Here are some code snippets:
this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
definition, null);
//change its datasource to the new one
DataSourceReference reference = new DataSourceReference();
reference.Reference = "/" + dbName + "/" + dbName;
DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
dbName + "/" + reportType.ToString() );
for(int i=0;i<dataSources.Length;i++)
{
if(dataSources[i].Item is InvalidDataSourceReference)
{
dataSources[i].Item = reference;
}
}
this.ReportService.SetReportDataSources("/" + dbName + "/" +
reportType.ToString() ,dataSources);
and for credentials:
DataSourceCredentials d = new DataSourceCredentials();
d.DataSourceName = dbName;
d.UserName = userName;
d.Password = password;
this.mDataSourceCredentials = new DataSourceCredentials[]{d};
any help would be greatly appreciated.It seems recently that my posts never get replied to - Im sure others must
share the same frustration. The solution I came up with was to read the .rdl
file in as xml, search for all data source nodes and replace the old name
(attribute) with the new name. I also created the data source as a data
source, not as a reference.
"comet61" wrote:
> Hello,
> I am deploying reports via the web service. I create a folder, then create
> a data source. Once I create a report, I make a reference to the data source
> created and change the reports invalid data source to the reference.
> When I did this with a data source with credentials stored on the report
> server, it worked with no problems. When I changed this to prompt for
> crendentials, accessing the report via web services cannot find the data
> source associated with the report (the one defined in the rdl does not exist
> in the folder fyi). Is this because a reference has no name in the database?
> It does work via the Report Manager.
> Here are some code snippets:
> this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
> definition, null);
> //change its datasource to the new one
> DataSourceReference reference = new DataSourceReference();
> reference.Reference = "/" + dbName + "/" + dbName;
> DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
> dbName + "/" + reportType.ToString() );
> for(int i=0;i<dataSources.Length;i++)
> {
> if(dataSources[i].Item is InvalidDataSourceReference)
> {
> dataSources[i].Item = reference;
> }
> }
> this.ReportService.SetReportDataSources("/" + dbName + "/" +
> reportType.ToString() ,dataSources);
> and for credentials:
> DataSourceCredentials d = new DataSourceCredentials();
> d.DataSourceName = dbName;
> d.UserName = userName;
> d.Password = password;
> this.mDataSourceCredentials = new DataSourceCredentials[]{d};
> any help would be greatly appreciated.
>