Insert Using Subquery Fails To Insert With No Error
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 code
InsertCommand="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
ADVERTISEMENT
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
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
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
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
May 6, 2008
Hello All,
I'm having a problem doing a bulk insert on a tab delimited text file into mssql 2005 using either bulk insert or bcp.
When using the following bulk insert command I get the "The column is too long in the data file for row 1, column 2" error.
I have tried
Code Snippet
BULK
INSERT
test.dbo.customerdefinition
FROM
'data_file.txt'
WITH
(
FORMATFILE = 'format_file.txt',
FIELDTERMINATOR = ' ',
ROWTERMINATOR = 'n',
KEEPIDENTITY
)
The data file only has data for the first 10 columns of a table with over 100 columns.
First 10 table columns have the format of
CREATE TABLE [dbo].[CustomerDefinition](
[Rowid] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CustomerId] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Addr3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zipcode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_CustomerDefinition] PRIMARY KEY CLUSTERED
The data-file looks like this (it is tab delimited):
1 1 BEN ONE BENVENUTO 1 BENVENUTO ST. CLAIR & AVENUE ROAD TORONTO ON
2 1 BIGGIN DDP PARTNERSHIP 1 BIGGIN LTD. 1 BIGGIN COURT NORTH YORK ON
3 1 EVA MELIA CORPORATION 1 EVA ROAD SUITE #412 ETOBICOKE ON
4 1 FINANC CONCERT PROPERTIES 200 BAY STREET- SOUTH TOWER SUITE 2100- PO BOX 56 TORONTO ON
5 1 LONGBRID BERKLEY PROPERTY MANAGEMENT INC 1 LONGBRIDGE ROAD 2ND FL THORNHILL ON
6 10 DORA VILLA LASFLORES C/O FOCUS PROPERTIE 10 DORA AVENUE TORONTO- ON
7 10 HOLMES HALTON COMMUNITY HOUSING 10 HOLMESWAY PLACE ACTON ON
8 100 CANYON DEL PROPERTY MANAGEMENT 100 CANYON AVENUE BATHURST & SHEPPARD NORTH YORK ON
9 100 CEDAR LAWRENCE CONSTRUCTION 100 CEDAR AVENUE YONGE & MAJOR MACKENZIE WEST RICHMOND HILL ON
10 100 GOWAN KANCO - 100 GOWAN LTD. 100 GOWAN AVENUE PAPE & DANFORTH EAST YORK ON
The format-file looks like this:
9.0
10
1 SQLINT 0 4 " " 1 Rowid ""
2 SQLCHAR 2 15 " " 2 CustomerId SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 " " 3 Name SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 50 " " 4 Addr1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 50 " " 5 Addr2 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 50 " " 6 Addr3 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 30 " " 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 50 " " 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 10 " " 9 Zipcode SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 50 " " 10 Country SQL_Latin1_General_CP1_CI_AS
I can't for the life of me understand what I'm doing wrong but if someone can help me out here it would be greatly appreciated.
Thanks,
View 3 Replies
View Related
Sep 6, 2005
hello..
is there any way to use insert into and subquert together ?
I am trying that
insert into X (a)values((select (max(id) from Y))
but it says
Subqueries are not allowed in this context. Only scalar expressions are allowed.
View 1 Replies
View Related
Dec 7, 2000
Hi Everyone,
I want to insert a row in a table with 2 fields.
The data for the first column comes from a variable
and the data for the second column comes from a select query
can i insert the record in one sql statement like
************************************************** *************
declare @var1 int
set @var1 = 5
insert into mytable1 (field1,field2)
values
(@var1,select field1 from mytable2 where field2 = 10)
************************************************** ***************
where field2 in mytable2 is a primary key.
Awaiting a reply!
Thanks,
saad.
View 1 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
Mar 24, 2008
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 + ' ')
View 4 Replies
View Related
Apr 1, 2008
Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
View 4 Replies
View Related
Apr 1, 2008
Hi.
I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?
View 6 Replies
View Related
Sep 20, 2007
Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.
drop TABLE X
GO
CREATE TABLE X (
x1 bigint NOT NULL ,
x2 nvarchar(40)
)
GO
drop TABLE Y
CREATE TABLE Y (
y1 bigint NOT NULL ,
y2 nvarchar(40)
)
GO
CREATE TRIGGER trg1
ON X
FOR INSERT AS
BEGIN
DECLARE
@prfirststatus INTEGER,
@newcontextid NCHAR
SELECT @newcontextid = x2,
@prfirststatus = x1
FROM inserted
PRINT 'See the trigger getting invoked without even a single ' +
' row being inserted in table X and values passed to this ' +
' triggers are inserted.x2 = ' + @newcontextid
insert into Y values (@prfirststatus,@newcontextid)
END
GO
insert into X SELECT y1, 'x' from Y where y2 = 'DOESNTEXIST'
View 2 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 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
Apr 11, 2008
I have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?
CREATE TABLE [dbo].[tmptimesheets](
[TimesheetID] [int] IDENTITY(1,1) NOT NULL,
[Placementid] [int] NOT NULL,
[Periodstarting] [datetime] NOT NULL,
[createdon] [datetime] NOT NULL,
[createduserid] [int] NOT NULL,
[Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
[ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
[Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[Comments] [text] COLLATE Latin1_General_CI_AS NULL,
[Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED
(
[TimesheetID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED
(
[Placementid] ASC,
[Periodstarting] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tmptimesheethours](
[TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL,
[Timesheetid] [int] NOT NULL,
[applicantid] [int] NOT NULL,
[Workedon] [datetime] NOT NULL,
[Hoursworked] [numeric](10, 2) NOT NULL,
[performancevalueid] [int] NOT NULL,
[Reject] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Breaks] [numeric](10, 2) NOT NULL,
CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED
(
[TmpTimesheethourid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [Pronet_TS]
GO
ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid])
REFERENCES [dbo].[tmptimesheets] ([TimesheetID])
GO
ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]
Error Message
Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40
Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
View 1 Replies
View Related
Apr 8, 2008
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
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
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
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
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
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
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
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
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
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
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
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
