Determine Processor Architecture for SQL Server 2005

Determine Processor Architecture for SQL Server 2005

I like the new features in SERVERPROPERTY but there's a huge, glaring oversight in my opinion: it doesn't return the processor architecture. I'm trying to write a detailed server info collection script and (1) use only TSQL, and (2) have it return the processor architecture. Here's what I have so far:

SELECT
SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('ServerName') AS [ServerName]
, @.@.VERSION AS Version
, CASE
WHEN CHARINDEX('Intel X86',@.@.VERSION)<>0 THEN '32-bit'
WHEN CHARINDEX('Intel IA-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('x86-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('WOW64',@.@.VERSION)<>0 THEN 'WOW64'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN 'WOW32'
ELSE 'Unknown'
END AS ProcessorArchitecture

The problem is that I can only include the architectures that I've worked on (X86 and IA-64). The documentation does nothing to give us any clue what the values from @.@.VERSION will possibly return. I've made some guesses (in comments) but I haven't installed these so I'm not positive they are the right values returned by @.@.VERSION.

Anyone have confirmation of other architectures returned by @.@.VERSION? Or, even better, does anyone have a better TSQL-only way of returning this info?

Thanks!

Scott Whigham

Take a look at xp_msver.|||

xp_msver is not really any better/different than @.@.VERSION, is it? Actually I like @.@.VERSION better since I can use it in a query along with the other SERVERPROPERTY properties. Thanks though :) And it's weird that xp_msver and @.@.VERSION report the architecture using different values. That's a big incongruous to me.

Anyone else have other values or can confirm the values I posted?

Determine Processor Architecture for SQL Server 2005

I like the new features in SERVERPROPERTY but there's a huge, glaring oversight in my opinion: it doesn't return the processor architecture. I'm trying to write a detailed server info collection script and (1) use only TSQL, and (2) have it return the processor architecture. Here's what I have so far:

SELECT
SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('ServerName') AS [ServerName]
, @.@.VERSION AS Version
, CASE
WHEN CHARINDEX('Intel X86',@.@.VERSION)<>0 THEN '32-bit'
WHEN CHARINDEX('Intel IA-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('x86-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('WOW64',@.@.VERSION)<>0 THEN 'WOW64'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN 'WOW32'
ELSE 'Unknown'
END AS ProcessorArchitecture

The problem is that I can only include the architectures that I've worked on (X86 and IA-64). The documentation does nothing to give us any clue what the values from @.@.VERSION will possibly return. I've made some guesses (in comments) but I haven't installed these so I'm not positive they are the right values returned by @.@.VERSION.

Anyone have confirmation of other architectures returned by @.@.VERSION? Or, even better, does anyone have a better TSQL-only way of returning this info?

Thanks!

Scott Whigham

Sorry for the double post - the forums reported an error when I clicked "Post" so, of course, I posted again!

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank youI've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank you
I've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>
|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.
|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
David
With ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David
|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David