If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to execute DBCC statements through remote stored procedures

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 22
How to execute DBCC statements through remote stored procedures

Hello,

Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.

I donít want to use isql, osql, openquery, openresultset because I have to pass user id or password.

I have linked the required SQL servers using Linked servers.

If so please give some example.

Thanks in Advance,
Sateesh
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
Yup, it is. Do a search
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 22
How to execute DBCC statements through remote stored procedures

I am not able to get solution for that
Please give me some example

I tried with the following ways but it's not working

Wrong Statemetns:
EXEC ('SrvSateesh.master.dbo.sp_execsql dbcc showfilestats')
SELECT * FROM OPENQUERY(SrvSateesh, 'use master 'dbcc showfilestats'')

DECLARE @strsql VARCHAR(100)
SET @strsql = 'dbcc showfilestats'

SELECT * FROM OPENQUERY('SrvSateesh', @strsql)

SELECT * FROM OPENQUERY(SrvSateesh, @strsql)

ELECT * FROM OPENQUERY(SrvSateesh, 'EXEC sp_executesql @statement = @strsql ')


Quote:
Originally posted by rdjabarov
Yup, it is. Do a search
Reply With Quote
  #4 (permalink)  
Old
Just DBA
 
Join Date: Jul 2002
Location: Village, MD
Posts: 621
Create sp on linked server and call it from another server:

-- on linked server
CREATE PROCEDURE JustDoIt @sql varchar(8000)
AS
exec(@sql)
GO
--
exec server.testdb.dbo.JustDoIt 'DBCC CHECKDB (testdb)'
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 22
How to execute DBCC statements through remote stored procedures

Thanks snail...that will work....but my requirement is different

But we don't want to create any maintenance procedurs in all existing 50+ servers
We want to ceate a single procdure in a single server for collecting free space information of all files(mdf and ndf) existing in all linked servers.........


waiting for reply
Reply With Quote
  #6 (permalink)  
Old
Just DBA
 
Join Date: Jul 2002
Location: Village, MD
Posts: 621
Good luck in your efforts! We have even more servers and every server has special db for the same purposes (maintenance, checking, etc.). It is much easier to manage servers in this case - every server is independent. You just need to query servers for results from any place not only from central server. What if your central server went down?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 22
How to execute DBCC statements through remote stored procedures

What ever you said is write. Itís very easy to write/manage stored procís, creating jobs and scheduling them in the same server (no complex work).

If you have any idea about how to create stored procedures remotely or the question that I asked you then give me the reply other wise leave it Buddy. I am not interested on how many servers that you are having.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
...And I am not interested helping someone like you, especially with this kind of attitude
Reply With Quote
  #9 (permalink)  
Old
Just DBA
 
Join Date: Jul 2002
Location: Village, MD
Posts: 621
Quote:
Originally posted by rdjabarov
...And I am not interested helping someone like you, especially with this kind of attitude
Reply With Quote
  #10 (permalink)  
Old
The SQL Apostle
 
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,397
Quote:
Originally posted by rdjabarov
...And I am not interested helping someone like you, especially with this kind of attitude
And that is one attitude i would respect.
__________________
Get yourself a copy of the The Holy Book

order has no physical Brett in The meaning of a Kaiser . -database data
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On