Automate to get version number for a list of SQL Instances

We have a bunch of SQL Server instances in our domain. Some of them
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>

Automate setting permissions to databases (SQL 2005)?

Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>I set a long list of granular user database permissions with every reinstall
of my development database and I would like to automate this work with a
script instead of doing this manually in SQL Server Management Studio.
Is there a security settings export to file option? Or maybe some script
samples that show how to set user permissions?
I saw this example, but it requires both old and new databases to be running
at once. I reinstall the same PC repeatedly so this doesn't work for me:
http://www.sql-server-performance.c...ion_scripts.asp
Thanks!
Adam|||Here is a bunch of links for issues in that area:
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
Andrew J. Kelly SQL MVP
"AdamM" <adam@.nospam.com> wrote in message
news:un9nntilGHA.1272@.TK2MSFTNGP03.phx.gbl...
>I set a long list of granular user database permissions with every
>reinstall of my development database and I would like to automate this work
>with a script instead of doing this manually in SQL Server Management
>Studio.
> Is there a security settings export to file option? Or maybe some script
> samples that show how to set user permissions?
> I saw this example, but it requires both old and new databases to be
> running at once. I reinstall the same PC repeatedly so this doesn't work
> for me:
> http://www.sql-server-performance.c...ion_scripts.asp
> Thanks!
> Adam
>
>