How to determine SQL version from a command line

Hi, I am trying to run a command line script against 50
SQL servers to determine the SQL ver and SP level
installed but when I run srvinfo -ns this returns way more
info then I require and does not include SP level. I have
run registry searches but this will only give the
installed version of SQL, it will not give me the latest
ver, i.e. if it has had an SP installed or not. Any help
would be great. ThanksYou can use OSQL from the command line to connect to SQL Server. Once
connected issue SELECT @.@.VERSION or use SERVERPROPERTY
Without using SQL you could always do a DIR and seach for sqlservr.exe, from
it's size and file data you should be able to work out which version.
--
HTH
Ryan Waight, MCDBA, MCSE
"Jonathon" <Jonathon_Taaffe@.hotmail.com> wrote in message
news:296d601c3919f$d3ea6f90$a601280a@.phx.gbl...
> Hi, I am trying to run a command line script against 50
> SQL servers to determine the SQL ver and SP level
> installed but when I run srvinfo -ns this returns way more
> info then I require and does not include SP level. I have
> run registry searches but this will only give the
> installed version of SQL, it will not give me the latest
> ver, i.e. if it has had an SP installed or not. Any help
> would be great. Thanks|||Jonathan,
Refer to following url:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321185
You can run these queries from command prompt, using osql utility by passing quries to -Q
parameter.
--
- Vishal|||You can run 'select @.@.version' with osql in dos.
>--Original Message--
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way
more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
>.
>|||In article <296d601c3919f$d3ea6f90$a601280a@.phx.gbl>, Jonathon
<Jonathon_Taaffe@.hotmail.com> writes
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
If you are looking for any SQL Servers then you could try SQL Scan as
well-
http://www.microsoft.com/sql/downloads/securitytools.asp
I use a combination of methods to monitor what servers appear on the
network and in what state.
The registry will tell you which SP you are running, but it will not
tell you if there are any patches on top as well.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVers
ion]
The CurrentVersion value gives you the base version, e.g 8.00.194 = SQL
Server 2000 RTM.
The CSDVersion key will then tell you the service pack level, e.g.
8.00.761 = SP3a. Note this is actually quite useful because the TSQL
@.@.VERSION and similar will only give you 8.00.760, which means SP3 or
SP3a. However since I also have the latest security patch installed
@.@.VERSION says 8.00.818, so a combination is often better.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org|||I've just tried, it worked without problem.
From a cmd line :-
OSQL -Sservername -Q"select @.@.Version" -E
--
HTH
Ryan Waight, MCDBA, MCSE
"Ray Miao" <rmiao@.bloomberg.com> wrote in message
news:03d801c391b4$f2bded60$a401280a@.phx.gbl...
> You can run 'select @.@.version' with osql in dos.
> >--Original Message--
> >Hi, I am trying to run a command line script against 50
> >SQL servers to determine the SQL ver and SP level
> >installed but when I run srvinfo -ns this returns way
> more
> >info then I require and does not include SP level. I have
> >run registry searches but this will only give the
> >installed version of SQL, it will not give me the latest
> >ver, i.e. if it has had an SP installed or not. Any help
> >would be great. Thanks
> >.
> >

How to determine programatically what datafiles are associated with a database

I'm writing a data collection script to gather database and data file
information. This information will be used for reporting and analysis. I
want to be able to tell what data files are associated with each of the
databases. sysfiles doesn't include the database name and sysaltfiles
doesn't include all of the datafiles.
Query I'm using:
use master
select f.file_name, f.name, ...
from master.dbo.sysaltfiles a ,
dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid
and db.name = 'master'
I can probably infer the database name from the name column but I'd rather
not go there!
Please don't tell me to go somewhere and point and click. I'm monitoring
almost 70 databases and this data needs to be collected on a daily basis.
Message posted via http://www.webservertalk.comevelyn,
use sp_msforeachdb:
exec sp_msforeachdb '
select db.name, f.name
from master.dbo.sysaltfiles a ,
?.dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid '
hth
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eebc4237a7204ec3a8b8ca5fab4890a5@.SQ
webservertalk.com...
> I'm writing a data collection script to gather database and data file
> information. This information will be used for reporting and analysis. I
> want to be able to tell what data files are associated with each of the
> databases. sysfiles doesn't include the database name and sysaltfiles
> doesn't include all of the datafiles.
> Query I'm using:
> use master
> select f.file_name, f.name, ...
> from master.dbo.sysaltfiles a ,
> dbo.sysfiles f ,
> master.dbo.sysdatabases db
> where a.name=f.name
> and a.fileid = f.fileid
> and a.dbid = db.dbid
> and db.name = 'master'
> I can probably infer the database name from the name column but I'd rather
> not go there!
> Please don't tell me to go somewhere and point and click. I'm monitoring
> almost 70 databases and this data needs to be collected on a daily basis.
> --
> Message posted via http://www.webservertalk.com|||Try this:
exec sp_MSforeachdb
'
use [?]
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
'|||For this query add brackets around the question mark. i.e. [?]|||What does sp_msforeachdb do? It is not in my online book. I hesitate to
run something in 70 production databases without knowing the possible
ramifications.
Message posted via http://www.webservertalk.com|||I figured out what the procedure does.
Perhaps I'm not clear I want the database name and all associated data file
names. The following query gives me table names.
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
Message posted via http://www.webservertalk.com|||it is undocumented and unsupported, meaning that ms might choose not to
include it in future versions of sql server. however, it is widely used. you
can check the definition (in master db) with:
exec sp_helptext 'sp_msforeachdb'
nothing fancy (builds a cursor internallly), but very handy.
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f3affb3bea874a3fbf0edb3bcc8b8868@.SQ
webservertalk.com...
> What does sp_msforeachdb do? It is not in my online book. I hesitate to
> run something in 70 production databases without knowing the possible
> ramifications.
> --
> Message posted via http://www.webservertalk.com|||Hi
The database may have two or more log files. You also need to see them
Look at this script helps you.
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:960a4cbd69a241458a0b54a9fd0b14ea@.SQ
webservertalk.com...
> I figured out what the procedure does.
> Perhaps I'm not clear I want the database name and all associated data
file
> names. The following query gives me table names.
> select so.name as Table_Name,
> sfg.groupname as GroupName,
> sf.name as LogicalName,
> sf.filename as PhysicalFileName
> from dbo.sysobjects so
> inner join sysindexes si
> on so.id = si.id
> inner join sysfilegroups sfg
> on si.groupid = sfg.groupid
> inner join sysfiles sf
> on sf.groupid = sfg.groupid
> group by so.name,
> sfg.groupname,
> sf.name,
> sf.filename
> --
> Message posted via http://www.webservertalk.com

How to Determine if a user is a member of the System Admin role?

Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')

> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

how to deploy a script

Hello,

What i'm looking for is a way to write a script which would change few data type fields in a specific table, in a specific database. Like i want to change it from char(4) to varchar(15). And then deploy this script, so that when he executes it, changes are reflected in his database. I may use query analyzer to excute i think. Plz also tell me how to write that script.(not sure if its called script).

Thank You.

Use the ALTER TABLE ALTER COLUMN command. Here is an example:

Code Snippet

ALTER TABLE MyTable ALTER COLUMN Col1 VarChar(100)

where MyTable is the table you're updating and Col1 is the column that you want to change its type.

I hope this answers your question.

Best regards,

Sami Samir

|||

if you don't need to alter your source database you can use this approach,

You can get all the table scripts from "SQL Server - Enterprise Manager" using Generate SQL Script.

After that you can find the Char(7) datatype and replace with varchar(15). (you can simply use any text editors Find & Replace)

|||

ya, i used following,

ALTER TABLE GRNs
ALTER COLUMN GRNNo VARCHAR(15) NOT NULL

i was just wondering if it can be conditional, like what's use of running it if its already varchar.

is their anything like if else?

|||

Try the following script:

Code Snippet

Declare @.Type as Int

Declare @.Length as Int

Select @.Type = System_Type_ID, @.Length = Max_Length

From sys.objects Inner Join sys.columns On sys.objects.Object_ID = sys.columns.Object_ID

Where sys.objects.Name = GRNs And sys.columns.Name = GRNNo

--Check that the column actually exists

If @.@.RowCount <> 0

Begin

--Check if it is already Varchar (ID is 167) and length is already 15

If @.Type <> 167 Or @.Length <> 15

Alter Table GRNs Alter Column GRNNo Varchar(15)

End

I hope this helps.

Best regards,

Sami Samir

how to delete the day before bak file created by sql

the below script in sql backup runs fine, however i need it to delete the 1
day prior bak first, before it starts the next day. its like this for
redundicy. but i dont have the space to perform the next day job because the
day before is on the drive. we bakup to tape every night. so we have a copy
of the bak file. i just need to buy some time before i replace the drive
sizes.
EXECUTE master..xp_sqlmaint '-D
PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
1DAYS -UpdOptiStats 15'
could i just create a new job to just delbkups 1 days ... and arange it to
go first (1) and on success goto (2)
please help
Am sure this can be done in a round about way. Can use xp_cmdshell to delete
the file before initiating the backup call. this way your old backup is
deleted and the backup process can succeed.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||In this situation we run a job that uses xp_cmdshell to delete the backup
file before our xp_sqlmaint is run. Might be a nice feature if the backup
job could offer to delete old backups before running a new backup.
Chris Wood
Alberta Department of Energy
CANADA
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||Chris Wood wrote:
> In this situation we run a job that uses xp_cmdshell to delete the backup
> file before our xp_sqlmaint is run. Might be a nice feature if the backup
> job could offer to delete old backups before running a new backup.
Hi,
It is not a good idea to remove the previous backup before you know the
new backup is ok. If the new backup fails you don't have a spare one.
Jo.
|||Very true. But in the case of not enough disk space for more than 1 backup
and the backup file backed up to some other media it could still be a nice
option of the backup plan.
Chris Wood
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:e4fS8hu1EHA.2572@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>
> Chris Wood wrote:
backup[vbcol=seagreen]
backup
> Hi,
> It is not a good idea to remove the previous backup before you know the
> new backup is ok. If the new backup fails you don't have a spare one.
> Jo.

how to delete a column using script code

hi all

iam having a query , c guys iam having a table in sql with some coulmns in it , i have a column named as country in the table , now wat i want to be done is , i want to delete the column country based on some conditions , i ve to write a script code as : i ve to check if the column is there already or not if its there it shld delete the column or if its not there it shld not show any error and just return empty handed thats all , i dont know wat to be done , so if anyone knows abt it pls do send as soon as possible guys , hopefully waiting for a reply

Note : any dbts pls do mail me again

Venkatesan

I understand this is your first post. Welcome to the forums.

Some info that might help you:

If you want a proper response to your question, you need to explain the facts, provide the table structure(s), some sample data. You dont need to provide your entire HTML code from visual studio. Only relevant code. Any amount of ambiguity or missing information can lead people trying to answer your question away from your post, to a more informative post. Otherwise your post will go down the pages unanswered.

Now, coming back to your question, do you want to delete the column or the values in the column? Posting some sample condition would also help!!

|||

hi dinakar

thanks for ur reply , first let me apologise , c yar i dont have any code to post it here , actually i dont know how to write one , i will explain my req again , 1st i have a table named as tbl_employees which has some columns and in that there is a column named as country , intially for live database that country column will not be there and if clients( i mean in live ) wants to have it , then they can add it , the scenario is like this , so as a developer we used to do it and ensure it that its working in local , after it works we have to put it in live , so that time we cannot delete it manually , for that we have to send a script file to check whether the column is there or not , if its there i have to delete the country column through a query thats wat i dont know how to do , so wat i need is to code to delete the country column itself not the values in it , i think i have explained to u well , waiting for a reply asap, thanks in advance

Venkatesan

|||

ALTER TABLE yourTableDROP COLUMNCountry

|||thanks man