SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 2 of 2

Thread: SQL ERROR 2

  1. #1
    Member
    Join Date
    Nov 2001
    Location
    Davenport, IA
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    SQL ERROR 2

    User Dave Parker (parker_dave@hotmail.com.nospam) posted:

    ------=_NextPart_000_0099_01C1B2DE.0B8601A0
    Content-Type: text/plain;
    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable

    Everyone,=20

    Sorry about the previous post. I had an extra =3D in the code I posted, and=
    when I tried to edit the post it really got messed up. Anyway, I'll try it=
    again. I am trying to execute a SQL statement in TSL. I get a -10162 - SQL=
    syntax error. Here is the code. I can run it in Query Analyzer, but when I=
    try to string it to execute from TSL I get the error. When I look at the s=
    tring in the debugger, it seems to have truncated it after the second FROM =
    - in the middle of the "JOIN t_mp_office mp (nolock)".=20

    Is there a limitation on the length of the string?

    sqlOfficeByAcct =3D "";
    sqlOfficeByAcct =3D "IF(SELECT count(mp.office_number)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " FROM t_mp_office mp (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " JOIN t_ps_account ps (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " ON ps.mp_office_list_id =3D mp.mp_o=
    ffice_list_id";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " WHERE ps.ps_account =3D 550000";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " AND mp.office_number =3D 'ALL' ) =
    =3D1";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " BEGIN";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " SELECT o.office_number, o.name";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " FROM t_office o (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " WHERE closed =3D 'N'";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " END";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " ELSE";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " BEGIN";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " SELECT DISTINCT o.office_number, o.=
    name";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " FROM t_office o (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " JOIN t_mp_office mp (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " ON o.office_number =3D mp.office_nu=
    mber";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " JOIN t_ps_account ps (nolock)";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " ON ps.mp_office_list_id =3D mp.mp_o=
    ffice_list_id";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " WHERE ISNUMERIC(mp.office_number) =
    =3D 1";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " AND ps.ps_account =3D 550000";
    sqlOfficeByAcct =3D sqlOfficeByAcct & " END";

    Thanks Again,=20

    Dave

    ------=_NextPart_000_0099_01C1B2DE.0B8601A0
    Content-Type: text/html; charset=US-ASCII
    Content-Transfer-Encoding: 7bit

    < !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
    <META content="MSHTML 6.00.2600.0" name=GENERATOR>
    <BODY bgColor=#ffffff>


    the code I posted, and when I tried to edit the post it really got messed up.
    Anyway, I'll try it again. I am trying to execute a SQL statement in TSL. I get
    a -10162 - SQL syntax error. Here is the code. I can run it in Query Analyzer,
    but when I try to string it to execute from TSL I get the error. When I look at
    the string in the debugger, it seems to have truncated it after the second FROM
    - in the middle of the <FONT face="Times New Roman" color=#008000>"JOIN
    t_mp_office mp (nolock)". </FONT></FONT></DIV>
    sqlOfficeByAcct &amp; </FONT><FONT color=#008000 size=2>" JOIN t_ps_account ps
    &amp; </FONT><FONT color=#008000 size=2>" WHERE ps.ps_account =
    color=#008000 size=2>" BEGIN"</FONT><FONT size=2>;
    sqlOfficeByAcct =
    sqlOfficeByAcct &amp; </FONT><FONT color=#008000 size=2>" SELECT
    o.office_number, o.name"</FONT><FONT size=2>;
    sqlOfficeByAcct =
    sqlOfficeByAcct &amp; </FONT><FONT color=#008000 size=2>" FROM t_office o
    sqlOfficeByAcct &amp; </FONT><FONT color=#008000 size=2>" ON o.office_number =
    &amp; </FONT><FONT color=#008000 size=2>" JOIN t_ps_account ps
    &amp; </FONT><FONT color=#008000 size=2>" WHERE ISNUMERIC(mp.office_number) =
    color=#008000 size=2>" AND ps.ps_account = 550000"</FONT><FONT

    IMPORTANT EMAIL ADDRESSES:

    Post message: winrunner@yahoogroups.com

    Subscribe: winrunner-subscribe@yahoogroups.com

    Unsubscribe: winrunner-unsubscribe@yahoogroups.com

    ***To unsubscribe via email: From your email program, send a blank message to winrunner-unsubscribe@yahoogroups.com.

    *To reply to the entire list, use the Reply button in your email program

    *To respond offline to an individual, locate the sender's email address in the email message

    *To view archives go to groups.yahoo.com

    Direct any questions, comments, or issues about this list to...

    Eric Marx

    ------=_NextPart_000_0099_01C1B2DE.0B8601A0--


  2. #2
    Senior Member
    Join Date
    Nov 2001
    Location
    Wilmington, NC, USA
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: SQL ERROR 2

    User (rrkey@visionair.com.nospam) posted:

    ------_=_NextPart_001_01C1B310.FC6EC110
    Content-Type: text/plain;
    charset="iso-8859-1"

    Yes, there is a limitation on string lengths. Try to utilize multiple
    variables.
    sqlOfficeByAcct = "";
    sqlOfficeByAcct = "IF(SELECT count(mp.office_number)";
    sqlOfficeByAcct = sqlOfficeByAcct & " FROM t_mp_office mp (nolock)";
    sqlOfficeByAcct = sqlOfficeByAcct & " JOIN t_ps_account ps (nolock)";
    sqlOfficeByAcct = sqlOfficeByAcct & " ON ps.mp_office_list_id =
    mp.mp_office_list_id";
    sqlOfficeByAcct = sqlOfficeByAcct & " WHERE ps.ps_account = 550000";
    sqlOfficeByAcct = sqlOfficeByAcct & " AND mp.office_number = 'ALL' ) =1";
    sqlOfficeByAcct = sqlOfficeByAcct & " BEGIN";
    sqlOfficeByAcct = sqlOfficeByAcct & " SELECT o.office_number, o.name";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " FROM t_office o (nolock)";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " WHERE closed = 'N'";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " END";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " ELSE";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " BEGIN";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " SELECT DISTINCT o.office_number,
    o.name";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " FROM t_office o (nolock)";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " JOIN t_mp_office mp (nolock)";
    sqlOfficeByAcct1 = sqlOfficeByAcct & " ON o.office_number =
    mp.office_number";
    sqlOfficeByAcct2 = sqlOfficeByAcct & " JOIN t_ps_account ps (nolock)";
    sqlOfficeByAcct2 = sqlOfficeByAcct & " ON ps.mp_office_list_id =
    mp.mp_office_list_id";
    sqlOfficeByAcct2 = sqlOfficeByAcct & " WHERE ISNUMERIC(mp.office_number) =
    1";
    sqlOfficeByAcct2 = sqlOfficeByAcct & " AND ps.ps_account = 550000";
    sqlOfficeByAcct2 = sqlOfficeByAcct & " END";

    then run sqlOfficeByAcct,sqlOfficeByAcct1, sqlOfficeByAcct2

    Scott

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 06:25 PM.

Copyright BetaSoft Inc.