Executing BCP from C# code

Aloha to all,
I have thrown together some code that generates a file with a lot of
data (something like 200,000 rows). I would like to load all this data
in a SQL Server table, but my attempt to execute BCP from C# doesn't
want to behave.
I do as follows (more of less stealing everything from
http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
-Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
for user 'username'."
I have off course checked the username/password a thousand times and it
is correct. If I copy my Arguments string to the command prompt, I can
execute BCP without any problem
Thank you and roger over, MadsYou could try creating a batch file and then run that. It seems like it's
having trouble putting the arguments in correctly. I don't have the time
right now to try compiling and running your code... but I'm sure you could
easily throw the whole line into a file and running it.
Security-wise though, I'd consider using a trusted connection and
impersonating a particular user.
Unfortunately my timezone means I'm about to go offline for a while, so I
can't post a follow-up. Someone else will though I'm sure (and they'll
probably tell you to ignore me!)
Rob
"Mads.phi@.gmail.com" wrote:

> Aloha to all,
> I have thrown together some code that generates a file with a lot of
> data (something like 200,000 rows). I would like to load all this data
> in a SQL Server table, but my attempt to execute BCP from C# doesn't
> want to behave.
> I do as follows (more of less stealing everything from
> http://dotnetjunkies.com/WebLog/ste...8/19/22566.aspx
> ):
> System.Diagnostics.Process proc = new System.Diagnostics.Process();
> proc.EnableRaisingEvents = false;
> proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
> proc.StartInfo.UseShellExecute = false;
> proc.StartInfo.RedirectStandardOutput = true;
> proc.StartInfo.RedirectStandardError = true;
> proc.StartInfo.FileName = "bcp";
> proc.StartInfo.Arguments = @."DATABASE_NAME.dbo.TABLE_NAME in
> c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t','
> -Sservername";
> proc.EnableRaisingEvents = true;
> proc.Start();
> string s1 = proc.StandardOutput.ReadLine();
> string s2 = proc.StandardOutput.ReadLine();
> All I get from this code is:
> s1 = "SQLState = 28000, NativeError = 18456";
> s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed
> for user 'username'."
> I have off course checked the username/password a thousand times and it
> is correct. If I copy my Arguments string to the command prompt, I can
> execute BCP without any problem
> Thank you and roger over, Mads
>|||Excellent! That fixed my problems, so the only thing left is that I'm
ashamed of not having thought of a .bat file myself.
Thank you, Mads|||About the C# code, in general I'd change
proc.StandardOutput.ReadLine();
to
proc.StandardOutput.ReadToEnd();
to grab all the error message for troubleshooting purposes. Of course, this
doesn't address your particular problem.
Linchi
"Mads.phi@.gmail.com" wrote:

> Excellent! That fixed my problems, so the only thing left is that I'm
> ashamed of not having thought of a .bat file myself.
> Thank you, Mads
>

Executing a View timesout through Open View

Hello All,

I have created a view that will potentially return a huge number of records around 500,000 rows. When i execute this view by clicking Open View from the SQL Management studio i get the following error

SQL Execution error

Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

However the query executes when paste the generated query of the view in the query designer window. It returns the 500,000 rows. I am wondering if there is some option that i will have to configure to return a large size result set in view designer. I also want to know if this is an inherent problem with view designer.

Any help regarding this will be really appreciated.

Satya

Open View creates a fully editable grid of the rows returned by the view so it is not surprising that it is choking on that much data.

First - this is not the view designer, the view designer opens when you select Modify, and that will let you edit the view definition.

If you do want to work with the data in the view through Open View, then after you select Open View, click the stop button down at the bottom of the window, next to the message that says Retrieving Data... It will stop and show whatever data it retrieved before you clicked the button. Now click the Show SQL Pane button on the toolbar (or Query Designer/Pane menu). In the SQL pane, edit the query by adding a WHERE clause to the SELECT statement that is selecting the data from you view. Now click the Execute SQL button (the exclamation mark) and it will run and return only the rows that match. Now you can selectively pull only a portion of the 500,000 rows at a time and work on them, then change the WHERE clause to select other rows. I can't believe you really want all 500,000 rows on screen at the time - and SQL Server doesn't think it's a good idea either :-)

|||

Hi,

Thanks for your reply. I didnt intend to see all the 500,000 rows of data at a time but did want to know the maximum volume that i can see when i click Open View. The stop button is also disabled when i click Open view and i get this error directly and so i cannot see a partial set of the data the way you had specified. Also i wrote <select a top 1 * > query enveloping the query generated by the view so that i could get atleast the first tuple of the query result but even this was aborted by the same error. The error comes immediately after a minute and no option is enabled for me to do anything once i click Open View or execute.

I was trying to see if this was a configuration issue in VIEW or if i was missing something else. The same query does give the intended results when i run in the query designer. Is there a capacity constraint for the Results grid pane in the View Designer as compared to normal query window.

Thanks again for your help and would appreciate if you can clarify the above too

satya

|||Do you by any chance have a TOP and an ORDER BY in the view definition, or is the view just a standard SELECT?|||It just seems that the CommandTimeout property is not updated in Management Studio when using View, and that it is always stuck at 30 sec. I have the same problem, but it seems impossible to fix.|||I have a view that returns over 500,000 rows, it takes about 5 minutes and never times out. I can click the Stop button at any time. I haven't changed anything from default so I'm not sure what's different.sql

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

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

ExecuteNonQuery to count number of rows?

My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.

Does this also work when calling stored procedures and passing parameters?

I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.

What should I do?

Dim myCommandAs New SqlClient.SqlCommandmyCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@.selectDate", dateEntered)myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))myCommand.Connection = concon.Open()Dim rowCountAs Integer = myCommand.ExecuteNonQuery()numberParts.Text = rowCount.ToStringcon.Close()

Thank you.

Check yourap_Select_ModelRequests_RequestDateTimestored procedure, and if you find:

SET NOCOUNT OFF

useually at the end of SP, remove it and I hope this will work.

Good luck.

|||

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

|||

You can use @.@.ROWCOUNT in your stored procedure and assign it to an output parameter.

|||

Peter Lee:

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

Hi Pete.

What you wrote is correct, but I do not think it will work if the stored procedure has a SET NOCOUNT OFF?

Thanks.

|||

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

|||

Peter Lee:

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

You are right.

Thanks Pete.

ExecuteNonQuery not returning rows affected

I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...

SQL Server 2005 likes to put theSET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.

So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.

Thanks for your help...

But can u tell me what is the use of SET NOCOUNT ON ?

|||You use it to suppress the counts of records affected in your stored procedure. There must also always be a resultset that is returned for each such message. By turning nocount on, then the records affected counts aren't transferred to the client, and empty resulsets are discarded.

Execute SQL Task with no rows affected

Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

What error or errors are returned when the task fails? Can you please copy and paste the error output from BIDS or from a log file into a reply?

Are you attempting to store the resultset from the query in a variable?

Can you run the query successfully (where zero rows are affected) without error using SQL*Plus or another query tool?

This information will likely be invaluable in helping track down the source of the error.

|||

Thank you for your reply.

This is the error:

"Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "update TableName set FieldA=SYSDATE where FieldB is Null" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task."

No, I didn't attempt to store the resultset, I just executed the query.

and yes, I ran the query successfully using SQL*Plus with the result : "0 rows updated."

Please your help.

Thanks

Noam

|||

I found it.

My connection was odbc.

I changed the connection to Ado.Net provider for odbc and it works without fail.

Why?

It's another question.

Thank you anyway.

Noam

Execute SQL based on number rows in two tables

I have a table that I would like to backup each morning only if the number o
f
records in this table is greater than in another table. How can achieve
this? Basically, I would like it to be like this:
IF (rowcount in A) > (rowcount in B) then
Execute SQL
ELSE --NOTHING
END IF"examnotes" <Pasha@.discussions.microsoft.com> wrote in
news:DA8B4552-A94A-4974-9C4A-9A639CC7C1EC@.microsoft.com:

> I have a table that I would like to backup each morning only if the
> number of records in this table is greater than in another table. How
> can achieve this? Basically, I would like it to be like this:
> IF (rowcount in A) > (rowcount in B) then
> Execute SQL
> ELSE --NOTHING
> END IF
Something like this?
if ((select count(*) from sys.tables) > (select count(*) from sys.views))
begin
print 'More tables than views'
end
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Below is what I was able to create. Not sure if it is the best solution
though:
if ((select rows FROM sysindexes WHERE id = OBJECT_ID('Daily_Fin_Apps') AND
indid < 2) > (select rows FROM sysindexes WHERE id =
OBJECT_ID('BACKUP_Daily_Fin_Apps') AND indid < 2))
begin
truncate table [BACKUP_Daily_Fin_Apps]
insert into [BACKUP_Daily_Fin_Apps]
select * from [Daily_Fin_Apps]
end
Thanks,
Pasha
"Ole Kristian Bang?s" wrote:

> "examnotes" <Pasha@.discussions.microsoft.com> wrote in
> news:DA8B4552-A94A-4974-9C4A-9A639CC7C1EC@.microsoft.com:
>
> Something like this?
> if ((select count(*) from sys.tables) > (select count(*) from sys.views))
> begin
> print 'More tables than views'
> end
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>

Execute report and go directly to a save file option

We have a report that is basically a data file. It can have 2 rows or it can
have 100,000 rows. We are running into response time issues, memory issues,
etc. I know that RS is not the preferred method to deliver these sorts of
large data files. We need to do anything possible to satify the user at this
point and the amount of time it's currently taking to run this report with a
large amount of data, even when the report runs successfully, is unacceptable
to them. But, until we have time to re-design, is there any way to have the
report always prompt to save the file instead of waiting for the default HTML
format and then exporting to CSV? Any other short-term solutions?
StephanieFrom your own web page it is easy. Using URL integration you can specify the
format. Even if you are using Report Manager you could have a report that
has the appropriate parameters, they open it up and all you have in the
report is a link that says Export Data. Use the Jump to URL action (I also
make the link blue and underlined).
One other thing, perhaps you have seen this already.If you do this you will
want to change the CSV to use ASCII instead of unicode. Excel puts unicode
into a single column. To have RS export CSV in ASCII format you have to make
a change to a config file.
You only need to change in one place, rsreportserver.config. Reboot after
the change. The below shows commenting out the existing entry and putting in
the needed change to have CSV export as ASCII
<!--
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
-->
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:10819562-B6BC-4535-BE4A-4B5AA6BD2B8A@.microsoft.com...
> We have a report that is basically a data file. It can have 2 rows or it
> can
> have 100,000 rows. We are running into response time issues, memory
> issues,
> etc. I know that RS is not the preferred method to deliver these sorts of
> large data files. We need to do anything possible to satify the user at
> this
> point and the amount of time it's currently taking to run this report with
> a
> large amount of data, even when the report runs successfully, is
> unacceptable
> to them. But, until we have time to re-design, is there any way to have
> the
> report always prompt to save the file instead of waiting for the default
> HTML
> format and then exporting to CSV? Any other short-term solutions?
> Stephanie

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can
you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...[vbcol
=seagreen]
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>[/vbcol]|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it
interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I d
on't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand wh
at you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...en">
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
Jack
We need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>
|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I don't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand what you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>