Dynamic SQL, Parameter exceeds 128 characters

Dynamic SQL, Parameter exceeds 128 characters

Post by Garret Goldstei » Tue, 25 May 1999 04:00:00



Hi,

I created a stored procedure that uses dynamic sql.  All I pass is the
criteria.  Because my criteria has commas and needs to be read as one
long string/parameter, I use the brackets.  If my criteria exceeds more
than 128 characters I get an error.  I read the BOL and found that this
is a limitation in sql 7.  I also found that if I use quotes I don't
have this problem.  So when I call my sp from Query Analyzer I use
double quotes...the problem is how do I call the stored procedure in
VB?  My code currenlty has:    sSql = "{call spCountSearch([" &
sCriteria & "])}"   How can I replace the brackets with Double quotes?
Is there away around this bug?

thanks

 
 
 

Dynamic SQL, Parameter exceeds 128 characters

Post by Tony Rogerso » Wed, 26 May 1999 04:00:00


Garret,

I'm more interested in why you can't exec the sql than the quotes, this
should be ok.

Post back your stored procedure.

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


Quote:> Hi,

> I created a stored procedure that uses dynamic sql.  All I pass is the
> criteria.  Because my criteria has commas and needs to be read as one
> long string/parameter, I use the brackets.  If my criteria exceeds more
> than 128 characters I get an error.  I read the BOL and found that this
> is a limitation in sql 7.  I also found that if I use quotes I don't
> have this problem.  So when I call my sp from Query Analyzer I use
> double quotes...the problem is how do I call the stored procedure in
> VB?  My code currenlty has:    sSql = "{call spCountSearch([" &
> sCriteria & "])}"   How can I replace the brackets with Double quotes?
> Is there away around this bug?

> thanks


 
 
 

Dynamic SQL, Parameter exceeds 128 characters

Post by Garret Goldstei » Wed, 26 May 1999 04:00:00


What do you mean, "Exec the sql and than the quotes"?  Could you show me an
example?

Here is the stored procedure:

/*This is a test sp for the BIRS prototype application, problem: limit to 128
characters between quotes for criteria*/





The criteria will be one or more Contains() statements (which is another
problem, the Near() function doesn't allow you to specify the distance
between two words and I need this...any ideas)

Thanks,


> Garret,

> I'm more interested in why you can't exec the sql than the quotes, this
> should be ok.

> Post back your stored procedure.

> --
> Tony Rogerson MCP,SQL Server MVP
> Torver Computer Consultants Ltd.



> > Hi,

> > I created a stored procedure that uses dynamic sql.  All I pass is the
> > criteria.  Because my criteria has commas and needs to be read as one
> > long string/parameter, I use the brackets.  If my criteria exceeds more
> > than 128 characters I get an error.  I read the BOL and found that this
> > is a limitation in sql 7.  I also found that if I use quotes I don't
> > have this problem.  So when I call my sp from Query Analyzer I use
> > double quotes...the problem is how do I call the stored procedure in
> > VB?  My code currenlty has:    sSql = "{call spCountSearch([" &
> > sCriteria & "])}"   How can I replace the brackets with Double quotes?
> > Is there away around this bug?

> > thanks

 
 
 

Dynamic SQL, Parameter exceeds 128 characters

Post by Tony Rogerso » Wed, 26 May 1999 04:00:00


Bad english on my part, I thought you where having problems with the EXEC
statement.

I've not used contains yet, but there is a proximity parameter, have you
tried this ???

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


> What do you mean, "Exec the sql and than the quotes"?  Could you show me
an
> example?

> Here is the stored procedure:

> /*This is a test sp for the BIRS prototype application, problem: limit to
128
> characters between quotes for criteria*/





> The criteria will be one or more Contains() statements (which is another
> problem, the Near() function doesn't allow you to specify the distance
> between two words and I need this...any ideas)

> Thanks,


> > Garret,

> > I'm more interested in why you can't exec the sql than the quotes, this
> > should be ok.

> > Post back your stored procedure.

> > --
> > Tony Rogerson MCP,SQL Server MVP
> > Torver Computer Consultants Ltd.



> > > Hi,

> > > I created a stored procedure that uses dynamic sql.  All I pass is the
> > > criteria.  Because my criteria has commas and needs to be read as one
> > > long string/parameter, I use the brackets.  If my criteria exceeds
more
> > > than 128 characters I get an error.  I read the BOL and found that
this
> > > is a limitation in sql 7.  I also found that if I use quotes I don't
> > > have this problem.  So when I call my sp from Query Analyzer I use
> > > double quotes...the problem is how do I call the stored procedure in
> > > VB?  My code currenlty has:    sSql = "{call spCountSearch([" &
> > > sCriteria & "])}"   How can I replace the brackets with Double quotes?
> > > Is there away around this bug?

> > > thanks

 
 
 

Dynamic SQL, Parameter exceeds 128 characters

Post by Garret Goldstei » Wed, 26 May 1999 04:00:00


No problem...but back to the original problem.  Is there a way around not having
the criteria I pass cut off at 128 characters?

Here is what is on the books online:
If you enclose a character string in double quotation marks that is more than
128 characters, the application may receive this error. When the
QUOTED_IDENTIFIERS option is set on (SET QUOTED_IDENTIFIERS ON), Microsoft? SQL
Server? expects quoted identifiers to be enclosed in double quotation marks ()
and data values to be enclosed in single quotation marks (). In the case of
character parameters of stored procedures, data values enclosed in double
quotation marks are accepted by SQL Server if the character string is less than
128 characters. They should be considered syntax errors by SQL Server and
generate an error.

You can also see this in ODBC applications using the SQL Server ODBC driver
versions 2.50.0121 and later. These drivers set QUOTED_IDENTIFIERS ON when run
against a SQL Server version 6.x or later server so that the drivers behavior
more closely matches the ANSI and ODBC standards. ODBC applications which use
double quotation marks for parameter values may see this behavior after you
upgrade to SQL Server 6.x or later and the ODBC 2.50.0121 or later driver.

I am afraid it is an ODBC limitation...we do have away around this, but it means
passing the who sql string...which is slower.

Thanks,


> Bad english on my part, I thought you where having problems with the EXEC
> statement.

> I've not used contains yet, but there is a proximity parameter, have you
> tried this ???

> --
> Tony Rogerson MCP,SQL Server MVP
> Torver Computer Consultants Ltd.



> > What do you mean, "Exec the sql and than the quotes"?  Could you show me
> an
> > example?

> > Here is the stored procedure:

> > /*This is a test sp for the BIRS prototype application, problem: limit to
> 128
> > characters between quotes for criteria*/






> > The criteria will be one or more Contains() statements (which is another
> > problem, the Near() function doesn't allow you to specify the distance
> > between two words and I need this...any ideas)

> > Thanks,


> > > Garret,

> > > I'm more interested in why you can't exec the sql than the quotes, this
> > > should be ok.

> > > Post back your stored procedure.

> > > --
> > > Tony Rogerson MCP,SQL Server MVP
> > > Torver Computer Consultants Ltd.



> > > > Hi,

> > > > I created a stored procedure that uses dynamic sql.  All I pass is the
> > > > criteria.  Because my criteria has commas and needs to be read as one
> > > > long string/parameter, I use the brackets.  If my criteria exceeds
> more
> > > > than 128 characters I get an error.  I read the BOL and found that
> this
> > > > is a limitation in sql 7.  I also found that if I use quotes I don't
> > > > have this problem.  So when I call my sp from Query Analyzer I use
> > > > double quotes...the problem is how do I call the stored procedure in
> > > > VB?  My code currenlty has:    sSql = "{call spCountSearch([" &
> > > > sCriteria & "])}"   How can I replace the brackets with Double quotes?
> > > > Is there away around this bug?

> > > > thanks

 
 
 

Dynamic SQL, Parameter exceeds 128 characters

Post by Tibor Karasz » Thu, 27 May 1999 04:00:00


Garret,

I haven't used the Ft features much at all, and you've probably already
tried this, but:

Is it possible to use single quotes instead of double quotes?
This should get rid of the fact that some component (odbc?) tries to parse
the stuff as an identifier.
You might have to (if possible at all) try doubling them, trippling etc.

--
Tibor Karaszi
MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


>No problem...but back to the original problem.  Is there a way around not
having
>the criteria I pass cut off at 128 characters?

>Here is what is on the books online:
>If you enclose a character string in double quotation marks that is more
than
>128 characters, the application may receive this error. When the
>QUOTED_IDENTIFIERS option is set on (SET QUOTED_IDENTIFIERS ON), Microsoft?
SQL
>Server? expects quoted identifiers to be enclosed in double quotation marks
()
>and data values to be enclosed in single quotation marks (). In the case
of
>character parameters of stored procedures, data values enclosed in double
>quotation marks are accepted by SQL Server if the character string is less
than
>128 characters. They should be considered syntax errors by SQL Server and
>generate an error.

>You can also see this in ODBC applications using the SQL Server ODBC driver
>versions 2.50.0121 and later. These drivers set QUOTED_IDENTIFIERS ON when
run
>against a SQL Server version 6.x or later server so that the drivers
behavior
>more closely matches the ANSI and ODBC standards. ODBC applications which
use
>double quotation marks for parameter values may see this behavior after you
>upgrade to SQL Server 6.x or later and the ODBC 2.50.0121 or later driver.

>I am afraid it is an ODBC limitation...we do have away around this, but it
means
>passing the who sql string...which is slower.

>Thanks,

 
 
 

1. Inserting Ascii(128-255) characters into SQL Server database

I trying to insert high ascii characters into a SQL Server
database using Microsoft's JDBC driver. When inserted the
characters are converted to ?.

The following code doesn't not work.
String s = new String("t's");
lPreparedStatement.setString(1,s);    
lPreparedStatement.executeUpdate();

Selecting this value using Query Analyzer gives t?s.

I was able to get this to work by:

String s = new String("t's");
lPreparedStatement.setString(1,new String( s.getBytes("ISO-
8859-1")));    
lPreparedStatement.executeUpdate();

Has anyone come across this issue and knows of a simpler
way to resolve this issue?

2. Select a range of records

3. 128 character SQL String length limit??

4. DBGrid Freeze

5. probs in Assigning more then 128 characters to a variable in SQL2000

6. Keeping focus on top level form and grid

7. 128 Character limit on Insert?

8. Interactive Stored Procedure.

9. 128 Character Limit?

10. Receiving a 128 character limit error

11. how use KEYB for more than 128 characters?

12. dynamic T-SQL for dynamic parameter

13. dynamic SQL statement's length exceeding 4000 -- cannot use sp_executesql