How To Control One Unique Record Per Proc Instance??
Oct 19, 2007
Greetings,
I am running a proc (PROC1) that gets one record at a time and does some work against it.
select min(myid) as NextID
from tbl_ListOfThingsToDo
where AlreadyDone is null
Then once I have the MyID, I set the 'AlreadyDone' bit to True so that no one else picks that record up.
update tbl_listofThingsToDo set AlreadyDone=1 where MyID=(from above)
If there is only one PROC1 running, this is no problem. He just keeps grabbing the next record to operate on.
--but--
If I kick off multiple instances of PROC1 for scaleability reasons, how can I make sure that only one instance gets the next record? For example, couldn't the second instance get the same myid BEFORE the first instance has written that he already got it?
Design is to run 3-5 instances of this to get extra cycles.
Generally I install SQL 2005 using the Default Instance Name. When asked for the SQL server (setting up the ODBC connection) I simply enter the name of the server.
I now have installed a new SQL 2005 server, and I gave it a unique Instance Name.
When asked for the SQL server (setting up the ODBC connection) what name do I enter? I assume it has to be more than the server name as the SQL server is unique. What format do I use to enter the unique name of the SQL db?
I HAVE THREE TABLE GEN, PARTY, TXN (GEN & PARRTY IS MASTER TABLE AND TXN IS TRANSACTION TABLE) IN TXN TABLE - STORE ALL DATA IN WHICH SAME VOUCHER NUMBER IS REPEATED ACCORDING TO JOURNAL ENTRY) NOW PROBLEM IS THIS AT THE TIME OF DISPLAY THE RECORD ON SCREEN I HAVE TO SHOW ONLY THE UNIQUE VOUCHER NUMBER ONLY..
PLS GUIDE IN THIS QUERY HOW CAN I GET THAT RESULT.
SELECT DISTINCT TXN.VOUCHER, GEN.GLNAME, PARTY.PARTYNAME, TXN.DOCDATE, TXN.GLCODE, TXN.PARTYCODE, TXN.AMOUNT, TXN.VTYPE, TXN.CBCODE, TXN.ADJ, TXN.DC, TXN.NARR FROM (TXN LEFT JOIN GEN ON GEN.GLCODE=TXN.GLCODE) LEFT JOIN PARTY ON PARTY.PARTYCODE=TXN.PARTYCODE WHERE TXN.VTYPE='JV' ORDER BY TXN.VOUCHER
I am trying to make a where clause using the sql server unique record id that is set automatically when a record is inserted. It is the record id that is unique for one table. In a where clause. What type should go where the _______ is below? Here is the program <%@ page language="vb" %><%@ reference page="try7.aspx" %><% @import namespace="system.data"%><% @import namespace="system.data.sqlclient"%> <html> <head> <title> </title> <script language="vb" runat="server"> sub page_load(sender as object,e as eventargs) response.write(request.queryString("a")) response.write(",") response.write(request.queryString("b")) get1.text=request.queryString("b") end sub sub star(sender as object,e as eventargs) dim connection1 as new sqlconnection("") const q1 as string="update tbltest set code=@code where dxid=@dxid" dim command1 as sqlcommand=new sqlcommand(q1,connection1) command1.parameters.add("@code",sqldbtype.varchar).value=get1.text command1.parameters.add("@dxid",sqldbtype._________).value=request.queryString("a") command1.connection.open() command1.executenonquery() command1.connection.close() end sub </script> </head> <body> <form runat="server"> <asp:textbox id="get1" columns="6" maxlength="6" runat="server" /> <asp:button id="buttonsave" text="click" onclick="star" runat="server" /> </form> </body></html>
I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)
What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.
So that I don't have duplicate candidate ids and i have single row with all column data.
I have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO
I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:
EXEC ServerInstance.DB.dbo.usp_fm_proc 1
It seems it doesn't like the as I get an error "Incorrect syntax near 'Instance'.
I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.
Tables include:-
1. REQ 2. RFQ 3. PO 4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO) 5. DOCUMENT_STATUS (contains status of document, REQ_CREATE) 6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT) 7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)
Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.
I realised the multiple lines generated due to the following:-
One to many relationships: A. RFQ - 1 or more PO B. PO - 1 or more PO_REVISON
I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
Hello, I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2 While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index. Contents RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description..... Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database. Any advice, thoughts or comments would be helpful. Thanks,P.
Hello Hello, This Noob has a question. I have been atempting for days to figure this out. I have this set up: (See Below)
The Problem I am having is occuring at the Table named OU in Maroon below. It combines 2 chains of keys into one table to try and make a unique record. The problem is that it is not.
Pasted below is a Query run on the data within the tables: As you can see certain data is getting duplicated in this tabel. I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.
Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.
Table:zCombined PK: zCombinedID FK: TempID ~ To Temp Table FK: ShipTypeID ~ To Ship Type Table FK: CoordinatorID ~ To Coordinator Table FK: BoxTypeID ~ To Box Type Table
can someone pls show me a way to get an unique sequence at below senario:
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 SeqNo Invoice 100 DeliveryOrder 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?
Greetings all, I am tring to capture the ID of a newly inserted record from a form to a label that I will reference in a reciept page. I intend to pass the rowid to retrieve record information on other pages. The insert suceeds... I just need to capture the auto generated ID for the new row to a label on the page post onclick. Any thoughts?
Dim MySQL As String = "Insert into dropkick (name, status, payroll, unit, contactnumber, email, equipment, issue, timein) values (@name, @status, @payroll, @unit, @contactnumber, @email, @equipment, @issue, @timein)"Dim myConn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString) Dim Cmd As New SqlCommand(MySQL, myConn)Cmd.Parameters.Add(New SqlParameter("@payroll", txt_payroll.Text)) Cmd.Parameters.Add(New SqlParameter("@name", txt_name.Text))Cmd.Parameters.Add(New SqlParameter("@status", "W")) Cmd.Parameters.Add(New SqlParameter("@unit", txt_dept.Text))Cmd.Parameters.Add(New SqlParameter("@contactnumber", txt_cell.Text)) Cmd.Parameters.Add(New SqlParameter("@email", txt_email.Text))Cmd.Parameters.Add(New SqlParameter("@equipment", txt_equipment.Text)) Cmd.Parameters.Add(New SqlParameter("@issue", txt_issue.Text))Cmd.Parameters.Add(New SqlParameter("@timein", lbl_datetime.Text)) myConn.Open() Cmd.ExecuteNonQuery() Label1.Visible = "true" Page.DataBind() myConn.Close() Label1.Text = "Your data has been received!" ''LABEL TO BE POPULATED WITH ID OF NEW RECORD lbl_id.Text = ID
Id StdId TeacherName Day subject 1 1 Archana Monday English 2 1 Archana Tue Marathi 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi 6 2 Archana Mon english 7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid 1 Shalini 1 2 Monika 1 3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject from StudentTeacherRelation inner join Student Student.id = StudentTeacherRelation.StuId where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show Id StdId TeacherName Day subject where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.
What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
I'm sure this is an easy problem but my brain is fried today...however how do I do the following:
I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:
OK, how do I get the top 1 of each key so that I get a subset of records looking like the following: 1231999-06-14 12:17:11.000 8901999-06-15 10:00:18.000
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
I need to call the stored procedure below. Basically what I need to know is if the query returns a record? Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query. GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you. Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column). Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs. Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC). At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS??? Does an error raise? Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.
For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table
SALES LINE ARCHIVE TABLE Version No - Order No. - Customer No -----> (other columns) 1 s-5 1000
RESULTS OF JOINED TABLES Version No - Order No - Customer No - Customer Name ---> (other columns) 2 s-5 1000 Something, Inc. 1 s-6 2000 Acme 3 s-7 3000 Company, LLC 1 s-8 4000 Blah & Associates 2 s-9 2000 Acme
It should return the last Version No of each Sales order.
Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
Hi All, I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
