I need a bit of generic advice about a project I am currently working on which is slightly out of my comfort zone. I€™m using SQL 2005 and VB.Net to develop an application that produces invoices for a number of different countries. The issue is that the data is arriving from an AS400 system so the tables I am working from are a little strange! This results in a fair degree of work tweaking the data into a format that is useful for my application.

To simplify the explanation each invoice in its basic form consists of the invoice data and the line items (so there is nothing new here). What is unusual is that the invoice data has an invoice number but then an additional invoice number specific to the country it is destined for. The initial invoice number arrives with the data from the AS400 but the secondary number is generated according to a number of business rules.

I have written an update query in a stored procedure which updates the invoice data with a number of values from a secondary table and a number of calculated values. There are several functions, one in particular which looks up the new invoice number from a secondary table based on specific criteria (country of destination being one of them) and I had intended to write an update query, called from within this function which then increments the invoice number but I have since found out that you cant do an update/delete from a function?

I can€™t add an update query to increment the value after the initial update query as this results in the stored procedure running through and adding all the invoice numbers (which are the same) and then the number being incremented at the end.

I assume I can€™t call an update query from within the initial update query so that the increment takes place after each loop of the initial update (if I can what would the syntax be?).

I have tried writing a trigger which completes the increment when the invoice data is updated. I haven€™t used triggers before but once again it appears as if the first update query is running through and completing all the updates and then the trigger fires incrementing the number. Or have I just got this wrong?

I guess what I need is a trigger which fires after each row of the table is updated which was how I assumed it should work!

Ironically I could write this in VB.Net with ease but for speed we need it within the database.

What is the best way to increment my invoice number?

I€™m sorry of this is general in nature but im really looking for some advice as to the best approach to deal with this issue.

I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.

The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.

For ExampleAnytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).

Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..

Row Sequence Number
Hello all,I´m currently using a SQL Serve 2K. Would like to do a selectwhich returns the row number - this should not be physically stored inthe database. So for example, I would like to do a query against theCUSTOMER table and receive:* rowID || name1 Evander2 Ron3 Scoth4 JaneI don´t want to store the ID, because if I change the order byclause, the sequence may modifiy, and, for another example, having thesame set of data, I would receive:* rowID || name1 Scoth2 Ron3 Jane4 Evander could someone help me ?best regards,Evandro

Log Sequence Number
Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ?10x,Clara

Create Trigger To Check Values At Insert/update
I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

Unique Number/sequence
Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton

The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.

Generating Sequence Number....
Hi, I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1----1111223333345667

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........

I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.

I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.

Sequence Number Generation
Hi,I have an error table that is to be updated by more than one package.There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table.When more than one package runs parallely, conflict occurs in generating the sequence number.How can this be handled?

How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :

Auto Number Sequence
How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

Creating Alphanumberic Sequence Number
Database in:SqlServer builtin interface with VS05Using:ASP.NET VISUAL STUDIO 2005I want to create a unique ID that identifies a row.Id:STU+(SeqNumber) for Student andId:EMP+(SeqNumber) for Employee[Question]:But how can i create sequence number and store it for use like that of ORACLE

Current Idea:Based on registration info if type is student generate STU+seq num and insert to table along with username and other details and if employee like wise.So a procedure will receive this from user and append that with seqnumber and stores that in the table

Chalenge Returning The First Available Number In A Sequence
Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367

I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:

I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.

I have a dataset with 3 fields:

AreaIDRefNo

This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.

The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.

The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.

These numbers I create have to follow on from the highest RefNo for the Area.

Generate Sequence Number Basing On Each Id
Hi all anbody can help me writing sql code for this. All i need is to generate sequence basing on id_noEx: if ID=ABC(twice) in seq_col as abc --1abc ---2Tables which I have Uniques_No ID_NO SEQ--------------------------------------------- 1 ABC 2 ABC 3 ABC 4 BBC 5 BBC

Update Table - Insert New Column
This should be easy for someone, but I just can't seem to find a sample to do this.....I have created a table...CREATE TABLE dbo.test ( oId int NOT NULL UNIQUE, test1 varchar(50) NOT NULL PRIMARY KEY )Now, I need to go back and simply add another column to the table such as test2 varchar(50)Not sure if the insert is the way to go and been playing around with various statements but with no luck.Suggestions?Thanks all,Zath

Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Sequence Number In A Year Without Auxiliary Table
Hi,I'vethis problem. I've a table to manage spedition with an identity column(id), a year field and a numberInYear field. The identity is the pk ofthe table and I want to make the numberInYear growing sequentially andrestart every change year. I don't want to make a table to manage thissequence.I thought this alghoritm:1) Extract the max identity value (id) of the year before this, before the insert of the new record;2) Insert the new record;3) NumberInYear = the id just inserted minus the max id extract before.Theproblem is the NumberInYear cannot be null, so I've to know the id ofthe record inserting before the inserting itself... Is it possible?

PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.In the SERVER1, there is a table SEQUENCE in a database DATABASE1.And the table's structure of SEQUENCE are SeqType & SeqNo.Here is the sample data:

SeqType SeqNoInvoice 100DeliveryOrder 200

Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?

I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but...

How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value?

For instance:New row...

Table1, Column1 (PK) has a value of 101Table1, Column2 has a value of 'Blue'.

// When a new row is created in Table1 and Column2 has a value of 'Blue'...I want to insert a new row into Table2 - with Table1 Column1's value.

// Now if Table1, Column2 has a value of 'Red' when the new row was created...I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2

This has to be inserted into one or the other tables based on column2's value, not both.Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value.

This (the conditional part above) has to work with an update to Table1 also.So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa.

Insert Or Update Into A Image Column Causes Data To Double In Size
We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated