how disable MSX server generated job

I have some jobs that when I want to delete or disable it I get bellow error
Msg:
"Error 14274 : cannot add, update, or delete a job that originated from an
MSX server"
what it means? how can I delete this job?
Thanks
TarvirdiIf memory servers, go into the MSDB.dbo.SysJobs table and update the
originating_server column to be that of the name the job currently resides
on. You should then be able to do whatever you want.
"Tarvirdi" wrote:

> I have some jobs that when I want to delete or disable it I get bellow err
or
> Msg:
> "Error 14274 : cannot add, update, or delete a job that originated from an
> MSX server"
> what it means? how can I delete this job?
> Thanks
> Tarvirdi
>
>

how disable MSX server generated job

I have some jobs that when I want to delete or disable it I get bellow error
Msg:
"Error 14274 : cannot add, update, or delete a job that originated from an
MSX server"
what it means? how can I delete this job?
Thanks
TarvirdiIf memory servers, go into the MSDB.dbo.SysJobs table and update the
originating_server column to be that of the name the job currently resides
on. You should then be able to do whatever you want.
"Tarvirdi" wrote:
> I have some jobs that when I want to delete or disable it I get bellow error
> Msg:
> "Error 14274 : cannot add, update, or delete a job that originated from an
> MSX server"
> what it means? how can I delete this job?
> Thanks
> Tarvirdi
>
>

How can i write polling and processing queries

i dont want to fully update the molap cache and i also dont want to fully read the source database that is why i am using polling query to poll the changes and processing query to get the changed records from database. for information about polling and processing query you can check the link

http://msdn2.microsoft.com/en-us/library/ms188965.aspx

but i am unable to write these queries to get all changes i.e insert, update and delete records can any one tell me how can i use these queries OR SQL Server provide some other way to just read the changes in source database not the whole dabatase.

This is

about proactive caching, right? If the changes in the table happen to be

insert, update and delete then the processing will *most probably* need to be

done fully.

When you end up with full processing, if you need to use polling notification

mechanism then the processing query is not needed. In case of deletes or

updates the polling query should fetch something like timestamp of the last

change in the database. Why not to use automatic SQL notifications?

The link you provided discusses polling and processing queries for incremental

processing. It is possible when only new records appear in the table (only

inserts).

There is one case when incremental processing can be used with "inserts,

deletes and updates". Suppose at time t1 you have a state of records S1 of

your table. Suppose you manage to make proactive caching to be scheduled at

well known time t2. I suppose Client Initiated is the only reliable one for

deterministic start of the proactive caching. If between t1 an t2 the inserts,

updates and deletes in the table do not change the records from S1 but only

create and modify incremental records then it is possible to use incremental

processing with proactive caching.

How can I use Profile.UserName as a parameter in a SqlDataSource

I have looked and tried with no luck on using the Profile.UserName in an SQLDataSource Update select statement. and anyone please help me with this?

UpdateCommand="UPDATE tblDocumentsSET DocumentTypeID = @.DocumentTypeID, DocDescription = @.DocDescription, DocLocation = @.DocLocation, DocStartDate = @.DocStartDate, DocEndDate = @.DocEndDate, LastUpdate =GETDATE(), LastUpdateBy = @.ProfileUserWHERE (DocumentID = @.DocumentID)"

 <UpdateParameters> <asp:Parameter Name="DocumentID" /> <asp:Parameter Name="DocumentTypeID" /> <asp:Parameter Name="DocDescription" /> <asp:Parameter Name="DocLocation" /> <asp:Parameter Name="DocStartDate" /> <asp:Parameter Name="DocEndDate" /> <asp:Parameter Name="ClientID" /> <asp:Parameter Name="ProfileUser" /> </UpdateParameters>

Hi,

you should be able to use the <asp:ProfileParameter>.

Grz, Kris.

|||Thank You Very Much!

How can I update the col value using extended stored procedure

Hi,
I have created extended stored procedure. In that I need to update the value
of the perticular column of the table and return the same value. Is that
possible using extended stored procedure.
Please help me to solve this problem. If you have any sample code plz send
me, it would be more helpful to me.
Regards
VeeruI can think of 2 strategies:
1) If you only have to update a single value at the end of your processing
inside the XP code, you are best of returning this as a output parameter or
set of output parameters and use this inside a regular SP to do an INSERT,
UPDATE or DELETE
2) If you need to do this within the XP, you need to create a loop back
connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
DELETE on the loopback connection. There is a ODBC sample for setting up a
loop back connection using ODBC that ships with SQL Server. The rest is
normal ODBC programming of which there are many samples flying around.
GertD@.SQLDev.Net
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
connect back to SQL Server from an XP and execute SQL commands.
http://msdn.microsoft.com/library/d...
o1f.asp
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||Hi Thanks for your responce.
The documentation in the below path you have sent saying that the sample is
available at x:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz sen
d
if you have that.
"Mike C#" wrote:

> SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
> connect back to SQL Server from an XP and execute SQL commands.
> http://msdn.microsoft.com/library/d...r />
_2o1f.asp
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||Hi Gert E.R. Drapers,
Thanks for your response.
I need to implement first point what you mentioned in your post below in XP
code. Can you please guide me with sample. I just started learning and
working on SQL Server 2005. We are using OLEDB not ODBC.
Regards
Veeru
"Gert E.R. Drapers" wrote:

> I can think of 2 strategies:
> 1) If you only have to update a single value at the end of your processing
> inside the XP code, you are best of returning this as a output parameter o
r
> set of output parameters and use this inside a regular SP to do an INSERT,
> UPDATE or DELETE
> 2) If you need to do this within the XP, you need to create a loop back
> connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
> DELETE on the loopback connection. There is a ODBC sample for setting up a
> loop back connection using ODBC that ships with SQL Server. The rest is
> normal ODBC programming of which there are many samples flying around.
> GertD@.SQLDev.Net
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||BTW if you're using SQL 2005, then don't use extended stored proc's, as they
are depracated. Use the SQLCLR to create assemblies.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>|||It should be in C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib, assuming you installed on the
C: drive. They are installed by the SQL 2000 installer.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>

How can I update relationship tables?

<----Ihave 2 tables are: 'customers (parent)' and 'open_ac (child)'

<---Ihave tried to insert and update data into sql database by using textboxes(don't use datagrid)

<---Mytables details are below

<---thistable uses for keeping user data

customersfields:

Columnname type length Description

cu_id int 4 Primary key Identifiers

cu_fname nvarchar 20 allow null first name

cu_lname nvarchar 40 allow null last name

cu_nat nvarchar 20 allownull nationality

cu_add nvarchar 40 allow null address

cu_wplace nvarchar 40 allownull workplace

cu_tel nvarchar 10 allownull telephone

cu_fax nvarchar 10 allow null fax

cu_email nvarchar 10 allownull email

<--theopen_ac uses for keeping register date/time of customers

open_acfields:

Columnname type length Description

cu_id int 4 link key

op_date date/time 8 register date

<----mycode

ImportsSystem.Data.SqlClient

Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""

Public Property MyError() As String
Get
Returnm_Error
End Get
Set(ByVal Value As String)
m_Error =Value
IfTrim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property

Private Sub Page_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 AsNew MISSQL
'DS =C1.GetDataset("select * from customers;select * from open_ac;select * fromaccounts")
DS =C1.GetDataset("select * from customers;select * from open_ac")

Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()

Dim Dtr AsDataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =Session("data")
iRec =ViewState("iRec")
End If
Me.MyError = ""
End Sub

Public Function BindField(ByVal FieldName As String) AsString
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub

Here isupdate code

Private Sub bUpdate_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)

If DR.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")= txtcu_id.Text
End If

If txtcu_fname.Text.Trim ="" Then
Me.MyError ="please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If

If txtcu_lname.Text.Trim ="" Then
Me.MyError ="please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If

If txtcu_add.Text.Trim ="" Then
Me.MyError ="please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If

If txtcu_occ.Text.Trim ="" Then
Me.MyError ="please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If

If txtcu_wplace.Text.Trim ="" Then
Me.MyError ="please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If

If txtcu_nat.Text.Trim ="" Then
Me.MyError ="Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If

If txtcu_email.Text.Trim ="" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If

Dim Strsql As String
If DR.RowState = DataRowState.AddedThen
Strsql ="insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10)"
Else
Strsql ="update customers setcu_fname=@.P2,cu_lname=@.P3,cu_add=@.P4,cu_occ=@.P5,cu_wplace=@.P6,cu_nat=@.P7,cu_tel=@.P8,cu_fax=@.P9,cu_email=@.P10where cu_id =@.P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =C1.CreateCommand(Strsql)
C1.CreateParam(cmd,"ITTTTTTTTT")
cmd.Parameters("@.P1").Value = DR("cu_id")
cmd.Parameters("@.P2").Value= DR("cu_fname")
cmd.Parameters("@.P3").Value = DR("cu_lname")
cmd.Parameters("@.P4").Value = DR("cu_add")
cmd.Parameters("@.P5").Value = DR("cu_occ")
cmd.Parameters("@.P6").Value = DR("cu_wplace")
cmd.Parameters("@.P7").Value= DR("cu_nat")
cmd.Parameters("@.P8").Value = DR("cu_tel")
cmd.Parameters("@.P9").Value = DR("cu_fax")
cmd.Parameters("@.P10").Value = DR("cu_email")

Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

<---code above in this sub it can update only customers tables and when I tried to coding below

<----it alerts can not update

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")= txtcu_id.Text
End If
If Label2.Text.Trim = ""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
If DR1.RowState =DataRowState.Deleted Then
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If
End Sub
End Class

<--thisis class I use for connecting to database and call parameters...

MISSQLclass

ImportsSystem.Data.SqlClient
Public Class MISSQL
Dim PV As String ="Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &"database=" & m_Database
End Sub
Public Property Database() As String
Get
Returnm_Database
End Get
Set(ByVal Value As String)
m_Database =Value
Strcon = PV& "database=" & m_Database
End Set
End Property

Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String= "Dataset1", _
Optional ByVal TableName As String ="Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function

Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName AsString = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function

Public Function CreateCommand(ByVal Strsql As String) AsSqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function

Public Function Execute(ByVal Strsql As String) AsInteger
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function

Public Function Execute(ByRef Cmd As SqlCommand) AsInteger
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function

Public Sub CreateParam(ByRef Cmd As SqlCommand, ByValStrType As String)
'T:Text, M:Memo, Y:Currency,D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =UCase(Mid(StrType, i, 1))
Dim P1 AsNew SqlParameter
P1.ParameterName = "@.P" & i
Select Casej
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class

<---Thank you in advance

<---and Thank you very much for all help

Hi,

Try the following code :(one part of your code)

Dim DT1As DataTable = DS.Tables(1)
Dim DR1As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
If txtcu_id.Text.Trim =""Then
Me.MyError ="Please enter id"
Exit Sub
End If
DR1("cu_id") = txtcu_id.Text
End If

////If Label2.Text.Trim ="" Then
//// DR1("op_date") = Label2.Text
////End If

If Label2.Text.Trim <>""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1As String
////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.AddedThen
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2As New MISSQL
Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2,"ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1As Integer = C2.Execute(cmd2)
If Y1 > 0Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

Thanks.

|||

So thank you very much for help for coding.

I tried to follow your code below.

>Dim DT1As DataTable = DS.Tables(1)
> Dim DR1As DataRow = DT1.Rows(iRec)
> If DR1.RowState = DataRowState.AddedThen
> If txtcu_id.Text.Trim =""Then
> Me.MyError ="Please enter id"
> Exit Sub
> End If
> DR1("cu_id") = txtcu_id.Text
> End If////If Label2.Text.Trim ="" Then
> //// DR1("op_date") = Label2.Text
> ////End If

> If Label2.Text.Trim <>""Then
> DR1("op_date") = Label2.Text
> End If

> Dim StrSql1As String
> ////If DR1.RowState = DataRowState.Deleted Then
> If DR1.RowState = DataRowState.AddedThen
> StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
> Else
> StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
> End If
> Dim C2As New MISSQL
> Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
> C2.CreateParam(cmd2,"ID")
> cmd2.Parameters("@.P1").Value = DR1("cu_id")
> cmd2.Parameters("@.P2").Value = DR1("op_date")

> Dim Y1As Integer = C2.Execute(cmd2)
> If Y1 > 0Then
> DR1.AcceptChanges()
> Else
> Me.MyError ="Can not register"
> End If

But still show "Can not register" I don't know why?

and I try another way by follow your code and editing something below this can work. But I don't know what I did wrong above

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added Then
DR1("cu_id") = txtcu_id.Text
End If

'////If Label2.Text.Trim = "" Then
' //// DR1("op_date") = Label2.Text
' ////End If

If Label2.Text.Trim <> "" Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
'////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.Added Then
StrSql1 = "insert into open_ac (cu_id,op_date) values (@.P_C1,@.P_C2)"
Else
StrSql1 = "update open_ac set op_date=@.P_C2 where cu_id=@.P_C1"
End If
Dim C2 As New MISSQL
Dim cmd_child As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam_child(cmd_child, "TD")
cmd_child.Parameters("@.P_C1").Value = DR1("cu_id")
cmd_child.Parameters("@.P_C2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd_child)
If Y1 > 0 Then
DR1.AcceptChanges()
End If
End Sub

Thank you very very much for your help

How Can i Update many fields by using SQL

I got myInvTable, myCompanyTable,
I need to update
CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Compan
yContact.
How can I write it by one SQL statment '
Thanks a lotYou can update only one table at a time in a single Transact-SQL statement.
It is unclear from your post what columns belong to which tables. Single
table UPDATE example:
UPDATE myCompanyTable
SET
CompanyAdd1 = 'address 1',
CompanyAdd2 = 'address 2',
CompanyAdd3 = 'address 3',
CompanyAdd4 = 'address 14',
CompanyTel = 'tel 1',
CompanyFax = 'fax 1',
CompanyContact' = 'contact 1'
WHERE myCompanyTableID = 1
Hope this helps.
Dan Guzman
SQL Server MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23U2uIF%23EFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I got myInvTable, myCompanyTable,
> I need to update
> CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Comp
anyContact.
> How can I write it by one SQL statment '
> Thanks a lot
>|||OH, I need to update myInvtable indeed. update the information from the
companytable
Can I
update myinvTable
set companyadd1 = myCompanyTable.companyadd1,
companyadd2 = myCompanyTable.companyadd2,
....etc
where .. myInvTable.companycode = myCompanyTable.companycode ?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:OZroCL%23EFHA.1936@.TK2MS
FTNGP14.phx.gbl...
> You can update only one table at a time in a single Transact-SQL
> statement. It is unclear from your post what columns belong to which
> tables. Single table UPDATE example:
> UPDATE myCompanyTable
> SET
> CompanyAdd1 = 'address 1',
> CompanyAdd2 = 'address 2',
> CompanyAdd3 = 'address 3',
> CompanyAdd4 = 'address 14',
> CompanyTel = 'tel 1',
> CompanyFax = 'fax 1',
> CompanyContact' = 'contact 1'
> WHERE myCompanyTableID = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:%23U2uIF%23EFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||You can use subqueries to solve this problem
update myinvTable
set companyadd1 = (select companyadd1 from myCompanyTable where
companycode=.... ),
companyadd2 = (select companyadd2 from myCompanyTable where companycode=
.... ),,....etc
where companycode = ....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:OaPdiP%23EFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OH, I need to update myInvtable indeed. update the information from the
> companytable
> Can I
> update myinvTable
> set companyadd1 = myCompanyTable.companyadd1,
> companyadd2 = myCompanyTable.companyadd2,
> ....etc
> where .. myInvTable.companycode = myCompanyTable.companycode ?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
glsD:OZroCL%23EFHA.1936@.TK2MSFTNGP14.phx.gbl...
CompanyAdd1,CompanyAdd2,CompanyAdd3,Comp
anyAdd4,CompanyTel,CompanyFax,Compan
yContact.
>|||On Wed, 16 Feb 2005 12:54:27 +0800, Agnes wrote:

>OH, I need to update myInvtable indeed. update the information from the
>companytable
>Can I
(snip)
Hi Agnes,
You can, but you shouldn't. You'd be storing redundant data in your
database.
It's far better to join the Invoices table to the Companies table in the
code that prints invoices.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names> ) REFERENCES <other table name> (<column names> ) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How can i update all table relationships in one go?

Hello experts,
Assume I have created 3 tables in MS SQL 2000:
table A
* a_id (PK)
* name
table B
* b_id (PK)
* a_id
* name
table C
* c_id (PK)
* a_id
* name
Both table B and C have a cascade delete relationship with table A.
Question, now I want to cancel all cascade delete relationships in table
A, how can I do this in just one go? I dont want to go to each
relationship in table A and uncheck the "cascade delete related records"
option.
Thanks,
Benny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Benny,
You need to write a script that will drop the FK constraint and create a
new one. e.g. something like this from Jacco Schalkwijk:
BEGIN TRAN
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY
(<column names>) REFERENCES <other table name> (<column names>) ON DELETE NO
ACTION
COMMIT TRAN
and reverse it with the same with ON DELETE CASCADE when you want to
enable cascading again.
The transaction is there to prevent other users from accessing the table
while you change the foreign key.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Benny wrote:
> Hello experts,
> Assume I have created 3 tables in MS SQL 2000:
> table A
> * a_id (PK)
> * name
> table B
> * b_id (PK)
> * a_id
> * name
> table C
> * c_id (PK)
> * a_id
> * name
> Both table B and C have a cascade delete relationship with table A.
> Question, now I want to cancel all cascade delete relationships in table
> A, how can I do this in just one go? I dont want to go to each
> relationship in table A and uncheck the "cascade delete related records"
> option.
>
> Thanks,
> Benny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:
>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:

>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )
|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:

>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT