Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: Exécution de plusieurs requêtes consécutives (MS Query)

    The idea is to retrieve in an Excel QueryTable the result set of a multiple SQL statement script. The query is executed by MS Query. The script has to run as a whole to preserve query context.

    Some simplified query examples :

    declare @MM varchar(2)
    select @MM = "OK"
    select @MM

    or

    create table #MM (Field1 int null)
    insert into #MM
    select 5
    select * from #MM

    With the current configuration, only the 1st statement is actually executed and the rest is simply ignored. The SQL scripts shown above generate no result set as they only go as far as the select statement (1) and the create one (2).

    Environnement details:

    SGBD: Sybase (ASE 12)
    ODBC: ASE 1252 (4.20.00.67)
    MS Excel / Query: 10.6308.6839 (SP3), 2002

    I know executing multiple statements is possible since I already managed to in the past. In fact, I asked friends to execute these same scripts in their environnement, very similar to mine by the way - Sybase ASE / ODBC 12.5, - and they did carry through with success and brought results. So it must be a configuration issue.

    Thanks
    Last edited by Snowball98; 06-23-08 at 06:41.

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    In English, por favor?
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stuschmied
    In English, por favor?
    tsk, tsk, we must tell you about google language tools one day

    Bonjour
    Hi how are ya

    Mon problème consiste à pouvoir exécuter, dans une plage de données externes Excel, un script SQL contenant plusieurs commandes autocommitantes en une seule fois (notamment pour préserver le contexte entre les commandes).
    My problem is to be able to execute in a range of external data Excel, SQL script autocommitantes containing several commands at once (in particular to preserve the context between orders).

    Le script s'exécute via MS Query (objet QueryTables), soit via Données / Raffraîchir, soit par la commande VBa .Refresh.
    The script runs through MS Query (subject QueryTables), or via Data / Refresh or by the command VBa. Refresh.

    Dans l'état actuel de paramétrage, le script s'arrête après l'exécution de la 1ère requête de la liste, sans qu'aucune erreur ne soit signalée (ce qui est normal vu que le script n'en contient pas). Les requêtes données en exemples ne ramènent aucun résultat car elles s'arrêtent au 1er select (1) et au create (2).
    Under the current setup, the script stops after the execution of the 1st application of the list, without any error being reported (which is normal because the script does not contain). The complaints data examples not bring any results because they stop at the 1st select (1) and create (2).

    Précisions nécessaires sur l'environnement:
    Details about the environment:

    Je sais que cette exécution est possible car j'en ai déjà fait par le passé. Par ailleurs j'ai réussi à exécuter ces mêmes scripts en exemple sur d'autres environnements pourtant très semblables (Sybase ASE / ODBC 12.5), avec les résultats attendus. Je suppose qu'il est question d'un paramètre à un niveau de la chaîne...
    I know that this execution is possible because I have already done in the past. On the other hand I managed to run the same scripts as an example to other environments very similar (Sybase ASE / ODBC 12.5), with expected results. I suppose it is a question of setting a level of the channel ...

    Merci
    Ta very much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Sacre bleau! Out of my league, but maybe someone else can help!

    Thanx for the xlation.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Jun 2008
    Posts
    3
    Sorry guys, I kinda forgot this forum was in English. This post is a copy/paste of a post I left on a French one, so you must forgive me...

    Anyways, the idea is to figure out what I need to modify in my environment in order to be able to submit multiple SQL statements in one refresh in an Excel QueryTable (MS Query / ODBC). So far, only one is actually carried out, the others are simply ignored.

  6. #6
    Join Date
    Jun 2008
    Posts
    3

    Data import: Execution of multiple SQL statements (MS Query)

    This post is an English translation of a previously posted request in French.

    The idea is to retrieve in an Excel QueryTable the result set of a multiple SQL statement script. The query is executed by MS Query. The script has to run as a whole to preserve query context.

    Some simplified query examples :

    declare @MM varchar(2)
    select @MM = "OK"
    select @MM

    or

    create table #MM (Field1 int null)
    insert into #MM
    select 5
    select * from #MM

    With the current configuration, only the 1st statement is actually executed and the rest is simply ignored. The SQL scripts shown above generate no result set as they only go as far as the select statement (1) and the create one (2).

    Environnement details:

    SGBD: Sybase (ASE 12)
    ODBC: ASE 1252 (4.20.00.67)
    MS Excel / Query: 10.6308.6839 (SP3), 2002

    I know executing multiple statements is possible since I already managed to in the past. In fact, I asked friends to execute these same scripts in their environnement, very similar to mine by the way - Sybase ASE / ODBC 12.5, - and they did carry through with success and brought results. So it must be a configuration issue.

    Thanks

  7. #7
    Join Date
    Aug 2008
    Posts
    1
    I am having the same problem. Any ideas as to what configuration change I need to make?

  8. #8
    Join Date
    Mar 2009
    Posts
    2
    Having the same problem here on my Windows XP machine. It does work on a Windows Server 2003 though.
    Anyone managed to solve this yet?

  9. #9
    Join Date
    Mar 2009
    Posts
    2
    Problem solved!

    I just followed the instructions at Sybase ODBC - ODBC driver fails to return results for SQL which uses variables and added the following registry key:
    Software\ODBC\ODBC.INI\Your-DSN\SkipDMLInBatches=1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •