|
Hi,
I am trying to shred values of ModuleTorulemapid,selecteditemid and customvalue from following xml in SQL server 2005. For this purpose, I have written following code, but not able to shred it properly
----------------------------------------------------------------------
EXEC sp_xml_preparedocument @IDoc OUTPUT, @RollForwardRuleXML
SELECT DISTINCT
ModuleToRuleMapId 'ModuleToRuleMapGUID',SelectedItemId 'SelectedItemGUID',[UniqueIdentifier] 'OperationGUID',
CustomValue
FROM
OPENXML(@IDOC,'/ROOT/RuleBuilderModule/RuleBuilderRule/RuleBuilderOperation /RuleBuilderItem ',2)
WITH(ModuleToRuleMapId UNIQUEIDENTIFIER '/ROOT/RuleBuilderModule/RuleBuilderRule/@ModuleToRuleMapId',
SelectedItemId UNIQUEIDENTIFIER '../@SelectedItemId',
[UniqueIdentifier] UNIQUEIDENTIFIER '../@UniqueIdentifier',
CustomValue NVARCHAR(50) '@CustomValue'
)
EXEC sp_xml_removedocument @IDOC
-----------------------------------------------------------------------
Following is the XML which I am using
-----------------------------------------------------------------------
<ROOT>
<RuleBuilderModule Name="RollForwardMethod" UniqueIdentifier="6d6800a6-9006-4d65-a579-731872b16004" Timestamp="0">
<RuleBuilderRule Expression="Which method, automatic or manual, do you want to use for roll forward? <38fc8c42-c1ba-4843-81a4-69d8b9b21b98>" LockedStatus="3" ModuleToRuleMapId="93d552ea-5d8b-4cbd-b936-030ea4fca60a" RuleType="Question" IsBubbleHelpRequired="False" UniqueIdentifier="b88e94f3-48dc-4ce8-ae0c-be17c1133486" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdAutoManual" ValueSource="Internal" DisplayStyle="Multiple" SelectedItemId="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="38fc8c42-c1ba-4843-81a4-69d8b9b21b98" Timestamp="0">
<RuleBuilderItem Name="Automatic" IsDefault="True" CustomValue="" UniqueIdentifier="45d72595-c7b7-41e4-bea3-1ffefa9bbfae" Timestamp="0" />
<RuleBuilderItem Name="Manual" IsDefault="False" CustomValue="" UniqueIdentifier="b0e4538f-8dc0-46d0-925e-f4a52e0ffdc6" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="Rule: Automatically roll the project <363afba8-df04-4af2-a661-dc458bb55f5d>" LockedStatus="3" ModuleToRuleMapId="d5aeeaa7-09d9-4b33-aff9-74cedef43814" RuleType="Rule" IsBubbleHelpRequired="True" UniqueIdentifier="6ee47614-4632-498e-a619-7cacf3c547c4" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdAutoDuration" ValueSource="Internal" DisplayStyle="Link" SelectedItemId="8cc2752a-575f-484e-be83-1aa79fe1b45d" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="363afba8-df04-4af2-a661-dc458bb55f5d" Timestamp="0">
<RuleBuilderItem Name="after the client's fiscal year end" IsDefault="False" CustomValue="" UniqueIdentifier="38c5f62d-4859-4f1d-bcd2-807c9d81bf53" Timestamp="0" />
<RuleBuilderItem Name="when the project is completed" IsDefault="False" CustomValue="" UniqueIdentifier="3c66e057-baf2-4c81-a85e-1d4120ab8da2" Timestamp="0" />
<RuleBuilderItem Name="when the first linked work object is rolled" IsDefault="True" CustomValue="" UniqueIdentifier="8cc2752a-575f-484e-be83-1aa79fe1b45d" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="How do you want to define who should approve the roll forward? <39d49d0f-6811-4286-8b7e-e75ee9abbdb5>" LockedStatus="3" ModuleToRuleMapId="992f66bb-d84e-4e4c-ba54-c8684b7aa4ae" RuleType="Question" IsBubbleHelpRequired="False" UniqueIdentifier="06b80a58-a700-4f2d-8d8d-e3ce1b752eaa" Timestamp="0">
<RuleBuilderOperation Name="RBuildMthdApprover" ValueSource="Internal" DisplayStyle="Multiple" SelectedItemId="18dca662-6713-4a5e-a3ea-375d48a68afc" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="39d49d0f-6811-4286-8b7e-e75ee9abbdb5" Timestamp="0">
<RuleBuilderItem Name="Individual" IsDefault="True" CustomValue="" UniqueIdentifier="18dca662-6713-4a5e-a3ea-375d48a68afc" Timestamp="0" />
<RuleBuilderItem Name="Role" IsDefault="False" CustomValue="" UniqueIdentifier="93aa037c-efb0-4038-b4ab-8d3f0da43f2d" Timestamp="0" />
<RuleBuilderItem Name="Team" IsDefault="False" CustomValue="" UniqueIdentifier="7984fc62-ad68-47ea-9b25-4ea9e97945b2" Timestamp="0" />
<RuleBuilderItem Name="Client Responsible Staff" IsDefault="False" CustomValue="" UniqueIdentifier="367fc7ee-f547-4fb2-bd8e-18b126e7da27" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
<RuleBuilderRule Expression="Rule: <2346cf95-6281-4710-9a0a-ef743b50960d> should approve the automatic roll forward." LockedStatus="3" ModuleToRuleMapId="175b4326-eb2e-4a96-8930-06081070d471" RuleType="Rule" IsBubbleHelpRequired="False" UniqueIdentifier="f3b9104f-994c-46a7-82d4-7b85b71e4a23" Timestamp="0">
<RuleBuilderOperation Name="RBuildIndividgual" ValueSource="External" DisplayStyle="Link" SelectedItemId="739f5023-62f2-4ac0-a977-c6d074b4b0fd" SelectedItem="Cch.WorkFlow.BusinessEntities.RuleBuilder.RuleBuilderItem" DialogType="ComboStyle" UniqueIdentifier="2346cf95-6281-4710-9a0a-ef743b50960d" Timestamp="0">
<RuleBuilderItem Name="Unassigned" IsDefault="True" UniqueIdentifier="739f5023-62f2-4ac0-a977-c6d074b4b0fd" Timestamp="0" />
<RuleBuilderItem Name="Roger Fedrer" IsDefault="False" UniqueIdentifier="b5c3d0d2-d58e-4211-abdd-361ad729e309" Timestamp="0" />
<RuleBuilderItem Name="Martina Hinggis" IsDefault="False" UniqueIdentifier="0866c3b8-58ba-45fe-9135-3823f84ded31" Timestamp="0" />
<RuleBuilderItem Name="Jennifer Justin" IsDefault="False" UniqueIdentifier="ebac3d11-0b31-4266-a6c5-a04494397312" Timestamp="0" />
<RuleBuilderItem Name="Wilson Danial" IsDefault="False" UniqueIdentifier="c9b17eb4-7915-47ff-801d-90c7ae7abd91" Timestamp="0" />
<RuleBuilderItem Name="John Thomas" IsDefault="False" UniqueIdentifier="48876e2e-899e-4d1a-be00-eeadc744c481" Timestamp="0" />
</RuleBuilderOperation>
</RuleBuilderRule>
</RuleBuilderModule>
</ROOT>
------------------------------------------------------------------
Could anyone please help me out to shred those values properly?
Thanks In Advance
OmPrakash
|
|
|
|
|
I have created the following assembly:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace ClrBusinessObjects
{
public class Person
{
[SqlProcedure]
public static string GetPersonName()
{
return "John Smith";
}
}
}
-------------------------------------------
I have attached the assembly in SQL Server as follows:
CREATE ASSEMBLY
ClrBusinessObjects
FROM 'C:\DLL_TEST\ClrBusinessObjects.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--------------------------------------------
I have tried to associate the assembly to a stored proc as follows:
CREATE PROCEDURE TestSqlClrExample
AS EXTERNAL NAME ClrBusinessObjects.Person.GetPersonName
but the SQL Server compiler returns:
Msg 6505, Level 16, State 1, Procedure TestSqlClrExample, Line 1
Could not find Type 'Person' in assembly 'ClrBusinessObjects'.
--------------------------------------------------------------------
What am I doing wrong?
|
|
|
|
|
|
I am still not clear on what the problem is.
Is there anyone on this forum with expertise in this area who could look at the code that I posted in my first post and troubleshoot what the problem may be?
Since all of the code is in the post then you should be able to easily reproduce this scenario. If the example works successfully for you then maybe it's my database configuration....
|
|
|
|
|
I figured out that the namespace needed to be updated and the method should return an int:
[ClrBusinessObjects].[ClrBusinessObjects.Person].[GetPersonID]
|
|
|
|
|
Hello there,
i have the following problem with the DataView RowFilter:
In my Dataset there is a table that contains my friends and among others there´s a column Nick containing their nickname.
when I use for example
<br />
ds.Tables["tblfriends"].DefaultView.RowFilter = "Nick = 'Alex'";<br />
to filter for Nicknames Alex everything works fine. But when I afterwards change the nickname in this row to let´s say xx, the row is still displayed everytime I set the Rowfilter back to show Alex. When I set it to show xx no line is displayed.
It seems like the DataView somehow remembers the rows connected to a certain RowFilter and does not check if something has changed in the rows such that they don´t match the filter anymore.
Does anyone know if there is some sort of workaround for this problem?? Thanks for your help!
der gunnar
|
|
|
|
|
This might be because of your design. Where are you setting the filter? Is the filter executed again after you change the row? If not then the program is doing what it is supposed to do. Just debug and see if it hits the filter again after changing the value.
|
|
|
|
|
What do you mean with "where are you setting the flter"? Well I set the filter. then the value is changed. Afterwards the row still is displayed, but that´s not what bothers me. When I set the filter to something else and afterwards set it back to the iriginal filter the row with the changed value still shows up, even though it doesn´t match the filter anymore.
any ideas?
|
|
|
|
|
Hi,
In SQL 2005 we have Formula Dialog Box [Model Designer] to compute field values in a table.
How to perform the same operation (computing a field value in table) using SQL server 7.0 ? I didn't find any option to compute during designing the table. Have I to write explicit triggers/function for it ?
Please help me ....
Thank you in advance !
Vardhini Kanakarajan
|
|
|
|
|
Hi!
I thought i was heading somewhere with those links Blue_Boy had sent.. but i've been stuck for quite a while now. So let me describe my problem in more detail: Plese help me 
I have 2 datasets, with identical schema. Now i have merged them using the dataset1.merge(dataset2) method. So my dataset1 is a merge of the two datasets. Now when i try to update the database of dataset1 using dataadapter.update().. method, i dont see the database getting updated.This is the code snippet...
<br />
OleDbCommand accessCommandDB1 = new OleDbCommand(strAccessSelect, accessConnDb1);<br />
OleDbDataAdapter dataAdapterDB1 = new OleDbDataAdapter(accessCommandDB1);<br />
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapterDB1);<br />
<br />
dataSetDB1.Merge(dataSetDB2);<br />
<br />
n = dataAdapterDB1.Update(dataSetDB1, "Customers");<br />
<br />
I see that no records are updated, from the value of n(n =0 after the call)
What could be the reason? Note that i dont have any primay keys to any table in my database
Also wanted to know how to polpulate a fresh empty ACCESS database, with my dataset.. Again no primary keys for any table.. i would prefer this over the update method..
Please help me at the earliest.. 
Awaiting your replies....
Shreyas
|
|
|
|
|
I've never used the merge and there isn't very clear info on it on msdn. But I did notice that it is overloaded. What happens when you try to use dataSetDB1.Merge(dataSetDB2, True) instead?
|
|
|
|
|
I have tried that as well. It doesnt work.
But i am sure the merge has worked because i am printing certain values to confirm it.. Si i guess its some update issue.. Right?
Btw, how do i populate an empty access database with my dataset?
|
|
|
|
|
When you say "empty" do you mean that it doesn't have any tables? Because I'm thinking you'd have to write code to build create sql statements and excute them against the database file.
|
|
|
|
|
Yes, i mean a newly created database, with no tables. So will i have to manually add all tables to it? Or is there something like setting its datasource to the new dataset?
What if i have a new database with the same schema, but no tuples in the tables?
|
|
|
|
|
If you have a database with no tables, you will not be able to save data to it until you create those tables. You could probably write some code to dynamically build the tables by looping through the schema of your dataset object and building sql CREATE TABLE statements that you can run from a command object via the .ExecuteNonQuery function.
If you have a database with the same schema as a dataset, you should be able to use the .Update on the command object and save your data to it.
If you need more help regarding the CREATE TABLE statement, try looking for articles in Code Project or on Google[^] for more background information to get you going.
Hope this helps.
|
|
|
|
|
|
Thanks for your help! 
Btw, dont know why the update didnt work, but i did manage to insert all rows into the database, by generating queries for each tuple..
Shreyas
|
|
|
|
|
hi
how can i select the Minmam value from row that have many coulm
like
select * from room where Capacity >= SumCapacity and featuer='0000'
how can i chose the Minmam Capacity that are grater than SumCapacity
and have the featuer '0000'
please i need help 
|
|
|
|
|
1. Can you post your data how they are stored in table?
2. Can you post the result which you want to get?
I Love T-SQL
|
|
|
|
|
RoomID Capacity Feature
11 40 1100
13 35 1000
137 60 1100
138 92 1100
i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap ( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )
i hop you can help me
|
|
|
|
|
I can't undestand well your question but down below is what i undestand from your question.
asha_s wrote: i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap
I think this fits to your need.
select sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum desc
asha_s wrote: ( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )
the second fit for you.
select top 1 sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum asc
I Love T-SQL
|
|
|
|
|
thanks but i do not want the sum
sumcap:is variable that contain value
i want the room than have the manmam capacity from a set of rooms that are their capacity greater than sumcap
because i have a set of room that have capacity greater than sumcap and
have the same feature and i want to select the one that have the manimam capacity
i hop you understand
please help 
|
|
|
|
|
By manmam you mean minimum? (I hope I had understand you even if i didn't got it 100%)
select top 1 min(roomID) as RoomID,feature from myTable where roomid >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by roomID asc<br />
I Love T-SQL
|
|
|
|
|
yes i mean minimum
can i want the minimum capicity can i write
select top 1 min(Capacity ) as Capacity,feature from myTable where Capacity >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by Capacity asc<br />
thanks so much
|
|
|
|
|
I guess you got right solution, right?
I Love T-SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
