extended stored procedures: is there a 256 character limit on INPUT parameters?

Hello,
On SQL Server 2000 (SP3), for extended stored procedures: is there a
256 character limit on INPUT parameters?
And if so, is there a way around that?
Thanks,
BertAFAIK, there is no such limit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bert Szoghy" <webmaster@.quadmore.com> schrieb im Newsbeitrag
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Here's a quick test script for you:
CREATE PROCEDURE dbo.usp_Char256Test
@.s VARCHAR(257)
AS
PRINT LEN(@.s)
GO
DECLARE @.TempString VARCHAR(257)
SET @.TempString = REPLICATE('X', 257)
PRINT LEN(@.TempString)
EXEC dbo.usp_Char256Test @.TempString
This creates an SP that allows a 257 char VARCHAR to be passed in. It then
prints the Length of the passed in string to verify that all 257 chars were
passed successfully.
The limit you're encountering is probably due to a CHAR(256) or VARCHAR(256)
limit placed on the definition of the parameter in the SP declaration (above
I set the limit to 257, but any value up to 8000 for VARCHAR [4,000 for
NVARCHAR] should work).
"Bert Szoghy" <webmaster@.quadmore.com> wrote in message
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFTNGP14.phx.gbl>..
.
> Bert,
> For some system extended stored procedures, the parameters
> are defined as varchar(256). For those, there is no way around
> unless you rewrite the xp (not a good idea I think). For xp's you
> write yourself, I'm not aware of any limit. Are you having a
> particular problem you need help with?
> Steve Kass
> Drew University
> Bert Szoghy wrote:
>
Hello,
Thank you for your responses.
I have an extended stored proc DLL with C++ code which just looks fine
to me, which it refuses to give me more than 256 characters.
I found the following reference before posting here which made me
suspect it was a SQL Server limitation:
http://groups.google.ca/groups?hl=e...r />
GP15.phx.
gbl%26rnum%3D1
I am using a varchar(8000) in the trigger calling the extended stored
proc.
I'm about to try stepping through the DLL but the code is a bit nasty
and it would be nice if a SQL Server guru would point out a detail to
tweak.
Thanks guys!
Thanks again,
Bertsql

extended stored procedures: is there a 256 character limit on

Bert,
For some system extended stored procedures, the parameters
are defined as varchar(256). For those, there is no way around
unless you rewrite the xp (not a good idea I think). For xp's you
write yourself, I'm not aware of any limit. Are you having a
particular problem you need help with?
Steve Kass
Drew University
Bert Szoghy wrote:

>Hello,
>On SQL Server 2000 (SP3), for extended stored procedures: is there a
>256 character limit on INPUT parameters?
>And if so, is there a way around that?
>Thanks,
>Bert
>Bert,
The thread you posted indicates there is a 256-character limit
for DBLib, not the SQL Server xp architecture. According to John
Kane in that thread:
Anil,
ESP or (Extended Stored Procs) can use the OLE-DB API and if you are using
the DBLib API you are cutting your own code's future as it is most likely
(un-confirmed) that Microsoft will drop, i.e.. remove the DB-Library API
from future SQL Server versions, such as SQL Server 2005 (Yukon).
See SQL Server 2000 BOL titles "Running Stored Procedures (OLE DB)",
"Extended Stored Procedure Architecture", "Programming Extended Stored
Procedures" and "Samples" and specifically the sample app xp-ODBC for more
info.
This is not a SQL Server limitation, but a limitation of the old DBLib
interface
only. Rewriting the xp to use a more modern interface, as John suggested,
is probably what you need to do.
SK
Bert Szoghy wrote:

>Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFT
NGP14.phx.gbl>...
>
>Hello,
>Thank you for your responses.
>I have an extended stored proc DLL with C++ code which just looks fine
>to me, which it refuses to give me more than 256 characters.
>I found the following reference before posting here which made me
>suspect it was a SQL Server limitation:
>http://groups.google.ca/groups?hl=e...K2MSFTNGP15.phx
.gbl%26rnum%3D1
>I am using a varchar(8000) in the trigger calling the extended stored
>proc.
>I'm about to try stepping through the DLL but the code is a bit nasty
>and it would be nice if a SQL Server guru would point out a detail to
>tweak.
>Thanks guys!
>Thanks again,
>Bert
>

Extend MSDE to Enterprise

I have a system that came with the MSDE database, because of the 2G limit I
am trying to point it to the enterprise version of SQL 2000. All connection
strings have been changed to the enterprise (With vendor help). The connec
tion is established and acc
ording to a trace from Profiler, all queries are running and inserting the d
ata in the right table. But there is no data in the tables! No errors eith
er.
One of my tests was to point it to another MSDE and it worked fine. What is
different between these two versions that will cause data not to be written
to the tables.
ThanksHi,
Can you insert few records in to the tables manually and see what is
happening. I feel that application is
still pointing towards a different database.
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:4937BB3E-CD2B-4DA1-9C83-2C03E8470989@.microsoft.com...
> I have a system that came with the MSDE database, because of the 2G limit
I am trying to point it to the enterprise version of SQL 2000. All
connection strings have been changed to the enterprise (With vendor help).
The connection is established and according to a trace from Profiler, all
queries are running and inserting the data in the right table. But there is
no data in the tables! No errors either.
> One of my tests was to point it to another MSDE and it worked fine. What
is different between these two versions that will cause data not to be
written to the tables.
> Thanks|||It is pointing to the right DB because at first I pointed it to a DB that ha
d records and from the front end the viewer showed the Data (but did not ins
ert any) then, I pointed it to an empty DB and the viewer showed empty rows
and still wouldn't insert a
ny records. Are there any differences between MSDE and SQL to cause such th
ing?
Thanks
"Hari Prasad" wrote:

> Hi,
> Can you insert few records in to the tables manually and see what is
> happening. I feel that application is
> still pointing towards a different database.
>
> Thanks
> Hari
> MCDBA
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:4937BB3E-CD2B-4DA1-9C83-2C03E8470989@.microsoft.com...
> I am trying to point it to the enterprise version of SQL 2000. All
> connection strings have been changed to the enterprise (With vendor help).
> The connection is established and according to a trace from Profiler, all
> queries are running and inserting the data in the right table. But there
is
> no data in the tables! No errors either.
> is different between these two versions that will cause data not to be
> written to the tables.
>
>

Extend MSDE to Enterprise

I have a system that came with the MSDE database, because of the 2G limit I am trying to point it to the enterprise version of SQL 2000. All connection strings have been changed to the enterprise (With vendor help). The connection is established and acc
ording to a trace from Profiler, all queries are running and inserting the data in the right table. But there is no data in the tables! No errors either.
One of my tests was to point it to another MSDE and it worked fine. What is different between these two versions that will cause data not to be written to the tables.
Thanks
Hi,
Can you insert few records in to the tables manually and see what is
happening. I feel that application is
still pointing towards a different database.
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:4937BB3E-CD2B-4DA1-9C83-2C03E8470989@.microsoft.com...
> I have a system that came with the MSDE database, because of the 2G limit
I am trying to point it to the enterprise version of SQL 2000. All
connection strings have been changed to the enterprise (With vendor help).
The connection is established and according to a trace from Profiler, all
queries are running and inserting the data in the right table. But there is
no data in the tables! No errors either.
> One of my tests was to point it to another MSDE and it worked fine. What
is different between these two versions that will cause data not to be
written to the tables.
> Thanks
|||It is pointing to the right DB because at first I pointed it to a DB that had records and from the front end the viewer showed the Data (but did not insert any) then, I pointed it to an empty DB and the viewer showed empty rows and still wouldn't insert a
ny records. Are there any differences between MSDE and SQL to cause such thing?
Thanks
"Hari Prasad" wrote:

> Hi,
> Can you insert few records in to the tables manually and see what is
> happening. I feel that application is
> still pointing towards a different database.
>
> Thanks
> Hari
> MCDBA
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:4937BB3E-CD2B-4DA1-9C83-2C03E8470989@.microsoft.com...
> I am trying to point it to the enterprise version of SQL 2000. All
> connection strings have been changed to the enterprise (With vendor help).
> The connection is established and according to a trace from Profiler, all
> queries are running and inserting the data in the right table. But there is
> no data in the tables! No errors either.
> is different between these two versions that will cause data not to be
> written to the tables.
>
>

Extend MSDE to Enterprise

I have a system that came with the MSDE database, because of the 2G limit I am trying to point it to the enterprise version of SQL 2000. All connection strings have been changed to the enterprise (With vendor help). The connection is established and according to a trace from Profiler, all queries are running and inserting the data in the right table. But there is no data in the tables! No errors either.
One of my tests was to point it to another MSDE and it worked fine. What is different between these two versions that will cause data not to be written to the tables.
ThanksHi,
Can you insert few records in to the tables manually and see what is
happening. I feel that application is
still pointing towards a different database.
Thanks
Hari
MCDBA
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:4937BB3E-CD2B-4DA1-9C83-2C03E8470989@.microsoft.com...
> I have a system that came with the MSDE database, because of the 2G limit
I am trying to point it to the enterprise version of SQL 2000. All
connection strings have been changed to the enterprise (With vendor help).
The connection is established and according to a trace from Profiler, all
queries are running and inserting the data in the right table. But there is
no data in the tables! No errors either.
> One of my tests was to point it to another MSDE and it worked fine. What
is different between these two versions that will cause data not to be
written to the tables.
> Thanks