Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

How to use Database mail feature in SQL Server 2000 Expand / Collapse
Author
Message
Posted Friday, November 4, 2011 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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.
Post #1200654
Posted Friday, November 4, 2011 10:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Did you set up the linked server on SQL Server 2000 successfully?

Mohammed Moinudheen
Post #1200753
Posted Friday, November 4, 2011 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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.
Post #1200813
Posted Saturday, November 5, 2011 9:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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.



Mohammed Moinudheen
Post #1201050
Posted Sunday, November 6, 2011 8:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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.
Post #1201211
Posted Monday, November 7, 2011 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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!
Post #1201467
Posted Tuesday, November 8, 2011 9:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Nice to know setup is working fine

Mohammed Moinudheen
Post #1202265
Posted Tuesday, November 8, 2011 10:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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.
Post #1202607
Posted Thursday, November 10, 2011 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Did you query sysservers to find out if 'Server_2005' exists srvname column

Mohammed Moinudheen
Post #1203315
Posted Thursday, November 10, 2011 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 2:01 PM
Points: 8, Visits: 51
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.
Post #1203851
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse