Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry...
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
Iain
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain
|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>
|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of
other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is al
so stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain
|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
Regards
|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released!
Any ideas?
|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current installed SQL Server Service pack via either a registry key or a WMI class entry..
I've had a good look, but can't seem to find anything that matches or is useful. I know you can retrieve this via a SQL query (with Select @.@.version), but I'm after a way that uses our existing WMI/registry reading process as we are collecting info from servers all over the world, so I don't want to have to deal with all the SQL permissions issues & have to use a different tool in order to run a SQL query..
Any info or advice that people can offer would be greatfully accepte
Thanks in Advanc
Cheer
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex..
This is exactly what I nee
Can't believe I didn't spot this, must have been due to a long day on friday for me to have missed it. The other method I found was to check the event log via WMI for the event that SQL records on startup, which give the version of SQL, and also the version of SQL Server that is running (Enterprise / Standard /Developer etc)...the only limitation on this is that you need a relatively specific WQL (WMI Query Language) query - as its an information event that only differs in the message text from a range of other event log informational messages from SQLServer, and the LIKE operator for WQL is only suppported in XP/Server2003.....not w2k which the majority of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection tool they retrieve the version from SQL statement, but I assume that it is to grab the SQL product type (enterprise edition etc) at the same time.....is this something that is also stored in the registry, as the only reference that might give this info is the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration), which I'll check on some different version machines to see if it actaully does identify the product type..
if you already know if productID correlates with the different editions, and what the productIDs are for the various editions, that would be great, otherwise I'll post the results of what I find out back here for your referenc
Thanks agai
cheer
Iain|||Rasmus,
This should help:
How do I know which version of SQL Server I'm running?
http://aspfaq.com/show.asp?id=2160
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Rasmus wrote:
> Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Service Pack installed?
>
> Regards|||Thanks Mark
I can use the list of version numbers - but what a crappy way of encoding the SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - that'll also work when SP4 i released
Any ideas?|||... and on a related node
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out the SQL Server Version and SP via registry or WMI?

Hi All,
Just keen to find a way to find the version of SQL Server and the current in
stalled SQL Server Service pack via either a registry key or a WMI class ent
ry...
I've had a good look, but can't seem to find anything that matches or is use
ful. I know you can retrieve this via a SQL query (with Select @.@.version), b
ut I'm after a way that uses our existing WMI/registry reading process as we
are collecting info from s
ervers all over the world, so I don't want to have to deal with all the SQL
permissions issues & have to use a different tool in order to run a SQL quer
y...
Any info or advice that people can offer would be greatfully accepted
Thanks in Advance
Cheers
IainHKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
/CSDVersion
It's for default instance, don't know what will be for named instance really
but you can check it by yourself
"iain sandercock" <iain*DOT*sandercock@.*NOSPAM*riotinto.com> wrote in
message news:8E21AF3D-25DF-4709-AB10-4CD106A57340@.microsoft.com...
> Hi All,
> Just keen to find a way to find the version of SQL Server and the current
installed SQL Server Service pack via either a registry key or a WMI class
entry...
> I've had a good look, but can't seem to find anything that matches or is
useful. I know you can retrieve this via a SQL query (with Select
@.@.version), but I'm after a way that uses our existing WMI/registry reading
process as we are collecting info from servers all over the world, so I
don't want to have to deal with all the SQL permissions issues & have to use
a different tool in order to run a SQL query...
> Any info or advice that people can offer would be greatfully accepted
> Thanks in Advance
> Cheers
> Iain|||Alex,
A named instance can be found here:
HKEY_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL
Server/INSTANCE_NAME/MSSQLServer/CurrentVersion/CSDVersion
Where INSTANCE_NAME is the name of your SQL Server named instance.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alex Cieszinski" <nomail@.thanks> wrote in message
news:ODWl%23YwPEHA.3304@.TK2MSFTNGP12.phx.gbl...
>
HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/MSSQLServer/CurrentVersion
> /CSDVersion
> It's for default instance, don't know what will be for named instance
really
> but you can check it by yourself
>|||Thanks Mark & Alex...
This is exactly what I need
Can't believe I didn't spot this, must have been due to a long day on friday
for me to have missed it. The other method I found was to check the event l
og via WMI for the event that SQL records on startup, which give the version
of SQL, and also the versi
on of SQL Server that is running (Enterprise / Standard /Developer etc)...th
e only limitation on this is that you need a relatively specific WQL (WMI Qu
ery Language) query - as its an information event that only differs in the m
essage text from a range of
other event log informational messages from SQLServer, and the LIKE operator
for WQL is only suppported in XP/Server2003.....not w2k which the majority
of the servers will be.
Given that this info is in the registry, I wonder why in the PSS collection
tool they retrieve the version from SQL statement, but I assume that it is t
o grab the SQL product type (enterprise edition etc) at the same time.....i
s this something that is al
so stored in the registry, as the only reference that might give this info i
s the productID (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL Server\
80\Registration), which I'll check on some different version machines to see
if it actaully does identi
fy the product type...
if you already know if productID correlates with the different editions, and
what the productIDs are for the various editions, that would be great, othe
rwise I'll post the results of what I find out back here for your reference
Thanks again
cheers
Iain|||Ok, CSDVersion gives me "8.00.760" - what does that tell me about the Servic
e Pack installed?
Regards|||Thanks Mark,
I can use the list of version numbers - but what a crappy way of encoding th
e SP version into the version number.
It's impossible for me to write an algorithm displaying the SP version - tha
t'll also work when SP4 i released!
Any ideas?|||... and on a related node:
- how do i retrieve the version and SP of Internet Explorer?

Programatically find out stored procedures properties

Hi all,
How do I find out what id's have execute permissions on each procedure in a
database for all procedures using t-sql?
-Thanks! daveHi
I'm sure if you asks questions like this you know you way around SQL,
I did some searching in BOL since this is something that could be usefull
and I would in the future maybe use it.
So I came across this
A. List the permissions for a table
This example lists the permissions for the titles table.
EXEC sp_helprotect 'titles'
B. List the permissions for a user
This example lists all permissions that user Judy has in the current databas
e.
EXEC sp_helprotect NULL, 'Judy'
Now there's a lot of other "users info" tables and processes, so I'm sure if
you search BOL you'll find some usefull info. I'll search and see if I can
come up with something, and add it.
HTH
"dfate" wrote:

> Hi all,
> How do I find out what id's have execute permissions on each procedure in
a
> database for all procedures using t-sql?
> -Thanks! dave
>
>

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.

Programatically Email a report

Does anyone have any code to run a report and email it. I want to run the
report that has one parameter and then email it. With the report as the Body
of the email and then the same report as an attachment in pdf format. I would
like to also add some text to the email in front of the report. Or could you
point me to a sample or book that would help. Which report interface would I
use?
--
Thanks,
Jon AYou could use subscriptions - see "E-Mail Delivery in Reporting Services" in
reporting Services Books On Line.
If you want more control over the email you could create the pdf file
programatically either using URL access (again in BOL) or the Web Services,
then write a bit of code to send email - that will depend on what your email
system is.
"Jon A" wrote:
> Does anyone have any code to run a report and email it. I want to run the
> report that has one parameter and then email it. With the report as the Body
> of the email and then the same report as an attachment in pdf format. I would
> like to also add some text to the email in front of the report. Or could you
> point me to a sample or book that would help. Which report interface would I
> use?
> --
> Thanks,
> Jon A|||Which is more efficient Web services or url access. I tried using
subscriptions but I can't figure out how to get the report in the body of the
message. Also my mail server needs you to logon. Where do I put the user name
and password for the mail server to get or does subsciptions use an outlook
profile?
--
Thanks,
Jon A
"Mary Bray [SQL Server MVP]" wrote:
> You could use subscriptions - see "E-Mail Delivery in Reporting Services" in
> reporting Services Books On Line.
> If you want more control over the email you could create the pdf file
> programatically either using URL access (again in BOL) or the Web Services,
> then write a bit of code to send email - that will depend on what your email
> system is.
>
> "Jon A" wrote:
> > Does anyone have any code to run a report and email it. I want to run the
> > report that has one parameter and then email it. With the report as the Body
> > of the email and then the same report as an attachment in pdf format. I would
> > like to also add some text to the email in front of the report. Or could you
> > point me to a sample or book that would help. Which report interface would I
> > use?
> > --
> > Thanks,
> > Jon A

PROGRAMATICALLY ELIMINATE DUPLICATE ADDRESSES

I am trying to find a way to eliminate duplicate addresses from a table. I
have a table that is imported from a seperate system so I have no control
over it but I need to be able to design a query that brings out last name,
first name, zip code, city, and state, address1 and address2 for a mailing
list.
Currently I have many people at the same address that I need to remove and
when I check the zip code against our zipcode table it removes any with a zi
p
+4 setup.
Is there any way to set this up to keep the zip+4 part and eliminate all of
the duplicate addresses?
You can modify the "double cursor" example at:
http://groups.google.com/group/micr...cbce3491367da2f
Where I have
declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
from Orders where CustomerID = @.CustomerID
put a counter there. (of course reset it for each new Customer)
if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
..
Its a hack, but it works.
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I am sorry, I did not state my problem completely. I also cannot delete fro
m
the original table so it must all be done in a query
"sloan" wrote:

>
> You can modify the "double cursor" example at:
> http://groups.google.com/group/micr...cbce3491367da2f
> Where I have
> declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
> from Orders where CustomerID = @.CustomerID
> put a counter there. (of course reset it for each new Customer)
> if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
> ...
> Its a hack, but it works.
>
>
>
>
> "Don" <djw@.nospam.postalias> wrote in message
> news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I
> zip
> of
>
>|||I also cannot affect the original table as we rerun other queries that use
the extra information for other purposes.
I am pretty weak when it comes to SQL also, sorry.
"Don" wrote:

> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all o
f
> the duplicate addresses?|||Select 'distinct' (without the quotes of course). This will eliminate
duplicates as long as you have selected all the data properly. Be careful
that you are getting all the data you want though.
"Don" wrote:
> I also cannot affect the original table as we rerun other queries that use
> the extra information for other purposes.
> I am pretty weak when it comes to SQL also, sorry.
> "Don" wrote:
>|||Why not just:
select distinct
firstname, zipcode, city, and state, address1, address2
from sometable
or you want to keep only the row with the longest zipcode? If so, try:
select
firstname, max(zipcode), city, and state, address1, address2
from sometable
group by firstname, city, and state, address1, address2
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I have used distinct to get rid of duplicate entries, but distinct does not
seem to help with different people at the same address.
This is where my problem is.
First, Last, Addr1, Addr2, City, State, Zip
Joe Brown PO Box 33 NULL Town ST 99999
George Brown PO Box 33 NULL TOWN ST 99999
Jay Sherman 123 Somestrret NULL TOWN ST 99999
More specifically, I want to remove the PO Box 33 or only send to one of
them not both.
"MattB" wrote:
> Select 'distinct' (without the quotes of course). This will eliminate
> duplicates as long as you have selected all the data properly. Be careful
> that you are getting all the data you want though.
> "Don" wrote:
>|||Don what is the primary key for this table?
Say you have the customerID (ideally you should be having one), So your
query should go like this (untested, of course).
SELECT LAST_NAME, FIRST_NAME, ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2
FROM YOUR_TABLE A
where
CustomerID in (SELECT MIN(Customer_id) from your_table group by ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2 )
Let me know if this is what u r looking for.|||You could use an aggregate function (such as MIN or MAX) when you select the
names. Don't change anything related to the address information.
Keith Kratochvil
"Don" <djw@.nospam.postalias> wrote in message
news:A558C92F-EE0E-4835-9C31-BAF798905A62@.microsoft.com...
>I have used distinct to get rid of duplicate entries, but distinct does not
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>|||Omni's subquery should work, but if you want different parameters another wa
y
is;
begin
create table ##address (LAST_NAME varchar(50) null,
FIRST_NAME varchar(50) null,
ZIP_CODE varchar(10) null,
CITY varchar(50) null,
STATE varchar(50) null,
ADDRESS1 varchar(50) null,
ADDRESS2 varchar(50) null)
set nocount on
insert ##address
SELECT distinct
null,
null,
ZIP_CODE,
CITY,
STATE,
ADDRESS1,
ADDRESS2
FROM table (NOLOCK)
WHERE your_parameter
AND no po boxes, etc
--you could also use a case stmt to eliminate wanted or unwanted parameters
--this will load the addresses only into temp table.
update ##address
set LAST_NAME = a.LAST_NAME,
FIRST_NAME = a.FIRST_NAME
FROM table a (NOLOCK)
WHERE your parameter to tie to address
select LAST_NAME,FIRST_NAME,ZIP_CODE,CITY,STATE
,ADDRESS1,ADDRESS2
from ##address (nolock)
drop table ##address
end
"Don" wrote:
> I have used distinct to get rid of duplicate entries, but distinct does no
t
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>