Simple (?) SQL 7.0 Update Taking Forever...

May 3, 2000

I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...

View 3 Replies


ADVERTISEMENT

SQL Query Taking Forever

Mar 1, 2006

I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT     dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage,                       dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM         dbo.tblErrorLevel INNER JOIN                      dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN                      dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.

View 2 Replies View Related

Shrinkdatabase Taking Forever...

Mar 17, 2008

Hi all,

2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.

So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...

The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??

Anyone has experience running shrink on large DBs?


thanks!

View 14 Replies View Related

Cte Query Taking Forever

May 14, 2008

I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..

; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update



thanks

View 8 Replies View Related

All Queries Are Taking Forever

Aug 23, 2007

what can I do?

all queries that used to work are taking forever now???

what can I do?

is there a max size for the db that I may have reached

please advise asap

View 20 Replies View Related

Report Taking Forever To Come Up

Dec 14, 2007

I am using RS 2005 and SQL Server 2005. I am having a table with about 6 million rows. I am extracting about 2 milliion rows for a report. When i run the report as a single user the report comes up in 6-7 minutes but when i run the report with 2 users the report takes forever to come up.

The statistics are different each time sometimes 19 minutes sometimes 30 minutes. The report connects to the db with the same dbuser id for both the people running the report. The stored procedure being invoked uses temp tables and also indexes are created on the fly for these temp tables.

The moment 2 people are running the report and when i run an SP_WHO2 i see that one process id that is being started by reportserver blocks another process being run by reportserver.

Timeouts are not happening the report justs goes on forever to come up. Any help? Also if you need any more information please do let me know I will be glad to give them.

The report is a matrix report and there are 4 levels of grouping on the report.

Thanks in advance

View 1 Replies View Related

Stored Procedure Taking Forever To Run

Apr 9, 2008

Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.

Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.

I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??

has anyone got any ideas ?

View 6 Replies View Related

Report Taking Forever To Render

Jan 15, 2008



Hi All,

I have 2 reports that report on baiscally the same thing, just group differently.

Report 1 groups summary phone call stats by Department, Day, and Hour - which are all drop down options.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see daily stats ... the daily stats can then be expanded to see the hourly stats.

Kinda Like:
-------------------------
-Department 1
-10/1/2007
12:00 AM
1:00 AM
+10/2/2007

+Department 2
-------------------------

Report 2 shows the same summary stats by department and extension - which is also a drop down option.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see summary stats for each extension.

Kinda Like:
-------------------------
-Department 1
Ext 2005
Ext 2008

+Department 2
-------------------------


The queries for these reports run from the Management Studio in about 10 seconds each with the Report 1 query returning about 800 rows for the month of October 2007 and the Report 2 query returning about 30 rows for the same date range.

When the reports are rendered, Report 1 (with 800 rows) is rendered in about 20 seconds, while Report 2 (with only 30 rows) takes about 5 minutes to render.

The reports themselves are very similar, with the only difference being the grouping. It is weird that the report that returns the samller Dataset is actually taking longer to render.

One thing I did try was running the queries from the Data tab of the .rdl files (in visual studio) and the query for Report 2 took about 4 minutes to return data, while (as I mentioned above) it ran in about 10 seconds in Management Studio.

Has anyone else run into this?
Any suggestions?


Thanks in advance!
-Matt

View 1 Replies View Related

What Is Wrong With This Query..It's Taking Forever...

May 16, 2008



USE GLPDEMO
GO

select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'


DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)

DECLARE TCursor CURSOR for

SELECT TriggerName, TableName from Temp_TablesAndTriggers

OPEN TCursor

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

WHILE @@FETCH_STATUS = 0


select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable


EXECUTE sp_executesql @exestr;

FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable

CLOSE TCursor
DEALLOCATE TCursor

--DROP TABLE #Temp_TablesAndTriggers

View 4 Replies View Related

Simple Select Query Runs Forever

Dec 14, 2007



Hi all,

I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.

If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.

I am doing something like this:

###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";

SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();

############################################################################################

StudentTable contains roughly 1000 rows.


Somebody please help me out of this. Thanks in advance.

Surya

View 11 Replies View Related

Update Stmt Takes Forever

Nov 26, 1998

We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.

We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'

this returns immediately.

Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.

There is no index on UDF3 and WOID is the primary key.

Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.

name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB

View 1 Replies View Related

Update Operation Takes Forever! How Can I Speed It Up?

Jul 20, 2005

I'm having a problem with an update operation in a stored procedure. Itruns so slowly that it is unusable, unless I comment a part out in whichcase it is very fast. However, I need the whole thing :). I have atable of email addresses of people who want to get invited to parties.Each row contains information like email address, city, state, country,and preferences for what types of events are of interest.The primary key is an EMAILID, and has a unique constraint on the emailfield. The stored procedure receives the field data as arguments, andinserts the record if the email address passed is not in the database.This works perfectly. However, if the stored procedure is called for anemail address that already exists, it updates the existing row insteadof doing an insert. This way I can build a web page that lets peoplemodify their preferences, opt in and out of the list and so on.If I am doing an update, the stored procedure runs SUPER SLOW (and thepage times out) unless I comment out the part of the update statementfor city, state, country and zipcode. However, I really need to be ableto update this!My database has 29 million rows.Thank you for telling me anything about how I can speed up this update!Here is the SQL statement to run the stored procedure:declare @now datetime;set @now = GetUTCDate();EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',@Country='United States'Here is the stored procedure:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE [usp_EMAIL_Subscribe](@Email [varchar](50),@Opt_GenInterest [tinyint],@Opt_DatePeople [tinyint],@Opt_NewFriends [tinyint],@Opt_OldFriends [tinyint],@Opt_Business [tinyint],@Opt_Couples [tinyint],@OptOut [tinyint],@OptOutDate datetime,@Opt_Events [tinyint],@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),@Country [varchar](20))ASBEGINdeclare @EmailID intset @EmailID = NULL-- Get the EmailID matching the provided email addressset @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS whereEmailAddress = @Email)-- If the address is new, insert the address and settings. Otherwise,UPDATE existing email profileif @EmailID is null or @EmailID = -1BeginINSERT INTO v_SENWEB_Email_Subscribers(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,ZipCode,GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,MeetOtherCouples, MeetAtEvents)VALUES(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,@Opt_GenInterest, @Opt_DatePeople,@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,@Opt_Events)EndElseBEGINUPDATE v_SENWEB_EMAIL_SUBSCRIBERSSET--City = @City,--StateProvinceUS = @State,--Country = @Country,--ZipCode = @ZCode,GeneralInterest = @Opt_GenInterest,MeetDate = @Opt_DatePeople,MeetFriends = @Opt_NewFriends,KeepInTouch = @Opt_OldFriends,MeetContacts = @Opt_Business,MeetOtherCouples = @Opt_Couples,MeetAtEvents = @Opt_Events,OptedOut = @OptOut,OptOutDate = CASEWHEN(@OptOut = 1)THEN @OptOutDateWHEN(@OptOut = 0)THEN 0ENDWHERE EmailID = @EmailIDENDreturn @@ErrorENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOFinally, here is the database schema for the table courtesy ofenterprise manager:CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] ([EmailID] [int] IDENTITY (1, 1) NOT NULL ,[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[OptinDate] [smalldatetime] NULL ,[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StateProvinceOther] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GeneralInterest] [tinyint] NULL ,[MeetDate] [tinyint] NULL ,[MeetFriends] [tinyint] NULL ,[KeepInTouch] [tinyint] NULL ,[MeetContacts] [tinyint] NULL ,[MeetOtherCouples] [tinyint] NULL ,[MeetAtEvents] [tinyint] NULL ,[OptOutDate] [datetime] NULL ,[OptedOut] [tinyint] NOT NULL ,[WhenLastMailed] [datetime] NULL) ON [PRIMARY]GOCREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON[PRIMARY]GOALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADDCONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR[WhenLastMailed],CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED([EmailID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]GOMeet people for friendship, contacts,or romance using free instant messaging software! See a picture youlike? Click once for a private conversation with that person!<a href="http://www.sen.us"><imgsrc="http://www.sen.us/mirror/SENLogo_62_31.jpg"></a>*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 9 Replies View Related

SQL 2005 To SQL 2000 Update Takes Forever

May 7, 2007

I have a SQL 2005 & SQL 2000 server. I am attempting to execute a simple update statement, something that looks like:



update AD

set AD.SomeDate = getdate()

from [ServerX].DB.dbo.Table

where ColumnX = 'X'



ServerX is the SQL 2000 box.

ServerY is the SQL 2005 box. Server Y is where this statement is invoked from. (Not shown in statement).



I have a linked server set up.



When executed from the 2000 box, it runs in < 1 second.



When both environments are 2005 to 2005, it takes less than < 1 second.



View 1 Replies View Related

Remote Update Having A Linked Server Takes Forever To Execute

Oct 17, 2006

UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,

col8=SR.col8,col9=SR.col9,col10=SR.col10

FROM LNKSQL1.db1.DBO.Table1 CD

join Table2 USRI on USRI.col00 = CD.col00

join table3 SR on USRI.col00 = SR.col00

Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you

View 4 Replies View Related

Update Query Taking 23 Seconds

Jan 26, 2007

I am not sure if I am in the correct location or if I should be in the SQL forum but here is my question:

I have an update statement that goes out through SQL 2000 through a local linked server to another SQL 2000 server on my machine. When I run the update in Query Analyzer it takes less than a second. When I run it in my C# code using the SqlCommand object and parameters it takes me ~23 seconds. If I remove one of the parameters it goes down to ~15 milliseconds. Has anyone heard of this happening?

The parameter that I remove is a simple char(10) column that isn't the primary key and is used in the WHERE statement. There isn't an index on the field.

23 Seconds
Update table Set column = @val WHERE field = @field AND other = @other
15 milliseconds
Update table Set column = @val WHERE field = 'values' AND other = @other

View 11 Replies View Related

Update Taking Long Time In 2000 Then SQL 7.0

Mar 7, 2002

Hi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????

Here is Query::

update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'


Thanks
Ananth

View 6 Replies View Related

Update Statment Taking Long Time

Mar 27, 2008



I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.

this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null

It is using ole db connection. About 36,000 records that it is updating.

I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?


any input greatly appreciated.


View 7 Replies View Related

Table Update Process Is Taking Too Much Time

Mar 9, 2008



hi,


I'm using a OLE DB COMMAND component to perform an update (SQL statement) but the procces takes about 9 hours, so I changed it to a stored procedure but it was the same, I need to update about a a million of rows and the package is very simple.

How can I improve the time, Can I use another component or startegy?

thanks

View 3 Replies View Related

Simple Update

Nov 23, 1999

Hi..This may be silly but i am stuck at this for some time.
I have 10000 rows in a table and i want to copy the value of column 1 in all the rows to column 2 ( which contains NULL presently in all the rows ). The two columns are of the same datatype.

Thanx.

View 1 Replies View Related

Simple SQL Update

Mar 16, 2004

Hello all and I look forward to reading your replys. Currently, I'm trying to update a table in my SQL database by writing a script that will go get the field name and correct info, verify it has the correct field by using two other columns and then update the information.

I do not know how to use tell SQL what to update when the field name can change. I've used the following in the past.

UPDATE [Import Table]

SET [Import Table].[???] = [Correction_Table].[Correct_Info]

WHERE [Import Table].[UNIQUE ID] in (select [USO Unique ID] from [Correction_Table])

IN this new instance, I'm reading the Field_Name from the Correction_Table so I don't know what to put in my SET statement.

Please any help or pointers in the right direction would be greatly appreciated.

Battle_n

View 4 Replies View Related

Help With Simple Update

Dec 11, 2006

I have a table Products with their prices, and I have another table Sales, that have the prices of the products but they are null, I need to make an update that update the tables Sales, inserting the prices from the table Products

View 3 Replies View Related

Simple UPDATE

Feb 26, 2008

I need to do an update. Can you tell me if it's possible to update multiple tables in a single statement when joined, as with a select, or this has to be taken care of in seperate statements with the same join? I'm sure this is a simple YES / NO answer.

Here is what I'm using, with the same structure as somewhere else... why do I get 'incorrect syntax near q' on that very first line?

-- Do the update
update ccs_quotes q
set ccs_quo_user_date#1 = j.completed
from "ken-sql-002".hettonhosttestdatabase.dbo.job j
inner join ccs_quotes q
on left(j.cid,10) = q.ccs_quo_ref_number#2
where completed is not null
and ccs_quo_ref_number#2 = 'SHGD40021'


Thanks a lot
Mike

View 5 Replies View Related

Simple SQL Update

Jul 23, 2005

I need to copy data from one column that has a max size of 45 characters toa column that has a max size of 20 characters.What is the syntax to say fill the first 20 characters out of 45 fromcolumn 1 into column 2?jeff--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Simple Db Update Question

Oct 4, 2007

Hi folks. I'm trying to update a single field in a database table with the input from a single text box, but I'm getting "Insert Error: Column name or number of supplied values does not match table definition". I know at least part of the problem is that I haven't specified the name of the table field, but I'm not sure where to do this. The table in question has many fields, but I only want to update one. The submit button is "sendMail", which calls UpdateDB.I'm learning and trying to limit myself to 1 idiotic question per week. Please have mercy. Thanks. Chrisprivate void UpdateDB(string cmd)
{
// point to connection string in web.config
string connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True";
// create connection object; initialize; open
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
try
{
connection.Open();
// create sqlcommand object and assign connection
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.Connection = connection;
command.CommandText = cmd;
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}

protected void sendMail(object sender, EventArgs e)
{
string cmd = @"INSERT INTO tableName VALUES ('" + this.TextBox2.Text + "')";
UpdateDB(cmd);
// send smtp mail ... } 

View 3 Replies View Related

Simple Update Page

Oct 9, 2007

I have been trying to create a simple page to update my SQL Express database with some text boxes that are auto filled.
If here is the code if you have any help for me it would be great. Partial Class Secured_Default2
Inherits System.Web.UI.PageProtected Sub Accept_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Accept.Click
SqlDataSource3.Update()
End SubProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
AcceptDate.Text = System.DateTime.Now.ToStringAcceptedBy.Text = Request.ServerVariables.Get("Logon_User")
StatusBox.Text = "2"End Sub
End Class<%@ Page Language="VB" MasterPageFile="~/Secured/MasterPage.master" AutoEventWireup="false" CodeFile="TEST.aspx.vb" Inherits="Secured_TEST" title="Untitled Page" %>
<%@ Import Namespace="System.Data.SqlClient" %><%@ Import Namespace="System.Data" %>
 
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<p>
<br />
&nbsp;&nbsp; Date:
<asp:TextBox ID="AcceptDate" runat="server" ></asp:TextBox>&nbsp; &nbsp; Accepted By:
<asp:TextBox ID="AcceptedByBox" runat="server" ></asp:TextBox><br />
Status:
<asp:TextBox ID="StatusBox" runat="server" Width="18px"></asp:TextBox>
&nbsp; &nbsp; &nbsp;&nbsp;
<asp:Button ID="Accept" runat="server" CommandName="Update" Text="Accept Ticket" /><br />
<br />
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:HelpDeskConnectionString2 %>"
SelectCommand="SELECT TicketNUM, Accepted, AcceptedBy, Status FROM HelpDesk WHERE (TicketNUM = @TicketNUM)"
UpdateCommand="UPDATE HelpDesk SET Accepted = @UAccepted, AcceptedBy = @UAcceptedBy, Status = @UStatus WHERE (TicketNUM = @TicketNUM)">
 
<UpdateParameters>
<asp:FormParameter Name="UAccepted" FormField="AcceptedDate"/>
<asp:FormParameter Name="UAcceptedBy" FormField="AcceptedByBox" />
<asp:FormParameter Name="UStatus" FormField="StatusBox" />
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</UpdateParameters>
<SelectParameters>
<asp:QueryStringParameter Name="TicketNUM" QueryStringField="TicketNUM" />
</SelectParameters>
</asp:SqlDataSource>
 </p>
</asp:Content>

View 4 Replies View Related

Simple Update Data

Jun 18, 2008

I am attempting to update contact data that is displayed in a form. Unfortunately the followiing code that runs off the button click event does not seem to work or I am misssing something to make it fire. I get no errors but the data does not get updated. What am I missing? Thanks.Private Sub btn_Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Submit.Click
Dim strConnection As StringDim cn As SqlConnection
Dim sql As String
strConnection = ConfigurationSettings.AppSettings("ConnectionString")cn = New SqlConnection(strConnection)
sql = "UPDATE vw_BS_IssuerContacts SET ContactLastName = @ContactLastName, ContactFirstName = @ContactFirstName, ContactTitle = @ContactTitle, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, Address1 = @Address1, Address2 = @Address2, Address3 = @Address3, Country = @Country, ZipCode = @zipCode, AccessLevel = @AccessLevel WHERE IssuerCode = @IssuerCode"Dim UpdateCommand As New SqlCommand(sql, cn)
UpdateCommand.Connection = cn
UpdateCommand.CommandText = sqlUpdateCommand.Parameters.Add(New SqlParameter("@IssuerCode", Lbl_IssuerCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactLastName", txt_Lname.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactFirstName", txt_Fname.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactTitle", txt_title.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ContactEmail", txt_Email.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@ContactPhone", txt_Phone.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address1", txt_Address.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Address2", txt_Address2.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Address3", txt_City.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@Country", txt_Country.Text))UpdateCommand.Parameters.Add(New SqlParameter("@ZipCode", txt_zipCode.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", Lbl_currAccessLevel.Text))
'UpdateCommand.Parameters.Add(New SqlParameter("@AccessLevel", SqlDbType.Char, 2).Value = txt_CurrAccessLevelSelectedValue))UpdateCommand.Parameters.Add(New SqlParameter("@UserName", txt_Username.Text))
UpdateCommand.Parameters.Add(New SqlParameter("@infopw", txt_pw.Text))UpdateCommand.Parameters.Add(New SqlParameter("@Company", txt_IssName.Text))
'do the update
cn.Open()
UpdateCommand.ExecuteNonQuery()
 
cn.Close()
 
 
 End Sub
 
 

View 1 Replies View Related

Help With A Simple Update Query..

Feb 5, 2007

hi, i've just started using sql server 2005 and visual studio, i have made 3 stored procedures, an update, insert and delete. The update one doesn't work but the others do so i'm on the right track with them..

is there anything obvious i am missing in the following update stored procedure? All of the datatypes and names line up with the datatypes/sizes/names in the table..


Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spUpdateAddresses]
-- Add the parameters for the stored procedure here
(@addressID int, @fname varchar(50), @lname varchar(50), @address01 varchar(50), @address02 varchar(50), @suburb varchar(50), @state varchar(50), @postcode char(10), @comments varchar(1250), @phone char(10), @fax char(10), @mobile char(10))

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update addresses
set @fname=fname, @lname=lname, @address01=address01, @address02=address02, @suburb=suburb, @state=state, @postcode=postcode, @comments=comments, @phone=phone, @fax=fax, @mobile=mobile
where (@addressID = addresses.addressID)

END

View 2 Replies View Related

Simple Update Query Help

Feb 27, 2007

Hi, wondered if anyone here can help out with an SQL query thats been driving me mad. Currently I do this programmatically in a C# program with a hard coded mapping class but want to put the new codes into a table and run an update query instead.

2 tables like this;

Table: CarModels
SORT
GROUP_CODE
MODEL_NAME
PK: SORT

Table: NewGroupCodes
OLD_CODE
NEW_CODE
PK: OLD_CODE, NEW_CODE

Basically I want to update all GROUP_CODE entries in the CarModels table with the NEW_CODE values from NewGroupCodes where there is a match between CarModels.GROUP_CODE and NewGroupCodes.OLD_CODE.

thanks in advance,
afro

View 7 Replies View Related

Simple Update Error

Jul 20, 2005

Hey all,i have a students_table which i want to do a multiple update on.Update student_tableset grant = 35000where course_id = 2There are lots of students in the student table which have course_id of2.I get this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used as anexpression.The statement has been terminated.Does anyone know how to do a multiple update like this.It seems such asimple thing but im confused*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Noobie Here - Need Help With Simple Update

Mar 20, 2008

I've got two tables:

CallLog (one)
Repository (many)

They both have the shared id of CallID. I'm trying to write a trigger that when one adds a record to the repository table, I want the CallLog table flagged with 'T'. My existing code is flagging everything thing in the CallLog table that has CallID match from the Repository table... How do I update the single row in the CallLog table? Driving me nuts!


USE [HEATPROD]

GO

/****** Object: Trigger [dbo].[WriteAttachmentFlagNew] Script Date: 03/19/2008 12:29:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[WriteAttachmentFlagNew]

ON [dbo].[Repository]

AFTER Insert

AS

BEGIN

UPDATE CallLog

SET AttachmentFlagNew = 'T'

FROM repository

WHERE calllog.CallID= repository.CallID

END

View 6 Replies View Related

A Simple Question About SQL Update Statement

Jul 10, 2006

hi, everyone,
When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?
Can somebody help me? Thanks in advance!

View 2 Replies View Related

Help! A Simple &#39;update Records&#39; Question

Nov 19, 1999

Hi there,

I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.

When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.

Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com

View 1 Replies View Related

Simple Table Update With Lookup

Feb 9, 2006

Hi i have three tables

T1 is a table of data imported by DTS, it has staff numbers and location names

T2 is a table that contains system data about staff and has a column for the location id

T3 is a table for the location names and their ID's

i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.

so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location

the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:

Thanks
FatherJack

View 2 Replies View Related







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