Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

How can i stored procedure in openrowset?

How can i run following tsql?


USE master
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','servename';'sa';'password',
    'EXEC master.dbo.xp_fixeddrives') AS a
GO
0
Bharat Guru
Asked:
Bharat Guru
  • 3
  • 2
1 Solution
 
adatheladCommented:
I don't think you can call extended stored procedures using OPENROWSET. Try using linked servers:

To add a linked server:
EXECUTE sp_addlinkedserver 'servername', N'SQL Server'

To define the login to use on this linked server:
EXECUTE sp_addlinkedsrvlogin 'servername', 'false',           'localusername', 'linkedserverusername', 'linkedserverpassword'

Then you can run the query as:
EXECUTE servername.master.dbo.xp_fixeddrives
0
 
Eugene ZCommented:
You wrote:
>How can i run following tsql?

Answer: Successfully - for example from QA
0
 
Bharat GuruAuthor Commented:
when i run above sql some how i'm geting error and i don't want to create a linkserver.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
adatheladCommented:
I think the error is because you're trying to call an extended stored procedure. It would work if you were calling a standard stored procedure.
0
 
adatheladCommented:
This works:

EXECUTE OPENDATASOURCE('SQLOLEDB', 'Data Source=ServerName;User ID=UserName;Password=Password'
         ).master.dbo.xp_fixeddrives
0
 
Bharat GuruAuthor Commented:
Excellent thanks a lot, Thats work perfectly
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now