SQL Server Is Not Updating

Jun 13, 2007

I am in a fix where I cant seem to update my records.  I am trying to follow a template for an Event calendar and cant seem to get the update page working correctly.   basically the update on the server never occurs but the message is still returned.  Very confused is it my stored procedure?

Imports System.Data

Imports System.Data.SqlClientPartial Class _Default

Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Bind()

End SubSub Bind()Using Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False")

Conn.Open()Dim cmd As New SqlCommand("sp_GetEvent", Conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim ParameterID As New SqlParameter("@Event_ID", SqlDbType.Int, 4)ParameterID.Value = Request.QueryString("Event_ID")

cmd.Parameters.Add(ParameterID)Dim myDataReader As SqlDataReader

myDataReader = cmd.ExecuteReader

myDataReader.Read()txtapeDate.Text = myDataReader.Item("apeDate")

txtapEvent.Text = myDataReader.Item("apEvent")txtWho.Text = myDataReader.Item("Who")

txt_type.Text = myDataReader.Item("_type")txtEvent_ID.Text = myDataReader.Item("Event_ID")

myDataReader.Close()

Conn.Close()

End UsingEnd Sub

 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

 Dim APEvent As String = txtapEvent.Text

Dim Who As String = txtWho.TextDim apeDate As String = txtapeDate.Text

Dim Event_ID As Integer = txtEvent_ID.TextDim _type As Integer = txt_type.Text

'add it to the DBUsing Conn As New SqlConnection("Data Source=WINSERV;Initial Catalog=EventCalendar;User ID=antonio;Password=xxxx;User Instance=False")

Conn.Open()Dim cmd As New SqlCommand("sp_editSingleEvent", Conn)

cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@Who", Who)

cmd.Parameters.AddWithValue("@apEvent", APEvent)cmd.Parameters.AddWithValue("@apeDate", apeDate)

cmd.Parameters.AddWithValue("@_type", _type)cmd.Parameters.AddWithValue("@Event_ID", Event_ID)

cmd.Parameters.Add(New SqlParameter("@message", SqlDbType.VarChar, 200))cmd.Parameters("@message").Direction = ParameterDirection.Output

cmd.ExecuteNonQuery()Dim sMessage As String = Convert.ToString(cmd.Parameters("@message").Value)

Conn.Close()

lblMessage.Text = sMessage

End Using

End Sub

 

 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Response.Redirect("MainEvent.aspx")End Sub

End Class

 ===========Stored Procedure==========USE [EventCalendar]

GO

/****** Object: StoredProcedure [dbo].[sp_EditSingleEvent] Script Date: 06/13/2007 15:15:24 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GOALTER PROCEDURE [dbo].[sp_EditSingleEvent]

-- Add the parameters for the stored procedure here

@apeDate smalldatetime,

@apEvent nvarchar(250),

@who nvarchar (50),

@Event_ID int,

@message nvarchar (200)OUTPUT,@_type int

AS

Begin

Set @Message = 'Hasnt added yet'

UPDATE apEvent

SET apeDate=@apeDate, apEvent=@apEvent, Who=@Who, _type=@_type

WHERE Event_ID = @Event_ID

Set @message = 'its added'

END

View 2 Replies


ADVERTISEMENT

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.

Any suggestions?

View 7 Replies View Related

SQL Server Admin 2014 :: Error While Updating Data Using Oracle Linked Server

Sep 11, 2015

We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.

Below error occurred during process .

OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".

View 7 Replies View Related

SQL Server 2008 :: Updating Server Table From Excel Cells?

Jul 3, 2015

I have an sql server table which serves as a criteria table for my sql server query.

i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.

The table in question has the following fields

SELECT
[Cluster]
,[Max_Break_btw]
,[RefD_Max_Break]
,[DischD_Max_Break]
,[MaxReviewPeriods]
FROM [databseName].[dbo].[SpellClusterAssum]

I will like to change / update the values in the "[Max_Break_btw]" column.

View 0 Replies View Related

Updating SQL Server From ASP.Net

Jan 23, 2004

I am trying to write a web application for a Karate Association TMADragons.com, that will allow instructors to log on, access data on their schools and update their files.

I have managed to get a handle on retreiving data and displaying fairly wel with data biding, but I am having a lot of trouble giving them an interface to update data.

What I would like to do is have a checkbox on the the grid, and a button to click to change selected records, then call a different page that displays and updates each selected record, one by one.

Right now, I would like to try anything that would allow remote update of the files through the internet. I have spent about $700 for a stack of manuals, but they all show you how to build an interface to retrieve data, not how to update it.

Can anyone point me to a pratical example I can look at?

View 3 Replies View Related

Updating SQL Server 2000 From ASP.net

Jun 9, 2004

I am really lost understanding using SQL Server 2000 with ASP.net Webb apps. If you are dong windows apps, the form builder wizard will either build you a page with a grid for displaying multiple records, or a single record page with text boxes.

All well and good.

With ASP.net web apps, though, you only get the option for making the gred/multiple record page.

So I try to make a page with textboxes to use to update my table in Sequel Server. I do my data binding, and fill my adapter, and viola, the data from my first row appears in the text boxes.

Here is the problem. I have a "Next" button and a "Previous" button that I want to use to move forward or backward through the rows in the table. What code do I put behind the buttons?

I created a windows app and used the dataform wizard to make a single record form. The code it puts behind the "Next" button is

Private Sub btnNavNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavNext.Click
Me.BindingContext(objDS_SclRcd, "IMA_School").Position = (Me.BindingContext(objDS_SclRcd, "IMA_School").Position + 1)
Me.objDS_SclRcd_PositionChanged()

End Sub

I tried to incorate the logic into my ASP web page, but can't figure out how to do it.

It seems like it ought to be simple, like position = position + position, but I can't figure it out.

Also, after I enter data changes, how do I save the updated data from the screen to the data set before moving on to the last record?

Last, how do I tell the dataset to go back and update the connextion server?


I really want to be able to update SQL data from a remote client usuing ASP web page, but none of my manual address how to do it.

Any help would be great.

Thanks

Jim

View 4 Replies View Related

Updating Table On Different Sql Server

Aug 9, 2002

Hi,

Here is my problem.

There are 2 different SQL Servers, SQLSvr1 and SQLSvr2(both of them are SQL Server 7.0). On SQLSvr1, I have 3 tables and 1 stored procedure and on SQLSvr2, I have 1 table.

Now, I want to know is it possible for me to update table on SQLSvr2 using a stored procedure on SQLSvr1. Assuming I have all rights and permissions on both servers.


Thanks in advance
-Dinesh

View 1 Replies View Related

Updating 2nd MS Sql Server Over Internet

Feb 2, 2004

Hi,

I'm reasonably new to MS SQL server, I've mostly been setting up databases and creating ASP Pages to look at them with.

However recently I've been given a fun job and I can't seem to find a way to do it.

The problem is we have 2 servers located on different sites, one is actually doing stuff the other just monitors some equipment and when status chages on the equipment a report is made to a table. The software doing this is bespoke and it would cost a fortune to have changed, what we want to do is have the monitoring server send a copy of the inserted row as they get inserted.

I've tried linking the servers via ODBC (in the linked servers part) but when a trigger i've put on the insert table fires it throws up an error about ANSI NULLS and WARNINGS and nothing happens.

After a look round on the internet I've found reference to using the 'set ANSI_NULLS' on command but this seems to be for stored procedures, even then when i try to execute the stored procedure as they show it still comes back with this error.

Can anyone suggest any other ways of copying the inserted row across?

Both servers are MS SQL but are not on a LAN so I have to refere to them as IP addresses.

Cheers,

Iain

View 1 Replies View Related

Updating An SQL Server Database From An ASP.NET Page

Dec 14, 2006

Hello again!I think what I need to do should be simple, but it is proving difficult.I am displaying a page with data drawn from three different SQL Server tables. Most of the fields on the page correspond to fields in one of the three tables, and these fields can be updated by the user. I then want to propagate these updates to SQL Server, refill the DataSet, and rebind.What I'm currently doing is basically this. I've set up a separate SqlCommand for this update. I have a ParameterCollection defined for this SqlCommand that includes the key field to be used in my WHERE clause and all the fields I want to update.When the user clicks on the [Save Customer Review] button, I (1) set the values of the parameters to the values of the corresponding screen fields, in most instances using one of the Convert methods so the data being assigned to the parameter has the correct type; (2) open an SqlConnection; (3) do SqlCommand1.ExecuteNonQuery(); (4) close the SqlConnection; (5) fill the DataSet; (6) bind.I've experienced multiple kinds of failure in this process, but the primary kinds of failure I've experienced are these:- The one value I'm actually updating (a text field) doesn't get updated on the database. No other fields are touched.- Two fields with Int values (SQL Server shows them as length 4; does that correspond to Int32?) are set to NULL.- The ExecuteNonQuery complains that I'm not supplying a value for the one field that is type DateTime. (In the SQL Server database, this field is permitted to be NULL, and in the record I'm playing with, it is in fact NULL. However, since TextBox.Value is of type String, I'm having to set it to Nothing [Visual Basic] if the text box is blank.)One thing I don't like about this is that I can't see what values the parameters are taking on as I do my processing, even in the debugger. At least I don't know of any way to examine or watch them. One reason this bothers me is that I have a rather strong suspicion that there's something wrong with the way I've set up the parameters, but I can't tell what's happening to them to confirm that, and I also can't see the SQL that's being generated (or can I?).Any thoughts?Thanks,TimPS Be gentle, please -- this is my first Web application. :-)

View 10 Replies View Related

Updating An ASPNETDB On The Production Server

Jan 29, 2007

Group,
 I have an ASP.NET web application using the default ASPNETDB that I did in VWD 2005, and deployed to a production server (IIS).
 I need to make a table addition to the production database (ASPNETDB).  I'm trying to use SQL Management Studio to attache the database, but it keeps saying the database is locked by a process.  I shut down all the services that would likely be using it, but it's still showing locked.  What do I need to shut down to attach the database to make the change?
 Or is there a better way to do it?

View 1 Replies View Related

Updating A Users Table In SQL Server

Nov 10, 2003

Hello all,

I am relatively new to this platform and am trying to update a table of user information and passwords through a series of SQL commands. We import a new file of users every two hours and I would like to have three SQL statements:

- One to add new users not currently in Users table
- One to delete users no longer in our source file
- One to update every field (but one or two) for already existing users.

Any help on creating the SQL (as well as where to store it and how to automate it) is greatly appreciated!

Thanks!

View 2 Replies View Related

Add Rows To A DataSet Without Updating The MS SQL Server?

Jan 9, 2006

I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way?
DataSet myDS = new DataSet();DataTable myTable = new DataTable("table1");myTable.Columns.Add("col1", typeof(string));myDS.Tables.Add(myTable);myTable.Rows.Add("MyValue");
Thanks.

View 1 Replies View Related

Updating Microsoft SQL Server 2000 SP 2 To SP4

Jan 31, 2006

Greeetings!

I am newbie in SQL, asking help from you people.

We are using Microsoft SQL Server 2000 Service Pack 2 running on Windows 2000. We are planning to update service pack from to 2 to SP4...Is it okay? What are the requirements? Will there be side effects in our systems?

Hope to hear from you soon.

Regards,
Len

View 1 Replies View Related

Updating Image In SQL Server 6.5 Using Powerbuilder 4

Jul 5, 1998

How do I update and retrieve data which is of type image into/from SQL Server database in Powerbuilder 4.0.06 scripts? I`ve tried using SELECTBLOB and UPDATEBLOB, but it doesn`t work. I wanted to use WRITETEXT, UPDATETEXT and READTEXT in stored procedure, but SQL Server doesn`t allow datatype image to be passed in as a parameter. I also tried using the WRITETEXT directly in Powerbuilder scripts as follows:
WRITETEXT tablename.colname :ptr :graphics;

where ptr and graphics are local variables in PB scripts. ptr is supposed to be of type varbinary in SQL Server syntax. However, there is no equivalent datatype in PB. graphics is of type BLOB in PB scripts which is equivalent to image in SQL Server syntax.

Please advise. Thank you.

View 1 Replies View Related

Help With MS SQL Server - Updating Data From 2 Tables

Oct 5, 2004

Hi all,
Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables? If yes- what would be the query structure for that? Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it
Thank you in advance.

View 2 Replies View Related

Updating DB Server To SQL2005 *after* The Install?

Feb 15, 2007

I had a SQL2000 Server with a simple database. I did an "in place" upgrade to SQL2005 expecting the DB Server and databases to upgrade. However, now when I run SQL Server Management Studio and enter the query:-

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

I get the answer:-

8.00.2039SP4Enterprise Edition

Why didn't it upgrade? And how do I upgrade it now? I'm very confused!

View 3 Replies View Related

Updating A Text Field In SQL Server

Jul 23, 2005

Hi,I have a website using a SQL Server database to store all of it's data.Retrieving data and writing basic data back is fine, however, when i goto update one table that has a text field in it the update fails ifthe amount of data being passed to the text field is too large.Is there a way around this or a particular update i should be using?Any information would be greatly appreciated.CheersBj

View 8 Replies View Related

Updating SQL Server Database From Access

Jul 23, 2005

I'm taking over an SQL Serverdatabase which is often updatedwith data from an Access database.Knowing little about either, thissimple task has become a challenge.I'm told that the previous personused to type some simple SQLcommands and this would updatethe SQL server database but thecommands have been lost and theprevious person is unavailable.The Access database has exactlythe same column names as the SQL-server databaseand it is something simplelike:Control ID Phone Name Address1 Address2Does anyone know the SQL commandsto do this kind of SQL server database update from theabove mentioned Access database?The previous person left an app which connectsthe two databases and leaves a placewhere the SQL commands are to be typed.ThanksTAK

View 2 Replies View Related

Error Updating DB2 On MVS-MF From A Linked Server

Apr 30, 2007

Need to be able to run update queries on DB2 on IBM MF from a Linked Server. Select and Insert queries work but Update and Delete queries don't. DB2 connect is installed and ODBC System dsn's are created for DEV and Production DB2 environments.



The ODBC drivers can be selected when running Imports/Exports but can't be specified through a linked server.



Any Ideas??



Tom...

View 1 Replies View Related

Updating The Image Field In SQL Server Via DataSet

Aug 22, 2006

Hello,I am trying to update the Image type field named as BlobData in sqlServer 2000. I capture the record in a data set to have the schema and try to update the BlobData field of type Image by assigning it a value of buffer as below. but my assignment seems to be wrong and generates an error saying Object reference not set.
Code=========================
Dim fileBuffer(contentLength) As Byte
 
Dim attachmentFile As HttpPostedFile = Me.fileUpload_fu.PostedFile
 
attachmentFile.InputStream.Read(fileBuffer, 0, contentLength)
 
Dim cn As SqlClient.SqlConnection
 
Try
      Dim sSQL As String
      Dim cmd As SqlClient.SqlCommand
      Dim da As SqlClient.SqlDataAdapter
      Dim ds As System.Data.DataSet = New DataSet
 
      cn = New SqlClient.SqlConnection(myconnectionString)
 
cn.Open()
 
sSQL = "SELECT * FROM Attachment WHERE ID = " & attachmentRecordID
                           
cmd = New SqlClient.SqlCommand(sSQL, cn)
 
da = New SqlClient.SqlDataAdapter(cmd)
 
da.Fill(ds)
If (ds.Tables(0).Rows.Count = 1) Then
 
      ' ======================================
' ERROR GENERATED HERE
' ======================================
 
ds.Tables("Attachment").Rows(0).Item("BlobData") = fileBuffer
' ====================================== 
 
da.Update(ds, "Attachment")
 
Return True
Else
Return False
End If
 
Catch ex As Exception
Me.error_lbl.Text = ex.Message
            Return False
Finally
cn.Close()
End Try
==========================
Can anyone please help this one out.
Cheers.Imran.

View 2 Replies View Related

Error Updating Dynamic Datagrid With SQL Server

Jun 29, 2007

Hello, I have a datagrid which is populated with data from an MS SQL server database. When I run an update query it always throws an exception - what is the most likely cause for this given that I am using the code below:  1 public void DataGrid_Update(Object sender, DataGridCommandEventArgs e)
2 {
3 String update = "UPDATE Fruit SET Product = @ID, Quantity = @Q, Price = @P, Total = @T where Product = @Id";
4
5 SqlCommand command = new SqlCommand(update, conn);
6
7 command.Parameters.Add(new SqlParameter("@ID", SqlDbType.NVarChar, 50));
8 command.Parameters.Add(new SqlParameter("@Q", SqlDbType.NVarChar, 50));
9 command.Parameters.Add(new SqlParameter("@P", SqlDbType.NVarChar, 50));
10 command.Parameters.Add(new SqlParameter("@T", SqlDbType.NVarChar, 50));
11 command.Parameters["@ID"].Value = DataGrid.DataKeys[(int)e.Item.ItemIndex];
12 command.Connection.Open();
13
14 try
15 {
16 command.ExecuteNonQuery();
17 Message.InnerHtml = "Update complete!" + update;
18 DataGrid.EditItemIndex = -1;
19 }
20 catch (SqlException exc)
21 {
22 Message.InnerHtml = "Update error.";
23 }
24
25 command.Connection.Close();
26
27 BindGrid();
28 }
 All of the row types in MS SQL server are set to nvarchar(50) - as I thought this would eliminate any inconsistencies in types. Thanks anyone 

View 5 Replies View Related

Updating Columns In Sql Server Table From My Asp Web Page

Oct 1, 2007

I have a web interface where i have listing of several data and check box for inserting data into SQL server 2005 database table,
so I am able to inset data to sql tables using stored procedure. Now the question is i want to update these inserted records(agency approval column inserted as 1) in same table and assign value 1 fot the checked data to column finace approval as 1.
Here is how ia have webclas  library where i script for getting the insert parameterspublic void Process_Payment(ref DataTable TableWithPayments, string Payment)
{SqlCommand InsertCommand = new SqlCommand();
SqlConnection AccessDatabase = new SqlConnection(FinanceSourceWrite.ConnectionString);int PaymentID = 0;
AccessDatabase.Open();
InsertCommand.Connection = AccessDatabase;
 
 
//DataTable TemporaryTable = new DataTable();
//TemporaryTable = TableWithPayments;SqlTransaction TransactionProcess = null;
SqlParameter InsertParameters;foreach (DataRow DataCommentInfo in TableWithPayments.Rows)
{InsertCommand.CommandText = "InsertPaymentList"; //THIS IS my stored procedureInsertCommand.CommandType = CommandType.StoredProcedure;
TransactionProcess = AccessDatabase.BeginTransaction();
// SET ALL THE VALUES FOR THE PARAMETERSInsertParameters = InsertCommand.Parameters.Add("@JC_ID", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["JC_ID"];
 InsertParameters = InsertCommand.Parameters.Add("@Payment_Type", SqlDbType.Int);
InsertParameters.Direction = ParameterDirection.Input;InsertParameters.Value = DataCommentInfo["Payment_Type_ID"];
 
InsertParameters = InsertCommand.Parameters.Add("@Agency_approval", SqlDbType.Int);InsertParameters.Direction = ParameterDirection.Input;
InsertParameters.Value = DataCommentInfo["Agency_approval"];
 
Now my stored procedure
  
ALTER PROCEDURE [dbo].[InsertPaymentList](
@JC_ID int ,
@Payment_Type int,
@Payment_Group int,
@AGENCY_ID int,
@Agency_approval int,
@Agency_approval_date datetime,
@Program_ID nvarchar(50),
@Status bit,
@Jobsite_code_ID int,
@Date_Stamp datetime,
@Provider nvarchar(50),
@UserName nvarchar(256),
@Activity_ID int,
@Subproject_ID int,
@Payment_Support_Retention_List_ID int,
@WPR_ID int,
@Placement_ID int,
@Enrollment_ID int,
@Satisfaction_ID int,
@Enrollment_Bonus_ID int,
@Re_Placement_Bonus_ID int
)AS
INSERT INTO Payment_LIST_AIMS
(JC_ID, Payment_Type, Payment_Group, AGENCY_ID, Agency_approval,
Agency_approval_date, Program_ID, status,
Jobsite_code_ID, Provider, UserName, Placement_ACTV_ID, Placement_Sub_ID, Support_Retention_ID, WPR_ID, Placement_ID,Enrollment_ID,Satisfaction_ID,Enrollment_Bonus_ID,Re_Placement_Bonus_ID)
VALUES  @JC_ID, @Payment_Type, @Payment_Group, @AGENCY_ID, @Agency_approval,@Agency_approval_date, @Program_ID, @Status,
@Jobsite_code_ID, @Provider, @UserName, @Activity_ID,
@Subproject_ID, @Payment_Support_Retention_List_ID, @WPR_ID, @Placement_ID,@Enrollment_ID,@Satisfaction_ID,@Enrollment_Bonus_ID,@Re_Placement_Bonus_ID)
SELECT CAST(scope_identity() as int)
Here like you see agency approval column in SQL server table gets value assigned as 1 when Agency user clicks the confirm payment button and so all the values as above....
Now another user Finance user process the same records from the web UI and clicks the process payment button at this stage ..i need to update Finance approval column as 1 agains that particular record existing th the SQL table, there are two three coulmc to be updated , Finance approval(this is where i need help) , Finance approval date , and user
 Being a newbie please help me whith how i can fix this
Thanks
Santosh
 
 
 

View 1 Replies View Related

Help On Updating 1.3 Million Rows On The Production Server

May 4, 2000

I need to update about 1.3 million rows in a table of mine.
I am getting the data from one of the columns of the same table and
updating the new column.
I am doing this using a cursor which I have put in a stored procedure.
As this is a production table which users might be accessing.It is a
web based application and I can't slow the system down.
So I am willing to run the stored prcedure during off peak hours.
However, do I need to put this in a transaction?
If I did put it in a transaction what type of isolation level should I
opt for?
Data integrity is very important for me and I don't mind to compromise
on the performance.
I am doing this because one of the columns which has "short description"
entry is has become too small for business purposes and we want to increase it's
length from varchar(100) to varchar(150).
As this is SQL 6.5, I can't increase the lenght of the column.
So Iadded a new column and will run the stored proc.
What precautions are to be taken?
This is on a high priority basis and very important too.

Thanks in advance...

Stored procedure code:

USE DB_Registration_Dev
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='usp_update_product' AND TYPE='P')
DROP PROCEDURE usp_update_product
GO
CREATE PROC usp_update_product
AS
DECLARE @short_desc varchar(100)
DECLARE @prod_id int

DECLARE sdesc_curs CURSOR
FOR
SELECT [Product].[product_id] , [Product].[short_description]
FROM Product

OPEN sdesc_curs

FETCH NEXT FROM sdesc_curs
INTO @prod_id, @short_desc

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Product
SET [Product].[sdesc] = @short_desc
WHERE Product_id=@prod_id
FETCH NEXT FROM sdesc_curs
INTO @prod_id, @short_desc
IF @@FETCH_STATUS <> 0
PRINT ' Finished Updating the table...go ahead and have fun ...! '
END
DEALLOCATE sdesc_curs
GO

View 1 Replies View Related

Cross Server Updating Via Trigger Issue

May 22, 2002

I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,

the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.

serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

thanks for the help
David

View 2 Replies View Related

Updating A Record On A SQL 6.5 From Data In SQL 2000 Server

Sep 30, 2004

I need to update one row in a SQL Server 6.5 DB from a row in SQL 2000 server DB. What would be the best way to do this?


I have my 2000 server defined as a Remote Server in 6.5, however I get the error message:

contains more than the maximum number of prefixes. The maximum is 2.

View 9 Replies View Related

Updating Varchar Field In SQL Server 2000

Oct 4, 2004

I would like to update a varchar field with an update statement that appends information to what is currently stored in the field, for example in the Statement field I may currently have a name (Mark) and I want to add a unique identifier to the end of the name so that it may look like, Markq1572, is there a way to do this in an update statement?

View 1 Replies View Related

SQL Server 2012 :: XML - Updating Parts Of Fields

May 30, 2014

I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>22:30:00</time><timezoneId>0</timezoneId><timezone>America/New_York</timezone></schedule></scheduleitem>

After the query is run, rows like the above would be changed to

<scheduleitem><schedule><frequency><weekly weeklyInterval="1"><WED/></weekly></frequency><startDate>2008-08-05</startDate><time>21:30:00</time><timezoneId>1</timezoneId><timezone>America/Chicago</timezone></schedule></scheduleitem>

View 6 Replies View Related

Updating Changed Row Via Trigger In SQL Server 2000?

Jul 20, 2005

Hi All,I'm a relatively newbie to SQL Server 2000, having come from a MySQLbackground.I'm creating my first Trigger statement on a table, and I'd like toknow how I go about performing an update on the row that was changedwhen the trigger was fired.To explain, I have 2 columns, one which contains a member number, theother which contains a flag that is supposed to indicate whether ornot the member number in the row has changed since the last time thetable was processed for updates.So, whenever the value in the member number field [memnum] is updated,I want to set the flag [igproc] to true.The best I've been able to do is:CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]FOR UPDATEASdeclare @key as intIF UPDATE (memnum)select @key = recid from insertedUPDATE dd_testtable set igproc=1 where recid=@keyThis seems to work, but I'd like to know if there's a better way ofretrieving the recid value of the changed row to pass to the UPDATEstatement? Also, I read somewhere in passing that using SELECTstatements and variable assignments within triggers can cause problemswhen called from other applications; in this case it will either be aweb site using ASP.or an application developed in FOXPRO. I can't findwhere I read this originally, so it's entirely possible I imagined itor misunderstood it, but I'd very much appreciate it if someone couldconfirm whether or not this is the case?Many, many thanks in advance!Much warmth,Murray

View 2 Replies View Related

Updating More Than 10000 Records SQL Server 2000

Jul 20, 2005

Hi allI just ranUPDATE dbo.tbl_forecastedSET update_ref = 0but it only updated the first 10000 records. I'm wondering if anyone cantell me why this is, and can I get around it.I have looked on google and found a few references, but nothing in too muchdetailsthanks in advanceAndy

View 2 Replies View Related

Trouble Updating SQL Server Express Database

Aug 18, 2007

I am having trouble updating my database. I have seen a couple of other people had the same problem. Two answers I had found and tried did not work. The updating if newer I had already came across in a step by step class room tutorial written by a college professor and to make sure the save procedure came after the update procedure. I did need to make this last change but its still not updating. Any further advice on this subject would be appreciated.
.

View 1 Replies View Related

Performance Problems After Updating To SQL Server 2005

May 3, 2007

We have updated to SQL Server 2005, let€™s say, in a hurry without thinking or testing. Databases were attached to the new instance of SQL Server 2005. It looked great when I tested it alone but then a new day come and as all users logged into the system we had got a big problem. The response times are very long and users receive time out errors all the time.

A little background:

The instance of SQL Server 2005 is installed on the same server as 2000 was installed on. 2000 has been uninstalled. It is a Xenon 3.2 GHz with 2GB RAM and SCSI raid. Data and logs are on different spins.

Application is an old ASP code and some parts are not optimized at all. But it worked fine on SQL Server 2000.

What could be the problem?

I really don€™t want to downgrade to SQL Server 2000.

View 2 Replies View Related

Updating Aspnetdb.mdf Once It Is Launched To The Website Hosting Server

Oct 5, 2006

I'm having my website hosted with webhost4life.com which is very helpful and a really great company if you are using Visual Web Developer with SQL Express.  The question I have for anyone who can help me is that my site will offer member roles for customer account maintenance with login and the works.  As you know, when you use memberships with Visual Web Developer, it associates all these controls with a database called aspnetdb.mdf.  And you maintain this database through the asp.net configuration control.  Once this database is launched, I've learned how to access it by changing my web.config file removing the LocalSQLServer and adding one with the connection string to the aspnetdb.mdf database on the server.  My question is, once I've done this and connected through that string, can I still use the asp.net configuration to edit the database (e.g. the users, roles, etc)?

View 1 Replies View Related

SQL Server 2012 :: Updating A Field Based On Result Set

Feb 21, 2014

I am trying to update records based on the results of a query with a subquery.

The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.

When I run the update statement all records are updated and not the result set when I run the query by itself.

Below you will find the code I am running:

create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int

[Code] ....

Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.

Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)

[Code] ...

Why is the update changing each record? How can I change the update to choose the correct records?

View 6 Replies View Related







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