Conecting linked servers using VB6.0

I have an SQL web site linked to may SQL Server. I am using the IP address t
o
do this.
Then I tried to make an ODBC to link VB6.0 to update some tables in the
linked server, but it always give me an error message saying that the server
doesn't exist or i do not have permitions. I am using the same permitions I
used to create the linked server, so i do not know what else i can do.
can any body help me?Why aren't you simply connecting directly to the server instead of through S
QL
Server's linked server?
If you want to connect to a single source but have data from multiple source
s,
then create stored proces and views that query the linked server. Granted, t
his
will be slower than querying the linked server directly.
HTH
Thomas
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>I have an SQL web site linked to may SQL Server. I am using the IP address
to
> do this.
> Then I tried to make an ODBC to link VB6.0 to update some tables in the
> linked server, but it always give me an error message saying that the serv
er
> doesn't exist or i do not have permitions. I am using the same permitions
I
> used to create the linked server, so i do not know what else i can do.
> can any body help me?|||Of course, why didn't I think about it before?
Thanks a lot!
"Thomas" wrote:

> Why aren't you simply connecting directly to the server instead of through
SQL
> Server's linked server?
> If you want to connect to a single source but have data from multiple sour
ces,
> then create stored proces and views that query the linked server. Granted,
this
> will be slower than querying the linked server directly.
>
> HTH
>
> Thomas
>
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in messag
e
> news:0C040BE5-A598-489C-9EC9-F681841EE641@.microsoft.com...
>
>sqlsql

Conditionalize field values based on other field values

Here's a portion of the current statement.

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = 0,

bControlRiskIsAffirmed = 0,

bCombinedRiskIsAffirmed = 0,

bApproachTypeIsAffirmed = 0,

bCommentsIsAffirmed = 0

FROM EngagementAuditAreas WITH(NOLOCK) ...

And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),

bControlRiskIsAffirmed = (numControlRiskID IS NULL),

bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),

bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),

bCommentsIsAffirmed = (parent.txtComments IS NULL)

FROM EngagementAuditAreas WITH(NOLOCK)

Thanks.

Here is a small example of how you might accomplish your task.

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Affirmed char(4),
Num int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( NULL, 1 )
INSERT INTO @.MyTable VALUES ( NULL, 0 )
INSERT INTO @.MyTable VALUES ( NULL, NULL )

UPDATE @.MyTable
SET Affirmed = CASE Num
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE 'n/a'
END

SELECT *
FROM @.MyTable

RowID Affirmed Num
-- -- --
1 No 1
2 Yes 0
3 n/a NULL

However, it is usually NOT a good idea to have two columns that contain the same information (even if in two forms). You can easily 'transform' the values in the select queries using the same CASE structure as above.

conditional update within value

I have a column of data in SQL Server 2000 that I need to replace
values within it with new values. I know how to use CASE statements to
do conditional updates but not how to do this. Here is an example, not
the real example as the values relevant to my company would mean little
to anyone.
If value contains "name", replace it with "fullname"
If value contains "address", replace it with "fulladdress"
and so on...
What I want to do in the field is the following:
Field value now: abc##name##123
Field after change: abc##fullname###123
Field value now: asdlfkjlsdkafjnameasldfjk123
Field after change: asdlfkjlsdkafjfullnameasldfjk123
Field value now: adlsfkjaddresslksdfj34
Field after change: adlsfkjfulladdresslksdfj34
And update all rows in the approriate column with the above logic.
Any ideas?
Thanks.
JRYou don't need a Case statement to do this, you can use
Update #t Set foo = Replace (Replace (foo, 'address', 'fulladdress'),
'name', 'fullname')
Where foo Like '%name%' Or foo Like '%address%'
You could also do it with a Case statement like
Update #t Set foo = Case
When foo Like '%name%' Then Replace (foo, 'name', 'fullname')
When foo Like '%address%' Then Replace (foo, 'address', 'fulladdress')
Else foo
End
Where foo Like '%name%' Or foo Like '%address%'
Please note, however, that depending on your data, those two statements may
do different things. If a row has both "name" and "address" in that column,
the first update statement will change both name and address, but the Case
statement version will update only name to fullname, but won't change
address in that row.
Tom
"JR" <jriker1@.yahoo.com> wrote in message
news:1142706489.831624.92670@.j33g2000cwa.googlegroups.com...
>I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>|||You might want to have a look at STUFF as well, although REPLACE may well do
the trick.
The thing about CASE expressions is that they are 'falling rock' ie for the
first WHEN condition it finds to be true, it will return the THEN bit and
exit the statement. So if your string has multiple bits that need to
replacing, you'll need to run the UPDATE multiple times.
Hope that helps.
Damien
"JR" wrote:

> I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>

Conditional update

Hello all, my update statement works as expected, but lacks some conditional logic. How can I change the statement to not decrement qtyonhand if the quantity is 0? Additionally, I would need to return to the calling application something that would allow me to populate a label with a message to the user.. How can that be accomplished?

Here is my sproc:
CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
GO

Here is my app code:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

(1) Get the quantity into a variable and check if its > 0, only then update.

(2) You can use an OUTPUT parameter to return values back to the application. To retrieve the value returned through an OUTPUT parameter add the parameter to the collection and set its direction to OUTPUT. Check the second part ofthis articlefor some code.

CREATE PROCEDURE [webuser].[cssp_removeItem] (
@.lblID int
,@.result int OUTPUT )
AS

BEGIN
SET NOCOUNT ON

DECLARE @.qty int

SET @.result = 0

SELECT
@.qty = qtyonhand
FROM
cstb_inventory
WHERE
Id = @.lblID

IF @.qty > 0
BEGIN

UPDATE
cstb_inventory
SET
qtyonhand = qtyonhand -1
WHERE
Id = @.lblID
SET @.result = @.@.ROWCOUNT

END

SET NOCOUNT OFF
END
GO

|||

Thanks! that helped out a lot.

|||CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int

AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblIDAND qtyonhand>=1
GO

You can also use qtyonhand>0 if the field is an integer type.

|||

Thank you for responding. For those watching, this works.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue =qtyonhand
FROM cstb_inventory
where Id = @.lblID
GO

And this:

Try

Dim cmdAs SqlCommand = cn.CreateCommand

cmd =New SqlCommand("cssp_removeItem", cn)

cmd.CommandType = CommandType.StoredProcedure

With cmd

cmd.Parameters.Add("@.lblId", SqlDbType.Int).Value = lblId.Text

cmd.Parameters.Add("@.newValue", SqlDbType.Int).Direction = ParameterDirection.Output

EndWith

IfNot cn.State = ConnectionState.OpenThen

cn.Open()

EndIf

cmd.ExecuteNonQuery()

lblMessage.Text = cmd.Parameters("@.newValue").Value.ToString()

If lblMessage.Text = 0Then

lblMessage2.Text ="There are no more parts at this location"

EndIf

Catch exAs Exception

Response.Write(ex.ToString)

Finally

IfNot cn.State = ConnectionState.ClosedThen

cn.Close()

cn =Nothing

EndIf

ProductGrid.DataBind()

EndTry

|||

Of course, if you had 1 before your call, you would still return the message that there are no more parts available, which I don't believe you want.

CREATE PROCEDURE [webuser].[cssp_removeItem]

@.lblID int
, @.newvalue int OUTPUT
AS

Update cstb_inventory
set qtyonhand = qtyonhand -1
where Id = @.lblID
and qtyonhand > 0

SELECT @.newvalue=@.@.Rowcount
GO

I think is what you want, not only does it save you the database query, but it will return 1 if there was a part available, or 0 if qtyonhand was already 0.

|||Way to go Motley! Your code is a better solution.

Conditional update

I want to update a varbinary(max). If the column is null, then I will just
set it, but if not null, then append with a .Write. Something like:
row = select...
if (row.Document = null)
Update myTable
Set Document = 0xFF
where FileName = 'Text99.txt';
else
UPDATE myTable
SET Document .WRITE(0xFF, null, 0)
WHERE FileName = 'Text99.txt';
What is the pattern to do this sort of thing? TIA
William Stacey [MVP]William Stacey [MVP] wrote:
> I want to update a varbinary(max). If the column is null, then I
> will just set it, but if not null, then append with a .Write.
> Something like:
> row = select...
> if (row.Document = null)
> Update myTable
> Set Document = 0xFF
> where FileName = 'Text99.txt';
> else
> UPDATE myTable
> SET Document .WRITE(0xFF, null, 0)
> WHERE FileName = 'Text99.txt';
> What is the pattern to do this sort of thing? TIA
Update MyTable
Set Document =
CASE ISNULL(Document, -99)
WHEN -99 THEN SOMETHING
WHEN Document THEN SOMETHING_ELSE
END
Where FileName = 'Text99.txt'
Even easier would be to write a stored procedure and set the new column
value accordingly using a local variable.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David.
William Stacey [MVP]

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

Conditional if within a Select Where statement?

Update #tempResourceMetrics
Set RequestsStartPeriod = (Select Count(Distinct ProjectID) From #tempResourceAllocation
Where #tempResourceAllocation.ParentDepartmentID = #tempResourceMetrics.ProjectDivisionID
And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)
And #tempResourceAllocation.ProjectStatusID In (1, 2, 3, 4)
And #tempResourceAllocation.ProjectCategoryID = 1333)

In the second condition I'm using Month() to ensure that the totals I get for this column are calculated from the entries created in the preceeding month. The problem appears in January when the preceeding month becomes 12 as opposed to 1(what my code would think) and also the year changes.

How can I modify my select or update statements so that this logic would be included correctly?

Try:

> And (Month(#tempResourceAllocation.StartDate) = Month(GETDATE()) - 1)

And (

#tempResourceAllocation.StartDate >= convert(varchar(6) , dateadd(month, -1, getdate()), 112) + '01'

and

#tempResourceAllocation.StartDate < convert(varchar(6) , getdate()) + '01'

)

AMB

|||

This should work also, AND has the advantage that it will use any indexing on StartDate:

Code Snippet

AND ( #tempResourceAllocation.StartDate >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND #tempResourceAllocation.StartDate < dateadd( month, datediff( month, 0, getdate() ), 0 )

)

|||
Thanks for your help guys! I'll try out both solutions.

Conditional column NAME on insert/update

I need to dinamically select a column in which to insert based on a
parameter, I have this code, but it throws an incorrect syntax
error.
The value that I'm inserting is always static (the current date) what I
need to be dynamic is the column in which it'll be inserted.
How do I dinamically select a column to insert based on a parameter?
Create PROCEDURE dbo.UpdateDetalleOT (
@.eotId int, --Parameter
)
insert into OT (
select Case
when @.eotId = 1 THEN OTFechaBorrador
when @.eotId = 2 THEN OTFechaAAsignar
end
) values ......
Here's the explanation of the case:
Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished...)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...
That's why I need to create a dynamic Insert/Update, because depending
the
state the Job Order will be saved...will depend which column
(DraftDate, ConfirmedDate, etc) to insert the current
date.
Best Regards
Fabio CavassiniYou should go out of your way to avoid dynamic SQL. If that means you have
to write several nearly identical insert statements, then so be it. A
little redundant code is a whole lot easier to understand and to maintain
and a whole lot more secure than dynamic SQL. You could also specify all
columns in the column list and then use CASE in a SELECT clause to insert
NULLs into the nonrelevant columns (That's what will be inserted anyway if
column values aren't supplied.).
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>|||"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137972528.339815.240160@.g14g2000cwa.googlegroups.com...
>I need to dinamically select a column in which to insert based on a
> parameter, I have this code, but it throws an incorrect syntax
> error.
> The value that I'm inserting is always static (the current date) what I
> need to be dynamic is the column in which it'll be inserted.
> How do I dinamically select a column to insert based on a parameter?
> Create PROCEDURE dbo.UpdateDetalleOT (
> @.eotId int, --Parameter
> )
> insert into OT (
> select Case
> when @.eotId = 1 THEN OTFechaBorrador
> when @.eotId = 2 THEN OTFechaAAsignar
> end
> ) values ......
> Here's the explanation of the case:
> Suppose that you have a Job Order that goes over diferrent states
> (Draft, Confirmed, Assigned, Finished...)
> Well, I need to save the Date when the Job Order changed it's state, so
> I have the following columns in the JobOrder Table:
> DraftDate : Date when the Job Order get's the Draft state
> ConfirmedDate : Date when the Job Order get's the Confirmed state
> AssignedDate : Date when the Job Order get's the Assignedstate
> etc...
> That's why I need to create a dynamic Insert/Update, because depending
> the
> state the Job Order will be saved...will depend which column
> (DraftDate, ConfirmedDate, etc) to insert the current
> date.
> Best Regards
> Fabio Cavassini
>
In an INSERT there is no need to do such a thing. Obviously ALL columns are
affected by an INSERT statement, so just specify values for the ones you
want to populate and defaults or nulls for the ones you don't.
In the case of UPDATE you can use the general form:
UPDATE tbl
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3),
..
WHERE ...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I didn't have realized that every column is affected with the insert,
I'll use condition for the values...and use COALESCE for the update.
Thanks very much for your help
Best Regards
Fabio Cavassini|||This is how I implemented:

>INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
>SELECT CASE WHEN @.eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
> CASE WHEN @.eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
I include all columns and the insert, and then I conditionally select
the value (null or current date) according to the parameter value.
Best Regards
Fabio Cavassini

Condition validation on Crystal report Fields

Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

ThnaksIf the field was numeric in the DB, and you want to change the value of the field in some records to an alphanumeric ('Good') you gotta problem. Create another field.|||Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

Thnaks

you can't update field of your data base and which are used in
crystal report.

you would have create a formula to it.

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
Todd
Can you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd
|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of th
e
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted r
ow
> to an audit table. .Net is calling the same stored procedure to update th
e
> same row in the base table 4 times. The stored procedure subtracts a pass
ed
> in value from a column in the base table. After the code runs, the value
in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of
the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger t
o
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],
[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[Tota
lAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
>

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:
> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
> > I have a table that I am auditing by having a trigger insert the Deleted row
> > to an audit table. .Net is calling the same stored procedure to update the
> > same row in the base table 4 times. The stored procedure subtracts a passed
> > in value from a column in the base table. After the code runs, the value in
> > the base table is correct, but the audit rows appear to show the the first
> > update subtracted first two amounts. The other weird thing is the time
> > stamp, which is generated from a getdate() is exactly the same for two of the
> > rows.
> >
> > How tightly is the trigger code tied to the code that causes the trigger to
> > fire? We have tried playing with isolation levels on the .Net transaction
> > and this hasn't helped. Have a tripped on a bug, or am I doing something
> > wrong. Thanks for the help.
> >
> > Todd

Concurrent Insert and Update Commands

Hi there,

I've had a look at this thread - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=104399&SiteID=1 - but it's a bit old so I thought I'd try to clarify a couple of things.

I have a data flow which inserts or updates a table in a SQL 2000 database.

The insert is done via an OLE DB destination and the update is done via an OLE DB Command.

As stated in the previous thread, when these commands run concurrently issues arise with blocking etc.

The only way I've managed to get around it is by setting the access mode of the insert to OpenRowset.

So 2 questions:

1) Does anyone know how this problem occurrs and what the best way to get around it is?

2) Are there issues around NOT using the fast load options - I've read stuff about the double-byte character set etc but I admit I don't quite understand it.

Thanks.

I personally prefer to push my data to be updated into a raw file and then do the update in a seperate data-flow.

-Jamie

|||

Some points that might help:

Using Fast load defaults to having table lock on. You can turn table lock off (and keep Fast load.)

concatenation?

I have a field named "changes" it is nvarchar (150) it is in a table called changes_log.

A new row will be inserted everytime that an update statement occurs on another table and it should describe what was updated. I want to essential check if each new value is = to old value and if it is add that to a string that will become the value of "changes" for the new row.
It might contain 1 field name or many.

Here is the SQL code I am trying to work with inside a stored procedure:

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
END

But it only adds "ROUTER_NAME" even when both are changed. Maybe I'm going about this all wrong, can anyone offer me and tips on how to log what fields were changed in the database, by who and when?Your UPDATE statments have no WHERE clause. Other than that, I am not clear on exactly what you are trying to do.|||You are right! I totally forgot and left that out!

INSERT INTO CHANGES_LOG(ITEM_NAME, _DATE, USER_ID) VALUES(@.NAME, GETDATE(), @.USER_ID)
IF NOT @.NAME = @.ROUTER
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'SERIAL_NUM '
WHERE ITEM_NAME = @.NAME
END
select * from changes_log

output (using a different or changed name and serial_num so that both if statements would be true:

ITEM_NAME _DATE USER_ID CHANGES
------------------------------
NEW 10/17/2003 12:17:33 PM LOUMAS\NCL4504S ROUTER_NAME

Where Changes should contain "ROUTER_NAME SERIAL_NUM"

I might be approaching this all wrong but here is what the requirement is:

KEEP A LOG OF EACH ITEM THAT HAS BEEN EDITED, WHO EDITED IT, WHEN DID THEY DO IT, WHAT FIELDS WERE CHANGED.

NOTE: these items are always edited through a stored procedure. there many field unique for different types of items (from different tables) that could be edited. that is why I attempted to simply capture the field name of field that was changed.

Any ideas or help is welcome, I can change the structure of the Changes_Log table (above) if necessary. I can start from scratch if necesary.|||This is incorrect:

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = CHANGES + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

|||thanks for the reply. I made your suggested change and the result is "changes" in the new row = NULL|||try...

BEGIN
UPDATE CHANGES_LOG
SET CHANGES = IsNull(CHANGES,'') + 'ROUTER_NAME '
WHERE ITEM_NAME = @.NAME
END

I gather CHANGES defaults to NULL, and so NULL + anything is NULL. IsNull returns the leftmost non-null argument, and thus should work.|||If I understand correctly you want one record in changes_log per update with a concatenated list of changes in the changes field indicating which fields in another table have been edited.

Why don't you declare a @.Changed varchar and assemble that before making any inserts to changes_log? Do a single insert at the end instead of an insert and two updates.

Are you launching this from a before trigger?
Where does @.Router come from?

Glenn|||YES IT WORKED! THANKS SO MUCH!|||OK, you do understand what I am trying to do correctly.

I'm not sure what you are getting at with declaring a @.Changed varchar? Is this a temporary variable to do a single insert? If so that sounds like a good idea, I just wanted to get something to work first. Actually there will be way more than 2 updates when complete, unless I do it this way.

I am not using any triggers.

@.Router is a value passed to the stored procedure that identifies the item by it's original name even if the name has been changed. @.Name would be the new name if name has been changed, otherwise @.Name = @.Router.

Thanks.|||Ok I changed my code as you suggested and things were working fine, until after adding all the fields I realized that if the current value of a field is NULL the IF statement returns false. Logically I think

IF NOT (@.NEW_VALUE = (SELECT NAME FROM TABLE WHERE ID = @.ID) )

where the @.NEW_VALUE is not NULL and the NAME value returned is null, should return true but it must not compare the same for NULL or something because only those statements do no execute.

Here is my code: note that I tried checking if the value returned from select is null on some items but it still did not work.

ALTER PROCEDURE sp_UPDATE_ROUTER
(
@.ROUTER nvarchar(50),
@.NAME nvarchar(50) = @.ROUTER, /*IF NO NEW NAME IS PASSED DEFAULT = CURRENT ROUTER NAME*/
@.SERIAL nvarchar(25) = NULL,
@.MODEL nvarchar(30) = NULL,
@.IOS_VER nvarchar(15) = NULL,
@.IOS_BASED nvarchar(3) = NULL,
@.BOOT_VER nvarchar(15) = NULL,
@.NETWORK nvarchar(3) = NULL,
@.FIREWALL nvarchar(16)= NULL,
@.REACH nvarchar(60) = NULL,
@.DIAL nvarchar(30) = NULL,
@.FAC_ID nvarchar(20) = NULL,
@.FAC_NAME nvarchar(40) = NULL,
@.STREET nvarchar(60) = NULL,
@.CITY nvarchar(40) = NULL,
@.STATE nvarchar(2) = NULL,
@.ZIP nvarchar(10) = NULL,
@.CONTACT nvarchar(60) = NULL,
@.CONTACT2 nvarchar(60) = NULL,
@.PO nvarchar(20) = NULL,
@.MO nvarchar(20) = NULL,
@.COMMENTS nvarchar(50) = NULL,
@.USER_ID nvarchar(50)
)
AS
DECLARE @.TEMP nvarchar(300)
IF NOT @.NAME = @.ROUTER
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'ROUTER_NAME '
END
IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
IF NOT @.MODEL = (SELECT MODEL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MODEL '
END
IF NOT @.IOS_BASED = (SELECT IOS_BASED FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_BASED '
END
IF NOT @.IOS_VER = (SELECT IOS_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'IOS_VER '
END
IF NOT @.NETWORK = (SELECT NETWORK FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'NETWORK '
END
IF NOT @.FIREWALL = (SELECT FIREWALL FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FIREWALL '
END
IF NOT (@.BOOT_VER = (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT BOOT_VER FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'BOOT_VER '
END
IF NOT (@.REACH = (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) OR (SELECT REACHABLE_FROM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER) = NULL)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'REACHABLE_FROM '
END
IF NOT @.DIAL = (SELECT DIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'DIAL_NUM '
END
IF NOT @.FAC_ID = (SELECT FACILITY_ID FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY_ID '
END
IF (NOT @.FAC_NAME = (SELECT FACILITY_NAME FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STREET = (SELECT STREET FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.CITY = (SELECT CITY FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.STATE = (SELECT STATE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)) OR
(NOT @.ZIP = (SELECT ZIP_CODE FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER))
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'FACILITY ADDR '
END
IF NOT @.CONTACT = (SELECT CONTACT FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT '
END
IF NOT @.CONTACT2 = (SELECT CONTACT2 FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'CONTACT2 '
END
IF NOT @.PO = (SELECT PO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'PO '
END
IF NOT @.MO = (SELECT MO_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'MO '
END
IF NOT @.COMMENTS = (SELECT COMMENTS FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'COMMENTS '
END
INSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, SERIAL_NUM, USER_ID, CHANGES) VALUES(@.NAME, GETDATE(), @.SERIAL, @.USER_ID, @.TEMP)
select * from changes_log
RETURN

THANKS|||What I meant with the @.Changed varchar was what you did with @.TEMP.|||If I understand your problem, you need to check the @.Parameters if they are null before comparing them with the previous value selected from ROUTERS.

A nested IF for each value would work.

IF NOT IsNull(@.SERIAL,'') = ''
BEGIN
 IF NOT @.SERIAL = (SELECT SERIAL_NUM FROM ROUTERS WHERE ROUTER_NAME = @.ROUTER)
BEGIN
SET @.TEMP = IsNull(@.TEMP,'') + 'SERIAL_NUM '
END
END

Also I think you should be able to SET @.TEMP = '' right off, and eliminate the IsNull check in your existing code. This would go back to the simpler...

 SET @.TEMP = @.Temp + 'SERIAL_NUM '

I don't know if that will finish it, but it's another step...|||well I cna tell you just to test it I used 'xyz' for every value and none are null and none currently have a value of 'xyz'|||And?
What were your results? Your changelog should've shown a change for each field right? Did it work?|||The same as above, I posted that message with the datavalues that I described. I have already thought of all that.

Concatenation and NTEXT

Hi everyone,
I have a problem trying to update an NTEXT column by enclosing it
between two strings, as shown in the SQL below:
UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
The error I get back is this:
Invalid operator for data type. Operator equals add, type equals ntext.
Please help! I'm using SQL Server 2000.
Thanks,
JonoWhy would you want to update every column in the table with the exact same
enclosure? You can't concatenate to an NTEXT column, and I don't see any
value in doing it the exact same way for every row anyway.
Anyway, since we are talking about HTML, this is something you have the
presentation layer do. For example, it is easier in ASP to say:
<pre><%=rs("NTextField")%></pre>
...than to do what you are proposing. If you really want to do this, see
the UPDATETEXT function in Books Online, but I still recommend against the
approach.
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>|||u need to use UPDATETEXT for this . Use the following example
-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text,
text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998),
NULL
DECLARE @.txtPtr1 Varbinary(16)
DECLARE @.txtPtr2 Varbinary(16)
DECLARE @.txtPtr3 Varbinary(16)
SELECT @.txtPtr1 = TEXTPTR(text1)
FROM TextExample
SELECT @.txtPtr2 = TEXTPTR(text2)
FROM TextExample
UPDATE TextExample
SET Text3 = Text1
WHERE i = 1
SELECT @.txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 ' '
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 TextExample.Text2 @.txtPtr2
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>

concatenating sql fields and parameters

Hi,
I am trying to run a update stored procedure where one of the fields is
dynamic eg.
UPDATE table
SET field_ + @.number = @.a_value
WHERE (key = @.key_value)
@.number is chosen by the user and the field_## can be anything from field_01
to field_99.
Is there a way i can do this? The above method doesn’t work.
Any help will be greatly appreciated.
Many ThanksIn t-SQL, you will have to use each columns explcitly in the SET clause,
with commas separating each column assignments. For syntax, refer to the
topic UPDATE in SQL Server Books Online.
Perhaps with Dynamic SQL you might be able to kludge it out. For details,
refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note, it
is possible that you have a flawed design which force you to use such
meaningless constructs in your code.
Anith|||Hi Vortex
consider rewriting as:
EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
key = ' + @.key_value)
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"vortex" wrote:

> Hi,
> I am trying to run a update stored procedure where one of the fields is
> dynamic eg.
> UPDATE table
> SET field_ + @.number = @.a_value
> WHERE (key = @.key_value)
> @.number is chosen by the user and the field_## can be anything from field_
01
> to field_99.
> Is there a way i can do this? The above method doesn’t work.
> Any help will be greatly appreciated.
> Many Thanks
>|||I will give it a try,
I am creating a stored procedure with your update command, if I use the
method you suggested will SQL have to compile the sp every time a new value
is used or will it just compile the once. (Speed is required, that is why I
am using a sp)
Thanks
Khalid
"Chandra" wrote:
> Hi Vortex
> consider rewriting as:
> EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
> key = ' + @.key_value)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "vortex" wrote:
>|||If you can suggest a better way of doing it, i would be a very happy bunny a
s
i have a lot more stored procedures to write :(
thanks
"Anith Sen" wrote:

> In t-SQL, you will have to use each columns explcitly in the SET clause,
> with commas separating each column assignments. For syntax, refer to the
> topic UPDATE in SQL Server Books Online.
>
> Perhaps with Dynamic SQL you might be able to kludge it out. For details,
> refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note,
it
> is possible that you have a flawed design which force you to use such
> meaningless constructs in your code.
> --
> Anith
>
>|||It is not as ease as it seems. For example, Chandra's solution will fail if
@.number is tinyint/int/bigint because you can not those data types have
greater precedence than varchar so sql server will try to convert 'UPDATE
table SET field_' to tinyint/int/bigint and this will give an error. The sam
e
will happen @.a_value, you have to quote it between apostrophes for char /
varchar / datetime values. The same with @.key_value. You will have to use
dynamic sql and bunch on lines to accomodate the statement to the variables
data type.
I will not write about readability and maintenance of your final code, you
can guess what will be the result.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"vortex" wrote:

> I will give it a try,
> I am creating a stored procedure with your update command, if I use the
> method you suggested will SQL have to compile the sp every time a new valu
e
> is used or will it just compile the once. (Speed is required, that is why
I
> am using a sp)
> Thanks
> Khalid
> "Chandra" wrote:
>
>|||>> If you can suggest a better way of doing it,..
Better way of doing an UPDATE or changing the design? Regarding the UPDATE,
did you refer to the manual for exact syntax?
Regarding the design, with simple one-liners as in your initial post, it is
hard to suggest anything meaningful. Post some more information regarding
this table, the entity type that is being modelled and the attributes
involved. Also provide some details regarding the business model and how
this table fits into the overall schema.
Generally it is hard to provide accurate design suggestions over newsgroup
responses, however with the above requested info, you could perhaps get
started.
Anith