Would this store procedure did not work??? 1

[Pizarro]

I am simply trying to append all records from table ORDERS to table ORDERS CUME and this is as simple as it gets
but it does not work, I am greatful for any light!
Regards Julio
ALTER PROCEDURE StoredProcedure1
/*
(INSERT INTO dbrders Cume

OrderNo AS Expr5,
Sponsor AS Expr6,
SponNo AS Expr7,
FeedDate AS Expr17,

SELECT
OrderNo AS Expr5,
Sponsor AS Expr6,
SponNo AS Expr7,
FeedDate AS Expr17,
FROM ORDERS
)
*/
AS
/* SET NOCOUNT ON */
RETURN

 

[JayKusch]

INSERT INTO dbrders Cume
(
OrderNo,
Sponsor,
SponNo,
FeedDate

SELECT
OrderNo,
Sponsor,
SponNo,
FeedDate
FROM ORDERS
)


Thanks

J. Kusch

 

[Pizarro]

Thank you very much for your reply ...
I tried it in a stored procedure (I am new at this but with mucho experience in DAO) tried to run it and nothing ....what am I doing wrong or a step I'm missing?

Thanks for your tremendous help!

 

[MDXer]

Your target table needs to be wraped in square braces due to the space and the close paren for your insert into should be prior to your select.

INSERT INTO dbo.[Orders Cume]
(
OrderNo,
Sponsor,
SponNo,
FeedDate
)
SELECT
OrderNo,
Sponsor,
SponNo,
FeedDate
FROM ORDERS



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JayKusch]

ok ... lets start fresh w/ a new stored procedure (SP)

First of all ... If you have a table with a space in the name, which is NOT advised, you must enclose it in brackets for it to work. So if your table as I read it is call "Orders Cume", which really should be "OrdersCume", then you are going to have to enclose it in brackets [], like this ... [Orders Cume].

Second, if the target table "Orders Cume" has the same number of fields and same datatypes as the source table "Orders" you can use the following commands ...

Open Query Analyzer on the database we want to create the SP in and that the tables reside.

Now paste this code and execute ...

CREATE PROCEDURE StoredProcedureTest1

AS 

INSERT INTO dbo.[Orders Cume]

   SELECT OrderNo, Sponsor, SponNo, FeedDate 
   FROM ORDERS

Once the SP is created, you need to execute it as ...

EXEC StoredProcedureTest1

If the source and target tables do not have the same structure, you need to tell the statement what fields are to be INSERTed based on what fields are SELECTed. Somewhat like this ...

CREATE PROCEDURE StoredProcedureTest2

AS 

INSERT INTO dbo.[Orders Cume]
   ( OrderNo, Sponsor, SponNo, FeedDate)

   SELECT OrderNo, Sponsor, SponNo, FeedDate 
   FROM ORDERS

Once the SP is created, you need to execute it as ...

EXEC StoredProcedureTest2

Thanks

J. Kusch

 

[Pizarro]

Thank you so much for help. And I hope you understand that it is a bit hard just diving into this, all I need is some valuable direction, just like the one you gave me.
I have developed great apps with Access but now my data has overgrown. I am not scheduled to start training until next month and guess what... I need to start making it happen now.

Please be patient if I ask you some basic questions like this one ....

How or better yet from where to I run :
EXEC StoredProcedureTest2
I am in your dept my friend, thanks for all your help.

Regards - Pizarro

 

[Pizarro]

Here is what I came up with but I am getting a time out error and the stored procedure is not executing.....
What could be wrong???

Any help as always very much appreciated!
regards - Pizarro

Private Sub Command0_Click()

Dim oConn As ADODB.Connection
Dim oComm As ADODB.Command
Set oConn = New ADODB.Connection
Set oComm = New ADODB.Command
oConn.Open _
"Provider=Microsoft.Access.OLEDB.10.0;Data Source=BLK00FINA00129;Integrated Security=SSPI;" & _
"Initial Catalog=Cume2SQL;Data Provider=SQLOLEDB.1"
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdStoredProc
oComm.CommandText = "StoredProcedure2"
oComm.Execute "StoredProcedure2"

End Sub

 

[JayKusch]

Ahhh... The programming part is my downfall friend. I am not sure of your issue. Maybe another forum member can shed some light on this issue.

Thanks

J. Kusch

 

[Pizarro]

Thanks for lookin in Jay!

 

[SonOfEmidec1100]

Try these changes, you may need to tidy up the security on the connection string.

Private Sub Command0_Click()

Dim oConn As ADODB.Connection
Dim oComm As ADODB.Command
    Set oConn = New ADODB.Connection
    Set oComm = New ADODB.Command
    oconn.ConnectionString = "Provider=sqloledb;Data Source=BLK00FINA00129;" _
                       & "Initial Catalog=Cume2SQL;Trusted_Connection=Yes;"
    oConn.Open 
    oComm.ActiveConnection = oConn
    oComm.CommandType = adCmdStoredProc
    oComm.CommandText = "StoredProcedure2"
    oComm.Execute 
End Sub

 

[Pizarro]

Thanks for your reply and here is what worked:
On Error GoTo ErrHandler
Dim oConn As ADODB.Connection
Dim oComm As ADODB.Command
Set oConn = New ADODB.Connection
Set oComm = New ADODB.Command
'====
oConn.ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;;Data Source=BLK00FINA00129;Persist Security Info=True;" _
& "Initial Catalog=Cume2SQL;Trusted_Connection=Yes;Data Provider=SQLOLEDB.1"
oConn.Open
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdStoredProc
oComm.CommandText = "StoredProcedure1"
oComm.Execute
oComm.CommandText = "StoredProcedure2"
oComm.Execute

Exit Sub
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; err.Description
For Each er In cn.Errors
Debug.Print "err num = "; err.Number
Debug.Print "err desc = "; err.Description
Debug.Print "err source = "; err.Source
Next