InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 19:55:23 GMT20http://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxThanks for that realised 5 minutes after posting this. all working fine now.Thanks again.Tue, 12 Jun 2012 09:16:57 GMTCliveWhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxPlace [] around the database name.Tue, 12 Jun 2012 08:51:46 GMTRichard Moore-400646http://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxNice article all set-up and works fine in Query Analyser but fails as a job this is down to the way our servers are named any suggestions on getting round this.In Query Analyser can use.exec "res-sql-002".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'But soon as trying to add within a job will not parse with error around the -sql-Error 170 line 1 Incorrect syntax near sqlTue, 12 Jun 2012 08:47:42 GMTCliveWhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxThat is ok. You have been a big help to me. Thanks!Fri, 11 Nov 2011 08:16:12 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI am not quite sure on this one.Fri, 11 Nov 2011 06:39:16 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxYes. 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.Thu, 10 Nov 2011 14:41:11 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxDid you query sysservers to find out if 'Server_2005' exists srvname columnThu, 10 Nov 2011 01:09:38 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxCould 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 forServerB.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 1Could 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.Tue, 08 Nov 2011 22:36:08 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxNice to know setup is working fine :hehe:Tue, 08 Nov 2011 09:35:44 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI 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 toBE 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!Mon, 07 Nov 2011 08:29:23 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxThank 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 thisresult.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.sysjobsServer: Msg 18456, Level 14, State 1, Line 1Login 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.Sun, 06 Nov 2011 20:53:57 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxFirst, go to SQL Server 2005 management studio and type select @@servernameThen 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.Sat, 05 Nov 2011 09:38:33 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI don't know. When I try to connect I get this error.Server: Msg 17, Level 16, State 1, Line 1SQL 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 1Login failed for user 'domain\useraccount'.If I change@datasrc='computername\MSSQLSERVER' I get a different error.Server: Msg 14, Level 16, State 1, Line 1Invalid 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 haveowner rights to the database on 2005.Any help is appreciated.Fri, 04 Nov 2011 12:03:37 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxDid you set up the linked server on SQL Server 2000 successfully?Fri, 04 Nov 2011 10:29:13 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxThank 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.Fri, 04 Nov 2011 08:53:43 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]danskisanjar (11/3/2011)[/b][hr]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. [/quote]This is just an example. It shouldn't matter if job fails or not.\[quote]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. [/quote]Profile name is the database profile you configured in SQL Server 2005[quote] Is that also reentered in the @recipients only with the @companyemail.com at the end? [/quote]No, @recipients is your email address[quote] I am assuming that we don't have to use copy_recipients if we don't want, that is optional?[/quote]Yes, it is optionalFri, 04 Nov 2011 06:50:10 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxDoes this only run if the job fails? Can you make this run when jobs also complete? On our 2005 machineswe 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.Thu, 03 Nov 2011 11:09:19 GMTdanskisanjarhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxThank you Moin Sir:-) I read this article today.;-)Just awesome.!!!!--BojFri, 14 Oct 2011 14:04:39 GMTMithrahttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxColin please let us know which method u r using Mohammed or sabyThu, 15 Apr 2010 03:13:10 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxHI, I got a error using this way:Msg 0, Level 11, State 0, Line 0A 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:-PWed, 14 Apr 2010 02:04:34 GMTColinChenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxsrinivas please go for this blog which might clear ur doubts.....http://databasemail.blogspot.comSat, 03 Apr 2010 23:39:38 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxSince the query is referring to the database in SQL Seerver 2000, when I creaetd the query to call sp_db_mail in SQL 2005 through link server , It came out with databse objet not found After creating two way link servr it worked. Provide me how to create a query out put from SQL Server 2000 in dbmail using the specified approach Sat, 03 Apr 2010 04:32:58 GMTM A Srinivashttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxOnly one Linkserver need to be created .....Sat, 03 Apr 2010 00:39:52 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxIf you want to send query result using link server approach , you need to create two way link server and provide access to databases in sql 2000 to the user in sql server 2005 Fri, 02 Apr 2010 01:46:52 GMTM A Srinivashttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxAny suggestion on this will be highly appreciated.......Thu, 01 Apr 2010 23:47:46 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxHi all, I have Implemented the Mail system on my sqlserver 2000.Here as my jobs fails the mail will be send my the 2005 mailing server.I have not used the concept of Mohammed M(the article is good) what i did is [code="sql"] --[center][/center]Here are the steps [Step 1]Created two tables on 2000 and 2005 server ---called--Temp_Job_FailWrite query on last step of the each job on sqlserver 2000insert into Temp_Job_Failselect b.name,b.originating_server,a.run_date,a.message,a.step_name from msdb.dbo.sysjobhistory a,msdb.dbo.sysjobs bwhere a.job_id = b.job_id and a.message like '%Error%' and a.run_date = convert(varchar(10),getdate(),112)and a.message not like '%DBCC printed error messages%'(INSERT INTO [Linked Server].Databasename.DBO.Temp_Job_Fail---2005 serverSELECT * FROM Temp_Job_Fail -- 2000 server)[STEP 2]createD a trigger on server 2005--- to run the JobCREATE TRIGGER trg_Insert_Temp_Job_Fail ON Temp_Job_Fail AFTER INSERT AS BEGIN if (SELECT NAME from DATABASENAME.dbo.Temp_Job_Fail)is not null begin EXEC msdb.dbo.sp_start_job @job_name = 'MailSendingForJobFailedin2000' end else Print 'Issue' END [STEP 3]Created a job on sqlserver 2005 which will send the mailJobs steps--Step1declare @body1 varchar(100)set @body1 = ''Server :250''+ '' Email Alert ''EXEC msdb.dbo.sp_send_dbmail @recipients=''MailId'', @subject = ''Job Failed Mail Status'', @body = @body1, @body_format = ''HTML'', @query = ''SELECT * from databasename.dbo.Temp_Job_Fail'' , @attach_query_result_as_file = 1 ;---Step2Truncate table DATABASENAME.dbo.Temp_Job_Fail[center][/center][/code]Thu, 01 Apr 2010 06:45:17 GMTsabyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]Lilita (3/15/2010)[/b][hr]sorrYIt didn't help I run job and getExecuted as user: sa. Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002. [SQLSTATE 42000] (Error 15404). The step failed.[/quote]Thanks, will have to check this.Tue, 16 Mar 2010 11:27:31 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]tferguson (3/16/2010)[/b][hr]Neat concept, but did you consider that since you've added a linked server to connect to your 2005 instance, anyone with a login on the 2000 instance now can access your 2005 server's msdb database with db_owner permissions via the linked server? Access to a linked server cannot be controlled, so we avoid them.[/quote]tferguson, Thanks for your comments.This security question came up earlier in the discussion. In my company, the access control is very strict. Only people in my team can get high level access. The others from the application team etc, would be give only select access on few tables for few hours. Due to this, I did not facesecurity issues when this was configured on my servers. Tue, 16 Mar 2010 11:26:41 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxNeat concept, but did you consider that since you've added a linked server to connect to your 2005 instance, anyone with a login on the 2000 instance now can access your 2005 server's msdb database with db_owner permissions via the linked server? Access to a linked server cannot be controlled, so we avoid them.Tue, 16 Mar 2010 09:29:28 GMTtfergusonhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxsorrYIt didn't help I run job and getExecuted as user: sa. Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002. [SQLSTATE 42000] (Error 15404). The step failed.Mon, 15 Mar 2010 02:27:17 GMTLilitahttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]Lilita (3/14/2010)[/b][hr]Hi,I read the link you advised me.Thank youI tried to connect to server 2000 with saand run the ...msdb.dbo.sp_send_dbmail ... I get the same error: "Could not obtain information about Windows NT group/user 'usr_testmail'" I saw in Help :"Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message."May be you know how to give permission to use the profile. I think that it the point of my problem.By the way, if i enter credentials as SA for linked server security context, sp_send_dbmail works. [/quote]You could try this on SQL Server 2005[b]use msdbgoGRANT EXECUTE ON [dbo].[sp_send_dbmail] TO [usr_testmail][/b]Please let us know if this works for you.Sun, 14 Mar 2010 13:11:58 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxHi,I read the link you advised me.Thank youI tried to connect to server 2000 with saand run the ...msdb.dbo.sp_send_dbmail ... I get the same error: "Could not obtain information about Windows NT group/user 'usr_testmail'" I saw in Help :"Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message."May be you know how to give permission to use the profile. I think that it the point of my problem.By the way, if i enter credentials as SA for linked server security context, sp_send_dbmail works. Sun, 14 Mar 2010 11:16:47 GMTLilitahttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]CirquedeSQLeil (3/9/2010)[/b][hr]Thanks for the article. Unfortunately there are plenty of 2000 servers still in production (75% of mine are 2000). It was quite a refresher course when coming from a SQL 2005 shop.[/quote]Thank you JasonThu, 11 Mar 2010 16:33:59 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]Lilita (3/11/2010)[/b][hr]I tried it from Query window.I always test my scripts in Query Window before job's creation. I suppose your code must work from Query Window too.I have permissions of administrator.[/quote]I came across this link related to your error message. http://www.sqlservercentral.com/Forums/Topic555269-146-1.aspxCould you please tryThu, 11 Mar 2010 16:33:12 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI tried it from Query window.I always test my scripts in Query Window before job's creation. I suppose your code must work from Query Window too.I have permissions of administrator.Thu, 11 Mar 2010 04:37:13 GMTLilitahttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]mohammed moinudheen (3/8/2010)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server+2000/69475/">How to use Database mail feature in SQL Server 2000</A>[/B][/quote]Good use of linked server to make use of Database mailWed, 10 Mar 2010 14:16:53 GMTBuntyBoyhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspx[quote][b]Lilita (3/10/2010)[/b][hr]when I try this idea, i get the errorMsg 15404, Level 16, State 10, Line 1Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002.'usr_testmail' - is sqlServer userSelect * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs') works fineWhat can be the reason of the error?[/quote]Lilita, Are you getting this error message when you are running the dummy job.If so, could you kindly confirm the job owner. Please change the job owner to sa, if it is not and try.Wed, 10 Mar 2010 10:31:02 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI've used them for years with no issues. There are some tricks for getting some setup, but have never had any reliability issues. I have links from 2000 to 2005, 2005 x64 to 2000 x86, 2000 to DB2, 2005 to DB2, and 2000 to Active Directory. I have learned to use OpenQuery as much as possible to increase performance.Wed, 10 Mar 2010 06:34:30 GMTMeow Nowhttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxI heard a dba state that linked servers are not reliable and can fail anytime for no reason. Is this correct?Wed, 10 Mar 2010 05:24:22 GMTgaurav.bhagathttp://www.sqlservercentral.com/Forums/Topic879134-2643-1.aspxwhen I try this idea, i get the errorMsg 15404, Level 16, State 10, Line 1Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002.'usr_testmail' - is sqlServer userSelect * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs') works fineWhat can be the reason of the error?Wed, 10 Mar 2010 03:52:10 GMTLilita