September 11, 2002 at 11:27 am
exec master.dbo.xp_getnetname
returns computer name the db is on.
How can I set the result to a local variable so that I can reference it later on?
Thanks
September 11, 2002 at 12:14 pm
Something like this will work:
create table #tmp_t (
[Server Net Name] varchar(1024)
)
insert into #tmp_t exec('master.dbo.xp_getnetname')
declare @netname varchar(1024)
select @netname = [Server Net Name] from #tmp_t
print @netname
drop table #tmp_t
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 12:28 pm
Any simpler way? or SQL server is just that stupid.
September 11, 2002 at 1:32 pm
Try this:
select @@servername
Andy
September 11, 2002 at 1:33 pm
Well now you are asking a different question.
If you have not changed the name of the server since installation of SQL Server, or used sp_addserver to change the name you could use the @@SERVERNAME .
Like:
declare @netname varchar(1024)
set @netname = @@SERVERNAME
print @netname
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 1:43 pm
If it's a SQL2K system you also have the option of:
SELECT SERVERPROPERTY('MachineName')Which means you could do the following:
DECLARE @MyServer nvarchar(1024)
SET @MyServer = (SELECT CAST(SERVERPROPERTY('MachineName') AS nvarchar))
PRINT @MyServer
You can check SERVERPROPERTY() vs. @@SERVERNAME to see if there is a mismatch. If you need to rename a server, here's the KB article. It points out a bug in the original documentation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303774
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 11, 2002 at 3:26 pm
Brian's solution solves the problem nicely. But in general, if I want to assign the result of a stored procedure to a local variable, is there a better way than that provided by Gregory?
Thanks
September 11, 2002 at 3:35 pm
If its a single value you'd normally design the proc to return an output parameter. I think you can do a hack using openrowset if you want to treat it like a table (assuming you have a resultset and not params).
Andy
September 11, 2002 at 4:02 pm
Here is an example of some code for the hack Andy was talking about.....
Create proc x
@rs cursor varying output
as
set @rs = cursor for select name from sysobjects
open @rs
go
-- to call this proc
declare @name char(128)
declare @results cursor
exec x @rs=@results output
fetch next from @results into @name
while (@@fetch_status <> -1)
begin
print @name
fetch next from @results into @name
end
close @results
deallocate @results
go
drop proc x
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 8:09 pm
Here is another way:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','yourserver;'sa';'sapassword',
'exec Northwind.dbo.salesbycategory ''beverages''') AS a
GO
Andy
September 14, 2002 at 9:01 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply