Stored Procedure Inserting Duplicate Records Randomly
I have a web app that calculates tax filing status and then stores data about the person.
Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()
And that is it.
View Complete Forum Thread with Replies
Related Forum Messages:
Stored Procedure Producing Duplicate Records
Hi, I have written the following stored procedure: alter proc GetProducts @prodcatint=null as select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name] ,p.designer,p.weight,p.price from productdescription pd inner join products p on pd.productcategory=p.productcategory where @prodcat=p.productcategory order by p.productid return My Results are: ProductCategory ProductID (Rest of the columns) 22 47 22 47 22 58 22 58 In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4. Does anybody have any suggestions. Thank you in advance, poc1010
View Replies !
Inserting Records Via Stored Procedure
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails... here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder" === Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim conn As SqlConnection = Nothing Dim trans As SqlTransaction = Nothing Dim cmd As SqlCommand conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString) conn.Open() trans = conn.BeginTransaction cmd = New SqlCommand() cmd.Connection = conn cmd.Transaction = trans cmd.CommandText = "usp_InserOrder" cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int) cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit) cmd.Parameters("@MaterialID").Value = 3 cmd.ExecuteNonQuery() trans.Commit() ===== I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2. Please help, I am a newb and lost...as you can tell from my code...
View Replies !
Inserting Of Duplicate Records Error Message
I would like to know what options I have with regards to trapping a duplicate record before it tries to post to a SQL database. I have set the column to unique in SQL. But when I try to use ASP and post a duplicate record I get a system error. I would like to just create a referential error to notify the user that they cannot post a duplicate record please try again. Any help would be greatly appreciated. RT
View Replies !
Slow Procedure While Checking Duplicate Records
hello friends i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can... the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country.... i m using SQL server 7.0 i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries... for this i m generating another table(name of another table is RESULT)... how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....) now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only *** similarly when it checks the 100th record it compares it for 99 times *** similarly when it checks the 10000th record it compares it for 9999 times so here lies the problem.... when it checks the 100000th record it gets dammm slow... what i have get till now is that i have checked.....>>>>> 5000 records in 4 mins.... 25000 records in 22 mins.... and 100000 records in 20 hours....(means initially its faster but it gradually slows down) ************************************************** ************************ here i m giving the code for the procedure...... ************************************************** ************************* CREATE PROCEDURE pro1 as SET NOCOUNT ON Declare @IvgId as numeric(15) Declare @Address as nvarchar(250) Declare @AddressClean as nvarchar(250) Declare @MaxLen as INT Declare @Add as nvarchar(250) Declare @Ic as int Declare @FoundIvgId as numeric(15) Declare @NewIvgId as numeric(15) /* here 'N' is for keeping track for some system failures etc */ Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N' OPEN CurData FETCH NEXT FROM CurData INTO @IvgId, @Address WHILE @@FETCH_STATUS = 0 Begin /*here i m doing string cleaning by removing special characcters */ Select @MaxLen = len(LTRIM(RTRIM(@Address))) Select @Address = LOWER(@Address) Select @Ic = 1 Select @AddressClean = ' ' While @Ic <= @MaxLen /* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */ Begin Select @Add = Substring(@Address, @Ic, 1) If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End If ascii(@Add) > 90 AND @Add <> ' ' Begin Select @AddressClean = @AddressClean + @Add End Select @Ic = @Ic + 1 End /* now we have removed special characters , for failure checking i m using this 'Y' */ Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address End PRINT 'Cleaning Done.............................' Close CurData Deallocate CurData /* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */ Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N' OPEN CurData FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean Select @NewIvgId = 100 WHILE @@FETCH_STATUS = 0 Begin If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)) Begin Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20) End ELSE Begin Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0) Select @NewIvgId = @NewIvgId + 1 End Update Data set ProcessDup = 'Y' Where IvgId = @IvgId FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean End Close CurData Deallocate CurData SET NOCOUNT OFF Print 'Done................................' ************************************************** ************************** now the procedure is over....now i m writing the SQL script of DATA & RESULT table ************************************************** ************************ CREATE TABLE [dbo].[DATA] ( [IVGID] [numeric](18, 0) NOT NULL , [Title] [varchar] (10) NULL , [FirstName] [varchar] (50) NULL , [MiddleName] [varchar] (10) NULL , [LastName] [varchar] (30) NULL , [Add1] [varchar] (150) NULL , [Add2] [varchar] (50) NULL , [Add3] [varchar] (50) NULL , [City] [varchar] (30) NULL , [State] [varchar] (30) NULL , [Country] [varchar] (20) NULL , [Pincode] [varchar] (10) NULL , [OffPhone] [varchar] (20) NULL , [OffFax] [varchar] (20) NULL , [ResPhone] [varchar] (20) NULL , [ResFax] [varchar] (20) NULL , [EMail] [varchar] (50) NULL , [Source] [varchar] (20) NULL , [MODEL] [varchar] (20) NULL , [PNCD] [varchar] (6) NULL , [DupKey] [decimal](18, 0) NULL , [Duplicate] [int] NULL , [HouseHoldID] [varchar] (50) NULL , [YearSlab] [varchar] (10) NULL , [CleanStatus] [int] NULL , [AddStatus] [int] NULL , [BatchNo] [varchar] (20) NULL , [ModelStatus] [int] NULL , [Month] [int] NULL , [Year] [int] NULL , [SapStatus] [int] NULL , [ErrCase] [int] NULL , [cmpCity] [varchar] (50) NULL , [Product] [varchar] (1) NULL , [cmpPinCode] [varchar] (6) NULL , [Address] [nvarchar] (250) NULL , [AddressClean] [nvarchar] (250) NULL , [DupIvgId] [numeric](18, 0) NULL , [ProcessClean] [nvarchar] (1) NULL , [ProcessDup] [nvarchar] (1) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/ CREATE TABLE [dbo].[DATA_TEST] ( [IVGID] [numeric](18, 0) NOT NULL , [Address] [nvarchar] (50) NULL , [AddressClean] [nvarchar] (50) NULL , [DupIvgId] [numeric](18, 0) NULL , [ProcessClean] [nvarchar] (1) NULL , [ProcessDup] [nvarchar] (1) NULL ) ON [PRIMARY] GO so now i have given the whole description of my problem....i m eagerly waiting for reply...... if anybody can help....i will be very thankful..... bye for now Bhupinder singh
View Replies !
Selecting Records Randomly With SQL
I'm looking for a bit of SQL code that will select some entries randomly from an SQL table. For instance I'd like to feed a parameter in that contains the number 20, and the returned record contains 20 randomly and distinct selected records. Anyone know how this can be done? (never came across randomly select records) Appreciate any help
View Replies !
Randomly Pick Records
I need to randomly pick one or more records from a query e.g select c_id, c_name from c_table where cat_id = 52 There may be more than one records for cat_id = 52. and I need to pick 3 of them randomly. Thanks in advance!
View Replies !
Selectings Records Randomly From A Quiz Database
Hi i have created a quiz software in VB. i have used ini files to fetch questions.. now i have thought of changing it to SQL.. i have created a table with questionno, question, option1,option2,option3,option4 and the correctanswer.. now i want to select the questions randomly each time the question session is started.. please help me out as i need to complete this project for my school...
View Replies !
How To Randomly Select Records From Sql Server 2000 To ASP.NET(vb Language)
Hai friends,, I have a table name "Student" it contain 2 fields no ,name no name 1 Raja 2 Larsen 3 Ravi 4 Ankit 5 Eban my questions is I have a webform random.aspx whenever any user open a webform random.aspx it should display anyone of name in a random order..... CODING:- Dim cn As New System.Data.sqlclient.SqlConnectionDim rd As sqlDataReader cn.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=master;password=david;" cn.Open()Dim cmd1 As New SqlCommand("select no,name from Student", cn) ' how i can chage to random order.... rd = cmd1.ExecuteReader rd.Read() Response.Write(rd(0) & "." & rd(1)) rd.Close() thank u.. Ambrose...
View Replies !
Stored Procedure Retrieving Duplicate Data
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance Pmillio Jones Pmillio Jones Pmillio Jones Robert Walsh Here is my stored procedure; ALTER PROCEDURE [dbo].[stream_UserFind] -- Add the parameters for the stored procedure here @userName varchar(100), @subCategoryID INT,@regionID INT AS SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName, Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON Users.userID= UserSubCategories.userIDINNER JOIN SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
View Replies !
Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String 'Get the users IP address strIPAddress = Request.UserHostAddress Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress Try con.Open() cmd.ExecuteNonQuery() Finally con.Close() End Try End Sub
View Replies !
Inserting Into Tables Using Stored Procedure
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table. When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure. However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work. Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
View Replies !
About Inserting Some Parameters To A Stored Procedure.
Hello everyone, I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance Errors 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType' 'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand' protected void Button1_Click(object sender, EventArgs e) { /* These two variables get the values of the textbox (i.e user input) and assign two local * variables, This is also a good strategy against any Sql Injection Attacks. * */ string Interview1 = TextBox1.Text; string Interview2 = TextBox2.Text; string Interview3 = TextBox3.Text; string ProdMentioned = TextBox4.Text; string ProdSeen = TextBox5.Text; string Summary = TextBox6.Text; string Compere = TextBox7.Text; string Duration = TextBox8.Text; //Create Sql connection variable that call the connection string SqlConnection SqlConnection = new SqlConnection(GetConnectionString()); //Create a sql command to excute SQL statement against SQL server SqlCommand Command = new SqlCommand(); // Set the command type as one that calls a Stored Procedure. Command.InsertCommandType = CommandType.StoredProcedure; //Call the stored procedure so we can pass it on the user input to retrieve user details Command.InsertCommand = "Summaries"; //open the command connection with the connection string Command.Connection = SqlConnection; // Pass the user input to the Stored Procedure to check if user exists in our system. Command.InsertParameters.Add("interview1", interview1); Command.InsertParameters.Add("interview2", interview2); Command.InsertParameters.Add("interview3", interview3); Command.InsertParameters.Add("ProdMentioned", ProdMentioned); Command.InsertParameters.Add("ProdSeen", ProdSeen); Command.InsertParameters.Add("Compere", Compere); Command.InsertParameters.Add("Duration", Duration); int rowsAffected = 0; try { rowsAffected = Command.Insert(); } catch (Exception ex) { Resonse.Redirect("InsertSuccessfull.aspx"); } // open the connection with the command //Command.Connection.Open(); } private static string GetConnectionString() { return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString; } }
View Replies !
Inserting BLOB Using Stored Procedure
Hi, Can we insert a blob in the database(eg: doc, jpeg, pdf, etc) from the sqlcmd prompt. I want to insert few files into my table having varbinary(max) column and i dont want to use any front end tool for making such insertions. What i am thinking of is providing a file system path for a particular file(eg: doc, jpeg, pdf, etc) to a stored procedure so that it can be inserted into the database, something that we can do via Oracle's sqlldr tool. Regards Salil
View Replies !
Inserting Request.Querystring Into The Stored Procedure
Hi i am trying to insert the value of my Request.Querystring into my stored procedure, but i am having trouble with it, how would i insert the id as a parameter which is expected from the stored procedure this is what i have doen so far;string strID = Request.QueryString["id"]; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand comm = new SqlCommand("stream_PersonnelDetails", conn);comm.CommandType = CommandType.StoredProcedure; conn.Open();SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection); DataList1.DataSource = reader; DataList1.DataBind(); conn.Close(); Thank you
View Replies !
Regarding Stored Procedure For Selecting A Value From One Table And Inserting It To Another
Hi iam Prameela, I want to select some dynamic values from a table and store them to another table. Let me give u an example,its like: I have UID,QID,Option1,Option2,Survey Name in one table called Survey Answers and i must select these values and insert them into Surevy Count table which contains some fields as QID,Opt1Cnt,Opt2Cnt,Survey Name. this is an online survey and when ever an user participate in the survey then values will be changed in Survey Answers like: Surevy Answers Table: UID QID Option1 Option2 Survey Name---------These are the fields 1 1 1 0 Articles 1 2 0 1 Articles 2 1 1 0 Articles 2 2 0 1 articles I need to add all these Options of particular QID and store them in Survey Count table,like QID Opt1Cnt Opt2Cnt Survey Name 1 2 0 Articles 2 0 2 Articles When ever the user participate in survey then there will be change in Survey answers table i.e the option count will be increased So this count should be modified in Survey Count Table,like: If another user participated in survey and if he voted for Option1 of QID1,Option1 of QID2 then the survey count table should be modified as: QID Opt1Cnt Opt2Cnt Survey Name 1 3 0 Articles 2 1 2 Articles I need a Stored Procedure for this. Please help me with this query.
View Replies !
Can Anybody Tell Me Why The Following Stored Procedure Is Not Inserting Into My Database Table?....
ALTER PROCEDURE AddListAndReturnNewIDValue ( @EditorId int,@CategoryID int, @ListTitle nvarchar(50),@Blurb nvarchar(250), @FileName nvarchar(50),@ByLine nvarchar(50), @HTMLCopy nvarchar(MAX),@MainStory bit, @MainStoryImageFile nvarchar(50),@Publish bit, @PublishDate smalldatetime, @ListId int OUTPUT ) AS -- Insert the record into the database INSERT INTO shortlist (EditorId,CategoryID,ListTitle,Blurb,FileName,ByLine,HTMLCopy,MainStory,MainStoryImageFile,Publish,PublishDate) VALUES (@EditorID,@CategoryID,@ListTitle,@Blurb, @FileName, @ByLine, @HTMLCopy, @MainStory, @MainStoryImageFile,@Publish,@PublishDate) -- Read the just-inserted ProductID into @NewProductID SET @ListId = SCOPE_IDENTITY() here is the sqlDataSource <asp:SqlDataSource id="srcShortList" ConnectionString="<%$ ConnectionStrings:ShortList %>" SelectCommand="SELECT Id,EditorId,CategoryID,ListTitle,Blurb,FileName, ByLine, HTMLCopy, MainStory, MainStoryImageFile, Publish,PublishDate,Date,Deleted FROM shortlist" InsertCommand="AddProductAndReturnNewProductIDValue"SelectCommandType="StoredProcedure" UpdateCommand="UPDATE shortlist SET CategoryID=@CategoryID,ListTitle=@ListTitle,Blurb=@Blurb,ByLine=@ByLine,HTMLCopy=@HTMLCopy,MainStory=@MainStory,Publish=@Publish,PublishDate=@PublishDate WHERE Id=@Id" Runat="server" > <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </SelectParameters> </asp:SqlDataSource>
View Replies !
Inserting Simultaneous Tables Using Stored Procedure
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below. I made up the two tables just for testing, they are: tbltest ECID – int (PK)
View Replies !
Inserting A Record Using Values From Another Stored Procedure
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
View Replies !
Inserting 1:M Relationship Data Via One Stored Procedure
Hi, Uses: SQL Server 2000, ASP.NET 1.1; I've the following tables which has a 1:M relationship within them: Contact(ContactID, LastName, FirstName, Address, Email, Fax) ContactTelephone(ContactID, TelephoneNos) I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields: LastName, FirstName, Address, Fax, Email, MobileNo, PhoneNo1, PhoneNo2, PhoneNo3. Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How? Eagerly awaiting a response. Thanks,
View Replies !
Stored Procedure Format Incorrect - Inserting To Two Tables
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements
Hi can anyone help me with the format of my stored procedure below. I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true. At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2 @publicationID Int=null,@typeID smallint=null, @title nvarchar(MAX)=null,@authorID smallint=null AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ERROR Int --Create a new publication entry INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) --Obtain the ID of the created publication SET @publicationID = @@IDENTITY SET @ERROR = @@ERROR --Create new entry in linking table PublicationAuthors INSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SET @ERROR = @@ERROR IF (@ERROR<>0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
View Replies !
Using Stored Procedure Records
How can I use the data returned from a stored procedure within another stored procedure? For example, I trying to something along these lines: select * from tbl_Test union all select * from (exec sp_Test)
View Replies !
Counting Records In A Stored Procedure
I am trying to count records in my stored procedure. Can someone please help me. these are the two procedures I am using Alter Procedure usp_rptQualityReport As SELECT tblRMAData.RMANumber, tblRMAData.JobName, tblRMAData.Date, tblFailureReasons.LintItemID, tblLineItems.Qty, tblLineItems.Model, tblLineItems.ReportDate, tblFailureReasons.FailureReason, tblTestComponentFailures.ComponentID, tblTestComponentFailures.FailureCause FROM tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber WHERE (((tblFailureReasons.FailureReason) <> N'NONE')) ORDER BY tblFailureReasons.FailureReason Alter Procedure usp_rptQualityReport2 As exec usp_rtpQualityReport SELECT usp_rptQualityReport.RMANumber, usp_rptQualityReport.JobName, usp_rptQualityReport.Date, usp_rptQualityReport.LintItemID, usp_rptQualityReport.Qty, usp_rptQualityReport.Model, usp_rptQualityReport.ReportDate, usp_rptQualityReport.FailureReason, usp_rptQualityReport.ComponentID, usp_rptQualityReport.FailureCause, (SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel FROM usp_rptQualityReport;
View Replies !
Get Records After Executing A Stored Procedure
Hi All, I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters. EXEC [dbo].[ETLloadGROUPS] @countRun =?, @startTime =?, @endTime = ? This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped. When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine. Am I missing something here ? Please Advice Thanks in Advance
View Replies !
Stored Procedure For Archiving Records
I have two tables called A and B and C. Where A and C has the same schema A contains the following columns and values ------------------------------------------- TaskId PoId Podate Approved 1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes Table B contains the following columns and values ------------------------------------------------- TaskId TableName Fromdate Approved_Status 1 A 7/7/2007 No 3 B 2/4/2006 Yes Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column Pls provide me with full stored procedure code. C.R.P RAJAN
View Replies !
Inserting Duplicate Data
This is probably a silly question to most of you, but I'm in the processof splitting off years from a large DB to several smaller ones. Some ofthe existing smaller DBs already have most of the data for theirrespective years. But some of the same data is also on the source DB.If I simply do an insert keying on the year column, and a row beinginserted from the source DB already exists in the target DB, will aduplicate row be created?And if so, how can I avoid that?Thanks,John Steen*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Stored Procedure For Archiving The Records In Another Table
I have two tables called A and B and C. Where A and C has the same schema A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved 1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status 1 A 7/7/2007 No3 B 2/4/2006 Yes Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column Pls provide me with full stored procedure code.
View Replies !
Count Records When RecordSource Is A Stored Procedure
I have a stored procedure named mySP that looks basically like this:Select Field1, Field2From tblMyTableWhere Field 3 = 'xyz'What I do is to populate an Access form:DoCmd.Openform "frmMyFormName"Forms!myFormName.RecordSource = "mySP"What I want to do in VBA is to open frmContinuous(a datasheet form) ifmySP returns more than one record or open frmDetail if mySP returnsonly one record.I'm stumped as to how to accomplish this, without running mySP twice:once to count it and once to use it as a recordsource.Thanks,lq
View Replies !
Calling A Stored Procedure On Multiple Records
I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made: CREATE PROCEDURE dbo.GetUserStats @UserID BIGINT AS DECLARE @TempTable TABLE ( UserID BIGINT, UserName VARCHAR(60), TotalAmt FLOAT ) INSERT INTO @TempTable (UserID, UserName, TotalAmt) SELECT u.RecID, u.LastName + ', ' + u.FirstName, (SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @UserID) FROM Users u WHERE u.RecID = @UserID SELECT * FROM @TempTable GO So if I execute this amount entering a single ID, it returns a single row for that user: UserID UserName TotalAmt -------------------------- 1 Doe, John 100.00 What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users: UserID UserName TotalAmt -------------------------- 1 Doe, John 100.00 2 Smith, Bob 123.45 3 Blow, Joe 150.55 Is there a way to re-use a stored procedure within another, based on the results of a query?
View Replies !
SQL Stored Procedure Not Returning Expected Records.
I am running a SP using this code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly] ( @postcode nvarchar(50) ) AS SET NOCOUNT ON; SELECT Cust_Address.Cust_Address_ID, Cust_Address.Cust_Address_1, Cust_Address.Cust_Address_2, Cust_Address.Cust_Address_Post_Code, Cust_Address.Cust_Post_Town, Post_Town.Post_Town_ID, Post_Town.Post_Town_Name, Post_Town.Post_Town_Priority FROM Cust_Address INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID WHERE Cust_Address.Cust_Post_Town LIKE @postcode The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all. Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine. Cheers Matt
View Replies !
Can I Insert Records Into A Table From A Stored Procedure
I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset. I use the logic in other sprocs too. Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like.... INSERT INTO Insurance (Name) Exec sp_AddNamesList Thanks, Mike
View Replies !
Stored Procedure Does Not Return Records Error In VB
I am using SQL Server 2005 std edition SP2 on a Windows 2003 server. I have created a simple stored procedure that deletes all records from two tables: BEGIN SET NOCOUNT ON DELETE FROM dbo.table1 DELETE FROM dbo.table2 END Executing the procedure generates the message "The stored procedure executed successfully but did not return records." which produces an error condition when run from an Access 2007 VB module using the DoCMD function: On Error GoTo ErrorExit DoCmd.SetWarnings False DoCmd.OpenStoredProcedure "dbo.myStoredProcedure" When the above VB code is run (it's part of an Access 2007 adp project connected to the SQL Server database) it takes the error exit and returns the "... did not return records." message. How can I avoid this?? Thanks, Paul
View Replies !
How Can I Prevent From Inserting Duplicate Data?
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue? Thanks.
View Replies !
Stop Inserting Duplicate Entries
Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis. INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD) (SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B)) When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax. Can anybody help me out?
View Replies !
Limit The Number Of Records Returned In Stored Procedure.
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks. Query SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
View Replies !
SQLdatasource Wired To A Stored Procedure Not Returning Records.
Hi, and thanks in advance. I have VWD 2005 Express and SQL 2005 Express running. I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting... <asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" /> I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded. Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle. Any help would be appreciated! <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString %>" ProviderName="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString.ProviderName %>" SelectCommand="spDisplayServiceOrders" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" EnableCaching="True" cacheduration="300" OnSelected="SqlDataSource1_Selected"> <SelectParameters> <asp:ControlParameter ControlID="RadioButtonList1" ConvertEmptyStringToNull="False" DefaultValue="2005-11-1" Name="SDate_Entered" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> ALTER PROCEDURE [dbo].[spDisplayServiceOrders] ( @SDate_Entered SmallDateTime, @SClosed nvarchar(50)= NULL ) AS If @SClosed IS NULL BEGIN SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered Order by SDate_Entered DESC END ELSE BEGIN SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID and SDate_Entered >= @SDate_Entered and SClosed = @SClosed Order by SDate_Entered DESC END
View Replies !
Create A String Of Records From A Table In A Stored Procedure,
I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View Replies !
How To Update Group Of Records In SQL Statement Or Stored Procedure
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count. How could I write this in a stored procedure or a SQL statement if possible? Thanks. Kevin SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1 FROM dbo.ReportTable INNER JOIN dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget HAVING (dbo.ReportTable.Budget < 1) ORDER BY dbo.ReportTable.ProjectNo ProjectNo Category Type Count Budget BudgetTotal 100143 Travel Travel, Meals, No Report IRS 2 0 300.27 100146 Travel Travel Costs, Training (all) 1 0 300.27 100164 Supplies & Materials Supplies, Educational 1 0 300.27 100167 Equipment Eq NonCapital Desktop Comp 1 0 300.27 100170 Faculty Salaries FB, Faculty 11 0 300.27 100170 Faculty Salaries Salary, Faculty, T&R FT 11 0 300.27 100170 Wages Wages, Student 2 0 300.27 100171 Faculty Salaries FB, Faculty 19 0 300.27 100171 Faculty Salaries Salary, Faculty, T&R FT 19 0 300.27 100176 Scholarships & Fellowships Fell, Assist, Out, Grad 1 0 300.27 100177 Scholarships & Fellowships Fell, Assist, In, Grad 1 0 300.27
View Replies !
Executing A Stored Procedure As Times As The View Has Records
i have a stored procedure with one coming id parameter Code BlockALTER PROCEDURE [dbo].[sp_1] @session_id int ... and a view that holds these @session_id s to be sent to the stored procedure. how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.
View Replies !
Searching A Column For A Value To Avoid Inserting A Duplicate Value
Hi there, newbie here. I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server. I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them. Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter. Here's what I want to do: I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created. Here's where I'm up to: I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control. Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along? Cheers!
View Replies !
Insertion And Deletion Of Records Inside A Single Stored Procedure
Hi, I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days. Thanx in Advance
View Replies !
|
TRACKER
