Plz Clarify my query in SSIS

Hi There,

I have Task in SSIS to import the flat file to the table and break the table and insert and update the other tables in the same database.

I searched every where i couldnt find appropriate tutorial for this.

Please advice me how to achieve this task.

Thanks in Advance

Regards

Savera

Savera wrote:

Hi There,

I have Task in SSIS to import the flat file to the table and break the table

What do you mean by "break the table"?

Savera wrote:

and insert and update the other tables in the same database.

What other tables? where are you inserting FROM?

Savera wrote:

I searched every where i couldnt find appropriate tutorial for this. Please advice me how to achieve this task.

Which bit?

Savera wrote:

Thanks in Advance

Regards

Savera

-Jamie

|||Load the table first in one data flow. Then in a second data flow, select from that table as the source. Use a conditional split transformation to direct the rows to other destinations based on your criteria.|||

HI Jamie,

Thanks a lot.

I have some more queries to clarify.

I have some complicated criteria like join the tables and set the flag in main table if any of the table updation tasks fails.Will it be done in Conditional split Transformation if its not please let me know how to get this task done.

Thanks in Advance.

Regards

|||

Savera wrote:

HI Jamie,

Thanks a lot.

I have some more queries to clarify.

I have some complicated criteria like join the tables and set the flag in main table if any of the table updation tasks fails.Will it be done in Conditional split Transformation if its not please let me know how to get this task done.

Thanks in Advance.

Regards

To set a flag in a table you would probably use an Execute SQL Task. Put an OnError precedence constraint between your data-flow and this new Execute SQL Task. Something like that anyway - I don't really understand why you're talking about a conditional split.

-Jamie

|||

Jamie,

Thanks for the reply.

I wanted to ask you that whether complicated query can be set in conditional split transformation or have to use Execute sql task ?

Please let me know how to put an OnError precedence constraint between your data-flow and this new Execute SQL.

Ans also i am getting an error while mapping between flat file to database tables the error is column cannot convert between unicode and non unicode string data types.Please advice how it can be solved.

Many thanks in Advance.

Regards,

|||

Savera wrote:

Jamie,

Thanks for the reply.

I wanted to ask you that whether complicated query can be set in conditional split transformation or have to use Execute sql task ?

This makes no snse. You cannot execute queries from the Conditional Split component.

Savera wrote:

Please let me know how to put an OnError precedence constraint between your data-flow and this new Execute SQL.

Drag a precedence constraint between the two tasks (it will be OnSuccessby default) and edit the properties of it. Make it an OnError constraint.

Savera wrote:

Ans also i am getting an error while mapping between flat file to database tables the error is column cannot convert between unicode and non unicode string data types.Please advice how it can be solved.

Somewhere you are trying to push a unicode value into a non-unicode field. You should use a Data Conversion component to explicitly make the change that you need.

-Jamie

|||Savera,
Please read through my posts in this thread. I think you should go through some tutorials so that you can obtain a better understanding of SSIS.|||

Phil Brammer wrote:

Savera,
Please read through my posts in this thread. I think you should go through some tutorials so that you can obtain a better understanding of SSIS.

Agreed.

This may be of use:

Online Beginner Resources
(http://blogs.conchango.com/jamiethomson/archive/2007/01/30/SSIS_3A00_-Online-Beginner-Resources.aspx)

-Jamie

Plug-in Algorithms in Managed Code

Can anyone update me on the status of the managed-code framework for plug-in algorithms? I've heard reports that it's "in the works" and "almost ready to release", and now I'm really anxious to get my hands on it!

We're still working out the delivery details for the component. Hopefully it will be available by April.

Thanks

-Jamie

PLS. HELP. SQL NEWBIE

hello all. please tell me why the following update staement doesn't
work.

what i want to do is update tblmaster.mcol2 based on the value of
tblheader.hcol2

hcol2 values:
1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2

i tried it using query analyzer but it still adds even though the
value of hcol2 is 2.

create table tblmaster ( mcol1 nvarchar(3),
mcol2 float )

insert into tblmaster values ('001', 1)
insert into tblmaster values ('002', 1)
insert into tblmaster values ('003', 1)
insert into tblmaster values ('004', 1)
insert into tblmaster values ('005', 1)

create table tblheader ( hcol1 int,
hcol2 smallint )

create table tbldetails ( dcol1 int,
dcol2 nvarchar(3),
dcol3 float,
dcol4 float )

insert into tblheader values (1, 1)
insert into tblheader values (2, 1)
insert into tblheader values (3, 2)

insert into tbldetails values ( 1, '001', 1, 10 )
insert into tbldetails values ( 1, '002', 1, 10 )

insert into tbldetails values ( 2, '001', 1, 10 )
insert into tbldetails values ( 2, '003', 2, 10 )

insert into tbldetails values ( 3, '004', 1, 10 )
insert into tbldetails values ( 3, '005', 2, 10 )

declare @.lo as int
declare @.hi as int

set @.lo = 1
set @.hi = 3

UPDATE tblmaster
SETmcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t, tblheader h, tbldetails d
WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi

select * from tblmaster

TIA,

diegoHi Rey

It is not clear how your tables are actually related but you may want to try
something like:

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego|||Hi Rey

It is not clear how your tables are actually related but you may want to try
something like:

UPDATE t
SET mcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t
JOIN tbldetails d ON t.mcol1 = d.dcol2
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi

As tblmaster has is a one to many relationship with tbldetails you will have
to either restrict which rows match or use an aggregate

UPDATE t
SET mcol2 = mcol2 + val
FROM tblmaster t
JOIN ( SELECT d.dcol2, SUM( CASE WHEN h.col2 = 1 THEN d.dcol3 * dcol4
ELSE -1 * dcol3 * dcol4 END ) as val
FROM tbldetails d
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi GROUP BY d.dcol2 ) d ON t.mcol1 =
d.dcol2

John

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego

Pls Help-2000 Replication

How do I know whether the record in a row was insert,update and delete that replicated to a subscriber?
Is there a flag or an indicator in msmerge_contents,msmerge_tombstone ,msmerge_genhistory tables or any other msdb/replication system tables?You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks

Originally posted by joejcheng
You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Sory I left out a sentence. Let say, just to know whether the records in MSMERGE_CONTENTS and MSMERGE_TOMBSTONE has been send over to replicate on Publisher or Subscriber.

Thanks again!

Originally posted by eshl
Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks|||msrepl_commnads and msrepl_transactions.|||I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Originally posted by joejcheng
msrepl_commnads and msrepl_transactions.|||back to this issue, I could find the history record that stored the latest record that was merged in th publisher and subscriber. By using this, I will be able to know which record was generated and which wasnt. This is helpful in determining the records that was replicated or not yet.
I m looking into modifying the replication engine,MSSQL replication engine is flexible.

Originally posted by eshl
I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Please provide me with a solution

I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
SubhaAre these 2 DBs on the same server or different servers?|||The sqlnts1 and 2 are two servers.

I would look forward ot hear from you.
Thanks,
Subha

Originally posted by sbaru
Are these 2 DBs on the same server or different servers?|||What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs|||I have only one transaction on. I don't open multiple transactions.
@.trancount is only 1

Originally posted by Enigma
What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs

Please provide me with a solution

I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
SubhaCrosspost

http://www.dbforums.com/t970872.html (http://www.dbforums.com/t970872.html)|||I could not see any reply posted by enigma??????

Originally posted by subkrish
I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
Subha

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedu
re
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data int
o
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when firs
t
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/d... />
2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
NikiIt automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"nikila" wrote:
> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

pLEASE HELP:URGENT please

I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
It automatically fires. Here's a rough example:
create trigger tru_MyTable on MyTable
after update
as
if @.@.ROWCOUNT = 0
return
insert MyOtherTable
select
*
from
inserted
go
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"nikila" <nikilav@.yahoo.com> wrote in message
news:O%232LMtoVFHA.1384@.TK2MSFTNGP09.phx.gbl...
I am new to sql server . I have to write a trigger to insert the data into
second table when there is any update in first table. Do I have to execute
the trigger every time by some command or it automatically fires when first
table gets updated?
Thanks in advance
Niki
|||Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when first
> table gets updated?
> Thanks in advance
> Niki
>
>
|||Uh, this trigger would insert the entire contents of TABLE-1 into TABLE-2
every time TABLE-1 was updated.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A4F71FF9-CD9F-45B7-A95F-C728E89B156D@.microsoft.com...
Hi Nikila
The difference between Trigger and Stored Procedure is,
You need to execute a stored procedure explicitly, to run the stored
procedure
There is no way where you can fire a Trigger from outside. The Trigger is
fired when an operation is performed on a table.
U need a "FOR UPDATE" trigger in your situation
Here is how you do it:
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE-1>
FOR UPDATE AS
INSERT INTO TABLE-2 SELECT * FROM TABLE-1
GO
For more information about triggers, you can refer:
http://msdn.microsoft.com/library/de...eate2_7eeq.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"nikila" wrote:

> I am new to sql server . I have to write a trigger to insert the data
> into
> second table when there is any update in first table. Do I have to execute
> the trigger every time by some command or it automatically fires when
> first
> table gets updated?
> Thanks in advance
> Niki
>
>

Please help: Views and INSTEAD OF UPDATE trigger

Hi:

Currently I have two tables T1 and T2. A view V1 is defined over T1 with an INSTEAD OF UPDATE trigger and another view V2 is defined over V1 and T2 with another INSTEAD OF UPDATE trigger. Unfortunately, inside V2s trigger following update statement is not working:
Update V1 set V1.name = i.name from inserted i
because SQL server complains:
View 'V1' has an INSTEAD OF UPDATE trigger and cannot be a target of an UPDATE FROM statement.
Is there any way to get around this problem? I.e., how can I make the INSTEAD OF UPDATE trigger in V2 to work if I cant reference inserted?

Your help is appreciated,

JeffI think the problem is not with your "inserted" table, but with the target of the insert (View V1).

Modify your INSTEAD OF triggers so that they reference the underlying tables directly, rather than through secondary views.

Please Help: Error: Missing semicolon (;) at end of SQL statement.

Hey

I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.

I am recieving this error and don't know why, i have the following coed below.

Dim objReaderQ as OleDBDataReader
 Dim strSQLRead As String
 Dim objCmd As New OleDbCommand
 strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
 objCmd = new OleDbCommand(strSQLRead, objConn)
 objReaderQ = objCmd.ExecuteReader()
 if objReaderQ.Read()
 'update quantity by 1
 Dim i as integer
 i = objReaderQ("quantity")
 i = i + 1
 objReaderQ.Close()
 Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@.quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
 Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
 objCmdQuantity.Connection = objConn
 objCmdQuantity.Parameters.Add("@.quantity", OleDbType.VarChar, 255)
 objCmdQuantity.Parameters("@.quantity").Value = i
 objCmdQuantity.ExecuteNonQuery() ' <-- Error Is Occuring On This Line
 Response.Redirect("ViewBasket.aspx")
 end if

I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.

Thanks you for your time

ChrisHey,

I have solved this problem, wrong sql statement, should be update! lol :(

But i do have the problem that once go to the viewbasket.aspx page it shows the product with the quantity 1, as it pulls the quantity from the db and its default value is 1 (which is correct).

But now if that same product is clicked 'Add To basket' for a second time, it executes the above code and goes to the viewbasket.aspx page, but the quantity stays as 1 ! (should be 2)

And now if that same product is clicked 'Add To basket' for a third time, it executes the above code again and goes to the viewbasket.aspx page, but this time the quantity is 2 ! (should be 3)

From this point on the code worked fine and increments the number properly, 4,5,6 etc..

Any idea why the first two clicks dont work ?

Thank you for your help

Chris

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterwards
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?
Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:

> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwards
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says it
> could not verify user even though I have even changed the password and know
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterward
s
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:

> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwa
rds
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says i
t
> could not verify user even though I have even changed the password and kno
w
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?

Please Help! Service Pack update Cannot Verify User with SA login

I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
Services & Manager,Decision Service Objects and Client Components. Afterwards
I tried to run the update that MS put out for Analysis Services and in the
install you have two options: Use user logged on authentication or use SQL
authentication and provide the SA password. I do this and it always says it
could not verify user even though I have even changed the password and know
that it is correct. I am running SQL in Mixed Mode Authentication if this
makes a difference?Also, Service Pack 3 was already installed on this server for SQL, But
Analysis Services and Manager was not. So I installed them. So Do I still
need to run the update. There are 3 different files for updateing SQL. 1.
Database Components
2. Analysis Services
3. Desktop Engine
But I get the error that I mentioned when I run the analysis update!
"Jay" wrote:
> I have SQL 2000 that has Service Pack 3 installed. So I installed Analysis
> Services & Manager,Decision Service Objects and Client Components. Afterwards
> I tried to run the update that MS put out for Analysis Services and in the
> install you have two options: Use user logged on authentication or use SQL
> authentication and provide the SA password. I do this and it always says it
> could not verify user even though I have even changed the password and know
> that it is correct. I am running SQL in Mixed Mode Authentication if this
> makes a difference?