Quote: Originally Posted by mrtwice99 Yes, but how would you figure out which row was "before" it?
Code:
SELECT id, sortOrder, name FROM daTable
WHERE id = 937 OR sortorder =
( select max(sortorder) from daTable
where sortorder < ( select sortorder from daTable where id = 937) )
The business operation is this:
The data is stored and accessed by Invoice Number and Line Number. If there are 4 lines, 1,2,3,4 and number 2 is deleted the users want lines 3 and 4 reordered to be 2 and 3.
The existing code does things the following way, but I have my suspicions about how bulletproof it is:
Existing Data:
123,1
123,2
123,3
123,4
Begin Transaction
Delete where InvoiceNumber = 123 and LineNumber = 2.
Update set LineNumber=2 where InvoiceNumber=123 and LineNumber=3
Update set LineNumber=3 where InvoiceNumber=123 and LineNumber=4
Commit Transaction
Is there a better way of doing this?
Thanks.
Hi,
I have a parent table (Projects) and few child tables (Project invoices, Project Sub-consultants, etc). Typically, 'Projects' table contain details about projects. Other child tables contain specific information about a particular project, like invoices, details of sub-consultants, etc.
I introduced a 'After Delete' trigger in parent table, which should delete all rows in child tables, at once the row is deleted in parent table. The code I have used is as follows:
Declare @PID char(8) -- Project ID SET @PID = (Select ProjNo from Deleted)BEGIN delete from ProjInvoice where ProjectID = @PID END
Now, when I delete a row in parent table and see the child tables, only the particular field says 'NULL' and all other field remains. (I have tested the SQL statement as stand-alone query and it works fine).
What's wrong with the trigger?
Thanks in advance.
Sathya
Hi,
I have 2 table that are exactly the same and want to create a trigger on table1 that will do the following:
Every time i delete from table1, the "Instead of delete Trigger" will automaticaly delete the data from table2 and not from table1.
Can anyone help me?
Hi!
I have 1 sql server 6.5 sp3 with wrong sortorder, is there anyone who can give some feedback of this job.
- How to?
- What issues should i have in mind?
Thanks in advance
Fredrik
i'm in a bit of a bind at work. if anyone could help, i'd greatlyappreciate it.i have a web app connecting to a sql server using sql serverauthentication. let's say, for example, my login/password isdbUser/dbUser. the web app however, is using windows authentication.so if I am logged into the network as 'DOMAINEric', when I access myweb app, my web app knows that I am 'DOMAINEric'. but to the sqlserver db, I am user 'dbUser'.now, i for each table i have, i need to implement an audit table torecord all updates, inserts, deletes that occur against it. i wasgoing to do so with triggers. this is all fine for selects, inserts,and updates. for each table, i have an updatedby and an updatedate.for example, let's say i have a table:create table blah(id int,col1 varchar(10),updatedby varchar(30),updatedate datetime)and corresponding audit table:create audit_blah(id int,blah_id int,blah_col1 varchar(10),blah_updatedby varchar(1),blah_updatedate datetime)for update and insert triggers, i can know what to insert into theupdatedby column of audit_blah because it's in a corresponding row inblah. my web app knows what user is accessing the application, andcan insert that name into blah. blah's trigger will then insert thatname into audit_blah.however, in the case of a delete, i'm not passing in an 'updatedby',because i'm deleting. in this situation, how can the trigger knowwhat user is deleting? the db only knows that sql user 'dbUser' isdeleting, but doesn't know that 'dbUser' is deleting on behalf of'DOMAINEric'. is there any way for my app to inform the trigger toaccess my windows identity without having a corresponding row in thetable from which to pull that info?obviously, i could have each of my app's users log into SQL serverthrough Windows authentication; then i could just use SYSTEM_USER.but let's say, for performance's sake, it'd be better for me to useone sql server login. (i believe one user works better for connectionpooling purposes.) is there a way to get around this?(i'm hoping a built-in function exists that solves all my problems.)suggestions? resources?any help would be great appreciated.happy turkeys.Eric
View 2 Replies View RelatedHi everybody,I just wrote my first two triggers and from the minimal amount of testing Ihave done, they work! However, I was hoping I could get some feedback fromthose of you more experienced in writing triggers.Here is the first one:CREATE TRIGGER DecreInters ON InteractionFOR DELETEASdeclare @stu INTdeclare @num INTselect @stu = Student_FK from deletedselect @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num - 1FROM StudentWHERE Student.Student_Key = @stuHere is the second one:CREATE TRIGGER IncreIntersON InteractionAFTER INSERTASdeclare @stu INTdeclare @num INTdeclare @last_rec INTselect @last_rec = @@IDENTITYselect @stu = (select Student_FK from Interaction where Interaction_ID =@last_rec)select @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num + 1FROM StudentWHERE Student.Student_Key = @stuAre there any shortcuts I could use or things I could do to make thesetriggers more efficient. Please give me some feedback and let me know ofany problems that might possibly be caused due to my doing this improperly.Thanks ahead,Corey
View 1 Replies View RelatedI have below sample data table,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
[Code] ....
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Below Query works perfect, when count of child or grand child count less than 10, as SortOrder column here is
NVARCHAR,
;With cte As
(Select t.ItemId, t.ItemName, t.ItemDate, t.ParentItemName, t.ItemOrder, t.ReportId,
Cast(t.ItemOrder As nvarchar(max)) As SortOrder
From @TBL t
Where t.ParentItemName Is Null
[Code] ....
The output rows with ItemOrder's 10, 11, 12 (test grand child 10, test grand child 11, test grand child 12) should display after ItemOrder = 9 (test grand child 9). I know this is happening due to varchar sort order?
Howdie y'all,
Im adding rows to my database like this:
GroupID = IdentityGroupUser = UserIdGroupColumn = 1 GroupPosition = Count (*) where UserId = UserId AND Column = 1
Int this way my table looks like this on a certain moment:
1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 13 ; 1 ; 1 ; 24 ; 1 ; 1 ; 3
But then i would, for example, like to delete the 3rd row and i like to renumber the last column so form this:1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 14 ; 1 ; 1 ; 3 <- this is my problem
to this:1 ; 1 ; 1 ; 02 ; 1 ; 1 ; 14 ; 1 ; 1 ; 2 <- here it is solved
Whats the simplest sql stored procedure to perform this reordering task???
Thanks,
Wes
I have a SqlDataReader object that has some records:
1 hello12 hello23 hello3
Is there a fast/easy way to reorder them?
3 hello32 hello21 hello1
So far I'm able to populate an ajax control toolkit reorder list with an SQL table, but I can't get it to reorder through the database.The SQL table is something like this-int ListID, varchar Title, int SortingOrder (SortingOrder always goes 1-n, there are no duplicates and no gaps) When the user changes the position of an entry, I need the reorder list to update the SortingOrder of all the pages between the starting and ending place of the reordered entry. I already have that function written, ReorderListEntries (int OldSortingOrder, int NewSortingOrder), but how and where do I call it?Basically, when the user reorders the list, I need it to-1. get the old and new positions of the reordered entry.2. pass this information back to the server3. call ReorderListEntries (old,new)4. partial page refresh the listHelp with this one will be greatly appreciated.
View 1 Replies View RelatedHow or is there a way to reorder the packages in BIDS? Right now they are ordered by when they are created with the newest ones on the bottom. I'd like to organize these in more of a logical order but am unsure of how to do so.
Any advise?
Thanks,
Beac
In my report i would have 2 groups.
The first group should cause a real page break, the secound group should cause a column break.
Any idea on how to realize this, i've been playing with quite some settings but .....
So, any help ...
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 Replies View RelatedUse the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
View 4 Replies View Relatedin Table A Column is
PriKey No Name
1 1 Apple
2 2 Orange
3 3 Juicy
in Table B column is
Prikey ColumnA Price
1 1 10
2 3 2
3 2 5
TableA.Prikey have Depency with TableB.ColumnA
when I am trying to Delete data from Table A , I got error message becaue the depency
how to delete data when there have depency?
I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted
does there have a way to disable depency and then enable ?
thank you
hi friends, i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me......
View 3 Replies View RelatedI have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedI have a report looking something like this:
Field 1 Field 3 Field 5
RandomInfo1 RandomInfo1 RandomInfo1
RandomInfo2 RandomInfo2 RandomInfo2
Field 2 Field 4 Field 6
RandomInfo1 RandomInfo1 RandomInfo1
RandomInfo2 RandomInfo2 RandomInfo2
It extends along a fair number of pages, and I'm currently using the "# of columns = 3" in the Report properties --> Layout section in order to make it span over 3 columns instead of the usual 1. I'm using a table in which the random info fields are the repeating values, and the Field 1,2,3,4,etc are included in the list control, but not part of the physical table itself.
My question stems from a couple of chunks of data which basically extend over the course of 1.5 pages (yep. There's enough to fill about 4.5 columns worth.)
I currently have
Field 1 RandomInfo50 RandomInfo100
RandomInfo1 RandomInfo51 RandomInfo101
Randominfo2 RandomInfo52 RandomInfo102
etc.... etc.... etc....
-----NewPage----
RandomInfo150 Field 2
Randominfo151 NewRandomInfo1
RandomInfo152 NewRandomInfo2
etc...
What I'm looking to do is have "Field 1" appear at the top of each column whenever a larger chunk of data forces itself into multiple columns, or even multiple columns on multiple pages. I have this nasty feeling it's something silly and blatantly obvious I'm missing...
Any thoughts?
Hi Every One,
I have a simple matrix like below
Categories
A
Total A
B
Total B
Products
SHOES
$100
$100
$50
$50
SOCKS
$80
$80
$90
$90
How can I add a Columns Group page break for the Group Categegory in Reporting Services 2005 so the first page break takes place after Total A and rest of the data moves to the next page?
Hi,
Can some reporting expert suggest me how to acheive following scenerio?
I have simple long running Table reports having usually more then 500 rows.I want that in excel I should have 100 rows in each page.I can create a group having =Int((RowNumber(Nothing)-1)/100) and enabling page break at the end
But I also want that header to be repeated across all sheets in excel,Can I do that?
-Thanks,
Digs
Hi guys,
I have a matrix report with one row group and one dynamic column group. My issue is that I want to see the column group to be appeared only on the first page of the report not on every next page, because I am going to finally export the report to Excel spreedsheet so I don't want the column to be repeated in the middle of the records.
Let me know if anybody has some idea.
Thanks!
--Amde
Hello,
I wonder how and if this can be achieved:
In a tabular report I have one or more columns that need to be repeated on every new physical page when printed.
Viewing the report in the ReportViewer control allows such columns to be fixed using the "FixedHeader" switch, allowing the user to conveniently scroll the reports content while always having the fixed columns in sight. This is perfect. However, when switching to the Print Layout view or when printing the report, I would like to have these fixed columns be printed on every new page that is generated at the beginning of the table printed.
E.g. I have a report that has a huge number of columns that need to be shown. When printed, the columns need at least 6 pages' width. I would be very convenient if I could repeat e.g. the first column (containing some identificational information) on every of these 6 pages. It wouldn't hurt if e.g. 7 pages would be generated because of the repeated column(s).
Any help is appreciated, thanks a lot!
Frank
I have a multi-column report with a page header that spans the width of the report. Whenever I close the report and reopen it, the header is changed to the width of the column. This isn't a real big deal because I deploy it with the header across the whole page; but once in a blue moon I accidentally hit the F5 key (which I am used to using for refresh in other environments I work in) and the report gets redeployed with the messed up header. Is there anyway to stop RS from automatically adjusting the page header?
And is there anyway to disable the F5 key from deploying the reports. I do use it to build and run windows apps, and don't want to change that, but I don't want to deploy 80+ reports when I accidentially hit it.
Thanks.
This seems to happen when the report is not 8.5 x 11 portrait. Any other size or orientation will cause it to put the column on the last page.
Does anyone know why?
Hey folks,
I have a 2 column report that is not page breaking the way I would like. The report has a list that is grouped by a "Type" field. The header includes this type, though I had to use an aggregate of the report field like:
=Max(ReportItems!PlanType.value) & " Contacts"
I want a brand new page when the type changes, but since RS treats columns like pages, if my type ends in the first column, the new type begins in the second column. Is there any way to force it to leave that column blank and start a brand new page?
Hello people
Can someone tell me if, and then how I can delete/remove a column from a table in SQL6.5. (I know how to add a column)
Regards
De Waal
Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".
i.e.:
rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com
etc like that.
each row varies with one exception. administrator@sthou.com is in each one.
is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?
thanks again for any easy answer
rik
Hi
I have to delete the value of acktitle where ackby = null
I tried using update but its not helping me i tried doing
update incidents(table name)
set acktitle = null where ackby = null
its not giving me any results thats why i thought of delecting but delete syntax removes the whole row
How should i go about it
When I use the PageBreakAtEnd on the table or on a group in the table, all it does is create a new column of the column report.
I'd want it to start a new page, how can I do this ? Should I work around this issue using code ?
Background: What I need to achieve is a report with 2 columns where the list of products in category 1 are listed in the left column and then snake to the 2nd column on the same page, then to column 1 on page 2, column 2 on page 2, etc...
When it comes to category 2, it should start a fresh new page regardless of whether the previous product was rendered in column 1 or column 2.
I get the snaking to work using the "Columns" property of the report Body. However page breaks do not start a new page, they just start a new column.
hi all iam having a query , c guys iam having a table in sql with some coulmns in it , i have a column named as country in the table , now wat i want to be done is , i want to delete the column country based on some conditions , i ve to write a script code as : i ve to check if the column is there already or not if its there it shld delete the column or if its not there it shld not show any error and just return empty handed thats all , i dont know wat to be done , so if anyone knows abt it pls do send as soon as possible guys , hopefully waiting for a reply Note : any dbts pls do mail me again Venkatesan
View 4 Replies View RelatedDear all
I would like to ask one sql question , it's that possible to use statement delete my columns ?
select * from tablename where columna = '--delete from tablename '
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com