473,885 Members | 2,514 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stored procedure problem

Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo
Nov 12 '05 #1
5 3667
Timppa wrote:
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo


Without the INTO you're just doing a normal select statement. With the
INTO statement it reports that n records affected but does not return
any. You should either:

a) SET NOCOUNT ON at the top of the procedure, then Access won't expect
any records back or

b) if a recordset was required, do the above but then SET NOCOUNT OFF
then select from the table you just created.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #2
this is an .adp i take it? try running your SP thru an ado command
object. there's no reason that is obvious to me why you should be
getting the error if it's running fine thru QA.

i find it better programming practice to exectute SPs thru a command
object anyhow. most importantly if there is a return value or output
parameters it can be captured.

co***@jippii.fi (Timppa) wrote in message news:<37******* *************** ****@posting.go ogle.com>...
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo

Nov 12 '05 #3
Trevor Best <nospam@localho st> wrote in message news:<40******* *************** @auth.uk.news.e asynet.net>...
Timppa wrote:
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo


Without the INTO you're just doing a normal select statement. With the
INTO statement it reports that n records affected but does not return
any. You should either:

a) SET NOCOUNT ON at the top of the procedure, then Access won't expect
any records back or

b) if a recordset was required, do the above but then SET NOCOUNT OFF
then select from the table you just created.


a) I have SET NOCOUNT ON in my sp.

b) I don't need any recordset.
Thanks for reply

I have to continue for looking solution to my problem.

colpo
Nov 12 '05 #4
te********@yaho o.com (Ted Theodoropoulos) wrote in message news:<f5******* *************** ****@posting.go ogle.com>...
this is an .adp i take it? try running your SP thru an ado command
object. there's no reason that is obvious to me why you should be
getting the error if it's running fine thru QA.

i find it better programming practice to exectute SPs thru a command
object anyhow. most importantly if there is a return value or output
parameters it can be captured.

co***@jippii.fi (Timppa) wrote in message news:<37******* *************** ****@posting.go ogle.com>...
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo

I use .adp. I have tried also run my sp thru ado command.
Didn't work :(.
Here is code example of that:
Dim stDocName as String
Dim cmd As New ADODB.Command
stDocName = "myStoredProced ure"
Set cmd = New ADODB.Command
With cmd
.CommandText = stDocName
.CommandType = adCmdStoredProc
' This is valid connection
.ActiveConnecti on = CurrentProject. Connection
.Execute
End With
Colpo
Nov 12 '05 #5
what error message are you getting when you execute the sp thru a
command object specifically. one thing u can do is try and isolate
which sql statement is causing the problem by only running the main
sql statement and slowly joining in your sub queries. try executing
the sp each time in between adding a statements and you should be able
to tell which statement is causing the problem.

co***@jippii.fi (Timppa) wrote in message news:<37******* *************** ****@posting.go ogle.com>...
te********@yaho o.com (Ted Theodoropoulos) wrote in message news:<f5******* *************** ****@posting.go ogle.com>...
this is an .adp i take it? try running your SP thru an ado command
object. there's no reason that is obvious to me why you should be
getting the error if it's running fine thru QA.

i find it better programming practice to exectute SPs thru a command
object anyhow. most importantly if there is a return value or output
parameters it can be captured.

co***@jippii.fi (Timppa) wrote in message news:<37******* *************** ****@posting.go ogle.com>...
Hi,
Could anyone help me with my problem ?
Environment: Access 2000 and Sql Server 2000.

I have a stored procedure as follows:

DROP table1

SELECT alias1.field1,a lias2.field2,ta ble2.field6
INTO table1
FROM table2 INNER JOIN

(SELECT field1
FROM table3 INNER JOIN table4 ON table3.field1 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias1 INNER JOIN

(SELECT field2
FROM table3
INNER JOIN table4 ON table3.field2 = table4.field4
WHERE table3.field5 Is Not Null)
AS alias2 ON (alias1.field1= @Give_name)

ON table2.field7 = alias1.field1
Stored procedure is quite complicated.

When I run this procedure in Sql Query Analyzer it works fine.
But if I run that via form's command button which runs it in VBA, it
works also fine and it makes rows into table1 . But after execution
it comes error message: "You canceled the previous operation".
In same code after above stored procedure I should execute an other
stored procedure but now it is unpossible because of error message.

In VBA I run stored procedure with DOCMD.OPENSTORE DPROCEDURE "spName",
acNormal,acEdit -command.

I think that in that error there is something to do with "INTO table1"
clause because if I take away that clause pure SELECT works fine
without that error messages.

I appreciate any help !

Colpo

I use .adp. I have tried also run my sp thru ado command.
Didn't work :(.
Here is code example of that:
Dim stDocName as String
Dim cmd As New ADODB.Command
stDocName = "myStoredProced ure"
Set cmd = New ADODB.Command
With cmd
.CommandText = stDocName
.CommandType = adCmdStoredProc
' This is valid connection
.ActiveConnecti on = CurrentProject. Connection
.Execute
End With
Colpo

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
10913
by: Michael Trosen | last post by:
Hi Everyone, I hope someone can help, I'm pretty new to pro*c programming. I have the following application setup: a pro*c program calls a stored procedure and recieves a cursor back: the cursor is defined as: SQL_CURSOR delpt_cursor
3
2811
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default" when I launched the IBM Distributed Debugger via D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001 First, a bit of background. I am running DB2 V7.2 with Fixpack 9 applied on Windows XP Professional (all critical service applied). I've written...
7
15605
by: Jeff Wang | last post by:
Hi all, Can someone help me out? I've been struggling with this for almost a week and still have no clue what's wrong. Basically I want to write a DB2 stored procedure for OS/390 in REXX. In this procedure it reads a dataset and return the first line of the dataset. I met two problems: Problem 1:
8
7952
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10); SELECT A FROM SESSION.TEMP; works from a query tool.
2
9255
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
6
6775
by: Wojciech Wendrychowicz | last post by:
Hello to All, I'm trying to retrieve records from AS/400 in an VBA application. So, I've made an RPG program, then a stored procedure wchich calls that RPG program, and finally some VBA code to call the stored procedure and retrieve data from AS/400. The problem is, that when I finally run my VB code, it just hangs. But when I call the same stored procedure from "pure" SQL - it works perfect. (I evaluate Aqua Data Studio 3.7) What I...
2
3341
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some of them are quite big (insert datas in several
7
3475
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function 'UpdateRegistrant' expects parameter '@EMail', which was not supplied. The field value was null in the database and not changed in the FormView so is null going back into the stored procedure. I'm stumped and would greatly appreciate any suggestions.
4
3999
by: nishi57 | last post by:
I hope I can get some help regarding this issue, which has been going on for a while. I have a desktop user who is having problem running "Stored Procedures". The DB2 Connect application works fine but when he runs the stored procedure, he gets the following error message. "SYSPROC".CSGCSB54 - Run started. Data returned in result sets is limited to the first 100 rows. Data returned in result set columns is limited to the first 20...
9
4150
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)...
0
9956
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10427
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9592
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7139
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4627
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.