Database Names

Hi Everyone,
I've got Windows SharePoint Services installed and it uses a database called
STS_svr-apps-1_1414639615
I'm trying to back up this database from the QSQL command line tool.
However when I try to run the backup, I get the following error:
1> BACKUP DATABASE STS_svr-apps-1_1414639615 TO DISK
'D:\ShareBack\Backup.bak'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near '-'.
1>
Thinking it might be an issue with the name I enclosed it in single quotes:
1> BACKUP DATABASE 'STS_svr-apps-1_1414639615' TO DISK
'D:\ShareBack\Backup.bak'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'.
1>
Yet I still get the same error!
Then I tried to just use the database:
1> use STS_svr-apps-1_1414639615
2> go
Msg 911, Level 16, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Could not locate entry in sysdatabases for database 'STS_svr'. No entry
found
with that name. Make sure that the name is entered correctly.
1>
Again I tried to using quotation marks, another error:
1> use 'STS_svr-apps-1_1414639615'
2> GO
Msg 170, Level 15, State 1, Server SVR-APPS-1\SHAREPOINT, Line 1
Line 1: Incorrect syntax near 'STS_svr-apps-1_1414639615'.
1> quit
If I do:
SELECT name
FROM master..sysdatabases
ORDER BY name
It lists:
master
model
msdb
STS_Config
STS_svr-apps-1_1414639615
tempdb
From this I can only assume MSDE doesn't support having '-' in a database
name?
Can someone suggest a way around this?
Thanks
Chris Moon
hi Chris,
> From this I can only assume MSDE doesn't support having '-' in a
> database name?
>
SQL Server (and MSDE) support this kind of identifier.. executing
SET NOCOUNT ON
SELECT @.@.VERSION
GO
CREATE DATABASE [STS_svr-apps-1_1414639615];
GO
USE [STS_svr-apps-1_1414639615];
GO
USE master ;
GO
DROP DATABASE [STS_svr-apps-1_1414639615];
reports
Microsoft SQL Server 2000 - 8.00.2162 (Intel X86)
Sep 30 2005 19:59:14
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
The CREATE DATABASE process is allocating 0.75 MB on disk
'STS_svr-apps-1_1414639615'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'STS_svr-apps-1_1414639615_log'.
Deleting database file 'C:\Programmi\Microsoft SQL
Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615_log.LDF'.
Deleting database file 'C:\Programmi\Microsoft SQL
Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615.mdf'.
http://msdn.microsoft.com/library/de...on_03_6e9e.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Many thanks Andrea, it worked perfectly.
Thanks again.
Chris Moon
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:43ml8mF1o772rU1@.individual.net...
> hi Chris,
> SQL Server (and MSDE) support this kind of identifier.. executing
> SET NOCOUNT ON
> SELECT @.@.VERSION
> GO
> CREATE DATABASE [STS_svr-apps-1_1414639615];
> GO
> USE [STS_svr-apps-1_1414639615];
> GO
> USE master ;
> GO
> DROP DATABASE [STS_svr-apps-1_1414639615];
> reports
> ----
> Microsoft SQL Server 2000 - 8.00.2162 (Intel X86)
> Sep 30 2005 19:59:14
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
> The CREATE DATABASE process is allocating 0.75 MB on disk
> 'STS_svr-apps-1_1414639615'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'STS_svr-apps-1_1414639615_log'.
> Deleting database file 'C:\Programmi\Microsoft SQL
> Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615_log.LDF'.
> Deleting database file 'C:\Programmi\Microsoft SQL
> Server\MSSQL$MSDE2K\Data\STS_svr-apps-1_1414639615.mdf'.
> http://msdn.microsoft.com/library/de...on_03_6e9e.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database maintenance plan: .trn file not backed up

We have installed an SQL 2000 database over a Windows 2000
server platform. We have used the database maintenance
plan wizard to back up three databases on a daily basis.
We have been able to back up the .BAK files, but not
the .TRN files. In addition, a failure message has been
generated to the SQthe "Jobs" module
See my reply to your post int he other group. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"apm2524" <anonymous@.discussions.microsoft.com> wrote in message
news:8fd901c432fb$ce8ca380$a101280a@.phx.gbl...
> We have installed an SQL 2000 database over a Windows 2000
> server platform. We have used the database maintenance
> plan wizard to back up three databases on a daily basis.
> We have been able to back up the .BAK files, but not
> the .TRN files. In addition, a failure message has been
> generated to the SQthe "Jobs" module
|||Do not use the daily plan but use the weekly and tick all seven days if need be. For some reason 'Daily' is not quite what it seems, as it is more of a 'one off' than a schedule.

Database maintenance plan: .trn file not backed up

We have installed an SQL 2000 database over a Windows 2000
server platform. We have used the database maintenance
plan wizard to back up three databases on a daily basis.
We have been able to back up the .BAK files, but not
the .TRN files. In addition, a failure message has been
generated to the SQthe "Jobs" moduleSee my reply to your post int he other group. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"apm2524" <anonymous@.discussions.microsoft.com> wrote in message
news:8fd901c432fb$ce8ca380$a101280a@.phx.gbl...
> We have installed an SQL 2000 database over a Windows 2000
> server platform. We have used the database maintenance
> plan wizard to back up three databases on a daily basis.
> We have been able to back up the .BAK files, but not
> the .TRN files. In addition, a failure message has been
> generated to the SQthe "Jobs" module|||Do not use the daily plan but use the weekly and tick all seven days if need
be. For some reason 'Daily' is not quite what it seems, as it is more of a
'one off' than a schedule.

Database maintenance plan: .trn file not backed up

We have installed an SQL 2000 database over a Windows 2000
server platform. We have used the database maintenance
plan wizard to back up three databases on a daily basis.
We have been able to back up the .BAK files, but not
the .TRN files. In addition, a failure message has been
generated to the SQthe "Jobs" moduleSee my reply to your post int he other group. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"apm2524" <anonymous@.discussions.microsoft.com> wrote in message
news:8fd901c432fb$ce8ca380$a101280a@.phx.gbl...
> We have installed an SQL 2000 database over a Windows 2000
> server platform. We have used the database maintenance
> plan wizard to back up three databases on a daily basis.
> We have been able to back up the .BAK files, but not
> the .TRN files. In addition, a failure message has been
> generated to the SQthe "Jobs" module

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?
Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ
|||The result was...
STARTED
|||You may need to define an operator.
|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).
|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ|||The result was...
STARTED|||You may need to define an operator.|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

Database mail issue - any ideas?

We recently installed 2005 and have been playing with it. I seem to be
having a problem with Database Mail as it relates to job notifications,
and wanted to see if anyone knew what I was doing wrong or how to fix
this issue.
We've enabled Database Mail using the SAC tool. I've set up a mail
profile & account successfully and sent successful test messages. I
followed the instructions here (
http://msdn2.microsoft.com/en-us/library/ms186358.aspx ) on configuring
SQL Agent mail to use my Database Mail profile. But alas - no messages
ever arrive. In the SQL Agent log, I see this:
Message
[264] An attempt was made to send an email when no email session has
been established
Everything seemed rather straightforward....am I doing something wrong?Did you run msdb.dbo.sysmail_start_sp? Try
msdb.dbo.sysmail_help_status_sp and see what the result is.
-KJ|||The result was...
STARTED|||You may need to define an operator.|||I also defined an operator (myself). The operator email has the same
email address as the test message that was sent successfully, so I know
everything outside of SQL is working (SMTP relay, etc.).|||OK - got it figured out. I just had to stop & restart the service,
which I did through SAC tool. Then I immediately received my next test
from the job.

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

Database mail - send test email

i've got a brand new server and just installed SQL 2005.

when i try to send a test email, i get the following error message:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2006-11-23T11:49:34). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it). )

I have checked all items from troubleshoot and eveything is ok...any help ?

thanks

Are you properly connecting to smtp server?|||

So clearly everything is not OK, but what troubleshooting have you done?

The message says it cannot connect to the server, so he probably is not connecting to the smtp server.

From the machine hosting the package, are there any firewalls in between? Do they allow you access? Can you telnet to port 25 of the SMTP server from the machine running the SSIS package? Remember SSIS is not server side, so that is the machine that hosts the DTEXEC/BIDS process.

'Database Loading' state after Restore

Hi,
I have a Windows Server 2003 cluster setup with 3 SQL
Server 2000 virtual servers installed. When I perform a
restore of an 85 GB database, the database gets stuck in
a 'Database Loading' state. It doesn't appear to be a
resource issue since SQL has access to all 8 processors
and 4 GB of memory and the system is not under stress. I
have checked the SQL and Event Viewer logs for clues but
have found nothing to pursue. Any assistance is greatly
appreciated.
Sincerely,
ABGABG,
Did you get a message that the database restore finished
successfully (even though it is 'loading' state')?
During the restore process, you must the with 'RECOVERY'
OPTION.
If the restore is finished, you could do the following to
recover the database:
RESTORE DATABASE_NAME
WITH RECOVERY
hth
DeeJay
>--Original Message--
>Hi,
> I have a Windows Server 2003 cluster setup with 3 SQL
>Server 2000 virtual servers installed. When I perform a
>restore of an 85 GB database, the database gets stuck in
>a 'Database Loading' state. It doesn't appear to be a
>resource issue since SQL has access to all 8 processors
>and 4 GB of memory and the system is not under stress. I
>have checked the SQL and Event Viewer logs for clues but
>have found nothing to pursue. Any assistance is greatly
>appreciated.
> Sincerely,
> ABG
>.
>|||Yes, it stated that the recovery completed. Although the
database stayed in Loading state. I'm running the restore
from Enterprise Manager, is there a way to specify
the 'Recovery' option from here? Or must the restore be
performed from Query Analyzer? Thank you for your reply.
Sincerely,
ABG
>--Original Message--
>ABG,
>Did you get a message that the database restore finished
>successfully (even though it is 'loading' state')?
>During the restore process, you must the with 'RECOVERY'
>OPTION.
>If the restore is finished, you could do the following to
>recover the database:
>RESTORE DATABASE_NAME
>WITH RECOVERY
>hth
>DeeJay
>>--Original Message--
>>Hi,
>> I have a Windows Server 2003 cluster setup with 3 SQL
>>Server 2000 virtual servers installed. When I perform a
>>restore of an 85 GB database, the database gets stuck in
>>a 'Database Loading' state. It doesn't appear to be a
>>resource issue since SQL has access to all 8 processors
>>and 4 GB of memory and the system is not under stress. I
>>have checked the SQL and Event Viewer logs for clues but
>>have found nothing to pursue. Any assistance is greatly
>>appreciated.
>> Sincerely,
>> ABG
>>.
>.
>|||In EM, right.most tab (at the bottom), you have option for
RECOVERY/NORECOVERY/STANDBY. Default is, however RECOVERY on the last backup
(the topmost alternative). The command shown by DeeJay to recovery without
doing an actual restore is not available from EM.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ABG" <anonymous@.discussions.microsoft.com> wrote in message
news:03c101c3a8a0$d4e30f70$a401280a@.phx.gbl...
> Yes, it stated that the recovery completed. Although the
> database stayed in Loading state. I'm running the restore
> from Enterprise Manager, is there a way to specify
> the 'Recovery' option from here? Or must the restore be
> performed from Query Analyzer? Thank you for your reply.
> Sincerely,
> ABG
> >--Original Message--
> >ABG,
> >
> >Did you get a message that the database restore finished
> >successfully (even though it is 'loading' state')?
> >
> >During the restore process, you must the with 'RECOVERY'
> >OPTION.
> >
> >If the restore is finished, you could do the following to
> >recover the database:
> >
> >RESTORE DATABASE_NAME
> >WITH RECOVERY
> >
> >hth
> >
> >DeeJay
> >>--Original Message--
> >>Hi,
> >> I have a Windows Server 2003 cluster setup with 3 SQL
> >>Server 2000 virtual servers installed. When I perform a
> >>restore of an 85 GB database, the database gets stuck in
> >>a 'Database Loading' state. It doesn't appear to be a
> >>resource issue since SQL has access to all 8 processors
> >>and 4 GB of memory and the system is not under stress. I
> >>have checked the SQL and Event Viewer logs for clues but
> >>have found nothing to pursue. Any assistance is greatly
> >>appreciated.
> >> Sincerely,
> >> ABG
> >>
> >>.
> >>
> >.
> >