Insert Statement Fails
Aug 16, 2007
All,
Trying to format some data before I drop it into a grid. I have this in a stored proc but it fails
CREATE TABLE dbo.tmpSummary (
AE NVARCHAR(50)
, PRODUCT_LINE NVARCHAR(20)
, ANNUAL_REV NUMERIC (9)
, [GRWTH/ACQ] NUMERIC (9)
, RETENTION NUMERIC (9)
, CATEGORY NVARCHAR(20)
)
INSERT INTO dbo.tmpSummary (
[AE]
, [PRODUCT_LINE]
, [ANNUAL_REV]
, [GRWTH/ACQ]
, RETENTION
, CATEGORY
)
SELECT
A.AE
, A.PRODUCT_LINE
, A.ANNUAL_REV
, A.[GRWTH/ACQ]
, A.RETENTION
, B.PRODUCT_CATEGORY AS CATEGORY
FROM
tmpSummary A RIGHT OUTER JOIN PRODUCT B
On A.PRODUCT_LINE=B.PRODUCT_CATEGORY
I keep getting an error "Invalid Column name CATEGORY" Anyone know why? Thanks
View 2 Replies
ADVERTISEMENT
Oct 16, 2007
Hi,
How can avoid SQL to rollback the transaction after an insert fails?. I would like to keep the sucessfully inserted rows and continue with next ones after a single one has failed, and then report the ones that failed.
NOT ATOMIC CONTINUE ON SQLEXCEPTION??
Sorry for such a lame question
Regards,
View 13 Replies
View Related
Oct 16, 2006
I ve a simple table with a column of type datetime. I ve successfully inserted the following values in it,
2006-09-13 18:00:10
2006-09-14 18:00:10
2006-09-15 18:00:10
however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails
INSERT INTO TEST VALUES('0000-00-00 00:00:00')
The error thrown is,
Server Msg 242, Level 16, State 3, Line 1
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.
View 13 Replies
View Related
Apr 20, 2008
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??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
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();
}
View 1 Replies
View Related
Apr 10, 2008
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record. Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName" <InsertParameters><asp:Parameter Name="LoginName" Type="String" /> <asp:Parameter Name="strusername" Type="String" /> <asp:Parameter Name="UFID" Type="String" /> <asp:Parameter Name="DateAwarded" Type="DateTime" /> <asp:Parameter Name="Amount" Type="Decimal" /> <asp:Parameter Name="AwardingAgency" Type="String" /> </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form. When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement. Thanks in advance for your help,Ken
View 3 Replies
View Related
Nov 5, 2015
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 Related
Apr 21, 2008
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
View 7 Replies
View Related
Apr 9, 2007
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.
View 5 Replies
View Related
Jan 14, 2008
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
View 5 Replies
View Related
Jan 24, 2008
This is a very weird problem. SQL 2000. A bcp calls an SP:
bcp "exec MyDB.dbo.usp_DF_NA_Analytics_OOW" queryout SomeFile.dat -T -c -t "|" -S "MySvrMyInst"
SP code:
----------------------------
CREATE PROCEDURE dbo.usp_DF_NA_Analytics_OOW AS
BEGIN
SET NOCOUNT ON
--Declare Variables
DECLARE @CURRENTDATE DATETIME
DECLARE @BEGINTIME DATETIME
declare @feedname varchar(50)
set @feedname = 'DF_NA_Analytics_OOW'
SET @CURRENTDATE = CURRENT_TIMESTAMP
-- ensure there is a row for us - create 1 if it doesn't exist
exec usp_datafeed_timestamp_init @feedname
SET @BEGINTIME = (
SELECT LastRunTime
FROM NewAccounts.dbo.DataFeed_Timestamp
WHERE TaskName = @feedname
)
select oowReason.OOWTransaction_id,
oowReason.Position,
oowReason.ReasonCode,
oowTx.UpdateDateTime
from OOWTransaction oowTx (nolock)
join OOWReason oowReason (nolock)
on oowReason.OOWTransaction_id = oowTx.OOWTransaction_id
WHERE oowTx.UpdateDateTime >= @BEGINTIME and oowTx.UpdateDateTime < @CURRENTDATE
ORDER BY oowReason.OOWTransaction_id, Position
-- update the timestamp
exec usp_UPD @feedname, @currentdate
end
------------------------------
ALTER procedure dbo.usp_UPD (@feedname varchar(50), @lastruntime datetime)
as
begin
set nocount on
if not exists (select * from dbo.datafeed_timestamp where lower(taskname) = lower(@feedname))
INSERT INTO Datafeed_Timestamp(TaskName, LastRunTime) VALUES(@feedname, @lastruntime )
else
update Datafeed_Timestamp
set LastRunTime = @lastruntime
where TaskName = @feedname
end
----------------------------------------
The above bcp fails consistently unless the "exec usp_UPD" is completely removed.
Even if I substitute it with the update stmt instead of the SP call, it still fails.
I move the usp_UPD call and move it above the select making the select as the last command in the SP -- still fails.
Removed Order by -- still fails.
The weird thing is -- several other SPs that follow the same exact format (only select query is different) - they all succeed everytime.
This above bcp fails everytime unless the usp_UPD is fully removed.
I have tried putting the result dataset into a table variable and select it in the end -- still fails. several other attempt to workaround - fails -- by fails I mean "0 rows returned" from bcp -- when the UPD is removed, it returns the correct dataset. Otherwise always returns 0 rows.
Outside bcp, if I simply execute the SP from QA, it returns the correct dataset everytime. From bcp, it just doesn't like it. It returns 0 rows everytime, but does the UPD task -- the value does get updated adter execution.
Any thoughts/ideas? This thing is driving me NUTS
Thanks,
Rajesh
View 1 Replies
View Related
Jun 25, 2002
This is a proc that was working on production 7.0 server but is being tested on a 2000, what's changed?
TIA
JeffP....
View 2 Replies
View Related
Feb 22, 2002
Hi
I have C++ Client application writes data using sp in SQL Server.
After inserting more than 150000 records, i am unable to insert data , simply it hangs and no more connection are not allowing.If I close the application, it is working fine. What could be the reason?
Thank in advance
View 1 Replies
View Related
Jun 25, 2002
This is a proc that was working on production 7.0 server but is being tested on a 2000, what's changed?
TIA
JeffP....
View 1 Replies
View Related
Mar 29, 2004
INSERT INTO ticket_dump_datawarehouse
SELECT Ticket_ID,
ID_Entry_Type,
Region,
Related_Case,
Create_Date,
ID_Service_Center,
ID_Type,
ID_Priority
FROM dump_view
will fill fail >
cannot insert the value NULL into column 'Ticket_ID', table 'capacity_base_tracking.dbo.ticket_dump_datawareho use'; column does not allow nulls. INSERT fails.
The statement has been terminated.
But dump_view does not contain any Ticket_ID columns that have NULL value..
where is the problem
View 7 Replies
View Related
May 26, 2006
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.
View 10 Replies
View Related
Oct 20, 2014
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]....
View 1 Replies
View Related
Mar 26, 2007
Hi
I have a page that bulkinsert data to my sql server, I build up the bulk insert part like this....
1 sb.Append("Exec p_BulkInsertPDI '<ROOT><PROT>")
2
3 sb.Append("<PDI NID=""" & HiddenField1.Value & """ AID=""" & HiddenField1a.Value & """ MID="" GID="" UID=""" & UserID & """/>")
4
5 sb.Append("</PROT></ROOT>'")The problem I have here is that sometimes the AID value doesn't have any value beacuse on the previous page haven't sent any value to that hiddenfield. So when I try to run this, I get a error message like this... "Conversion failed when converting the nvarchar value 'AID=' to data type int".It would be the best if I could insert Null values if no value have been provided. Is this possible to do? Regards
View 5 Replies
View Related
Sep 26, 2007
I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\FILESERVERNAMEsharedfolderfilename.txt" could not be opened. Operating system error code 5(Access is denied.).
Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):
BULK INSERT #FIRSTROW FROM '\FILESERVERNAMEsharedfolderfilename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '',
LASTROW = 1
)
If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.
If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.
My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.
I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.
I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.
Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).
Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).
I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.
Can someone provide instructions?
Thanks!
View 7 Replies
View Related
Aug 28, 2007
Here is my problem. There is a form where some information has to be filled out while the other does not. Information that is left blank is not inserted into the database so that I don't have millions of empty rows. This works perfectly, keeps the database accurate. When someone goes to update, if they add something new it would need to be inserted, since there is nothing to update. I was going to write a procedure, but that seems like such a waste since this will happen often. Is there an sql command in MS SQL to do this. I tried all of the sql commands from other databases I had used. (I am new to MS SQL). If there isn't one does someone have a procedure that won't create a huge amount of overhead.
A.
View 7 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
View 4 Replies
View Related
Sep 9, 2015
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
View 2 Replies
View Related
Mar 26, 2001
Hi all,
I have a table with 4869068 rows and when some one tries to insert the records into this table the database times out....Does any one know what could be the reason and from where do I start debugging.
I have no problem with the disk space?
Thanks,
Venu
View 1 Replies
View Related
Mar 17, 2006
I need a trigger (well, I don't *need* one, but it would be optimal!)but I can't get it to work because it references ntext fields.Is there any alternative? I could write it in laborious code in theapplication, but I'd rather not!DDL for table and trigger below.TIAEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblMyTable]GOCREATE TABLE [dbo].[tblMyTable] ([fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTableFOR INSERTASBEGINIF UPDATE(fldKBSubject)BEGINUPDATEtblMyTableSETfldSubject = i.fldKBSubjectFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDIF UPDATE (fldKBDescription)BEGINUPDATEtblMyTableSETfldDescription = i.fldKBDescriptionFROMinserted i INNER JOINtblMyTable ON i.fldCSID = tblMyTable.fldCSIDENDEND
View 3 Replies
View Related
May 7, 2008
Hi Folks,
I have been trying to setup distributed views. On two machines I was successful. These machines do not have any domain configured. I was running SQL 2000 Enterprise on both machines with Win2K.
The steps I followed were:
1) Add linked servers
EXEC master.dbo.sp_addlinkedserver @server = 'FED2', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='WEBSERVER1', @catalog='Federated_Bridge'
2) Add linked servers login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='FED2',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
3) Create table
create table Entity_2(id int not null ,EntityID int not null)
go
4) Add Table constraints
ALTER TABLE [dbo].Entity_2 ADD
CONSTRAINT [CK_11] CHECK ([EntityID] <= 10),
CONSTRAINT [PK_Entity_2] PRIMARY KEY CLUSTERED (id,EntityID )
GO
5) Set Xact Abort on
SET XACT_ABORT ON
GO
..The above steps were repeated on two machines. Step 4 was changed so the check constraint did not overlap.
The steps worked. But now I am trying to do the same thing on two different machines. Both are running Win2K and SQL 2000. The only difference that I can see in the setup is that these new machines are setup under a single domain - ie there is a domain controller - whereas in the first case there is no domain controller.
The error message I get is:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Please help.
View 3 Replies
View Related
Nov 22, 2006
I always got this error when i change the identity key of the table A to yes or no
when i performing an insert into table B and b,accountid is in table A .
seems that when i save, the foreign keys reflect a change of schema...and when i do an insert operation, it fails.
how can i avoid such thinsg to happen? i m using a cursor to load the data and comparing them to see if they are already loaded in the table before inserting them,
ERROR -------------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 16943, Level 16, State 4, Procedure LOADDEPOSIT, Line 167
Could not complete cursor operation because the table schema changed after the cursor was declared.
View 3 Replies
View Related
Nov 11, 2007
Hello,
I seem to have a strange problem in that my code runs fine, but I am trying to INSERT rows into a database, and the rows affected by the INSERT command is alwsy zero, even though no error is thrown. Hence no data gets inserted.
Here is the code:
void recordTick(string pair, DateTime time, decimal sell, decimal buy)
{
SqlConnection thisConnection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FOREX.mdf;Integrated Security=True;User Instance=True");
insert = "INSERT INTO " + pair + " VALUES(" +
"'" + time.ToString("yyyy-MM-dd HH:mms") + "'," +
"'" + sell.ToString() + "'," +
"'" + buy.ToString() + "'" + ")";
thisConnection.Open();
SqlCommand command = new SqlCommand(insert, thisConnection);
Int32 success = command.ExecuteNonQuery(); // success always = 0!
thisConnection.Close();
}
The database is type .MDF that I created with the Project/Add Component/SQL Database menu. I can inspect it easily in Server Exporer and add data manually with no problem.
Can any one suggest how to get the INSERT to actually add the data?
Any help much appreciated!
Anding
View 8 Replies
View Related
Jan 24, 2006
Hi,
I am new to MS DTS and i am using MS SQL 2000 as my database. I am trying to do a Bulk insert using MS DTS package. The package is trying to load data from Text file to a SQL 2000 table. When runninh the package i am getting an error saying that 1 task failed during execution and the task is shown in red colour indicating that the task has failed. Now when i get the details of the error it shows the follows:
Could not bulk insert because the file D:DtsFile.txt could not be opened. Operation system error code: 21 (The device is not ready).
Please help me in solving this problem, if any one has got this error and resolved or have any idea of the error please help. :)
Regards,
Rajeev Prabhu
View 2 Replies
View Related
Jul 30, 2004
who still uses the old dblib can help me?
thanks in advance:
1.create table & procedure in db:
test_table(uniqueidentifier a,varbinary50 b)
CREATE PROCEDURE insert_table
@b_in varbinary
AS
insert into test_table(b) values (@b_in)
GO
2.write program use dblib:
wchar_t str[120]=L"ABC";
dbrpcparam(dbproc[i], "@b_in", (BYTE)NULL, SQLVARBINARY,
-1, 6, &str)
3.I can find the program executed successfully,but only 1 Byte is inserted:
a b
-----------------------------------------------
199D71BE-327A-4BC1-AEC8-ACB0C96076CA 0x41
how to insert the whole string into the database?
View 2 Replies
View Related
Feb 5, 2015
I need to import a CSV file to a table and the CSV has an Address field that has a carriage return in it.
Example:
123 Main St.
Anywhere, CO, 99999
I'm working in Windows with SQL Server 2008. What can I do to the CSV file or from within SQL Managment Studio to get the BULK INSERT to work?
Here's my query:
BULK INSERT Contact
From 'C:UsersBrianDownloadsImport-FilteredContact9c.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '
'
)
View 1 Replies
View Related
Mar 1, 2006
I have a maintenance plan that consists of several parts. It basically backs up all the databases, deletes old backups and then shrinks the databases.
The odd thing is that it appears to back up all the databases, can't tell if it does step 2 or 3 and then it fails with the errors below.
How do i correct this short of throwing it out and starting from scratch?
This is a package originally from one server that i'm trying to deploy to a 2nd server. Already using configuration files to chagne the target connections etc.
thx.
PackageStart,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:16 PM,2/28/2006 5:09:16 PM,0,0x,Beginning of package execution.
OnError,WSWT4361,NT AUTHORITYSYSTEM,{56B53144-6DA7-4276-B37B-A09B1254DD3C},{56B53144-6DA7-4276-B37B-A09B1254DD3C},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,OnPreExecute,{B88BD5B5-138F-4024-A2A5-D60403296701},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',NULL, @Guid OUTPUT
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 2 Replies
View Related
Apr 20, 2007
I have an SSIS job that selects rows from a Sql Server table and inserts them into a table on an AS400 DB2 instance. This process has been running correctly for many months. Last week, the AS400 operating system was upgraded from V5r3 to V5r4 and since that upgrade, the SSIS job is failing on the insert step with the following error:
OLE DB provider "DB2OLEDB" for linked server "BKL400" returned message "SQLDA or descriptor area not valid. SQLSTATE: 07002, SQLCODE: -804".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "DB2OLEDB" for linked server "BKL400" could not INSERT INTO table "[BKL400].[BKL400].[MM4R4LIB].[INV911WK]".
The insert is in an Execute SQL task and uses a linked server definition to access the AS400.
As I said, this process has been working well for many months until the OS upgrade. Any idea on what is causing this and how to correct it will be Greatly appreciated!
View 7 Replies
View Related
Oct 7, 2014
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "Fatals_CTE.t_enrollmentID" could not be bound.
Here are the table definitions:
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[enrollOK] [bit] NULL,
[Code] .....
WHERE enrollmentID = Fatals_CTE.t_enrollmentID;
(I'm trying to update an enrollment's LeaveDate to the date of his Grade 5 event. )
View 2 Replies
View Related
Jan 4, 2010
I'm a developer working on PHP - MSSQL 2008 combined platform. While running a procedure from a webpage I'm encountering the error as below.
My Query:
DECLARE @ReturnValue INT
EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502'
SELECT ReturnValue=@ReturnValue
Error
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl
column does not allow nulls. INSERT fails.
Though the same query when run from SSMS runs fine:
Execute S_AccountsBatchActivation
@FirstName='abc',
@LastName='abc',
@Address='abc',
@City='abc',
@State='abc',
@Country='India',
@Phone='11',
@BatchId=6502
[code]...
On my end I've tried out with ANSI_NULL AND ANSI_NULLS settings being both ON and OFF but it didn't worked.
View 9 Replies
View Related
