Hi,
We are having Sqlserver 7.0 with service pack2. One of my table is acting crazy. Let me explain.
I have table called aps_table , whenever i am selecting one record it is coming with zero records, then i tried to insert the same record then i am getting the following error.
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_QUESTION_ANSWERS_T'. Cannot insert duplicate key in object 'QUESTION_ANSWERS_T'.
The statement has been terminated
PLs anyone suggest me how i have to overcome this problem.
bulk insert DB_Kash.dbo.tb_category from 'C:cpdataSUPPLIER_5305OUTPUTTb_Category.txt' with (formatfile = 'C:b_category.txt')
This works on one sql server and the same code does not work on another server.I have taken care to see that the path is appropriate. Are there any server settings involved? Infact the same format file and datafile work fine when I am doing BCP. I have tried with select into bulk copy option on and off too. Any info on this is greatly appreciated.
Hi, I am trying to insert the Source name and clientId to a table called clientSource...
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name 170234 Constructions Employee Deferral Employer Discretionary Employer Matching....
And in another table called SourceDBF i have the following fields with data PlanNumber PARTID SOURCE_NUM etc... 170234 123456789 1 170234 123456789 3 170234 451231334 1 So how do i match the Source_NUM with SRCnames when i insert it into the table.. INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF .. but i am stuck.. any help will be appreciated. Regards Karen
bulk insert DB_Kash.dbo.tb_category from 'C:cpdataSUPPLIER_5305OUTPUTTb_Category.txt' with (formatfile = 'C:b_category.txt')
This works on one sql server and the same code does not work on another server.I have taken care to see that the path is appropriate. Are there any server settings involved? The table structure is the same in both cases and the select into nulk copy option has been selected. The table has full text indexing set on it but I don't think that this would make any difference. The only error it gives is "ILE DB Stream reported an error.The stream does not provide any expalanation regarding this error." Something like this.
Infact the same format file and datafile work fine when I am doing BCP. I have tried with select into bulk copy option on and off too. Any info on this is greatly appreciated.
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID) select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}', '{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID From members Inner Join groupLeaders ON members.memberID = groupLeaders.memberID SELECT @@Identity AS UserID
How can I modify the portion that is inserting the '56' at the end of each username to do the following:
1) check to see if username already exists in the database (using a query with "LIKE %'")
2) if not, create the username "as-is" or how it should be without the number
3) if already exists, get a count of records matching your search criteria .... now make a new username + + (count + 1).ToString();
Any thoughts... I am struggling to put these two pieces together.
Thanks,
Zoop
[EDIT - original post below this]
I have modified my method to make this a bit easier. I added a memberID field to my [Users] table so that I can update my [Members] table in a difference statement after the insert takes place.
I have the following query, and it completes succesfully in query analyzer (though I haven't actually executed the SP, just testing the syntax...) anyway, here is what I have:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID) select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}', '{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID From members Inner Join groupLeaders ON members.memberID = groupLeaders.memberID SELECT @@Identity AS UserID
I am hoping this will create a user for all members whose 'memberID' can be found in the groupLeaders table... is this correct?
Also, notice the 56 being appended to the end of each username. I would like this to be a random number generated within a given range... can this be done? any advice?
Thanks,
Zoop
[Original post below - provide more background]
I have three tables involved with this insert/update:
I want to insert into the [Users] table the memberFirstName.memberLastName + randomNum into the 'UserName' column from the [Members] table. Also, I want to make all passwords the same, in this case I know the Salt, Hash1, Hash2 I will be using and would like to pass these in for the 'UserHash1' 'UserHash2' fields.
Now, I only want to make this insert where the memberID is in the GroupLeaders table. and Finally, I need to Update my Members table with a UserID where the memberID matches the one used from the groupLeaders table.
Does anyone have any ideas on how I can accomplish this, even if it requires adding a temporary field to one of my tables... here is what I have so far, but am recieving errors and can't quite figure this one out. (btw - I also don't know how to gen the rand num and was using the literal 23 as a placeholder.) Thanks...
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2) select a.memberFirstName + '.' + a.memberLastName + '23' + as userName, '{AA99FCDE-6E06-437D-B9E9-3E3D27955C3E}', '{7xxxxxx2-4xx6-9xx1-7xx9-4x3xx4Axxx59}', '{0xx8xxE-Cxx4-6xxx-xxxx-Fxx3xxxx3xxF}', b.memberID as newMemID From members a, groupLeaders b Where a.memberID = b.memberID SELECT @@Identity AS UserID
Update Members Set UserID = Ident_Current('Users') where memberID = newMemID
Hi,Question: Why is INSERT of a string having 8000+ characters causing truncation in a TEXT field?We are working on a website for a client, and one of their CMS-driven pages contains more than 8000 chars for its content. The database field was originally TEXT datatype at the default size, but the content was getting truncated upon INSERT. So I looked into why this was happening, tried many things including: 1. Executing: EXEC sp_tableoption 'section', 'text in row', '7000'2. Changing datatype from TEXT to VARCHAR(8000). (of course this truncated at 8000).And this truncation still occurring. This is happening on SQL Server 2000 installed and configured on our client's server. The servers we host don't have this problem, and we have even tested the INSERT on our server and it does not truncate. So why is it truncating only on our client's Sql Server? Is there some kind of default configuration setting that limits the allowable length?I'm not intimately familiar with the large BLOB datatypes, and haven't really needed to deal with this issue before now. Now the client is upset about the limitation and does not wish to copy edit the content for their webpage. This is an urgent matter. I need to figure out the problem within a few hours today (it is morning in Europe on Tuesday 14 mar 2006 right now). Thanks in advance.UPDATE: I HAVE RESOLVED THE ISSUE. THE PROBLEM WAS A RESTRICTION IN THE STORED PROCEDURE INPUT VARIABLE DECLARATION -- WAS TRUNCATING AT 8000 CHARACTERS.
Hi When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....). Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
hi all, i created a register form for users to fill in. I have a problem when i submit new record to my database. It shows "Invalid cast from System.String to System.Byte[]. i don't know how to solve the problem. i have created a database name User_Profile.It included Username(char),Password(char),Name(varchar),Department(varchar),Workphone(binary),mobilephone(binary),Email(varchar). Any problem with the data type? Please help.Thanks.
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim cm As OleDbCommand = New OleDbCommand Dim ds As New DataSet Dim conn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDb;Username=;Password=;Name=;Department=;Workphone=;Mobilephone=;Email=;Trusted_Connection=yes;Initial Catalog=InstantMessenger;Data Source=(local)")
I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5
I am able to select
Following is the code i am using.error is same for both stmts insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1) insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)
please help.thanks in advance
following is the error OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".
I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called Received an invalid column length from the bcp client for colid 1.
I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
Hello, I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query. Ex. how can the following be implemented using the same query but in sql server? Access ------ SELECT MAT_Charts.YYYYMM FROM MAT_Charts WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key])) GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested: [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
hello friends my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn) 'you should use sproc instead cmd.Parameters.AddWithValue("@UserId", textbox1.text) 'your value Try conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery() conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString())) Catch sex As SqlExceptionThrow sex Finally If conn.State <> Data.ConnectionState.Closed Then conn.Close() End If End Try MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource() SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)" SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0 Try rowsaffected = SglDataSource2.Insert()Catch ex As Exception Server.Transfer("yardim.aspx") Finally SglDataSource2 = Nothing End Try If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx") ElseServer.Transfer("urunsat.aspx") End If
I am getting error in sysindexes when i run dbcc checkdb on a production db. the error is Server: Msg 8928, Level 16, State 1, Line 1 please help me to remove this all the options of dbcc checkdb as well as table are not helping me
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
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.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
On my site users can register using ASP Membership Create user Wizard control. I am also using the wizard control to design a simple question and answer form that logged in users have access to. it has 2 questions including a text box for Q1 and dropdown list for Q2. I have a table in my database called "Players" which has 3 Columns UserId Primary Key of type Unique Identifyer PlayerName Type String PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table. I am having problems getting this to work and keep getting exceptions. Be very helpful if somebody could check the code and advise where the problem is??
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) { SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1"); MembershipUser myUser = Membership.GetUser(this.User.Identity.Name); Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue; DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString()); DataSource.Insert();
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them 1) Create the table 2) Create the trigger 3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value 4) Drop the trigger 5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
GO /************************************************ 2) - Create the trigger ************************************************/ CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table] INSTEAD OF INSERT AS BEGIN
INSERT INTO my_table ( forename, surname) SELECT forename, surname FROM inserted
END
/************************************************ 3) - Do the insert ************************************************/
INSERT INTO my_table ( forename , surname ) OUTPUT inserted.my_table_id INTO @my_insert VALUES( @forename , @surname )
select @@identity -- expect this value in @my_insert table select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************ 4) - Drop the trigger ************************************************/
drop trigger [dbo].[trig_my_table__instead_insert] go
/************************************************ 5) - Re-run insert from 3) ************************************************/ -- @my_insert now contains row expected with identity of inserted row -- i.e. OK
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
Details: VB2008 Pro .NET Frameworks 2.0 SQL Compact Edition V3.5 Encryption = Engine Default Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
TRY 2
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
cmd.CommandText = "SELECT * FROM BackupDatabase" cmd.ExecuteNonQuery() Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
rec.SetString(1, Group_Name1) rec.SetString(2, Full_Folder_Address1) rec.SetString(3, File1) rec.SetSqlString(4, File_Size_KB1) rec.SetSqlString(5, Schedule_To_Run1) rec.SetSqlString(6, Backup_Time1) rec.SetSqlString(7, Last_Run1) rec.SetSqlString(8, Result1) rec.SetSqlString(9, Last_Modfied1) rec.SetSqlString(10, Latest_Modfied1) rec.SetSqlBoolean(11, Backup_On_Next_Run1) rec.SetSqlString(12, Total_Backup_Times1) rec.SetSqlString(13, Server_File_Number1) rec.SetSqlString(14, Server_Number1) rec.SetSqlString(15, File_Break_Down1) rec.SetSqlString(16, No_Of_Servers1) rec.SetSqlString(17, Full_File_Address1) rec.SetSqlBoolean(18, OverWriteQuick) rec.SetSqlBoolean(19, CompressAns) rs.Insert(rec) Catch e As Exception MessageBox.Show(e.Message) Finally conn.Close() End Try End Sub
€™20 to 24 rows per sec
TRY 3
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF, 'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
