Information about an SQL server

i would like to create a Store procedure that can put the following
information into a database.
1.) Version of SQL on that instance
2.) Version of Windows including SPs (can i get this from SQL?)
3.) Size of each log file on the server instance
I would like to out this information to a database which i will connect to
via ASP so that it can be displayed on our Intranet .hi
can u check
select @.@.version
this will give u info that u require
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Nick Pennisi" wrote:

> i would like to create a Store procedure that can put the following
> information into a database.
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .|||For the first two questions, use SELECT @.@.VERSION.
For the third question, use:
SELECT filename, size*8192 as filesize FROM master..sysaltfiles
Razvan|||1) You can parse SELECT @.@.VERSION
2) You can parse SELECT @.@.VERSION
3) see the following:
CREATE TABLE #logfiles
(
dbid int,
fileid int,
groupid int,
size bigint,
maxsize bigint,
growth bigint,
status bigint,
perf int,
name sysname,
filename sysname
)
EXEC sp_msForEachDB 'INSERT #logfiles SELECT DB_ID(N''?''),* FROM
[?]..sysfiles'
SELECT db_name(dbid),size,name,filename
FROM #logfiles
WHERE status & 0x40 = 0x40 -- log file
ORDER BY 1, name
DROP TABLE #logfiles
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:F041A69E-3057-458A-8FB0-DA1BAB8A2DFB@.microsoft.com...
>i would like to create a Store procedure that can put the following
> information into a database.
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .|||1 and 2 = SELECT @.@.VERSION
3 = sp_helpdb dbname
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:F041A69E-3057-458A-8FB0-DA1BAB8A2DFB@.microsoft.com...
>i would like to create a Store procedure that can put the following
> information into a database.
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .|||Try these 2 queries:
SELECT @.@.version
SELECT *
FROM master..sysaltfiles(nolock)
WHERE groupid = 0
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message news:F041A69E-3057-
458A-8FB0-DA1BAB8A2DFB@.microsoft.com...
>i would like to create a Store procedure that can put the following
> information into a database.
>
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
>
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .|||1) select @.@.version
Hope this helps
Cheers,
JP
--
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:F041A69E-3057-458A-8FB0-DA1BAB8A2DFB@.microsoft.com...
>i would like to create a Store procedure that can put the following
> information into a database.
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .|||xp_msver
Returns and allows to be queried Microsoft SQL ServerT version information.
In addition to version information regarding the actual build number of the
server, various environment information is also returned. This information
can be used within Transact-SQL statements, batches, stored procedures, and
so on, to enhance logic for platform-independent code.
Syntax
xp_msver [optname]
Arguments
optname
Is the name of an option, and can be one of the following.
Option/Column name Description
ProductName Product name; for example, Microsoft SQL Server.
ProductVersion Product version; for example, 7.00.419 where 7.00 is
the version and 419 is the Microsoft internal build number.
Language The language version of SQL Server.
Platform Operating-system name, manufacturer name, and chip family
name for the computer running SQL Server. For example, NT INTEL X86
indicates Microsoft Windows NT as the operating system, Intel as the chip
manufacturer, and a 486 or higher processor.
Comments Miscellaneous information about SQL Server.
CompanyName Company name that produces SQL Server; for example,
Microsoft Corporation.
FileDescription The operating system.
FileVersion Version of the SQL Server executable. For example,
1998.02.01 indicates a file version of February 1, 1998, on the Sqlservr.exe
file.
InternalName Microsoft internal name for SQL Server; for example,
SQLSERVR.
LegalCopyright Legal copyright information required for SQL Server;
for example, Copyright Microsoft Corp. 1998.
LegalTrademarks Legal trademark information required for SQL Server.
For example, Microsoft is a registered trademark of Microsoft Corporation.
OriginalFilename File name executed at SQL Server startup; for
example, Sqlservr.exe.
PrivateBuild Reserved.
SpecialBuild Reserved.
WindowsVersion Microsoft Windows version installed on the computer
running SQL Server. For example, 4.0 indicates version 4.0 of Microsoft
Windows NT, and 1381 indicates the internal build number.
ProcessorCount The number of processors in the computer running SQL
Server.
ProcessorActiveMask Indicates what processors installed in the
computer running SQL Server are activated and usable by Microsoft Windows
NT.
ProcessorType Processor type. Similar to Platform.
PhysicalMemory Amount in megabytes (MB) of RAM installed on the
computer running SQL Server. For example, 32 indicates 32 MB of RAM.
Product ID Product ID (PID) number, which is specified during
installation. This number is located on a sticker on the original SQL Server
compact disc case.
Return Code Values
0 (success) or 1 (failure)
Result Sets
xp_msver, without any parameters, returns this four-column result set
(values may vary):
Index Name Internal_Value Character_Value
-- -- -- --
1 ProductName NULL Microsoft SQL
Server
2 ProductVersion 458752 7.00.498
3 Language 1033 English (United
States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft
Corporation
7 FileDescription NULL SQL Server
Windows NT
8 FileVersion NULL 1998.05.25
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL Copyright
Microsoft Corp. 1998
11 LegalTrademarks NULL Microsoft is a
registered trademark of
Microsoft Corporation. Windows(TM) is
a trademark of Microsoft
Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 262242 NULL
15 WindowsVersion 90505220 4.0 (1381)
16 ProcessorCount 1 1
17 ProcessorActiveMask 1 00000001
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 63 63 (66510848)
20 Product ID NULL NULL
(20 row(s) affected)
xp_msver, for any option, returns the four-column headings with values for
that option. For example, this result set is returned when xp_msver is
executed with the FileDescription option.
xp_msver FileDescription
Index Name Internal_Value Character_Value
-- -- -- --
7 FileDescription NULL SQL Server Windows NT
(1 row(s) affected)
Permissions
Execute permissions default to the public role.
See Also
System Functions
System Stored Procedures (General Extended Procedures)
@.@.VERSION
1988-2000 Microsoft Corporation. All Rights Reserved
"Nick Pennisi" <NickPennisi@.discussions.microsoft.com> wrote in message
news:F041A69E-3057-458A-8FB0-DA1BAB8A2DFB@.microsoft.com...
>i would like to create a Store procedure that can put the following
> information into a database.
> 1.) Version of SQL on that instance
> 2.) Version of Windows including SPs (can i get this from SQL?)
> 3.) Size of each log file on the server instance
> I would like to out this information to a database which i will connect to
> via ASP so that it can be displayed on our Intranet .

Inequality Comparision on Dimension members

Hi,

Take foodmart sales cube as an example, I want to find out store sizes that are bigger than 35000. I have tried.

1. select filter([Store Size in SQFT].[Store Sqft].members, STRTOVALUE([Store Size in SQFT].currentmember.name) > 35000) on columns from sales

Output: An MDX expression was expected. An empty expression was specified.

2.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember.name > '35000')

on columns from sales

Output: lexicological comparison, not what I want.

3.

select filter([Store Size in SQFT].[Store Sqft].members, [Store Size in SQFT].currentmember > 30000) on columns from sales

Output: comparing with default measure instead.

I guess I am still having the SQL mindset. Any help is really appreciated.

Hi

you should use function Val in order to convert string property into number.

select
filter([Store Size in SQFT].[Store Sqft].members, Val([Store Size in SQFT].currentmember.name) > 35000)
on columns
from sales

sql

indexing urls

Hi, I have a column on a table that store urls. I want to index this column.
But it seems that SQL Server is indexing the whole url and not each part of
it (domain, querystring, etc). So if I have this url
www.onedomain.com/somepage.aspx
And I query 'onedomain' I want to get that url. However I'm not getting it.
I think it's because it is indexing the whole url and not every part of it.
could you tell me how can I workaround this?
thanks
Matias
try indexing it using the neutral word breaker.
it works for me using SQL 2000 sp3, on win2k professional. sp4
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Matias Woloski" wrote:

> Hi, I have a column on a table that store urls. I want to index this column.
> But it seems that SQL Server is indexing the whole url and not each part of
> it (domain, querystring, etc). So if I have this url
> www.onedomain.com/somepage.aspx
> And I query 'onedomain' I want to get that url. However I'm not getting it.
> I think it's because it is indexing the whole url and not every part of it.
> could you tell me how can I workaround this?
> thanks
> Matias
>
>
|||Matias,
It will also work if you upgrade to Windows Server 2003 and you get to take
advantage of using US_English and still can use formsof(inflectional) which
is a function that you lose with the Neutral word breaker.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:5D3295FD-D6F7-4F2A-8721-9855FD3E0362@.microsoft.com...[vbcol=seagreen]
> try indexing it using the neutral word breaker.
> it works for me using SQL 2000 sp3, on win2k professional. sp4
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Matias Woloski" wrote:
column.[vbcol=seagreen]
of[vbcol=seagreen]
it.[vbcol=seagreen]
it.[vbcol=seagreen]
sql

Indexing on calculated fields

I would like to index on a calculated field. I need to index records by
w and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan

Indexing of binary and varbinary

Hi
I’m writing an Alarms and Events database. Our A&E is identified by a byte
array in the A&E object. This byte array I will store in a column of a tabl
e
in addition to other columns with other relevant data.
I’m thinking of using the binary or varbinary type for the purpose of
storing the byte array.
I also need to have an index on the varbinary field, -is that possible?
This field will identify an instance of a specific alarm and will be used to
calculate statistics regarding how often the alarm occurs etc.
Is possible to do SELECT on a varbinary, and if so is it more resource
demanding compared to do this with a string on the same size? (Another
possibility is to convert the byte array to a hex string).
SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
ORDER BY BinId
Regards
Kjell ArneHi
You can index varbinary columns, but if you are going to always divide this
into individual bytes and want to mainly access these separately then you
would probably be better off holding them separately, expecially if you want
to index them separately. If the bytes represent characters then you should
hold them in char columns if they are numeric or binary they should be
numeric or binary.
HTH
John
"Kjell Arne Johansen" wrote:

> Hi
> I’m writing an Alarms and Events database. Our A&E is identified by a by
te
> array in the A&E object. This byte array I will store in a column of a ta
ble
> in addition to other columns with other relevant data.
> I’m thinking of using the binary or varbinary type for the purpose of
> storing the byte array.
> I also need to have an index on the varbinary field, -is that possible?
> This field will identify an instance of a specific alarm and will be used
to
> calculate statistics regarding how often the alarm occurs etc.
> Is possible to do SELECT on a varbinary, and if so is it more resource
> demanding compared to do this with a string on the same size? (Another
> possibility is to convert the byte array to a hex string).
> SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
> ORDER BY BinId
> Regards
> Kjell Arne

Indexing of binary and varbinary

Hi
I’m writing an Alarms and Events database. Our A&E is identified by a byte
array in the A&E object. This byte array I will store in a column of a table
in addition to other columns with other relevant data.
I’m thinking of using the binary or varbinary type for the purpose of
storing the byte array.
I also need to have an index on the varbinary field, -is that possible?
This field will identify an instance of a specific alarm and will be used to
calculate statistics regarding how often the alarm occurs etc.
Is possible to do SELECT on a varbinary, and if so is it more resource
demanding compared to do this with a string on the same size? (Another
possibility is to convert the byte array to a hex string).
SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
ORDER BY BinId
Regards
Kjell Arne
Hi
You can index varbinary columns, but if you are going to always divide this
into individual bytes and want to mainly access these separately then you
would probably be better off holding them separately, expecially if you want
to index them separately. If the bytes represent characters then you should
hold them in char columns if they are numeric or binary they should be
numeric or binary.
HTH
John
"Kjell Arne Johansen" wrote:

> Hi
> I’m writing an Alarms and Events database. Our A&E is identified by a byte
> array in the A&E object. This byte array I will store in a column of a table
> in addition to other columns with other relevant data.
> I’m thinking of using the binary or varbinary type for the purpose of
> storing the byte array.
> I also need to have an index on the varbinary field, -is that possible?
> This field will identify an instance of a specific alarm and will be used to
> calculate statistics regarding how often the alarm occurs etc.
> Is possible to do SELECT on a varbinary, and if so is it more resource
> demanding compared to do this with a string on the same size? (Another
> possibility is to convert the byte array to a hex string).
> SELECT BinId FROM Events WHERE BinId like @.IdFromEvent
> ORDER BY BinId
> Regards
> Kjell Arne

Indexing documents in SQLServer 2005

I have a SQLServer 2005 database and store documents in a table. I have installed the index and it works fine. My problem is that it does not index pdf documents, Office 2007 documents and so on.

Does anyone have a clue on how to solve this?

I have installed the Adobe ifilter 6 but how do you tell SQLServer this?

Is there an add method for the table that contains the filters?

Hi Robert,

Please check the following link:
http://msdn2.microsoft.com/en-us/library/ms175058.aspx

Try the following steps on your SQL Server Instance

sp_fulltext_service 'load_os_resources',1. This command tells the Microsoft Search Service to load OS specific wordbreakers, stemmers, and such.
sp_fulltext_service 'verify_signature', 0. Do not verify that the binaries are signed.

Hope this helps.

Thanks
Arpita

|||You might also check whether the MSSearch service has been started or up and running. Also check the SQL error log for any sort of fulltext indexing related errors.

Indexing custom BLOB Field with SQL Server 2000!

Hi,
I want define a full text indexing in a BLOB field that store custom binary
file. As I know in such cases should write an IFilter for extracting plain
text from this binary file.
How is is possible with SQL Server 2000? Is there a guide about writing an
IFilter?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mamouri" <m@.m.com> wrote in message
news:eYphDbuHHHA.420@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want define a full text indexing in a BLOB field that store custom
> binary
> file. As I know in such cases should write an IFilter for extracting plain
> text from this binary file.
> How is is possible with SQL Server 2000? Is there a guide about writing an
> IFilter?
>

Indexing ?

I have a simple table that includes an ID and 8 user-defined data fields. Th
e
fields are nvarchar(255) and the application allows the user to store any
data they like in them. The application also allows searching on any
combination of up to 5 of the fields, with an AND or OR logical combination.
The query returns the result set unordered.
Does anyone have any thoughts on an indexing scheme for this situation? I'm
not quite sure what is best given that the user may search on any combinatio
n
of fields. Some obvious thoughts are:
1. An separate index on each column.
2. A single index covering all columns.
3. A separate index on each of the 8 choose 5 combinations of columns
(whoah!).
Any thoughts?Although I do not condone this, creating an index on each of the columns
would allow SQL Server to use Index Joining / Intersection to perform the
search. Creating an Index on all the columns would only benefit the
statements that search on the columns in the order of the index.
"Ken" wrote:

> I have a simple table that includes an ID and 8 user-defined data fields.
The
> fields are nvarchar(255) and the application allows the user to store any
> data they like in them. The application also allows searching on any
> combination of up to 5 of the fields, with an AND or OR logical combinatio
n.
> The query returns the result set unordered.
> Does anyone have any thoughts on an indexing scheme for this situation? I'
m
> not quite sure what is best given that the user may search on any combinat
ion
> of fields. Some obvious thoughts are:
> 1. An separate index on each column.
> 2. A single index covering all columns.
> 3. A separate index on each of the 8 choose 5 combinations of columns
> (whoah!).
> Any thoughts?
>