When I run this query against PFGDSMRISSQLQ1 in Server manager 2005:use <database>INSERT INTO dbo.<table> (<column1>, <column2>, <column3>)VALUES (12598,2900,1.00)I get this error:Msg 8624, Level 16, State 1, Line 5Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.Can someone please help. Thanks!
View 1 RepliesI support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.
Thanks,
-Dave
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 here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
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.
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
Hi All,
I am trying to run an insert statement and getting an error.
Insert statement:
insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master
Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Any idea why? Thanks.
Hi
All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error
" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
And
code is as follows
Code Snippet
Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)
connection()
myCommand = New SqlCommand("INSERT INTO Purchase_Header(PH_Voucher_Date,PH_Bill_No,PH_Bill_Date,PH_GRN_No,PH_GRN_Date,PH_Type_Of_Purchase_Id,PH_Supplier_Id,PH_Total_Purchase_Value,PH_Total_Product_Value,PH_Bill_Paid_On,PH_Bill_Due_On,PH_No_Of_Sub_Bills,PH_No_Of_Sub_Bills_Received,PH_Bill_Status,PH_Payment_Status,PH_Apportionment_Status,PH_Goods_Receipt_Status,PH_Sub_Bill_Receipt_Status,PH_Cash_Credit_Indicator,PH_Total_Tax_Paid) VALUES('" & System.DateTime.Today & "'," & BillNo & ",'" & BillDate & "'," & GRNNO & ",'" & GRNdate & "'," & TOP & "," & Supplier & "," & TotalPurchasevalue & "," & TotalProductValue & ",'" & BillPaid & "','" & BillDue & "'," & NoOfSBills & "," & noofbreceived & "," & billstatus & "," & paymentstatus & "," & appstatus & ",'" & recitstatus & "','" & sbillstatus & "' ,'" & ccindicator & "',0)", myConnection)
myCommand.ExecuteReader()
MsgBox("Data saved successfully")
myConnection.Close()
and table structure is as follows and for date types i am passing them through date variables from textbox
INSERT INTO [MoneyBee].[dbo].[Purchase_Header]
([PH_Voucher_Date]
,[PH_Bill_No]
,[PH_Bill_Date]
,[PH_GRN_No]
,[PH_GRN_Date]
,[PH_Type_Of_Purchase_Id]
,[PH_Supplier_Id]
,[PH_Total_Purchase_Value]
,[PH_Total_Product_Value]
,[PH_Bill_Paid_On]
,[PH_Bill_Due_On]
,[PH_No_Of_Sub_Bills]
,[PH_No_Of_Sub_Bills_Received]
,[PH_Bill_Status]
,[PH_Payment_Status]
,[PH_Apportionment_Status]
,[PH_Goods_Receipt_Status]
,[PH_Sub_Bill_Receipt_Status]
,[PH_Cash_Credit_Indicator]
,[PH_Total_Tax_Paid])
VALUES
(<PH_Voucher_Date, datetime,>
,<PH_Bill_No, varchar(15),>
,<PH_Bill_Date, datetime,>
,<PH_GRN_No, numeric,>
,<PH_GRN_Date, datetime,>
,<PH_Type_Of_Purchase_Id, numeric,>
,<PH_Supplier_Id, numeric,>
,<PH_Total_Purchase_Value, numeric,>
,<PH_Total_Product_Value, numeric,>
,<PH_Bill_Paid_On, datetime,>
,<PH_Bill_Due_On, datetime,>
,<PH_No_Of_Sub_Bills, numeric,>
,<PH_No_Of_Sub_Bills_Received, numeric,>
,<PH_Bill_Status, numeric,>
,<PH_Payment_Status, numeric,>
,<PH_Apportionment_Status, numeric,>
,<PH_Goods_Receipt_Status, bit,>
,<PH_Sub_Bill_Receipt_Status, bit,>
,<PH_Cash_Credit_Indicator, varchar(6),>
,<PH_Total_Tax_Paid, numeric,>)
Thanks
Avinash
hi
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
VragenTableAdapter VraagAdapter = new VragenTableAdapter();
lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1)
{
pnlVraag1.Visible = true;
}
if (Vraag_ID == 2)
{
pnlVraag2.Visible = true;
}
}
protected void btnVraag1_Click(object sender, EventArgs e)
{
//make a new user
GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
DateTime Moment = System.DateTime.Now;
GebruikerAdapter.GebruikerToevoegen(Moment);
int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
this.ViewState.Add("gebruiker_id", GebruikerID);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);
//go to 2nd question
Response.Redirect("Default.aspx?vraagid=2", true);
}
protected void btnVraag2_Click(object sender, EventArgs e)
{
//get back the user from question 1
int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);
when i run debug it allways stops here
//go too 3th question
Response.Redirect("Default.aspx?vraagid=3", true);
}
}
can somebody plz help me ? =(
ps: sorry for my bad english
Ok, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue:
sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery()
The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error:
Incorrect syntax near the keyword 'Bulk'.
Anyone have any ideas, thanks
I am receiving the following error when trying to insert values from textboxes on a registration form:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30205: End of statement expected.
Source Error:
Line 19: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Line 20:
Line 21: Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Line 22: Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
Line 23: dbCommand.CommandText = queryString
My code is below. Can anyone help me figure out what is wrong with my code?
<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim UCde as string = txtUserID.Text
Dim UserID as string = txtUserID.Text
Dim Password as string = txtPassword.Text
Dim Email1 as string = txtEmail.Text
Dim FirstName as string = txtFirstName.Text
Dim LastName as string = txtLastName.Text
Dim AdminLevel as string = dropAccountType.SelectedItem.Value
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim rowsAffected As Integer = 0
dbConnection.Open()
dbCommand.ExecuteNonQuery()
dbConnection.Close()
End Sub
Thanks for your help.
Chris
Here is my insert statement: StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO patients_import_test "); sb.Append("(Referral_Number,Referral_Date,FullName,Patient_ien,DOB,FMP,SSN_LastFour,Race_Id,PCM,Age) "); sb.Append("VALUES(@rnum,@rdate,@fname,@patid,@birthDate,@fmp,@ssan,@race,@pcm,@age) "); sb.Append("WHERE Referral_Number NOT IN ( SELECT Referral_Number FROM patients_import_test )");I'm getting an "Incorrect syntax near the keyword 'WHERE'".If I remove the WHERE clause the INSERT statement work fine.
View 3 Replies View RelatedUsing Access 2010 and SQL Server 2012..i added a database through SSMS and added a table named tblEmployee (dbo.tblEmployee)
The table has 3 fields
LName (nvarchar(50), null)
FName (nvarchar(50), null)
Code (nvarchar(50), null)
The access table has 3 fields
FName, text
LName, text
Code, text
I found a code snippet here to insert from the Access table to the SQL table.UtterAccess Discussion Forums > Appending Access table to SQL Server table (and vice-versa) usin...The code is generating this error
Run-time error '3134':
Syntax error in INSERT INTO statement
Code:
Option Compare Database
Sub AppToSQL()
Dim strODBCConnect As String
Dim strSQL As String
'Code from:
[code]...
Hi,
I have a problem as shown below
Server Error in '/ys(Do Not Remove!!!)' Application.
Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source Error:
Line 8: <Script runat="server">
Line 9: Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
Line 10: SqlDataSource1.Insert()
Line 11: End Sub ' InsertAuthorized
Line 12: </Script>
Source File: C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx Line: 10
Stack Trace:
[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +88
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +410
System.Web.UI.WebControls.SqlDataSource.Insert() +13
ASP.authorizing_aspx.InsertAuthorized(Object Source, EventArgs e) in C:Documents and SettingsDream_AchieverDesktopys(Do Not Remove!!!)Authorizing.aspx:10
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919
And part of my program is as shown
<Script runat="server">
Private Sub InsertAuthorized(ByVal Source As Object, ByVal e As EventArgs)
SqlDataSource1.Insert()
End Sub ' InsertAuthorized
</Script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DIP1ConnectionString %>" ProviderName="<%$ ConnectionStrings:DIP1ConnectionString.ProviderName %>"
InsertCommand="Insert Into Authorize (Army ID,Tag No,Vehicle ID,Vehicle Type,Prescribed Route,Start Time, End Time) VALUES (@ArmyID, @TagNo, @VehicleID, @VehicleType, @PrescribedRoute, @StartTime, @EndTime)">
<insertparameters>
<asp:formparameter name="ArmyID" formfield="ArmyID" />
<asp:formparameter name="TagNo" formfield="TagNo" />
<asp:formparameter name="VehicleID" formfield="VehicleID" />
<asp:formparameter name="VehicleType" formfield="VehicleType" />
<asp:formparameter name="PrescribedRoute" formfield="PrescribedRoute" />
<asp:formparameter name="StartTime" formfield="StartTime" />
<asp:formparameter name="EndTime" formfield="EndTime" />
</insertparameters>
</asp:SqlDataSource>
Anybody can help? thanks
Public DBString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Q:VoicenetRTS FolderRTS ChargesAccountscosting.mdb"
Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
Dim connstring As New OleDbConnection(DBString)
connstring.Open()
Dim searchstring As String = "SELECT * FROM Costings1"
Dim da As New OleDbDataAdapter(searchstring, connstring)
Dim DS As New DataSet()
Dim dt As DataTable = DS.Tables("Costings1")
da.FillSchema(DS, SchemaType.Source, "Costings1")
da.Fill(DS, "Costings1")
Dim cb As New OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand
da.UpdateCommand = cb.GetUpdateCommand
Dim dr As DataRow = DS.Tables("Costings1").NewRow
dr("Key") = TextBox1.Text
dr("CODE") = TextBox2.Text
dr("Element") = TextBox3.Text
etc...
DS.Tables("Costings1").Rows.Add(dr)
da.Update(DS, "Costings1") <<<<<<<<<<<Syntax error in INSERT INTO statement.
There are no spaces in the field names.
hi
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
VragenTableAdapter VraagAdapter = new VragenTableAdapter();
lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1)
{
pnlVraag1.Visible = true;
}
if (Vraag_ID == 2)
{
pnlVraag2.Visible = true;
}
}
protected void btnVraag1_Click(object sender, EventArgs e)
{
//make a new user
GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
DateTime Moment = System.DateTime.Now;
GebruikerAdapter.GebruikerToevoegen(Moment);
int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
this.ViewState.Add("gebruiker_id", GebruikerID);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);
//go to 2nd question
Response.Redirect("Default.aspx?vraagid=2", true);
}
protected void btnVraag2_Click(object sender, EventArgs e)
{
//get back the user from question 1
int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);
when i run debug it allways stops here
//go too 3th question
Response.Redirect("Default.aspx?vraagid=3", true);
}
}
can somebody plz help me ? =(
ps: sorry for my bad english
I Have an error: Server Error in '/quanlythietbi' Application. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated. Source Error: Line 129:mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));
Line 130:mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;
Line 131:int i = mycommand.ExecuteNonQuery();
Line 132:if (i>0)
Line 133:{and this is my code:string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";myconnection = new SqlConnection(stringconn);mycommand = new SqlCommand(sqlstring,myconnection);myconnection.Close();myconnection.Open();mycommand = new SqlCommand(insertquery,myconnection); mycommand.Parameters.Add(new SqlParameter("@ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));mycommand.Parameters["@ngayGiaiQuyetxong1"].Value = Label8.Text;mycommand.Parameters.Add(new SqlParameter("@date_yc1",System.Data.SqlDbType.SmallDateTime));mycommand.Parameters["@date_yc1"].Value = Label7.Text;mycommand.Parameters.Add("@manv_yc1",System.Data.SqlDbType.Char,10);mycommand.Parameters["@manv_yc1"].Value = TextBox2.Text;mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;int i = mycommand.ExecuteNonQuery();if (i>0){lbcheck.Text = "Ä?ã Cáºp Nháºt Yêu Cầu.";} --------------------------------------------------------------------------------------------------------------------------------I don't know what I must do to repair it :(
HI,
WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.
BUT WE ARE GETTING A FEW HICCUPS. PLS HELP
THIS IS HOW IT GOES :-
CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN
CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP2
EXEC MY_PROC1
ST1 .........;
ST2..........;
END
THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED
NO PROBLEM TILL NOW.......
BUT,
CREATE PROCEDURE MY_PROC3
AS
BEGIN
CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP3
EXEC MY_PROC2
ST1 .........;
ST2..........;
END
THEN IT GIVES AN ERROR AS :-
"An INSERT EXEC statement cannot be nested."
CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........
WHAT IS THE NESTING LEVEL OF A PROCEDURE ?
IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
PLS HELP ME OUT IN THIS
THANKS
Hi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
View 2 Replies View RelatedThe following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
Have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11
The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note
- There are no triggers depending on the table
- Permissions are granted at a roll level which is rolled down to the login
- The test environments have the same level of permission which works fine.
Good day,
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
Please someone help, this is driving me insane.
Thanks
Hi, I have the following error logged in SQL Server's error log whenever I run a query on a particular database :
2002-01-04 22:54:02.46 spid11 Error: 823, Severity: 24, State: 1 2002-01-04 22:54:02.46 spid11 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14eac480, page ptr = 0x73c94000, pageid = (0x1:0x158c89), dbid = 8, status = 0x801, file = d:mssql7datamydb.mdf..
2002-01-05 05:54:22.01 kernel SQL Server terminating because of system shutdown. 2002-01-05 05:54:22.14 kernel LogEvent: Failed to report the current event. Operating system error = 31(A device attached to the system is not functioning.).
and get this error in the Results pane of Query analyzer : Server: Msg 823, Level 24, State 1, Line 1 I/O error 1117(The request could not be performed because of an I/O device error.) detected during read of BUF pointer = 0x14ace4c0, page ptr = 0x35df6000, pageid = (0x1:0x44b73), dbid = 14, status = 0x801, file = d:MSSQL7DATAmydb.mdf.
Connection Broken
select @@version returns, Microsoft SQL Server 7.00 - 7.00.961 (Intel X86) Oct 24 2000 18:39:12 Copyright (c) 1988-1998 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Books Online Help on Error 823 indicates that it is a disk problem and suggests that DBCC checkdb needs to be run on the database. But, hardware diagnostics on the server do not report any disk problems and dbcc checkdb also returns no errors.
Help on the cause of error 823 would be greatly appreciated. Thank you, Praveena
Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1;
sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,";
sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)";
sql1 += " VALUES (";
sql1 += "'" + TxtTitle.Text + "'," ;
sql1 += "'" + TxtForename.Text + "'," ;
sql1 += "'" + TxtSurname.Text + "'," ;
sql1 += "'" + TxtHouseNo.Text + "',";
sql1 += "'" + TxtRoad.Text + "',";
sql1 += "'" + TxtTown.Text + "',";
sql1 += "'" + TxtPostcode.Text + "',";
sql1 += "'" + TxtPhone.Text + "',";
sql1 += "'" + TxtDob.Text + "',";
sql1 += "'" + TxtEmail.Text + "',";
sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName,
Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5
1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!
Whenever I'm running query against the below database alone, it throws me the error.
use [AdventureWorks_2005]
SELECT * FROM dbo.sysFILES
OR
SELECT * FROM [AdventureWorks_2005].[sys].[SYSFILES]
Error- >
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.sysFILES'
Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed.
Thanks for your help!
Sue
question 1:
i found that database log file can contain more records after performing backup database statement.
for example:
i create a database and limit the log file to 2mb. then i create a table and insert data.
If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.
If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records
why?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end
question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.
After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.
As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end
any suggestions?
thanks in advance.
Hi,
Please Help, I am very desparate for a solution to this one.
I have an error appearing
"Error 25115 Wrong Page (potential database corruption). Try Running the Repair Utility."
I have SQL Server Enterprise set up with Merge replication, it is being used to Sync with Windows Mobile Devices as the subscribers.
All the Windows Mobile subscribers have been synchronising fine up until recently, when 1 of the Subscribing Windows Mobile devices received this error.
The day after, another device Synchronised fine the first time (note that the Synchronisation was a long one and the SDF grew in size dramatically while the synchronisation occured, but the Sync worked fine and the SDF dropped back to its initial size) The 2nd sync 5 mins later failed however so this Mobile device has the same error also.
Any suggestions as to the below are much appreciated
a) Why this error occurs?
b)How do you resolve this error?
c)What is the Repair utility mentioned in the error, and is it of use to me?
Thanks in advance, I am very greatful for any advice.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedI have the code below that will insert the record to the database. The code works fine, until I try to add the current user thats logged on and then I try to submit the values to the database and it gives me this errorIncorrect syntax near the keyword 'user'.and highlights this line.MyCmd.ExecuteNonQuery() Thanks
Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As String
Dim sConnString, sSQL As String
Dim u As MembershipUser
'Receiving values from Form, assign the values entered to variables
name = Request.Form("name")
address = Request.Form("address")
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")
'declare SQL statement that will query the database
sSQL = "INSERT into test (name, address, user) values ('" & _
name & "', '" & address & "', '" & _
nice & "')"
'define the connection string, specify database
'driver and the location of database
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()
'create an ADO connection object
Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)
Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()
End Using
Response.Write("The form information was inserted successfully.")
End Sub
End Class
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
thanks
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
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
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
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
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
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
Hallo,
for my project I need to calculate the estimated time an SQL statement will be running. The statements will be composed via a GUI and get become very complex. So the user needs a rough time approximation of how long to wait for the answer. A few seconds, ten minutes or several hours!?
My question:
Does anyone know good links, articles or other source to get familiar with this topic?
I'd also like to start a little discussion. On what does the runtime depend? (Is runtime the right word here?) Number of JOINs Number of different tables Size of the tables Complexity of the WHERE-Condition ("id=5" or rather "name LIKE '%foo%'") Indizes Hardware Number of users on the DBWhat else influences the time and how?
Aside from that I found the "SET SHOWPLAN_XML ON" for SQL Server 2005 to get the execution plan of the query.
Extract:
Code:
<StmtSimple StatementText="SELECT * FROM ContactPerson WHERE LocationID = '4';" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0033205" StatementEstRows="17.027" StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
...
</StmtSimple>
But I can't find a reasonable listing of the elements and attributes and their meaning.
I think StatementSubTreeCost sound interesting. Is that the time, the statement needs to run or what does the number 0.0033205 say?
Is anyone familiar with the SHOWPLAN_XML? Or can anyone provide me a link about the values of this plan, perhaps in the Microsoft-MSDN area!?
The principle is clear. But what about the values in detail. There must be a documentation somewhere!
Thank you in advance,
Dominik