Problem in instead of trigger

hi,
i am using instead of trigger in sqlserver 2005, my trigger looks like
create trigger [dbo].[docsUpdate]
on [dbo].[Docs]
instead of update
as
IF (Update(MetaInfo))
BEGIN
bla bla
bla bla
[Some operation]
end
my table looks lke this
Dirname LeafName TimeLastModified Extension Metainfo
-- -- -- -- --
if any data in metainfo column updated then my trigger will get fired
and do respective operation, but when dirname , leafname or any if
other column get modified other them metainfo then how these modified
data will get affected in to my docs table.
Since changes done on table will not get affected due to instead of
trigger, how can i update other column values.
please help me.
thanks
sathya narayanan
narayanan@.gsdindia.comhttp://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&mid=2144dbff-77b7-4327-b3c7-cdfb17b609f8
AMB
"sathya" wrote:
> hi,
>
> i am using instead of trigger in sqlserver 2005, my trigger looks like
>
> create trigger [dbo].[docsUpdate]
> on [dbo].[Docs]
> instead of update
> as
> IF (Update(MetaInfo))
> BEGIN
> bla bla
> bla bla
> [Some operation]
> end
> my table looks lke this
>
> Dirname LeafName TimeLastModified Extension Metainfo
> -- -- -- -- --
>
> if any data in metainfo column updated then my trigger will get fired
> and do respective operation, but when dirname , leafname or any if
> other column get modified other them metainfo then how these modified
> data will get affected in to my docs table.
> Since changes done on table will not get affected due to instead of
> trigger, how can i update other column values.
> please help me.
>
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>

problem in inner join

Hey,

I want to update the price in the items table to be equal to the corrosponding price in the books table but my problem is in the "on stmt", i cant set items.id=books.id because in the items table the id is saved as nvarcharex:b1234 where b identifies a book and in the books table it is saved as int as follows 1234 and this is the only primary key ! so how can i solve it and how can i write the query to update the price in the items table??

Thank you

Hiba

You can try to use some string functions to do so. Fo r example you can try RIGHT(items.id,4)=books.id if both ids' column is nvarchar column. Or

SUBSTRING( items.id, 2,LEN( items.id))=CONVERT(NVARCHAR,books.id)) if the books id is not nvarchar.

|||

Actually you can use RIGHT or SUBSTRING to get you data like RIGHT(book,4) if you are sure that your ID will be no longer then 4 digits or

if you know that you will always have 1 character to identify book you can use syntax like

where items.ID= LEFT(items.ID,1)+books.id

actually it is the same like using

where items.ID= SUBSTRING(items.ID,0,1)+books.id

Because Left just call substring to do its work.

Thanks

JPazgier

|||

hi.

are u familiar with adobc?

how can i use dataadapter using adobc?

my code is:

Dim sqldataadapter As New SqlClient.SqlDataAdapter(stringQuery, MyConn)
Dim ds As New DataSet()
Dim foundrow, temprow As DataRow
Dim ds2 As New DataSet
Dim temp_data_table As New DataTable
sqldataadapter.Fill(ds, "TT0001")

Dim sqldataadapter2 As New SqlClient.SqlDataAdapter(stringQuery2, MyConn)
sqldataadapter2.Fill(ds2, "tempo_db")
Dim date_ctr As Integer

but it doesnt accept sqlClient....what can u suggest?

|||

Hey, Anyways thank you but I sloved it in this way:

update

items

set

items.weight=books.weight,items.profit=books.profit,items.alldiscount=books.alldiscount

from

(booksjoin itemson'b'+cast(books.idAsnvarchar(12))=items.id)

where

items.center='lb'and items.ordernum>47000

Thanx

Hiba

Problem in concatinating two paramters to update one column

Using gridview to display the data and sql server 2000

I havea column in the database say departtime of datetime datatype thatcntains the date and time resp(09/19/2007 9:00 PM). I am separating thedate and time parts to display in two different textboxes saytxt1(09/19/2007) contaons date and txt2(9:00 PM) contains time by usingthe convert in sqldatasource. Now i need to update the column in thedatabase and i am using Updatecommand with parameters in aspx lkeupdatecommand = "Update table set departtime = @.departtime" . How cani update my column as datetime by getting the data from 2 texboxes asnow i have 2 textboxes displaying data for single column means if useredit the data in txt1 as(10/19/2007) then on click of update i need topopulate the column daparttime as (10/19/2007 9:00 PM).

Please let me know if you have any questions.

Hello Nick,

What you need is the DateTime.Parse method. See:http://msdn2.microsoft.com/en-us/library/system.datetime.parse(VS.71).aspx

This will create a datetime field of your two text fields.

Jeroen Molenaar.

sql

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIA
Change
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA
|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegro ups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.
sql

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

Problem executing stored procedure to update all rows of a table

Hi,
I got a stored procedure which interacts with the pubs database. The
following is the code for the stored procedure:
CREATE PROCEDURE usp_UpdatedPrices_para
@.Type char(12)= '%',
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type
Now, if I execute the above stored procedure(from QA) in the following manne
r:
exec usp_UpdatedPrices_para 'Business' , 1
all four rows of the price field of titles table corresponding to the
'Business' type gets updated.
Now I need to execute the stored procedure so that all the rows get the
updates for all the various types. Since I have a default value for the type
as '%', I am
executing the stored procedure as
exec usp_UpdatedPrices_para , 1
to which I am getting a syntax error as the following:
Line 1: Incorrect syntax near ','
If anybody could suggest me something about the error, it would be helpful.
Thanks.Hello, Jack
You can call the procedure like this:
EXEC usp_UpdatedPrices_para DEFAULT, 1
or:
EXEC usp_UpdatedPrices_para @.Percent=1
However, this will not get you the expected result, because your
condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
the default for the @.Type parameter (leave it to be NULL) and use a
condition like this:
WHERE Type=@.Type OR @.Type IS NULL
Razvan

Problem executing stored procedure to update all rows of a tab

Thanks Razvan for your help. As per your advise I have changed the stored
procedure as follows:
CREATE PROCEDURE usp_UpdatedPrices_para1
@.Type char(12),
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
Now I am trying to execute the stored procedure as:
exec usp_UpdatedPrices_para1 @.percent = 10
To the above command I am getting the following error message:
Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was not
supplied.
Do you have any further ideas for resolution. Thanks.
"Razvan Socol" wrote:

> Hello, Jack
> You can call the procedure like this:
> EXEC usp_UpdatedPrices_para DEFAULT, 1
> or:
> EXEC usp_UpdatedPrices_para @.Percent=1
> However, this will not get you the expected result, because your
> condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
> the default for the @.Type parameter (leave it to be NULL) and use a
> condition like this:
> WHERE Type=@.Type OR @.Type IS NULL
> Razvan
>When passing parameters to a stored procedure using the @.variable=value
syntax, you must explicity set a valure for all input parameters that don't
have a default value assigned in the procedure defenition; even if you want
it to be NULL.
Try
EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
Alternatively, you could define the procedure to use a default of NULL for
the variable @.Type
CREATE PROCEDURE usp_UpdatedPrices_para2
@.Type char(12)=NULL,
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
And then execute as
EXEC sp_UpdatedPrices_para2 @.Percent=1
"Jack" wrote:
> Thanks Razvan for your help. As per your advise I have changed the stored
> procedure as follows:
> CREATE PROCEDURE usp_UpdatedPrices_para1
> @.Type char(12),
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> Now I am trying to execute the stored procedure as:
> exec usp_UpdatedPrices_para1 @.percent = 10
> To the above command I am getting the following error message:
> Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was n
ot
> supplied.
> Do you have any further ideas for resolution. Thanks.
> "Razvan Socol" wrote:
>|||Thanks a lot Mark for your generous help. I tried executing both the
procedures and both worked great. Best Regards.
"Mark Williams" wrote:
> When passing parameters to a stored procedure using the @.variable=value
> syntax, you must explicity set a valure for all input parameters that don'
t
> have a default value assigned in the procedure defenition; even if you wan
t
> it to be NULL.
> Try
> EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
> Alternatively, you could define the procedure to use a default of NULL for
> the variable @.Type
> CREATE PROCEDURE usp_UpdatedPrices_para2
> @.Type char(12)=NULL,
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> And then execute as
> EXEC sp_UpdatedPrices_para2 @.Percent=1
>
> "Jack" wrote:
>

Problem due to locking in MSSQLServer 2000

Insert or update statements seems to be locking entire
page or table rather than locking
the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
scenario 1)
In transaction A, I'm updating the 3'rd row. and in
another transaction B I'm reading
row 1.
Transaction B seems to be waiting for transaction A to
finish before returning the select results.
Scenario 2)
In transaction A, I'm inserting new row (4'th row) and in
another transaction B I'm reading
row 1.
here as well trasaction B does not return the row 1 unless
transaction A is complete.
Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have
returned the results without waiting
for update/insert to finish. As the read is being done on
different rows than that of being updated
or inserted.
I have tried both the insert/update as well as select
queries with all the possible locking hints
such as ROWLOCK, READCOMMITED, UPDLOCK etc...
The only way select query returns the row without blocking
is by using the NOLOCK locking hint. But then this is
not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above
issue.Adding to Tony's comments (the DDL/DML will be useful), you might find the
problem is that it's the index page that's locked during the update/insert
which is blocking the select. How many rows are in the table we're
discussing?
Depending on the WHERE clause and the optimizer query plans, sometimes row
locks are NOT released until the entire statement has finished, and
sometimes these can be escalated to table locks. See
http://www.hanlincrest.com/SQLServerLockEscalation.htm for a description.
I couldn't quite figure it out from your note, but is it the same process
attempting to execute two separate transactions on two SQL Server sessions
and thus blocking itself? If this is the case it would be wise to rethink it
so only one transaction is active from the process ar once.
Kind Regards, Howard
"Sangram Deshmukh" <sangram@.savvion.com> wrote in message
news:0c2e01c34ad9$841875a0$a301280a@.phx.gbl...
> Insert or update statements seems to be locking entire
> page or table rather than locking
> the corresponding row to be inserted or updated.
> lets assume the table with 3 rows.
> scenario 1)
> In transaction A, I'm updating the 3'rd row. and in
> another transaction B I'm reading
> row 1.
> Transaction B seems to be waiting for transaction A to
> finish before returning the select results.
>
> Scenario 2)
> In transaction A, I'm inserting new row (4'th row) and in
> another transaction B I'm reading
> row 1.
> here as well trasaction B does not return the row 1 unless
> transaction A is complete.
> Select operation is blocked due to insert.
>
> Ideally in both the scenarios , read operation should have
> returned the results without waiting
> for update/insert to finish. As the read is being done on
> different rows than that of being updated
> or inserted.
> I have tried both the insert/update as well as select
> queries with all the possible locking hints
> such as ROWLOCK, READCOMMITED, UPDLOCK etc...
> The only way select query returns the row without blocking
> is by using the NOLOCK locking hint. But then this is
> not the proper solution as it gives us the dirty read.
> Please suggest me any solution or workaround for above
> issue.

Problem doing Update and Insert to different tables in same procedure.

We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated.

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[AddPhoto]

@.AlbumID

int,

@.Caption

nvarchar(MAX)

AS

INSERT

INTO [Photos](

[AlbumID]

,

[Caption]

,

[Location]

,

[LastModified]

)

VALUES

(

@.AlbumID

,

@.Caption

,

'tmpLocation'

,/* tmpLocation needed because app broke when Location column set to Allow NULLs */

GetDate

())

/* Retrieve generated PhotoID */

DECLARE

@.PhotoIDint

SET

@.PhotoID=SCOPE_IDENTITY()

/* Build unique location path from album and photo ID */

DECLARE

@.Locationnvarchar(MAX)

SET

@.Location='\'+CONVERT(nvarchar(10), @.AlbumID)+'\'+CONVERT(nvarchar(10),@.PhotoID)+'.jpg'

/* Update photo with new location path */

UPDATE

[Photos]

SET

[Location]

= @.Location

WHERE

[PhotoID]

= @.PhotoID

/* Update photo with new location path */

******************************************The code never executes the statement below********************************************

INSERT

INTO [PhotoDefault](

[pidm]

,

[defaultPhoto]

,

[activityDate]

)

VALUES

(

'1234'

,

'test'

,

getdate

()

)

/* Return PhotoID and Location */

SELECT

@.PhotoID, @.Location

RETURN

Thanks,

Jason

Next time, when you post your code please use the Code editor available when you post. Your code is hard to read.

The code (After formatting) looks fine to me. Throw in a couple of PRINT statements before and after the INSERT. There is no reason the INSERT should be skipped.

|||

Sorry about the code post. I didn't know about the code editor.

I have posted print statements after the insert and they are never excuted. I have also tried posting the first insert after with no luck as well. Any other suggestion?

|||

I formatted your code for you. Try this new code if you see the messages:

set ANSI_NULLSON set QUOTED_IDENTIFIERON GOALTER PROCEDURE [dbo].[AddPhoto] @.AlbumIDint, @.Captionnvarchar(MAX)ASINSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified])VALUES ( @.AlbumID, @.Caption,'tmpLocation' ,/* tmpLocation needed because app broke when Location column set to Allow NULLs */GetDate())/* Retrieve generated PhotoID */DECLARE @.PhotoIDint SET @.PhotoID = SCOPE_IDENTITY()/* Build unique location path from album and photo ID */DECLARE @.Locationnvarchar(MAX)SET @.Location ='\' +CONVERT(nvarchar(10), @.AlbumID) +'\' +CONVERT(nvarchar(10),@.PhotoID) +'.jpg'/* Update photo with new location path */UPDATE [Photos]SET [Location] = @.LocationWHERE [PhotoID] = @.PhotoID/* Update photo with new location path */******************************************The code never executes the statement below********************************************SELECT'I am here'INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate])VALUES ('1234','test',getdate() )SELECT'I am here again'/* Return PhotoID and Location */SELECT @.PhotoID, @.LocationRETURNGo

problem creating a sqldatasource connection

I am able to connect but when I try to use the advanced sql generation options the two check boxes are non enabled (generate insert, update, and delete statements

use optimistic concurrency

what is happening the user id has permissions to update/delete/select from the selected table

Are you using a SQL statement that has a JOIN in it? The sql generator does not work with joined tables.