Passing servername as parameter in sqlserver.

Question

Post reply

Passing servername as parameter in sqlserver.

Mithra

SSChasing Mays

Points: 639
More actions
October 30, 2012 at 1:43 pm

#264817

Team,

I am trying to write a generic stored procedure to check all the processes running in the server.I will be using 'select * from sysprocesses.How can i deploy this in a stored proc by making servername as a parameter.Basically i want to know how to pass 'servername' as a parameter in stored procedure in sqlserver.A basic script will be helpful,since i am very new to TSQL and I work only on administration side.

Thanks in advance,

Mithra.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Mithra (10/30/2012)

Team,

I am trying to write a generic stored procedure to check all the processes running in the server.I will be using 'select * from sysprocesses.How can i deploy this in a stored proc by making servername as a parameter.Basically i want to know how to pass 'servername' as a parameter in stored procedure in sqlserver.A basic script will be helpful,since i am very new to TSQL and I work only on administration side.

Thanks in advance,

Mithra.

Not quite sure what you mean by servername. Are you saying you want to create a proc on one of your database servers that can query sys.sysprocesses from any other server on your network?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mithra SSChasing Mays Points: 639 More actions · Answer 2

Mithra

SSChasing Mays

Points: 639

October 30, 2012 at 2:32 pm

#1554382

Yes Sean.That right.I need to create a sp in one of my test server and have that sp to pull all the details from another server in the network by just passing a server name as parameter.

Thanks,

Mithra.

Lowell SSC Guru Points: 323482 More actions · Answer 3

ok weird requirement, this won't work if ad Hoc is disabled, obviously.

this works for me on my dev machine, which does allow adhoc:



--DROP PROC pr_Processes

CREATE PROCEDURE pr_Processes

@SERVERNAME varchar(128)

AS

BEGIN --PROC

DECLARE @SQL varchar(8000)

SET @SQL = 'SELECT  * FROM  OPENROWSET( ''SQLNCLI'',

                    ''Server={DbNameplaceholder};Trusted_Connection=yes;'',

                    ''SET FMTONLY OFF; SET NOCOUNT ON; select * from sysprocesses''

                  )'

SET @SQL = REPLACE(@SQL,'{DbNameplaceholder}',@SERVERNAME)

EXEC(@SQL)

END --PROC

GO

EXEC pr_Processes 'DEV223'

/*

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286562 More actions · Answer 4

Mithra (10/30/2012)

Yes Sean.That right.I need to create a sp in one of my test server and have that sp to pull all the details from another server in the network by just passing a server name as parameter.

Thanks,

Mithra.

You will need to have a link server setup on the same machine where this sproc will live that will point to each server you want to query.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

Then you will have to execute your query via dynamic sql.

This is a very basic look at how you can do this.



declare @ServerName varchar(50)

set @ServerName = 'YourLinkServerNameHere'

declare @sql varchar(max)

set @sql = 'select * from ' + @ServerName + '.master.sys.sysprocesses'

exec (@sql)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/