database name

Is there a way to determine the current database the session is using? I want
to use this to determine if a script should proceed. It should only proceed
if the current database is not a system database.
I was thinking something like,
IF (select dbid < 6) THEN RAISERROR.
Thoughts? Thanks!
Check books online for db_name.
If you execute something like:
select db_name()
you will get the current database name.
-Sue
On Mon, 13 Sep 2004 16:41:12 -0700, "Bevo"
<Bevo@.discussions.microsoft.com> wrote:

>Is there a way to determine the current database the session is using? I want
>to use this to determine if a script should proceed. It should only proceed
>if the current database is not a system database.
>I was thinking something like,
>IF (select dbid < 6) THEN RAISERROR.
>Thoughts? Thanks!
|||if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> Is there a way to determine the current database the session is using? I
want
> to use this to determine if a script should proceed. It should only
proceed
> if the current database is not a system database.
> I was thinking something like,
> IF (select dbid < 6) THEN RAISERROR.
> Thoughts? Thanks!
|||I will not know the name of the database to be used, but I will know that it
is not a system database.
Can I be sure that all database ids greater than 6 are not system databases?
If so, the logic would be this:
if ( db_id(db_name()) < 6) Raiserror ('Must not be a system database',16,1)
"Wayne Snyder" wrote:

> if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> want
> proceed
>
>
|||There is no need for db_name().
e.g.
If (db_id()<6) Raiserror ('Must not be a system included database',16,1)
Howerver, it's best to be explicit because you would never know if there
would be more than 6 system included databases. Plus, the db_id for the 7th
might be >6. Thus,
If (db_name() in ('master','msdb','tempdb','model','Northwind','Pub s'))
Raiserror ('Must not be a system included database',16,1)
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:10284D82-E541-46F4-B3C3-C76AA021BA40@.microsoft.com...
> I will not know the name of the database to be used, but I will know that
it
> is not a system database.
> Can I be sure that all database ids greater than 6 are not system
databases?
> If so, the logic would be this:
> if ( db_id(db_name()) < 6) Raiserror ('Must not be a system
database',16,1)[vbcol=seagreen]
>
sql

database name

Is there a way to determine the current database the session is using? I want
to use this to determine if a script should proceed. It should only proceed
if the current database is not a system database.
I was thinking something like,
IF (select dbid < 6) THEN RAISERROR.
Thoughts' Thanks!Check books online for db_name.
If you execute something like:
select db_name()
you will get the current database name.
-Sue
On Mon, 13 Sep 2004 16:41:12 -0700, "Bevo"
<Bevo@.discussions.microsoft.com> wrote:
>Is there a way to determine the current database the session is using? I want
>to use this to determine if a script should proceed. It should only proceed
>if the current database is not a system database.
>I was thinking something like,
>IF (select dbid < 6) THEN RAISERROR.
>Thoughts' Thanks!|||if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> Is there a way to determine the current database the session is using? I
want
> to use this to determine if a script should proceed. It should only
proceed
> if the current database is not a system database.
> I was thinking something like,
> IF (select dbid < 6) THEN RAISERROR.
> Thoughts' Thanks!|||I will not know the name of the database to be used, but I will know that it
is not a system database.
Can I be sure that all database ids greater than 6 are not system databases?
If so, the logic would be this:
if ( db_id(db_name()) < 6) Raiserror ('Must not be a system database',16,1)
"Wayne Snyder" wrote:
> if ( db_name != 'mydb') Raiserror ('Incorrect database',16,1)
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:4A5919FA-B5A6-4B2E-BF05-F681FBA08019@.microsoft.com...
> > Is there a way to determine the current database the session is using? I
> want
> > to use this to determine if a script should proceed. It should only
> proceed
> > if the current database is not a system database.
> >
> > I was thinking something like,
> >
> > IF (select dbid < 6) THEN RAISERROR.
> >
> > Thoughts' Thanks!
>
>|||There is no need for db_name().
e.g.
If (db_id()<6) Raiserror ('Must not be a system included database',16,1)
Howerver, it's best to be explicit because you would never know if there
would be more than 6 system included databases. Plus, the db_id for the 7th
might be >6. Thus,
If (db_name() in ('master','msdb','tempdb','model','Northwind','Pubs'))
Raiserror ('Must not be a system included database',16,1)
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:10284D82-E541-46F4-B3C3-C76AA021BA40@.microsoft.com...
> I will not know the name of the database to be used, but I will know that
it
> is not a system database.
> Can I be sure that all database ids greater than 6 are not system
databases?
> If so, the logic would be this:
> if ( db_id(db_name()) < 6) Raiserror ('Must not be a system
database',16,1)
>
> >

Database Mirroring. Asp application (IIS 6.0) does''t forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

sql

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Clienterror '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

Database Mirroring Monitor

Hi there!

I've some trouble with monitoring a database mirroring session. I am testing a lot configuration possiblities, and i use the database mirroring monitor to view the mirroring states.

But in some (most) of my configurations, i only get following informations in the database mirroring monitor:

Oldest unsend transaction, Unrestored Log, Current Restore rate and the Witness Adress. I am working in High safety mode with automatic failover.

Question 1: What are the reasons that all the other indicators (Unsend log, Current send rate, Mirror commit overhead) are not shown?

Question 2: In the moment, i am using certificates for the mirroring communication. I think, my client (who the database mirroring monitor is running) connects with the usual windows authentication to the server and only selects from sys.database_mirroring or something else. So there is no need to create inbound certificates on the partners for the client who is connecting with the mirroring monitor?

Torsten

1. The counters that you say you cannot see are ones that are specific to the principal. Make sure that your connection from the monitor to the principal is good.

2.OK, sounds a little strange. Looks like the certs are what is messing things up. So, the connection from the monitor into the partners should be SA to make everything run nicely. But if the conneciton cannot be SA, then the monitor can come in as a normal login and get the data as long as the login is a member of the DBM_Monitor security group in MSDB. Note: the job that updates the base table for monitoring must be running as well.

Thanks,

Mark

|||

Hi Mark,

1. The connection is good, all related users have logins on the principle, are member of the dbm_monitor group, and are in sysadmin group.

In my opinion its not a problem of the database mirroring monitor, because the main stored procedure doesn't even show the results:

sp_dbmmonitorresults 'EASYRIS_41', 0,1

--> EASYRIS_41 1 4 1 NULL NULL NULL NULL NULL NULL NULL NULL 2006-06-21 12:38:44.547 2006-06-21 12:38:44.547 2006-06-21 14:38:44.547

Furthermore i've recognized that the Database Mirroring Monitor Job is located on the MIRROR (thats because the mirror has been the principal when i set up the session). Should the job run on BOTH partners? Furthermore the job is runnning under 'sa'. sa is not member of dbm_monitor group, and i can not put him into the group.

What is the exact recommendation? Is there any documentation i've not seen?

Thanks and sorry for the extra work ;-)

Torsten

|||

yes, you must run the job on both servers. if the job is running under SA then that's fine.

The user that the monitor logs in as can be in the dbm_monitor group if you do not want it to be SA. If it is SA, then the monitor should be able to update the info anyway...

mark

Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database Mail does not work from SQL Agent Job

Receiving Message:
[264] An attempt was made to send an email when no email session has been established

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

Receiving Message:
[264] An attempt was made to send an email when no email session has been established

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

Receiving Message:
[264] An attempt was made to send an email when no email session has been established

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet

Database Mail does not work from SQL Agent Job

Receiving Message:
[264] An attempt was made to send an email when no email session has been established

Just installed Service pack 1 and that did not help.

http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx

Take a look at the above blog and this should address the problem you are running into.

Thanks,

Gops Dwarak

|||

I have already read that blog before posting this problem. This is why I upgraded my Server to Service Pack 1. It appears to me that they did not in fact fix this in the 64-bit version unless I am doing something wrong that I am not aware of.

I Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

|||

We could not repro in our labs.

Did you restart SQL Server Agent after setting/changing the profile ?

Thanks,

Gops Dwarak

|||

I got the same error after running a script (inherited from our DBA) that calls
sp_MSupdate_agenttype_default
several times with @.profile_id values in
(1, 2, 4, 6, 11).

Maybe, it helps to narrow down the problem

|||

We are having the exact same problem and restarting the SQL Server Agent did not help.

We run SP1 on x86. We get both the "[264] An attempt was m..." error and the "2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'...." error. Send test email works.

|||

Did you perform the following 3 steps when setting up your database mail?

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

I got hung up a few times on this myself because I kept forgetting to do step 2, and I had the same exact error message you do. Once I did step 2, everything was resolved.

|||

Thank you for your reply.

Unfortunately, I have already done those three steps, and still have the same problem

|||After doing the 3 steps you need to restart the agent. That fixed the problem for me - Manmeet