I'm trying to use a recursive query to find path between assembly and parts.
The BOM is similar to(I've limited the number of rows and lines):
BOM NumberMat Number
20000222001770
20000222003496
20000222001527
20000222003495
20002462002005
20005062000246
[code]....
How should I modify it so that is returns the 4 path?
I have the following table.GO/****** Object: Table [dbo].[itTransactionProcess] Script Date:05/01/2007 10:42:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[itTransactionProcess]([TransactionID] [int] IDENTITY(1,1) NOT NULL,[LotNumber] [int] NOT NULL,[CurrentProcessStepID] [int] NOT NULL,[NextProcessStepID] [int] NULL,[CategoryID] [int] NULL,[ProductID] [int] NULL,[ProductVariantID] [int] NULL,[ParentTransactionID] [int] NULL,[TransactionDateEntered] [datetime] NULL,[TransactionDateExit] [datetime] NULL,[Settlement] [money] NULL,[Completed] [int] NULL,CONSTRAINT [PK_itTransactionProcess] PRIMARY KEY CLUSTERED([TransactionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Sample data is as followsBasically what I need to do is return the lotid where all path have asettlement date.this is my current procedure/****** Object: StoredProcedure [dbo].[getPendingSettlementDetails] Script Date: 05/01/2007 10:47:47******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[getPendingSettlementDetails]ASdeclare @LotNumbersTable table(LotNumber int)insert into @LotNumbersTable EXEC GetPendingSettlementsLotNumbersDeclare @ResultsTable table(LotNumber int, Company varchar(150),Contact varchar(150), DateReceived datetime, DateComplete datetime,SettlementLength int)Declare @LotNumber intDeclare @DateRecieved datetime, @DateComplete datetimeDeclare @NumberOfDaysForSettlement intDeclare @Company varchar(150)Declare @Contact varchar(150)select @LotNumber = min(LotNumber) from @LotNumbersTablewhile @LotNumber is not null beginSelect @DateRecieved = min(TransactionDateEntered) fromitTransactionProcess where LotNumber = @LotNumberSelect @DateComplete = max(TransactionDateExit) fromitTransactionProcess where LotNumber = @LotNumber and Settlement isnot nullSET @NumberOfDaysForSettlement = DATEDIFF(DAY, @DateRecieved,@DateComplete)Select @Company = Company from SP_Active_Lot_Deliveries where LotID =@LotNumberSelect @Contact = ContactName from SP_Active_Lot_Deliveries whereLotID = @LotNumberINSERT INTO @ResultsTable (LotNumber, DateReceived, DateComplete,SettlementLength, Company, Contact) Values(@LotNumber, @DateRecieved, @DateComplete,@NumberOfDaysForSettlement, @company, @contact)select @LotNumber = min(LotNumber) from @LotNumbersTable whereLotNumber @LotNumberendSelect * From @ResultsTable where SettlementLength is not nullhere is sample data"TransactionID","LotNumber","CurrentProcessStepID","NextProcessStepID","CategoryID","ProductID","ProductVariantID","ParentTransactionID","TransactionDateEntered","TransactionDateExit","Settlement","Completed""628","47","1","2","5","","","","","2007-05-0110:23:15.747000000","","""629","47","1","3","17","","","","","2007-05-0110:23:15.747000000","0.25","""630","47","1","4","34","","","","","2007-05-0110:23:15.747000000","-0.15","""631","47","1","3","38","","","","","2007-05-0110:23:15.747000000","-0.15","""632","47","1","4","33","","","","","2007-05-0110:23:15.747000000","-0.35","""633","47","1","3","15","","","","","2007-05-0110:23:15.747000000","10","""634","47","2","3","86","","","628","2007-05-0110:23:15.747000000","2007-05-01 10:32:41.320000000","-0.35","""635","47","3","","17","","","629","2007-05-0110:23:15.747000000","","","""636","47","4","","34","","","630","2007-05-0110:23:15.747000000","","","""637","47","3","","38","","","631","2007-05-0110:23:15.747000000","","","""638","47","4","","33","","","632","2007-05-0110:23:15.747000000","","","""639","47","3","","15","","","633","2007-05-0110:23:15.747000000","","","""640","47","2","3","85","","","628","2007-05-0110:24:47.983000000","2007-05-01 10:32:41.320000000","0.05","""641","47","2","4","88","","","628","2007-05-0110:24:56.343000000","2007-05-01 10:32:41.333000000","0.8","""642","47","2","4","9","","","628","2007-05-0110:25:07.517000000","2007-05-01 10:32:41.333000000","-0.15","""643","47","2","4","100","","","628","2007-05-0110:25:22.470000000","2007-05-01 10:32:41.333000000","-0.35","""644","47","2","4","90","","","628","2007-05-0110:25:44.297000000","2007-05-01 10:32:41.333000000","-0.35","""645","47","2","4","12","","","628","2007-05-0110:25:59.347000000","2007-05-01 10:32:41.333000000","-0.15","""646","47","2","4","26","","","628","2007-05-0110:26:12.610000000","2007-05-01 10:32:41.333000000","-0.35","""647","47","2","3","94","","","628","2007-05-0110:26:29.523000000","2007-05-01 10:32:41.333000000","-3","""648","47","2","3","95","","","628","2007-05-0110:26:47.323000000","2007-05-01 10:32:41.333000000","-0.35","""649","47","2","3","38","","","628","2007-05-0110:27:01.450000000","2007-05-01 10:32:41.333000000","-0.15","""650","47","2","4","33","","","628","2007-05-0110:27:15.533000000","2007-05-01 10:32:41.333000000","-0.35","""651","47","2","4","34","","","628","2007-05-0110:27:33.767000000","2007-05-01 10:32:41.333000000","-0.15","""652","47","2","3","96","","","628","2007-05-0110:27:46.850000000","2007-05-01 10:32:41.350000000","-0.35","""653","47","2","3","97","","","628","2007-05-0110:28:00.917000000","2007-05-01 10:32:41.350000000","0.05","""654","47","2","4","36","","","628","2007-05-0110:28:10.813000000","2007-05-01 10:32:41.350000000","-15","""655","47","2","4","37","","","628","2007-05-0110:28:25.347000000","2007-05-01 10:32:41.350000000","0.35","""656","47","2","3","98","","","628","2007-05-0110:28:36.917000000","2007-05-01 10:32:41.350000000","-0.35","""694","47","2","10","26","","","628","2007-05-0110:32:17.170000000","2007-05-01 10:32:41.350000000","","""695","47","2","10","35","","","628","2007-05-0110:32:27.883000000","2007-05-01 10:32:41.350000000","45","""696","47","3","","86","","","634","2007-05-0110:32:41.320000000","","","""697","47","3","","85","","","640","2007-05-0110:32:41.333000000","","","""698","47","4","","88","","","641","2007-05-0110:32:41.333000000","","","""699","47","4","","9","","","642","2007-05-0110:32:41.333000000","","","""700","47","4","","100","","","643","2007-05-0110:32:41.333000000","","","""701","47","4","","90","","","644","2007-05-0110:32:41.333000000","","","""702","47","4","","12","","","645","2007-05-0110:32:41.333000000","","","""703","47","4","","26","","","646","2007-05-0110:32:41.333000000","","","""704","47","3","","94","","","647","2007-05-0110:32:41.333000000","","","""705","47","3","","95","","","648","2007-05-0110:32:41.333000000","","","""706","47","3","","38","","","649","2007-05-0110:32:41.333000000","","","""707","47","4","","33","","","650","2007-05-0110:32:41.333000000","","","""708","47","4","","34","","","651","2007-05-0110:32:41.333000000","","","""709","47","3","","96","","","652","2007-05-0110:32:41.350000000","","","""710","47","3","","97","","","653","2007-05-0110:32:41.350000000","","","""711","47","4","","36","","","654","2007-05-0110:32:41.350000000","","","""712","47","4","","37","","","655","2007-05-0110:32:41.350000000","","","""713","47","3","","98","","","656","2007-05-0110:32:41.350000000","","","""714","47","10","","26","","","694","2007-05-0110:32:41.350000000","","","""715","47","10","","35","","","695","2007-05-0110:32:41.350000000","","",""If you follow transaction id 714 up through the parent transaction idsit doesn't not have a settlement cost yet lot 47 shows up as settled.Thanks for you help.
View 6 Replies View RelatedHi
I wrote a simple sql query to get the shortest path length from node 1 to all the other nodes. Since there's a loop in the graph, I want to prevent it from going back to some nodes it has expanded before.
I got the following error:
Msg 253, Level 16, State 1, Line 2
Recursive member of a common table expression 'CTE_Sample' has multiple recursive references.
It is referring to "and Table1.t NOT IN (select fr from CTE_Sample)"
Can somebody help me to solve it?
btw. How to use the UNION, EXCEPT or INTERSECT operators when doing the recursive join? It seems I must use UNION ALL.
Thanks!
drop table Table1
CREATE TABLE Table1 ( fr int, t int)
INSERT Table1 VALUES (1, 2)
INSERT Table1 VALUES (2, 3)
INSERT Table1 VALUES (3, 4)
INSERT Table1 VALUES (1, 3)
INSERT Table1 VALUES (1, 4)
INSERT Table1 VALUES (4, 5)
INSERT Table1 VALUES (4, 2)
GO
WITH CTE_Sample (fr, t, level) AS
(
SELECT Table1.fr, Table1.t, 1 AS level
FROM Table1
WHERE fr=1
UNION ALL
SELECT Table1.fr, Table1.t, level+1
FROM Table1
INNER JOIN CTE_Sample ON Table1.fr = CTE_Sample.t
and Table1.t NOT IN (select fr from CTE_Sample)
)
SELECT CTE_Sample.t, min(CTE_Sample.level)
FROM CTE_Sample
group by CTE_Sample.t
i've following data in a recursive query:
Par_IdIDABCDEFLevel
Null022646000100022646
02264602242632000002264622426
02264602253232100002264622532
02264602254082000002264622540
02264602255751100002264622557
[code]....
in few words i need subtotal only for who have children.I tried with rollup but i wasn't able to have it similar to the aspected.I put it the level just to let clearer the dependencies.
I have following input:
CREATE TABLE #tree
(
Childid varchar(20),
Parentid varchar(20)
)
INSERT INTO #tree
(Childid,ParentId)
SELECT '123' , null UNION ALL
SELECT '456' , '123' UNION ALL
SELECT '789' , '456' UNION ALL
SELECT '870' , '456' UNION ALL
SELECT '985' , '870';
Input:
Child IDParent ID
123 NULL
456 123
789 456
870 456
985 870
I am trying to populate lowest level child with path and depth...Output should be:
Child IDParent IDLast ChildPath Depth
123 NULL 789/123 1
456 123 789/123/456 2
789 456 789/123/456/789 3
123 NULL 985 /123 1
456 123 985/123/456 2
870 456 985/123/456/870 3
985 870 985/123/456/870/9854
Dear,
I'm having this table:
PathIDParentPathIDPath
1 NULL D:
2 1 Sections
3 2 Bin
4 3 Data
5 4 FinancialReport.doc
6 4 DebtReport.doc
7 3 db.dll
I would like to create a store procedures that will return me a full path by passing a PathID
I started with this code:
DECLARE @path_id int
SET @path_id = 5;
WITH fullPath (PathID, ParentPathID, Path)
AS
(
SELECT PathID, ParentPathID, Path
FROM tblPath WHERE PathID = @path_id
UNION ALL
SELECT tblPath.PathID, tblPath.ParentPathID, tblPath.Path AS Path
FROM tblPath
JOIN fullPath ON tblPath.PathID = fullPath.ParentPathID
)
SELECT * FROM fullPath
This code will return this:
1 NULL D:
2 1 Sections
3 2 Bin
4 3 Data
5 4 FinancialReport.doc
What I would like to get is something like this:
D:/Sections/Bin/Data/FinancialReport.doc
Any help would be really appreciated. Lost too much time on it already.
Thanks,
pharvey
I need to write recursive query to find child of a parent until the last leaf. Below is my code.
;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)
When I execute this code. It is returning me millions of rows. Whereas i have only 20 rows in a table max 40 rows it should return.
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec
And this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
[Code] ...
I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.
I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>
After the query is run, rows like the above would be changed to
<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
[Code] ....
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
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)
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
I am new to the wonders of CLR and, as can be expected, have hit a snag. We have multiple environments to run this off of (dev, test, production, etc.) and need to be able to use the same scripts to install them. Here is our basic setup:
Batch script runs a master SQL file using sqlcmd utility
Master SQL file runs secondary SQL files, including the one with the CLR definitions
Secondary SQL file with CLR calls the CREATE ASSEMBSLYI want to be able to call
CREATE ASSEMBLY assemblyName FROM 'myAssembly.dll'
or
CREATE ASSEMBLY assemblyName FROM '..myAssembly.dll'
or something to the effect. However, when I try this, it says I cannot do it or it cannot find the file, even though the file is located in the same folder.
Any ideas?
I have the following table:-
CREATE TABLE [dbo].[TransactionComponents](
[pkTransactionComponent] [int] IDENTITY(1,1) NOT NULL,
[pkTransactionID] [int] NOT NULL,
[ComponentID] [int] NULL
) ON [PRIMARY]
With the following data:-
INSERT [dbo].[TransactionComponents]([pkTransactionID], [ComponentID])
SELECT 1,5
UNION SELECT 1,6
UNION SELECT 1,7
UNION SELECT 1,8
[Code] ....
pkTransactionID and ComponentID both link to the same column on another table this enables a many to many relationship, what I need to figure out is a complete tree of relationships from one of the ID's in it. I think I need to write a recursive CTE to achieve this but I am not entirely sure how to write it. Below is my attempt:-
DECLARE @ID INT
SET @ID = 1;
WITH
cteTxHeirachy (TxID, RelTxID, TxLevel)
[Code] ...
This returns:-
15
16
17
18
19
110
But the following are missing:-
10 2
2 11
2 12
3 and 4 should not be returned. I figured if I added the code that is commented out in the CTE that should give me everything but I think I get caught in an infinite loop.
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as (
select 0 [iterations], 1e+0 [computed]
union all
select iterations+1,dbo.f(computed)
from where rCTE
where iterations < 30
)
select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]`
articlegroups are defined in `[articlegroups]`
and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
id categorystring
2481446 Taarttoppers > Taarttoppers grap'pig
2481446 Bruidstaart > Taarttoppers > Grappig
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0
FROM articlegroups ga
INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id
INNER JOIN products gp on gp.id=pcm.artikelid
WHERE gp.id=2481446
[code]....
I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.
So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?
*some data redacted
Query:
DECLARE @a XML
SET @a =
(SELECT FileCreationDate,
(
SELECT PCE_TPD.PJN,
(
SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA
[Code] ....
Current Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
[Code] ...
Desired Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>
[Code] ...
I have a database with deployed CLR assembly on SQL 2008. I recently took a backup for the database and restored it on SQL 2012 server. Everything worked fine except very strange working of CLR assemblies functions.The result of the function is very odd. It manipulates the numeric string and return the result. It is returning very strange result on SQL 2012 as compared to SQL 2008.Example SQL 2008 ManipulateString('1234') returns 2345 On SQL 2012 the same call returns 1155.I am also trying to deploy the same assembly to SQL 2012 server database. I have successfully register the assembly, but the database do not list the exposed methods.
View 5 Replies View RelatedI would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View Relatedsql recursive query, for example purpose i m providing sample table with insert script
CREATE TABLE Details(
parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)
GO
INSERT INTO Details
SELECT '','7419-01',0 union all
SELECT '7419-01','44342-00',1 union all
SELECT '7419-01','45342-00',1 union all
SELECT '7419-01','46342-00',1 union all
SELECT '7419-01','47342-00',1 union all
SELECT '7419-01','48342-00',1 union all
SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
NULL,'7419-01',0
'7419-01','52342-00',1
'7419-01','52342-00',1
'52342-00','54342-00',2
'54342-00','54552-00',3
'54552-00','R111-54',4
'R111-54','R222-54',5
'R222-54','52342-00',6
Here is my problem:-
declare @test as varchar(32)
declare @test2 as varchar(32)
set @test='today''s problem'
set @test2='my <string> '
select @test as '@attribute' for xml path ('myrow')
select @test2 as '@attribute' for xml path ('myrow')
I want for xml path to correctly encode the single apostrophe as &apos but the single apostrophe doesn't get encoded. In the second example the greater and less than does get encoded.
I have a table with two columns
id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG
And in a physical system there are more files than the table.
D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG
D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG
I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...
Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.
Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.
Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
I created an assembly to access my SSRS web service in a Script task. The package runs fine on my machine but gets the following error from the production box.....
The script threw an exception: Could not load file or assembly 'Microsoft.SqlServer.ReportingServices2005, Version=0.0.0.0, Culture=neutral, PublicKeyToken=3bd4760abc5efbcb' or one of its dependencies. The system cannot find the file specified.
I followed the exact same procedures for creating the .dll on the production box as I did developing on my machine...strong name, load to gac etc....but it still cannot find it.
My production SQL Server is 64 bit so perhaps there is another step I need to take? Anyone have a clue as to what I may be missing?
TIA
Hi,
I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.
The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.
I have created the assembly in sqlserver thus:
create assembly PinCodeLoader from
'C:PinCodeLoaderPinCodeLoaderPinCodeLoaderinDebugPinCodeLoader.dll'
with permission_set = external_access
But when I try to reference the assembly from a stored proc
create procedure dbo.interface_processPinCodefile(@filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main
I get the following error:
Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.
I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows
namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{
Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.
Has anybody come across this or can they offer any assistance?
Many thanks,
Paul
I am attempting to create a CLR Procedure. I was able to create the assembly, but I am unable to create a procedure on the assembly. This is the error I receive:
Msg 6505, Level 16, State 1, Procedure DINEServiceProc, Line 2
Could not find Type 'DINEServiceProc' in assembly 'DINEService'
Here is the VB code to create the class:
<code>
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class DINEServiceProc
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub ServiceProc(ByVal iMsg As String, ByVal iMsgType As String)
Dim conn As SqlConnection
'Create an in-process connection to the instance of SQL Server
conn = New SqlConnection("Context Connection=True")
Dim DINEService As New DINEService
Try
conn.Open()
DINEService.ProcessStartRequest(iMsg, iMsgType)
Catch sqe As SqlException
'Console.WriteLine(sqe.Message)
Return
Finally
conn.Close()
End Try
End Sub
End Class
</code>
And here is the code to create the assembly and the procedure:
<code>
USE [ServiceBrokerTest]
GO
/****** Object: SqlAssembly [DINEService] Script Date: 01/03/2006 10:38:00 ******/
CREATE ASSEMBLY [DINEServiceProc]
AUTHORIZATION [dbo]
FROM 'D:EHITServiceBrokerDINEServiceDINEServiceinDebugDINEService.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE dbo.DINEServiceProc
(
@msg nvarchar(MAX),
@msgType nvarchar(MAX)
)
AS EXTERNAL NAME DINEServiceProc.DINEServiceProc.ServiceProc;
</code>
What am I doing wrong here?
RESTORE DATABASE NewAbitaDB
FROM DISK = 'c:7augbkup.bak' WITH REPLACE,
here, instead of "c:" it has to take the current path that means where i loaded the backup file, how to find out?
which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.
Hello,
The main problem I am currently facing is that I do not have the following directory to place the DLL file: C:Program FilesMicrosoft SQL Server80ToolsReport Designer. It is the "Report Designer" part that is missing. I tried adding it, but that didn't work.
Any ideas why that is?
Thank you.
-Gumbatman