[dba-SQLServer] SHAPE question

SHAPE applies to ADO recordsets.  I suspect part of your problem may be
in this line:

RELATE ProjectID TO ProjectID

When I use SHAPE, I always alias each expression so I can specify the
sources for the join fields.

Charlotte Foust


-----Original Message-----
From: Steve Erbach [mailto:erbachs at gmail.com] 
Sent: Thursday, April 07, 2005 8:07 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SHAPE question


Dear Group,

I have never worked with the SHAPE command before. It, of course, is
scantily documented in BOL. I understand the concept and I have an
example of its use in an ASP application I'm modifying. But where I'm
having trouble is in debugging this example. It doesn't appear to work
any longer.

That is, I have a printed sample of what the SHAPE query produces once
it's been manipulated by ASP to format the output. I understand how the
formatting is grabbing different hierarchical blocks of data and looping
through the rows, etc. But now that I've connected the SHAPE query to my
data source, the ASP code fails.

I've tried copying the whole shebang into Query Analyzer, but that
doesn't seem to be the kind of thing it handles. I believe that SHAPE
only applies to ADO objects; that is, if I create an ADODB.Recordset
object and apply the SHAPE query to it with the Open method, then it's
supposed to work. Well, it's failing at that Open method.

Here's the query:

SHAPE 
  {
   SELECT DISTINCT 
     ProjectID, ProjName, BidDate, BidTime, PRLocation, City, State 
   FROM Projects1 
   INNER JOIN Counties 
   ON Projects1.countyid = counties.countyid 
   WHERE (((Projects1.BidDate)='4/8/2005') 
   AND (PhaseID != 7) 
   AND (Counties.Region=5))
   ORDER BY Projects1.BidTime, Projects1.ProjectID 
  } 
APPEND(
  {
   SELECT 
     AAMNumber, AAMInfo, ProjectID 
   FROM ProjectAddenda 
   ORDER BY AAMNumber
  } 
  AS Addenda 
  RELATE ProjectID TO ProjectID
), 
((SHAPE {SELECT CompanyID, CompanyTypeID, ProjectID FROM Projects2 
ORDER BY CompanyTypeID} AS Company 
APPEND({SELECT Company, Phone, Fax, CompanyID FROM Company1} AS Detail 
RELATE CompanyID TO CompanyID)) 
RELATE ProjectID TO ProjectID)

Is it the way the second SHAPE command is tacked onto the end of the
first one after the comma? I'm stumped.
-- 
Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com