Can the Output parameter length be more than 8000 characters?

Hi!
I am running one SP - which needs to return strings, seperated by
delimitter. I am using output parameter of type Varchar (8000). I learned
that this is maximum length allowed.
Now what problem I am facing is, for a particular field, the delimitted text
is getting higher than 8000 characters and that is why the rest of the value
is getting truncated.
Can you guys let me know any better way of achieving this?
I will be extremely thankful to you.
Regards,
SachinYou'll have to select the data instead of using an output param... Or
upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted
> text
> is getting higher than 8000 characters and that is why the rest of the
> value
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Instead of Varchar(8000) ... how about using TEXT or NText as your datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Sachin Vaishnav" wrote:

> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted te
xt
> is getting higher than 8000 characters and that is why the rest of the val
ue
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Thanks. Using 2005 is not possible for me now. I will have to manage fromw
what I have already :)
Anyways, as per your other suggestion, the problem in that is, I am already
having one select returned out of the SP. So, there is no point in that also
.
Can some cursor type of output or XML type of output is useful to me?
I need to send it back to the API and the API is used by UI.
Help me,
Sachin
"Adam Machanic" wrote:

> You'll have to select the data instead of using an output param... Or
> upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
>
>|||Stored procedures can return multiple rowsets... Why not use two?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
> Thanks. Using 2005 is not possible for me now. I will have to manage fromw
> what I have already :)
> Anyways, as per your other suggestion, the problem in that is, I am
> already
> having one select returned out of the SP. So, there is no point in that
> also.
> Can some cursor type of output or XML type of output is useful to me?
> I need to send it back to the API and the API is used by UI.
> Help me,
> Sachin
> "Adam Machanic" wrote:
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Ogxg9Nr7FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Stored procedures can return multiple rowsets... Why not use two?
...or use multiple Output parameters.
When one reaches the 8000 character limit, insert the rest in the 2nd.
But I'd prefer Adam's solution, 2 recordsets.|||Hi!
Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to get
once. Can I have some example of the same?
Thanks
Sachin
"Adam Machanic" wrote:

> Stored procedures can return multiple rowsets... Why not use two?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
>
>|||Sure...
CREATE PROCEDURE TWO_RESULT_SETS
AS
BEGIN
SELECT 1
SELECT 2
END
GO
EXEC TWO_RESULT_SETS
GO
DROP PROCEDURE TWO_RESULT_SETS
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
> Hi!
> Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to
> get
> once. Can I have some example of the same?
> Thanks
> Sachin
> "Adam Machanic" wrote:
>|||Thanks a lotl!
However, I know this. But i guess, the problem is perhaps, when I write 2
selects in the SP, if I am using ADODB.Recordset to retrieve the data, I
won't get the result of both the record set. Right?
So, can you suggest me how do I tackle that one? :)
Thanks again!
Regards,
Sachin
"Adam Machanic" wrote:

> Sure...
> --
> CREATE PROCEDURE TWO_RESULT_SETS
> AS
> BEGIN
> SELECT 1
> SELECT 2
> END
> GO
> EXEC TWO_RESULT_SETS
> GO
> DROP PROCEDURE TWO_RESULT_SETS
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
>
>|||Set rsSecond = rsFirst.NextRecordset()
cheers,
</wqw>