0

I'm facing a problem. I have these 3 servers:

1 Server1
2 Server2
3 Server3

I want to create a job on Server1 which will send email in html format include drives spaces xp_fixeddrives of all three servers.

Please help me - what will be best scenario to achieve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahsan Mumtaz Abbasi
  • 53
  • 1
  • 11
  • What part exactly are you needing help with? Just executing the stored procedure across linked servers? Setting up dbmail? Creating the linked server? – SQLChao Apr 19 '18 at 18:46
  • sir i have created 3 stored procedures on server 1,2 & 3. which will dump data into their respective tables. but i'm unable to run that stored procedure by link server. – Ahsan Mumtaz Abbasi Apr 19 '18 at 18:50
  • Are the servers set up properly as Linked Servers? Does the user executing the base sproc have necessary permissions? Do all of the linked servers have proper permissions? – Shawn Apr 19 '18 at 18:51
  • Are the links set up properly? Can you log in to Server 1 and run a query to execute the Server 2 sproc? – Shawn Apr 19 '18 at 18:52
  • yup sir user have sa level rights to test this scenerio . for example i'm unable to run this selection query as well by link server : exec [link server name].[database name].[scehma name].xp_fixeddrives – Ahsan Mumtaz Abbasi Apr 19 '18 at 18:53
  • Just to be clear, you want the job to execute the sprocs on each of the servers and then query the tables that those sprocs populate to dump the results into an HTML email? – Shawn Apr 19 '18 at 18:55
  • yes sir actually i follow broth options but couldn't success. when i run EXECUTE [linkserver2].database..xp_fixeddrives on server 1 it return no record. but simply execute xp_fixeddrives on server2 return valid result set. – Ahsan Mumtaz Abbasi Apr 19 '18 at 18:57
  • OK, just a note, for testing, `sa` is OK, but I would HIGHLY recommend creating users specifically to do just the one operation they're needed to do. – Shawn Apr 19 '18 at 19:01
  • Does it `EXECUTE` or does it fail with an error? – Shawn Apr 19 '18 at 19:03
  • its executed successfully but no record found. – Ahsan Mumtaz Abbasi Apr 19 '18 at 19:15
  • But it does show a recordcount if you run the query locally? Which user are you running locally with? All of this with the `sa` account? – Shawn Apr 19 '18 at 21:15

1 Answers1

0

If you have linked server for server2 & server3 in server1 then you can execute like below

EXEC master.dbo.xp_fixeddrives
EXEC Server2.master.dbo.xp_fixeddrives
EXEC Server3.master.dbo.xp_fixeddrives

Best approach is run the powershell script from SQL job. please check the below example https://gallery.technet.microsoft.com/scriptcenter/PowerShell-and-Notification-50630dc9

ASP
  • 666
  • 4
  • 9
  • thank you sir its working already but i need to insert all 3 of above queries result into #temp from where i can get like: insert into #temp exec master.dbo.xp_fixeddrives...etc – Ahsan Mumtaz Abbasi Apr 19 '18 at 21:18