I am trying to extract what I believe is called a Node from a column in a table that contains XML. What's the best way to do this? It is pretty straightforward since I'm not even looking to include a WHERE clause.
What I have so far is:
SELECT CCH.OrderID, CCH.CCXML.query('/cc/auth')
FROM tblCCH AS CCH
I have a table which has a field with a data type of Xml. A sample record might contain something like this:
<?xml version="1.0" encoding="utf-16"?>
<order>
<Product Index="14c9643f-63d9-40c5" ProductID="13" Name="............>
<Attribute Name="Paper" OptCatId="1" Value="72" ....../>
<Attribute Name="Ink" OptCatId="2" Value="6" ......../>
<Attribute Name="Bleed" OptCatId="3" Value="15" ......./>
<Attribute Name="Number of Drops" OptCatId="0" Value="1" ...../>
I need to grab the Node/Attribute with the name of "Number of Drops" and get the value of "Value". So in this case, I simply want to get "1". How could I do this?
SELECT "value of Number of Drops"
FROM myTable
WHERE ID = ....
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?
I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query
SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')
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 RelatedWe 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.
Hi all.
This is my first SSIS project it's also an utter mare.
I'm a C# developer with reasonable SQL knowledge but I haven't used SSIS projects in BIS before.
What we have (for some insane reason) is an SQL table like so:
Column A |Column B |Column X |
Where Column X contains XML like this:
<Column C></Column C><Column D></Column D><Column ArrgWTF></Column ArrgWTF>
Where <Column ArrgWTF> contains two '-' delimited values.
What I need is some guidence (or just being told if its possible or not) to take this data and arange it like so (see bellow) in a new SQL table:
Column A |Column B |Column C | Column D |Column ArrgWTF1 |Column ArrgWTF2 |
Where A&D are from the original SQL table, C & D are pulled from the XML and ArrgWTF1 & 2 are sourced from the '-' delimited vales within the XML.
Many, many, thanks in advance.
Hi All,
I have to do this work in ssis.
I have to read a xml and that xml have node like this
<Attribute Name="apDlBhUtil" SF="F" Value="5"/>
<Attribute Name="apDlBhUtilThresh" SF="F" Value="2"/>
<Attribute Name="apUlBhUtil" SF="F" Value="12"/>
I have a table which contaion column name are values of name attribute. there are 50 to 60 attribute in xml.
table script is
create table Attribute
(
apDlBhUtil int,
apDlBhUtilThresh int,
apUlBhUtil int
)
now would please tell me how i can insert related value in my table.
my result would be like
apDlBhUtil apDlBhUtilThresh apUlBhUtil
5 2 12
Thanks in advance.
Manish Jain
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
Is this possible using DAX ?
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedA column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
I want to extract day of the week from given date.
If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
[Code] ....
Source System : Oracle
Target System : Ms Sql Server 2012
ETL Tool Used : SSIS
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
I have an A & B node set up and running. I now want to add a C NODE.
The catch is the C NODE is in a SQL Cluster Environment.
I am guessing I add the Virtual Name of the C node to the current Always ON Cluster?
I tried adding it...the error message says" the Virtual Node is really the A node and to add the A NODE"?
Can I add a new node to an existing sql server 2012 cluster witthout stopping all the current resources?
Currently, there are 2 nodes and 3 sql server instances, which can not be shutdown. Is the process of adding nodes a "online" process?
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I have the following code and trouble reading values of Bank Accounts. If i remove the line it says "xmlns="http://applications.apch1.com/webservice/schema/" then i my query is working. But i cant remove this becasue that is what i will get response from a web service. All the records are stored in the database with this line included.
DECLARE @MyXML XML
SET @MyXML = '<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<CustomerID> A8339 </CustomerID>
<BankAccounts>
[Code] .....
I have installed SQL Server 2012 on Node1 successfully and trying add node on Node2 and its failing with below message. I attached the screen shot.
Here we are not doing multi-subnet failover clustering. Why we are getting extra and asking for IP and what IP should we enter there?Add Node option should automatically detect the IP we configured on Node1.
[Error Message] To support SQL Server multi-subnet failover clustering, you must select at least one valid IP address for every subnet in the cluster.
[Details] Microsoft.SqlServer.Configuration.Cluster.ClusterIPAddressPublicValidationException: To support SQL Server multi-subnet failover clustering, you must select at least one valid IP address for every subnet in the cluster.
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.
View 7 Replies View RelatedDuring the installation of the SSISDB in SQL 2012 AG, the password used to encrypt the database was not preserved. Now when the server is failed over, the SSISDB on the new primary node is not use-able.Document the password and decrypt after using the master key after failover.
View 5 Replies View RelatedWhat I have- Sql server 2012 (Standard Ed) Cluster on Windows 2012 R2 with both instances running on the same node- just to save on License, i.e. technically it’s Act/Pas cluster.
What I am looking for- how to configure cluster (e.g. via quorum, etc) to force both instances failed together? Means if for some reason 1-st instance will fail to node 2 another instance should follow (otherwise it will be Act/Act cluster and 2-nd license is required).
If there is no standard way (cluster configuration I mean) to do it I should create some custom process to monitor where each instance is running.
I have configured Alwayson HA setup as following environment and versions, and working fine to client side by connected with AG Listener.
Two Node WSFC setup. Node1 (Primary), Node 2(Secondary Replica). Normal quorum(Not shared disk)
Server Platform: HyperV
OS: Windows server 2008 R2 64 bit with SP1
RDBMS: MS SQLSERVER 2012 64 bit with SP2
when I am doing testing Failover method between two nodes. automatic failover not happened.
Just shutdown Node1 and try to connect at client side by using AG Listener name but not succeeded, also Cluster name and AG Listener name are not pinging, Second node2 cluster service is not started by automatically due to as below errors. So how will connect client and overcome this issues? Any modification need to be done on both side WSFC and AlwaysOn SQL? for achieving and automatic failover.
Cluster network 'Cluster Network 1' is partitioned. Some attached failover cluster nodes cannot communicate with each other over the network. The failover cluster was not able to determine the location of the failure. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Client side got connected once Node1 get started again also cluster services started.
Our network guys have to carry out an IBM Flex Chassis move at our data centre, which will affect the primary replica of one of our SQL 2012 AlwaysOn Availability Group nodes (the secondary replica won't be affected).
They have suggested using vMotion to migrate the primary replica to another virtual host, which will result in a very brief period of network outage for the node.
I've done some reading and have seen a few potential issues regarding Stun During Page Send (SDPS) and increasing thresholds within WSFC. Unfortunately, we're not able to test this prior to the migration, so I have a few questions...
Would it be necessary to failover to the secondary replica node before performing the vMotion (and back again afterwards)?
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution.
View 9 Replies View RelatedI have a SP that runs on the primary in 18 min and 45 min on the secondary( poorly written cursor,trying to fix it).Both machines are Exactly the same.I ran them in the middle of the night when no one was on the Sec. Node as we use it for reporting.
PLE: 7,000+
AVG Disk sec/write below .01
AVG Disk sec/read below .01
CPU below 5%
both machines set a max dop 4
Few questions on a SQL Server 2012 two node active/passive cluster installation on Win2012.
1. What are the permissions required for the user used to install SQL Server 2012 cluster. Does it need to have any rights on DC or anywhere else apart from the local nodes ?
2. Can we give ANY meaningful name to "SQL Server Network Name" during installation ? Do we need to manually configure it anywhere else before or after the installation ?
3. On what scenarios we need to check/uncheck DHCP check box ?
I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.
The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.
Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).
I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2
--***** Table Definition With Insert Into to provide some basic data ****
IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,
[Code] ....
How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?
It is an active passive cluster which doesn’t allow any testing. All instances have to be failed over together, we aren’t allowed to just failover 1 even for testing purposes. Node 1 is the active node and we can failover to node 2 for 30 days free of charge but services have to then be failed back.
We need to run the cluster with node 1 as the primary node always and 2 just use for failover testing or for less than 30 day periods whilst performing cluster patch upgrades etc.
Now l am sure we could fail over 1 instances at a time for testing and diagnosing issues plus if add a new instance that's not production to get to the platform level as the rest of the instances this would avoid taking production down in the fail over process.
what is the command to uninstall sql node with skip validation in sql 2012 ?
View 1 Replies View RelatedWhen adding a node to a SQL Server 2012 Standard edition cluster, how I do I identify the location for SQL server shared components and the rest of the SQL Server installation binaries?
When adding a node to a SQL Server 2012 Standard edition cluster all the binaries went to the C: drive default location. We put those files on a different drive when installing the first node. What needs to be done so both nodes have the binaries on the same drives and folders?