Insert Not Working
May 15, 2004
Ok, changing over from access to sql server 2000 and adjusting code as needed.
Reading and deleting just fine, but insert is not working and don't know why.....
sql = "Insert Into tblUser (" _
& "fldUserEmail) values ('" _
& strUserEmail & "')"
Dim DBCommand As SqlCommand = New SqlCommand(sql, conn)
Dim insSDA As SqlDataAdapter = New SqlDataAdapter()
insSDA.InsertCommand = DBCommand
DBCommand.Connection.Open()
DBCommand.ExecuteNonQuery()
conn.Close()
strUserEmail is from a previous select in the code.
This should work but it's not and have checked permissions on the table as well.
Thanks,
Zath
View 1 Replies
ADVERTISEMENT
May 15, 2007
Nothing is being inserted into the database. Â My code is below:Line 12: add_friend_source.InsertCommand = "INSERT INTO Friends (UserID, UserName, FriendName, AddedOn, IP) VALUES (@UserID, @UserName, @FriendName, @AddedOn, @IP)"Line 13: detailsview_addfriend.DataSource = add_friend_sourceLine 14: add_friend_source.Insert()Line 15: End Sub
View 8 Replies
View Related
Feb 27, 2008
What am I doing wrong here? I want to insert the current date into Companies.EmailDate given the Company ID ( C_ID )
error message: Incorrect syntax near the keyword 'WHERE'
PROCEDURE EmailSentDate @CID intASINSERT INTO tblCompanies ( EmailDate )VALUES(GetDate())WHERE Companies.C_ID = @CID RETURN
Thank you
View 3 Replies
View Related
Mar 21, 2008
Hey people im just wondering if someone could help me out with this Insert query im doing from one of the learn asp videos. I have a table called EquipmentBooking and it contains the following fields
<teachingSession, int,> <staff, char(5),> <equipment, varchar(15),> <bookedOn, datetime,> <bookedFor, datetime,> now im doing the following Insert statement in Visual Studio 2005 when the submit button is clicked but all I get is the catched error exception and I just can working out why. Can someone help? heres the code im using
Dim WebTimetableDataSource As New SqlDataSource()WebTimetableDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("WebTimetableConnectionString").ToString()
WebTimetableDataSource.InsertCommandType = SqlDataSourceCommandType.Text
WebTimetableDataSource.InsertCommand = "INSERT INTO EquipmentBooking (teachingSession, staff, equipment, bookedOn, bookedFor) VALUES (@TeachingDropDown, @StaffDropDown, @EquipmentDropDown, @DateTimeStamp, @DateTextBox)"WebTimetableDataSource.InsertParameters.Add("TeachingDropDown", TeachingDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("StaffDropDown", StaffDropDown.Text)WebTimetableDataSource.InsertParameters.Add("EquipmentDropDown", EquipmentDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now())WebTimetableDataSource.InsertParameters.Add("DateTextBox", DateTime.Now())
Dim rowsAffected As Integer = 0
Try
rowsAffected = WebTimetableDataSource.Insert()Catch ex As Exception
Server.Transfer("problem.aspx")
Finally
WebTimetableDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("confirmation.aspx")
End If
View 5 Replies
View Related
Jan 24, 2004
This is a real head ache. Nothing I do to add a record to my SQL2k Database wil work.
I'm logged into it as "sa".
I've Tried Stored Procedures:
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("AddLender", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100)
parameterUserName.Value = userName
myCommand.Parameters.Add(parameterUserName)
Dim parameterName As New SqlParameter("@Name", SqlDbType.NVarChar, 100)
parameterName.Value = name
myCommand.Parameters.Add(parameterName)
Dim parameterCompany As New SqlParameter("@Company", SqlDbType.NVarChar, 100)
parameterCompany.Value = Company
myCommand.Parameters.Add(parameterCompany)
Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 100)
parameterEmail.Value = email
myCommand.Parameters.Add(parameterEmail)
Dim parameterContact As New SqlParameter("@Contact", SqlDbType.NVarChar, 100)
parameterContact.Value = contact
myCommand.Parameters.Add(parameterContact)
Dim parameterPhone As New SqlParameter("@Phone", SqlDbType.NVarChar, 100)
parameterPhone.Value = Phone
myCommand.Parameters.Add(parameterPhone)
Dim parameterFax As New SqlParameter("@Fax", SqlDbType.NVarChar, 100)
parameterFax.Value = Fax
myCommand.Parameters.Add(parameterFax)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Return CInt(parameterItemID.Value)
The Sproc.......
CREATE PROCEDURE AddLender
(
@Username nvarchar(100),
@ModuleID int,
@Email nvarchar(100),
@Name nvarchar(100),
@Rep nvarchar(250),
@Phone nvarchar(250),
@Fax nvarchar(250),
@City nvarchar (100),
@State nvarchar(100),
@ItemID int OUTPUT
)
AS
INSERT INTO Lenders
(
Email,
Name,
Rep,
Phone,
Fax,
CIty,
State
)
VALUES
(
@Email,
@Name,
@Rep,
@Phone,
@Fax,
@City,
@state
)
SELECT
@ItemID = @@Identity
GO
I get no Errors... I've run SQLProfiller and I don;t even see it run...
I also tried the method..
Dim strSql As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter
strSql = "Select ItemId,ModuleId,Name,Rep, Email,Phone,Fax,City,State,Address From Portal_Lenders;"
objConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionStringOledb"))
objAdapter = New OleDbDataAdapter(strSql, objConnection)
objAdapter.Fill(objDataSet, "Lenders")
Dim objtable As DataTable
Dim objNewRow As DataRow
objtable = objDataSet.Tables("Lenders")
objNewRow = objtable.NewRow()
objNewRow("ModuleId") = moduleId
objNewRow("Name") = NameField.Text
objNewRow("Rep") = RepField.Text
objNewRow("Email") = EmailField.Text
objNewRow("Phone") = PhoneField.Text
objNewRow("Fax") = FaxField.Text
objNewRow("City") = CityField.Text
objNewRow("State") = Statefield.Text
objNewRow("Address") = StreetAddress.Text
objtable.Rows.Add(objNewRow)
Still no Error or activity in the Pofiller.
Please Help...
View 2 Replies
View Related
Nov 10, 2004
I know this is a sin in dbforums to jump forums to ask other forum questions, but I just had to do it.....
it's actually related to foxpro dbf tables. Here is the case:
I am opening this existing DBF file in Microsoft Visual Fox Pro 6.0 .
In the command window, select, update and even including delete statements works .
What is getting on my nerves is the "insert" statement. It always prompts "syntax error". But the @#$@#$ error message just didn't help much.
The funny thing is, if I use the "Append Mode" and add data directly via the GUI, it works!.
Here is the insert statement, just a very simple one:
<code> INSERT INTO ASSET (ACCNO) values '2000/141'</code>
You can reply me here , or go to the real thread to reply me if you can help out..thanks
http://www.dbforums.com/t1058508.html
View 2 Replies
View Related
Jun 16, 2006
I have this statement buried in a sproc:
INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)
SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1
FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON
PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR]
AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
WHERE PLAN_DEMAND.BOD_INDEX IS NULL
When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.
I'm sure it's something obvious (but not to me).
Any help would be much appreciated.
View 7 Replies
View Related
Mar 26, 2004
i MADE A USER AS bULK aDMIN BUT HE STILL CAN'T BULK iNSERT TO A TABLE . He has dd_writer and dd_reader roles assigned in database .
What should I do to fix this . Here is the error
The current user is not the database or object owner of table 'Temp_load'. Cannot perform SET operation.
View 2 Replies
View Related
Apr 22, 2004
I have a situation. The userID running the Stored Proc is assigned Bulk-Admin privileges . Program creates a temp# table and bulk inserts a text file. The process runs fine running as Sysadmin . However , it fails if run with that UserID with following error
"The current user is not the database or object owner of table '#Temp'. Cannot perform SET operation."
What should I do to fix it .
Thanks
View 6 Replies
View Related
May 27, 2008
Hello,
I have a stored procedure that updates my table from values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case. Here is my code, where am I going wrong?
ALTER PROCEDURE [dbo].[spUploadUser]
(@userid varchar(10), @username varchar(50), @userstatus varchar(20))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ERROR_STATE INT;
BEGIN TRANSACTION
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
SELECT @ERROR_STATE = @@ERROR;
IF (@ERROR_STATE <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
COMMIT TRANSACTION
END
Regards,
MizPippz
View 8 Replies
View Related
Jun 18, 2008
Hi there
I've amended a table to include some extra columns to track when changes are made. Next step is to amend the stored procedure that updates that table when the changes are made.
I amended an existing stored proc to include CreateTS, CreateID, ModifyTS, ModifyID. Unfortunately, the INSERT and UPDATE aren't working for the new columns.
Am fairly new to this, so not sure why it's not working? Code is below:
DECLARE @ThisBSB VarChar(6)
DECLARE @intCount int
DECLARE @intInserted int
DECLARE @intUpdated int
SET @intInserted = 0
SET @intUpdated = 0
-- fields from New Table
DECLARE curBSB CURSOR
FOR
SELECT Replace(bsbnumber,'-','')
FROM ztblBSBText (nolock)
OPEN curBSB
FETCH NEXT FROM curBSB
INTO @ThisBSB
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @ThisBSB
-- See if this BSB Already Exists
SELECT @Intcount = Count(*)
FROM tblBankBSB (nolock)
WHERE BSBcode = @ThisBSB
IF @intCount = 0
BEGIN
-- Insert New Record
--Print 'Insert: ' + @ThisBSB
INSERT INTO tblBankBSB
([BSBCode]
,[BankID]
,[BranchNumber]
,[BranchName]
,[CountryID]
,[Address]
,[Suburb]
,[StateID]
,[StateCode]
,[State]
,[PostcodeID]
,[Postcode]
,[StatusID]
,[TransferedToBSB]
,[CreateID]
,[CreateTS]
,[ModifyID]
,[ModifyTS])
SELECT @ThisBSB
,tblBank.BankID
,Cast(Right(bsbnumber,3) AS Int)
,ztblBSBText.BSBName
,1
,ztblBSBText.Address
,ztblBSBText.Suburb
,tblState.StateId
,Null
,ztblBSBText.State
,Null
,ztblBSBText.Postcode
,1
,Null
,Null
,Null
,@UserContactID
,getDate()
FROM ztblBSBText
INNER JOIN tblBank (nolock) on ztblBSBText.Mnemonic = tblBank.BankCode
INNER JOIN tblState (nolock) on ztblBSBText.State = tblState.State
WHERE tblState.StatusID = 1
AND tblState.CountryID = 1
AND Replace(bsbnumber,'-','') = @ThisBSB
SET @intInserted = @intInserted + 1
END
ELSE
BEGIN
-- See If Closed since last time this was run, and if so, update
SELECT @intCount = Count(*)
FROM ztblBSBText
INNER JOIN tblBankBSB (nolock) ON Replace(ztblBSBText.bsbnumber,'-','') = tblBankBSB.BSBCode
WHERE Replace(bsbnumber,'-','') = @ThisBSB
AND ztblBSBText.BSBName = 'Closed'
AND tblBankBSB.BranchName Not Like '%Closed%'
IF @intCount > 0
BEGIN
--Print 'Update: ' + @ThisBSB
UPDATE tblBankBSB
SET tblBankBSB.StatusID = 0
,tblBankBSB.BranchName = tblBankBSB.BranchName + ' - Closed'
,tblBankBSB.TransferedToBSB = (SELECT replace(substring(address, 14,7),'-','')
FROM ztblBSBText
WHERE Replace(ztblBSBText.bsbnumber,'-','') = @ThisBSB)
,tblBankBSB.ModifyID = @UserContactID
,tblBankBSB.ModifyTS = getDate()
WHERE BSBCode = @ThisBSB
SET @intUpdated = @intUpdated + 1
END
END
FETCH NEXT FROM curBSB
INTO @ThisBSB
END
CLOSE curBSB
DEALLOCATE curBSB
_____________________________
"Nihil est incertius volgo." - Cicero
View 2 Replies
View Related
Jul 20, 2005
Hello,I have been trying to load a delimited data file to SQL Server. Ihave tried both of the options that are available: each time, I getdifferent errors. This is on an eval version of SQL Server 2K, withSP 3a on a Windows XP box.First, I tried to load the data with Bulk Insert. This didn't gothrough as it requires sysadmin/bulkadmin privileges. I am the onlyperson using the SQL Server, and I wanted to grant myself thoseprivileges. But I cannot find them using the Enterprise Manager. AllI see is privileges like datareader, datawriter, etc.Then I tried to use bcp. This doesn't seem to work either as it givesthe following error:ERROR: DB Code: (CR001): SQLState = 08001, NativeError = 17Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server doesnot exist or access denied.SQLState = 01000, NativeError = 53Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).child process exited abnormallyAnybody with a solution to make this work? For reference, I am usingthe following bcp command. I can login to the database using theserver/user/password combination with no problem:C:/Program Files/Microsoft SQL Server/80/Tools/Binn/bcp.exetestUser.productsIN"C:/Documents and Settings/testUser/products.txt"-f "C:/Documents and Settings/testUser/prodformat.txt"-t "-" -r "
"-S"sqlserver_eval" -U"testUser" -P"password" -R -k -h TABLOCK
View 2 Replies
View Related
Apr 21, 2007
Hi,
I am tryin to run the store procedure which has bulk insert command.when i run the code,it says you don't have permission to use bulk insert command. This query/storeprocedure is running succesfully in the backend(sqlserver2000).
solution is only user in Bulkadmin role has permission for bulk insert .But i don't know how to create one and use it in asp.net.
Can anyone help me in this. ..Here is the code.
string conn_string1 =@"Data Source=SENTHILTEST;Initial Catalog=EMPLOYEE;Integrated Security=SSPI";
SqlConnection objconn1= new SqlConnection(conn_string1);
objconn1.Open();
SqlCommand command1=new SqlCommand("bulkinsert",objconn1);
command1.CommandType=CommandType.StoredProcedure;
command1.ExecuteNonQuery();
Response.Write("Stored procedure excecuted");
objconn1.Close();
thanks,
kar
View 1 Replies
View Related
Dec 13, 2007
Hi,
In my localmachine everything is working fine. But online only Update, Delete and Select statements are working correctly, while INSERT doesn't work and I got a similar error to this when I try to insert any data into any table:
Server Error in '/' Application.
Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated. 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: Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.
The statement has been terminated.]
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.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
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
So wat's wrong?
Note:1-The Insert statement work correctly in localmachine.2-The application and the sql database are both in remote hosts.3-The application configuration had been checked and is correct.
View 3 Replies
View Related
Feb 18, 2008
hi, i am trying to insert my values into the database, however the code i have doesnt seem to work. i have looked at old posts, one suggested to take away my code behind code where the insert method has been written. i did try this but it does not seem to work, can some please help me sort out this problem and advice or examples of what i need to do will be very much appreciated, thank you. the following is the code i have
code behind code
'Save vlues into database.
If IsPostBack = False ThenDim test As SqlDataSource = New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
test.InsertCommand = "INSERT INTO [UserQuiz] ([QuizID], [DateTimeComplete], [CorrectAnswerCount], [UserName]) VALUES (@QuizID, @DateTimeComplete, @CorrectAnswerCount, @UserName)"test.InsertParameters.Add("QuizID", Session("QuizID").ToString())
test.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString())test.InsertParameters.Add("CorrectAnswerCount", "12")test.InsertParameters.Add("UserName", User.Identity.Name)
test.Insert()
End If
when i run the program i get this error
Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.
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: Cannot insert the value NULL into column 'UserQuizID', table 'C:VISUAL STUDIO 2008WEBSITESquizAPP_DATAQUIZ.MDF.dbo.UserQuiz'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
Line 26: test.InsertParameters.Add("UserName", User.Identity.Name)
Line 27:
Line 28: test.Insert()
Line 29:
Line 30: End If
View 4 Replies
View Related
Jun 23, 2005
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId
BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
View 6 Replies
View Related
Apr 16, 2006
Hi,
The following INSERT query works in all aspects apart from the date value:
String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);
The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").
In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.
What am I doing wrong? Thanks very much.
Regards
Gary
View 3 Replies
View Related
Mar 28, 2008
Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me.
My data:
“lname�, “fname�, “addr�, “phone�, “lang�
My command:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '","')
The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '"')
But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes?
View 4 Replies
View Related
Jan 28, 2008
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million
Current record count in t_DTM_DATA_1 - 267 Million
Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP]
,
,[MAJ]
,[MINR]
,[LOCN]
,[DPT]
,[YEAR]
,[PD]
,[WK]
,[TRDT]
,[SYSTEM]
,[AMOUNT]
,[DESCRIPTION]
,[GROUP]
,[VENDOR]
,[INVOICE]
,[IDAT]
,[PO_NUMBER]
,[DDAT]
,[RCV#]
,[RDAT]
,[RSP]
,[EXPLANATION]
,[UPLOAD_DATE]
,[UPLOAD_USER]
,[UPLOAD_NAME]
,[RELEASE_DATE]
,[RELEASE_USER]
,[RELEASE_NAME]
,[TRTM])
SELECT
t_DTM_DATA_STAGING.CP,
t_DTM_DATA_STAGING.CO,
t_DTM_DATA_STAGING.MAJ,
t_DTM_DATA_STAGING.MINR,
t_DTM_DATA_STAGING.LOCN,
t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR,
t_DTM_DATA_STAGING.PD,
t_DTM_DATA_STAGING.WK,
t_DTM_DATA_STAGING.TRDT,
t_DTM_DATA_STAGING.SYSTEM,
t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION,
t_DTM_DATA_STAGING.[GROUP],
t_DTM_DATA_STAGING.VENDOR,
t_DTM_DATA_STAGING.INVOICE,
t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER,
t_DTM_DATA_STAGING.DDAT,
t_DTM_DATA_STAGING.RCV#,
t_DTM_DATA_STAGING.RDAT,
t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME,
t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
View 4 Replies
View Related
Apr 8, 2006
It would be great to get some feedback on this, I've wasted nearly the whole day trying to figure it out.
I've created a simple test database called test.sdf. It contains a few tables but no data. My plan is to populate the database programatically; however, the INSERT command does not seem to be working correctly. I can see that the data actually gets added to the database when I add it and iterate over it programatically (using SqlCeCommands), but after the program completes and I look at the Table in "Server Explorer" via VS 2005, the data is no longer there!
Now note that the location of the sdf is in the Emulator's "shared folder." I have read from other posts (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=173589&SiteID=1) that you cannot create a database on emulated "storage card" with PPC 2005 emulators but I've read nothing about modifying a database which exists there. Unfortunately I do not currently have access to a "real" device so I can't test this in any other way.
Here's the test function:
public void Test()
{
SqlCeConnection conn = null;
try
{
conn = new SqlCeConnection();
conn.ConnectionString = @"Data Source = Storage Card est.sdf";
conn.Open();
SqlCeCommand cmd1 = conn.CreateCommand();
cmd1.CommandText = "INSERT INTO Customers VALUES ('Joe Schmoe')";
int n = cmd1.ExecuteNonQuery();
cmd1.CommandText = "INSERT INTO Customers VALUES ('Alice Wonderland')";
n = cmd1.ExecuteNonQuery();
SqlCeCommand cmd2 = conn.CreateCommand();
cmd2.CommandText = "SELECT * FROM Customers";
SqlCeDataReader rdr = cmd2.ExecuteReader();
//this correctly prints out Joe Schmoe and Alice Wonderland
while (rdr.Read())
{
String str = rdr.GetString(0);
Console.WriteLine(str);
}
rdr.Close();
}
finally
{
conn.Close();
}
}
After running this test function, I open the Customers table in VS2005 and no Customer rows exist despite the fact that the SqlCeDataReader correctly iterated through the previously added 2 customers.
View 6 Replies
View Related
Jan 7, 2008
Hello,
I have a SqlDataSource that is not doing my inserts properly. even if the user is logged in (UserName!=""), it always inserts a null in the UserName field.
SqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLLConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserName]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserName)" SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserName] FROM [tblDiaryEntries] WHERE [UserName]=@UserName" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate WHERE [DiaryEntryID] = @DiaryEntryID"> <DeleteParameters> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> </InsertParameters> </asp:SqlDataSource>
and from code behind, i do:
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { SqlDataSource1.SelectParameters.Add("UserName", this.User.Identity.Name); SqlDataSource1.InsertParameters.Add("UserName", this.User.Identity.Name); } }
Any ideas/suggestions? Thanks!
View 9 Replies
View Related
Mar 27, 2004
If anyone has examples of pulling the text out of textboxes and passing it to a INSERT statement which then puts the data into a SQL database table please if you could pass this on that would be great.
Regards and thanks in advance.
Ryan J. Boyle
View 1 Replies
View Related
Dec 2, 1999
Hi I have the following Stored Proc which works in SQL Server 6.5 but not in SQL Server 7.0. All this Stored Proc does is Create a temp table, execute the DBCC ShowContig on a table and insert the results of the DBCC into a temp table. What am I missing. Thanks.
The code of the Stored Proc is:
/* This Stored Procedure Creates a temp table. (Step 1) */
/* Initializes a local variable @StirngToBeExecuted with */
/* a DBCC command. (Step 2) */
/* Step 3. The Command is Executed and the results of the */
/* DBCC command is inserted into Temp Table. */
/* Step 4. The results of the Temp table are shown on the */
/* Screen. */
/* This SQL Works Fine in SQL Server Version 6.5 */
/* In SQL Server 7.0 the results of the DBCC command is */
/* NOT getting inserted into the Temp table. WHY??? */
IF EXISTS (SELECT * from sysobjects where id = object_id('dbo.Test_sp') and sysstat & 0xf = 4)
drop procedure dbo.Test_sp
GO
CREATE PROCEDURE Test_sp
AS
DECLARE
@StirngToBeExecuted Varchar(100)
CREATE TABLE #temp( -- Step 1
OutputOfExecute Varchar(255)
)
-- Step 2
SELECT @StirngToBeExecuted = 'DBCC SHOWCONTIG (123456789)'
INSERT
INTO #temp exec (@StirngToBeExecuted) -- Step 3
SELECT * FROM #temp -- Step 4
DROP TABLE #temp --Drop the Temp Table
View 2 Replies
View Related
May 20, 2008
Hi
When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....).
Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
o INSERT INTO #TMP_F_B (
o Field1 ,
o Field2,
o Field3 ,
o …,
o )
o EXEC dbo.ps_a5s_rpt_charge700 @util, @annee , @perimetre , @secteur , @poste , @fournisseur , @err , @lib_err
Many thanks
Sincerly
View 2 Replies
View Related
Aug 15, 2005
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
View 1 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
Â
[code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 10 Replies
View Related
Mar 11, 2014
I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE
[M2Data]
GO
/****** Object:Â
Trigger [dbo].[tblContacts_UTrig]Â Â Â Script Date: 3/11/2014 9:07:13 AM ******/
SET
ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?
View 8 Replies
View Related
Mar 16, 2014
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE
[Code] .....
View 8 Replies
View Related
Oct 7, 2015
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
View 6 Replies
View Related
Aug 11, 2007
here is my code:
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
View 5 Replies
View Related
Jul 23, 2005
I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
View 1 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
