Insert Produces Error

Jul 23, 2005

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table


Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

Todd

View 1 Replies


ADVERTISEMENT

Sp_refreshview Produces An Error

Nov 7, 2006



When I execu sp_refreshview,

I got this error message on a valid view on SQL 2005 express edition.

Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1

Object 'vName' does not exist or is invalid for this operation.



Next, I got a similar error on SQL 2005 for a valid view.

Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1

Object 'vNamehere' does not exist or is invalid for this operation.

Is this Microsoft Bug?

View 1 Replies View Related

VB6 ADO 2.7 SQL 6.5 Error Not Raised If Execute Produces A Warning First

May 15, 2002

I am running an environment as below

ADO 2.7
SQL Sever OLE DB Provider
VB 6
SQL Server 6.5 SP 5 A Update

If I run a stored procedure that contains a Print statement or gives a warning. Then any error that occurs after that is ignored. No error state is raised in VB and the Errors collection only contains the print or warning.

Can anyone tell me why?

Thanks

Chris

View 1 Replies View Related

SQL Startup Produces Resource Manager Error

Oct 26, 2007

We have two instances of SQL Server 2005 - SP1 installed on one server. The default instance starts very slowly. When looking at the log I can see the delay is due to Resource Manager based upon the following error.

Message
Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)

What does this mean?

What would cause this to happen?

How do I resolve the problem?

Thanks, Dave

View 8 Replies View Related

SP Produces Intermingled Results

Jan 29, 2004

I have a custom SP running from a PB application. When I am retrieving information for a certain client, a co-worker decides to run the same custom SP for another client before my results are displayed. When both results display, I end up with certain pieces of data from his client and he ends up with certain pieces of data from my client. What code in my SP do I need to prevent this from happening?

View 14 Replies View Related

CHECKSUM , Produces Same Hash For Two Different Inputs. Is This Right?

Jul 25, 2007

Hi,

We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.

We are using following type of inside the trigger.



UPDATE [dbo].[Hospital]

SET

[HospitalID]= I.[HospitalID],

[Name]= I.[Name],

[HospitalNumber]= I.[HospitalNumber],

[ServerName] = I.[ServerName],

[IsAuthorized]= I.[IsAuthorized],

[IsAlertEnabled]= I.[IsAlertEnabled],

[AlertStartDate]= I.[AlertStartDate],

[AlertEndDate]= I.[AlertEndDate],

[IsTraining]= I.[IsTraining],

[TestMessageInterval]= I.[TestMessageInterval],

[DelayAlertTime]= I.[DelayAlertTime],

[IsDelayMessageAlert]= I.[IsDelayMessageAlert],

[IsTestMessageAlert]= I.[IsTestMessageAlert],

[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],

[IsWANDownAlert]= I.[IsWANDownAlert],

[IsWANUpAlert]= I.[IsWANUpAlert],

[CreateUserID]= Hospital.[CreateUserID],

[CreateWorkstationID]= Hospital.[CreateWorkstationID],

[CreateDate]= Hospital.[CreateDate] ,

/* record created date is never updated */

[ChangeUserID]= suser_name(),

[ChangeWorkstationID]= host_name(),

[ChangeDate]= getdate() ,

/* Updating the record modified field to now */

[CTSServerID]= I.[CTSServerID]

FROM inserted i

WHERE

i.[HospitalID]= Hospital.[HospitalID]

AND binary_checksum(

Hospital.[HospitalID],

Hospital.[Name],

Hospital.[HospitalNumber],

Hospital.[ServerName],

Hospital.[IsAuthorized],

Hospital.[IsAlertEnabled],

Hospital.[AlertStartDate],

Hospital.[AlertEndDate],

Hospital.[IsTraining],

Hospital.[TestMessageInterval],

Hospital.[DelayAlertTime],

Hospital.[IsDelayMessageAlert],

Hospital.[IsTestMessageAlert],

Hospital.[IsUnAuthorizedMessageAlert],

Hospital.[IsWANDownAlert],

Hospital.[IsWANUpAlert]) !=

binary_checksum(

I.[HospitalID],

I.[Name],

I.[HospitalNumber],

I.[ServerName],

I.[IsAuthorized],

I.[IsAlertEnabled],

I.[AlertStartDate],

I.[AlertEndDate],

I.[IsTraining],

I.[TestMessageInterval],

I.[DelayAlertTime],

I.[IsDelayMessageAlert],

I.[IsTestMessageAlert],

I.[IsUnAuthorizedMessageAlert],

I.[IsWANDownAlert],

I.[IsWANUpAlert]) ;





Here is the checksum example which produces same results for two different input.





DECLARE @V1 VARCHAR(10)

DECLARE @V2 VARCHAR(10)

SELECT @V1 = NULL, @V2=NULL

SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@V1,@V2,0,30,180,1,0,1,1,1),

binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@V1,@V2,0,30,180,1,1,1,1,1)



Lookat the two binary_checksum above, they are different and should not match, but they both return same value.



Can someone please provide some info on these.

View 4 Replies View Related

Pivot Transformation Produces Duplicates

Apr 21, 2008

Hey,

I am using the pivot component in SSIS to pivot a set of data, and up until now it has worked fine. However lately it has started to produce duplicates, meaning 2 rows for a combination of the keys (type 1) used. It is completely random, meaning a combination of keys for one run can work correctly, and in the next run it inserts an extra record for the combination for just one of the pivoted values. This also means that for a batch of 10 mill rows input the output is completely random, because the number of duplicates vary.

Are there any memory restrictions or similar that causes this, or do you guys have any idea what might be wrong?

Thanks in advance

View 5 Replies View Related

BCP Batch File Produces Sporatic Corrupt Data

Aug 16, 2006

Matt writes "Greetings! Warning, I'm a rookie. I wrote a stored procedure to pull data in order to do a nightly export/import from one system to another. I have a batch file that looks like this:

bcp "exec WinSNAP_retrieveStudents '0607'" queryout c:WinSNAPData06.txt -c -U user -P password

Sometimes, the file works and I get perfectly formed data, with everything just as I've requested (mostly basic demographic information: names, addresses, etc.).

But, other times the output file contains nothing but garbage characters, like this:

剒乏†††††††††ठ䅍啎䱅†††䴉㐉㐷‰䕓䄠䅐䡃⁅剄

The file size looks right, but it contains nothing but characters like this from beginning to end. I can find no pattern as to why/when good data gets pulled versus the corrupt data. I can run the batch file one minute and get good data, and run it the next minute and it's all corrupt. We have the batch file scheduled late at night when no users are online, and I get the same results -- one day it works, the next it doesn't.

Forgive me if this is a well-documented issue -- my searches so far haven't turned up a thing!

Thanks much for any advice you can provide!!

Matt Smith
DeSoto County School District
Arcadia, FL"

View 1 Replies View Related

Produces Multiple Lines, Need To Populate Single Line

Mar 11, 2008

I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.

What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX



If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.



After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.



CASE HB.PLAN_TYPE

WHEN '10' THEN 'Y'

ELSE 'N' END AS 1A,

CASE HB.PLAN_TYPE

WHEN '10' THEN 'N'

ELSE ' ' END AS 1B,

' ' AS 1C,

CASE HB.PLAN_TYPE

WHEN '10' THEN HB.BENEFIT_PLAN

ELSE ' ' END AS 1D,

CASE HB.PLAN_TYPE

WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'

WHEN HB.COVRG_CD ='2' THEN 'YY'

WHEN HB.COVRG_CD ='3' THEN 'ZZ'

ELSE 'WW' END)

ELSE ' ' END AS 1E,



It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

Y N __ MD XX N __ __ __ __ N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ Y N __ MD XX N __ __ __ __ N __ __ __ __



1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E

== == == == == == == == == == == == == == == == == == == ==

N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX



While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.

View 5 Replies View Related

SQL Server 2000 Produces A Shared DB Lock With A Use DB. Normal?

Nov 20, 2007

Hi Folks,

I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.

Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:

use Master
go
sp_lock
go
use Experience
go
sp_lock
go

which results in:

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 1 85575343 0 TAB IS GRANT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 13 0 0 DB S GRANT
52 1 85575343 0 TAB IS GRANT

My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open

Question 1:
The way I read this is: the "use Experience" statement creates a shared database lock.
Am I reading this right?


Question 2:
I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained.
Should I be concerned about these locks? Is this expected behavior?

Thanks Much in advance.

- D



View 3 Replies View Related

Left Outer Join Produces Cartesian Product

Feb 9, 2006

Tables have 100K rows. Inner join produces 99k. The outer join should be 100K. It is 1000 times larger.

If I do a SELECT DISTINCT, the right # rows are returned (100K)

What gives?

SELECT tblEarth.key1, tblEarth.key2a, tblEarth.key2b
FROM ((tblEarth INNER JOIN tblMoon ON Mid(tblEarth.key1,1,6) = tblMoon.key1) LEFT OUTER JOIN
tblVenus ON (tblEarth.key2b = tblVenus.key2b)
AND (tblEarth.key2a = tblVenus.key2a)) LEFT OUTER JOIN
tblMars ON tblEarth.key2a = tblMars.key2a;

Laurence

View 1 Replies View Related

Regression Testing A Stored Procedure That Produces Multiple Rowsets

Nov 1, 2006

How do I write a regression test for a stored proc that produces multiple rowsets via multipl e select queries? E.g.
CREATE PROCEDURE myProc AS
SELECT 'Some stuff', GETDATE()
SELECT 'Some more stuff'

For single-select procs, I can create a temp table and INSERT #temp EXEC myProc, then evaluate the contents of the table to verify correct behavior, but that doesn't work in this case.

View 1 Replies View Related

XML Source Produces DT_UI8 Key Columns - Which SQL Server Data Type To Store?

Aug 12, 2007

I'm using the XML Source to process a hierarchical set of XML. As such, the XML Source creates keys to maintain the hierarchy. This is very convenient, and keeps me from having to invent my own keys.

The problem is that the datatype of these keys defaults to DT_UI8. Which SQL Server 2005 datatype should I use to store these values in my staging tables? BIGINT corresponds to DT_I8, which can't accept DT_UI8 values.

View 8 Replies View Related

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

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

Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005

Aug 7, 2007

We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.


The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)


Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?


declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID


QM_UID count(*)

25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3

...



--- Script to replicate problem



-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant

-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error

-- at 100000 rows but does at 1000000 rows.



-- Create a table

CREATE TABLE tmp (
[QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
[QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
[QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
[QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
[QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
[QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
[QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
[QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
[QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
[QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
[QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
[QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
[QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
[QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
[QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
[QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
[QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
[QM_HELD] [smallint] DEFAULT 0 NOT NULL,
[QM_PROOF] [char](12) DEFAULT '' NOT NULL,
[QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
[QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
[QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
[QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
[QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
[QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_TITLE] [char](72) DEFAULT '' NOT NULL,
[QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
[QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
[QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
[QM_PHONE] [char](22) DEFAULT '' NOT NULL,
[QM_FAX] [char](22) DEFAULT '' NOT NULL,
[QM_ORDER] [char](20) DEFAULT '' NOT NULL,
[QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
[QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
[QM_REP] [char](12) DEFAULT '' NOT NULL,
[QM_REP_1] [char](12) DEFAULT '' NOT NULL,
[QM_REP_2] [char](12) DEFAULT '' NOT NULL,
[QM_REP_3] [char](12) DEFAULT '' NOT NULL,
[QM_REP_4] [char](12) DEFAULT '' NOT NULL,
[QM_REP_5] [char](12) DEFAULT '' NOT NULL,
[QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
[QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
[QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
[QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
[QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
[QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
[QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
[QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
[QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
[QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
[QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
[QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
[QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
[QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
[QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
[QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
[QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
[QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
[QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
[QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
[QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
[QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
[QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
[QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
[QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
[QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
[QM_CURR] [char](6) DEFAULT '' NOT NULL,
[QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
[QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
[QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
[QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
[QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
[QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
[QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
[QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
[QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_CODE] [char](36) DEFAULT '' NOT NULL,
[QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
[QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
[QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
[QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
[QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
[QM_UID] [int] DEFAULT 0 NOT NULL,
[QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
[QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]


GO

-- Create an index on the table

CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]

GO



-- Populate the table

declare @Counter as int

SET NOCOUNT ON

set @Counter = 1

while @Counter < 100000

begin

insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);

set @Counter = @Counter + 1

end

GO

-- Update QM_UID to a sequential value

declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;



-- Find rows with a duplicate QM_UID (there should be no duplicate)

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID

--drop table tmp



-- output from select @@VERSION

-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

View 4 Replies View Related

Running A Large Number Of SSIS Packages (with Dtexec Utility) In Parallel From A SQL Server Agent Job Produces Errors

Jan 11, 2008

Hi,

I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.

I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.

The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.

The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).

We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).

I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.

In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).

If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.

The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.

Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.

I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).

Additional information:

The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.

The request:

Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!

Eero Ringmäe

View 2 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

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

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

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

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 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

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

Phantom Row Insert After Insert Error..... Please Help!!

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

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')

[code]...

View 5 Replies View Related

Error On Insert

Jul 12, 2006

Hi All
Here is the error I am getting:
"No mapping exists from object type System.Web.UI.WebControls.DropDownList to a known managed provider native type."
Here is my code:Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click'set variables for Items to save to Time SheetDim strUserName As String '@usernameDim strWeek As String '@weekDim strDate As String '@dateDim strStartTime As String '@starttimeDim strEndTime As String '@endtimeDim intHeatTicket As Integer '@heatticketDim strDesc As String '@descriptionDim strTakenAs As String '@takenasDim strDinner As String '@dinnerDim dblHours As Double '@hoursDim dblRate As Double '@rateDim strDueDate As String '@duedate'set variables for SPstrUserName = User.Identity.NamestrWeek = CStr(ddlWeek.SelectedItem.Text)strDate = CStr(lblSelectDate.Text)strStartTime = txtStartTime.TextstrEndTime = txtEndTime.TextintHeatTicket = txtHeatTicket.TextstrDesc = txtReason.TextstrTakenAs = CStr(ddlTakenAs.SelectedItem.Text)strDinner = CStr(ddlDinner.SelectedItem.Text)dblHours = txtHours.TextdblRate = CDbl(ddlRate.SelectedItem.Text)strDueDate = CStr(ddlDueDate.SelectedItem.Text)'set connection stringDim errstr As String = ""Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SP to create new blank time sheetDim cmdcommand = New SqlCommand("InsertReportLineItem", conn)cmdcommand.commandtype = CommandType.StoredProcedurecmdcommand.parameters.add("@LineUserName", strUserName)cmdcommand.parameters.add("@LineWeek", strWeek)cmdcommand.parameters.add("@LineDate", strDate)cmdcommand.parameters.add("@LineStartTime", strStartTime)cmdcommand.parameters.add("@LineEndTime", strEndTime)cmdcommand.parameters.add("@LineHeatTicket", intHeatTicket)cmdcommand.parameters.add("@LineTicketDescription", strDesc)cmdcommand.parameters.add("@LineTakenAs", strTakenAs)cmdcommand.parameters.add("@LineDinnerPremium", ddlDinner)cmdcommand.parameters.add("@LineRate", dblHours)cmdcommand.parameters.add("@Rate", dblRate)cmdcommand.parameters.add("@LineReportDueDate", ddlDueDate)Try'open connection hereconn.Open()'Execute stored proccmdcommand.ExecuteNonQuery()Catch ex As Exceptionerrstr = ""'An exception occured during processing.errstr = "Exception: " & ex.Message.ToString()'MsgBox("This is your Error: " & errstr, MsgBoxStyle.Exclamation)Finally'close the connection immediatelyconn.Close()End TryEnd Sub Here is my Stored Procedure:
 
ALTER PROCEDURE dbo.InsertReportLineItem
(
@LineUserName varchar(50),
@LineWeek int,
@LineDate datetime,
@LineStartTime datetime,
@LineEndTime datetime,
@LineHeatTicket int,
@LineTicketDescription varchar(MAX),
@LineTakenAs varchar(50),
@LineDinnerPremium varchar(50),
@LineHours float,
@LineRate float,
@LineReportDueDate datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[ReportLineItems] ([LineUserName], [LineWeek], [LineDate], [LineStartTime], [LineEndTime], [LineHeatTicket], [LineTicketDescription], [LineTakenAs], [LineDinnerPremium], [LineHours], [LineRate], [LineReportDueDate]) VALUES (@LineUserName, @LineWeek, @LineDate, @LineStartTime, @LineEndTime, @LineHeatTicket, @LineTicketDescription, @LineTakenAs, @LineDinnerPremium, @LineHours, @LineRate, @LineReportDueDate);

SELECT LineId, LineUserName, LineWeek, LineDate, LineStartTime, LineEndTime, LineHeatTicket, LineTicketDescription, LineTakenAs, LineDinnerPremium, LineHours, LineRate, LineReportDueDate FROM ReportLineItems WHERE (LineId = SCOPE_IDENTITY())
  

View 7 Replies View Related

Sql Error - Insert Into

Nov 21, 2006

I am trying to insert information into a database calls FSI and a table called Racks.  Below is the error I recieve. 
 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
 
The sub with the error is inserted at the bottom of this post.  I have used the exact same code to inset into other tables and it worked but for some reason this one is not working.  If you would like to see the entire file please let me know and I will email it to you. or post it on here.
 The field names for the table are (beside the field name is datatype. Im using an Access table for now).
RackID - autonumber
Container - text
 Date - Date/Time
CustomerID - number (all numbers are set to interger)
Comments - text
Size - number
Bars - number
 If you need any further database information it can be seen at http://paws.wcu.edu/tf32761/database/tool_database.aspx  The link for the actuall page is http://paws.wcu.edu/tf32761/fsi/racks.aspx feel free to insert dumby data to see the stack trace and the entire error message.
 
What is wrong with the syntax of my sql insert statement? 1 '---------------------------------------------
2 ' name: BuildCommandObject() As OleDbCommand
3 '---------------------------------------------
4 Function BuildCommandObject(ByVal strDate As String, ByVal strContainer As String, ByVal decCustomerID As Decimal, ByVal strComments As String, ByVal decSize As Decimal, ByVal decBars As Decimal) As OleDbCommand
5
6 Dim strSQL As String
7 Dim objCommand As New OleDbCommand()
8
9 'Build sql string
10 strSQL = "INSERT INTO Racks " & _
11 "(Date, Container, CustomerID, Comments, Size, Bars)" & _
12 " VALUES(?, ?, ?, ?, ?, ?)"
13
14 Trace.Warn("strSQL=" & strSQL)
15
16 objCommand.CommandText = strSQL
17
18 'Build parameters and add to parameters collection
19 objCommand.Parameters.Add("@Date", OleDbType.VarChar, 50).Value = strDate
20 objCommand.Parameters.Add("@Container", OleDbType.VarChar, 50).Value = strContainer
21 objCommand.Parameters.Add("@CustomerID", OleDbType.Decimal, 50).Value = decCustomerID
22 objCommand.Parameters.Add("@Comments", OleDbType.VarChar, 255).Value = strComments
23 objCommand.Parameters.Add("@fldItemNumber", OleDbType.Decimal, 50).Value = decSize
24 objCommand.Parameters.Add("@fldItemNumber", OleDbType.Decimal, 50).Value = decBars
25
26 Return objCommand
27
28 End Function
 
Thanks for the assistance.  Hope everyone has a great Thanksgiving holiday.
TJ
 
 
 

View 2 Replies View Related

SQL INSERT INTO Error

Dec 5, 2006

I've been staring at this code and I am at a lost as to what is the problem, Could someone look at it and let me know? The error I get  is "Error in INSERT INTO syntax"
thanks1 Dim sConnStr As String = ConfigurationManager.ConnectionStrings("accmon").ConnectionString
2 Dim MyConn As New OleDbConnection(sConnStr)
3
4 Dim MySQL As String = "INSERT INTO icrRenewal (month, year, oa, omb_number, current_Inventory, 60fr) " & _
5 "Values (@month, @year, @oa, @omb_number, @currentInv, @60fr)"
6 Dim Cmd As New OleDbCommand(MySQL, MyConn)
7
8 With Cmd.Parameters
9 .Add(New OleDbParameter("@month", ddlMonth.SelectedItem.Value))
10 .Add(New OleDbParameter("@year", ddlYear.SelectedItem.Value))
11 .Add(New OleDbParameter("@oa", ddlOA.SelectedItem.Value))
12 .Add(New OleDbParameter("@omb_number", txtOMBNumber.Text))
13 .Add(New OleDbParameter("@currentInv", txtcurrentInv.Text))
14 .Add(New OleDbParameter("@60fr", txt60fr.Text))
15 End With
16 MyConn.Open()
17 Cmd.ExecuteNonQuery()
18 MyConn.Close()
19
  

View 5 Replies View Related

INSERT Error Using C#/Asp.Net/SQL

Oct 18, 2007

I have been learning how to retrieve data from my SQL database using C#/Asp.Net. I am able to read the data and I am able to update my data. I am now wanting to insert into my database. I think I've gotten everything under control except for one error. It's something I don't understand and would like some assistance with.I assumed that as soon as I inserted something into a table that the auto-increment field would auto increment. From the error I take it that I am wrong.  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_inventoryTbl_categoryTbl". The conflict occurred in database "Chaos", table "dbo.categoryTbl", column 'categoryID'.The statement has been terminated.That foreign key is categoryID but it's a number I am trying to insert that does exist in the other table, categoryID 11. The auto increment field is ID, in the inventoryTbl. The id of a new item that gets entered is auto incremented for the new item. I am not sure whether it's an error related to that I am not inserting a number into the id field of a newly added item into the table or whether it's come kind of constraint rule that I do not understand concerning Insertion and relationships between two tables.  Here is the code:    protected void bttnApplyChanges_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["ChaosConnectionString1"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand comm = new SqlCommand("INSERT INTO inventoryTbl (categoryID, item, description, price, image_url, qty, page_url) VALUES (@category, @item, @desc, @price, @imageurl, @quantity, @page)", conn);
// Add command parameters
comm.Parameters.Add("@category", System.Data.SqlDbType.SmallInt);
comm.Parameters["@category"].Value = txtbx_itemCategory.Text;
comm.Parameters.Add("@item", System.Data.SqlDbType.NVarChar);
comm.Parameters["@item"].Value = txtbx_itemName.Text;
comm.Parameters.Add("@desc", System.Data.SqlDbType.NVarChar);
comm.Parameters["@desc"].Value = txtbx_ItemDesc.Text;
comm.Parameters.Add("@price", System.Data.SqlDbType.Money);
comm.Parameters["@price"].Value = txtbx_ItemPrice.Text;
comm.Parameters.Add("@imageurl", System.Data.SqlDbType.NVarChar);
comm.Parameters["@imageurl"].Value = txtbx_itemImgUrl.Text;
comm.Parameters.Add("@quantity", System.Data.SqlDbType.SmallInt);
comm.Parameters["@quantity"].Value = txtbx_qty.Text;
comm.Parameters.Add("@page", System.Data.SqlDbType.NVarChar);
comm.Parameters["@page"].Value = txtbxPageUrl.Text;

int rowsAffected = comm.ExecuteNonQuery();
bttnAddItem.Text = rowsAffected.ToString() + " row affected: Success";

comm.ExecuteNonQuery();
conn.Close();
}  

View 14 Replies View Related

Insert Error

Dec 24, 2007

Hi,
I'm getting an Error on this code - "no overlod for method 'select' takes '0' arguments
what should I do?  protected void Button2_Click(object sender, EventArgs e)
{SqlDataSource SqlDataSource6 = new SqlDataSource();
SqlDataSource6.ConnectionString = ConfigurationManager.ConnectionStrings["DolevDBConnectionString"].ToString();SqlDataSource6.SelectCommandType = SqlDataSourceCommandType.Text;
 SqlDataSource6.SelectCommand = "SELECT * FROM Employees WHERE (EE_ID = @EE_ID)";
SqlDataSource6.SelectParameters.Add("EE_ID", TextID.Text);int Rowseffected = 0;
try
{
Rowseffected = SqlDataSource6.Select();
}
finally
{SqlDataSource6 = null;
}if (Rowseffected == 1)
{Server.Transfer("Default6.aspx");
}
else
{Label20.Text = "העובד כבר קיי×? במערכת";
}
}

View 4 Replies View Related

What Does This Insert Error Mean?

Jun 4, 2008

 Msg 8101, Level 16, State 1, Line 10
An explicit value for the identity column in table '@tbl' can only be specified when a column list is used and IDENTITY_INSERT is ON.  
 and this is my query..Declare @tbl table
(
Id int identity(1,1),
PlanName varchar(200),
ClientPlan Varchar(10),
AddDate datetime,
LastchangeDate datetime
)

Insert into @tbl
Select
c.Pl_Id,
c.Pl_Name,
c.Pl_Number,
c.pl_Create_Date,
c.pl_Modify_Date

--pf.PF_LI_ID,
-- f.FundId
From
Plan c
Where

(c.PL_CL_ID = 396) OR
(c.PL_CL_ID = 410) OR
(c.PL_CL_ID = 411) OR
(c.PL_CL_ID = 412) OR
(c.PL_CL_ID = 413) OR
(c.PL_CL_ID = 414)

ORder By c.Pl_ID

 
any help will be appreciated.
Regards,
Karen

View 5 Replies View Related

Help With Error While Trying To Insert

Jun 5, 2008

Hello there,I have now looked at his code for an hour I think, and I cant simpy not see what's wrong: 1 Try
2 'aspnet_Membership
3 nonqueryCommand.CommandText = "CREATE TABLE aspnet_Membership (ApplicationId uniqueidentifier NOT NULL, UserId uniqueidentifier NOT NULL PRIMARY KEY, Password nvarchar(128) NOT NULL, PasswordFormat integer NOT NULL, PasswordSalt nvarchar(128) NOT NULL, MobilePIN nvarchar(16), Email nvarchar(256), LoweredEmail nvarchar(256), PasswordQuestion nvarchar(256), PasswordAnswer nvarchar(128), IsApproved bit NOT NULL, IsLockedOut bit NOT NULL, CreateDate datetime NOT NULL, LastLoginDate datetime NOT NULL, LastPasswordChangedDate datetime NOT NULL, LastLockoutDate datetime NOT NULL, FailedPasswordAttemptCount integer NOT NULL, FailedPasswordAttemptWindowStart datetime NOT NULL, FailedPasswordAnswerAttemptCount integer NOT NULL, FailedPasswordAnswerAttemptWindowStart datetime NOT NULL, Comment ntext)"
4 Console.WriteLine(nonqueryCommand.CommandText)
5 Session("Tables") = Session("Tables") + "Number of Rows Affected with table aspnet_Membership is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
6
7 nonqueryCommand.CommandText = "INSERT INTO aspnet_Membership(ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, Email, LoweredEmail, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) VALUES ('69272d43-c1d4-46d5-af8b-5f638882fb55','b0e198c6-1fbb-4aa6-82a6-32c2bc60d097','5gCmCptv9egj+IkDHk1yeozjp6I=','1','cKURew9OVHBmK46GTl8ykg==','din@email.dk','din@email.dk','True','False','03-03-2008 16:05:51','05-06-2008 12:28:37','16-05-2008 22:58:28','01-01-1754 00:00:00','0','01-01-1754 00:00:00','0','01-01-1754 00:00:00')"
8 Console.WriteLine(nonqueryCommand.CommandText)
9
10 Session("Tables") = Session("Tables") + "Number of Rows Affected with table aspnet_Membership is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
11
12 Catch ex As SqlException
13 Session("FEJL") = Session("FEJL") + "<br />" + ex.ToString() + "<br />"
14 End Try
 
I this error show up:System.Data.SqlClient.SqlException: 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. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at updates_100_2.Page_Init(Object sender, EventArgs e) in http://server//admin/NewSystem/1.0.0/2.aspx.vb:line 53
Anyone who can see what I'm doing wrong in this insert statement??

View 3 Replies View Related

Insert Error

Dec 19, 2004

I am inserting information into my SQL table and receiving the following error:

error:
--------------------------------------------------------------------------------

INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'.
Exception Details: System.Data.SqlClient.SqlException: INSERT permission denied on object 'tblMaster', database 'master', owner 'dbo'

---------------------------------------------------------------------------------

Here is my code: (I am only trying to insert one field. ):

Dim myConnectionString As String

If myConnectionString = "" Then
myConnectionString = "server=Discovery3;database=master;trusted_Connection=true"
End If

Dim myConnection As New Data.SqlClient.SqlConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO tblMaster(TITLE) values('" & TextBox1.Text & "')"
Dim myCommand As New Data.SqlClient.SqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myConnection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Any help would be appreciated.

Thanks

View 1 Replies View Related

Error On Insert...

May 22, 2005

I'm getting an error on the insert... I recently when to switch over from the @param.Oledb type and am now using the ? for the values.... Well I switched my updates and now am working on the inserts, but I am getting the error from this statement. I tried taking off the VALUES portion, but that didn't work either...
queryString = "INSERT INTO [griff30_24_stats] ([W_Rec], [Opponent], [L_Rec], [points], [done]) VALUES(?, ?, ?, ?,)"
Dim cmd as new OleDbCommand(queryString,dbConnection)
cmd.Parameters.Add("W_Rec",OleDbType.VarChar).Value = "Y"
'
'
'

View 4 Replies View Related

Insert Error

Jun 14, 2000

What would produce this error message? my data types are correct as well as
the number of columns & values.

insert into table1(field1,field2,field3,field4,field5)
values(10,11,'abc',2000-06-14,1,1)

field1 numeric
field2 numeric
field3 varchar
field4 datetime
field5 numeric

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

View 2 Replies View Related

Insert Error

Nov 5, 1999

When in Query Analyzer performing the following insert
command on the PUBS database (SQL 7.0 sp1)
"insert into address_list
select au_lname + ',' + au_fname, address, city, state
from authors"
I receive the following error message:
"Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated."

If I just run the select portion of the query, it works
fine. Can anyone tell me what the problem is?

Thanks!
Toni

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved