How to get two different SQL servers to talk to one another

Hi all I need to create a view for another sql server on the network.

I can create a view for two to databases on the same sql server to talk but how do you do it for a differend sql server??

CREATE VIEW dbo.Revocations_View
AS
SELECT TM#, LastName, FirstName, MI, SSN, [I/R #], Date, ReasonofRevocation, Notes, Termination, Conditional, WasEmployeeFined, LicenseSuspension,
Status
FROM LicensingActions.dbo.Revocations_TblUse sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) to put the servers on "speaking terms" with each other. In a secured network, you may have to deal with Security Account Delegation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp). After you've resolved that, you need to use four part names (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_5vvp.asp) and you're in business.

-PatP|||thanks pat thats exaclty what I need
appreciate it :)|||a fine meal and big bottle of wine outta do it. could'nt resist.

How to get the OS Version

Hey guys,
My team supports databases on about 75 different servers. I would like to know what OS Version is running on those server. I have done some research and I know I can use the following three methods:
1. master..xp_msver
or
2. master..xp_cmdshell 'netsh diag SHOW os /p'
or
3. select right(@.@.version, 44)

are there any other options out there? Option 2 gives me the output I would like, but takes a long time to return the result:
i.e.
Microsoft(R) Windows(R) Server 2003, Standard Edition
5.2.3790

xp_msver and @.@.version gives me the info, but not quite in the format I would like:
5.2 (3790)
and
Windows NT 5.2 (Build 3790: Service Pack 1)

Are there any other options out there?

Thanks,
ReghardtIs this a one time gathering of statistics, or an ongoing thing/ If you have SMS on your network, you can query some of their views much more effectively.|||It will be an ongoing thing, and yes we do have SMS. Thanks for the advice I have to remember to sometimes think outside the box.

How to get the list of sql servers

Hi,
Am on a LAN and there are several sql servers running. I wanted know the list of servers with the version. I know we can get the list of servers using cmd line prompt "OSQL -L", but i need to know the version of each server also. Is there someway to get this information for all the sql servers in the LAN
Thanks very muc
YogishYou could use SQLDMO for this.Check out
http://www32.brinkster.com/srisamp/sqlArticles/article_15.htm. You can
easily extend the procedure using more properties of SQLDMO to return the
information that you want.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:F03C14A9-47C6-4010-BD6B-1D7B4CB3A417@.microsoft.com...
> Hi,
> Am on a LAN and there are several sql servers running. I wanted know the
list of servers with the version. I know we can get the list of servers
using cmd line prompt "OSQL -L", but i need to know the version of each
server also. Is there someway to get this information for all the sql
servers in the LAN?
> Thanks very much
> Yogish

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
Frank
Frank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>
|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Frank
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
table.[vbcol=seagreen]
table?
>

How to get service accounts for 150 servers

Hi Everyone.

I have 150 SQL servers (2000 MSDE). They all run using various domain accounts as their service logins. Is there an automated way to find out those service logins? Maybe a query I could run on each server?

I really do not want to go to each of those 150 servers and look at their properties manualy! :S

Any help would be greatly appreciated! Thank you.Something around the master.dbo.sysusers and/or master.dbo.syslogins
table(s) ?|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.

-PatP|||I think that you want the startname value from this (http://www.microsoft.com/technet/scriptcenter/scripts/os/services/ossvvb08.mspx) script.

-PatP

Yeah, I would think that WMI would be your best bet. Dump the list of servers into a text file (or XML). Open that file using the File system object and spin through (using oFile.ReadLine). Then feed the string value (the computer name) into a function that returns the name of of the service account you are looking for. Dump that into a separate text file (or a database).

Regards,

hmscott|||Thank you everyone for responding. I got an answer at experts-exchange. The following query will return the service account:

declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQLServer\',N'ObjectName', @.dir output, 'no_output'
select @.dir

I've modified it put the result in the central table and then ran it through a SQL loop. Worked perfectly! :D

WMI would have worked as well, but this solution took just 10 min to implement.

P.S. Had no idea that something like xp_instance_regread existed. Is too much to hope that sql server 2005 will have better documentation? :-)|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

-PatP|||That Transact-SQL code will work nicely as long as you only need the default instance of SQL 2000.

-PatP

Good point, Pat. For named instances the command will need to be modified:

declare @.rc int,
@.dir nvarchar(4000)
exec @.rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'System\CurrentControlSet\S ervices\MSSQL$Your_Instance_Name\',N'ObjectName', @.dir output, 'no_output'
select @.dir

How to get rid of unused servers in SSMS?

In Connect to Server dialog box in SQL Server Management Studio, it has a drop-down box with a list previously connected servers. However, some of these servers are not used anymore. I want to get rid of them in order to make a room for new server names. So far I could not find a way how to do this, apparently SQL Server does not store these values in the Registry. Is there a way to get rid of them ?

Thanks.

I looked earlier today for you and like you it appears the registry is not the "store" for this data. Upon looking through all of the "known" sql directories my hunch (though I cannot find it) would be that its somewhere at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.|||

You can blow away all your Most Recently Used lists (including MRU connections) by deleting c:\Documents and Settings\<you>\Application Data\Microsoft SQL Server\90\Tools\Shell\mru.dat while Management Studio is not running. Management Studio will recreate the file the next time it starts.

Hope this helps,
Steve

How to get remote server datetime

Hi All ,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Haven't tested but it should work using Openquery instead of
the 4 part name. The statement passed in the openquery is
executed on the remote server.
-Sue
On Fri, 27 Apr 2007 12:24:01 -0700, Ram
<Ram@.discussions.microsoft.com> wrote:

>Hi All ,
>I have 3 sql servers located at different time zones. Say, CST,PST,EST.
>Now how can I get current time at EST,PST from the SQL server located
>at CST? Is there any query to do that?
>I have a stored proc located in SQL server at CST zone where I need to
>query for the current date/time of the other zone sql servers.
>I tried below query at CST SQL server
>SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
>But it always gives CST datetime.
>Please reply...
>Thanks
>RP
|||Hi Sue,
It works gr8...
Thanks for the help...
"Sue Hoegemeier" wrote:

> Haven't tested but it should work using Openquery instead of
> the 4 part name. The statement passed in the openquery is
> executed on the remote server.
> -Sue
> On Fri, 27 Apr 2007 12:24:01 -0700, Ram
> <Ram@.discussions.microsoft.com> wrote:
>
>
|||Hi,
Can you please write the exact syntax you used for Openquery? I also have
requirement similar to this.
Thanks for your help.
Namwar
"Ram" wrote:
[vbcol=seagreen]
> Hi Sue,
> It works gr8...
> Thanks for the help...
>
> "Sue Hoegemeier" wrote:

How to get list of all Sql servers on network

Hi,
Is there any function/API in vb to retrieve all the sql servers that are avaiable on a network? Basically, i wanted to implement this facility in my application where users can select a server from list of all avaiable servers on LAN.

Thanks!
Asif.Net, Im having trouble with a couple of the TYPES that are used in the functions.

Have you got this figured out in .NET yet?

Please let me know if you have a resolution.

Thanks,
Malcolm Phillips
Malcolm.Phillps@.prgx.com|||If you get GFI lanscan you'll find there's a script there that enumerates SQL Servers on the network. Its basically VB script but I don;'t have it here at home.

To do it in asp.net I think you'd need to import the SQLDMO namespace|||How do I import the SQLDMO namespace? Please|||You add the Microsoft SQLDMO Object Library as a (COM) Reference to your project, then you add

<%@. Import Namespace="SQLDMO"%>

to your ASP.NET page.

Terri|||Take a look at http://www.extremeexperts.com/sql/faq/SQLDMO-ListServers.aspx for an example ... This works in VB also ...|||Dim mDMOApp As New SQLDMO.Application Dim mNames As SQLDMO.NameList Dim t As Integer mDMOApp = New SQLDMO.Application mNames = mDMOApp.ListAvailableSQLServers() lstServers.Items.Clear() For t = 1 To mNames.Count lstServers.Items.Add(mNames.Item(t)) Next