Transact SQL :: How To Find A Node

Nov 8, 2015

i have a table below like :
Id    ,    Request   

int     nvarchar(100)   
and in Request field i put below data :
1 <request><F3>353535</F3><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F3>353535</F3><F6></F6></request>

now i need to a query that i can find records that exists <F3> and if exists , remove just the <F3> tag

 below like :
1 <request><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F6></F6></request>

View 18 Replies


ADVERTISEMENT

Transact SQL :: HierarchyID - How To Give A Column Name For Node Path

Aug 19, 2015

I just started learning HierarchyID and copied the the following code from [URL] .... and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):

-- shcHierarchyid_1.sql
-- 19 August 2015 13:15 PM

USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
DROP DATABASE shcDB
GO
CREATE DATABASE shcDB
GO

[Code] ....

I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012).  It worked nicely. But, I don't understand the following 2 things:

#1: In the Results, I got:          

(No column name)     Position    Title
                1       /          CEO
                2 /1/         0x58      Purchase Manager
                3      /1/1/         0x5AC0 Purchase Executive
                4      /2/             0x68       Sales Manager
                5 /2/1/         0x6AC0  Sales Executive

I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?) I guessed). How I can give a column name to that column.

#2:   The book lists "USE master  GO .....".  I wonder whether I can do the "hierarchyid" thing in the private database I created!!??  Whether I can do the "hierarchyid" thing in the private database or not.

View 8 Replies View Related

SQL 2012 :: Log Shipping - Reboot Secondary Node And Then Primary Node

Apr 20, 2015

I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.

Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?

When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?

View 3 Replies View Related

Do I Need To Install SQL Server To Second Node In Cluster Prior To Adding The Node?

Jun 12, 2007

I read these instructions:

http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx



But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?



Thanks

View 1 Replies View Related

SQL 2012 :: Running 2 Distinct AGs On Each Node Of A Two Node Cluster?

Oct 23, 2014

I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.

View 1 Replies View Related

SQL 2012 :: 3 Node Cluster Runs With 2 Node Failures?

Jun 11, 2015

We are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).

As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.

However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.

View 4 Replies View Related

Transfering Cluster Resources From One Node To Another Node

Aug 21, 2007



I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.



So please suggest in this regard

View 2 Replies View Related

How Do You Determine The Active Node Inside A Stored Proc For An Active/passive 2-node Cluster?

Jan 16, 2008

I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?

Thanks.

Edit:

Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...

Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.

View 1 Replies View Related

Transact SQL :: How To Find Duplicates For Different Fields

Oct 27, 2015

I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.

What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.

So If I have in the table

Serial          MSDSID          Date          Size...
001                 20            1/1/2015        5
002                 21            1/1/2015        3
001                 22            2/1/2015        1
003                 21            3/1/2015        1
004                 23            1/15/2015      5
003                 22            1/20/2015      6
004                 23            2/21/2015      5
002                 21            4/25/2015      4

I would like the results to show:

Serial            Count
001                  2           the count is 2 because Serial 001 has an MSDSID of 20 and 22
002                  1           the count is 1 because Serial 002 only has MSDSID 21
003                  2           the count is 2 because Serial 003 has an MSDSID of 21 and 22
004                  1           the count is 1 because Serial 002 only has MSDSID 23

It would be even better if the results just showed where the count is greater than 1.

View 5 Replies View Related

Transact SQL :: How To Find Mail Is Queued

Jul 19, 2015

I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.

How  can i find that "Mail queued" now i should commit.

View 4 Replies View Related

Transact SQL :: How To Find If A Column Is Used Anywhere In Database

Oct 20, 2015

I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.

I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used. 

View 14 Replies View Related

Transact SQL :: How To Find Alphanumeric Formats

Jun 12, 2015

create table example (f varchar(10))

insert into example values('fd')
insert into example values('fd')
insert into example values('fd1')
insert into example values('fd23')
insert into example values('fda23')
insert into example values('fd23g')

output:-

[A-Z][A-Z]
[A-Z][A-Z]
[A-Z][A-Z][0-9]
[A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][0-9][0-9][A-Z]

How to write SQl query to fetch this type of output.

View 6 Replies View Related

Transact SQL :: Find Max Of 2 Columns In One Table?

Jul 23, 2015

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative

View 21 Replies View Related

Transact SQL :: Find Duration From Two Records

Oct 26, 2015

I am working on one logic. I have a table as below.

Declare @Table table (ID int, StartDate datetime, EndDate datetime)
Insert Into @Table (ID, StartDate, EndDate)
Values (1, '2013-01-01', '2013-12-31') 
, (1, '2014-01-01', '2014-06-30') 
, (2, '2014-01-01', '2014-07-31') 
, (2, '2014-08-02', '2014-08-30') 
select * from @Table 

I need as below.

ID, StartDate, EndDate
1, '2013-01-01', '2014-06-30'
2, '2014-01-01', '2014-07-31'
2, '2014-08-02', '2014-08-30'

Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?

View 12 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
(    nRef INT,
     nOrd     INT,
     Token     INT,
     nML              INT,
     nNode            INT,
 sSymbol          VARCHAR(50),
 nMessageCode     INT
)
INSERT INTO A
(   nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),

[code]....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 6 Replies View Related

Transact SQL :: Find How Many Rows Replicated

Aug 20, 2015

How Do we find how many ROWS REPLICATED to subscriber at specific interval ?

View 3 Replies View Related

Transact SQL :: How To Find Transaction In A Backup

Oct 22, 2015

I have heard about fn_dblog() function, Is it possible to find a transaction in a backup using fn_dblog() function?

View 3 Replies View Related

Transact SQL :: How To Find A Non Matched Record In 2 Tables

Jul 15, 2015

I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this

Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.

And this query produces wrong results, its giving me match and also non match records.

how to write a query for my non match condition?

View 5 Replies View Related

Transact SQL :: How To Find If User Not Logged In For 45 Days

Nov 20, 2015

I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.

login table usercode  email       login_date
001          a@gmail.com    2015-11-18 22:02:41.153
001          a@gmail.com    xxx
.
.
.

I have another table called userLoged where i have column usercode,email and web_access 

UserLoged table usercode email         web_access
001         a@gmail.com   Y

Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.

View 4 Replies View Related

Transact SQL :: Find A Count On Group Of Member IDs

Aug 20, 2015

I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..

View 6 Replies View Related

Transact SQL :: Find All Duplicate Entries In Table

Jun 11, 2015

Someone ran an update statement multiple times so their are multiple entries in the table.  What is the quickest way to track down the multiple entries?  I would only want to see where timein and timeoff exist in the table multiple times for the same id.  So this would be a duplicate

EntryID -- ID  -- timein -- timeoff
1487   11     2015-05-05 16:33:23   2015-05-05 18:45:26
1623   11     2015-05-05 16:33:23   2015-05-05 18:45:26

View 7 Replies View Related

Transact SQL :: How To Find Duplicates For Multiple Fields

Oct 26, 2015

I have a table that exists of Serial, MSDSID and other fields.

What I need to check is to see if there are Serial numbers that multiple MSDSID numbers.

So if I have

Serial          MSDSID
001                23
002                24
003                25
004                23
005                26
006                24

The results would be

Serial             MSDSID           Count
001                23                   2
004                23                   2
002                24                   2
006                24                   2

View 12 Replies View Related

Transact SQL :: How To Find If Email Is Of Correct Format

Aug 13, 2015

I am getting email from the end client and i need to validate in sql query.

View 3 Replies View Related

Transact SQL :: Query To Find Out Database Usage

Sep 15, 2015

I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.

View 6 Replies View Related

Transact SQL :: Find The Number Which Exists In Different Groups

May 25, 2015

I have a table which has 2 columns and the data is like below

API_Number        Group_Name

1234                     Group A
3241                     Group A
1234                     Group B
4567                     Group C
7896                     Group D
3241                     Group E

 I wanted to find the API numbers which are repeating in different groups. In the output I want

 API_Number           Group_Name

1234                       Group A,Group B
3241                       Group A,Group E

View 5 Replies View Related

Transact SQL :: Find If A Table Is Partitioned By Month Or Day

Nov 6, 2015

Is there a way to figure out if a table is partitioned by month/day in SQL. 

View 2 Replies View Related

Transact SQL :: Find Circular Reference In The Table

Jun 1, 2015

Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null

[Code] ...

--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....

I need a sql qry which will require to find a circular reference in it.....

As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6  or 7 it should return 1 as they have circular reference....

View 4 Replies View Related

Transact SQL :: Find / Replace Numbers In Server

Aug 12, 2015

I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?

View 14 Replies View Related

Transact SQL :: How To Find Weekend Days Per Month

May 13, 2015

I want to find the first weekend day, second weekend day, third weekend day and fourth weekend day per month using sql query. This is getting from recurring appointment. If weekdays = 65 means it accepts only saturday and sunday. So I want first, second, third and fourth weekend days for a month using query in sql server 2008...

View 17 Replies View Related

Transact SQL :: Find Parts Matching In Inventory

Sep 15, 2015

We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it.  The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number.  Below is what I have so far but it only matches exact part numbers.  One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match.  Both PartNumbers are varchar(30). 

SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)

View 3 Replies View Related

Transact SQL :: Find Duplicates Within Existing Query

Nov 18, 2015

I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...

INSERT INTO SurveyInterface.tblLoadISFNotification (OperatingEntityNumber, SDDS, SurveyCodeId, QuestionnaireTypeCodeId, ReferencePeriod, DataReplacementIndicator, PrecontactFlag, SampledUnitPriority)
SELECT ISF.OperatingEntityNumber
       ,[SDDS]
       ,[SurveyCodeId]
       ,[QuestionnaireTypeCodeId]
       ,[ReferencePeriod]
       ,[DataReplacementIndicator]
      
[code]....

Know I also want to add in that new dataset(report) all the duplicates of concatenated variables

ISF.OperatingEntityNumber/ISF.QuestionnaireTypeCodeId 
GROUP BY ISF.OperatingEntityNumber, ISF.QuestionnaireTypeCodeId

View 4 Replies View Related

Transact SQL :: Find Last Transaction Of A Particular Type For Each Customer

Nov 12, 2015

I am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around

SELECT        MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net] 
FROM dbo.tblSalbatTxn
WHERE      ([sbt::typ] = 13 OR [sbt::typ] = 17) 

Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.

I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.

SELECT        TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM            dbo.tblSalAccounts AS a INNER JOIN
                         dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE        (b.[sbt::dte] =
                             (SELECT        MAX([sbt::dte]) AS Expr1
                               FROM            dbo.tblSalbatTxn
                               WHERE        (b.[sbt::typ] = 11 OR
                                                         b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code]))) 

View 8 Replies View Related

Transact SQL :: Find Actual Statements With Wildcards

Jun 17, 2015

STEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO Trace 
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted  from History'),
('Insert into Account  Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')

[code]....

now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved