Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"
|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing a package from within a C# program returns failure everytime

Hi All,

I am trying to execute a package (programmatically) that is stored on my local disk using the information that is provided on the following page:

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

This means, I am using the Application object and the Package object to actually load the package using the path to it.

So my code looks something like this:

--

pkgLocation = @."<package_path>/Package1.dtsx";

app = new Application();

pkg = app.LoadPackage(pkgLocation, null);

pkgResults = pkg.Execute();

Console.WriteLine(pkgResults.ToString());

Console.ReadKey();

My package reads in a flat file (located on another server) and transforms it and saves it to a database (on that same server). And mind you, I can execute this package just fine when I do it manually from within BIDS.

But when I try to execute the above mentioned code in my C# solution (compiled to a command line executable), I always get a "Failure".

Can somebody point out what I am doing wrong here?

Thank you in advance,

Manan Pancholi

You can use the following code to get the errors after execution

foreach (DtsError dtserr in pkg.Errors)
{
Console.WriteLine("Source: " + dtserr.Source + ", Description: " + dtserr.Description);
}|||

Thanks Kaarthik,

I believe I have narrowed down the problem. It seems that unless the Integration Services are installed on my machine, I would not be able to execute some tasks that are part of the package that I am calling programmatically. Atleast thats what the error information pointed out to me.

I believe that the authentication works differently when I am trying to execute the package manually and when I am trying to execute the package in my C# code. This is kind of odd, but thanks again for the help.

I will follow this up with a detailed example of how to execute a package programmatically and what sort of permissions would one need when working with remote sources and destinations.

Manan Pancholi

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 +ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 ='XXX'), 0)

OR

SELECT '' +ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 ='XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68sql

ExecuteScalar() Returns -1

The following query returns 0 when executing in Query Analyzer:

SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367

Yet ExecuteScalar() in vb.net return a -1.

Any ideas on what I might be doing wrong... ?Discovered my error...

The ExecuteScalar method deep in the plumbing of my DAL was actually calling an ExecuteNonQuery() function...

ExecuteScalar returns null

I am using the following C# code and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.

-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);

-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;

select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId

if @.@.rowcount <0
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

if @.@.rowcount <0
begin
print 'Path 3'
set @.pOutput = '2 reservations are already taken for the hours'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@.pBegin, @.pEnd, @.pUserId)
if @.@.error = 0
begin
print 'Path 4:1 @.@.error=' + cast(@.@.error as varchar(1))
print 'Path 4:1 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Reservation succeeded'
select @.pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Failed to make reservation'
select @.pOutput as 'Result'
return -1
end
end
end
ENDjs wrote:

Quote:

Originally Posted by

I am using the following C# code

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

But read on:

Quote:

Originally Posted by

and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
>
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
>
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
>
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
>
if @.@.rowcount <0

This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.

If you want to verify if records exist, use IF EXISTS, as in

IF EXISTS (select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:

declare @.cnt int
Set @.cnt= (select count(*) from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.

Quote:

Originally Posted by

begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that

Quote:

Originally Posted by

>
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

You do realize that because of the intervening statements, the @.@.rowcount
function returns a different value than was returned the first time you used
it ... ? @.@.error and @.@.rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.

Anyways, you already determined above that the records exist. Why bother
checking again?

Quote:

Originally Posted by

>
if @.@.rowcount <0

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

ExecuteNonQuery() not giving correct affected rows

When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @.@.RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance

-- With if/else ExecuteNonQuery returns -1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

-- Without if/else ExecuteNonQuery returns 1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN

INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]2 @.ObjectIdint,3 @.FanIdint,4 @.DateContributedDateTime,5 @.Notesnvarchar(512),6 @.ObjectTypeint7AS89BEGIN1011 BEGIN TRAN12 IF @.ObjectType = 213BEGIN14 INSERT INTO FighterContributions15 (FighterId, FanId, DateContributed, Notes)VALUES16 (@.ObjectId, @.FanId, @.DateContributed, @.Notes)17RETURN@.@.ROWCOUNT18END1920END212223

Try each statement like this

|||

RETURN @.@. ROWCOUNT on each statement won't work because it won't commit the transaction (it won't hit COMMIT TRAN).

|||

Perhaps you can add an OUTPUT Parameter to get the rows affected. Instead of this statement: IF @.@.ERROR <> 0, try:

SELECT @.rows = @.@.ROWCOUNT, @.Error = @.@.ERROr

IF @.ERROR <> 0

You will have to declare the @.Rows and @.Error variables. Add @.Rows to the parameters list as OUTPUT param. Check the value in @.rows from your front end.

|||

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

|||

I'm sorry guys. I think I messed up while testing the method because right now the stored procedure in question is now working. I also added RETURN SCOPE_IDENTITY() to get the ID of the new inserted record and it works. So having INSERT statements inside IF ELSE is not a problem for executenonquery.

So this is the final procedure

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS
BEGIN
BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

RETURN SCOPE_IDENTITY()

END

For those who are interested, here is how to get the value of the new id. Ignore CreateParameter() method. It is just an abstraction.

IDataParameter param = CreateParameter("ReturnValue", DbType.Int32);
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
connection.Open();
if (command.ExecuteNonQuery() > 0)
{
newObjectId = (int)((IDataParameter)command.Parameters["ReturnValue"]).Value;
}

|||

satya_tanwar:

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

Actually, I am going to add delete statements before the insert statements later. Anyways, thanks for helping out.

|||

thats good mark the post as answered and close the post

Execute stored procedure from stored procedure

Hello,

i need to execute a stored procedure from another stored procedure.

For example:

StoredProcedure1: Returns a table with columns "year" and "value".

StoredProcedure2: Also returns a table with columns "year" and "value".

In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them.

Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?

Kind regards and thanks,

carsaw

hi,

first off all let me clarify here - you cannot return table from a stored procedure you can use output variables but not the table.

what i would suggest you from your qeustion is - sp3 is main sp for you which needs data from sp1 & sp2 right

in that case let sp3 contain a temporary table #temptable with required columns

--parent proc

alterproc p1

as

Createtable #tempu

(

pidintidentity(1,1),

namesvarchar(50)

)

begin

insert #tempu(names)

exec tempinserter

select*from #tempu

end

--2nd proc

alterproc tempinserter

as

Createtable #temp2

(

pidintidentity(1,1),

namesvarchar(50)

)

insert #temp2

values('satish')

select namesfrom #temp2

return

--similarly create 3rd one then

exec p1 --the parent proc

hope it helps

also keep in mind to drop #temp tables at end of execution

regards,

satish.

|||

like i've called tempinserter in parent proc you can call other procs in the same manner.

regards,

satish

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it

Execute SQL Task - Assign the result to a variable

Hi,

Let's say that the query in my SQL Task returns a single integer number.

How can I put that single number in a variable?

Thank you.

You should read through Books On-Line for SSIS.

Here's another resource for the Execute SQL Task: http://www.sqlis.com/58.aspx

|||Thank you.

execute sql server agent job task - Job immediately returns success... However agent job is

when I run a package from a command window using dtexec, the job immediately says success.

DTExec: The package execution returned DTSER_SUCCESS (0).

Started:3:37:41 PM

Finished: 3:37:43 PM

Elapsed:2.719 seconds

However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.

Thanks,

Larry

Just to get this right. Your SSIS package starts a SQL Agent job. You are executing the package and it completes immediately. This is by design. There is no synchronous hook into the SQL Agent Job Scheduling, you can implement a waiting loop to check the job has completed but that is quite messy.

Execute SP if returns data

Hi all,
I keep getting this error message:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Else'.
I just want to execute a stored procedure if something is returned by
this: "Select hl7file from notes where FileWasCreated is null"
could be a large text file or nothing. If nothing then should do nothing.
I'm calling this from a SQLcommand object in VB.NET
Declare @.Cnt int
set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
If @.Cnt = 0
Else
BEGIN
Select hl7file from notes where FileWasCreated is null
EXECUTE InsertIncrementnumber
END
thanks
gvDo the opposite
If @.Cnt <> 0
BEGIN
..
END
"gv" <viatorg@.musc.edu> wrote in message
news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
> Hi all,
> I keep getting this error message:
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'Else'.
> I just want to execute a stored procedure if something is returned by
> this: "Select hl7file from notes where FileWasCreated is null"
> could be a large text file or nothing. If nothing then should do nothing.
> I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
> thanks
> gv
>|||thanks
gv
"news.microsoft.com" <zheka212@.hotmail.com> wrote in message
news:edjeJJGaFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Do the opposite
> If @.Cnt <> 0
> BEGIN
> ...
> END
> "gv" <viatorg@.musc.edu> wrote in message
> news:OxvPKGGaFHA.1148@.tk2msftngp13.phx.gbl...
>|||On Fri, 3 Jun 2005 13:27:36 -0400, gv wrote:

>Hi all,
>I keep getting this error message:
>Server: Msg 156, Level 15, State 1, Line 10
>Incorrect syntax near the keyword 'Else'.
>I just want to execute a stored procedure if something is returned by
>this: "Select hl7file from notes where FileWasCreated is null"
>could be a large text file or nothing. If nothing then should do nothing.
>I'm calling this from a SQLcommand object in VB.NET
>
> Declare @.Cnt int
> set @.Cnt = (Select count(*) from notes where FileWasCreated is null)
> If @.Cnt = 0
> Else
> BEGIN
> Select hl7file from notes where FileWasCreated is null
> EXECUTE InsertIncrementnumber
> END
>
>thanks
>gv
>
Hi gv,
Don't use COUNT(*) if you only want to know if there is none or at least
one - use EXISTS instead. (EXISTS will stop processing after finding a
row; COUNT will continue to count the other 300 million matching rows,
which is quite a shame if all you do is compare the result to 0).
IF EXISTS (SELECT * FROM notes WHERE FileWasCreated IS NULL
BEGIN
SELECT hl7file FROM notes WHERE FileWasCreated IS NULL
EXECUTE InsertIncrementnumber
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)