How To Get The SQL Affinity Mask Value In Sql 2005, And Table That Store This Info
Aug 29, 2006
Hi All
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");
lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));
lSQLBindCol(nAffMask);
But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.
View 1 Replies
ADVERTISEMENT
Jan 21, 2008
I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
View 7 Replies
View Related
Sep 17, 2007
I'm experiencing some problem with the affinity mask and affinity I/O mask configuration.
We are running SQL Server Enterprise Edition 2005 64bit on Windows 2003 Server 64bit with 4 dual core CPU. The actuelly configuration is:
Affinity Mask Affinity I/O Mask
CPU0 0 0
CPU1 0 0
CPU2 1 1
CPU3 1 1
CPU4 1 1
CPU5 1 1
CPU6 1 1
CPU7 1 1
We did it so, because we are running LiteSpeed Tool for the backup. LiteSpeed is configured to use CPU0 and CPU1.
Now, we know that the settings are wrong. If Affinity mask is 1 affinity I/O mask should be 0 and vice-versa.
My question is: how many CPU should I enable for affinity mask and how many for affinity I/O mask. Which are the correct criterias.
Thx in advence.
Laurent
View 2 Replies
View Related
Nov 17, 2007
We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck.
The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?
John
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'affinity mask', N'0'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity mask' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
View 7 Replies
View Related
Aug 7, 2014
I ran ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3 that means the SQL server 2012 is using the 2nd and 3rd CPU's respectively..
Now, when I run sp_configure 'affinity mask' I get the config_value as 12, so how it is getting calculated?
is there a code for each CPU?? wanted to know about this.
View 4 Replies
View Related
Mar 21, 2007
I have recently upgraded my databases from SQL 2000 to 2005. In 2000, I could set the network affinity so that sql server was only accessible from one of the two network cards. I cannot find how to do this in sql 2005. Is there a way?
In my installation, I have two network cards in each server. The "A" network adapter connects to the "A" hub, and the "B" adapter connected to the "B" hub. Traffic on trhe "B" hub is my private, non-routable network, This is where I want the SQL Server traffic to run. The A hub traffic is the public network. This gives me the ability to isolate SQL Server from the outside world.
View 1 Replies
View Related
May 10, 2004
I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.
However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'
A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.
I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg
"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"
It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.
The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.
Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.
View 3 Replies
View Related
May 7, 2007
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
View 3 Replies
View Related
May 20, 2008
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
Part 2
2006 4 3 0
2007 1 0 7
Customer M12345 totals
2006 9 9 2
2007 1 3 8
M45678
Part 3
2007 8 4 7
2008 3 4 8
Part 4
2006 3 8 7
2007 5 6 6
Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals
2006 12 17 9
2007 14 13 21
2008 3 4 8
View 1 Replies
View Related
Aug 13, 2006
Hello,
I want to store the contents of an MS Word document (or a brief Word extract) in a database field. This document could contain carriage returns, boldface, etc. I have tried storing the Word doc in fields of type nchar, text, and sql_variant but they do not keep the Word formatting. I found that the "Extended Properties" attribute of a field seems to retain these properties. (Though if I recall right, Extended Properties has a length limitation and is a pain in the #*&_*^! to access)
What is the "right" way to do what I'm trying? Keep in mind that we may want to view this document under either "Query Analyzer" or print a hardcopy version. And we'd like both to be intelligible!
TIA,
Barkingdog
View 5 Replies
View Related
Sep 16, 2004
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
Any ideas on what I am doing wrong here?
View 3 Replies
View Related
Jan 9, 2007
Hi everyone,
Primary platform is 2005 under 64-bit.
I'm trying to figure out what does 'Processor Affinity' and 'I/O Affinity' means when you're viewing server properties. We've got eight processors.
Thanks for your comments,
View 4 Replies
View Related
Aug 31, 2005
I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.
First TableTABLE_CATEGORY
PRIMARY_KEY = DIR_CATEGORY_ID = 5000
Column Name
DIR_CATEGORY_LVL_ID1 = 100
DIR_CATEGORY_LVL_ID2 = 200
DIR_CATEGORY_LVL_ID3 = 300
Second TableTABLE_CATEGORY_DESCRIPTION
PRIMARY KEY DIR_CATEGORY_LVL_ID1 = 100
Column Name
DESCRIPTION = "Car Model Company"
This is my DISPLAY_CATEGORY_DESC
Not sure how to get to this description. Thanks for your help.
select cwebs.SESSION_ID, webs.ENTERED_DATE, cwebs.DIR_CATEGORY_ID, DISPLAY_CATEGORY_DESC
FROM di_webclickstats cwebs
left join di_websessions webs on cwebs.session_id = webs.session_id
where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1
View 3 Replies
View Related
Jan 7, 2005
Hello Friends,
I'm trying to set affinity to mssearch.exe in our Windows 2000 Advanced
server (4cpus), but i am getting an "Access denied error". I checked the login which has administrator rights to the box.
It would be a great help if anyone can tell how to fix this.
Thanks
Haja
View 1 Replies
View Related
Oct 22, 2015
We are currently looking at consolidating 10 servers into one cluster server.
Some servers may be busier than others. Is there any reason to split them up and give the busy databases specific CPUs or is it always better to have them on one instance?
View 4 Replies
View Related
Jan 14, 2002
Is there a utility or function or 3rd party product that will show information about tables and other objects such as:
Last change date
Last used date
Where Used
more .....
I'm from an AS/400 environment and found it VERY useful to be able to access that kind of information to clean-up, monitor & manage the systems.
Any Ideas ?
View 1 Replies
View Related
Apr 7, 2008
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
View 6 Replies
View Related
Oct 25, 2007
I'm beating my head up against a wall here. I have one very LARGE table called op_hist.
CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)
)
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
Help me oby one - you're my only hope.
Sincerely,
Frustrated!
View 4 Replies
View Related
Mar 25, 2008
Hello All.
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
Can anyone point me in the right direction?
View 4 Replies
View Related
Sep 15, 2007
I have run into a challenge that I'm not sure how to best solve. I'dappreciate any opinions or input.I am working with a third party database. They are storing some datathat I need to use in a binary field. I've got the code to parse thebinary and reconstruct what I need. Unfortunately, there might bemultiple "entries" stored in a single binary field. a certain byte,let's just say the first, will always be the count of "entries" inthis particular SQL entry.An example:then entry might be:0x01000012341234where 12341234 is the data entry that I will parse.Another possible entry is:0x03000012341234567856789ABC9ABCThe first byte indicates that there are three data values I want toparse out:12341234567856789ABC9ABCThe portions of the binary I need are always the same length and theremay be from 1 to ~100 of them. (usually 1 if it matters.)The big question:How could a SQL query return an entry for each of the "entries" in thebinary field? For the second example I would want three entries in myresults, each row returning a different section of the binary data.For the first, only one row. I'll be querying the set and expecting toget back more results than the number of entries in the set.I haven't really tried much with this yet, (other than some mentalcalisthenics were I fell down onto the mat pretty hard), so just somedirection on where to begin would be helpful.(Yes, I understand that the difficulty is that this data shouldn't bestored this way, but there is not much I can do about that.)Thanks...James FraserJoin Bytes!
View 4 Replies
View Related
May 7, 2007
Hi There
I am trying to troubleshoot deadlock issues.
The dead lock is with a RID.
Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.
However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?
All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.
Thanx
View 5 Replies
View Related
Apr 24, 2008
Does TSQL (2005) offer a way to retrieve a given table's total size (data size vs index size)? Would like to be able to get at this info using TSQL (not management studio).
thanks
View 3 Replies
View Related
Apr 7, 2008
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
View 3 Replies
View Related
Sep 15, 2007
Hi,
I keep running into the following Error message when I try to execute the query below. Can anyone please give me an idea of how I can solve this issue and keep my data??
CREATE VIEW [dbo].[view_mCover]
AS
SELECT dbo.tProduct.intProductId AS dblCoverId, dbo.tProduct.strProductName AS strCoverName,strProductCode AS strCoverCode, dbo.tProduct.strProdPictUrl AS strCoverThumbnailSrc,
dbo.tProduct.intSubCategoryId AS dblCoverCategoryId, dbo.tProduct.strProdPictUrl AS strCoverSrc, dbo.tProduct.floatPrice AS floatCoverCost,
dbo.tProduct.intStockQuantity AS intProductStock, dbo.tProduct.isInDesignTool as isInDesignTool
FROM dbo.tProduct INNER JOIN
dbo.tCategory ON dbo.tProduct.intCategoryId = dbo.tCategory.intCategoryId
WHERE (dbo.tCategory.intCategoryId = 6) AND (dbo.tProduct.isDeleted = 0)
ERROR message
===============
Error Message: Column Name 'StrProdPictURL' appears more than once in the result column list
Thanks in Advance...
DollarJunkie
View 5 Replies
View Related
Feb 14, 2006
Hi,I'm writing a very simple content management site as a first web project.The info is being entered in a FreeTextBox ID'd as FreeTextBox2. When I use an UPDATE command to update the SQL record it inserts a NULL value into the record. If I change the variable to simple text it inserts it properly. For example, this inserts Test into the record: UpdateCommand="DECLARE @FreeTextBox2 as varChar(100) UPDATE t_content SET PageContent = 'Test' WHERE PageID = 1">But when I run this statement it inserts a NULL: UpdateCommand="DECLARE @FreeTextBox2 as varChar(100)
UPDATE t_content
SET PageContent = @FreeTextBox2 WHERE PageID = 1">Could someone please point out where I'm going wrong? Thx :)
View 7 Replies
View Related
Jul 31, 2006
Hi ,
I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.
1° question: Is Microsoft sql server 2005 everywere compatible for Microsoft windows embedded?
(I ask this because in specific of its is not clear)
2° question: In the case that it's compatible, if I instal it in a industrial Pc can it to act by database server or necessity of a remote server?
Tanks
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Aug 21, 2015
On NumaNode1 and NumaNode3. I can see all of the CPU's there but the checkboxes are disabled.
What could be disabling these?
View 4 Replies
View Related
Apr 5, 2001
Hi:
After I used "copy database" to upgrade a SQL 7 database to SQL 2000, all are fine except the TaskPad view. The table info section is blank. Though we know all tables are in the table object.
Any thought?
David
View 2 Replies
View Related
Oct 25, 2005
I am importing a csv file into a temp table (works fine), now I want to take the data in the temp table based off of the temp id compared to the id of the permanant file.
basically if the record id exists skip to the next line and so forth, I am able to import everything just fine but I cannot get the WHERE condition to acually work.
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1 WHERE table2.col1 <> table1.col1
this is what my actual code is
INSERT INTO USER_INFO (fpu_id,FIRSTNAME, LASTNAME, BOXNUMBER) SELECT id,first,last,box FROM stmailbox WHERE stmailbox.id <> dbo.USER_INFO.USERID
this is the actual error that I get.
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'USER_INFO' does not match with a table name or alias name used in the query.
-----
thanks for the help
View 1 Replies
View Related
Oct 19, 2013
I used to use this code to extract coupon usage from two tables orders and coupons
select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
[code]....
Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are
ID in Promotions
and
PromotionID in PromotionUsage
Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?
View 5 Replies
View Related
Mar 29, 2007
We have 2005 Reporting Services fully functional - Dev, QA and prod (farm). I have at least 5 distinct business group trees in my structure, who have inherited or modified security based upon NT Users and AD groups. Now, we want to replace a major group (<domain_name>Domain Users) with a controlled group.
I cannot find anyway to report or map the folders that a specific NT User and AD group has rights. Cannot it be done?
View 1 Replies
View Related
Mar 30, 2007
Hi there, can anyone tell me how to setup or enable mixed mode authentication in SQL 2005? also is it possible to transfer views or stored procedures from 1 database to another? If so where do I go to do this. I thought a DTS may do it but cant find the correct option in the Management Studio to do this.
Thanks in anticipation,
View 1 Replies
View Related