Problem with Client Side Printing

After the Cumulative Security Update for Internet Explorer (912812) the client side printing doesn't work any more. Almost each client get the error:

"Unable to load client print control."

Other clients get an error with code '0x8007F304'.

What can i do? Will SP1 solve the issue?

Client Print is an Active X control. The security update may have disabled the ability to run Active X controls. Check your IE properties to see if Active X controls are allowed to run. In the patch notes I read the following.

Compatibility Patch – To help enterprise customers who need more time to prepare for the ActiveX update changes discussed in Microsoft Knowledge Base Article 912945 and included in Microsoft Security Bulletin MS06-013, Microsoft is releasing a Compatibility Patch on April 11, 2006. As soon as it is deployed, the Compatibility Patch will temporarily return Internet Explorer to the previous functionality for handling ActiveX controls. This Compatibility Patch will function until an Internet Explorer update is released as part of the June update cycle, at which time the changes to the way Internet Explorer handles ActiveX controls will be permanent. This compatibility patch may require an additional restart for systems it is deployed on. For more information, see Microsoft Knowledge Base Article 917425.

|||

i checked my internet properties and nothing has changed.

other activex controls doesn't worked any more too. for example scriptX. meadriod published a new version of their activeX control.

so how can i get it work?

|||

Launch IE

Go to Tools\Internet Options\Security\Custom Level.

In the Active X controls and plugins, select to enable. If this is already done, you may need to then go to General\Settings\View Objects and remove and then reinstall the Active X controls.

Problem with CASE Expression

Hi, all here,

I have a problem with CASE expression in my SQL staments.

the problem is:

when I tried to just partly update the column a , I used the CASE expression : set a=case when b=null then 'null' end

the result was strange: then all the values for column a turned to null.

so what is the problem tho?

Thanks a lot in advance for any guidance.

Is it something like this you're trying to do?

create table #x ( a int null, b int null )
go
insert #x
select 1, 1 union all
select 2, null union all
select 3, 1 union all
select 4, null
go
select * from #x
go

a b
-- --
1 1
2 NULL
3 1
4 NULL

(4 row(s) affected)

update #x
set a = case when b is null then null else a end
go

select * from #x
go

a b
-- --
1 1
NULL NULL
3 1
NULL NULL

(4 row(s) affected)

drop table #x
go

/Kenneth

|||set a=case when b IS null then 'null' end|||The issue is with your comparison of the column value against NULL using equality operator. By default, <any non null value> <> NULL unless you set ANSI_NULLS to off and this affects few operations in the server. You can check the Books Online for more details. The recommended syntax is to use the IS NULL or IS NOT NULL clauses for checking NULL values.

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
----
--
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> ----
--
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> > Hi Tracy ,
> > your guess is correct. i'm only checking for a single ID in the
> > trigger. i dont know how can i handle the situation where i would have
> > mulitple rows.
> > i thought the trigger will be triggered each time row is getting
> > inserted. i dont know how the trigger behaves in bulk insert/update.
> >
> > by the way here is the code for the trigger. please give it a look and
> > advise me how can i modify it to handle the bulk insert/update.
> >
> > alter TRIGGER trigg_insert_row
> > ON [Table10]
> > FOR INSERT, UPDATE
> > AS
> >
> > declare @.ResourceID uniqueidentifier
> > declare @.msg varchar(255)
> > declare @.flag bit
> > set @.flag=0
> >
> > select @.ResourceID=ResourceID from inserted
> >
> > IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > if(@.flag=0)
> > begin
> > set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> > )
> > RAISERROR ( @.msg,16, 1 )
> > rollback transaction
> > end
> >
> >
> > -----
> > Please let me know if u need some more information on it.
> >
> > thanks,
> >
> > Lucky
> >
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:
> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>> Untested, but I think something like this will work better for you:
> [snip]
>> SELECT TOP 1 @.ResourceID = ResourceID
>> FROM
>> (
>> SELECT ResourceID
>> FROM inserted
>> LEFT JOIN Table1
>> ON inserted.ResourceID = Table1.ID
>> WHERE Table1.ID IS NULL
>> UNION
> [snip]
>> ) AS MissingIDs
>> IF @.ResourceID IS NULL
>> BEGIN
>> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
>> RAISERROR (@.msg, 16, 1)
>> ROLLBACK TRANSACTION
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Lucky
lucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.

> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegro ups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky
|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Tracy McKibben wrote:

> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
|||Ed Murphy wrote:
> Tracy McKibben wrote:
> [snip]
> [snip]
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with an UPDATE...

trying to create an UPDATE but am getting and error.
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

update XAPCHECKS
set xapck_amt =
(select sum(apph_paymnts), * from APPHISTF
LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = '01') and (apph_vend = '1010') and
xapck_check = apph_payck and xapck_chk_type = (CASE chm_type WHEN null THEN ' ' ELSE chm_type END) and xapck_check_status = (CASE chm_stat when null then ' ' ELSE chm_stat END)
and xapck_bank = apt_bank
GROUP by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id, apph_paymnts)the problem is here --

set xapck_amt = (select sum(apph_paymnts), *

the error says the subquery has more than one column|||"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."|||ah, that's because the subquery used in a SET can return only one column, one row

it's called a scalar subquery because it's supposed to return only a single scalar value|||not sure why i had that in there but it seems to working ok.
thanks

Problem with an update

Hi, I've a table "Table1" with:
ID - Cod - Type - Value - Period
1-COD1-AAA-0-Jan
2-COD2-BBB-0-Feb
3-COD3-AAA-0-Feb
4-COD4-CCC-0-Feb
Now I want to UPDATE this records using a Second Table "Table2"
Type-Qt-Value-Period
AAA-10-10-Jan
AAA-3-2-Feb
BBB-3-2-Feb
CCC-4-6-Feb
...
If, in table1, I've AAA I want to search in table2 the value of type AAA in
the table1.period
If, in table1, I've BBB I want to search in table2 the value of type BBB in
the table1.period
How Can I create this update'
After the UPDATE, I would like to obtain:
ID - Cod - Type - Value - Period
1-COD1-AAA-100-Jan (value = 10*10)
2-COD2-BBB-6-Feb (value = 3*2)
3-COD3-AAA-6-Feb (value = 3*2)
4-COD4-CCC-24-Feb (value = 6*4)
ThanksIdentity wrote:
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA i
n
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB i
n
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks
It really helps if you post DDL and INSERT statements instead of
sketches of tables - it could even save you some typing. Looking at the
information given it appears that the UPDATE is to be based on joining
the tables on type and period, but you've left us to guess whether
those two columns are unique in either table. If the combination of
those two columns isn't unique in Table2 then do you want to SUM the
multiple rows? If they aren't unique in Table1 then do you want the
same value figure to appear on multiple rows? If they are unique in
BOTH tables then is there a particular reason for having two tables
instead of one?
Notwithstanding the above uncertainties, here's my untested guess at
what you want:
UPDATE Table1
SET value =
(SELECT SUM(qt*value)
FROM Table2
WHERE type = Table1.type
AND period = Table1.period);
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
--|||Here's one method:
CREATE TABLE dbo.FirstTable
(
ID int NOT NULL
CONSTRAINT PK_FirstTable PRIMARY KEY,
Cod char(4) NOT NULL,
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Period char(3) NOT NULL
)
INSERT INTO dbo.FirstTable
SELECT 1,'COD1','AAA',0,'Jan'
UNION ALL SELECT 2,'COD2','BBB',0,'Feb'
UNION ALL SELECT 3,'COD3','AAA',0,'Feb'
UNION ALL SELECT 4,'COD4','CCC',0,'Feb'
CREATE TABLE dbo.SecondTable
(
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Qt int NOT NULL,
Period char(3) NOT NULL,
CONSTRAINT PK_SecondTable
PRIMARY KEY ([Type], Period)
)
INSERT INTO dbo.SecondTable
SELECT 'AAA',10,10,'Jan'
UNION ALL SELECT 'AAA',3,2,'Feb'
UNION ALL SELECT 'BBB',3,2,'Feb'
UNION ALL SELECT 'CCC',4,6,'Feb'
UPDATE dbo.FirstTable
SET
Value =
(SELECT [Value]*Qt
FROM dbo.SecondTable
WHERE
SecondTable.[Type] = FirstTable.[Type] AND
SecondTable.[Period] = FirstTable.[Period]
)
SELECT
ID,
Cod,
[Type],
[Value],
Period
FROM dbo.FirstTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Identity" <id@.id.it> wrote in message
news:uQxOzbpoGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA
> in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB
> in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks|||Thanks for help!
But If I've many fields I must to use

> value =
> (SELECT SUM(qt*value)
> FROM Table2
> WHERE type = Table1.type
> AND period = Table1.period);
for all fields
Thankssql

Problem with an update

Hi, I've a table "Table1" with:
ID - Cod - Type - Value - Period
1-COD1-AAA-0-Jan
2-COD2-BBB-0-Feb
3-COD3-AAA-0-Feb
4-COD4-CCC-0-Feb
Now I want to UPDATE this records using a Second Table "Table2"
Type-Qt-Value-Period
AAA-10-10-Jan
AAA-3-2-Feb
BBB-3-2-Feb
CCC-4-6-Feb
...
If, in table1, I've AAA I want to search in table2 the value of type AAA in
the table1.period
If, in table1, I've BBB I want to search in table2 the value of type BBB in
the table1.period
How Can I create this update'
After the UPDATE, I would like to obtain:
ID - Cod - Type - Value - Period
1-COD1-AAA-100-Jan (value = 10*10)
2-COD2-BBB-6-Feb (value = 3*2)
3-COD3-AAA-6-Feb (value = 3*2)
4-COD4-CCC-24-Feb (value = 6*4)
ThanksIdentity wrote:
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks
It really helps if you post DDL and INSERT statements instead of
sketches of tables - it could even save you some typing. Looking at the
information given it appears that the UPDATE is to be based on joining
the tables on type and period, but you've left us to guess whether
those two columns are unique in either table. If the combination of
those two columns isn't unique in Table2 then do you want to SUM the
multiple rows? If they aren't unique in Table1 then do you want the
same value figure to appear on multiple rows? If they are unique in
BOTH tables then is there a particular reason for having two tables
instead of one?
Notwithstanding the above uncertainties, here's my untested guess at
what you want:
UPDATE Table1
SET value = (SELECT SUM(qt*value)
FROM Table2
WHERE type = Table1.type
AND period = Table1.period);
--
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
--|||Here's one method:
CREATE TABLE dbo.FirstTable
(
ID int NOT NULL
CONSTRAINT PK_FirstTable PRIMARY KEY,
Cod char(4) NOT NULL,
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Period char(3) NOT NULL
)
INSERT INTO dbo.FirstTable
SELECT 1,'COD1','AAA',0,'Jan'
UNION ALL SELECT 2,'COD2','BBB',0,'Feb'
UNION ALL SELECT 3,'COD3','AAA',0,'Feb'
UNION ALL SELECT 4,'COD4','CCC',0,'Feb'
CREATE TABLE dbo.SecondTable
(
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Qt int NOT NULL,
Period char(3) NOT NULL,
CONSTRAINT PK_SecondTable
PRIMARY KEY ([Type], Period)
)
INSERT INTO dbo.SecondTable
SELECT 'AAA',10,10,'Jan'
UNION ALL SELECT 'AAA',3,2,'Feb'
UNION ALL SELECT 'BBB',3,2,'Feb'
UNION ALL SELECT 'CCC',4,6,'Feb'
UPDATE dbo.FirstTable
SET
Value = (SELECT [Value]*Qt
FROM dbo.SecondTable
WHERE
SecondTable.[Type] = FirstTable.[Type] AND
SecondTable.[Period] = FirstTable.[Period]
)
SELECT
ID,
Cod,
[Type],
[Value],
Period
FROM dbo.FirstTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Identity" <id@.id.it> wrote in message
news:uQxOzbpoGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA
> in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB
> in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks|||Thanks for help!
But If I've many fields I must to use
> value => (SELECT SUM(qt*value)
> FROM Table2
> WHERE type = Table1.type
> AND period = Table1.period);
for all fields
Thanks

Problem wit sp_execute SQL

I have to make a large number of updates (about 29k) so I generated teh update statements into a table and am trying to sue sp_executesql to run them. Here is my code:

Declare @.SQLState NVARCHAR(500)

Declare Code Cursor
for
select SQLState from updates
open Code
FETCH NEXT FROM Code
into @.SQLState
While @.@.fetch_Status = 0
Begin
Exec sp_executesql @.SQLState

FETCH NEXT FROM Code
END

CLOSE Code
DEALLOCATE Code

IT appears to run succesfully, but the updates never happen - I get the following results for each update line:

UPDATE REEmployeeEvent SET UpdatedByEmployeeID= '00013' Where UpdatedByEmployeeID='00279'

(1 row(s) affected)

(0 row(s) affected)

Any ideas what I am doing wrong?

BTW - If I run the statements manually, they do work.

Thanks for any help!is there any particular reason why you want to use such a non-standard method to execute a script?

Why not execute dump them to a file, and execute the file as a single batch? At least that way you could visually inspect the scripts for correctness.

What you are trying to do here seems risky at best.

Problem whit "." or "," in field MONEY

Hi guys...

UPDATE PRODUCT SET PRICE='1,11' WHERE COD='001'

but, in field "PRICE" = 111,00

I don′t need "." but i need to use ","

UPDATE PRODUCT SET PRICE='1.11' WHERE COD='001'

Works perfectly... but all sql commands in applications use ","

In Server

User′s set LANGUAGE=PORTUGUESES

DEFAULT LANGUAGE = PORTUGUESES

Tks All

If i'm correct then you are using money or decimal for price.

use varchar for price and use CHAR(44) for ',' and format the string.

Problem while updating data using SQLXML UpdateGrams

A long time I used SQLXML templates on serverside to get data for my application and update some records one by one.

But now I also need to update data of the whole dataset. So I want to use SQLXML UpdateGrams. (Or DataSet DiffGrams).

But My first attempt failed. I have troulble with identity field. No one solution from forums or articles worked for me . So I tried to implement some sample to post here. It uses Northwind database.

Sample should do 3 steps.

Load data - OK

Make some changes - OK

Save Changes.

But doing this sample I have event more troubles.

please help me. provide some sample or better show me fixes in my one. I tried to comment it and think it is rather simple. you can get it here http://rapidshare.de/files/26676262/SQLXML_Sample1.zip.html or here ftp://demon.selfip.com/SQLXML_Sample1.zip.

It is very urgent, so I'll wait for any replies.

Thanks.

-= Sergey =-

PS.: I'm using SQL Server 2005 to run samples. But productive server will be SQL Server 2000.

Thread is closed

I've found an error. I've copied sample xsd from the bad book

There was an error in schema uri

xmlns:sql="urn:schemas-microsoft-com:mapping-schema"

so SQLXML saw no mapping at all!

no everything is ok. Even identity inserts work properly.

-= Sergey =-

Problem when adding cascade actions to a relationship

Hello everyone,

I wanted to change a relationship in a database in order for it to have a cascading effect on update and on delete in order to preserve the data integrity. The problem is that when I click the save button I get the following error message:

'Role (Application)' table saved successfully
'Usergroup_Role (Security)' table
- Unable to create relationship 'FK_Usergroup_Role_RoleId_Application_Role_Id'.
Introducing FOREIGN KEY constraint 'FK_Usergroup_Role_RoleId_Application_Role_Id' on table 'Usergroup_Role' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Any suggestion on how can I fix this? For a better understanding of the real problem I've uploaded a picture of the tables diagram.Here's the link

Best regards.

Instead of using a cascade, why not either write the delete into your stored procedure or usea logical delete flag?

|||

TATWORTH:

Instead of using a cascade, why not either write the delete into your stored procedure or usea logical delete flag?

Thanks for your reply Tatworth. It's only because that would make me have half the app with cascade and the other half hand coded. Before I consider that solution I'd much rather understand why this error is being returned and to know if there's any design change I can put in place to fix it.

Problem when adding cascade actions to a relationship

Hello everyone,

I wanted to change a relationship in a database in order for it to have a cascading effect on update and on delete in order to preserve the data integrity. The problem is that when I click the save button I get the following error message:

'Role (Application)' table saved successfully
'Usergroup_Role (Security)' table
- Unable to create relationship 'FK_Usergroup_Role_RoleId_Application_Role_Id'.
Introducing FOREIGN KEY constraint 'FK_Usergroup_Role_RoleId_Application_Role_Id' on table 'Usergroup_Role' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Any suggestion on how can I fix this? For a better understanding of the real problem I've uploaded a picture of the tables diagram. Here's the link

Best regards.

Think about it. If you happen to delete a row in Base table, it trickle downs the cascade deletes in two ways. One thru Role table and the other UserGroup table. so its setting up the cascade deletes in two separate paths and they finally end up deleting data in UserGroup_Role table from two paths. You need to put some more thought to the model before setting up the relationships.

As a different matter, you need to work on the naming standards also. Use good names for the column so that it describes the entity attributes. Take Id column, its very to hard to know which Id you are referring to. Name the columns as RoleID, BaseID, UserGroupID, RoleCategoryID etc...
|||

Thanks for your reply Sankar,

Yes, I understand what your are saying but how do you suggest me to do this in order to preserve the database integrity when a Role is deleted? I was trying to enforce this through the use of a cascade effect, thus preventing the existence of orphan rows in the [Security].[Usergroup_Role] table. An alternative would be to create a trigger, or something like that, to do enforce this programmatically.

I’m still trying to learn the best way to solve this kind of issues in SQL Server.

As for your observation regarding the Id columns: thanks, that’s something I can certainly change.

Best regards.

|||

Whenever you have cyclic relationships between tables then as a common rule, you are either missing something or overdoing something. As we don't know the business rules wrt the model, we can only guess. Only you can make the judgement.

Ask yourself, does UserGroupID depend on applicationID, how and why?

What is the relationship between UserGroup and Role? and let us know how you got over this.

|||

Prolly, I'm just failing to see exactly what is overdone or missing. The rules are quite simple in fact.

Each application [Application].[Base] can have a variable number of Roles, for example Delete Employee. Each of the roles will have to be associated with the application to which they belong, which is represented by the column [ApplicationId] in the [Application].[Role] table. Furthermore, they also have to belong one (and only one) role Category from the [Application].[Role_Category] table. This relation is represented by the [CategoryId] column in the [Application].[Role] table.

In order to define who has access to what each application will define a variable number of usergroups through the [Security].[Usergroup] table. Since the usergroup is associated with an application (because they belong to it and are only to be used within it) this relation is enforced by the [ApplicationId] column in the [Security].[Usergroup].

Since the relation between usergroups and roles is many-to-many there was the need to create a link table [Security].[Usergroup_Roles] which makes the association of the roles that each usergroup has, or hasn't, access to.

Furthermore, there's the need to enforce data integrety in the following way:

If an application is deleted from the [Application].[Base] all of its usergroups, and consequently the roles that usergroup is associated with, must be deleted from the tables [Security].[Usergroup] and [Security].[Usergroup_Role] respectively.

The next integrety rule states that the roles associated with an application [Application.Role] must also be deleted should the application, to which they belong to, get deleted in the [Application].[Base]. That, by itself, isn't a problem. The cascading relation between the columns [Application].[Role].[ApplicationId] and [Application].[Base].[Id] takes care of that for us just fine.

So far so good!

The problem occurs when I try to enforce the 3rd integrety rule: Should an application role be deleted from the [Application].[Role] table, that action must be enforced in the [Security].[Usergroup_Role], as there can't be an allow / deny relation to an inexisting role. The creation of a cascading relation between [Security].[Usergroup_Role].[RoleId] and [Application].[Role].[Id] causes the multiple path error.

I know I could solve this with a trigger in the [Application].[Role] table that enforces the integrety after delete using the virtual DELETED table. That's infact what's in place at the moment. Thus, leaving me with only one question: is there any design change I can make to enforce all the integrety rules through cascading relations, instead of using programmatic solutions (triggers or otherwise)?

|||

I was reading this and thought i would chime in. I checked out the ERD and also agree that it looked a bit cyclical. When the ERD starts to look like a circle, I always recommend going back to the drawing board as it can lead to fictitious data being generated via queries.

I know this doesn't exactly meet your needs as detailed above, however, if you can come up with commonly conceived roles and groups, you may find that you can reuse them in multiple applications.

Not knowing your full details, I thought I would propose my solution anyways. I would have made a different design choice.

Your major objects appear to be the Base Application, User Groups and Roles. I believe you are trying to capture the the relationship in an inappropriate way. I would recommend having your 3 core tables each have a one-to-many relationship with a new link table which you may call ApplicationUserGroupRole. This would be uniquely indexed by a super key of the User Group, Role, and Application tables. (keeping the role_category table as is) Dropping the exising Usergroup_Role table.

This would allow you to capture the relationships of what User Group has which roles in which applications. I am not a believer of deleting historical security access information, so I would recommend having an Active Bit column or Active/Inactive DateTime fields to indicate when a specific user group has role rights in a specific application.

It wouldn't force the deletion of roles, etc... but a cascading delete should force the deletion of the link history table. (still a bad idea I think)

|||

Hello asjev1, I'm glad you dropped by

I understand your design implementation, however I believe it doesn't meet our requirements. For instance, in your design the usergroups and the roles can exist even if the application they belong to is no longer there, only enforcing such integrety in the ApplicationUsergroupRole table. Ultimately this would mean that you would, eventualy, and if I understood correctly, end up with a number of usergroups and roles, in their respective core tables, that are no longer in use (as in for good).

Regarding the historical data I'm doing this a bit differently that what you might expect. The history, in this particular case, works this way: when an object (say a datarow) is changed it's shipped in XML to a log system. That XML contains all sort of informations, including the object in its original state prior to the change. All the history is monitored 24/7 by the staff of the company.

|||

Triggers are evil! But they may do the trick in this instance, depending on the load placed on your server. If the server isn't being over utilized nor does the plan call for a huge increase, then triggers are a great solution. But if you want cascading deletes, you have to get away from the cyclical pattern.

Good luck! Let us know how you solve this!

asjev1

|||Yes, indeed. Don't suppose you have a suggestion to fix the problem I pointed in your design.|||

If I understand your largest concern, that you would like all groups and roles associated with an application deleted once an application is removed, you could place an instead of delete trigger on the application table per the design i mentioned previously. The primary key of the application table can be caught in the temporary deleted table available in the trigger.

You could then do something like this:

CREATE TABLE TempTable

(ApplicationID,

RoleID,

GroupID)

1. Select associated records into temp table

SELECT ApplicationID,RoleId, GroupID

From ApplicationUserGroupRole

where ApplicationId=(SELECT ApplicationID from deleted);

2. delete link table records

Delete from ApplicationUserGroupRole

Where ApplicationId in (Select ApplicationID from deleted);

3. delete from role and group and application tables

Delete from ApplicationRole

Where RoleID in (select roleID from TempTable);

Delete from ApplicationGroup

Where GroupID in (select groupID from TempTable);

Delete from Application

where applicationID in (select applicationID from TempTable)

4. Clean up temp table

Drop Table TempTable

The net effect is:

1. Copy needed records into temporary table

2. Drop link table record (ApplicationUserGroupRole).

3. Drop Application/Role/Group records

4. Clean up

Hope it helps.

|||

Thanks both of you for your help.

Problem when adding cascade actions to a relationship

Hello everyone,

I wanted to change a relationship in a database in order for it to have a cascading effect on update and on delete in order to preserve the data integrity. The problem is that when I click the save button I get the following error message:

'Role (Application)' table saved successfully
'Usergroup_Role (Security)' table
- Unable to create relationship 'FK_Usergroup_Role_RoleId_Application_Role_Id'.
Introducing FOREIGN KEY constraint 'FK_Usergroup_Role_RoleId_Application_Role_Id' on table 'Usergroup_Role' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Any suggestion on how can I fix this? For a better understanding of the real problem I've uploaded a picture of the tables diagram. Here's the link

Best regards.

Think about it. If you happen to delete a row in Base table, it trickle downs the cascade deletes in two ways. One thru Role table and the other UserGroup table. so its setting up the cascade deletes in two separate paths and they finally end up deleting data in UserGroup_Role table from two paths. You need to put some more thought to the model before setting up the relationships.

As a different matter, you need to work on the naming standards also. Use good names for the column so that it describes the entity attributes. Take Id column, its very to hard to know which Id you are referring to. Name the columns as RoleID, BaseID, UserGroupID, RoleCategoryID etc...
|||

Thanks for your reply Sankar,

Yes, I understand what your are saying but how do you suggest me to do this in order to preserve the database integrity when a Role is deleted? I was trying to enforce this through the use of a cascade effect, thus preventing the existence of orphan rows in the [Security].[Usergroup_Role] table. An alternative would be to create a trigger, or something like that, to do enforce this programmatically.

I’m still trying to learn the best way to solve this kind of issues in SQL Server.

As for your observation regarding the Id columns: thanks, that’s something I can certainly change.

Best regards.

|||

Whenever you have cyclic relationships between tables then as a common rule, you are either missing something or overdoing something. As we don't know the business rules wrt the model, we can only guess. Only you can make the judgement.

Ask yourself, does UserGroupID depend on applicationID, how and why?

What is the relationship between UserGroup and Role? and let us know how you got over this.

|||

Prolly, I'm just failing to see exactly what is overdone or missing. The rules are quite simple in fact.

Each application [Application].[Base] can have a variable number of Roles, for example Delete Employee. Each of the roles will have to be associated with the application to which they belong, which is represented by the column [ApplicationId] in the [Application].[Role] table. Furthermore, they also have to belong one (and only one) role Category from the [Application].[Role_Category] table. This relation is represented by the [CategoryId] column in the [Application].[Role] table.

In order to define who has access to what each application will define a variable number of usergroups through the [Security].[Usergroup] table. Since the usergroup is associated with an application (because they belong to it and are only to be used within it) this relation is enforced by the [ApplicationId] column in the [Security].[Usergroup].

Since the relation between usergroups and roles is many-to-many there was the need to create a link table [Security].[Usergroup_Roles] which makes the association of the roles that each usergroup has, or hasn't, access to.

Furthermore, there's the need to enforce data integrety in the following way:

If an application is deleted from the [Application].[Base] all of its usergroups, and consequently the roles that usergroup is associated with, must be deleted from the tables [Security].[Usergroup] and [Security].[Usergroup_Role] respectively.

The next integrety rule states that the roles associated with an application [Application.Role] must also be deleted should the application, to which they belong to, get deleted in the [Application].[Base]. That, by itself, isn't a problem. The cascading relation between the columns [Application].[Role].[ApplicationId] and [Application].[Base].[Id] takes care of that for us just fine.

So far so good!

The problem occurs when I try to enforce the 3rd integrety rule: Should an application role be deleted from the [Application].[Role] table, that action must be enforced in the [Security].[Usergroup_Role], as there can't be an allow / deny relation to an inexisting role. The creation of a cascading relation between [Security].[Usergroup_Role].[RoleId] and [Application].[Role].[Id] causes the multiple path error.

I know I could solve this with a trigger in the [Application].[Role] table that enforces the integrety after delete using the virtual DELETED table. That's infact what's in place at the moment. Thus, leaving me with only one question: is there any design change I can make to enforce all the integrety rules through cascading relations, instead of using programmatic solutions (triggers or otherwise)?

|||

I was reading this and thought i would chime in. I checked out the ERD and also agree that it looked a bit cyclical. When the ERD starts to look like a circle, I always recommend going back to the drawing board as it can lead to fictitious data being generated via queries.

I know this doesn't exactly meet your needs as detailed above, however, if you can come up with commonly conceived roles and groups, you may find that you can reuse them in multiple applications.

Not knowing your full details, I thought I would propose my solution anyways. I would have made a different design choice.

Your major objects appear to be the Base Application, User Groups and Roles. I believe you are trying to capture the the relationship in an inappropriate way. I would recommend having your 3 core tables each have a one-to-many relationship with a new link table which you may call ApplicationUserGroupRole. This would be uniquely indexed by a super key of the User Group, Role, and Application tables. (keeping the role_category table as is) Dropping the exising Usergroup_Role table.

This would allow you to capture the relationships of what User Group has which roles in which applications. I am not a believer of deleting historical security access information, so I would recommend having an Active Bit column or Active/Inactive DateTime fields to indicate when a specific user group has role rights in a specific application.

It wouldn't force the deletion of roles, etc... but a cascading delete should force the deletion of the link history table. (still a bad idea I think)

|||

Hello asjev1, I'm glad you dropped by

I understand your design implementation, however I believe it doesn't meet our requirements. For instance, in your design the usergroups and the roles can exist even if the application they belong to is no longer there, only enforcing such integrety in the ApplicationUsergroupRole table. Ultimately this would mean that you would, eventualy, and if I understood correctly, end up with a number of usergroups and roles, in their respective core tables, that are no longer in use (as in for good).

Regarding the historical data I'm doing this a bit differently that what you might expect. The history, in this particular case, works this way: when an object (say a datarow) is changed it's shipped in XML to a log system. That XML contains all sort of informations, including the object in its original state prior to the change. All the history is monitored 24/7 by the staff of the company.

|||

Triggers are evil! But they may do the trick in this instance, depending on the load placed on your server. If the server isn't being over utilized nor does the plan call for a huge increase, then triggers are a great solution. But if you want cascading deletes, you have to get away from the cyclical pattern.

Good luck! Let us know how you solve this!

asjev1

|||Yes, indeed. Don't suppose you have a suggestion to fix the problem I pointed in your design.|||

If I understand your largest concern, that you would like all groups and roles associated with an application deleted once an application is removed, you could place an instead of delete trigger on the application table per the design i mentioned previously. The primary key of the application table can be caught in the temporary deleted table available in the trigger.

You could then do something like this:

CREATE TABLE TempTable

(ApplicationID,

RoleID,

GroupID)

1. Select associated records into temp table

SELECT ApplicationID,RoleId, GroupID

From ApplicationUserGroupRole

where ApplicationId=(SELECT ApplicationID from deleted);

2. delete link table records

Delete from ApplicationUserGroupRole

Where ApplicationId in (Select ApplicationID from deleted);

3. delete from role and group and application tables

Delete from ApplicationRole

Where RoleID in (select roleID from TempTable);

Delete from ApplicationGroup

Where GroupID in (select groupID from TempTable);

Delete from Application

where applicationID in (select applicationID from TempTable)

4. Clean up temp table

Drop Table TempTable

The net effect is:

1. Copy needed records into temporary table

2. Drop link table record (ApplicationUserGroupRole).

3. Drop Application/Role/Group records

4. Clean up

Hope it helps.

|||

Thanks both of you for your help.

Problem w/SqlDataAdapter in VB 2003 using SQLExpress

I am going through ADO.NET step-by-step and I tried the first project making a simple SqlDataAdapter and I get errors generating Update, Insert, and Delete statements. Any ideas? Should I just go back to MSDE using VB 2003?

Uriel

Dread Judge Forgive me!What errors are you getting?|||First, I should note that I am using the Northwinds DB that came with the book I am using. I add the SqlDataAdapter and the Wizard pops up. I added my connection. Choose Use SQL statements. I use the query builder to select several fields from one table, then click next. On the final page of the Wizard . I get the following:

The wizard detected the following errors when configuring the data adapter "SqlDataAdapter1".
"Symbol" - this is the symbol before the statements
"Check" Generated SELECT Statement
"Check" Generated Mappings
"Warning" Generated INSERT Statement
There were errors configuring the data adapter
"Warning" Generated UPDATE Statement
There were errors configuring the data adapter
"Warning" Generated DELETE Statement
There were errors configuring the data adapter

To apply these setting click finish.

There is no explanation of the errors and nothing that I can find to get an explanation. It allows you to go back and make changes, but I don't even know what's wrong.

Uriel

Dread Judge Forgive me!|||Any success? I'm getting the same thing, but for me it errors out creating the Select statement and doesn't even attempt the rest.

Problem w/SqlDataAdapter in VB 2003 using SQLExpress

I am going through ADO.NET step-by-step and I tried the first project making a simple SqlDataAdapter and I get errors generating Update, Insert, and Delete statements. Any ideas? Should I just go back to MSDE using VB 2003?

Uriel

Dread Judge Forgive me!What errors are you getting?|||First, I should note that I am using the Northwinds DB that came with the book I am using. I add the SqlDataAdapter and the Wizard pops up. I added my connection. Choose Use SQL statements. I use the query builder to select several fields from one table, then click next. On the final page of the Wizard . I get the following:

The wizard detected the following errors when configuring the data adapter "SqlDataAdapter1".
"Symbol" - this is the symbol before the statements
"Check" Generated SELECT Statement
"Check" Generated Mappings
"Warning" Generated INSERT Statement
There were errors configuring the data adapter
"Warning" Generated UPDATE Statement
There were errors configuring the data adapter
"Warning" Generated DELETE Statement
There were errors configuring the data adapter

To apply these setting click finish.

There is no explanation of the errors and nothing that I can find to get an explanation. It allows you to go back and make changes, but I don't even know what's wrong.

Uriel

Dread Judge Forgive me!

|||Any success? I'm getting the same thing, but for me it errors out creating the Select statement and doesn't even attempt the rest.

PROBLEM W/ OLE DB COMMAND

In a data I'm trying to implement an Ole DB Command to do a database table update. When I enter my sql command I get an error saying [OLE DB COMMAND [5926]] - unable to retrieve destination columns... It appears it doesn't recognize the table I'm trying to update, however I do have a sql task in the control flow that is successfully selecting a row from the same table that I'm trying to update (just to prove that I can access that table). I'm using the same connection mgr in the OLE DB as I used in the Sql task

Any suggestions?

thanks

It sounds like a problem in the way you have configured the OLE DB Command component.

What is the SQL statement that you have in there?

-Jamie

|||What is the database you are working with? Maybe the OLEDB driver doesn't provide metadata the way SSIS like it... So a SQL command in the control flow works fine (since it doesn't need to have the metadata, in the control flow it doesn't matter at all) but in the data flow metadata is essential and when the driver has a problem with it then it might be a problem...|||

My sql is

Update Person set Flag = 1 where name = ?

DB Oracle 9i

The sql in the control flow (using a sql task that is configured to use the same connection mgr) is not the same statement but's it's a select against a different table in the same schema - that works fine.

|||

I think the meta data may be the problem - I found that it does recognize the DB table since I get an oracle error when I type in an incorrect column name and that error goes away when I correct the column name and get this one:

the OLE db command [6061] unable to retrieve destination column descriptions

Has anyone had success doing a table 'update' against Oracle using the OLE DB command? if so what type of connection did you use?

thanks

|||

Tom

Unfortunately there is no easy way for OLEDBCommand transform to build up column metadata for an OLEDB Oracle connection since the provider does not support derive parameter info.

The only way to do make it work is to manually add "External Columns" info one by one at OLEDBCommand. For example for your case you need to go to "Input and Output Properties", find the "External Columns" and add a column of "name", fill in name, DataType, codepage, length etc manually there for "name", according to the correspondent input column's information.

Thanks

Wenyang

PROBLEM W/ OLE DB COMMAND

In a data I'm trying to implement an Ole DB Command to do a database table update. When I enter my sql command I get an error saying [OLE DB COMMAND [5926]] - unable to retrieve destination columns... It appears it doesn't recognize the table I'm trying to update, however I do have a sql task in the control flow that is successfully selecting a row from the same table that I'm trying to update (just to prove that I can access that table). I'm using the same connection mgr in the OLE DB as I used in the Sql task

Any suggestions?

thanks

It sounds like a problem in the way you have configured the OLE DB Command component.

What is the SQL statement that you have in there?

-Jamie

|||What is the database you are working with? Maybe the OLEDB driver doesn't provide metadata the way SSIS like it... So a SQL command in the control flow works fine (since it doesn't need to have the metadata, in the control flow it doesn't matter at all) but in the data flow metadata is essential and when the driver has a problem with it then it might be a problem...|||

My sql is

Update Person set Flag = 1 where name = ?

DB Oracle 9i

The sql in the control flow (using a sql task that is configured to use the same connection mgr) is not the same statement but's it's a select against a different table in the same schema - that works fine.

|||

I think the meta data may be the problem - I found that it does recognize the DB table since I get an oracle error when I type in an incorrect column name and that error goes away when I correct the column name and get this one:

the OLE db command [6061] unable to retrieve destination column descriptions

Has anyone had success doing a table 'update' against Oracle using the OLE DB command? if so what type of connection did you use?

thanks

|||

Tom

Unfortunately there is no easy way for OLEDBCommand transform to build up column metadata for an OLEDB Oracle connection since the provider does not support derive parameter info.

The only way to do make it work is to manually add "External Columns" info one by one at OLEDBCommand. For example for your case you need to go to "Input and Output Properties", find the "External Columns" and add a column of "name", fill in name, DataType, codepage, length etc manually there for "name", according to the correspondent input column's information.

Thanks

Wenyang

Problem using ROWLOCK with INSERT/UPDATE.

(SQL Server 2005)
I am having a little trouble that I hope someone can clarify for me.
I have a session running with implicit_transactions turned on, and another
session trying to access a table undergoing some changes in that first
session. I had thought that I could sort of isolate the two sessions from
one another using ROWLOCK, but it seems as if (at least in my example) that
the ROWLOCK always gets promoted to a table lock.
Please see the attached DDL/DML for an example of what I'm trying to
accomplish (note that I ran this in [tempdb]).
Is there any way for me to have the second session successfully report the
counts of the *committed* rows without being blocked on the first session if
that first session is within a transaction scope (with, presumably, a
default isolation level of read-committed)?
Thanks for any help that anyone can provide!
Kind regards,
John Peterson
On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:

>(SQL Server 2005)
>I am having a little trouble that I hope someone can clarify for me.
>I have a session running with implicit_transactions turned on, and another
>session trying to access a table undergoing some changes in that first
>session. I had thought that I could sort of isolate the two sessions from
>one another using ROWLOCK, but it seems as if (at least in my example) that
>the ROWLOCK always gets promoted to a table lock.
>Please see the attached DDL/DML for an example of what I'm trying to
>accomplish (note that I ran this in [tempdb]).
>Is there any way for me to have the second session successfully report the
>counts of the *committed* rows without being blocked on the first session if
>that first session is within a transaction scope (with, presumably, a
>default isolation level of read-committed)?
>Thanks for any help that anyone can provide!
>Kind regards,
>John Peterson
>
Hi John,
I didn't read the complete code, nor did I try to run it, but based on
your narrative I think you need to add a READPAST locking hint.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||I don't think specifying ROWLOCK hint prevents the engine from escalating
locks as it sees appropriate, be it page, extent, etc.
Off the top of my head I can't think of a way to interact with the committed
rows directly from a separate transaction.
TheSQLGuru
President
Indicium Resources, Inc.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AIcMavpHHA.208@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2005)
> I am having a little trouble that I hope someone can clarify for me.
> I have a session running with implicit_transactions turned on, and another
> session trying to access a table undergoing some changes in that first
> session. I had thought that I could sort of isolate the two sessions from
> one another using ROWLOCK, but it seems as if (at least in my example)
> that the ROWLOCK always gets promoted to a table lock.
> Please see the attached DDL/DML for an example of what I'm trying to
> accomplish (note that I ran this in [tempdb]).
> Is there any way for me to have the second session successfully report the
> counts of the *committed* rows without being blocked on the first session
> if that first session is within a transaction scope (with, presumably, a
> default isolation level of read-committed)?
> Thanks for any help that anyone can provide!
> Kind regards,
> John Peterson
>
>
|||Hello Hugo!
The READPAST hint appears to do exactly what I want! Thanks for the tip.
Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
(I'm too lazy to bring up a 2000 BOL. ;-)
Thanks again!
John Peterson
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:f6696390qvpj9qtqcv89tovhp0pjdvsfg2@.4ax.com...
> On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:
>
> Hi John,
> I didn't read the complete code, nor did I try to run it, but based on
> your narrative I think you need to add a READPAST locking hint.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:

>Hello Hugo!
>The READPAST hint appears to do exactly what I want! Thanks for the tip.
>Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
Hi John,
Typing a message and going back to check for an answer is less effort
than double-clicking an icon on your desktop? <shakes head>
READPAST is available in SQL Server 2000 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:catb6317978pkrfm33rimiofeqrguu524j@.4ax.com...
> On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:
>
> Hi John,
> Typing a message and going back to check for an answer is less effort
> than double-clicking an icon on your desktop? <shakes head>
Heh! Well, as it turns out, when I sent the previous reply, it wasn't from
a machine that had access to SQL Server at the time. :-)

> READPAST is available in SQL Server 2000 as well.
Ah, interesting. I didn't realize that -- good to know.
Thanks again for your help!

> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Problem using ROWLOCK with INSERT/UPDATE.

underprocessableOn Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:

>(SQL Server 2005)
>I am having a little trouble that I hope someone can clarify for me.
>I have a session running with implicit_transactions turned on, and another
>session trying to access a table undergoing some changes in that first
>session. I had thought that I could sort of isolate the two sessions from
>one another using ROWLOCK, but it seems as if (at least in my example) that
>the ROWLOCK always gets promoted to a table lock.
>Please see the attached DDL/DML for an example of what I'm trying to
>accomplish (note that I ran this in [tempdb]).
>Is there any way for me to have the second session successfully report the
>counts of the *committed* rows without being blocked on the first session i
f
>that first session is within a transaction scope (with, presumably, a
>default isolation level of read-committed)?
>Thanks for any help that anyone can provide!
>Kind regards,
>John Peterson
>
Hi John,
I didn't read the complete code, nor did I try to run it, but based on
your narrative I think you need to add a READPAST locking hint.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||I don't think specifying ROWLOCK hint prevents the engine from escalating
locks as it sees appropriate, be it page, extent, etc.
Off the top of my head I can't think of a way to interact with the committed
rows directly from a separate transaction.
TheSQLGuru
President
Indicium Resources, Inc.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AIcMavpHHA.208@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2005)
> I am having a little trouble that I hope someone can clarify for me.
> I have a session running with implicit_transactions turned on, and another
> session trying to access a table undergoing some changes in that first
> session. I had thought that I could sort of isolate the two sessions from
> one another using ROWLOCK, but it seems as if (at least in my example)
> that the ROWLOCK always gets promoted to a table lock.
> Please see the attached DDL/DML for an example of what I'm trying to
> accomplish (note that I ran this in [tempdb]).
> Is there any way for me to have the second session successfully report the
> counts of the *committed* rows without being blocked on the first session
> if that first session is within a transaction scope (with, presumably, a
> default isolation level of read-committed)?
> Thanks for any help that anyone can provide!
> Kind regards,
> John Peterson
>
>|||Hello Hugo!
The READPAST hint appears to do exactly what I want! Thanks for the tip.
Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
(I'm too lazy to bring up a 2000 BOL. ;-)
Thanks again!
John Peterson
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:f6696390qvpj9qtqcv89tovhp0pjdvsfg2@.
4ax.com...
> On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:
>
> Hi John,
> I didn't read the complete code, nor did I try to run it, but based on
> your narrative I think you need to add a READPAST locking hint.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:

>Hello Hugo!
>The READPAST hint appears to do exactly what I want! Thanks for the tip.
>Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
Hi John,
Typing a message and going back to check for an answer is less effort
than double-clicking an icon on your desktop? <shakes head>
READPAST is available in SQL Server 2000 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:catb6317978pkrfm33rimiofeqrguu524j@.
4ax.com...
> On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:
>
> Hi John,
> Typing a message and going back to check for an answer is less effort
> than double-clicking an icon on your desktop? <shakes head>
Heh! Well, as it turns out, when I sent the previous reply, it wasn't from
a machine that had access to SQL Server at the time. :-)

> READPAST is available in SQL Server 2000 as well.
Ah, interesting. I didn't realize that -- good to know.
Thanks again for your help!

> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis