locked
Pass-through query RRS feed

  • Question

  • Hi,

    I use Pass through querys to link to our sql server,

    What i want to do is: In the pass through construct a SQL temp table

    Now the error i am getting is this

    Pass through query with ReturnRecords set to true did not return any records.

    Any advise would be great.

    thanks


    David
    Wednesday, October 5, 2011 10:40 PM

Answers

  • Hi Andrey,

    I have worked it out.

    What i needed to do was create a Access pass-through stored procedure first as below

    Then create another Access pass-through and call the procedure by adding this one line

    usp_aSpecialtyData

    So we end up with 2 pass-through queries,

    I am going to work on joining them

    thanks for your help

    CREATE PROC usp_aSpecialtyData
    AS
    Begin
    
    CREATE TABLE #aSpecialty(
    	[IPM Value] [nvarchar](255) NULL,
    	[Bundled Value] [nvarchar](255) NULL,
    	[SpecialtyDescription] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ADDMED','GENMED','Addiction Medicine')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ALLHEA','ALLHEA','Allied Health')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ANAEST',	'ANAEST',	'Anaesthetics')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BRESUR',	'GENSUR',	'Breast Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BURMED',	'GENSUR',	'Burns')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CARDIO',	'GENMED',	'Cardiology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CHAEMA',	'GENMED',	'Clinical Haematology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CHEMOT',	'MEDONC',	'Chemotherapy')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('COLSUR',	'GENSUR',	'Colorectal Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('COMM',	'COMM',	'Community Service')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DENTAL',	'DENTAL',	'Dental Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DERMAT',	'DERMAT',	'Dermatology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DIABET',	'DIABET',	'Diabetes')
    SELECT * INTO #aSpecialty
    From #aSpecialty
    
    
    Select * from #aSpecialty
    End

     


    David
    • Marked as answer by KIWI DAVE Thursday, October 6, 2011 12:02 AM
    Thursday, October 6, 2011 12:02 AM

All replies

  • Hm, I'm afraid to ask whether you tried to change .ReturnRecords property of this query to false. :)
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, October 5, 2011 10:42 PM
  • Hi Andrey,

    No, i have not, should I?


    David
    Wednesday, October 5, 2011 10:46 PM
  • Hi David,

    yes, try it. At least, you error mesage says you should. You should check this this property according to what you are doing. Right now, as I understand, you are trying to create a table, that's not something like SELECT statement, which returns records.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, October 5, 2011 10:57 PM
  • Hi,

    I set the .ReturnRecords property to No, and i recieve no errors but i recieve no records.

    Here is my script. What do you think?

    My logic is, create the tempTable using Access Pass-Through then run the query

    Drop Table #aSpecialty
    CREATE TABLE #aSpecialty(
    	[IPM Value] [nvarchar](255) NULL,
    	[Bundled Value] [nvarchar](255) NULL,
    	[SpecialtyDescription] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ADDMED','GENMED','Addiction Medicine')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ALLHEA','ALLHEA','Allied Health')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ANAEST',	'ANAEST',	'Anaesthetics')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BRESUR',	'GENSUR',	'Breast Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BURMED',	'GENSUR',	'Burns')

     


    David
    Wednesday, October 5, 2011 11:04 PM
  • You create a table, insert some records into it and that's all. What do you want to get back from this query? #aSpeciality table content? If so, add SELECT * FROM #aSpeciality and don't forget to set .Returnrecords back to true. If you want smth else, please, describe a bit wider.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, October 5, 2011 11:10 PM
  • Hi Andrey

    This what i have, i still get the

    Pass through query with ReturnRecords set to true did not return any records.

    Drop Table  #aSpecialty
    CREATE TABLE #aSpecialty(
    	[IPM Value] [nvarchar](255) NULL,
    	[Bundled Value] [nvarchar](255) NULL,
    	[SpecialtyDescription] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ADDMED','GENMED','Addiction Medicine')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ALLHEA','ALLHEA','Allied Health')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ANAEST',	'ANAEST',	'Anaesthetics')
    
    Select * from #aSpecialty
    

     


    David
    Wednesday, October 5, 2011 11:29 PM
  • Sorry, have no SQL Server installed on the fresh OS, that's why I can't test exactly your case. But, if you run your pass-through without "select" part, can you see #aSpecialty table in the database? After this table is created, can you run another pass-through query with .ReturnRecords = True which contains only SELECT * FROM #aSpecialty? 

    BTW, if the content of this query isn't going to be changed, you can create a stored procedure with all these actions. This will shorten the text of your pass-through query, more comfortable.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, October 5, 2011 11:56 PM
  • Hi Andrey,

    I have worked it out.

    What i needed to do was create a Access pass-through stored procedure first as below

    Then create another Access pass-through and call the procedure by adding this one line

    usp_aSpecialtyData

    So we end up with 2 pass-through queries,

    I am going to work on joining them

    thanks for your help

    CREATE PROC usp_aSpecialtyData
    AS
    Begin
    
    CREATE TABLE #aSpecialty(
    	[IPM Value] [nvarchar](255) NULL,
    	[Bundled Value] [nvarchar](255) NULL,
    	[SpecialtyDescription] [nvarchar](255) NULL
    ) ON [PRIMARY]
    
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ADDMED','GENMED','Addiction Medicine')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ALLHEA','ALLHEA','Allied Health')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('ANAEST',	'ANAEST',	'Anaesthetics')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BRESUR',	'GENSUR',	'Breast Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('BURMED',	'GENSUR',	'Burns')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CARDIO',	'GENMED',	'Cardiology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CHAEMA',	'GENMED',	'Clinical Haematology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('CHEMOT',	'MEDONC',	'Chemotherapy')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('COLSUR',	'GENSUR',	'Colorectal Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('COMM',	'COMM',	'Community Service')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DENTAL',	'DENTAL',	'Dental Surgery')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DERMAT',	'DERMAT',	'Dermatology')
    INSERT INTO #aSpecialty([IPM Value],[Bundled Value],[SpecialtyDescription])
    	Values ('DIABET',	'DIABET',	'Diabetes')
    SELECT * INTO #aSpecialty
    From #aSpecialty
    
    
    Select * from #aSpecialty
    End

     


    David
    • Marked as answer by KIWI DAVE Thursday, October 6, 2011 12:02 AM
    Thursday, October 6, 2011 12:02 AM
  • And why not to create this sp once via SSMS and then just call it via pass-through in Access?
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Thursday, October 6, 2011 12:08 AM
  • Hi Andrey,

    I am trying to reduce the amount of saved objects in the Sql warehouse.

    Our Schema is getting larger and the demand for more saved views and sp. the other memebers of the team want to push Access to its limits.

    And so do i, so adhock users can get the performance they need but dont need the space in the warehouse.

    :)


    David
    Thursday, October 6, 2011 12:12 AM