Very Computer

I'm trying to build a SHAPE query as a stored procedure in an SQL database.

Here's the code:

SHAPE
 {SELECT * FROM Registrations} AS Regs
APPEND
 ({SELECT * FROM Transactions} RELATE
 RegID to TrnRegID) AS Trans
WHERE
 RegPrdID = '2612-0922'

The WHERE clause is hard-coded for clarity.

I've got two tables, Registrations and Transactions. I want to get all the
registrants for a particular program along with a child recordset of all the
transactions for each registrant, i.e. payments, refunds, etc. The RegID and
TrnRegID fields are the link between both tables.

I'm using the Visual BASIC IDE to create this stored procedure. Whenever I
try to save it I get the following error:
"Syntax error or access violation"

Am I missing a reference to some object library? Can I only create a stored
procedure like this from the server?

The sample above was adapted from an example in the Visual Basic
Programmer's Journal. Their example is very similar to the ones in the MSDN
library.

Thanks in advance for your help.

Rob

P.S. I know there are other SHAPE posts here (from 7/6), but for some
reason, my newsreader is telling me that the server no longer has them
available.

I have since discovered the MSDataShape provider that I must use in the
connection string. But doing so gives me an error with another query:

In code, I set the only parameter for a particular stored procedure and it
gives me an error about having to supply the parameter, which I'm doing!

When I go back to my original connection string, everything's fine again.

Here's the good connection string:
ConnectString = "Provider=SQLOLEDB;server=pmi;database=" & DBName &
";Trusted_Connection=yes;"

Here's the bad one:
ConnectString = "Provider=MSDataShape;Data
Provider=MSDASQL;server=pmi;database=" & DBName & ";Trusted_Connection=yes;"

If I keep the 'Data Provider' as SQLOELDB, it bombs trying to make the
connection.

Help!

Rob

Quote:> I'm trying to build a SHAPE query as a stored procedure in an SQL
database.

> Here's the code:

> SHAPE
>  {SELECT * FROM Registrations} AS Regs
> APPEND
>  ({SELECT * FROM Transactions} RELATE
>  RegID to TrnRegID) AS Trans
> WHERE
>  RegPrdID = '2612-0922'

> The WHERE clause is hard-coded for clarity.

> I've got two tables, Registrations and Transactions. I want to get all the
> registrants for a particular program along with a child recordset of all
the
> transactions for each registrant, i.e. payments, refunds, etc. The RegID
and
> TrnRegID fields are the link between both tables.

> I'm using the Visual BASIC IDE to create this stored procedure. Whenever I
> try to save it I get the following error:
> "Syntax error or access violation"

> Am I missing a reference to some object library? Can I only create a
stored
> procedure like this from the server?

> The sample above was adapted from an example in the Visual Basic
> Programmer's Journal. Their example is very similar to the ones in the
MSDN
> library.

> Thanks in advance for your help.

> Rob

> P.S. I know there are other SHAPE posts here (from 7/6), but for some
> reason, my newsreader is telling me that the server no longer has them
> available.