How to catch error and retry AFTER dts script step has ran

hello,

i am trying to figure out how to check for failure or success AFTER the script task has ran.

its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?

i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.

dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2

the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.

i have a java programming background. can anyone see this from my point of view and give me a hint?

this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...

how to call external exe from Database Trigger

Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>> Hi All,
>> There is a measurement data which storing some measurement values.
>> Now i want to write a trigger on this to achive following goal.
>> insert in a history table if the measurement.DataValue is increase by
>> some predefine value. also insert the time for this.
>> update in the time in same table if the measurement.DataValue come to
>> normal (i mean below that predefine value).
>> And also want to fier some external exe file on first insert scenario
>> (increase value)
>> Below is my stuff, it's working fine. But i don't know how do i call
>> external exe file to execute on specific condition.
>> Thanks & Regards
>> Rushikesh
>>
>> -- Trigger--
>> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
>> FOR INSERT
>> AS
>> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
>> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
>> inserted, Triggers
>> Where
>> Triggers.LocationID = inserted.LocationID
>> AND inserted.Value >= Triggers.TriggerValue
>> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
>> inserted)
>> where Trigger_History.TriggerID
>> IN( select t1.TriggerID from Triggers t1,inserted t2
>> Where t1.LocationID = t2.LocationID
>> AND t2.Value < t1.TriggerValue)
>>

how to call external exe from Database Trigger

Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] fro
m
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:

> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>

how to call external exe from Database Trigger

Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)
Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs
|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh
|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:

> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:

how to call external exe from Database Trigger

Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:

> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>

How to call an image via code behind

I'm running ssrs 2005 and want to write a function in the code in the page
something like this:
Public Shared Function ShowImage(val as integer) AS Image
IF val = 4 Then
Return apply.png
ELSE
Return nothing
END IF
End Function
Where apply.png is an image in the report project folder (source=external)
How can I do this?
--
moondaddy@.newsgroup.nospamI found a solution.
Basically, if a condition is true then I want to display the image. so I
put the following iff function in the images visibility property's hidden
section.
=IIF(Fields!VP_qpcCap.Value = 4,False,True)
I suppose if I had more complex logic, I could run a function in the code
behind and have it return true or false. then call that function in the
visibility property's hidden section.
"moondaddy" <moondaddy@.newsgroup.nospam> wrote in message
news:%23MMR56TPIHA.4740@.TK2MSFTNGP02.phx.gbl...
> I'm running ssrs 2005 and want to write a function in the code in the page
> something like this:
> Public Shared Function ShowImage(val as integer) AS Image
> IF val = 4 Then
> Return apply.png
> ELSE
> Return nothing
> END IF
> End Function
> Where apply.png is an image in the report project folder (source=external)
> How can I do this?
>
> --
> moondaddy@.newsgroup.nospam
>|||On Dec 12, 11:44 pm, "moondaddy" <moonda...@.newsgroup.nospam> wrote:
> I found a solution.
> Basically, if a condition is true then I want to display the image. so I
> put the following iff function in the images visibility property's hidden
> section.
> =IIF(Fields!VP_qpcCap.Value = 4,False,True)
> I suppose if I had more complex logic, I could run a function in the code
> behind and have it return true or false. then call that function in the
> visibility property's hidden section.
> "moondaddy" <moonda...@.newsgroup.nospam> wrote in message
> news:%23MMR56TPIHA.4740@.TK2MSFTNGP02.phx.gbl...
>
> > I'm running ssrs 2005 and want to write a function in the code in the page
> > something like this:
> > Public Shared Function ShowImage(val as integer) AS Image
> > IF val = 4 Then
> > Return apply.png
> > ELSE
> > Return nothing
> > END IF
> > End Function
> > Where apply.png is an image in the report project folder (source=external)
> > How can I do this?
> > --
> > moonda...@.newsgroup.nospam- Hide quoted text -
> - Show quoted text -
1. Add an Image object to your Report
2. Set the Value expression to "=Code.ShowImage( Fields!
VP_qpcCap.Value )
3. Change the ShowImage function to return a String
4. Change the Return line to Return "apply.pjm" (put it in double
quotes)
-- Scott

How To Call A Procedure While Declaring A Cursor

HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKSDo a insert inot temp table and then use the temp table to declare the cursor|||/*
As far as i know, there is only one way, you must create a temporary table, for example:
*/

Create Proc Caller
As
Create Table #temp (INTCUSTOMERID [FieldType],
CHREMAIL [FieldType], INTPREFERENCEID [FieldType],
CHRPREFERENCEDESC [FieldType])
Declare CHK_CUR For Select a.INTCUSTOMERID From #temp a
Insert Into #temp (INTCUSTOMERID, CHREMAIL, INTPREFERENCEID,
CHRPREFERENCEDESC) Exec MY_PROC

/*Do what ever you want with your cursor|||Hi,
Thanks Enigma And Sneaky Pie For The Quick Replies ,but The Problem Still Lies And It Has Not Helped Me Much.

The Thing Is It Might Be Possible That I Have Not Written It Down
Properly Or You Might Not Have Understood What I Have Wanted To Ask From You.

First Of All I Don't Want To Declare A Cursor Inside A Procedure For Some Reasons Out Here.

Second,i Want To Declare A Cursor Outside The Procedure And Then Call The Procedure From The Cursor.

Third,as You Said That I Should Use A Temp Table,which Is Not Possible Cause The Temp Table Is Not Present Outside The Procedure In Which It Is Created,i Might Be Wrong ,but Pls Tell Me So Then.

Fourth, I Can Go For A Permanent Table ,but It Consumes Too Much
Space,which Is A Constraint In Our Case.

Pls ,look Into The Above Scenario And Help Me Out With It.i Personally Think That It Should Be A Nobrainer,but Somehow It Has Become A Nightmare For Me.

Pls Look Into It As Soon As Possible As I Am Really Feeling The Heat On This Now.

Thanks.|||But the way with the temporary table is the only solution with procs.
Create the #table and after that you are able to create a cursor fetching this #table. If you need a result after some changes or calculations with the cursor data, you need a second #table. At the end return the data of the second #table.

There is, maybe, an other way, but it works with funtions, not with procs. You can create a function that returns a cursor.|||I think Enigma was getting at something like:CREATE TABLE #procResults (
column1 -- whatever
column2 -- etc
)

DECLARE scum CURSOR FOR SELECT
column1, column2
FROM #procResultsDoes that make more sense?

-PatP|||HI,
tHANKS SNEAKY PIE ,BUT I THINK PAT GOT IT RIGHT ABOUT WHAT I WANT : - BUT THE PROBLEM STILL PERSISTS.

I AM GETTING :-

IF,
I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY TO CALL THE PROCEDURE FROM A CURSOR AND SAVE THE RESULTS OF THE PROCEDURE IN A VARICABLE OR SOMETHING.
BUT REMEMBER ONLY AFTER THE PROCEDURE IS CALLED FROM THE CURSOR.

THANKS.|||Oops! My bad, I forgot an important step (sleep deprivation does funny things to me!). Let's try:CREATE TABLE #procResults (
column1 -- whatever
column2 -- etc
)

INSERT INTO #procResults -- The "missing link"
EXECUTE myProc

DECLARE scum CURSOR FOR SELECT
column1, column2
FROM #procResults-PatP|||Hi,

Fantastic !!! Right On !! Bull's Eye !!!!!

Got It ... Thanks To You... I Was Scratching My Skull Whole Of Last Week.

But,

Now Though, Things Are Running Fine ... It Is Giving :-

System Low On Resources
Affecting The Whole System .

Is There Any Way Or Any Settings That Could Be Changed. ?

So That I Can Allocate More Resources While The Cursor Is Running.

Thanks|||What is giving the messages? Are they in the Transact-SQL output, the SQL Server log, an NT log, or somewhere that I haven't thought of yet?

-PatP|||HI,

THE MESSAGES ARE FROM THE T-SQL OUTPUT :-

"SYSTEM LOW ON RESOURCES SOME OF THE RESULTS WOULD BE DROPPED."

THIS IS AFFECTING MY WHOLE SYSTEM... AFTER I RUN THE CURSORS

Is There Any Way Or Any Settings That Could Be Changed. ?

So That I Can Allocate More Resources While The Cursor Is Running.

Thanks|||Your choices are quite limited in that situation. You can upgrade the hardware (most likely RAM memory) to make more resources available, or stop other (non-essential) processes running on the same machine to free up the resources that they are using. Those are the only choices that I think might help.

-PatP|||OK...anyone want to ask WHY there's a CURSOR in the mix?

How many levels of nested cursors do you have?|||Hi,

I Don't Think My Ram Must Be A Problem Has It Is 256mb Ram And I Have Seen Stopping The Remaining Processes And Running Only This Processes Still It Gives A Problem.

Also, There Is Only One Level Of Cursors.

And The Data Contained In The Table Is Just Around 10,000 .so Shouldn't Be Problem.

So If Something About This Could Be Done Then It Would Be Fantastic.

Thanks.|||Don't use a Procedure use an inline table function:

CREATE FUNCTION dbo.fn_MY_PROC()
RETURNS TABLE
AS
RETURN (

SELECT TOP 100 PERCENT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

)

declare cursor cur as fast_forward local for
select * /*or whatever*/ from dbo.fn_MY_PROC()|||Hi,

Thanks,hanif For The Suggestion, But Yes I Will Have To Think On It On A Long -term Basis . About How To Convert All The Procs Into Functions .but Right Now Will Have To Stick On The Existing System To See How It Works.

Thanks.

How to call .NET framework library within SPROC

Hi, with SQL server 2005.

Could I write a SPROC with C# or VB.NET that calls methods within the .net library or

Could I package a .net methods into a SPROC to let other SPROC invoke it?

Thanks in advance.

Ricky.

You can write C#, VB.NET methods that can be used from inside SQL Server to call .NET libraries.

Niels
|||Yes, you can write a stored proc in clr (C# or VB.NET) that can call methods in the .NET Framework.
Yes.

http://msdn2.microsoft.com/en-us/library/ms131052.aspx|||

If the SPORC written with C# needs to call another C# method that is within a thrid-party component, is it feasible implement in the SQL server 2005?

Thanks,

Ricky.

|||Yes, but you have to deploy that third party component to the database as weel (i.e together with your original .NET assembly(ies)).

Niels
|||

Hi, nielsb.

I create a database project trying to add reference.

However, I cannot add .dll except for built-in .net library.

How do I deploy a third-party .dll onto sql server 2005? Please give me example if possible.

Thanks a million.

Ricky.

|||Deploy your third party dll manually to SQL Server, by using CREATE ASSEMBLY syntax. Read more about CREATE ASSEMBLY in SQL Server Books OnLine. By deploying it manually, you can then reference it from your database project.

Niels
|||

Thanks too much.

nielsb.

By the way, someone told me that previously (before .net era), he could wrote extended sproc with C and umlinitedly access the library outside SQL server. Why cannot we do as him currently? Security consideration?

Ricky.

|||

Ricky Wang wrote:

By the way, someone told me that previously (before .net era), he could wrote extended sproc with C and umlinitedly access the library outside SQL server. Why cannot we do as him currently? Security consideration?

Extebded stored proc's are still supported in SQL 2005, but are being deprecated. Main reason for this is security and reliability issues. The extended proc's are deprecated in favor of .NET methods. You can almost do anything that you are doing in and XP in a .NET method.

Niels
|||

Thanks for explanation.

Ricky.

How to build FROM clause dynamically

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.A SQL Server stored procedure is a poor option for giving users ad-hoc query capability. You would need to write a routine that parsed their input statement (very difficult considering that users have little understanding of relational databases) and then would create a logic execution plan from the statement given known relationships among tables.

Wait a minute...that's what Query Analyzer does! Why not just allow the user to submit adhoc query statements? (Make sure your security is tight and you have a query governor active!)

Either that, or check into some of the data-mining software packages such as DI Diver or Cognos.

Where do you live in Melbourne? I spent two years as a kid in Box Hill.|||Thaks for your reply,

but I am not writing the store procedure and I do not have an option using any packages, like Cognos, Crystal etc. I am writing the procedure in VB (it is not up to me). And the main idea, that uses SHOULD NOT HAVE any idea about relational database. Uses just say: I want SQL Server database(could be others - like Oracle, Sybase or MS Access), I supply them names of available servers, they choose the server, I supply names of availabe databases, then tables, then fields, they choose whatever the want, then they point at the related fields of the tables they chose before, and "magic" happened - they have a report. I've done almost everything, just bits and pieces left. AND a FROM clause! It works for simple queries, but for more complicated it works sometimes, which obviousy is not good enough. So I nee an algorithm and stuck with that.

I live at Moorabbin and I know Box Hill - very nice area. Where do you live now?|||*ack* the whole joining thing is the problem...

I have tried this before... basically unless you can query the db to find out what the foreign keys are you are kinda stuffed...

Otherwise the users have to know enough about the database to be able to define the relationships themseleves...|||it sounds to me that the best solution for your problem
which is:
dynamic sql statements
ad hoc queries
no sql knowledge at the end user

you are an excellent candidate for ENGLISH QUERY
There are sample apps available for this product
code samples and etc.

English Query (http://www.microsoft.com/sql/evaluation/features/english.asp)|||I think Ruprect's English Query suggestion is your best shot. The problem is that a user who does not know anything about relational database is more likely than not to get the WRONG ANSWER to a problem due to not understanding relational set manipulation. This is what DBAs and SQL developers are for.

Think about it. Basic SQL is not that complicated. If a user can't understand "Select columna, columnb from sometable where columnc = somevalue" they shouldn't be mucking about in a database anyway. I mean, the syntax is practically an English sentence anyway. Hey, how about a procedure that lets them submit it in Australian?

"Grab beer, prawns, lamington from cooler where label = 'Fosters'"

By the way, now I am back in the United States (Midwest), but I still remember my old address down under 25 years ago: 17 Simmons Street, Box Hill.|||if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think|||Depending on how many tables the users are going to want to access and how fluid the ad-hoc queries are, you could work this in two other ways - which aren't elegant but might be enough to serve your purpose:

Either, restrict the queries that the users can create by offering them a list of possible query options that you have already generated the SQL for, or create a table that holds the correct joining criteria for your tables so that you can select the required code.

These won't work if you do want it to be a completely open ended query tool - but I would have thought that if you're getting to the point of ad-hoc queries using OUTER JOINS, then your users will probably have the SQL knowledge already...

Just a thought...|||If you just want to give them slice, dice, and filter capability on defined recordsets. then consider a pivot table linked to a view from either a spreadsheet or a web page.|||Originally posted by r937
if you build an application that can correctly join any combination of tables from 25 possible tables, whether SQL Server, Oracle, Sybase or MS Access, using the appropriate (often proprietary) sql, with joins utilizing the right columns as determined by an analysis of foreign keys in the information schema, then you have something which you can go out and sell as commercial software against cognos, crystal, etc.

in other words, it ain't as easy as you think

That is exactly what our company is trying to do and I am aware that is not easy task, but I have to do it somehow.|||in that case i would suggest investigating INFORMATION_SCHEMA views to see if you can create queries that can access the tables, column, and especially primary/foreign keys

i think with MS access you are up the creek, but i believe the other databases all support INFORMATION_SCHEMA

good luck and let us know how your project turns out

How to browse for folder on SQL Server's machine?

Hi!
I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
allows me to write a folder select dialog. But i have seen such dialog
somewhere in one of installers. Unfortunately I dont remember in what
installer.
I dont want to reinvent a wheel. So my question is: may be there is method
somewhere in SQL Server or in SQL-DMO or in DTS which implements a folder
selection dialog based on these stored procedures?T-SQL, DMO and DTS do not present the user with GUI objects. They provide
information, and your application must present that information is a dailog.
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.suvw7dy0n8ihmu@.iw2k.helpmicro.local...
> Hi!
> I know there is xp_dirtree, xp_subdirs, xp_fixeddrives procedures which
> allows me to write a folder select dialog. But i have seen such dialog
> somewhere in one of installers. Unfortunately I dont remember in what
> installer.
> I dont want to reinvent a wheel. So my question is: may be there is method
> somewhere in SQL Server or in SQL-DMO or in DTS which implements a folder
> selection dialog based on these stored procedures?|||Ok, thank you for information. I must ask my question another way: Do you
know any library implementing such a dialog?

> T-SQL, DMO and DTS do not present the user with GUI objects. They provide
> information, and your application must present that information is a
> dailog.
> "Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
> message news:op.suvw7dy0n8ihmu@.iw2k.helpmicro.local...
>|||You have several options depending on what application development tool you
are using. This perhaps more a C# or Visual Basic question than SQL Server.
There is the Win32 API call SHBrowseForFolder
http://support.microsoft.com/defaul...kb;en-us;179497
How to write a managed C# wrapper for the API dialog call:
http://support.microsoft.com/defaul...kb;en-us;306285
Here is a Visual Basic sample:
http://www.vbexplorer.com/VBExplore...file_dialog.asp
I also think there is a method of the File System Object called
GetFolderName
http://msdn.microsoft.com/library/d...objectmodel.asp
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.suv0e6u4n8ihmu@.iw2k.helpmicro.local...
> Ok, thank you for information. I must ask my question another way: Do you
> know any library implementing such a dialog?
>
>|||As I mentioned in my original posting I can implement such dialog using
xp_dirtree, xp_subdirs, xp_fixeddrives procedures. But SQL Server's
Enterprise Manager already has such dialog. For example if you open
"Attach Database" dialog and then press browse [...] button Enterprise
Manager will show you "Browse For Existing File" dialog. And as you can
see all the path information in that dialog is local to SQL Server's
machine. This is what SHBrowseForFolder cannot do for you because
SHBrowseForFolder always shows paths relative to local machine.
My question is: Is there any way to reuse that Enterprise Manager's
"Browse For Existing File" dialog? Or I am obliged to reinvent it?
On Tue, 02 Aug 2005 19:30:46 +0300, JT <someone@.microsoft.com> wrote:

> You have several options depending on what application development tool
> you
> are using. This perhaps more a C# or Visual Basic question than SQL
> Server.
> There is the Win32 API call SHBrowseForFolder
> http://support.microsoft.com/defaul...kb;en-us;179497
> How to write a managed C# wrapper for the API dialog call:
> http://support.microsoft.com/defaul...kb;en-us;306285
> Here is a Visual Basic sample:
> http://www.vbexplorer.com/VBExplore...file_dialog.asp
> I also think there is a method of the File System Object called
> GetFolderName
> http://msdn.microsoft.com/library/d...objectmodel.asp
>
> "Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
> message news:op.suv0e6u4n8ihmu@.iw2k.helpmicro.local...
>