I would like to know how to put an attribute on the root node of thexml returned from a FOR XML PATH query. One thing I tried is this:selectm.msgid '@msgID',st.namelong 'set/@namelong',st.nameshort 'set/@nameshort',frommsgset minner join settable st on (st.setid = m.setid)where m.msgID = 195for xml path('set'), root('message')but it gives me:<message><set msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /></set><set msgID="195"><set namelong="MORE STUFF" nameshort="M STUFF" /></set><set msgID="195"><set namelong="TESTING 123" nameshort="TEST" /></set></message>here is what I want:<message msgID="195"><set namelong="STUFF HERE" nameshort="STUFF" /><set namelong="MORE STUFF" nameshort="M STUFF" /><set namelong="TESTING 123" nameshort="TEST" /></message>I can't get it. If I use: root(''), then it tells me: "Row tagomission (empty row tag name) cannot be used with attribute-centric FORXML serialization." I'm sure there is a trick to this-- anysuggestions?Many thanks.the chippster
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.
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
Hello, first time here, first time messing with SQL.
When creating a table in SQLServer2005 can you specify a column to be for a derived attribute. example a column monthlysalary and create a column that is a derived attribute - yearly salary. Is this done when creating the table and how is the syntax ?
I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.
For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.
In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.
(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)
Aliases are easy enough to obtain:
select object_id, column_id, name from sys.columns where object_id = object_id('SomeViewsName');
As are the 'physical' columns: SELECT t.object_id as TABLE_OID, c.column_id as COLUMN_OID, c.name FROM sys.views v JOIN sys.sql_dependencies d ON d.object_id = v.object_id JOIN .sys.objects t ON t.object_id = d.referenced_major_id JOIN sys.columns c ON c.object_id = d.referenced_major_id AND c.column_id = d.referenced_minor_id WHERE d.class < 2 AND v.name = 'SomeViewsName';
As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.
Ultimately I'd like to get this result:
Alias used in View, physical table's object id, physical column's id
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected . So how can I do this ??? How to form a realtion between the tables,... ????
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.
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?
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?
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.
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.
I have date and float in attribute in a dimension table. If deploy my cube and I try to create a report with RS, I can't format this data. The value seems to be a String so I have to do a CDate or a CDbl before formating it. I have no problem when I try to format my measures.
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.
I am importing SQL 2000 tables from a developer install of SQL 2000. There are no destination tables to append just new tables being brought in.
I use the All Tasks > Import (DTS) service to do this. I have noticed that the Identity columns do not maintain the Identity attributes. I have to reset them after I import new tables. Is this normal? or is there a bug here?
I checked the MS KB with no success. Anyone have some info on this?
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 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.
Ok I am faced with working with XML on a regular basis, which is fine.
DECLARE @ViewSN INT IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType> insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments) select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
[code]...
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
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.
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
