Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub

If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.

When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.

Can someone suggest how I can get the value 0-6 to my code?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

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 trigger as a specific user

Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only symin
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...

Execute properly even if no parameter value is supplies

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

First 2 exec statement gives many data row as result,

But why the last donot give any row ;( ;(

how can i rewrite the stored procedure, such that it gives out put even if i don't supply ManufactureID as input\

kindly help me

Hi There

You miss out 'OR CASE' for 'ItemsInStock'

sujithukvl@.gmail.com:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================

-- =============================================
ALTER PROCEDURE [dbo].[Product_FindByParameters]
(
@.Name Varchar(255),
@.ManufactureID bigint,
@.ShortDescription Varchar(255),
@.ManufactureProductID Varchar(255),
@.ItemsInStock bigint,
@.StorePartNumber Varchar(255)

)


AS
BEGIN

SELECT P.ProductId,
P.StorePartNumber,
P.ShortDescription,
P.ManufactureProductID,
P.Name,
P.Price,
P.ItemsInStock,
M.ManufactureName
FROM Product P left join Manufacture M
ON P.ManufactureID=M.ManufactureID
WHERE
( P.Name like '%' + @.Name + '%' OR @.Name is null)
AND (P.ShortDescription LIKE '%' + @.ShortDescription + '%' OR @.ShortDescription is null)
AND( P.ManufactureProductID LIKE '%' + @.ManufactureProductID + '%' OR @.ManufactureProductID is null)
AND (P.ItemsInStock=@.ItemsInStock OR@.ItemsInStock is null)
AND (P.ManufactureID = @.ManufactureID OR @.ManufactureID is null)
END

--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''
--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''
--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,''

|||

In your input parameter, give the default NULL value to ManufactureID. With that, you can do OR @.ManufactureID = NULL so that you can ignore it when the value doesn't provide.

@.ManufactureID bigint = NULL,