EXECuting SP within another SP

I am trying to call an SP within another SP, but the result being returned
from the inner SP is Null, whereas I should expect it to return a 1 or 0...
Here is the code:
ALTER Proc Returns_RecordPostClean
@.SerialNo int,
@.LineID int,
@.PostCleanDate smalldatetime,
@.PostCleanResult varchar(10),
@.PostCleanText varchar(50),
@.Exothermed tinyint,
@.Turned bit = 0
As
Set NoCount On
Begin Tran
Update Cleans
Set PostCleanDate = @.PostCleanDate,
PostCleanResult = @.PostCleanResult,
PostCleanText = @.PostCleanText,
Exothermed = @.Exothermed,
Turned = @.Turned
Where SerialNo = @.SerialNo
and LineID = @.LineID
Declare @.Status varchar(30)
Declare @.PFE bit
Declare @.Result int
Select @.PFE=O.PFE
From Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID
If @.PostCleanResult = 'Passed'
If @.PFE = 1
Set @.Status = 'PFE Credit Note'
Else
Begin
Set @.Status = 'Complete'
Exec @.Result=Common_UpdateOrderStatus @.LineID
<========= calling 2nd SP
End
Else
Set @.Status = 'Quarantined'
Update OrderDetail
Set Status = @.Status
Where LineID = @.LineID
If @.@.RowCount = 1 and @.Result = 1
Begin
Select @.Result as Success
Commit tran
End
Else
Begin
Select @.Result as Success
rollback tran
End
Set NoCount Off
If I call the 2nd SP outside this SP, it works fine. But for reasons I can't
be bothered going in to, it really needs to be call from within this SP.
This is actually the first time I've asked one SP to call another, so I'm
not sure if I'm missing something..
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Can you post the code of the second sp?
AMB
"CJM" wrote:

> I am trying to call an SP within another SP, but the result being returned
> from the inner SP is Null, whereas I should expect it to return a 1 or 0..
.
> Here is the code:
> ALTER Proc Returns_RecordPostClean
> @.SerialNo int,
> @.LineID int,
> @.PostCleanDate smalldatetime,
> @.PostCleanResult varchar(10),
> @.PostCleanText varchar(50),
> @.Exothermed tinyint,
> @.Turned bit = 0
> As
> Set NoCount On
> Begin Tran
> Update Cleans
> Set PostCleanDate = @.PostCleanDate,
> PostCleanResult = @.PostCleanResult,
> PostCleanText = @.PostCleanText,
> Exothermed = @.Exothermed,
> Turned = @.Turned
> Where SerialNo = @.SerialNo
> and LineID = @.LineID
> Declare @.Status varchar(30)
> Declare @.PFE bit
> Declare @.Result int
> Select @.PFE=O.PFE
> From Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID
> If @.PostCleanResult = 'Passed'
> If @.PFE = 1
> Set @.Status = 'PFE Credit Note'
> Else
> Begin
> Set @.Status = 'Complete'
> Exec @.Result=Common_UpdateOrderStatus @.LineID
> <========= calling 2nd SP
> End
> Else
> Set @.Status = 'Quarantined'
>
> Update OrderDetail
> Set Status = @.Status
> Where LineID = @.LineID
> If @.@.RowCount = 1 and @.Result = 1
> Begin
> Select @.Result as Success
> Commit tran
> End
> Else
> Begin
> Select @.Result as Success
> rollback tran
> End
> Set NoCount Off
>
> If I call the 2nd SP outside this SP, it works fine. But for reasons I can
't
> be bothered going in to, it really needs to be call from within this SP.
> This is actually the first time I've asked one SP to call another, so I'm
> not sure if I'm missing something..
> Thanks
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Sure, here it is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Proc Common_UpdateOrderStatus
@.LineID int
As
Declare @.Total int, @.Complete int, @.OrderID int
Select
@.OrderID=(Select O.OrderID
from Orders O
inner join OrderDetail D on D.OrderID = O.OrderID
where D.LineID = @.LineID),
@.Total=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID),
@.Complete=(Select Count(*)
from OrderDetail D
inner join Orders O on O.OrderID = D.OrderID
inner join OrderDetail D2 on D2.OrderID = D.OrderID
where D2.LineID = @.LineID
and D.Status = 'Complete' or D.Status = 'Cancelled')
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
Select 1 as Success
Else
Select 0 as Success
End
Else
Select 2 As Success
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
> Can you post the code of the second sp?
>
> AMB
>|||CJM wrote:
> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
You need to use a RETURN statement to return a... return value. Do not
use a result set as it will play havok with your application if not
managed carefully.
Instead of:
Select 0
Use:
Return 0
David Gugick
Imceda Software
www.imceda.com|||CJM,
A sp returns a value using the RETURN keyword, mainly to indicate success or
fail (0 - success, another int value diff from 0 to indicate fail).
If you want to return a value with a specific meaning, is better to use an
output parameter.
ALTER Proc Common_UpdateOrderStatus
@.LineID int,
@.op int output
As
...
if @.@.error <> 0 return 1
If @.Complete = @.Total
Begin
/* all lines complete or cancelled */
Update Orders
Set Status = 'Complete'
Where OrderID = @.OrderID
If @.@.RowCount = 1
set @.op = 1
Else
set @.op = 0
End
Else
set @.op = 2
return 0
go
declare @.i int
declare @.Result int
Exec @.Result=Common_UpdateOrderStatus @.LineID, @.i int output
print @.Result
print @.i
AMB
"CJM" wrote:

> Sure, here it is:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER Proc Common_UpdateOrderStatus
> @.LineID int
> As
> Declare @.Total int, @.Complete int, @.OrderID int
> Select
> @.OrderID=(Select O.OrderID
> from Orders O
> inner join OrderDetail D on D.OrderID = O.OrderID
> where D.LineID = @.LineID),
> @.Total=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID),
> @.Complete=(Select Count(*)
> from OrderDetail D
> inner join Orders O on O.OrderID = D.OrderID
> inner join OrderDetail D2 on D2.OrderID = D.OrderID
> where D2.LineID = @.LineID
> and D.Status = 'Complete' or D.Status = 'Cancelled')
> If @.Complete = @.Total
> Begin
> /* all lines complete or cancelled */
> Update Orders
> Set Status = 'Complete'
> Where OrderID = @.OrderID
> If @.@.RowCount = 1
> Select 1 as Success
> Else
> Select 0 as Success
> End
> Else
> Select 2 As Success
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5021F829-D752-4C84-B154-ACF772380731@.microsoft.com...
>

Executing SP From a Batch file

I was wondering how I can create a batch file that call a store procedure an
d
put the output on a file.Silver wrote:
> I was wondering how I can create a batch file that call a store
> procedure and put the output on a file.
Try osql.exe - the command-line options are documented in Books Online or by
typing osql /? at the command line.
John.|||Hi,
Call OSQL inside the command prompt
Entries inside the batch file will be
OSQL -Usa -Ppassword -S Servername -d Dbname -Qprocedurename -oc:\output.log
Thanks
Hari
SQL Server MVP
"Silver" <Silver@.discussions.microsoft.com> wrote in message
news:341FE12A-19E6-4A8B-8003-558797A637BF@.microsoft.com...
>I was wondering how I can create a batch file that call a store procedure
>and
> put the output on a file.

Executing Remote Stored Procedures in Triggers

Hi,

I've been scratching my head over this problem for quite a while. I have two SQL SERVER 2005 servers running on the same network, lets call them ServA and ServB. ServB is configured as a linked server on ServA as LinkedServB. On ServA, there is a database called DatabaseA, in which there is a table called TableAA, on which I wrote a trigger on delete.

In that trigger, I want to update a table, lets call it TableBB, in DatabaseB on ServB.

So the trigger looks like this:

CREATE TRIGGER triggerAfterDelete
ON TableAA
AFTER DELETE
AS
BEGIN

SET NOCOUNT ON;

Update [LinkedServB].[DatabaseB].[dbo].[TableBB]
set [SomeColumn] = 'SomeValue'
where [SomeOtherColumn] = 'SomeOtherValue'

END

When I delete something from TableAA, the trigger fires, and on trying to update, an error is raised which is:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Now the same query works as a separate query. But inside the trigger it does not. I've tried to use try-catches, nested transaction, named transaction, saving transactions, distributed tran, checking @.@.error, running the query using openquery, running the query using sp_executesql, but they have all given me some error or other.

Selects in the trigger work fine, but updates, inserts and deletes do not work.
And like I mentioned, as a stand alone query, they work fine, as a query, in openquery, or in sp_executesql

Any help would be much appreciated.

Thanks in Advance

Vinit Pandya
Senior .NET Developer

You need to SET XACT_ABORT ON to avoid the error. Otherwise the provider has to support nested transactions for this to work and SQL Server does not support nested transactions. For details on the behavior of distributed queries in transactions, see the BOL topic below:

http://msdn2.microsoft.com/en-us/library/aa213080(SQL.80).aspx

|||Hi Umachandar,

Thanks for your reply. I've actually tried that too, and also other settings which I read helped other people such as:

SET XACT_ABORT ON;
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

But still no luck. Any other ideas ?

Thanks.

Vinit Pandya
Senior .NET Developer

Executing Package From Web Service

Hi,

I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.

If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.

Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.

Many thanks in advance,

Grant

Are any exceptions being thrown within the WS code?

Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?

Do you get any error events?

You really need to have decent error handling and make good use of the events from those methods to find out what is going on.

Without an error message we are in the dark out here.

|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.

I suggest you to verify the permissions given to this user into the DB tables and also read this articles:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

regards|||Hi Darren,

You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.

The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".

If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.

In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.

I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.

Thanks,

Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.

What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?

Cheers,

Grant|||

Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)

Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)

I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.

Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.

|||hi Darren,

Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.

It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?

Many thanks for your help,

Grant

Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!

Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!
sql

Executing an application from a query

Hi All,
Is it possible to execute a file or perhaps send a call to a
webpage using an SQL query? Does anyone know of any such feature.
Basically I need to call a webpage(coldfusion script) from my query and
I am wondering if anyone knows of any way that his would be possible.
Thanks a heap in advance for all your help !
Harkirat>> Is it possible to execute a file or perhaps send a call to a webpage
Can you be more specific? SQL Queries are executed on the server while web
pages are displayed on the client. So what exactly do you mean by "calling"
a webpage? Are you trying to display the webpage on the client or somewhere
else? SQL Server has some provisions for generating web pages based on
generated resultsets, but I am not sure that is what you are looking for.
Anith|||Hi Anith,
What I need to do is be able to execute a coldfusion script
via a query. The script can be run via a webpage e.g.
http://mysite.com/CFScript.cfm
So if I could make a 'call' to my webpage via my query that would work.
Also if this is not a possibility would it be possible to execute a
.exe file using a query? That might help too.
Thanks for your reply.
Harkirat
Anith Sen wrote:
> Can you be more specific? SQL Queries are executed on the server while web
> pages are displayed on the client. So what exactly do you mean by "calling
"
> a webpage? Are you trying to display the webpage on the client or somewher
e
> else? SQL Server has some provisions for generating web pages based on
> generated resultsets, but I am not sure that is what you are looking for.
> --
> Anith|||You can use a stored procedure to execute an .exe file using
xp_cmdshell, but there are security issues. I don't think that it;s
the best approach to what you are trying to do, however; what does a
cold fusion script have to do with your data?
Can you provide a little more detail about the business problem you're
trying to solve, and perhaps we can suggest a better alternative.
Stu|||Hi Stu,
My coldfusion script has logic that
updates a table in the database. I need this process to run everytime a
new row is inserted in my table. Hence I was thinking of putting an
insert trigger that calls this coldfusion script.
Can you give an example on how to execute a .exe using a stored
procedure. I could perhaps make a .exe that executes the coldfusion
script which in turn would be called via my trigger.
Thanks.
Harkirat|||Hi
Look at xp_cmdshell in BOL.
If your exe fails, the insert will get rolled back as an error like that can
not be handled in T-SQL. It is not a good idea. Rather write a row in a
queue table, and have something poll the table and then call your exe.
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/
"harry" <i1073@.tamu.edu> wrote in message
news:1124657414.378047.144430@.g44g2000cwa.googlegroups.com...
> Hi Stu,
> My coldfusion script has logic that
> updates a table in the database. I need this process to run everytime a
> new row is inserted in my table. Hence I was thinking of putting an
> insert trigger that calls this coldfusion script.
> Can you give an example on how to execute a .exe using a stored
> procedure. I could perhaps make a .exe that executes the coldfusion
> script which in turn would be called via my trigger.
> Thanks.
> Harkirat
>|||I think your application would perform better if you could keep the
database logic at the dataase level; if it were me, I would probably
simply run the logic in T-SQL as part of the trigger. Why bubble back
up (unless of course your coldfusion script is extremely complicated)?
As for an example, I don't write many triggers, and I disable
xp_cmdshell altogether. Books OnLine is your best bet.
Stu|||Hi Stu,
My coldfusion is indeed complicated thats why I don't wish
to do it in SQL.
Thanks for your help.
Harkirat|||Hi Mike,
I had already thought of that. Seems like my best bet
now.
Thanks for your help.
Harkirat

ExecuteNonQuery hangs in Timer event notification

If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?

.B ekiM

class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");

static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);

System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;

// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);

Console.ReadLine();
}

private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);

SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @.When, @.AvailPhys);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @.When, @.AvailPageFile);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @.When, @.AvailVirtual);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @.When, @.AvailExtendedVirtual);\n");

DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);

cmd.ExecuteNonQuery();

Console.WriteLine("Inserted {0}", dt);
}

}

A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||
The problem is that the runtime catches an exception. It shouldn't: it hasn't published a contract saying it will catch exceptions. It also offers very little indication that it did catch the exception.

These problems are certainly not solved.

ExecuteNonQuery hangs in Timer event notification

If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?

.B ekiM

class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");

static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);

System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;

// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);

Console.ReadLine();
}

private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);

SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @.When, @.AvailPhys);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @.When, @.AvailPageFile);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @.When, @.AvailVirtual);\n" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @.When, @.AvailExtendedVirtual);\n");

DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);

cmd.ExecuteNonQuery();

Console.WriteLine("Inserted {0}", dt);
}

}

A-hah! It's not hanging; it's just throwing an exception that the runtime itself catches, then doesn't report.|||Is the problem solved then, or you you want to elaborate on the error message ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||
The problem is that the runtime catches an exception. It shouldn't: it hasn't published a contract saying it will catch exceptions. It also offers very little indication that it did catch the exception.

These problems are certainly not solved.

executeBatch fails on stored proc call

I have a stored procedure that is only supposed to insert if the record
does not already exist.
ALTER PROCEDURE [CARTS].[Insert_Store_Item_Price_Data]
@.Store_Item_Price_Change_ID varchar(50),
@.Store_ID char(4),
@.Item_ID char(14),
@.Batch_Number_ID varchar(6),
@.Effective_Start_Date datetime,
@.Price_AMT decimal(8,2),
@.Promotion_Code smallint,
@.State_Name varchar(10),
@.Record_Creation_Timestamp datetime
AS BEGIN
-- insert a record if no duplicate record is found
DECLARE @.ItemCode char(14)
SELECT @.ItemCode=Item_ID
FROM [CARTS].[Store_Item_Price] WITH (NOLOCK)
WHERE Store_ID=@.Store_ID AND
Item_ID=@.Item_ID AND
Batch_Number_ID = @.Batch_Number_ID AND
Price_AMT = @.Price_AMT AND
Promotion_Code = @.Promotion_Code AND
State_NAME = @.State_NAME
IF(@.ItemCode IS NULL)
BEGIN
INSERT INTO [CARTS].[Store_Item_Price]
([Store_Item_Price_Change_ID]
,[Store_ID]
,[Item_ID]
,[Batch_Number_ID]
,[Effective_Start_Date]
,[Price_AMT]
,[Promotion_Code]
,[State_Name]
,[Record_Creation_Timestamp])
VALUES
(@.Store_Item_Price_Change_ID,
@.Store_ID,
@.Item_ID,
@.Batch_Number_ID,
@.Effective_Start_Date,
@.Price_AMT,
@.Promotion_Code,
@.State_Name,
@.Record_Creation_Timestamp);
END
END
Is there an ELSE statement I can add that will return a zero for rows
effected? That way executeBatch will work. Right now, it returns the
following exception:
java.sql.BatchUpdateException: The returned update count was -1. Either
a procedure returned a result set or not every procedure returned an
update count. The driver expects 11 update counts to be returned from
this batch.StevenMartin (stevenmartin@.us.ibm.com) writes:
> I have a stored procedure that is only supposed to insert if the record
> does not already exist.
>...

> Is there an ELSE statement I can add that will return a zero for rows
> effected? That way executeBatch will work. Right now, it returns the
> following exception:
Write the query as:
INSERT tbl (...)
SELECT ....
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE ...
Note that there is not any FROM clause in the outer SELECT.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute Stored Procedure Y asynchronously from Stored Proc X using SQL Server 2000

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

When you say that you want to return from the SQL server execution.Really all of the Execution has to be completed before that Can Happen.When I say all of the Execution I mean both the X and Y[ that is Y and X internally called by Y].Am not sure that why you want the code to be executed this way but try out following list of possible case that can handle the requirement you have.

1. Stored Procedure X

Statement 1

Statement 2

Call to Y

Statement 4

Statement 5

Split the above code to three ... Statement 1 and 2 be in a single procedure . CAll it , once after execution return ...

from the code , again from the call the SP Y async.

sql API now support Async Calls ..hope your are using that ... BeginXXX and EndXXX pairs

|||

My requirement is little bit different man. I want to do lot of work in backgroud.

when i call a stored proc X then it should execute few lines of code and call Stored Proc Y in background. Once stored proc X call stored proc Y then stored proc X should return me result set (i mean execution of X is done) without waiting to get it complete stored Proc Y because it is running in background.

how can we do it? pls suggest me some solution.

You can suggest me solution either in ASP.NET 2.0 OR Sql Server 2000.

|||

Not very sure, but some ppl said it can be down using OLE automation . Could you please check this article?

http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2

Changing Stored Procedure to Submit Code Asynchronously

Now that you understand the initial performance problem with SP "usp_enter_order," let me discuss how I could re-write this SP to submit the slow code asynchronously. First, I will need to create a "new" SP that contains the slow code. The second thing will be to replace the slow code in "usp_enter_order" with some OLE Automation that submits the "new" SP asynchronously. Below is the code for the new SP, I called it "usp_run_slow_code":

Hope my suggestion can help

Execute stored proc on a named instance

I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:

EXEC Server\Instance.DB.dbo.usp_fm_proc 1

It seems it doesn't like the \ as I get an error "Incorrect syntax near 'Instance'.

What am I missing here?

Have you created linekd server, try the following:

EXEC [Server\Instance].DB.dbo.usp_fm_proc 1

|||Dooh! That's it, I was overlooking the []. Thanks!

Execute SQL Task speed

I've created a SSIS package, in a sql 2005 instance, that uses an Execute SQL Task" to call a stored proc as its last step. When run from BIDS, the last step takes about 2 to 3 minutes, consistently. When I run the exact same query from Management Studio (either via exec <spname> or by copying the sp's t-sql code into a query window) it consistently takes about 1 minute. I've run sevral test and these number are quite reproducible.

Any ideas to account for the "slowness" of the Execute SQL Task?

TIA,

Barkingdog

Hi, are you running your package in debug mode?

Try run the package without Visual Studio.

John Bocachica - Colombia

www.iquos-bi.com

|||

The dropdown box at the top of BIDs says "Development"

Here is what I have found. My package runs three control tasks. When I run all three, the Exec SQL tasks takes about 2 minutes to run but when I execute ONLY the Exec SQL task that task runs in about 1 minute!

I saved the package (in BIDS) to a .dtsx file and ran it. The whole process took about 2.5 miniutes which tells me that the Exec SQL task still took about 2 minutes.

barkingdog

|||

What are the other tasks doing? Do they use the same database connection? Are there transactions involved?

|||

The first task truncates a table called Contact. The second task imports a CSV file into a table (uses a SQL Server Destination. Does a straight copy of the data; no transformations. The file imported is on the sql 2005 server and database I'm importing into). The third task (Exec SQL Task) applies various UPDATE statements to the table populated in step 2. Steps 2 and 3 use the same sql connection.

II don't know how to tell if all the tasks belong to the same transaction. I set up three control flows in the same pane but they are not contained in any container object, if that helps at all.)

TIA,

Barkingdog

Execute Process Task; How to use user variable as an argument

I am trying to call a executable that takes an argument. I am using an "execute process task" and have declared a user string variable "file_name" (c:\file.txt)

How do I use this variable so that the executable will see it as an argument.

You can use expressions to define the path and parameters of you executable... Then you don't pass the variable but construct the "command line" with an expression including the parameter (which is the value of your variable)...

Execute Package Task

I am using many Execute Package Tasks in a DTS packages to call a number of other DTS packages. The problem is that when I change one of the called DTS packages the execute package task does not run the updated packages. It looks like the execute package task references a Package ID guid instead of the name. Is there a way to make it so a change to a called DTS package will reflect in all references to it

Hi James,

I'd like to know whether you're using the DTS in SQL Server 2000 or SSIS in SQL Server 2005.

If the DTS package is on the same machine as it is running, it has to be fine. The execute package task references the package according to its name.

If you're working on SSIS in SQL Server 2005, please confirm if you have uploaded it to the server. As when you have modified it, it still remains on the client side.

|||

Kevin, thanks for the response. I am using DTS and the package is on the same server. Though it is definatley referencing it by PackageID. If I use the DTSRun utility I can reference it by name. However, when use execute package inside another package it references it by PackageID

Execute DTS Package from Asp.net

Hi All,
How we can call and Execute DTS Scripts From Asp.net.
Can any one pls give me some help.
Regards
VAsu

There are many explanations of this on the Web. A simple Google search yielded these.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://www.sqlteam.com/item.asp?ItemID=19595