Solved

sp returning variables to ms access

Posted on 1998-11-09
9
229 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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