How to determine the version of SQL Server programatically.

What is the official supported way to determine the version of SQL server and installed Service Pack programatically.

Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.

Thank you in advance.

you can try this,

select @.@.version

select serverproperty('productversion')

you can refer, http://support.microsoft.com/kb/321185

|||

Thank you for the pointer to this kb article. However, I probably did not formulate my question correctly. I would need to determine the version by running some executable in the command line rather than connecting to SQL Server database. For example I can have just SQL server Integration Services installed but not the database itself.

Is there officially supported solution in this case?

|||How do you need to get the value back ? You could use the SQLCMD to execute and retrieve the value to the command line. Perhaps you can elaborate a bit more on the situation and the environment where the applciation is started.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer. I do understand that I can invoke sqlcmd. However what if database is not installed and all what I do have installed is Integration Services. What should I do in this situation. Besides I can apply the service pack to each of sql server major components separately. So if I do have the database with one version and analysis services with another version I will get simply incorrect value for Analysis Services invoking this command. What I actually need is a supported way to determine the version of the component I do need. Ideally it should work as version command in windows command line for each of the major component of SQL server that can be installed independently of each other. However, I can understand that this expectation might not be realistic. What I actually need is officially supported way so I will not make a mistake determining the version.

|||Try to read the below registry key value, whcih will provide you the version of Database Engine.

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

read the string value "CurrentVersion"

It will provide you the Version number. This can be done without connecting to SQL server.
|||Thank you for the reply. What I can do in the case of Integration Services and Analysis Services in case if database Engine is not installed.|||

Look at the Services registration:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to determine the version of SQL Server programatically.

What is the official supported way to determine the version of SQL server and installed Service Pack programatically.

Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.

Thank you in advance.

you can try this,

select @.@.version

select serverproperty('productversion')

you can refer, http://support.microsoft.com/kb/321185

|||

Thank you for the pointer to this kb article. However, I probably did not formulate my question correctly. I would need to determine the version by running some executable in the command line rather than connecting to SQL Server database. For example I can have just SQL server Integration Services installed but not the database itself.

Is there officially supported solution in this case?

|||How do you need to get the value back ? You could use the SQLCMD to execute and retrieve the value to the command line. Perhaps you can elaborate a bit more on the situation and the environment where the applciation is started.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer. I do understand that I can invoke sqlcmd. However what if database is not installed and all what I do have installed is Integration Services. What should I do in this situation. Besides I can apply the service pack to each of sql server major components separately. So if I do have the database with one version and analysis services with another version I will get simply incorrect value for Analysis Services invoking this command. What I actually need is a supported way to determine the version of the component I do need. Ideally it should work as version command in windows command line for each of the major component of SQL server that can be installed independently of each other. However, I can understand that this expectation might not be realistic. What I actually need is officially supported way so I will not make a mistake determining the version.

|||Try to read the below registry key value, whcih will provide you the version of Database Engine.

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

read the string value "CurrentVersion"

It will provide you the Version number. This can be done without connecting to SQL server.
|||Thank you for the reply. What I can do in the case of Integration Services and Analysis Services in case if database Engine is not installed.|||

Look at the Services registration:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services

Jens K. Suessmeyer

http://www.sqlserver2005.de

How to determine the version of SQL Server programatically.

What is the official supported way to determine the version of SQL server and installed Service Pack programatically.

Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.

Thank you in advance.

you can try this,

select @.@.version

select serverproperty('productversion')

you can refer, http://support.microsoft.com/kb/321185

|||

Thank you for the pointer to this kb article. However, I probably did not formulate my question correctly. I would need to determine the version by running some executable in the command line rather than connecting to SQL Server database. For example I can have just SQL server Integration Services installed but not the database itself.

Is there officially supported solution in this case?

|||How do you need to get the value back ? You could use the SQLCMD to execute and retrieve the value to the command line. Perhaps you can elaborate a bit more on the situation and the environment where the applciation is started.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you for your answer. I do understand that I can invoke sqlcmd. However what if database is not installed and all what I do have installed is Integration Services. What should I do in this situation. Besides I can apply the service pack to each of sql server major components separately. So if I do have the database with one version and analysis services with another version I will get simply incorrect value for Analysis Services invoking this command. What I actually need is a supported way to determine the version of the component I do need. Ideally it should work as version command in windows command line for each of the major component of SQL server that can be installed independently of each other. However, I can understand that this expectation might not be realistic. What I actually need is officially supported way so I will not make a mistake determining the version.

|||Try to read the below registry key value, whcih will provide you the version of Database Engine.

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

read the string value "CurrentVersion"

It will provide you the Version number. This can be done without connecting to SQL server.
|||Thank you for the reply. What I can do in the case of Integration Services and Analysis Services in case if database Engine is not installed.|||

Look at the Services registration:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Services

Jens K. Suessmeyer

http://www.sqlserver2005.de

how to determine programatically what output of a sp will be?

Using Sql Srv 7

I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
to determine all the sps in a db, and what input parms there are for a
particular sp... but... if the sp returns a result set, is there a way to
find out the stru of that in a similar manner?Am Wed, 5 Oct 2005 09:04:14 -0500 schrieb spiffo:

> Using Sql Srv 7
> I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
> to determine all the sps in a db, and what input parms there are for a
> particular sp... but... if the sp returns a result set, is there a way to
> find out the stru of that in a similar manner?

I never put time in finding an answer for this question but my first
thought is, that it cannot be possible, because i can write a procedure
like this:

create stored procedure myRandom as
if datepart(s,getdate()) % 2 = 1 select 'field_1', 1
else select 123, 'last'

So what is the struct of the resultset? It changes every second!

bye,
helmut

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 define programatically the width for columns?

Hi everyone,

Either Sql2k or Sql25k are targeted if you answer to this thread. When we have source/destination files we usually wish to define its properties, the width for each field and so on. My question is related with this, how do such by-hand tasks via scripting inside the own ETL? Tedious tasks are if there are more than 20 columns.

Is it possible? I think so regarding 2005 but about 2000 I haven't idea at all how to begin. Issue comes when one programmer must alter lots of columns due to for example, a new file format from mainframe is released.

Thanks in advance for your time or advices,

Any ideas?