Monday, September 9, 2013

[SQL Server 2008 issues] Add a flag field if all rows match?

[SQL Server 2008 issues] Add a flag field if all rows match?

Add a flag field if all rows match?

Posted: 08 Sep 2013 09:12 AM PDT

Hi,I have two tables, salesOrders and ProductList as per below:[code="other"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[salesOrders]( [SalesOrderID] [int] NOT NULL, [SalesOrderLineID] [int] NOT NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [varchar] (5) NOT NULL, [UnitPrice] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL) ON [PRIMARY]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[ProductList]( [ProductID] [varchar](5) NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOinsert into salesOrders values ('43659','1','1','A776','2024.994','2024.994')insert into salesOrders values ('43659','2','3','A777','2024.994','6074.982')insert into salesOrders values ('43659','3','1','A778','2024.994','2024.994')insert into salesOrders values ('43659','4','1','A771','2039.994','2039.994')insert into salesOrders values ('43659','5','1','A772','2039.994','2039.994')insert into salesOrders values ('43659','6','2','A773','2039.994','4079.988')insert into salesOrders values ('43659','7','1','A774','2039.994','2039.994')insert into salesOrders values ('43659','8','3','A714','28.8404','86.5212')insert into salesOrders values ('43659','9','1','A716','28.8404','28.8404')insert into salesOrders values ('43659','10','6','A709','5.7','34.2')insert into salesOrders values ('43659','11','2','A712','5.1865','10.373')insert into salesOrders values ('43659','12','4','A711','20.1865','80.746')insert into salesOrders values ('43660','1','1','A762','419.4589','419.4589')insert into salesOrders values ('43660','2','1','A758','874.794','874.794')insert into salesOrders values ('43661','1','1','A745','809.76','809.76')insert into salesOrders values ('43661','2','1','A743','714.7043','714.7043')insert into salesOrders values ('43661','3','2','A747','714.7043','1429.4086')insert into salesOrders values ('43661','4','4','A712','5.1865','20.746')insert into salesOrders values ('43661','5','4','A715','28.8404','115.3616')insert into salesOrders values ('43661','6','2','A742','722.5949','1445.1898')insert into salesOrders values ('43661','7','3','A775','2024.994','6074.982')insert into salesOrders values ('43661','8','2','A778','2024.994','4049.988')insert into salesOrders values ('43661','9','2','A711','20.1865','40.373')insert into salesOrders values ('43661','10','2','A741','818.7','1637.4')insert into salesOrders values ('43661','11','4','A776','2024.994','8099.976')insert into salesOrders values ('43661','12','2','A773','2039.994','4079.988')insert into salesOrders values ('43661','13','2','A716','28.8404','57.6808')insert into salesOrders values ('43661','14','2','A777','2024.994','4049.988')insert into salesOrders values ('43661','15','5','A708','20.1865','100.9325')insert into salesOrders values ('43662','1','3','A764','419.4589','1258.3767')insert into salesOrders values ('43662','2','5','A770','419.4589','2097.2945')insert into salesOrders values ('43662','3','2','A730','183.9382','367.8764')insert into salesOrders values ('43662','4','4','A754','874.794','3499.176')insert into salesOrders values ('43662','5','3','A725','183.9382','551.8146')insert into salesOrders values ('43662','6','5','A762','419.4589','2097.2945')insert into salesOrders values ('43662','7','3','A765','419.4589','1258.3767')insert into salesOrders values ('43662','8','2','A768','419.4589','838.9178')insert into salesOrders values ('43662','9','1','A753','2146.962','2146.962')insert into salesOrders values ('43662','10','1','A756','874.794','874.794')insert into salesOrders values ('43662','11','3','A763','419.4589','1258.3767')insert into salesOrders values ('43662','12','1','A732','356.898','356.898')insert into salesOrders values ('43662','13','6','A758','874.794','5248.764')insert into salesOrders values ('43662','14','1','A729','183.9382','183.9382')insert into salesOrders values ('43662','15','3','A722','178.5808','535.7424')insert into salesOrders values ('43662','16','1','A749','2146.962','2146.962')insert into salesOrders values ('43662','17','3','A760','419.4589','1258.3767')insert into salesOrders values ('43662','18','1','A726','183.9382','183.9382')insert into salesOrders values ('43662','19','1','A733','356.898','356.898')insert into salesOrders values ('43662','20','1','A738','178.5808','178.5808')insert into salesOrders values ('43662','21','3','A766','419.4589','1258.3767')insert into salesOrders values ('43662','22','1','A755','874.794','874.794')insert into salesOrders values ('43663','1','1','A760','419.4589','419.4589')insert into salesOrders values ('43663','2','1','A755','874.794','874.794')insert into salesOrders values ('43664','1','1','A772','2039.994','2039.994')insert into salesOrders values ('43664','2','4','A775','2024.994','8099.976')insert into salesOrders values ('43664','3','1','A714','28.8404','28.8404')insert into salesOrders values ('43664','4','1','A716','28.8404','28.8404')insert into salesOrders values ('43664','5','2','A777','2024.994','4049.988')insert into salesOrders values ('43664','6','3','A771','2039.994','6119.982')insert into salesOrders values ('43664','7','1','A773','2039.994','2039.994')insert into salesOrders values ('43664','8','1','A778','2024.994','2024.994')insert into salesOrders values ('43665','1','2','A711','20.1865','40.373')insert into salesOrders values ('43665','2','1','A773','2039.994','2039.994')insert into salesOrders values ('43665','3','1','A707','20.1865','20.1865')insert into salesOrders values ('43665','4','2','A715','28.8404','57.6808')insert into salesOrders values ('43665','5','2','A777','2024.994','4049.988')insert into salesOrders values ('43665','6','2','A712','5.1865','10.373')insert into salesOrders values ('43665','7','2','A775','2024.994','4049.988')insert into salesOrders values ('43665','8','1','A778','2024.994','2024.994')insert into salesOrders values ('43665','9','6','A709','5.7','34.2')insert into salesOrders values ('43665','10','1','A776','2024.994','2024.994')insert into salesOrders values ('43666','1','1','A764','419.4589','419.4589')insert into salesOrders values ('43666','2','1','A753','2146.962','2146.962')insert into salesOrders values ('43666','3','1','A732','356.898','356.898')insert into salesOrders values ('43666','4','1','A756','874.794','874.794')insert into salesOrders values ('43666','5','2','A768','419.4589','838.9178')insert into salesOrders values ('43666','6','1','A766','419.4589','419.4589')insert into salesOrders values ('43667','1','3','A710','5.7','17.1')insert into salesOrders values ('43667','2','1','A773','2039.994','2039.994')insert into salesOrders values ('43667','3','1','A778','2024.994','2024.994')insert into salesOrders values ('43667','4','1','A775','2024.994','2024.994')insert into salesOrders values ('43668','1','3','A756','874.794','2624.382')insert into salesOrders values ('43668','2','2','A753','2146.962','4293.924')insert into salesOrders values ('43668','3','7','A760','419.4589','2936.2123')insert into salesOrders values ('43668','4','6','A765','419.4589','2516.7534')insert into salesOrders values ('43668','5','6','A715','28.8404','173.0424')insert into salesOrders values ('43668','6','6','A730','183.9382','1103.6292')insert into salesOrders values ('43668','7','2','A707','20.1865','40.373')insert into salesOrders values ('43668','8','2','A711','20.1865','40.373')insert into salesOrders values ('43668','9','2','A754','874.794','1749.588')insert into salesOrders values ('43668','10','4','A712','5.1865','20.746')insert into salesOrders values ('43668','11','2','A729','183.9382','367.8764')insert into salesOrders values ('43668','12','3','A755','874.794','2624.382')insert into salesOrders values ('43668','13','3','A761','419.4589','1258.3767')insert into salesOrders values ('43668','14','2','A770','419.4589','838.9178')insert into salesOrders values ('43668','15','3','A726','183.9382','551.8146')insert into salesOrders values ('43668','16','2','A764','419.4589','838.9178')insert into salesOrders values ('43668','17','2','A766','419.4589','838.9178')insert into salesOrders values ('43668','18','2','A725','183.9382','367.8764')insert into salesOrders values ('43668','19','1','A716','28.8404','28.8404')insert into salesOrders values ('43668','20','2','A768','419.4589','838.9178')insert into ProductList values ('A755')insert into ProductList values ('A760')insert into ProductList values ('A776')insert into ProductList values ('A777')[/code]I have the following query:[code="other"]select p.*, s.*From salesOrders s left join ProductList pon s.ProductID = p.ProductID[/code]I want to add another field called "OrderMatched", where it will show a values of "yes" or "no" if the Product from the table ProductList appears on every SalesOrderLineID for that SalesOrderID. So for SalesOrderID 43663 this will show as Yes.Thanks

General Syntax on calling a stored procedure within a stored procedure

Posted: 08 Sep 2013 10:37 AM PDT

Hi All,Please I need someone to give me a general syntax on how to call a stored procedure within a stored procedure.Thanks,Em

export to csv

Posted: 01 Sep 2013 09:08 AM PDT

Hi ProfessionalsI have a script thats exports to a csv file[code]exec exporttocsv 'select top 5 * from newtable', 'test.csv'[/code]the problem I am encountering is that some of the csv files are over 100MB making it virtually impossible to open, manipulate or email to work colleagues.i am sure it is possible but do not know the correct syntax but is there a way to select say the first 100,000 rows then the next 100,000 rows and so on then finallly however may rows are left into more than one csveg[code]exec exporttocsv 'select top 100 * from newtable', 'test.csv'exec exporttocsv 'select NEXT100,000 rows from newtable', 'test1.csv'exec exporttocsv 'select REMAINING rows from newtable', 'test2.csv'[/code]

Violation Of primary key constraints, Cannot insert a duplicate key in object

Posted: 08 Sep 2013 07:25 AM PDT

Hi All,Please I need help on how to solve this problem. I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates. Please any help will be appreciated.EO

Row not being deleted from the publication when it's deleted in a subscription

Posted: 07 Sep 2013 11:48 PM PDT

We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it's a production issue.Note: delete_tracking is enabled(set to '1')Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog