Solved

sp returning variables to ms access

Posted on 1998-11-09
9
230 Views
Last Modified: 2008-07-03
Greetings,

I've build a sp which returns variables (instead of a recordset based on tables) to ms access.
The querydef which I declared in access has the property returnrecords set to true, but access complains that my sp doesn't return any records. Whenever I run it in the query-tool of SqlServer it returns the correct information.

Can anyone help me with this?

By the way: I use stored procedures extensivelly, but never before did it return variables.

Hope you can help,

Jeroen Westera
0
Comment
Question by:JeroenW
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091108
You need to return the parameters as one record and use recordset.Fields to get them in Access.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091109
Dear spiridonov,

I return the variables using
SELECT @Variable1, @Variable2, @Variable3, etc

The trouble is that Access does not recognize the fact that the sp has output and generates an error that the sp doesn't have output while I specified that it did (and it does!).

Hope you can help,

Jeroen Westera


0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091110
Try doing a SELECT @var1,...FROM any_table_with_one_row (or using where , that will return one row from any table.)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:JeroenW
ID: 1091111
Dear spiridonov,

Thanks for your suggestion. I've tried it (I had already, but I double checked) and it did not work. I even tried aliassing the variables by using: SELECT @Var1 as Var1, ... as Var15 FROM TheTable WHERE ...

The sp again had the correct results from the SqlSvr query-tool, but still Access complained.

I found an article on Technet (PSS ID Number: Q152174) which applies to my question. The only problem now is how to convert the example-code to VBA.
- Do I have to declare the ODBC-dll?
- Do I have to define the SqlServer variable-types (constants)?

Anyway,
I would like some example code (VBA) on how to handle this.

Spiridonov: If you do not have Technet, I will be happy to send you the article.

Thanks in advance,

Jeroen Westera
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091112
I do have Technet. The solution ,that i suggested (SELECT...) worked fine for me. Please post the code you are having difficulty with.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091113
Dear Spiridonov,

Here is the select-statement that(should) return the values in the variables (The variable-names are in dutch, but I think you get the idea):

/* return the outcome... */
 SELECT @Consistent AS Consistent, @SrtDv as SrtDv, @TypeVaststelling as TypeVaststelling, @TotAantDvsPerJaar as TotAantDvsPerJaar, @TotAantDvsPerMaandInPolis as TotAantDvsPerMaandInPolis, @AantPolismaandMeerDanNulDvs as AantPolismaandMeerDanNulDvs, @GemAantDvsInPolis as GemAantDvsInPolis FROM R05_AV_CONTRACT WHERE NR_CONTR_AV = @NrContrAv

/* I've tried these variants, but no cigar...
 SELECT @Consistent AS Consistent, @SrtDv as SrtDv, @TypeVaststelling as TypeVaststelling, @TotAantDvsPerJaar as TotAantDvsPerJaar, @TotAantDvsPerMaandInPolis as TotAantDvsPerMaandInPolis, @AantPolismaandMeerDanNulDvs as AantPolismaandMeerDanNulDvs, @GemAantDvsInPolis as GemAantDvsInPolis

Or

 SELECT @Consistent , @SrtDv , @TypeVaststelling , @TotAantDvsPerJaar , @TotAantDvsPerMaandInPolis , @AantPolismaandMeerDanNulDvs , @GemAantDvsInPolis  FROM R05_AV_CONTRACT WHERE NR_CONTR_AV = @NrContrAv
*/

Thanks in advance,

Jeroen Westera
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091114
Sorry, for not explaining proprly. I wanted to see the code you use to execute sp in Access.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091115
Spiridonov,

Here it is:

Dim db as Database
Dim qdDV As QueryDef
Dim rsDV As Recordset

Set db = CurrentDb()
Set qdDV = db.CreateQueryDef("")

qdDV.Connect = glbODBCConnect
qdDV.ReturnsRecords = True
qdDV.sql = "EXEC sp_BEPAAL_DVS " & NrOpdr & "," & NrWg & "," & NrVest & "," & NrContr & "," & Jaar & ",'" & CdSrtLoon & "'," & intIdCv & ",'" & strCdOP & "'," & intIdVp & "," & lngNrContrAv
Set rsDV = qdDV.OpenRecordset(DB_OPEN_SNAPSHOT)
If rsDV.EOF Then
  Blablabla

Jeroen Westera
0
 
LVL 1

Accepted Solution

by:
mikkon earned 500 total points
ID: 1091116
The problem is probably that SQL Server returns empty recordsets for your first SELECT's. Access uses only the first one of them, and discards the rest, of which the last one contains your real results.

Access is not able to process multiple recordsets. To solve this, add "SET NOCOUNT ON" as the first statement in your stored procedure. If this doesn't help, add it Access code, just before the EXEC part.

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question