locked
UNION ALL in VB6 RRS feed

  • Question

  • Dear Friends,

    My software is made in VB6 , one part of this software is passing parameters to SQL store procedure (this SP is creating a report  for grid). everythig was working fine untill i have added UNION ALL in that SQL SP ,

    now vb code is keep calling this SP for so many tmies(10 in a second) , if i manually run this SP with the same parameters , its runing fine. But with vb code its not working

    Please help

    • Moved by Youen Zen Wednesday, June 27, 2012 3:18 AM To provide better support (From:Visual Basic General)
    Tuesday, June 26, 2012 6:11 AM

Answers

All replies

  • This forum is not for vintage VB, nevertheless is your question in fact no VB question but an SQL transact question, for that is this forum.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads


    Success
    Cor

    • Proposed as answer by Heslacher Tuesday, June 26, 2012 6:36 AM
    • Marked as answer by Kalman Toth Thursday, July 5, 2012 10:19 PM
    Tuesday, June 26, 2012 6:30 AM
  • Dear Friends,

    My software is made in VB6 , one part of this software is passing parameters to SQL store procedure (this SP is creating a report  for grid). everythig was working fine untill i have added UNION ALL in that SQL SP ,

    now vb code is keep calling this SP for so many tmies(10 in a second) , if i manually run this SP with the same parameters , its runing fine. But with vb code its not working

    Please help

    • Merged by Naomi N Wednesday, June 27, 2012 5:35 PM Same question
    Tuesday, June 26, 2012 6:43 AM
  • But with vb code its not working

    Hello Parixit,

    What do you mean with "not working"; do you get an error message (and which one) or do you get a wrong result or ...?


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, June 26, 2012 7:02 AM
  • Hi,

    Whats the error you are getting in vb? also posting lines where the error occurs also will help

    Regards
    satheesh 


    Tuesday, June 26, 2012 7:25 AM
  • Have a look at this article on my web site:
    http://www.sommarskog.se/query-plan-mysteries.html

    Yes, it's a tad long, but I wrote it, not having to say the same things over and over again.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 26, 2012 7:30 AM
  • hi Olaf,
    sorry for the poor structure of sentences. My VB6 code was calling a SQL store procedure, and this procedure is a report procedure (means it have SELECT statement in the bottom). everything was working fine. the reports are comming properly when user is hitting the Button.

    , Now as pre the new requiremt i have provided  below statement in SQL Sp

    SELECT columns FROM TableA
    UNION ALL
    SELECT columns FROM TableB

    ... Baaaaam, the VB6 did not like it and it has started calling this procedure for so many times (like 10 times per second).

    If i call this procedure manually , its giving the desired resultset (not infinite or somehitng, it's just 12 lines of report).

    Please do the needful.

    Tuesday, June 26, 2012 11:28 AM
  • ... Baaaaam, the VB6 did not like it and it has started calling this procedure for so many times (like 10 times per second).

    What do you mean by VB6 did not like this change and started to behave on its own? A program will never behave like that unless you have a bug in there.

    Regards
    Satheesh

    • Proposed as answer by Naomi N Tuesday, June 26, 2012 4:53 PM
    Tuesday, June 26, 2012 11:33 AM
  • I'm not an expert on VB6 and it is impossible to accurately know what is going on without looking at the source code, but it could be that the stored procedure has an error that you are just not seeing.  Stored procedures will keep executing even if there is an error and produce a result set. Consider the following example:

    CREATE PROCedure dbo.foobar
    AS
    BEGIN
       RAISERROR('this ie an error con
    Tuesday, June 26, 2012 11:44 AM
  • The only answer, is to learn enough about how the VB6 half of your process works, to understand how the two items work together.

    You have a system, a blend of VB6 logic written and designed in conjunction with the SQL Server proc, and they both depend on one another to work properly together, things like design principles, data patterns, etc.    So although it may seem like the proc is to blame, that's not exactly the case: what is to blame, is how the VB6 works in conjunction with how the proc works, meaning your only answer is to understand the VB6 half of the puzzle more.

    Tuesday, June 26, 2012 1:30 PM
  • If your VB code keeps calling the procedure over and over again, it sounds like there some error handling that goes wrong. Have you tried debugging the program?

    When you have received the result set, do you pass it to the report, or do you make edits in the result set?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 26, 2012 1:34 PM
  • Hello Parixit

    1. - You Test The SQL connect  (VB6 VS SQL SERVER)

    2.-  You Check The Collation Database (Case Sentitive Options ) Can  Not Working In ANY VB6 CODE

    3.- If above options is correct.. Please Check SQL SERVER PROFILER.. Where you can monitoring what happens in you Process

    Any Result.. Please Let me know..

    Best Regards


    Please vote if you find this posting was helpful or Mark it as answered. Leonardo Martínez

    Tuesday, June 26, 2012 1:52 PM
  • Hi Leo,
    I have kept the sql server profiler while running that vb code. and i have copied the same SP from profiler with same parameters passed to it. i have run it manually in query analyser . It's running perfectly when i run it manually

    @ all guys who helped me to solve this issue

    I have tried one work-around and it worked. in the SQL SP , I have copied the result set in to a temporary table and then displayed it by using SELECT . please find below

    SELECT *
    INTO #TempTable
    FROM
    (
    SELECT columns FROM TableA
    UNION ALL
    SELECT columns FROM TableB
    )

    SELECT Columns FROM #TempTable

    Before this workaround it just had

    SELECT columns FROM TableA
    UNION ALL
    SELECT columns FROM TableB

    which was logically correct , but somehow temp table workaround has worked well for me now.

    Thursday, June 28, 2012 4:41 AM
  • Its strange!!!, I cant see any reason for union all not working.

    anyways good that its working for you

    Regards
    Satheesh


    Thursday, June 28, 2012 7:10 AM
  • Hello Parixit

    I see all fine in this Query.

    Please Check the user Database Permissions:

    Best Regards


    Please vote if you find this posting was helpful or Mark it as answered. Leonardo Martínez

    Thursday, June 28, 2012 1:53 PM