DBCC CHECKDB: setup

Showing posts with label setup. Show all posts

Thursday, March 29, 2012

DBA Format

I am a developer who recently stepped in for our DBA when they (abruptly)
left and am not sure how to setup a deployment for one of our clients.
I think I could do a detach then have them attach or a backup then send them
the backup.
I would like to ask how a DBA would like to see what I am sending out.
Also, a pointer to the documentation would help.
Thanks for your help
LanceThe answer to your question depends a lot on what it actually is you're
deploying. A whole app, a database only, updates to an existing database,
...?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lance Geeck" <lgeeck@.cox.net> wrote in message
news:N85xc.23665$aM1.8131@.fed1read02...
> I am a developer who recently stepped in for our DBA when they (abruptly)
> left and am not sure how to setup a deployment for one of our clients.
> I think I could do a detach then have them attach or a backup then send
them
> the backup.
> I would like to ask how a DBA would like to see what I am sending out.
> Also, a pointer to the documentation would help.
> Thanks for your help
> Lance
>|||Just a database. New installation.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u$0ZJfNTEHA.3608@.TK2MSFTNGP11.phx.gbl...
> The answer to your question depends a lot on what it actually is you're
> deploying. A whole app, a database only, updates to an existing database,
> ...?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Lance Geeck" <lgeeck@.cox.net> wrote in message
> news:N85xc.23665$aM1.8131@.fed1read02...
> > I am a developer who recently stepped in for our DBA when they
(abruptly)
> > left and am not sure how to setup a deployment for one of our clients.
> >
> > I think I could do a detach then have them attach or a backup then send
> them
> > the backup.
> >
> > I would like to ask how a DBA would like to see what I am sending out.
> >
> > Also, a pointer to the documentation would help.
> >
> > Thanks for your help
> > Lance
> >
> >
>|||You could do a backup and send them the .BAK file, then they could restore
it. However, this has some dependencies, e.g. data folder locations, etc.
Simplest might be to detach the database, send the MDF/LDF file(s), then
re-attach.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lance Geeck" <lgeeck@.cox.net> wrote in message
news:6k5xc.23713$aM1.9744@.fed1read02...
> Just a database. New installation.
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:u$0ZJfNTEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > The answer to your question depends a lot on what it actually is you're
> > deploying. A whole app, a database only, updates to an existing
database,
> > ...?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Lance Geeck" <lgeeck@.cox.net> wrote in message
> > news:N85xc.23665$aM1.8131@.fed1read02...
> > > I am a developer who recently stepped in for our DBA when they
> (abruptly)
> > > left and am not sure how to setup a deployment for one of our clients.
> > >
> > > I think I could do a detach then have them attach or a backup then
send
> > them
> > > the backup.
> > >
> > > I would like to ask how a DBA would like to see what I am sending out.
> > >
> > > Also, a pointer to the documentation would help.
> > >
> > > Thanks for your help
> > > Lance
> > >
> > >
> >
> >
>|||Another thing you might consider doing is scripting out the database and the
objects it contains. You can do this by right-clicking a database in
Enterprise Manager, all tasks, generate SQL script, and follow the wizard.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Lance Geeck" <lgeeck@.cox.net> wrote in message
news:6k5xc.23713$aM1.9744@.fed1read02...
> Just a database. New installation.|||Wouldn't the ATTACH be a problem if it is in a different folder location?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OFs%23bjNTEHA.3700@.TK2MSFTNGP09.phx.gbl...
> You could do a backup and send them the .BAK file, then they could restore
> it. However, this has some dependencies, e.g. data folder locations, etc.
> Simplest might be to detach the database, send the MDF/LDF file(s), then
> re-attach.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Lance Geeck" <lgeeck@.cox.net> wrote in message
> news:6k5xc.23713$aM1.9744@.fed1read02...
> > Just a database. New installation.
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> > news:u$0ZJfNTEHA.3608@.TK2MSFTNGP11.phx.gbl...
> > > The answer to your question depends a lot on what it actually is
you're
> > > deploying. A whole app, a database only, updates to an existing
> database,
> > > ...?
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "Lance Geeck" <lgeeck@.cox.net> wrote in message
> > > news:N85xc.23665$aM1.8131@.fed1read02...
> > > > I am a developer who recently stepped in for our DBA when they
> > (abruptly)
> > > > left and am not sure how to setup a deployment for one of our
clients.
> > > >
> > > > I think I could do a detach then have them attach or a backup then
> send
> > > them
> > > > the backup.
> > > >
> > > > I would like to ask how a DBA would like to see what I am sending
out.
> > > >
> > > > Also, a pointer to the documentation would help.
> > > >
> > > > Thanks for your help
> > > > Lance
> > > >
> > > >
> > >
> > >
> >
> >
>|||Doesn't this miss the records in the database? I have about 1/2 million
rows I need to include in what I send them.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uBSjakNTEHA.2372@.TK2MSFTNGP11.phx.gbl...
> Another thing you might consider doing is scripting out the database and
the
> objects it contains. You can do this by right-clicking a database in
> Enterprise Manager, all tasks, generate SQL script, and follow the wizard.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Lance Geeck" <lgeeck@.cox.net> wrote in message
> news:6k5xc.23713$aM1.9744@.fed1read02...
> > Just a database. New installation.
>|||you need to detach\reattach or Restore a backup using the "WITH MOVE" Option
if you need more info let me know
cheers,
Greg Jackson
PDX, Oregon|||Lance,
My preferred approach would be to backup/restore, but as Aaron said
there are dependencies and you will likely orphan any users in the
database. This can however be fixed by adding new logins to the
destination box and mapping orphaned users to the logins using
sp_change_users_login.
You will likely also need the WITH MOVE option of the RESTORE DATABASE
statement. You might also want to change the database owner to something
meaningful on the destination, I would recommend sa. Use
sp_changedbowner to do this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Lance Geeck wrote:
> I am a developer who recently stepped in for our DBA when they (abruptly)
> left and am not sure how to setup a deployment for one of our clients.
> I think I could do a detach then have them attach or a backup then send them
> the backup.
> I would like to ask how a DBA would like to see what I am sending out.
> Also, a pointer to the documentation would help.
> Thanks for your help
> Lance
>

DBA Format

Tuesday, March 27, 2012

DB2 Linked Server

I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?What OLE DB Provider are you using? I have had no luck with the standard ones provided by IBM; I have heard of others who have used 3rd party drivers with great success.

Regards,

hmscott

Originally posted by vmlal
I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?|||I'm using the OLE DB Provider as follows:
srvproduct: DB2OLEDB
providername: MSDASQL

I have installed the right drivers for DB2 etc.

What other 3rd party tools would be helpful? any ideas?

thx|||Sorry it took me so long to get back to you. I had to dig through my e-mails to find the link. Go to www.hitsw.com. On their home page, look in the middle third for a link to "SQL Middleware".

I have not used their products, but a friend of mine has. He demonstrated the capability to link a DB2 server using the OLE DB provider from Hit Software.

I hope this helps.

Regards,

hmscott

Originally posted by vmlal
I'm using the OLE DB Provider as follows:
srvproduct: DB2OLEDB
providername: MSDASQL

I have installed the right drivers for DB2 etc.

What other 3rd party tools would be helpful? any ideas?

thx|||Originally posted by vmlal
I have setup a linked DB2 server and i c the table/view list in EM>Security. But in QA when i run

select * from servername.catalog.user.tablename

i get the following error msg:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider does not support the necessary method.
[OLE/DB provider returned message: Method is not supported by this provider.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80040e53: The provider does not support the necessary method.].

Any way to solve this?

Use an openquery instead.|||Originally posted by snail
Use an openquery instead.

Doesnt work with openquery either.

PS: The DB2 is on AIX boxsql

DB2 driver/connection issue

Hi guys,

I have a kind of weird situation that may be causing this problem. My setup is that I'm remote desktopping into a 64-bit box on a client's network. They installed SSIS there, I use login 1 which has rights to the box, but none of the databases. To do that I run SSIS as login 2, who can hit the databases.

I've got two datasources for this project one is DB2 and the other is SqlServer2k5. I've been focusing on the DB2 (I'm running the Microsoft OLEDB Provider for DB2 that was part of the feature pack from feb 2007). I've registered the dll with "regsvr32.exe /s /c ibmdadb2.dll" and tried to import database settings from a pdb with "db2cfimp db2Settings.pdb". The connection to, we'll call it, DB2A doesn't actually show up in the "Admin Tools\ODBC Data Source Administrator", but seeing as how the connection sort of works I don't think this is the problem.

Using the "IBM OLE DB Provider for DB2 Servers" connection I can connect to the DB2 database in SSIS's Data Flow with a DataReader Source. The correct columns are shown in the column mappings and everything looks good while designing.

Upon executing the package I get the following error:

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at TGMV019, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0047062 at TGMV019, GM19 [31]: System.InvalidOperationException: The 'IBMDADB2.1' provider is not registered on the local machine.
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error: 0xC0047017 at TGMV019, DTS.Pipeline: component "GM19" (31) failed validation and returned error code 0x80131509.
Error: 0xC004700C at TGMV019, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at TGMV019: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.

The problem sure looks like it's with the IBMDADB2.1 provider. I have retried the regsvr32 command (is this just a 32bit version?) to no avail. Does this assignment require a reboot (because they installed this on a training database box that really can't be easily rebooted)?

Thanks for any ideas, I'm fresh out

Jeff
Since you are using the Microsoft OLEDB Provider for DB2, why aren't you using the OLE DB Source in the data flow instead of the DataReader source?|||I gave up on using "OLE DB Provider: Microsoft OLE DB Provider for DB2" because I can't seem to get away from the error: "Test connection failed because of an error in initializing provider. The parameter is incorrect.". Also, these forums (I think) said the IBM one was more reliable than the microsoft one.

I'm not sure what to enter under: Data Link Properties. I've tried various combination for 'Initial catalog', 'package collection' and 'default schema' but none of them work. Also, pressing the "Packages" button on the bottom crashes visual studio, so I'm not sure what's supposed to go there.
|||See if this helps any:
http://www.ssistalk.com/db2_configurations.jpg

That is the list of all of the properties for one of my connections that I use on a daily basis to transfer records from DB2 to SQL Server 2005.

Thursday, March 8, 2012

DB Owner Problem after restore on a new PC

I am using SQL2000. I took a backup of the database on the live server to setup the database on my new development computer. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)1.1
The problem is that the 'indiankarma' database user has no login name. (See below 1.2).1.2 1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)1.3
1.3

1.4
I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. I have tried sp_changedbowner 'indiankarma' which does give the security login name indiankarma database access but it makes the user dbo not indiankarma. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!1.5

There are two permissions in SQL Server one for the server under security in the management section of Enterprise Manager and the database permission, you may have created the database permissions and not the server login. If that is not your problem then you have a problem which is a known issue with restore called orphaned permissions where the permission is in the Master but not the database you created it for, solution delete the restore and start fresh. Hope this helps.|||

FYI, here is a stored procedure to check orphaned users:

CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @.DBName sysname, @.Qry nvarchar(4000)

SET @.Qry = ''
SET @.DBName = ''

WHILE @.DBName IS NOT NULL
BEGIN
SET @.DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @.DBName
)

IF @.DBName IS NULL BREAK

SET @.Qry = ' SELECT ''' + @.DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@.DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'

INSERT INTO #Results EXEC (@.Qry)
END

SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END

|||Or you could look inside the Master verifiy the account and edit it. But it is faster to rerestore I have done both.

Wednesday, March 7, 2012

DB Mnt plan issues

Hi,
We have a 40GB OLTP system, 24x7.
We've setup and schld DB mnt plans to optimize the user
databases at Sunday 3AM where there is less activity on
the system.
The Optimization tab is set up to reorg data and index
pages and change the free space per page percentage to 10%.
When the job ran yesterday, it started blocking clients
and locked up around 90% of the resources on the server.
No one could access the server. The transaction log grew
to 9GB. We had to cold start the windows server.
Question is why did this happen and what can I do to
prevent this ?
TIA
MOis the maintenance plan doing dbcc dbreindex ? or is it using dbcc
indexdefrag ?
for 24x7 ops, you'll want to use dbcc indexdefrag.
hope this helps,
Greg Jackson
PDX, Oregon|||Hi,
Thx for your reply.
How do I tell if it is running dbreindex or indexdefrag ?
I run sp_who and it says dbcc.
MO
>--Original Message--
>is the maintenance plan doing dbcc dbreindex ? or is it
using dbcc
>indexdefrag ?
>for 24x7 ops, you'll want to use dbcc indexdefrag.
>
>hope this helps,
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||The maintenance plans generated by enterprise manager use DBCC DBREINDEX.
Because this completely rebuilds all the indexes on a table it can cause
serious blocking. If you want to use DBCC INDEXDEFRAG you will have to write
your own task to do the reindexing. There is an example of this in BOL on
the DBCC SHOWCONTIG page (example E)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MO" <anonymous@.discussions.microsoft.com> wrote in message
news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
> Hi,
> Thx for your reply.
> How do I tell if it is running dbreindex or indexdefrag ?
> I run sp_who and it says dbcc.
> MO
> using dbcc|||Thx for your reply.
MO
>--Original Message--
>The maintenance plans generated by enterprise manager use
DBCC DBREINDEX.
>Because this completely rebuilds all the indexes on a
table it can cause
>serious blocking. If you want to use DBCC INDEXDEFRAG you
will have to write
>your own task to do the reindexing. There is an example
of this in BOL on
>the DBCC SHOWCONTIG page (example E)
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"MO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
indexdefrag ?
>
>.
>

DB Mnt plan issues

Hi,
We have a 40GB OLTP system, 24x7.
We've setup and schld DB mnt plans to optimize the user
databases at Sunday 3AM where there is less activity on
the system.
The Optimization tab is set up to reorg data and index
pages and change the free space per page percentage to 10%.
When the job ran yesterday, it started blocking clients
and locked up around 90% of the resources on the server.
No one could access the server. The transaction log grew
to 9GB. We had to cold start the windows server.
Question is why did this happen and what can I do to
prevent this ?
TIA
MOis the maintenance plan doing dbcc dbreindex ? or is it using dbcc
indexdefrag ?
for 24x7 ops, you'll want to use dbcc indexdefrag.
hope this helps,
Greg Jackson
PDX, Oregon|||Hi,
Thx for your reply.
How do I tell if it is running dbreindex or indexdefrag ?
I run sp_who and it says dbcc.
MO
>--Original Message--
>is the maintenance plan doing dbcc dbreindex ? or is it
using dbcc
>indexdefrag ?
>for 24x7 ops, you'll want to use dbcc indexdefrag.
>
>hope this helps,
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||The maintenance plans generated by enterprise manager use DBCC DBREINDEX.
Because this completely rebuilds all the indexes on a table it can cause
serious blocking. If you want to use DBCC INDEXDEFRAG you will have to write
your own task to do the reindexing. There is an example of this in BOL on
the DBCC SHOWCONTIG page (example E)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"MO" <anonymous@.discussions.microsoft.com> wrote in message
news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
> Hi,
> Thx for your reply.
> How do I tell if it is running dbreindex or indexdefrag ?
> I run sp_who and it says dbcc.
> MO
> >--Original Message--
> >is the maintenance plan doing dbcc dbreindex ? or is it
> using dbcc
> >indexdefrag ?
> >
> >for 24x7 ops, you'll want to use dbcc indexdefrag.
> >
> >
> >
> >hope this helps,
> >
> >
> >Greg Jackson
> >PDX, Oregon
> >
> >
> >.
> >|||Thx for your reply.
MO
>--Original Message--
>The maintenance plans generated by enterprise manager use
DBCC DBREINDEX.
>Because this completely rebuilds all the indexes on a
table it can cause
>serious blocking. If you want to use DBCC INDEXDEFRAG you
will have to write
>your own task to do the reindexing. There is an example
of this in BOL on
>the DBCC SHOWCONTIG page (example E)
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"MO" <anonymous@.discussions.microsoft.com> wrote in
message
>news:521b01c3ffbd$6f1d2a30$a101280a@.phx.gbl...
>> Hi,
>> Thx for your reply.
>> How do I tell if it is running dbreindex or
indexdefrag ?
>> I run sp_who and it says dbcc.
>> MO
>> >--Original Message--
>> >is the maintenance plan doing dbcc dbreindex ? or is it
>> using dbcc
>> >indexdefrag ?
>> >
>> >for 24x7 ops, you'll want to use dbcc indexdefrag.
>> >
>> >
>> >
>> >hope this helps,
>> >
>> >
>> >Greg Jackson
>> >PDX, Oregon
>> >
>> >
>> >.
>> >
>
>.
>

