April 14, 2010 at 2:04 am
HI, I got a error using this way:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
But the mail was sent by sql server. How to fix the error message:-P
C.G
April 15, 2010 at 3:13 am
Colin please let us know which method u r using Mohammed or saby
October 14, 2011 at 2:04 pm
Thank you Moin Sir:-) I read this article today.;-)Just awesome.!!!!
--Boj
November 3, 2011 at 11:09 am
Does this only run if the job fails? Can you make this run when jobs also complete? On our 2005 machines
we have all jobs will email on failure except the user databases we have email upon completion.
I am also not sure about this,
exec SQL_Server_2005.msdb.dbo.sp_send_dbmail
@profile_name = 'SampleSQLServer2005dbmailProfile',
@recipients='yourname@email.com',
--@copy_recipients = 'yourgroup@email.com',
@subject='Job test_db_mail failed on SQL Server 2000 server',
@importance='HIGH',
@body ='Job test_db_mail failed on SQL Server 2000 server'
--------------------------------------------------------------
Profile name is the name of the account with no @companyemail.com at the end.
Is that also reentered in the @recipients only with the @companyemail.com at the end?
I am assuming that we don't have to use copy_recipients if we don't want, that is optional?
Any help is appreciated.
November 4, 2011 at 6:50 am
danskisanjar (11/3/2011)
Does this only run if the job fails? Can you make this run when jobs also complete? On our 2005 machines we have all jobs will email on failure except the user databases we have email upon completion.
This is just an example. It shouldn't matter if job fails or not.
I am also not sure about this,
exec SQL_Server_2005.msdb.dbo.sp_send_dbmail
@profile_name = 'SampleSQLServer2005dbmailProfile',
@recipients='yourname@email.com',
--@copy_recipients = 'yourgroup@email.com',
@subject='Job test_db_mail failed on SQL Server 2000 server',
@importance='HIGH',
@body ='Job test_db_mail failed on SQL Server 2000 server'
--------------------------------------------------------------
Profile name is the name of the account with no @companyemail.com at the end.
Profile name is the database profile you configured in SQL Server 2005
Is that also reentered in the @recipients only with the @companyemail.com at the end?
No, @recipients is your email address
I am assuming that we don't have to use copy_recipients if we don't want, that is optional?
Yes, it is optional
M&M
November 4, 2011 at 8:53 am
Thank you for taking the time to reply. I am not sure why I use my email for this. On my 2005 servers, I have that account set up so it goes to mine and another persons inbox, using sqlaccount@mycompany.com.
But first thing is first. I can't even connect to this server. It tells me that the server doesn't exist or that the account is not valid, when the account is valid and is a sys admin on the 2005 server and has dbowner priveledges to the msdb database.
I am going from a SQL 2000 SP3 machine to SQL 2005 SPS x86. The 2000 machine is sitting on a 2003 x86 VM. Both machines are members of the domain.
Any hints? Thanks again.
November 4, 2011 at 10:29 am
Did you set up the linked server on SQL Server 2000 successfully?
M&M
November 4, 2011 at 12:03 pm
I don't know.
When I try to connect I get this error.
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
That is using for
@server='ServerName, i.e. computername ',
and
@datasrc='default instance name i.e. MSSQLSERVER'
-------------------------------------------
If I change
@datasrc='computername'
I get a different error.
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'domain\useraccount'.
If I change
@datasrc='computername\MSSQLSERVER'
I get a different error.
Server: Msg 14, Level 16, State 1, Line 1
Invalid connection.
-------------------------------------------
Named pipes is not enabled on SQL 2005 machine, only TCP/IP is enabled. Does named pipes have to be enabled?
------------------------------------------
Also I am using a windows account that is a sqldomainadmin account and DOES have
owner rights to the database on 2005.
Any help is appreciated.
November 5, 2011 at 9:38 am
First, go to SQL Server 2005 management studio and type
select @@servername
Then go to SQL Server 2000 Query Analyser and replace the above result in @server and @datasrc parameters for the sp_addlinkedserver procedure.
Then try validating if you configured linked server correctly.
M&M
November 6, 2011 at 8:53 pm
Thank you for you help. I double checked and did what you said, and I did already tried this. This is the one that gave me this
result.
If I change
@datasrc='computername' = what you told me to do which was 'select @@servername'
which gave me what's below using the test query select * from 'servername'.msdb.dbo.sysjobs
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'domain\useraccount'.
I don't get it. I triple checked the login and made sure that the account 'domain\domain account' is owner of all databases including the msdb, is a sysadmin on both machines.
Thank you for all your help.
November 7, 2011 at 8:29 am
I couldn't figure out why it kept saying the login was failing and I was logged in to both machines as that same user, so I change it from BE MADE USING THIS SECURITY CONTEXT and putting the account in there to
BE MADE USING THE LOGIN'S CURRENT SECURITY CONTEXT and it started working with the query, so I ran the "failing backup job" and it emailed me!
So, now I just have to tweak this thing.
Thank you so very much for your help! It is much appreciated!
November 8, 2011 at 9:35 am
Nice to know setup is working fine :hehe:
M&M
November 8, 2011 at 10:36 pm
Could I ask one more question?
I have about 10 SQL2000 servers. I have this working on about half of them now, but I ran into this.
I have two SQL2000 servers, ServerA and ServerB.
ServerA has under Enterprise management, both information from Server A and ServerB. On ServerB there is only information for
ServerB.
I added SQL2005 server to the linked server in both ServerA and and ServerB. SQL 2005 shows up in the linked servers area of them both. I can actually see the tables and views in the linked servers area on both ServerA and ServerB (I have data access temporarily enabled for testing purposes.) But when I run query analyzer (the test query) on both ServerA and ServerB, it fails and returns this.
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server_2005' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
But I did execute just like I did on the other servers and it shows up like the others.
The 'failed backup' doesn't email me on these two servers as well. Do you or anyone else have any ideas on this? It would be appreciated.
November 10, 2011 at 1:09 am
Did you query sysservers to find out if 'Server_2005' exists srvname column
M&M
November 10, 2011 at 2:41 pm
Yes. I even opened that table and the same table of a different server that works, side by side and went through every column. They matched up exactly.
Viewing 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply