Hi,
I need to make an insert into two tables connected to each other via foreign and primary key. The point is - I cannot use stored procedures or triggers. That tables looks like this: Candidate(ID, Name, Surname, Class) and Candidate_Exam(ID, date). ID in Candidate_Exam is a foreign key. When you insert a new record into Candidate, another one need to be inserted into Candidate_Exam with the same ID. Can anyone help? It is supposed to be an easy assigment so maybe I just have a bad approach.
Regards,
N.
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using: USE [DBName] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[AddToProject] (@DeptCode varchar(20), @ProjectTitle varchar(300), @ProjectDetails varchar(3000), @ProjectManagerID int, @RequestedBy varchar(100), @DateRequested datetime, @DueDate datetime, @ProjectStatusID int) AS BEGIN TRANSACTION SET NOCOUNT ON
DECLARE @Dept varchar(50), @ProjID varchar(50)
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
GO I get the following errors: Msg 128, Level 15, State 1, Line 1The name "BAT" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@ProjID".Msg 515, Level 16, State 2, Procedure AddToProject, Line 17Cannot insert the value NULL into column 'ProjID', table 'NYCEDC.dbo.tbl_Project'; column does not allow nulls. INSERT fails. If I run just the GetProjID sp, I get the following error: Must declare the scalar variable "@ProjID". -------------------------------------What I'm trying to do is, get a bunch of values from the aspx page, insert the DeptCode into the field DeptCode of the table 'tbl_ProjectNumber'+DeptCode so that the IDENTITY value field 'ProjectNumber'+DeptCode can increment by 1. After 'ProjectNumber'+DeptCode has incremented, I want to run the GetProjID sp so that I can insert ProjID into the 'tbl_Project' table along with the other values taken from the aspx page. ProjID is the primary key for the 'tbl_Project' table so it needs to be added along with the other values (other they obviously can't be added). I'm running the AddToProject as a Transaction to make sure that if more than 1 person is adding a project at the same time, the project numbers and other information don't get messed up. Any help would be appreciated.Thanks.
SQL Server 2000:Question 1If you drop / rename a table and then recreate the table with the SAMENAME, what impact does it have on stored procedures that use these tables?From a system perspective, do you have to rebuild / recompile ALL the storedprocedures that use this table?I had a discussion with someone that said that this is a good idea, sincethe IDs of the tables change in sysobjects and from a SQL SERVER query planperspective, this needs to be done...Question 2If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if anerror occurs? Will it Rollback? The theory is that it won't becauseTruncate doesn't utilize the logs where as Delete From uses the SQL Logs?Thanks!
Hi , it's sounds me bit stupid but can any one tell me that is there any tables/view that has information about database that who create this procedure or table.?
hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks
Hello all! What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB??? So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this? The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me! Any help would be greatly appreciated!!!
Hi, I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table. i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates. As I know nothing about stored procedures I'm not even sure how to go about this?? Regards. Peter.
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1 EXEC sp2 EXEC sp3
Job2
EXEC abc1 EXEC abc2 EXEC abc3 EXEC abc4 EXEC abc5
Job3
EXEC xyz1 EXEC xyz2 EXEC xyz3 EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1. procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.
I am using multiple stored procedures which are using same set of tables at a time . As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing. My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
I have a Product Table. And now I have to create its Stored Procedures. I am asking the best practice regarding the methods Insert And Update. There are two options. 1. Create separate 2 procedures like InsertProduct and UpdateProduct. 2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his article http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 Can any one explain the better one. Waiting for helpful replies. http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419 a
hi i need to insert the list of ipaddress using stored procedures. the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values. how to do this..
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE ----- CREATE TABLE sequences ( -- sequence is a reserved word seq varchar(100) primary key, sequence_id int );
MS SQL SERVER STORED PROCEDURE: ------------------------------- CREATE PROCEDURE nextval @sequence varchar(100)AS BEGIN -- return an error if sequence does not exist -- so we will know if someone truncates the table DECLARE @sequence_id int set @sequence_id = -1
UPDATE sequences SET @sequence_id = sequence_id = sequence_id + 1 WHERE seq = @sequence
Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000. The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it. The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport @AuditTypeName varchar(50), @PlantId int = NULL, @BuildingId int = NULL, @AreaId int = NULL, @WorkCellId int = NULL, @WorkShift int = NULL, @StartDate datetime = NULL, @EndDate datetime = NULL, @debug bit = 0 AS CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money) DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = ' INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent) SELECT DISTINCT QualityControl_Audit_Item.Header, QualityControl_Audit_Item.AuditItem, QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence, NULL, NULL FROM QualityControl_Audit INNER JOIN QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)' IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId' IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId' IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId' IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId' IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift' IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate' IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + ' ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence' IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int, @xWorkShift int, @xStartDate datetime, @xEndDate datetime' EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable OPEN my_cursor --Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent --Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN --Major EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) --Minor EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent END CLOSE my_cursor DEALLOCATE my_cursor SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
Hello there! I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with. Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50) I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes 2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes 3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.
I have a great problem !! I have a database having around 50 tables and around 70 t0 100 stored procedures. due to certain reason name of all the table and procedure was prefixed with "cls" , now i want to rename them by "tbl_" using some query !! in bulk , and tables have relation ships.
so how to rename them !!
is there any way that I can rename them by modifying data in any system table !?
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I setup the databse and Visual Web Developer to use stored procedures when the insert command is used. The database also uses the field UserName that I pass using a SessionParameter within the InserParameter block from the Membership.GetUser.Username from the aspnet_ tables.
My difficulty is when using the "Formview" as the body to with which to insert (I wanted the design versatility of FormView) I get an error: "system.formatExpression: Input string was not in a correct format" when I leave the textboxes empty and invoke the insert command. I first assumed that the bound textboxes are not being converted correctly when left blank, so I used the ConvertEmptyStringToNull=True, with no success. I then coupled that with the Type=[correct format] still with the same error. Alas, my final attempt was to set defaults in the "ALTER PROCEDURE" within the stored procedure itself.
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions. Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit]. There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C'] I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is: 1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)? 2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])? Any suggestions and advice or examples on how to do this would be appreciated. Craig
