Data Shaping with SQL Server 2000

[bgreenhouse]

Hi there

I'm trying to pull out some data using the following query:

Dim cn, sSQL, rsTxn, rsTxnDetail, cCard_Num
cCard_Num = "510987654321"
Set cn=server.createobject("ADODB.connection&quot
Set rsTxn=server.CreateObject("ADODB.recordset&quot
Set rsTxnDetail=server.CreateObject("ADODB.recordset&quot
cn.Provider="MSDataShape"
cn.Open(Application("DBconn&quot)

sSQL="SHAPE {SELECT store_no, purch_date, purch_amt, pay_type FROM gamehdr " & _
"WHERE card_num = " & cCard_Num & "} APPEND ({SELECT descr, qty, points " & _
"FROM gamedtl} RELATE ticket TO ticket) AS chapTxnDetail"

rsTxn.Open sSQL, cn

My Connection string is:
Provider=SQLOLEDB.1;Password=web;Persist Security Info=True;User ID=web;Initial Catalog=RSGC;Data Source=BCG

I get a stupid little error page in IE when I try to run this query, saying:

Error Type:
(0x80040E14)
/rsgc/txntest.asp, line 21

Line 21, being the rsTxn.Open line.

I have tried it with sSQL = "select * from gamehdr" and it works like a charm, indicating that nothing's wrong with my connection or permissions. I've tried different cursor types and lock types, but nothing seems to make a difference. I can't figure out what the error means. Anyone know what's wrong, or at least how to get a more comprehensive error message?

Thanks

Ben

 

[chiph]

Could be wrong on this, but don't you need the OLAP package installed on your MS SQL 2000 server to do data shaping calls?

Chip H.

 

[bgreenhouse]

That's a good question...I don't know. I have a book that talks about it data shaping, but never mentions anything like that. I'll have to look into it. If anyone knows for sure, let us know!

Thanks Chip.

Ben

 

[bgreenhouse]

Hi Everyone

I figured it out. For those who want to know, the problem was that my connection string contained Provider = SQLOLEDB.1, which is fine usually, but I was also specifiying cn.provider = "MSDataShape". I needed to specify that the SQLOLEDB was the Data Provider, so my new connection string looks like:

Data Provider=SQLOLEDB.1;...

Oh the little details hurt.

Ben