HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server) 
Author Message
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

PLEASE HELP:
I created a SQL Server stored procedure that has only 1 OUTPUT
variable.  (This variable can be an alpha-numeric string up to a few
thousand characters.)  I am trying to get the results of the output
variable in VB6, using ADO.

PROBLEM:
When I run the stored proc through Query Analyzer it runs perfectly.
When I use ADO in VB to get the returned results (dimensioning the
variable as, say VarChar(75), VB gets the value fine.  Once I define
it as VarChar(7000), when VB runs the .execute command, VB shuts
itself down!

QUESTIONS:
1) SQL Server: How should I dimension the variable? / What data type
should be assigned to it?
2) VB: What is the best way to get the value?

=====================

FYI:
Here is how I am currently defining the variable in the stored
procedure:

Here is how I am setting up the ADO object in VB6:
Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
adParamOutput, 7000, vbNull)

Here is how I access the output variable:
MsgBox cmd1.Parameters("OutClientMsg").Value

=====================


Thanks in advance!
Robert



Sun, 21 Nov 2004 21:35:30 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

I don't think You can return more then 1000 char from SP in one variable.
Return recordset instead.


Quote:
> PLEASE HELP:
> I created a SQL Server stored procedure that has only 1 OUTPUT
> variable.  (This variable can be an alpha-numeric string up to a few
> thousand characters.)  I am trying to get the results of the output
> variable in VB6, using ADO.

> PROBLEM:
> When I run the stored proc through Query Analyzer it runs perfectly.
> When I use ADO in VB to get the returned results (dimensioning the
> variable as, say VarChar(75), VB gets the value fine.  Once I define
> it as VarChar(7000), when VB runs the .execute command, VB shuts
> itself down!

> QUESTIONS:
> 1) SQL Server: How should I dimension the variable? / What data type
> should be assigned to it?
> 2) VB: What is the best way to get the value?

> =====================

> FYI:
> Here is how I am currently defining the variable in the stored
> procedure:

> Here is how I am setting up the ADO object in VB6:
> Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> adParamOutput, 7000, vbNull)

> Here is how I access the output variable:
> MsgBox cmd1.Parameters("OutClientMsg").Value

> =====================


> Thanks in advance!
> Robert



Sun, 21 Nov 2004 21:50:42 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
Could I use adLongVarWChar or adLongVarChar (for the "Type" argument in
.CreateParameter)?

Tried it, but couldn't get it to work yet.

Thanks for the response,
Robert


Quote:
> I don't think You can return more then 1000 char from SP in one variable.
> Return recordset instead.



> > PLEASE HELP:
> > I created a SQL Server stored procedure that has only 1 OUTPUT
> > variable.  (This variable can be an alpha-numeric string up to a few
> > thousand characters.)  I am trying to get the results of the output
> > variable in VB6, using ADO.

> > PROBLEM:
> > When I run the stored proc through Query Analyzer it runs perfectly.
> > When I use ADO in VB to get the returned results (dimensioning the
> > variable as, say VarChar(75), VB gets the value fine.  Once I define
> > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > itself down!

> > QUESTIONS:
> > 1) SQL Server: How should I dimension the variable? / What data type
> > should be assigned to it?
> > 2) VB: What is the best way to get the value?

> > =====================

> > FYI:
> > Here is how I am currently defining the variable in the stored
> > procedure:

> > Here is how I am setting up the ADO object in VB6:
> > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > adParamOutput, 7000, vbNull)

> > Here is how I access the output variable:
> > MsgBox cmd1.Parameters("OutClientMsg").Value

> > =====================


> > Thanks in advance!
> > Robert



Sun, 21 Nov 2004 22:07:30 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
The problem itself not in ADO or VB.
This is SQL Server itself.

Did you try to pass 7000 varchar from 1 sp to another?
I remember we tried(not sure) and ended up using 7 parameters.
Also, my preffered method of calling sps is like this:

Dim StProc As Command
Dim Param As ADODB.Parameter

Set StProc = New Command
With StProc
.ActiveConnection = WorkConnection
.CommandText = "spuMaintainUserGroup"
.CommandType = adCmdStoredProc
.Parameters(1).Value = MaintType
.Parameters(2).Value = UserGroupID
.Parameters(3).Value = UserGroupName
.Parameters(4).Value = Description
.Execute

End With
Set StProc = Nothing


Quote:
> Could I use adLongVarWChar or adLongVarChar (for the "Type" argument in
> .CreateParameter)?

> Tried it, but couldn't get it to work yet.

> Thanks for the response,
> Robert



> > I don't think You can return more then 1000 char from SP in one
variable.
> > Return recordset instead.



> > > PLEASE HELP:
> > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > variable.  (This variable can be an alpha-numeric string up to a few
> > > thousand characters.)  I am trying to get the results of the output
> > > variable in VB6, using ADO.

> > > PROBLEM:
> > > When I run the stored proc through Query Analyzer it runs perfectly.
> > > When I use ADO in VB to get the returned results (dimensioning the
> > > variable as, say VarChar(75), VB gets the value fine.  Once I define
> > > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > > itself down!

> > > QUESTIONS:
> > > 1) SQL Server: How should I dimension the variable? / What data type
> > > should be assigned to it?
> > > 2) VB: What is the best way to get the value?

> > > =====================

> > > FYI:
> > > Here is how I am currently defining the variable in the stored
> > > procedure:

> > > Here is how I am setting up the ADO object in VB6:
> > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > > adParamOutput, 7000, vbNull)

> > > Here is how I access the output variable:
> > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > =====================


> > > Thanks in advance!
> > > Robert



Sun, 21 Nov 2004 22:14:18 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
(Just to clarify: Using MSDE with SQL Server Enterprise Mgr.)

Haven't tried passing it from 1 sp to another.  Don't think I know how to do
that... yet :-)

I could resort to passing the recordset, or passing several output
parameters (less desirous).
Do you know the char size limit for OUTPUT parameters?

One more question:  When setting up the sp parameters in vb, don't you have
to specify the "type" as well as if the parameter is an input, or output
parameter, as well as add the parameter to the command object?  (Thanks for
the sample code.)

Thanks for all your help!
Robert


Quote:
> The problem itself not in ADO or VB.
> This is SQL Server itself.

> Did you try to pass 7000 varchar from 1 sp to another?
> I remember we tried(not sure) and ended up using 7 parameters.
> Also, my preffered method of calling sps is like this:

> Dim StProc As Command
> Dim Param As ADODB.Parameter

> Set StProc = New Command
> With StProc
> .ActiveConnection = WorkConnection
> .CommandText = "spuMaintainUserGroup"
> .CommandType = adCmdStoredProc
> .Parameters(1).Value = MaintType
> .Parameters(2).Value = UserGroupID
> .Parameters(3).Value = UserGroupName
> .Parameters(4).Value = Description
> .Execute

> End With
> Set StProc = Nothing



> > Could I use adLongVarWChar or adLongVarChar (for the "Type" argument in
> > .CreateParameter)?

> > Tried it, but couldn't get it to work yet.

> > Thanks for the response,
> > Robert



> > > I don't think You can return more then 1000 char from SP in one
> variable.
> > > Return recordset instead.



> > > > PLEASE HELP:
> > > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > > variable.  (This variable can be an alpha-numeric string up to a few
> > > > thousand characters.)  I am trying to get the results of the output
> > > > variable in VB6, using ADO.

> > > > PROBLEM:
> > > > When I run the stored proc through Query Analyzer it runs perfectly.
> > > > When I use ADO in VB to get the returned results (dimensioning the
> > > > variable as, say VarChar(75), VB gets the value fine.  Once I define
> > > > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > > > itself down!

> > > > QUESTIONS:
> > > > 1) SQL Server: How should I dimension the variable? / What data type
> > > > should be assigned to it?
> > > > 2) VB: What is the best way to get the value?

> > > > =====================

> > > > FYI:
> > > > Here is how I am currently defining the variable in the stored
> > > > procedure:

> > > > Here is how I am setting up the ADO object in VB6:
> > > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > > > adParamOutput, 7000, vbNull)

> > > > Here is how I access the output variable:
> > > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > > =====================


> > > > Thanks in advance!
> > > > Robert



Sun, 21 Nov 2004 22:25:34 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
Ivan,

Quote:
> I don't think You can return more then 1000 char from SP in one variable.

Not correct. See the following counter example:


as

go




-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I don't think You can return more then 1000 char from SP in one variable.
> Return recordset instead.



> > PLEASE HELP:
> > I created a SQL Server stored procedure that has only 1 OUTPUT
> > variable.  (This variable can be an alpha-numeric string up to a few
> > thousand characters.)  I am trying to get the results of the output
> > variable in VB6, using ADO.

> > PROBLEM:
> > When I run the stored proc through Query Analyzer it runs perfectly.
> > When I use ADO in VB to get the returned results (dimensioning the
> > variable as, say VarChar(75), VB gets the value fine.  Once I define
> > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > itself down!

> > QUESTIONS:
> > 1) SQL Server: How should I dimension the variable? / What data type
> > should be assigned to it?
> > 2) VB: What is the best way to get the value?

> > =====================

> > FYI:
> > Here is how I am currently defining the variable in the stored
> > procedure:

> > Here is how I am setting up the ADO object in VB6:
> > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > adParamOutput, 7000, vbNull)

> > Here is how I access the output variable:
> > MsgBox cmd1.Parameters("OutClientMsg").Value

> > =====================


> > Thanks in advance!
> > Robert



Sun, 21 Nov 2004 22:30:13 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

Thanks,
Robert


Quote:
> Ivan,

> > I don't think You can return more then 1000 char from SP in one
variable.

> Not correct. See the following counter example:


> as

> go




> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I don't think You can return more then 1000 char from SP in one
variable.
> > Return recordset instead.



> > > PLEASE HELP:
> > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > variable.  (This variable can be an alpha-numeric string up to a few
> > > thousand characters.)  I am trying to get the results of the output
> > > variable in VB6, using ADO.

> > > PROBLEM:
> > > When I run the stored proc through Query Analyzer it runs perfectly.
> > > When I use ADO in VB to get the returned results (dimensioning the
> > > variable as, say VarChar(75), VB gets the value fine.  Once I define
> > > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > > itself down!

> > > QUESTIONS:
> > > 1) SQL Server: How should I dimension the variable? / What data type
> > > should be assigned to it?
> > > 2) VB: What is the best way to get the value?

> > > =====================

> > > FYI:
> > > Here is how I am currently defining the variable in the stored
> > > procedure:

> > > Here is how I am setting up the ADO object in VB6:
> > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > > adParamOutput, 7000, vbNull)

> > > Here is how I access the output variable:
> > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > =====================


> > > Thanks in advance!
> > > Robert



Sun, 21 Nov 2004 22:29:52 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
BP Margolin corrected me about size :-)

Try my code and see if it works.
You can do
.Parameters(1).Value = UserGroupID
or

same thing.
Only with second line you don't need to worry if you add more parameters.
first one is assigning parameters in their order in sp.

VB String will be fine for SQL Char and VarChar.

Be Careful! Use VB Long for SQL int
VB Integer for SQL smallint


Quote:
> (Just to clarify: Using MSDE with SQL Server Enterprise Mgr.)

> Haven't tried passing it from 1 sp to another.  Don't think I know how to
do
> that... yet :-)

> I could resort to passing the recordset, or passing several output
> parameters (less desirous).
> Do you know the char size limit for OUTPUT parameters?

> One more question:  When setting up the sp parameters in vb, don't you
have
> to specify the "type" as well as if the parameter is an input, or output
> parameter, as well as add the parameter to the command object?  (Thanks
for
> the sample code.)

> Thanks for all your help!
> Robert



> > The problem itself not in ADO or VB.
> > This is SQL Server itself.

> > Did you try to pass 7000 varchar from 1 sp to another?
> > I remember we tried(not sure) and ended up using 7 parameters.
> > Also, my preffered method of calling sps is like this:

> > Dim StProc As Command
> > Dim Param As ADODB.Parameter

> > Set StProc = New Command
> > With StProc
> > .ActiveConnection = WorkConnection
> > .CommandText = "spuMaintainUserGroup"
> > .CommandType = adCmdStoredProc
> > .Parameters(1).Value = MaintType
> > .Parameters(2).Value = UserGroupID
> > .Parameters(3).Value = UserGroupName
> > .Parameters(4).Value = Description
> > .Execute

> > End With
> > Set StProc = Nothing



> > > Could I use adLongVarWChar or adLongVarChar (for the "Type" argument
in
> > > .CreateParameter)?

> > > Tried it, but couldn't get it to work yet.

> > > Thanks for the response,
> > > Robert



> > > > I don't think You can return more then 1000 char from SP in one
> > variable.
> > > > Return recordset instead.



> > > > > PLEASE HELP:
> > > > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > > > variable.  (This variable can be an alpha-numeric string up to a
few
> > > > > thousand characters.)  I am trying to get the results of the
output
> > > > > variable in VB6, using ADO.

> > > > > PROBLEM:
> > > > > When I run the stored proc through Query Analyzer it runs
perfectly.
> > > > > When I use ADO in VB to get the returned results (dimensioning the
> > > > > variable as, say VarChar(75), VB gets the value fine.  Once I
define
> > > > > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > > > > itself down!

> > > > > QUESTIONS:
> > > > > 1) SQL Server: How should I dimension the variable? / What data
type
> > > > > should be assigned to it?
> > > > > 2) VB: What is the best way to get the value?

> > > > > =====================

> > > > > FYI:
> > > > > Here is how I am currently defining the variable in the stored
> > > > > procedure:

> > > > > Here is how I am setting up the ADO object in VB6:
> > > > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg", adVarChar,
> > > > > adParamOutput, 7000, vbNull)

> > > > > Here is how I access the output variable:
> > > > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > > > =====================


> > > > > Thanks in advance!
> > > > > Robert



Sun, 21 Nov 2004 22:35:52 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
one more thing:

How you connect to SQL? ODBC or OLE DB?

Anyway, for big data (I'm not saying 7kb is big data)  make sure Timeout
property of ADO connection and ODBC is big enough


Quote:
> BP Margolin corrected me about size :-)

> Try my code and see if it works.
> You can do
> .Parameters(1).Value = UserGroupID
> or

> same thing.
> Only with second line you don't need to worry if you add more parameters.
> first one is assigning parameters in their order in sp.

> VB String will be fine for SQL Char and VarChar.

> Be Careful! Use VB Long for SQL int
> VB Integer for SQL smallint



> > (Just to clarify: Using MSDE with SQL Server Enterprise Mgr.)

> > Haven't tried passing it from 1 sp to another.  Don't think I know how
to
> do
> > that... yet :-)

> > I could resort to passing the recordset, or passing several output
> > parameters (less desirous).
> > Do you know the char size limit for OUTPUT parameters?

> > One more question:  When setting up the sp parameters in vb, don't you
> have
> > to specify the "type" as well as if the parameter is an input, or output
> > parameter, as well as add the parameter to the command object?  (Thanks
> for
> > the sample code.)

> > Thanks for all your help!
> > Robert



> > > The problem itself not in ADO or VB.
> > > This is SQL Server itself.

> > > Did you try to pass 7000 varchar from 1 sp to another?
> > > I remember we tried(not sure) and ended up using 7 parameters.
> > > Also, my preffered method of calling sps is like this:

> > > Dim StProc As Command
> > > Dim Param As ADODB.Parameter

> > > Set StProc = New Command
> > > With StProc
> > > .ActiveConnection = WorkConnection
> > > .CommandText = "spuMaintainUserGroup"
> > > .CommandType = adCmdStoredProc
> > > .Parameters(1).Value = MaintType
> > > .Parameters(2).Value = UserGroupID
> > > .Parameters(3).Value = UserGroupName
> > > .Parameters(4).Value = Description
> > > .Execute

> > > End With
> > > Set StProc = Nothing



> > > > Could I use adLongVarWChar or adLongVarChar (for the "Type" argument
> in
> > > > .CreateParameter)?

> > > > Tried it, but couldn't get it to work yet.

> > > > Thanks for the response,
> > > > Robert



> > > > > I don't think You can return more then 1000 char from SP in one
> > > variable.
> > > > > Return recordset instead.



> > > > > > PLEASE HELP:
> > > > > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > > > > variable.  (This variable can be an alpha-numeric string up to a
> few
> > > > > > thousand characters.)  I am trying to get the results of the
> output
> > > > > > variable in VB6, using ADO.

> > > > > > PROBLEM:
> > > > > > When I run the stored proc through Query Analyzer it runs
> perfectly.
> > > > > > When I use ADO in VB to get the returned results (dimensioning
the
> > > > > > variable as, say VarChar(75), VB gets the value fine.  Once I
> define
> > > > > > it as VarChar(7000), when VB runs the .execute command, VB shuts
> > > > > > itself down!

> > > > > > QUESTIONS:
> > > > > > 1) SQL Server: How should I dimension the variable? / What data
> type
> > > > > > should be assigned to it?
> > > > > > 2) VB: What is the best way to get the value?

> > > > > > =====================

> > > > > > FYI:
> > > > > > Here is how I am currently defining the variable in the stored
> > > > > > procedure:

> > > > > > Here is how I am setting up the ADO object in VB6:
> > > > > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg",
adVarChar,
> > > > > > adParamOutput, 7000, vbNull)

> > > > > > Here is how I access the output variable:
> > > > > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > > > > =====================


> > > > > > Thanks in advance!
> > > > > > Robert



Sun, 21 Nov 2004 22:38:05 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
I'm using OLE DB.

Did you figure out how to get a 7000 character stored procedure OUTPUT
parameter using VB ADO?  :)

You've been a great help.  Thanks!

Robert


Quote:
> one more thing:

> How you connect to SQL? ODBC or OLE DB?

> Anyway, for big data (I'm not saying 7kb is big data)  make sure Timeout
> property of ADO connection and ODBC is big enough



> > BP Margolin corrected me about size :-)

> > Try my code and see if it works.
> > You can do
> > .Parameters(1).Value = UserGroupID
> > or

> > same thing.
> > Only with second line you don't need to worry if you add more
parameters.
> > first one is assigning parameters in their order in sp.

> > VB String will be fine for SQL Char and VarChar.

> > Be Careful! Use VB Long for SQL int
> > VB Integer for SQL smallint



> > > (Just to clarify: Using MSDE with SQL Server Enterprise Mgr.)

> > > Haven't tried passing it from 1 sp to another.  Don't think I know how
> to
> > do
> > > that... yet :-)

> > > I could resort to passing the recordset, or passing several output
> > > parameters (less desirous).
> > > Do you know the char size limit for OUTPUT parameters?

> > > One more question:  When setting up the sp parameters in vb, don't you
> > have
> > > to specify the "type" as well as if the parameter is an input, or
output
> > > parameter, as well as add the parameter to the command object?
(Thanks
> > for
> > > the sample code.)

> > > Thanks for all your help!
> > > Robert



> > > > The problem itself not in ADO or VB.
> > > > This is SQL Server itself.

> > > > Did you try to pass 7000 varchar from 1 sp to another?
> > > > I remember we tried(not sure) and ended up using 7 parameters.
> > > > Also, my preffered method of calling sps is like this:

> > > > Dim StProc As Command
> > > > Dim Param As ADODB.Parameter

> > > > Set StProc = New Command
> > > > With StProc
> > > > .ActiveConnection = WorkConnection
> > > > .CommandText = "spuMaintainUserGroup"
> > > > .CommandType = adCmdStoredProc
> > > > .Parameters(1).Value = MaintType
> > > > .Parameters(2).Value = UserGroupID
> > > > .Parameters(3).Value = UserGroupName
> > > > .Parameters(4).Value = Description
> > > > .Execute

> > > > End With
> > > > Set StProc = Nothing



> > > > > Could I use adLongVarWChar or adLongVarChar (for the "Type"
argument
> > in
> > > > > .CreateParameter)?

> > > > > Tried it, but couldn't get it to work yet.

> > > > > Thanks for the response,
> > > > > Robert



> > > > > > I don't think You can return more then 1000 char from SP in one
> > > > variable.
> > > > > > Return recordset instead.



> > > > > > > PLEASE HELP:
> > > > > > > I created a SQL Server stored procedure that has only 1 OUTPUT
> > > > > > > variable.  (This variable can be an alpha-numeric string up to
a
> > few
> > > > > > > thousand characters.)  I am trying to get the results of the
> > output
> > > > > > > variable in VB6, using ADO.

> > > > > > > PROBLEM:
> > > > > > > When I run the stored proc through Query Analyzer it runs
> > perfectly.
> > > > > > > When I use ADO in VB to get the returned results (dimensioning
> the
> > > > > > > variable as, say VarChar(75), VB gets the value fine.  Once I
> > define
> > > > > > > it as VarChar(7000), when VB runs the .execute command, VB
shuts
> > > > > > > itself down!

> > > > > > > QUESTIONS:
> > > > > > > 1) SQL Server: How should I dimension the variable? / What
data
> > type
> > > > > > > should be assigned to it?
> > > > > > > 2) VB: What is the best way to get the value?

> > > > > > > =====================

> > > > > > > FYI:
> > > > > > > Here is how I am currently defining the variable in the stored
> > > > > > > procedure:

OUTPUT,...)

> > > > > > > Here is how I am setting up the ADO object in VB6:
> > > > > > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg",
> adVarChar,
> > > > > > > adParamOutput, 7000, vbNull)

> > > > > > > Here is how I access the output variable:
> > > > > > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > > > > > =====================


> > > > > > > Thanks in advance!
> > > > > > > Robert



Sun, 21 Nov 2004 23:33:43 GMT
 HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)
Did you make it work?


Quote:
> I'm using OLE DB.

> Did you figure out how to get a 7000 character stored procedure OUTPUT
> parameter using VB ADO?  :)

> You've been a great help.  Thanks!

> Robert



> > one more thing:

> > How you connect to SQL? ODBC or OLE DB?

> > Anyway, for big data (I'm not saying 7kb is big data)  make sure Timeout
> > property of ADO connection and ODBC is big enough



> > > BP Margolin corrected me about size :-)

> > > Try my code and see if it works.
> > > You can do
> > > .Parameters(1).Value = UserGroupID
> > > or

> > > same thing.
> > > Only with second line you don't need to worry if you add more
> parameters.
> > > first one is assigning parameters in their order in sp.

> > > VB String will be fine for SQL Char and VarChar.

> > > Be Careful! Use VB Long for SQL int
> > > VB Integer for SQL smallint



> > > > (Just to clarify: Using MSDE with SQL Server Enterprise Mgr.)

> > > > Haven't tried passing it from 1 sp to another.  Don't think I know
how
> > to
> > > do
> > > > that... yet :-)

> > > > I could resort to passing the recordset, or passing several output
> > > > parameters (less desirous).
> > > > Do you know the char size limit for OUTPUT parameters?

> > > > One more question:  When setting up the sp parameters in vb, don't
you
> > > have
> > > > to specify the "type" as well as if the parameter is an input, or
> output
> > > > parameter, as well as add the parameter to the command object?
> (Thanks
> > > for
> > > > the sample code.)

> > > > Thanks for all your help!
> > > > Robert



> > > > > The problem itself not in ADO or VB.
> > > > > This is SQL Server itself.

> > > > > Did you try to pass 7000 varchar from 1 sp to another?
> > > > > I remember we tried(not sure) and ended up using 7 parameters.
> > > > > Also, my preffered method of calling sps is like this:

> > > > > Dim StProc As Command
> > > > > Dim Param As ADODB.Parameter

> > > > > Set StProc = New Command
> > > > > With StProc
> > > > > .ActiveConnection = WorkConnection
> > > > > .CommandText = "spuMaintainUserGroup"
> > > > > .CommandType = adCmdStoredProc
> > > > > .Parameters(1).Value = MaintType
> > > > > .Parameters(2).Value = UserGroupID
> > > > > .Parameters(3).Value = UserGroupName
> > > > > .Parameters(4).Value = Description
> > > > > .Execute

> > > > > End With
> > > > > Set StProc = Nothing



> > > > > > Could I use adLongVarWChar or adLongVarChar (for the "Type"
> argument
> > > in
> > > > > > .CreateParameter)?

> > > > > > Tried it, but couldn't get it to work yet.

> > > > > > Thanks for the response,
> > > > > > Robert



> > > > > > > I don't think You can return more then 1000 char from SP in
one
> > > > > variable.
> > > > > > > Return recordset instead.



> > > > > > > > PLEASE HELP:
> > > > > > > > I created a SQL Server stored procedure that has only 1
OUTPUT
> > > > > > > > variable.  (This variable can be an alpha-numeric string up
to
> a
> > > few
> > > > > > > > thousand characters.)  I am trying to get the results of the
> > > output
> > > > > > > > variable in VB6, using ADO.

> > > > > > > > PROBLEM:
> > > > > > > > When I run the stored proc through Query Analyzer it runs
> > > perfectly.
> > > > > > > > When I use ADO in VB to get the returned results
(dimensioning
> > the
> > > > > > > > variable as, say VarChar(75), VB gets the value fine.  Once
I
> > > define
> > > > > > > > it as VarChar(7000), when VB runs the .execute command, VB
> shuts
> > > > > > > > itself down!

> > > > > > > > QUESTIONS:
> > > > > > > > 1) SQL Server: How should I dimension the variable? / What
> data
> > > type
> > > > > > > > should be assigned to it?
> > > > > > > > 2) VB: What is the best way to get the value?

> > > > > > > > =====================

> > > > > > > > FYI:
> > > > > > > > Here is how I am currently defining the variable in the
stored
> > > > > > > > procedure:

> OUTPUT,...)

> > > > > > > > Here is how I am setting up the ADO object in VB6:
> > > > > > > > Set prmOutputMsg = cmd1.CreateParameter("OutClientMsg",
> > adVarChar,
> > > > > > > > adParamOutput, 7000, vbNull)

> > > > > > > > Here is how I access the output variable:
> > > > > > > > MsgBox cmd1.Parameters("OutClientMsg").Value

> > > > > > > > =====================


> > > > > > > > Thanks in advance!
> > > > > > > > Robert



Sun, 21 Nov 2004 23:42:12 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

2. HELP: Calling Stored Procedure w/large output from VB6 (using SQL Server)

3. Calling a stored procedure from VC++ using CRecordset::Open using output paramet

4. Calling a store procedure from vb6 with sql server

5. Error at obtaining output parameters of the stored procedure, calling other stored procedure

6. Using OUTPUT Parameters in SQLServer Stored Procedures Called From VB

7. Using ESQL for C to call a stored procedure with output variables

8. Calling Oracle Stored Procedure from within SQL Server Stored Procedure

9. How did I call a store procedure by using VB6 DAO connection

10. Calling Oracle Stored Procedure from VB6 using DAO

11. Help on Calling Stored Procedures from VB6.0

12. OUTPUT Params from Stored Procedures HELP MS SQL Server


 
Powered by phpBB® Forum Software