Need some helps on this task

A user photo table with the following fields:

username
path
caption
status

where the status field indicates where the photo is the main one or not. Each user only can have one main photo.

For the deletion operation, another, if there is one, photo's status needs to be changed to main if the removing photo is the main one. What is the best approache to carry out this task: SQL and function/trigger?

Similar situation occur when a user want to change a non-main photo to become a main one.

Any advice?

Thanks,

v.While a trigger could be made to do this, it would not be trivial - would have to deal with the mutating table problem for one thing.

I would prefer to hide the logic in a packaged procedure, and force the user (i.e. the application) to delete via the procedure rather than an update or delete statement.|||Hi, Tony,

Thanks for your response and suggestion.

My thought on the issue is that it is somehow like a DB table constraint and not a business logic. Therefore, it shoud be resolved in the DB layer.

I can implement your suggestion to pass the information what the application knows about whether the photo is a main one or not. I think this solution is a suitable one.

Thanks again.

v.|||Here is a solution I just come out.

For the deletion operation, if the deleting photo placement is larger than 1, not the first/main one, only execute the deletion statement.

Otherwise, after the deletion statement, run the following query:

UPDATE photo SET main = 'true' WHERE userid = 'xxx' AND path IN (SELECT path FROM photo WHERE userid = 'xxx' )

This solution basically is on the DB side with a little help from the application (logic).

My feeling of the above query is the subquery can be in a better form, but can't think out one at this moment.

v.

Need more descriptive error message

Hi,

Currently I have an event handler on my package that executes a script task on the "OnError" event (at the package level).

One of the error handler's script task's job is to capture the "System::ErrorDescription" and store it to a user variable which is then later sent in an email. That part works fine.

But what I am wondering is, there a way to make the error description more descriptive? I would like to send a custom error message from one of the tasks in my package, if possible. The task is a script task.

Currently, the "System::ErrorDescription" is just:

The Script returned a failure result.

Which isn't too terribly descriptive! But this is the default error description.

Is there something I can do to change the default error message for my script task? Is there a way I can set the "System::ErrorDescription" on this script task, perhaps? Or is there another way?

Thanks much

Usually when there is an error, there will be some more additional errors raised by the SSIS. Hence Error Description will always have the last error message only, unless you handle all the previous ones.

jwelch has explained it more clearly here:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

Thanks

|||

I already have this code in place.

My question is how to add my own custom error message. But I think I have a solution.

I can create a user variable, populate it in the event of an error, and append it to my emailMessage variable.

Need ID after OLEDB command (insert)

Hi,

I'm stuck on the following thing:

After a slowly changing dimension task I replaced the OLE DB Destination task by an OLE DB Command and created the insert manually. This because I need to work further on the dataset. So I do a union all between the output of the two OLE DB Commands (insert and update). Untill here no problem. But than, because I need an ID further on, I do a lookup in the table in which I just inserted and updated my data for the right ID's. When I run this project I get the error message "row yielded no match during lookup".

I don't understand this beacause I just inserted the data and I've checked, it's there.

I could resolve this by splitting up in two control flows (reselect all the needed data wíth the ID-field in my selection) but I would prefer to solve it in another way

Greets,

Tom

You cannot be assured that the inserted records exist and are avaiable to you until after the data-flow completes. You will need two data-flows I'm afraid. Use a raw file to pass data between them if you require.

-Jamie

|||

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

|||

Tom DC wrote:

Hi Jamie,

In my opinion SSIS does a sort of precaching (I mean loading the lookup table in to memory before real execution of the whole data flow task and that must be the reason why he can't perform a lookup in an 'empty' table during the execution). << Can't I force him not to cache that table? just an idea >>

But in order to continue and to satisfy my boss, I will split it up...

Thanks anyway

Greets,
-Tom

Yes, you can easily configure the LOOKUP component not to cache any data. Still though you cannot guarantee that a row that you think will be there will indeed be there. [This is due to the buffer architecture of the pipeline which is elaborated on at various places around the internet and in textbooks if you fancy some heavy reading.]

So, regardless of your caching options you still need two data-flows.

-Jamie

|||

Allright, I've split it up. Thanks for your help!

|||

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

|||

Tom VdP wrote:

Setting the CacheType to none (and using a lookup table instead of SQL statement to avoid weird errors about unmatched parameters) did solve our problem. The data we expect to find in the table was written in the same dataflow by an OLE DB Command - it is not part of the dataflow itself.

Also, the lookup happens in a different "Execution Tree" than the insert via the OLE DB Command.

I think we are safe and do not have to split the dataflow.

Tom (the satisfied boss)

Fair enough. I can only advise as strngly as I possibly can that you do not under any circumstances do this. There is no way to guarantee that a record that you expect to be in teh table will indeed be there. This is because SSIS processes data buffer-by-buffer, not row-by-row.

It is of course up to you

-Jamie

|||

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

|||

Tom VdP wrote:

Jamie,

I understand. But how could SSIS treat an OLEDBCommand differently than just directly executing it ? The data flow engine doesn't know what the SQL command does, hence there is no way it would be able to buffer the result. Every "row" in the dataflow below the OLEDBCommand must have been processed (in our case: inserted in a table). Or am I thinking too much the old "DTS-way" ?

Regards,

Tom

Tom,

Basically, yeah. You're thinking in DTS terms. The data-flow processes rows in groups called buffers whereas DTS was more row-by-row. Hence, if a row enters the LOOKUP component and it requires a row from the same buffer to be in the lookup table - it won't (yet) be in the lookup table.

So when i talk about buffers - I don't mean that any result is being buffered. Sorry, I should have elaborated on that more. The buffer architecture is a key feature of SSIS and I highly recommend you read around it to understand what it does and why it does it.

-Jamie

|||

Slight misunderstanding :-)

I thought you were hinting at the fact that the output of the OLEDBCommand might not yet be available. But you are referring to the input of the Lookup component. But then what is the CacheType property for ? If this is set to "none", doesn't it indicate that each lookup should be done against the actual contents of the table ? This property is undocumented in MSDN...

Regards,

Tom

|||

Correct. But like I'm trying (and failing miserably ) to explain is that there is no guarantee that a row put there by an OLE_DB Command/Destination adapter/whatever... will be available to all subsequent rows in the data-flow.

-Jamie

|||

Ok... to roundup allow me to summarize:

all data below an OLEDBCommand will have had its corresponding SQL statement executed, hence those changes are visible in the database

Need help!!!! (SP or SSIS Task)

I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

I have some 15 tables being populated using this SPROC.

Here is some helpful description

ENTITYNAME gives me the table i need to work

FIELDNAME gives me the field i have to work on

CHANGEDVALUE gives me the value changed in that field

( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

while in Cursors i need to get using these above variables other variables like

FLAG

KeyName

Thrugh SQL1 I get the KeyValue

then using this KeyValue check if the data exist update else insert new data.

QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

/*******************************************************************************************************/

DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage

OPEN Table_cursor

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE @.FLAG NVARCHAR(50);
SET @.FLAG = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'LastUpdateFlag';

DECLARE @.KeyName NVARCHAR(50);
SET @.KeyName = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'Key'

DECLARE @.KeyValue NVARCHAR(50)

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = N'Select @.KeyValueOUT = '+ @.KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @.KEYID + ' GROUP BY ' + @.KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

EXECUTE sp_executesql @.SQL1, N'@.KeyValueOUT INT OUTPUT', @.KeyValue OUTPUT;

DECLARE @.WC_TABLE NVARCHAR(100)
SET @.WC_TABLE = 'WorkingCopy' + @.ENTITY

DECLARE @.SQL2 nvarchar (1000);
SET @.SQL2 = 'IF EXISTS (SELECT '+ @.KeyName +' FROM ' + @.WC_TABLE + ' WHERE ' + @.KeyName + ' = ' + @.KeyValue + ' )' +
' BEGIN UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @.WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @.ENTITY + ' WHERE ' + @.Flag + ' = ' + '''Y''' + ' AND '+ @.KeyName + ' = ' + @.KeyValue + ';' +
'UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END'

EXECUTE sp_executesql @.SQL2

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

END

CLOSE Table_cursor
DEALLOCATE Table_cursor

This is not a striaghtforward question, and there is no straightforward answer either.

The stored proc you have provided is dreadfully inefficient, and could be done much more efficiently. But that may not be an issue with the volumes of data you are working with.

An SSIS package is another approach, but do you need to re-develop this work? What is the justification for doing so? Do you need to be able to easily update the procedure? Perhaps using external configuration? In this case, an SSIS reimplementation may be justified. Do you need to hand it over to someone else to maintain? Then again, SSIS may be useful, especially if the maintainers have little experience with T-SQL.

Nobody here is going to say to you to do it one way or another way. I would ask the person who is paying for the job what it is they are after.

need help writing batch without cursor

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.
description of what i'm trying to do:
there is TABLE1, TABLE2, and TABLE3
i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3
how do i do this in a T-SQL batch?
thanks in advance!What kind of analysis?
AMB
"iaesun@.yahoo.com" wrote:

> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
> description of what i'm trying to do:
> there is TABLE1, TABLE2, and TABLE3
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
> how do i do this in a T-SQL batch?
> thanks in advance!
>|||i'd be curious how to do it even if there were no analysis, since it's
the row-by-row part that i'm not sure how to do in a set-based
solution.
but! in case it helps, here's the analysis i was thinking of (pardon
the psuedo-code for the row-by-row portion)
for each ROW in TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
begin
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
end
set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
}|||>> i want to select each row from TABLE1, do some analysis on the data
of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3 <<
Read what you wrote! What kind of spec is that? How do we debug code
which is not here. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. Sample data is also a good idea,
along with clear specifications.
Frankly, it sounds likeyou are splitting this vague Table1 into two
tables when you should be using a VIEW or a column with whatever the
criteria for this split is. The whole idea of databases was to avoid
redundant data.|||You don't know Joe.
Maybe he's actually normalizing table1.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112986095.913817.293370@.g14g2000cwa.googlegroups.com...
> of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3 <<
> Read what you wrote! What kind of spec is that? How do we debug code
> which is not here. Please post DDL, so that people do not have to guess
> what the keys, constraints, Declarative Referential Integrity,
> datatypes, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications.
> Frankly, it sounds likeyou are splitting this vague Table1 into two
> tables when you should be using a VIEW or a column with whatever the
> criteria for this split is. The whole idea of databases was to avoid
> redundant data.
>|||Try,
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"iaesun@.yahoo.com" wrote:

> i'd be curious how to do it even if there were no analysis, since it's
> the row-by-row part that i'm not sure how to do in a set-based
> solution.
> but! in case it helps, here's the analysis i was thinking of (pardon
> the psuedo-code for the row-by-row portion)
> for each ROW in TABLE1
> {
> if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
> begin
> insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
> end
> set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
> insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
> }
>|||Correction,
Swith the order of the statements.
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"Alejandro Mesa" wrote:
> Try,
> insert into t2 (colA)
> select col1
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
> insert into t3 (colB, colC)
> select col2, col3
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
>
> AMB
> "iaesun@.yahoo.com" wrote:
>|||well, i'm trying to keep the topic abstract, because i was hoping for a
more general description of how to do row-by-row processing in a
set-based solution. but, if such details are needed in this case, then
let me try to invent some. regarding the latter portion of your
message: this is, in a manner of speaking, splitting table1 into two
tables. however, it is more of a complex tranformation, not redundant
information. table1 is a staging table, and will be dropped after this
process is complete.
first, the three table definitions:
CREATE TABLE [sourcetable] (
[ID] [int] NOT NULL,
[column1] [int] NULL,
[column2] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable1] (
[ID] [int] NOT NULL,
[columnA] [int] NULL,
[columnB] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable2] (
[ID] [int] NOT NULL,
[columnY] [int] NULL,
[columnZ] [int] NULL,
) ON [primary]
GO
what i would like to do, read [sourcetable] row-by-row. for each row, i
would like to perform the following batch:
IF NOT EXISTS (SELECT * FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1)
begin
INSERT INTO destinationtable1 (columnA) VALUES
(sourcetable.column1)
end
SELECT @.idvariable = ID FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1
INSERT INTO destinationtable2 (columnY, columnZ) VALUES
(sourcetable.column2, @.table2id)
is that any clearer?|||yes, that is precisely the nature of this task. table1 is an imported
table from an outside system. i'm just splicing it into its logical,
normalized form.|||> what i would like to do, read [sourcetable] row-by-row. for each row,
i
would like to perform the following batch
Wrong. The idea is precisely to AVOID processing anything row-by-row.
Try this:
INSERT INTO destinationtable1 (columna)
SELECT DISTINCT column1
FROM sourcetable
WHERE NOT EXISTS
(SELECT *
FROM destinationtable1
WHERE columna = sourcetable.column1)
INSERT INTO destinationtable2 (columny, columnz)
SELECT S.column2, D.id
FROM sourcetable AS S
JOIN destinationtable1 AS D
ON S.column1 = D.columna
David Portas
SQL Server MVP
--

need help with web developer

hey everyone...guys i'll be honest i am a noob to web developer but i want to learn this real bad..i have been given a task from my teacher to make a table in SQL...i am really confused how to create a table? i have used the sql+ an old version before but i dont find any sort of an editor excep for the visual web developer itself..i can see a table designer but it is not accessible...i am really sorry for my noobness but i really want to learn...awaiting replies

You need to install SQL Server Express with Management Studio if you have not installed it and use the videos in the links below to guide you to create tables and other objects in SQL Server. Hope this helps.

http://www.asp.net/learn/videos/

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2036523&SiteID=1

|||

In order to create objects in SQL using VWD you will need to create a database first. You can create a database in VWD using the normal 'Create...' dialog box, just choose SQL Databasee when presented with the option. Once you've done that you should be able to pull up the database in VWD and do things such as create new tables.

You can also use Management Studio as has also be suggested, but you should check with your teacher to see if there is a prefered path you should follow. Creating a database and table using Management Studio has different ramifications than using VWD. If this is part of a larger class project about creating a program in VWD that uses a database, then you should use the VWD tools to create the database so that your ultimate VWD project can find the database easily.

Mike