problem o fc# accessing stored procedure at sql server 2k

[Mullin Yu]

i have a stored procedure at sql server 2k. which will update records and
select result from temp table.

if i use SqlConnection class, and i do both. but, if i use OleDbConnection
class, i can have only records updated, but no result come out.

how can i debug, and what's the error?

thanks!

[Mullin Yu]

it's my program.

really strange!!

Ole -> 0

Sql -> 1

Do I need to set something else?

thanks!

private void button1_Click(object sender, System.EventArgs e)

{

try

{

//string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=MULLIN-YU;Use Encryption for Data=False;Tag with
column collation when possible=False;database=CCBSAPP";

string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;database=CCBSAPP";

OleDbConnection conn = new OleDbConnection(dbstring);
OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "GetJobItems";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(oleCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

OleDbParameter paramMachine = new OleDbParameter("@Machine",
OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0,
"MachineLocked", DataRowVersion.Current, valMachine);

OleDbParameter paramOutboundType = new OleDbParameter("@OutboundType",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

oleCommand.Parameters.Add(paramMachine);

oleCommand.Parameters.Add(paramOutboundType);
oleDataAdapter.Fill(results);

MessageBox.Show("count: " + results.Rows.Count);
}

catch(OleDbException oleex)

{

Console.WriteLine(oleex.Message);

}

catch(Exception ex)

{

throw ex;

}

}

private void button2_Click(object sender, System.EventArgs e)

{

string dbstring = "User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;Workstation
ID=MULLIN-YU;database=CCBSAPP";

SqlConnection conn = new SqlConnection(dbstring);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = "GetJobItems";

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

SqlParameter paramMachine = new SqlParameter("@Machine", SqlDbType.VarChar,
20, ParameterDirection.Input, false, 0, 0, "MachineLocked",
DataRowVersion.Current, valMachine);

SqlParameter paramOutboundType = new SqlParameter("@OutboundType",
SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

sqlCommand.Parameters.Add(paramMachine);

sqlCommand.Parameters.Add(paramOutboundType);
//sqlCommand.Connection.Open();

sqlDataAdapter.Fill(results);

conn.Close();

conn.Dispose();
MessageBox.Show("count: " + results.Rows.Count);

}

"Mullin Yu" <mu*******@ctil.com> wrote in message
news:uF**************@TK2MSFTNGP09.phx.gbl...

i have a stored procedure at sql server 2k. which will update records and
select result from temp table.

if i use SqlConnection class, and i do both. but, if i use OleDbConnection
class, i can have only records updated, but no result come out.

how can i debug, and what's the error?

thanks!

[Chris Torgerson]

It may be in your Stored Procedure code. Please note that I haven't tested
this theory , but just something to think about. Have you set Nocount on in
the SP. I don't know if it effects the SQLClient, but having the record
counts on in Classic ADO would cause problems in the resultsets returned. I
assume the OLEDB classes would probably have the same problems. Just a
thought.

HTH
Chris Torgerson
"Mullin Yu" <mu*******@ctil.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

it's my program.

really strange!!

Ole -> 0

Sql -> 1

Do I need to set something else?

thanks!

private void button1_Click(object sender, System.EventArgs e)

{

try

{

//string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=MULLIN-YU;Use Encryption for Data=False;Tag with
column collation when possible=False;database=CCBSAPP";

string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;database=CCBSAPP";

OleDbConnection conn = new OleDbConnection(dbstring);
OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "GetJobItems";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(oleCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

OleDbParameter paramMachine = new OleDbParameter("@Machine",
OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0,
"MachineLocked", DataRowVersion.Current, valMachine);

OleDbParameter paramOutboundType = new OleDbParameter("@OutboundType",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0, "OutboundType", DataRowVersion.Current, valOutboundType);

oleCommand.Parameters.Add(paramMachine);

oleCommand.Parameters.Add(paramOutboundType);
oleDataAdapter.Fill(results);

MessageBox.Show("count: " + results.Rows.Count);
}

catch(OleDbException oleex)

{

Console.WriteLine(oleex.Message);

}

catch(Exception ex)

{

throw ex;

}

}

private void button2_Click(object sender, System.EventArgs e)

{

string dbstring = "User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;Workstation
ID=MULLIN-YU;database=CCBSAPP";

SqlConnection conn = new SqlConnection(dbstring);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = "GetJobItems";

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

SqlParameter paramMachine = new SqlParameter("@Machine", SqlDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0, "MachineLocked",
DataRowVersion.Current, valMachine);

SqlParameter paramOutboundType = new SqlParameter("@OutboundType",
SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

sqlCommand.Parameters.Add(paramMachine);

sqlCommand.Parameters.Add(paramOutboundType);
//sqlCommand.Connection.Open();

sqlDataAdapter.Fill(results);

conn.Close();

conn.Dispose();
MessageBox.Show("count: " + results.Rows.Count);

}

"Mullin Yu" <mu*******@ctil.com> wrote in message
news:uF**************@TK2MSFTNGP09.phx.gbl...

i have a stored procedure at sql server 2k. which will update records and select result from temp table.

if i use SqlConnection class, and i do both. but, if i use OleDbConnection class, i can have only records updated, but no result come out.

how can i debug, and what's the error?

thanks!

[Mullin Yu]

I've set it, but still error.

I tested with a simple query at the sp like
select * from Table1. I got resultset from both OleDbConnection and
SqlConnection.

It means there's problem when running my sp with OleDbConnection.

Here's my sp:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC GetJobItems @Machine as varchar(20), @OutboundType as int as
declare @JobID as bigint
declare @JobItemID as bigint
BEGIN

-- Select one JobItem from OutboundQueue
select top 1 @JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @OutboundType order by SubmissionDate
asc

--
************************************************** ***********
-- select JobItems within that JobID just got
-- Using Cursor
-- ************************************************** ***********

-- Declare the variables to store the values returned by FETCH.
DECLARE @tmpJobItemID as bigint

-- Declare the variable to store the concat value
DECLARE @tmpString as varchar(200)

-- Initialize
SET @tmpString = ''

DECLARE SampleCrsr CURSOR FOR
SELECT JobItemID FROM OutboundQueueItem where JobID = @JobID and IsLocked
= 0 and AddToProcessing = 1 and OutboundType = @OutboundType
-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)

OPEN SampleCrsr

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--SET @tmpString = @tmpString + Convert(varchar, @tmpJobItemID) + ', '
INSERT INTO #tmpJobItemID (tJobItemID) Values (@tmpJobItemID)

-- Concatenate and display the current values in the variables.
--PRINT 'JobItemID: ' + Convert(varchar, @tmpJobItemID)

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID
END

--PRINT 'tmpString: ' + @tmpString + '1'

CLOSE SampleCrsr
DEALLOCATE SampleCrsr

-- ************************************************** *************
-- End Of using cursor
-- ************************************************** *************

-- Lock the Job at OutboundQueue, and update the status to "Working"
-- update OutboundQueueItem set isLocked = 1, MachineLocked = 'Mullin-Yu',
Status = 2 where JobItemID = @JobItemID
update OutboundQueueItem set isLocked = 1, MachineLocked = @Machine,
Status = 2 where JobItemID in (select tJobItemID from #tmpJobItemID)

-- return recordset
-- select * from OutboundQueueItem where JobItemID =
@JobItemID
select * from OutboundQueueItem where JobItemID in (select tJobItemID from
#tmpJobItemID)

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


"Chris Torgerson" <ct********@nospam.mindspring.com> wrote in message
news:ez**************@tk2msftngp13.phx.gbl...

It may be in your Stored Procedure code. Please note that I haven't tested this theory , but just something to think about. Have you set Nocount on in the SP. I don't know if it effects the SQLClient, but having the record
counts on in Classic ADO would cause problems in the resultsets returned. I assume the OLEDB classes would probably have the same problems. Just a
thought.

HTH
Chris Torgerson
"Mullin Yu" <mu*******@ctil.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

it's my program.

really strange!!

Ole -> 0

Sql -> 1

Do I need to set something else?

thanks!

private void button1_Click(object sender, System.EventArgs e)

{

try

{

//string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data

Source=CCBS-EDMSDB4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MULLIN-YU;Use Encryption for Data=False;Tag with column collation when possible=False;database=CCBSAPP";

string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;database=CCBSAPP";

OleDbConnection conn = new OleDbConnection(dbstring);
OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "GetJobItems";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(oleCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

OleDbParameter paramMachine = new OleDbParameter("@Machine",
OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0,
"MachineLocked", DataRowVersion.Current, valMachine);

OleDbParameter paramOutboundType = new OleDbParameter("@OutboundType",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0,

"OutboundType",

DataRowVersion.Current, valOutboundType);

oleCommand.Parameters.Add(paramMachine);

oleCommand.Parameters.Add(paramOutboundType);
oleDataAdapter.Fill(results);

MessageBox.Show("count: " + results.Rows.Count);
}

catch(OleDbException oleex)

{

Console.WriteLine(oleex.Message);

}

catch(Exception ex)

{

throw ex;

}

}

private void button2_Click(object sender, System.EventArgs e)

{

string dbstring = "User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;Workstation
ID=MULLIN-YU;database=CCBSAPP";

SqlConnection conn = new SqlConnection(dbstring);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = "GetJobItems";

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

SqlParameter paramMachine = new SqlParameter("@Machine",

SqlDbType.VarChar,

20, ParameterDirection.Input, false, 0, 0, "MachineLocked",
DataRowVersion.Current, valMachine);

SqlParameter paramOutboundType = new SqlParameter("@OutboundType",
SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "OutboundType",
DataRowVersion.Current, valOutboundType);

sqlCommand.Parameters.Add(paramMachine);

sqlCommand.Parameters.Add(paramOutboundType);
//sqlCommand.Connection.Open();

sqlDataAdapter.Fill(results);

conn.Close();

conn.Dispose();
MessageBox.Show("count: " + results.Rows.Count);

}

"Mullin Yu" <mu*******@ctil.com> wrote in message
news:uF**************@TK2MSFTNGP09.phx.gbl...

i have a stored procedure at sql server 2k. which will update records

and select result from temp table.

if i use SqlConnection class, and i do both. but, if i use OleDbConnection class, i can have only records updated, but no result come out.

how can i debug, and what's the error?

thanks!

[Mullin Yu]

Got it.

Should put set nocount on after first begin.

thanks!

mullin

"Mullin Yu" <mu*******@ctil.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

I've set it, but still error.

I tested with a simple query at the sp like
select * from Table1. I got resultset from both OleDbConnection and
SqlConnection.

It means there's problem when running my sp with OleDbConnection.

Here's my sp:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC GetJobItems @Machine as varchar(20), @OutboundType as int as
declare @JobID as bigint
declare @JobItemID as bigint
BEGIN

-- Select one JobItem from OutboundQueue
select top 1 @JobID=JobID from OutboundQueueItem where isLocked = 0 and
AddToProcessing = 1 and OutboundType = @OutboundType order by SubmissionDate asc

--
************************************************** ***********
-- select JobItems within that JobID just got
-- Using Cursor
-- ************************************************** ***********

-- Declare the variables to store the values returned by FETCH.
DECLARE @tmpJobItemID as bigint

-- Declare the variable to store the concat value
DECLARE @tmpString as varchar(200)

-- Initialize
SET @tmpString = ''

DECLARE SampleCrsr CURSOR FOR
SELECT JobItemID FROM OutboundQueueItem where JobID = @JobID and IsLocked = 0 and AddToProcessing = 1 and OutboundType = @OutboundType
-- Create temp Table for storing JobItemIDs to be processed
CREATE TABLE #tmpJobItemID(tJobItemID bigint)

OPEN SampleCrsr

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

--SET @tmpString = @tmpString + Convert(varchar, @tmpJobItemID) + ', ' INSERT INTO #tmpJobItemID (tJobItemID) Values (@tmpJobItemID)

-- Concatenate and display the current values in the variables.
--PRINT 'JobItemID: ' + Convert(varchar, @tmpJobItemID)

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM SampleCrsr INTO @tmpJobItemID
END

--PRINT 'tmpString: ' + @tmpString + '1'

CLOSE SampleCrsr
DEALLOCATE SampleCrsr

-- ************************************************** *************
-- End Of using cursor
-- ************************************************** *************

-- Lock the Job at OutboundQueue, and update the status to "Working"
-- update OutboundQueueItem set isLocked = 1, MachineLocked = 'Mullin-Yu', Status = 2 where JobItemID = @JobItemID
update OutboundQueueItem set isLocked = 1, MachineLocked = @Machine,
Status = 2 where JobItemID in (select tJobItemID from #tmpJobItemID)

-- return recordset
-- select * from OutboundQueueItem where JobItemID =
@JobItemID
select * from OutboundQueueItem where JobItemID in (select tJobItemID from #tmpJobItemID)

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


"Chris Torgerson" <ct********@nospam.mindspring.com> wrote in message
news:ez**************@tk2msftngp13.phx.gbl...

It may be in your Stored Procedure code. Please note that I haven't tested

this theory , but just something to think about. Have you set Nocount on in

the SP. I don't know if it effects the SQLClient, but having the record
counts on in Classic ADO would cause problems in the resultsets returned.

I

assume the OLEDB classes would probably have the same problems. Just a
thought.

HTH
Chris Torgerson
"Mullin Yu" <mu*******@ctil.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

it's my program.

really strange!!

Ole -> 0

Sql -> 1

Do I need to set something else?

thanks!

private void button1_Click(object sender, System.EventArgs e)

{

try

{

//string dbstring = "Provider=SQLOLEDB.1;User

ID=sa;Password=ccbswsd;Data Source=CCBS-EDMSDB4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MULLIN-YU;Use Encryption for Data=False;Tag with column collation when possible=False;database=CCBSAPP";

string dbstring = "Provider=SQLOLEDB.1;User ID=sa;Password=ccbswsd;Data Source=CCBS-EDMSDB4;Packet Size=4096;database=CCBSAPP";

OleDbConnection conn = new OleDbConnection(dbstring);
OleDbCommand oleCommand = new OleDbCommand();

oleCommand.CommandText = "GetJobItems";

oleCommand.CommandType = CommandType.StoredProcedure;

oleCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(oleCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

OleDbParameter paramMachine = new OleDbParameter("@Machine",
OleDbType.VarChar, 20, ParameterDirection.Input, false, 0, 0,
"MachineLocked", DataRowVersion.Current, valMachine);

OleDbParameter paramOutboundType = new OleDbParameter("@OutboundType",
OleDbType.Integer, 4, ParameterDirection.Input, false, 0, 0,

"OutboundType",

DataRowVersion.Current, valOutboundType);

oleCommand.Parameters.Add(paramMachine);

oleCommand.Parameters.Add(paramOutboundType);
oleDataAdapter.Fill(results);

MessageBox.Show("count: " + results.Rows.Count);
}

catch(OleDbException oleex)

{

Console.WriteLine(oleex.Message);

}

catch(Exception ex)

{

throw ex;

}

}

private void button2_Click(object sender, System.EventArgs e)

{

string dbstring = "User ID=sa;Password=ccbswsd;Data
Source=CCBS-EDMSDB4;Packet Size=4096;Workstation
ID=MULLIN-YU;database=CCBSAPP";

SqlConnection conn = new SqlConnection(dbstring);

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = "GetJobItems";

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Connection = conn;

DataTable results = new DataTable("OutboundQueueItem");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
//string valMachine = "localhost";

//int valOutboundType = 2;

string valMachine = "aaa";

int valOutboundType = 2;

SqlParameter paramMachine = new SqlParameter("@Machine",

SqlDbType.VarChar,

20, ParameterDirection.Input, false, 0, 0, "MachineLocked",
DataRowVersion.Current, valMachine);

SqlParameter paramOutboundType = new SqlParameter("@OutboundType",
SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "OutboundType", DataRowVersion.Current, valOutboundType);

sqlCommand.Parameters.Add(paramMachine);

sqlCommand.Parameters.Add(paramOutboundType);
//sqlCommand.Connection.Open();

sqlDataAdapter.Fill(results);

conn.Close();

conn.Dispose();
MessageBox.Show("count: " + results.Rows.Count);

}

"Mullin Yu" <mu*******@ctil.com> wrote in message
news:uF**************@TK2MSFTNGP09.phx.gbl...
> i have a stored procedure at sql server 2k. which will update

records and

> select result from temp table.
>
> if i use SqlConnection class, and i do both. but, if i use

OleDbConnection

> class, i can have only records updated, but no result come out.
>
> how can i debug, and what's the error?
>
> thanks!
>
>
>