Problem with data shaping APPEND command against SQL Server 2000 
Author Message
 Problem with data shaping APPEND command against SQL Server 2000

Using ADO2.6 / 2.7, SQL Server 2000.

I am getting some unexpected results using shaping - in particular with the
record sets returned from
APPEND commands.

Using the pubs database as an example:

Say I have the following 2 stored procedures:

-- Get Title based on Title_Id

AS
  SELECT Title_Id, Title
  FROM Titles

and

-- Return a titles authors

AS

      SELECT au_fname, au_lname, 'xxx' as 'foobar'
  ELSE
      SELECT au_lname, au_fname, 'yyyyyy' As 'bar'

Using the following SHAPE command:

SHAPE {TitleGet 'BU1032'}
APPEND ({TitleAuthorsList ?, 1}
RELATE Title_Id To Parameter 0) As Author

I would expect the TitleAuthorsList call to execute the second SELECT
statement (in the ELSE block) and return
(as it does through SQL7)

  au_lname    au_fname   bar
  -----------  ----------  --------
  Green         Marjorie    yyyyyy
  Bennet        Abraham   yyyyyy

What I actually get is:

  au_fname    au_lname   foobar
  -----------  ----------  --------
  Green         Marjorie    yyy
  Bennet        Abraham   yyy

It seems like the schema for the recordset is being calculated using the
first select query including the column names, types and lengths.
The 'xxx' in the first select has caused 'yyyyyy'  to be truncated.

The length of the au_fname column is defined as 20
The length of the au_lname column is defined as 40

In the example au_lname would only ever return the first 20 characters

Having not found any mention of this change in behaviour on MSDN I'm a
little concerned that this might be overlooked
during SQL7 to SQL2000 testing.

Regards
Bryan Archer



Sun, 28 Aug 2005 17:58:17 GMT  
 Problem with data shaping APPEND command against SQL Server 2000
Bryan,

You are probably right (this is my guess as well) that provider gets schema
information based on first SQL statement. Try to do next: use CONVERT T-SQL
function to convert both values to the same varchar type with same length


       SELECT au_fname, au_lname, CONVERT(VARCAHR(20),'xxx') as 'foobar'
   ELSE
       SELECT au_lname, au_fname, CONVERT(VARCHAR(20), 'yyyyyy') As 'bar'

--
Val Mazur
Microsoft MVP


Quote:
> Using ADO2.6 / 2.7, SQL Server 2000.

> I am getting some unexpected results using shaping - in particular with
the
> record sets returned from
> APPEND commands.

> Using the pubs database as an example:

> Say I have the following 2 stored procedures:

> -- Get Title based on Title_Id

> AS
>   SELECT Title_Id, Title
>   FROM Titles

> and

> -- Return a titles authors

> AS

>       SELECT au_fname, au_lname, 'xxx' as 'foobar'
>   ELSE
>       SELECT au_lname, au_fname, 'yyyyyy' As 'bar'

> Using the following SHAPE command:

> SHAPE {TitleGet 'BU1032'}
> APPEND ({TitleAuthorsList ?, 1}
> RELATE Title_Id To Parameter 0) As Author

> I would expect the TitleAuthorsList call to execute the second SELECT
> statement (in the ELSE block) and return
> (as it does through SQL7)

>   au_lname    au_fname   bar
>   -----------  ----------  --------
>   Green         Marjorie    yyyyyy
>   Bennet        Abraham   yyyyyy

> What I actually get is:

>   au_fname    au_lname   foobar
>   -----------  ----------  --------
>   Green         Marjorie    yyy
>   Bennet        Abraham   yyy

> It seems like the schema for the recordset is being calculated using the
> first select query including the column names, types and lengths.
> The 'xxx' in the first select has caused 'yyyyyy'  to be truncated.

> The length of the au_fname column is defined as 20
> The length of the au_lname column is defined as 40

> In the example au_lname would only ever return the first 20 characters

> Having not found any mention of this change in behaviour on MSDN I'm a
> little concerned that this might be overlooked
> during SQL7 to SQL2000 testing.

> Regards
> Bryan Archer



Sun, 28 Aug 2005 20:51:18 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. BUG: Visual Basic 6.0 T-SQL Debugger Fails Against SQL Server 2000 Named Instance (Q310647)

2. Data View and SQL Server 2000 Problem

3. Parameter with ADODB.Command against SQL-server

4. Data Shaping error after upgrading to SQL 2000

5. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

6. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

7. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

8. Bug using ADO and data combo against SQL Server

9. Problems with ASP against SQL Server 7.0

10. Shape Command with SQL Server SP

11. VB6 application using SQL Server 2000 migrating db to SQL Server 2

12. Front Page Form posting data to SQL Server 2000

 

 
Powered by phpBB® Forum Software