I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT:
"insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
Hi all, (I am using SQL Server 2005) I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column. Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later! I do not want to re-use the already used Identity value. I just want to have the last CustomerID to be higher that all the previous ones. Is there any way to do this? Thanking you in advance, Tomy
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer Dim oJobServer As New SQLDMO.JobServer Dim oJob As New SQLDMO.Job Dim oJobSch As New SQLDMO.JobSchedule Dim oJobStep As SQLDMO.JobStep
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf. I tried sp_attach_db specifing only mdf file but geting errors ------- microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223) ..... You cannot attach a database that has been created with multiple log files without also attaching all the log files. ----
how can I remove those big ldf files and create new ldf (defult(1mgb)
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100) declare @SQL VarChar (2000) Declare spot cursor scroll for select GM from BIM_Historical_Performance.dbo.Performance_Master open spot fetch first from spot into @@GM While @@Fetch_Status =0 BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
I have created a installer for my application and database. When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON ________________________________________________________________________________________________ "I won't last a day without SQL"
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet ALTER procedure spCopyTemplateElementTypesToIssues @TemplateRecno integer, @ProjRecNo integer, @IssueRecNo integer as declare @ElementTypeRecno integer declare @ElementTypeDepRecno integer declare @ProjTypeRecno integer declare @PreElementRecNo integer declare @PostElementRecno integer declare @Count integer DECLARE element_Cursor CURSOR FOR SELECT ElementTypeRecNo FROM dbo.tblTemplateElementType where TemplateRecno = @TemplateRecNo OPEN element_cursor FETCH NEXT FROM Element_Cursor into @ElementTypeRecno --delete from tblElementCPO WHILE @@FETCH_STATUS = 0 BEGIN select @count = count (*) from tblElementCPO where ProjRecno = @ProjRecNo and IssueRecno = @IssueRecno and TemplateRecno = @TemplateRecno and ElementTypeRecno = @ElementTypeRecNo if @Count = 0 begin insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno, ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase, TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson) values (0, @ElementTypeRecno, @IssueRecno, @ProjRecno, 5,0,6,99, 99,@TemplateRecno, getdate(), current_user) end FETCH NEXT FROM element_Cursor into @ElementTypeRecno END CLOSE element_Cursor DEALLOCATE element_Cursor select @Count = count (*) FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo if @Count > 0 then begin DECLARE ElementTypeDep_Cursor CURSOR FOR SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo OPEN ElementTypeDep_cursor FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno WHILE @@FETCH_STATUS = 0 BEGIN select @Count = count (*) from tblElementDepCPO where ElementTypeDepRecno = @ElementTypeDepRecno and PreElementRecNo = @PreElementRecNo and PostElementRecno = @PostElementRecno if @Count = 0 begin insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo, PostElementRecno, ChangeDate, ChangePerson) values (@ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno, getdate(), current_user) end FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno END CLOSE elementTypeDep_Cursor DEALLOCATE elementTypeDep_Cursor end
called by Code Snippet Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev) Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "spCopyTemplateElementTypesToIssues" cmd.CommandType = CommandType.StoredProcedure Dim spmTemplateRecNo As SqlParameter = _ cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int) spmTemplateRecNo.Value = _ Me.cbTemplate.SelectedValue Dim spmProjRecNo As SqlParameter = _ cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _ Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _ cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _ Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414 cnQI02414.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. cnQI02414.Close()
I have been in the process of creating some new pkgs in BIS. My question is, once I am done writing the pkgs, what next?
That is, how do I get the pkgs into SQL Server, and how do I schedule them to run?
Also, what if I have the following scenario:
I have created a new SSIS pkg that has several EXECUTE DTS 2000 tasks in them. The DTS packages that are called from the EXECUTE DTS 2000 task are stored in the database under "Legacy". These should still run OK once I "load" the packages to SQL Server 2005, yes?
Where do the SSIS packages "live" in SQL Server 2005? That is, where can I view them.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/ IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
I loaded the import/export wizard into my Tools menu. I ran it and exported a view to an excel worksheet with mixed results (I got the headings, but no data). I want to take a look at the package that should have been created (I selected that option, but the export didn't complete and I couldn't decipher the log to determine whether the package was created). If it was successfully created, where would I find it in SQL 2005 Express?
in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
Does anyone knows what this account does? I heard that it is used by Visual Studio and Query Analyser. However, I couldn't find much documentation about it. This account has a random password which is generated by the Server. The password cannot be changed and will never expire.
I am installing SQL Server on a large number of servers. MSDB was not created on 7 of the 36 servers. MSDB was created just fine if I uninstalled SQL Server and reinstalled it and also when I created MSDB using the instlmsdb.sql script out of the MSSQLInstall directory.
There is no mention of this problem on TechNet but there is an article on it on www.support.microsoft. However, the article says that the bug happens if SQL Server is started in single user mode. I am not starting any of these servers in single user mode.
Someone else has tracked this problem to BIOS. What do you know?
I am getting a return code of 12 (""File not created"") from sp_trace_create. I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account. SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it. The following is the relevant portion of what I am attempting. DECLARE @RC int, @TraceId intExec @RC = sp_trace_create @TraceId OUTPUT, 0, N'C:TraceFile' I have tried other paths that also do not work.
