attach IDENTITY property to an existing column

Hi All,
Can any body tell me that how we can attach IDENTITY property to an existing int columnyou can not.

you have to...

1. rename the existing table
2. drop the foriegn keys referencing it.
3. drop the constraints on the renamed table.
4. define a new table with the identity property with the orginal table name
--the next 2 can go in any order based on what you decide is best
5. then reapply all constraints on the table, usually doing the primary key first.
6. insert data from the renamed table to the new table. you may want to do a INSERT IDENTITY MyTable ON to insert your existing keys. Just do not forget to turn it off when you are done.
7. drop the renamed table (optional)

you may want to wrap all or part of this in an explicit transaction with appropriate error handlers and print messages and you may not want to drop renamed table until you have committed the transaction without error and your row count after the insert matches the rowcount in the new table.

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_id
Hi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id
|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> for
> value
> to
> the
> where
>
>
|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...[vbcol=seagreen]
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:

> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>
>
|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.

> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...[vbcol=seagreen]
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> > I hope you guys can help. I am looking to create a trigger that watches
> for
> > updates to the sch_status column and fires when the update changes the
> value
> > to 4. The code that will be executed is below with addition of some code
> to
> > e-mail the results. It is the actual code to create the update trigger on
> the
> > schedule table I need some help with.
> >
> > select s.sch_id,
> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> > s.e_subject,
> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> where
> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> > dl.sch_id = s.sch_id),
> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> > convert(varchar(12),getdate(),112) +'.log'
> > from schedule s
> > where s.sch_status = 4
> > order by s.sch_id
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> All what you have to do it join the virtual table INSERTED in your query
>> on
>> the primary key.
>> It is not recommended that you send e-mails from within a trigger, as it
>> hurts performance, has horrible consequences with locking and blocking,
>> and
>> if the e-mail sending fails, your update will get rolled back (and you
>> might
>> have sent e-mails to 1/2 your customer base). Rather write a record into
>> a
>> queue type table where another process comes along a sends the e-mails.
>> Books On Line has some good examples on CREATE TRIGGER and how to use the
>> virtual tables INSERTED and DELETED.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> > I hope you guys can help. I am looking to create a trigger that watches
>> for
>> > updates to the sch_status column and fires when the update changes the
>> value
>> > to 4. The code that will be executed is below with addition of some
>> > code
>> to
>> > e-mail the results. It is the actual code to create the update trigger
>> > on
>> the
>> > schedule table I need some help with.
>> >
>> > select s.sch_id,
>> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> > s.e_subject,
>> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
>> where
>> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
>> > dl.sch_id = s.sch_id),
>> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> > convert(varchar(12),getdate(),112) +'.log'
>> > from schedule s
>> > where s.sch_status = 4
>> > order by s.sch_id
>>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:
> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> > In case it leads to a different outcome I plan to have the select
> > statement
> > and the e-mail (not sql mail) in a stored procedure and have it called by
> > the
> > trigger.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> All what you have to do it join the virtual table INSERTED in your query
> >> on
> >> the primary key.
> >>
> >> It is not recommended that you send e-mails from within a trigger, as it
> >> hurts performance, has horrible consequences with locking and blocking,
> >> and
> >> if the e-mail sending fails, your update will get rolled back (and you
> >> might
> >> have sent e-mails to 1/2 your customer base). Rather write a record into
> >> a
> >> queue type table where another process comes along a sends the e-mails.
> >>
> >> Books On Line has some good examples on CREATE TRIGGER and how to use the
> >> virtual tables INSERTED and DELETED.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> >> > I hope you guys can help. I am looking to create a trigger that watches
> >> for
> >> > updates to the sch_status column and fires when the update changes the
> >> value
> >> > to 4. The code that will be executed is below with addition of some
> >> > code
> >> to
> >> > e-mail the results. It is the actual code to create the update trigger
> >> > on
> >> the
> >> > schedule table I need some help with.
> >> >
> >> > select s.sch_id,
> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> >> > s.e_subject,
> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> >> where
> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> >> > dl.sch_id = s.sch_id),
> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> >> > convert(varchar(12),getdate(),112) +'.log'
> >> > from schedule s
> >> > where s.sch_status = 4
> >> > order by s.sch_id
> >>
> >>
> >>
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>> You still have the same issues (concurrency/performance), even if you
>> execute a proc in order to send email from the trigger. I suggest you
>> heed
>> Mike's suggestion and employ a queue-type table so that you can send the
>> actual email asynchronously. It's fairly simple to implement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>> > In case it leads to a different outcome I plan to have the select
>> > statement
>> > and the e-mail (not sql mail) in a stored procedure and have it called
>> > by
>> > the
>> > trigger.
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> All what you have to do it join the virtual table INSERTED in your
>> >> query
>> >> on
>> >> the primary key.
>> >>
>> >> It is not recommended that you send e-mails from within a trigger, as
>> >> it
>> >> hurts performance, has horrible consequences with locking and
>> >> blocking,
>> >> and
>> >> if the e-mail sending fails, your update will get rolled back (and you
>> >> might
>> >> have sent e-mails to 1/2 your customer base). Rather write a record
>> >> into
>> >> a
>> >> queue type table where another process comes along a sends the
>> >> e-mails.
>> >>
>> >> Books On Line has some good examples on CREATE TRIGGER and how to use
>> >> the
>> >> virtual tables INSERTED and DELETED.
>> >>
>> >> Regards
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> >> > I hope you guys can help. I am looking to create a trigger that
>> >> > watches
>> >> for
>> >> > updates to the sch_status column and fires when the update changes
>> >> > the
>> >> value
>> >> > to 4. The code that will be executed is below with addition of some
>> >> > code
>> >> to
>> >> > e-mail the results. It is the actual code to create the update
>> >> > trigger
>> >> > on
>> >> the
>> >> > schedule table I need some help with.
>> >> >
>> >> > select s.sch_id,
>> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> >> > s.e_subject,
>> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list
>> >> > dl
>> >> where
>> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl
>> >> > where
>> >> > dl.sch_id = s.sch_id),
>> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> >> > convert(varchar(12),getdate(),112) +'.log'
>> >> > from schedule s
>> >> > where s.sch_status = 4
>> >> > order by s.sch_id
>> >>
>> >>
>> >>
>>

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on th
e
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by th
e
trigger.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> All what you have to do it join the virtual table INSERTED in your query o
n
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, an
d
> if the e-mail sending fails, your update will get rolled back (and you mig
ht
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> for
> value
> to
> the
> where
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...[vbcol=seagreen]
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:

> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you hee
d
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.

> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...[vbcol=seagreen]
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>

Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assigning column types in select into

Group,

Is there a way to assign nullability on a column when using a select into?
I've tried some of the usual things like coalsce, isnull, and cast. Since
the new table gets definition from the source table or can be somewhat
adjusted with cast is there a way to cast a not null? In the example below
how can I select into causing tableone_new..col2 to be not null. We
typically must use an alter statement after the select into but this seems
inefficient.

Thanks,
Ray

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
col2
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
goRay (someone@.nowhere.com) writes:
> Is there a way to assign nullability on a column when using a select
> into? I've tried some of the usual things like coalsce, isnull, and
> cast. Since the new table gets definition from the source table or can
> be somewhat adjusted with cast is there a way to cast a not null? In
> the example below how can I select into causing tableone_new..col2 to be
> not null. We typically must use an alter statement after the select
> into but this seems inefficient.

As far as I know there isn't. You might be better off with CREATE TABLE
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Ray" <someone@.nowhere.com> wrote in message
news:t7tTd.8720$rK1.8198@.newssvr31.news.prodigy.co m...
> Group,
> Is there a way to assign nullability on a column when using a select into?
> I've tried some of the usual things like coalsce, isnull, and cast. Since
> the new table gets definition from the source table or can be somewhat
> adjusted with cast is there a way to cast a not null? In the example
> below how can I select into causing tableone_new..col2 to be not null. We
> typically must use an alter statement after the select into but this seems
> inefficient.

<snip
ISNULL() appears to do what you want (8.00.760 Enterprise Edition), although
COALESCE() does not (don't ask me why). Personally, I agree with Erland - it
would be much better to use CREATE TABLE and therefore make your intentions
clear and your data model explicit.

Simon

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
isnull(col2, 1) as col2 -- tableone_new.col2 is NOT NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
coalesce(col2, 1) as col2 -- tableone_new.col2 IS NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go|||Simon Hayes (sql@.hayes.ch) writes:
> ISNULL() appears to do what you want (8.00.760 Enterprise Edition),
> although COALESCE() does not (don't ask me why).

There is a similar case with indexed views where you can use isnull()
to make a view indexable but where coalesce() does not cut it. And,
no, I can't really make any sense of it.

(And in the particular case where I looked at it, the view does not
index at all in SQL 2000, but does so in SQL 2005 when you use isnull.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

assign numeric data type to user variable

how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

there is no numeric data type in variable..

if you click the drop down box in variable data type, you can only see the below data type:

Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char

Do some searching on this forum. It has been discussed in just the last week, along with several other occasions. You'll likely have to read it in as a string and then later convert it to an integer using variable expressions.|||thank you

Assign Null value to Variable

I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro

The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Am i getting this error becasue the column has NULL value?. how can I resove this probelm?

Populate your source with 0s instead of NULLs in your source query, if applicable. (Or some arbitrary number)|||

can i do thsi in Expression builder using NULL function?

if so, can you show me some examples?

|||Anywhere in an expression builder, you can do:

ISNULL(ColumnOrVariable) ? 0 : ColumnOrVariable

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

assign an output of derive column to variable

I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?

Why are you trying to assign a value from a derived column to a variable? The derived column works for every row passing through it. When passing a variable value back to the control flow, it would only contain the last value in the data flow.

With that said, you can use a script component's PostExecute sub to assign data to a variable.|||

Actually the derived column is in For each loop ( row by row process)...

"script component's PostExecute sub to assign data to a variable"

--> is there a code example that you can show me ?

Thank you for your help

|||

safddddddddddddddddddddd wrote:

Actually the derived column is in For each loop ( row by row process)...

No it isn't. You might have a data flow in the foreach loop.

The foreach loop can assign variables for each iteration. Why doesn't that work for you?|||

you are correct I meant I have a data flow which has the derive column transformation in the foreach loop..

I am tying to tryting to pass the result of derive column ( @.[User::Assessment_Dt] > assessment_dt ? 1 : 0 ) to a variable so that i can use it in SQLTASK ..

I understand i need to use a script task to assign the output of derive column in data flow to a variable ..but i don't know how? is there any examples that i can see?

|||What is the source for the foreach loop?

What is the source in the data flow?

I don't understand what you are trying to achieve, and I'd really like to help you architect this better.|||

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

|||

safddddddddddddddddddddd wrote:

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

So you're populating a data set with an execute SQL task and then using a foreach loop against that dataset?|||

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

|||

safddddddddddddddddddddd wrote:

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

You don't. You can write a SQL command in a variable expression that maps other variables (from your foreach loop) and then use the SQL variable as your OLE DB Source.

For instance:
@.TypeofAssessment - Source: Foreach loop
@.SQLCommand - Source: Expression: "select * from table where assessment = " + @.TypeofAssessment

OLE DB Source - Set to use sql command from variable - choose @.SQLCommand.|||yes. that is what i was trying to do...but since there are 71 columns that i am passing as variables,it's a kind of time consuming job.. thank you for all your help.. i really appreciate it..

Assign Aggregate count results to variable

I have an aggregate transform that outputs two columns, a group by (DT_STR) and a count(column name) (DT_UI8). The results are put into a Recordset Destination. When I attempt to map these columns to variables in a Foreach Loop Container (using a Foreach ADO Enumerator), I get the error:

Error: 0xC001F009 at ExtractNNRPersonUpdates: The type of the value being assigned to variable "User::NNRPersonCount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Foreach Household Loop Container: ForEach Variable Mapping number 2 to variable "User::NNRPersonCount" cannot be applied.

The variable mentioned in the error message is setup as a UInt64. I've tried all other integer data types and nothing works. I also tried changing the data type of the count coming out of the Aggregate transform but received a warning stating this isn't possible.

Any idea what I may be doing wrong?

I had a lot of trouble with this error, and ended up doing explicit casts in a script where I was doing my comparison, using the CType function.

eg:

Row.newid = rowIndex + CType(Me.Variables.MyCounter, ULong)

Dylan.

|||Out of desperation, I began trying all of the data types. When I set it to DBNull, it worked. I was then able to cast it to an integer in a Script Task. Doesn't seem right though because the values are never null.

Assign Aggregate count results to variable

I have an aggregate transform that outputs two columns, a group by (DT_STR) and a count(column name) (DT_UI8). The results are put into a Recordset Destination. When I attempt to map these columns to variables in a Foreach Loop Container (using a Foreach ADO Enumerator), I get the error:

Error: 0xC001F009 at ExtractNNRPersonUpdates: The type of the value being assigned to variable "User::NNRPersonCount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC001C012 at Foreach Household Loop Container: ForEach Variable Mapping number 2 to variable "User::NNRPersonCount" cannot be applied.

The variable mentioned in the error message is setup as a UInt64. I've tried all other integer data types and nothing works. I also tried changing the data type of the count coming out of the Aggregate transform but received a warning stating this isn't possible.

Any idea what I may be doing wrong?

I had a lot of trouble with this error, and ended up doing explicit casts in a script where I was doing my comparison, using the CType function.

eg:

Row.newid = rowIndex + CType(Me.Variables.MyCounter, ULong)

Dylan.

|||Out of desperation, I began trying all of the data types. When I set it to DBNull, it worked. I was then able to cast it to an integer in a Script Task. Doesn't seem right though because the values are never null.

asp/net sql update against ntext datatype

Guys Ihave a table that among the other columns has a column of ntext.

I also have another table that has another ntext column...
due to normalizations I need to merge the tables and set the column on the first table plus the value from the second table...(based on some where clause...

How can this be done?
if data type is varchar it is no problem..but due to text datatype I am unable to perform this update...

please help.

FrankUnfortunately you cannot concatenate an ntext field. I think the best you are going to be able to do is something like this:

SELECT
CAST(CAST(myText AS nVarchar(4000)) + CAST(myText AS nVarchar(4000)) AS ntext)
FROM
Test

However this will obviously truncate both columns at 4000 before putting them together.

Terri|||but the length exceeds 8000.

that is actually the problem.|||My only suggestion then is to run a VB.NET (or C#) program that selects in the data from both sources, concatenates the nText data, then updates the nText field in the desired database table. I don't think you are going to be able to do this within just Transact-SQL itself.

Terri|||well that requires application code..it must be a way of doing it too...in sql..
thanks anyway.|||There is no way to concatenate nText fields to each other. If you find a way around it, let us know.

Quoting directly from the Books Online:

+ (String Concatenation)
An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Syntax
expression + expression

Arguments
expression

Is any valid Microsoft® SQL Server? expression of any of the data types in the character and binary data type category,except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

Terri

ASP.Net Unleashed Listing 12.16 Example

I am trying to get the following code to work but I keep getting an error.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__title__060DEAE8'. The conflict occurred in database 'pubs', table 'titleauthor', column 'title_id'.

Has anyone else experienced a problem with this example? Let me know what is wrong
with it.

Thanks,
Ralph

<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<script runat="server"
 Sub Page_Load
 Dim dstPubs As DataSet
 Dim conPubs As SqlConnection
 Dim dadTitles As SqlDataAdapter
 Dim dtblTitles As DataTable
 Dim drowTitle As DataRow
 Dim objCommandBuilder As New SqlCommandBuilder
 ' Grab Titles Table
 dstPubs = New DataSet()
 conPubs = New SqlConnection( "Server='(local)';Database=Pubs;trusted_connection=true" )
 dadTitles = New SqlDataAdapter( "Select * from Titles", conPubs )
 dadTitles.Fill( dstPubs, "Titles" )
 dtblTitles = dstPubs.Tables( "Titles" )
 ' Display Original Titles Table
 dgrdOriginalTitles.DataSource = dstPubs
 dgrdOriginalTitles.DataBind()
 ' Add a Row
 drowTitle = dtblTitles.NewRow()
 drowTitle( "Title_id" ) = "xxxx"
 drowTitle( "Title" ) = "ASP.NET Unleashed"
 drowTitle( "Price" ) = 1200.00
 drowTitle( "Type" ) = "Mystery"
 drowTitle( "PubDate" ) = #12/25/1966#
 dtblTitles.Rows.Add( drowTitle )
 ' Delete the First Row
 dtblTitles.Rows( 0 ).Delete()
 ' Double the price of the Second Row
 drowTitle = dtblTitles.Rows( 2 )
 drowTitle( "Price" ) *= 2
 ' Generate the SQL Commands
 objCommandBuilder = New SqlCommandBuilder( dadTitles )
 ' Update Titles Table
 dadTitles.Update( dstPubs, "Titles" )
 ' Display New Titles Table
 dgrdNewTitles.DataSource = dstPubs
 dgrdNewTitles.DataBind()
 End Sub
</script>
<html>
<head>
 <title>UpdateDataSet</title>
</head>
<body>
 <h2>Original Titles Table
 </h2>
 <asp:DataGrid id="dgrdOriginalTitles" Runat="Server"></asp:DataGrid>
 <h2>New Titles Table
 </h2>
 <asp:DataGrid id="dgrdNewTitles" Runat="Server"></asp:DataGrid>
</body>
</html>

change this line

dgrdOriginalTitles.DataSource = dstPubs

to this

dgrdOriginalTitles.DataSource = dtblTitles

see if that works|||Thanks for the suggestion. I tried something else...I remarked out the following code and then it worked fine.

Thanks,
RT

' Update Titles Table
dadTitles.Update( dstPubs, "Titles" )

ASP.NET / SQL Server Stored Procedure Question

Hello,

I wrote a stored procedure that inserts data into one table, then inserts the value of the identity column into another table:

SET

NOCOUNTON;

INSERTINTO ContactUs_TBL

(FullName, Email, Phone, Message)

VALUES

(@.FullName, @.Email, @.Phone, @.Message)

SELECT@.@.IDENTITY

INSERTINTO ContactUsQuestions_TBL

(QuestionText, ContactId)

VALUES

(@.QuestionText,@.@.IDENTITY)

In the CodeFile in asp.net (c#.net), I'm not what to set the value property to below. Right now I just hardcoded a 2 to see how it would work. Could anyone help me out and tell me what I should put here? Each of the other statements I used were set to the value of a form control, but since this id isn't a form control, just an identity column, I'm not sure what to do:

comm.Parameters.Add(

"@.ContactId",SqlDbType.Int);

comm.Parameters["@.ContactId"].Value = 2;

-- rkeslar

Replace @.@.IDENTITY with SCOPE_IDENTITY(). This isn't related to your problem, but you should do it anyhow.

You don't add a parameter for ContactID. You don't pass it into the stored procedure, and you don't tell .NET about it.

|||

hmm. ContactID is a primary key in the ContactUs_TBL, so I see why you wouldn't pass it into the stored procedure or tell .net about it for that table. But it's also a foreign key in the ContactUsQuestions_TBL so how is it going to get inserted into that table if I don't pass it into the stored procedure and tell .net about it?

Thanks

|||Because the stored procedure picks up the value when it's running and passes it to the second insert statement.