Stored procedure problem

[Timppa]

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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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

[Trevor Best]

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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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

[Ted Theodoropoulos]

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.google. 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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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

[Timppa]

Trevor Best <nospam@localhost> wrote in message news:<40**********************@auth.uk.news.easyne t.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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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

[Timppa]

te********@yahoo.com (Ted Theodoropoulos) wrote in message news:<f5**************************@posting.google. 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.google. 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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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 = "myStoredProcedure"
Set cmd = New ADODB.Command
With cmd
.CommandText = stDocName
.CommandType = adCmdStoredProc
' This is valid connection
.ActiveConnection = CurrentProject.Connection
.Execute
End With
Colpo

[Ted Theodoropoulos]

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.google. com>...

te********@yahoo.com (Ted Theodoropoulos) wrote in message news:<f5**************************@posting.google. 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.google. 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,alias2.field2,table2.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.OPENSTOREDPROCEDURE "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 = "myStoredProcedure"
Set cmd = New ADODB.Command
With cmd
.CommandText = stDocName
.CommandType = adCmdStoredProc
' This is valid connection
.ActiveConnection = CurrentProject.Connection
.Execute
End With
Colpo