DB mirroring setup

Prepare to setup DB mirroring. Three MS 2003 servers. Two SQL 2005 standard edition as partners and one SQL 2005 Express as witness. Question:

1. Servers are under workgroup (not domain user). Is it ok to use local service account to run DB engine service on all three machine? Is SQLServerAgent service required when setup mirroring?

2. If SQL Server is running as a local service, Do I have to manually create certificates before setup mirroring?

3. After create certificates, can management studio be used to create endpoints on all three machines? If yes, how to select certificates that have been created? If no, can management studio be used to setup mirroring after endpoints have been created?

actually i'm also would like to know about the additional settings if there is. Hope can get some guidelines here. Thx

Best Regards,

Hans

Saturday, February 25, 2012

DB Maintenance Plan reporting

The previous DBA had setup writing DB Maintenance Plan reports to a remote server. I am new to MSSQL (I primarily work with Oracle). I have configured another MSSQL 2000 server similar to previous ones, but the reports are not going into the remote database tables. Everything on the 'Reporting' tab for the DB Maint. Plan looks the same as older ones. Also, the SQL Agent service starts as a domain user, such as previous setups.
I am not sure what to check from here. I appreciate any help. Thanks.On SQL Agent, properties select forwading server to the new server you just setup.

Sunday, February 19, 2012

DB mail fails with permission error

Hi:

I am trying to send mail using DB Mail. I made sure that there is a mail box setup for the service account and the service broker is enabled in the msdb database. Also I made the service account a user in the msdb and added him to the mail role as specified in BOL. However I consistently end up with the following error message when I try to send the mail.

Log ID 19
Process ID 5672
Mail Item ID 6
Last Modified 4/26/2006 11:05:00 AM
Last Modified By sa

Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-04-26T11:05:00). Exception Message: Cannot send mails to mail server. (Client does not have permission to submit mail to this server. The server response was: 5.7.3 Client does not have permission to Send As this sender.).
)

I am at loss to understand what Permissions it still needs?. Any thoughts appreciated.

Thanks

I have resolved this problem successfully and want to share it with the community for the benefit of others.

when the above error happens, please make sure that you have the following in place.

1). The Service account should have a mail box setup.

2). The account name you use when you configure DB Mail should be the same as that of the Mail box name.

3). The Account should be a user in the msdb and should be added to the DBMailuser role.

Everything then should work fine.

Thanks

|||Thank you so much for posting this! I was getting hung up on requirement #2. They don't make these things very obvious do they?

DB mail fails with permission error

Hi:

Log ID 19
Process ID 5672
Mail Item ID 6
Last Modified 4/26/2006 11:05:00 AM
Last Modified By sa

I am at loss to understand what Permissions it still needs?. Any thoughts appreciated.

Thanks

I have resolved this problem successfully and want to share it with the community for the benefit of others.

when the above error happens, please make sure that you have the following in place.

1). The Service account should have a mail box setup.

2). The account name you use when you configure DB Mail should be the same as that of the Mail box name.

3). The Account should be a user in the msdb and should be added to the DBMailuser role.

Everything then should work fine.

Thanks

|||Thank you so much for posting this! I was getting hung up on requirement #2. They don't make these things very obvious do they?

DB Mail config

I am trying to setup db mail and I cannot get it to send an email. I have
enabled e-mail using the surface area configurator. I went through the db
mail setup wizard and entered all the appropiate info, but when I try to
send a test email, I get the following error in the db mail log:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
Message: Cannot send mails to mail server. (Failure sending mail.).
).
What could be causing this?
Thanks,
What type of server are you using for Mail. Some SMTP server? Does the db
mail account have access to relay on the SMTP server. Can you ping the SMTP
server from the SQL server?
What does the event log say as far as DB mail errors?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>I am trying to setup db mail and I cannot get it to send an email. I have
>enabled e-mail using the surface area configurator. I went through the db
>mail setup wizard and entered all the appropiate info, but when I try to
>send a test email, I get the following error in the db mail log:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
> Message: Cannot send mails to mail server. (Failure sending mail.).
> ).
> What could be causing this?
> Thanks,
>
|||We are using SMTP for the mail server. I am using my own account to send
the mail so it should have access to relay on the mail server. I can ping
the SMTP server from the SQL server. I didn't find any errors in the SQL
log or the system application log.
Tim
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23up92$C2GHA.4264@.TK2MSFTNGP05.phx.gbl...
> What type of server are you using for Mail. Some SMTP server? Does the db
> mail account have access to relay on the SMTP server. Can you ping the
> SMTP server from the SQL server?
> What does the event log say as far as DB mail errors?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>

DB Mail config

I am trying to setup db mail and I cannot get it to send an email. I have
enabled e-mail using the surface area configurator. I went through the db
mail setup wizard and entered all the appropiate info, but when I try to
send a test email, I get the following error in the db mail log:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
Message: Cannot send mails to mail server. (Failure sending mail.).
).
What could be causing this?
Thanks,What type of server are you using for Mail. Some SMTP server? Does the db
mail account have access to relay on the SMTP server. Can you ping the SMTP
server from the SQL server?
What does the event log say as far as DB mail errors?
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>I am trying to setup db mail and I cannot get it to send an email. I have
>enabled e-mail using the surface area configurator. I went through the db
>mail setup wizard and entered all the appropiate info, but when I try to
>send a test email, I get the following error in the db mail log:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
> Message: Cannot send mails to mail server. (Failure sending mail.).
> ).
> What could be causing this?
> Thanks,
>|||We are using SMTP for the mail server. I am using my own account to send
the mail so it should have access to relay on the mail server. I can ping
the SMTP server from the SQL server. I didn't find any errors in the SQL
log or the system application log.
Tim
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23up92$C2GHA.4264@.TK2MSFTNGP05.phx.gbl...
> What type of server are you using for Mail. Some SMTP server? Does the db
> mail account have access to relay on the SMTP server. Can you ping the
> SMTP server from the SQL server?
> What does the event log say as far as DB mail errors?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>

DB Mail config

I am trying to setup db mail and I cannot get it to send an email. I have
enabled e-mail using the surface area configurator. I went through the db
mail setup wizard and entered all the appropiate info, but when I try to
send a test email, I get the following error in the db mail log:
The mail could not be sent to the recipients because of the mail server
failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
Message: Cannot send mails to mail server. (Failure sending mail.).
).
What could be causing this?
Thanks,What type of server are you using for Mail. Some SMTP server? Does the db
mail account have access to relay on the SMTP server. Can you ping the SMTP
server from the SQL server?
What does the event log say as far as DB mail errors?
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Tim Kelley" <tkelley@.company.com> wrote in message
news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>I am trying to setup db mail and I cannot get it to send an email. I have
>enabled e-mail using the surface area configurator. I went through the db
>mail setup wizard and entered all the appropiate info, but when I try to
>send a test email, I get the following error in the db mail log:
> The mail could not be sent to the recipients because of the mail server
> failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
> Message: Cannot send mails to mail server. (Failure sending mail.).
> ).
> What could be causing this?
> Thanks,
>|||We are using SMTP for the mail server. I am using my own account to send
the mail so it should have access to relay on the mail server. I can ping
the SMTP server from the SQL server. I didn't find any errors in the SQL
log or the system application log.
Tim
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:%23up92$C2GHA.4264@.TK2MSFTNGP05.phx.gbl...
> What type of server are you using for Mail. Some SMTP server? Does the db
> mail account have access to relay on the SMTP server. Can you ping the
> SMTP server from the SQL server?
> What does the event log say as far as DB mail errors?
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:uMroorA2GHA.4392@.TK2MSFTNGP04.phx.gbl...
>>I am trying to setup db mail and I cannot get it to send an email. I have
>>enabled e-mail using the surface area configurator. I went through the db
>>mail setup wizard and entered all the appropiate info, but when I try to
>>send a test email, I get the following error in the db mail log:
>> The mail could not be sent to the recipients because of the mail server
>> failure. (Sending Mail using Account 3 (2006-09-14T09:27:22). Exception
>> Message: Cannot send mails to mail server. (Failure sending mail.).
>> ).
>> What could be causing this?
>> Thanks,
>

Tuesday, February 14, 2012

DB detach/attach lost data?

I used the technique of detaching, copying and attaching a database to setup
another one. The data did not get moved only the objects (tables, etc.).
After further investigation we found that the original db that was detached
also lost it's data. Good thing it was a test db. What could have happened?Hi
This would not normally be possible, the only thing I can think of is that
the transaction used to insert the data was rolled back and not committed or
you attached the wrong file(s)!
Before doing the detach try issuing as sp_helpfile to see what the data
files are.
John
"Jim Phillips" <jphillips@.marsys.com> wrote in message
news:O15lcMY$DHA.2484@.TK2MSFTNGP12.phx.gbl...
> I used the technique of detaching, copying and attaching a database to
setup
> another one. The data did not get moved only the objects (tables, etc.).
> After further investigation we found that the original db that was
detached
> also lost it's data. Good thing it was a test db. What could have
happened?
>|||Personally, I prefer to use db back-up/restore to move data. This has the
following benefits:
** You do not need to keep track of more than one file and that file you
name yourself (i.e. no searching for linked files)
** You do not need to take the original database of production
** You can easy move data across new servers without having to think of a
lot of database dependant settings such as languages etc.
** The file you move is just the size of the data, not including any extra
allocated space not currently in use.
There is more benefits, but these are the most important IMHO...
"Jim Phillips" <jphillips@.marsys.com> wrote in message
news:O15lcMY$DHA.2484@.TK2MSFTNGP12.phx.gbl...
I used the technique of detaching, copying and attaching a database to setup
another one. The data did not get moved only the objects (tables, etc.).
After further investigation we found that the original db that was detached
also lost it's data. Good thing it was a test db. What could have happened?

Thursday, March 29, 2012

DBA Format

DBA Format

DBA Format

Tuesday, March 27, 2012

DB2 Linked Server

DB2 driver/connection issue

Thursday, March 8, 2012

DB Owner Problem after restore on a new PC

Wednesday, March 7, 2012

DB Mnt plan issues

DB Mnt plan issues

DB mirroring setup

Saturday, February 25, 2012

DB Maintenance Plan reporting

Sunday, February 19, 2012

DB mail fails with permission error

DB mail fails with permission error

DB Mail config

DB Mail config

DB Mail config

Tuesday, February 14, 2012

DB detach/attach lost data?

DBCC CHECKDB

Blog Archive

About Me