SqlBulkCopy And Triggers Cause Subquery Returned More Than 1 Value Error
Feb 22, 2008
Hi,
I am using .NET sqlBulkCopy to insert several rows at once into my sql table. Yesterday I created a simple trigger on this table to take the primary key of the inserted row and place into another table. Fairly straightforward I thought, but I keep getting this error when inserting 2 or more rows (works fine with one row):
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."
To test this, I changed the sqlBulkCopy into individual INSERT statements in a for each loop and then it works perfectly, so I know that it's definitely the sqlBulkCopy causing the problem, not the trigger itself. Here is my trigger code anyway:
alter trigger tg_InsertClaim
on AuditItem
for insert
as
declare @AuditItemID as int
set @AuditItemID = (select i.AuditItemID from inserted i inner join dbo.AuditItem a ON i.AuditItemID = a.AuditItemID)
insert into Claim (AuditItemID) VALUES (@AuditItemID)
Hi i am a newbie in sql so any help would be appreciated. i have a serverlog table that initially had one server and different functions types, my sql query got a percentage of the execution times for the different functions and all worked well, however as soon as i put more servers in to the table i got the following error...
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Here is my SQL, the problem is with the sub queries returning more than one server type... Hope somebody can help and thanks in advance..
SELECT server, [function], prelimquery * 100 AS [%OverallProcessingTime] FROM (SELECT server, [function], subquerya / (SELECT subqueryb FROM (SELECT totalallreqexec * (SELECT AVG([exec_time]) FROM llserverlogs) AS subqueryb FROM (SELECT COUNT(*) AS totalallreqexec FROM llserverlogs) DERIVEDTBL) DERIVEDTBL) AS prelimquery FROM (SELECT server, [function], NoRequestExecutions * (SELECT avgexectime FROM (SELECT DISTINCT server, AVG(exec_time) AS avgexectime FROM llserverlogs GROUP BY server) DERIVEDTBL) AS subquerya FROM (SELECT DISTINCT server, [function], COUNT(*) AS NoRequestExecutions FROM llserverlogs GROUP BY server, [function]) DERIVEDTBL) DERIVEDTBL) DERIVEDTBL
Hi group I get the above error in my SP when the tblHolidayDates has more than one record. Here is my SP: begin
if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date SELECT su.SuspensionID, su.ConsentNo, si.SiteID, si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc, si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached, rd.RefValue AS NoticeDays, si.MergeID FROM Suspension su INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID INNER JOIN RefData rd ON si.NotReq = rd.RefCode WHERE su.Status = 'ACT' and si.Archived = 'N' and (datediff(day, getdate()-10 , si.SuspensionStart) = rd.RefValue OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2) or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1) end
I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:
Code Snippet
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.
Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?
Stored Procedure:
Code Snippet
ALTER Procedure [dbo].[addRecord] @userID int, @itemID int, @info varchar(50), @comment varchar(50), @output int output AS
Declare @dateSubmitted datetime set @dateSubmitted = getDate();
--Insert the new record. THIS TABLE has an ID identity Primary Key --column that auto-increments. insert into RecordTable (UserPerson, Information, DateSubmitted) values (@userID, @info, @dateSubmitted);
---Get the ID Assigned in the record table. The Item table ---has a Foreign key on this column. Declare @assignedID int set @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)
/**Set the output parameter.*/ set @output = @assignedID;
--Now update the Item Table. insert into Item (ID, RecordID, Comment) values (@item,@assignedID, @comment);
I am just trying to do a simple update statement and am getting this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
My statment is like this:
Code:
update [Clients] SET [OPT-OUT] = 0 WHERE [OPT-OUT] is null
I have used this against another table with success, but get the error against that one. Anyone have any idea?
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl] @MtlTblMaterialType READONLY AS BEGIN Set NOCOUNT ON;
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
update purchaseorders set orderstatusID = 4 where purchaseorderid in ( select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null )
I am getting the below error while trying to run sql query
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.
i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code: select top 100 username, (Select b.name from application b inner join userroleapplication c on b.applicationID = c.applicationID ) Application, (select name from role d, userroleapplication gr where gr.roleID = d.roleID and gr.userID = a.userID) role from users a where username in ( 'username' )
i know where is the problem but i cant figure how to fix this
I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query
The table with data is like this,one file maybe belong to many group.
Filename FilegroupID
file1 1 file2 2 file1 3 file1 4 file3 1 file2 5
I need write a query to get the result like this
file1 1,3,4 file2 2,5 file3 1
I try to write a query with subquery but I get error message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I use SQL Server 2005.but I need the subquery return data like "1,3,4".
I have a simple update query that looks like this:
Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID
I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.
Okay, I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."
But the point of the subquery was to in fact return more than one value.
SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))
IF @parentPage IS NOT NULL
IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID])) SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]
And I kept getting this error when testing in SQL Management Studio.
Msg 512, Level 16, State 1, Procedure parentPage, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm having problem Inserting data from 1table into another table. I'm getting the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks
Here's an example of what I'm trying to do...
table1 has: ID | Name | countryID 1 | Joe | 101 2 | Ben | 102 3 | Tom | 102
Now I wrote this code to insert some of the data from table 1 into table2...
insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1 I need to write that kind of sql to return me single value a and multiple values b on each of lines, like a b ---------------------------- tom small,big,hugh But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
when I run the query UPDATE dbhal.dbo.tblUser SET vchrFirstName = FirstName, vchrLastName = LastName, vchrLogin = LoginName, bitActive = Active, vchrEmailAddress = EmailAddress FROM TempEmployee, tbluser where EmployeeID = intEmp and vchrcompid = 'sam'
and there is trigger
CREATE TRIGGER UPDATE_tblUser ON tblUser FOR UPDATE AS DECLARE @CompanyId as varchar(20), @intUid as int SET @CompanyId =(Select vchrCompID from deleted) Set @intUid = (Select intUid from deleted) IF @CompanyId = 'sam' BEGIN UPDATE [dbPortal].[dbo].tblUser SET intEmp = (Select intEmp from inserted), vchrCompID = (Select vchrCompID from inserted), vchrPwd = (Select vchrPwd from inserted), vchrLogin = (Select vchrLogin from inserted), vchrFirstName = (Select vchrFirstName from inserted) , vchrLastName = (Select vchrLastName from inserted), vchrEmailAddress = (Select vchrEmailAddress from inserted), bitActive = (Select bitActive from inserted), intPWDAttempt = (Select intPWDAttempt from inserted), vchrCreatedWho = (Select vchrCreatedWho from inserted), dtmCreatedDate = (Select dtmCreatedDate from inserted), vchrModifiedWho = (Select vchrModifiedWho from inserted), dtmModifiedDate = (Select dtmModifiedDate from inserted) WHERE intUID = @intUid END
I get the error. What can I do to get this to run?
"Subquery returned more than 1 value." etc when running a query on a linked server through OpenQuery. This returns around 6 values (some of them are fairly long varchar's). When running another query the same way which returns 2 values, it works fine.
The problem query also works fine when run locally. It's calling a User-Defined Functions which returns a table, but the second query which works fine also does this.
Where can this subquery be ? Inside the UDF (shouldn't matter, should it?) ?
Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows
=,<,>.................................Below is my query declare @WorkOrder as nvarchar(10) Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')
SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date', M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal, isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +
The error below is encounter when a mass update is attempted on our employee table.
Server: Msg 512, Level 16, State 1, Procedure emp_lock_id, Line 8 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.
The Trigger code is below
CREATE TRIGGER emp_lock_id ON dbo.EMPLOYEE FOR UPDATE AS DECLARE @NEXT_NUM DECIMAL(17, 12) DECLARE @NEW_NUM DECIMAL(17, 9) DECLARE @OLD_LOCK_ID DECIMAL DECLARE @NEW_LOCK_ID DECIMAL SET @OLD_LOCK_ID = (SELECT EMP_LOCK_ID FROM DELETED) SET @NEW_LOCK_ID = (SELECT EMP_LOCK_ID FROM INSERTED) IF @OLD_LOCK_ID <> @NEW_LOCK_ID BEGIN RAISERROR('Record Modified by different user.', 16, 1) ROLLBACK TRANSACTION END ELSE BEGIN SET @NEXT_NUM = (convert(decimal(17,12), getdate())) SET @NEW_NUM = @NEXT_NUM * 1000 UPDATE EMPLOYEE SET EMP_LOCK_ID = (@NEW_NUM - floor(@NEW_NUM)) * 1000000000 FROM EMPLOYEE, INSERTED WHERE EMPLOYEE.EMP_EMPLOYEE_ID = INSERTED.EMP_EMPLOYEE_ID END
I am struggling with syntax for an update statement. Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations] SET [HasHistory]= 1 FROM [Tbl_Relations] r INNER JOIN (SELECT RelationID FROM vw_Invoice_history GROUP BY RelationID ) inv ON r.RelationID= inv.RelationID
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'
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1 WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
I'm using SqlBulkCopy. Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks
I have encountered a very frustrating situation when trying to use SQLBulkCopy. I have two excel files that I am trying to import into two tables in an MSSQL Server 2005 Express DB. One excel file has 5,000 rows, while the other file has 500,000 rows.I was able to import the smaller file successfully using this vb.net code: Protected Sub L26ExcelToSQL() 'Declare Variables Dim sSQLTable As String = "Local26Members" Dim sExcelFileName As String = "Full Local 26 List Formatted.xls" Dim sWorkbook As String = "[Sheet1$]"
Dim sSqlConnectionString As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString.ToString 'Execute a query to erase any previous data from our destination table Dim sClearSQL = "DELETE FROM " & sSQLTable Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString) Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn) SqlConn.Open() SqlCmd.ExecuteNonQuery() SqlConn.Close() 'Series of commands to bulk copy data from the excel file into our SQL table Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString) Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn) OleDbConn.Open() Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader() Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString) bulkCopy.DestinationTableName = sSQLTable bulkCopy.WriteToServer(dr) OleDbConn.Close() End Sub However, when I tried to import the 500,000 row excel file, I got the following error: Server Error in '/L26' Application.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
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.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
[SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +44 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +79 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1336 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) +151 _Default.CSVToSQL() in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:440 _Default.ButtonTest3_Click(Object sender, EventArgs e) in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:905 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 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) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 After I received this error message, I tried viewing my database through the MSSQL Control Panel utilized by my hosting provider (WebHost4Life). However, I was unable to connect to the database and received this error: ___________________Microsoft OLE DB Provider for SQL Server error '80040e14' Database 1496 cannot be autostarted during server shutdown or startup. /getDBinfo.asp, line 29
_____________________ Now here is the most frustrating/mysterious part. I figured that maybe the error message were a result of the large size of the second excel file, so just for testing purposes, I created a new table in my MSSQL database. The table just has two fields, both set to varchar(50). I then created a test excel file, that had one row with the word "test" in the first and second column. When I tried using the code above to import the test excel data into the test table, I got the same exact error as I did with the 500,000 row file!Please help, I'm really stumped and I am not sure when I am having so much trouble replicating the success I had the 5,000 row file. Any suggestions are much apprecaited. -Bryan
Hi There, I'm trying to use a sql bulk copy to transfer data from xml file to a table in one of my page. In this page I'm doing 2 database related. The first is a simple insert that will return a value and the second one is the sql bulk copy data transfer. I'm using the same connection for both of them and the sql bulk copy always give me a "login failed" error while the insert is fine. Do I need to set a specific setting for the sql server account so that it can use sql bulk copy? Thank you
Hi, Iam using SqlBulkCopy to copy data of all the tables from one database to another database. SqlBulkCopy runs just fine but it throws exception for one of the tables which is having computed column.
Next For one of the tables iam getting the following error The column "Col4" cannot be modified because it is either a computed column or is the result of a UNION operator
Since iam fetching table names at runtime its not possible to use columnMappings.
I have two tables one is riders: riderid, firstname, lastname, addressid The other is address: addressid, address1, city, state...and foolishly riderid Originally, I saved the riderid as a FK in the address table, not the addressid in the ridertable. Not the worst thing, but now I want to use the address table for more than just riders. So, I should be storing the addressid in the riders table, not the riderd in the address. The address ID in the riders table is all NULLS. I thought that I could do a quick UPDATE to fix this... update riders SET address = ( SELECT addressid FROM address WHERE address.riderID = riders.riderid ) But this returns the error: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted .... and more... I can see where the query rerturns more than 1 row, how do you tie that to individual riders?
