-- Prepare sample data
DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))
INSERT@Sample
SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL
SELECT3, '172.16.11.0', '255.255.255.252'
-- Show the expected output
SELECTID,
NetworkIP,
SubnetMask,
dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange
FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP
(
@IP VARCHAR(15),
@NetIP VARCHAR(15),
@MaskIP VARCHAR(15)
)
RETURNS BIT
AS
BEGIN
RETURNCASE
WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT)
AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT)
AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT)
AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT)
THEN1
ELSE0
END
ENDE 12°55'05.25"
N 56°04'39.16"
We have a SQL 2014 AlwaysOn availability group running on two Windows 2012 R2 servers that are in the same subnet. We created a new server in a second subnet, installed SQL, joined the server to the Windows cluster, added a new IP resource for the new cluster, and performed the other remaining steps to add a new AG replica to the SQL instance on this new server. When we try to move the core cluster resources to the new node to test failover, we get an error. Here's the command we've been using:
Move-ClusterGroup "Cluster Group" -Node node3
and it returns the error: The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group...I've checked the ownership of the cluster groups and the cluster resources and it looks like they are set appropriately:
>Get-ClusterGroup | Get-ClusterOwnerNode Cluster Object Owner Nodes ---------------- --------------- Available Storage {} Cluster Group {node1,node2,node3} SQLAG {node1,node2,node3}
[code]....
We've double-checked that all IP resources are in the right subnets and that the dependencies for the Cluster Name resource and the Listener Name resource are set appropriately. I'm not sure what else to check since the PowerShell commands seem to indicate that node3 is an owner of the appropriate resources. What other things need to be checked or if the ownership being checked isn't the same as what PowerShell is checking?
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.
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.
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?
I am working on a app that stores IPs in a sqlsvr 2k db.. all is well and good but when doing some reports on the data, I need to include/exclude certain ip ranges/subnets. Is there an easy way to do this, is there an IP between function that I might not know about? Some like where IP in "127.144.4.0/24" or where ip between x and y? Otherwise I will have to break apart the ip into maybe its class c/d and convert it to a number and do some boolean >< logic on it. Thanks,BD
Hi, I need to develop an application where my web server is in different subnet (XXX.XXX.35.XXX) and SQL server 2005 on other subnet(XXX.XXX.46.XXX). But i could not connect to it. When I connect to remote server on same subnet the exception is not thrown. But an arror specifying(could not connect to remote server) if i connect to different subnet.Can any one help me please?
I have a SQL-Server on different subnet and I want to access it, I have all rights on that computer , but cant access on my local computer (SQL SErver) which is insided company's network... Is there a way i can link the server? or ?
According to Microsoft, if you need to change the subnet for the clustered servers, you will need to either edit the registry or reinstall virtual SQL Server.
Does any one know which registry key(s) store the subnet information? I was reviewing the clustered key but was able to find the key for the IP address only.
Hi Folks,I'm very new to SQL Server. I have a home network. My primary connectionto the internet is using "Gateway 1" a linksys router, of which, my serveris a LAN node on that router. I have another LAN node connecting to the WANnode of the "Gateway 2" a wireless USR router to use with my laptop. Usingthe wireless laptop, I am on a different subnet than the Server. I'd liketo make a connection to the server through Visual Studio.net but I can'tseem to do it. I'm using TCP/IP with a port different than the default port(let's call it port 2110, even though it's not). I do have port forwardingon Gateway1 and I've tried everything from just the server internal IPaddress, the external IP address, IP address with port designation, URL withport designation, nothing seems to work. Any ideas?Thanks!Rick
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT. It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
I have setup two node SQL 2012 Always ON cluster with file share witness. I have configured two networks, one for heartbeat and second for public.My question is do I need a third network/ subnet for AG Listener? Which network does the SQL user databases in the AG group use to replicate between the replica servers?When I do the SQL backups, do I need to backup primary and secondary replicas or just backup the databases in the secondary replica?
sorry for my question, maybe it seems un professional but I need to know the answer,
is it possible to enter the data in a sql table in a specific format and how it could be?
for example I want to have a mask in the table to enter the data in this format ##.## so it will not accept any other data to be typed in without this format.
We are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
I have statement which is comparing a smalldatetime column to literal string as follows:
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?
When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.
AnupG writes "hi, the most common feature that any RDBMS provides should be that if we want to store the data in the MSSQL tables then the data should be presented in masked/formatted like for eg.the password text in field should be like "*****". I am using SQL 2000 but i unofrtunately i couldn't find any such feature in defining the data type in SQL server 2000 ...?"
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'");
OS - Windows 2012 R2 Standard Edition. DB - SQL 2012 Enterprise edition
Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.
1. Local First 2 Nodes are same subnet XXX.XX.44.XX 2. DR Node another subnet XXX.XX.128.XX
Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover.
I have a web server that is on our DMZ subnet and I have a sql 2014 database server with Always on Availability Groups that is on our internal domain subnet.The failover cluster is on the internal domain subnet as well. I am trying to connect a web application, that is working off the .net 2.0 framework, to an SQL Server 2014 database using the Always on Availability Group Listener.
The connection strings works perfect with the named instance of the sql server but when I try to use the Availability Group listener it timed out. I did some research online and was led to believe that increasing the connection time would resolve it. I did that and got a different error pertaining to a successful connection but an error occurred during the login process (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)
I just want to connect to an Availability Group Listener on the domain subnet. I have rules in the firewall to allow all services for the IP of the Listener. I switch back to the named instance of the same server and it works flawlessly. I have also connected to the Listener while running the application on the same subnet as the sql server without an issue. I have tried the IP instead of the Listeners name and still received the same error on the DMZ subnet. I'm running on Windows Server 2012 R2 on all servers.
The Web Server is using IIS 8.5..All Physical Machines except the replicas and webserver are virtual machines.All Static IPs involved.As mentioned all SQL Servers are 2014.
I am trying to build out an AlwaysOn AG with 2 nodes each in a different subnet (in AWS if that matters), windows 2012r2 / SQL 2014 RTM
I created a AG Listener with 2 ip address, 1 for each subnet (checked that neither ip address are used). But whenever i failover the AG to the secondary, and try and connect via the listener it fails,
I am trying to connect via SSMS from the primary instance. and just time out, If i roll over to the primary i can connect no issues, I've tried playing with the connection settings, upping the time out to 30 secs, adding the MultiSubnetFailover=true. etc but not getting any joy.
I am setting up SQL 2014 always on. I was able to set up the replicas between 2 servers in the same subnet.Their IP addresses are say like this:
100.20.200.200 100.20.200.201
When I am trying to introduce another node into the cluster which has IP address like 100.10.101.102, I am getting an error that the server isn't reachable.
Hello all. I am trying to do a calculation within an SQL script, however it doesnt seem to be working and i'm a little bit lost. If anyone could shed some light on where i'm going wring it would be much appreciated. The code I have is:
select EMPLOYEE.EMPLOY_REF AS EDIT_REF, SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT, SV_EMPLOYEE_CURRENT_HOLIDAY.CARRIED_FWD, SV_EMPLOYEE_CURRENT_HOLIDAY.TAKEN, SV_EMPLOYEE_CURRENT_HOLIDAY.REMAINING, SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD, SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED, SV_EMPLOYEE_CURRENT_HOLIDAY.ENTITLEMENT + SV_EMPLOYEE_CURRENT_HOLIDAY.SOLD - SV_EMPLOYEE_CURRENT_HOLIDAY.PURCHASED AS TOTAL_ENTITLEMENT from EMPLOYEE left outer join SV_EMPLOYEE_CURRENT_HOLIDAY on EMPLOYEE.EMPLOY_REF = SV_EMPLOYEE_CURRENT_HOLIDAY.EMPLOY_REF where EMPLOYEE.EMPLOY_REF = = 027
Incidentaly SV_EMPLOYEE_CURRENT_HOLIDAY is a view which currently exists.
In order to find out if an event is late or not I need to do some time calculations in SQL as a Stored procedure. I have a DateTime variable called Due I also have an Allowance variable which is an integer and is an extra allowance for that day and a third variable Now which is set with GETDATE() If I compare Now to Due I can decide if the task is late or not - but I need to take itno account the Allowance. I tried : IF @Due + (@Allowance /24) < @Now ...... However I find that @Allowance/24 always equates to zero so this doesn't work. I'd appreciate any advice. Regards Clive
Hello, I ran into a little problem. My problem is: i need to substract 2 variabeles from 2 different tables in the database
TitleTimes left todayTimes left
My first excercise!15
My second excercise!19
The fields times left are a calculation... the number of times that the admin entered minus a count in the table scores. Has anyone an idea how i can solve this? An example excercise would be great! Thanks in advance
I am attempting to construct a SELECT statement which incorporates some variables. The variables begin life as strings (not String objects) looking like :"6/08/2008" and "06/10/2008" for example. The first is a start date which was retrieved using an AJAX calendar object and the second is an end date retrieved in the same manner. My records are all timestamped by MS SQL (2003) including the clock time. I am stumbling on the syntax. "CallStartTime" is the record's timestamp. The "TraversalString" is something else but I am not attacking that yet. Can anyone make a suggestion or two? SELECT count(*)FROM RealTime WHERE CallStartTime >= '@starttime' AND CallStartTime <= '@endtime' AND TraversalString LIKE '%1.0%'
