Problem WIth SQL Server 2000 and ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem WIth SQL Server 2000 and ADO

Author

Topic

diegoesp
Starting Member

3 Posts

Posted - 2002-05-10 : 14:24:52

Hi. we are having a very complicate issue that involves SQL Server 2000 and data shaping.

The problem is the following: I have a Data Shaping statement like this one:

sSQL = "SHAPE {Exec CategoriasDeContenidoSea Null, Null, Null, Null, Null, Null, Null, Null, Null, ' SoportaLinkHomePage IS NOT NULL ', 'SoportaLinkHomePage'} "
sSQL = sSQL & "APPEND "
sSQL = sSQL & "("
sSQL = sSQL & "{Exec CategoriasYContenidosSeaHome 'EnLinkHomePage'} "
sSQL = sSQL & "RELATE ID to IDCategoriaDeContenido"
sSQL = sSQL & ")"
sSQL = sSQL & "AS Contenidos"

The connection is a Data Shape one:

oConn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Database=Cristianet;Server=SRVCJ\NetPrueba;User Id=sa;Password=sa;Connect Timeout=3"

As you can see, i use two stored procedures (CategoriasDeContenidosSea and CategoriasYContenidosSeaHome). These stored procedures arm a SQL Query inside of them, execute it with sp_executesql and return a value. The problematic one is "CategoriasYContenidosSeaHome", so i will list it here:

CREATE PROCEDURE CategoriasYContenidosSeaHome
@TipoDeVisualizacion nvarchar(50)
AS

DECLARE @String nvarchar(1800)

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

SET @String = 'SELECT CategoriasYContenidos.IDCategoriaDeContenido, Contenidos.Titulo, ContenidosSTRel.BreveDescripcion, ContenidosST.PaginaVisualizacion'
SET @String = @String + ' FROM CategoriasYContenidos '
SET @String = @String + ' INNER JOIN ContenidosSTRel ON '
SET @String = @String + ' CategoriasYContenidos.IDContenido=ContenidosSTRel.IDContenido AND '
SET @String = @String + ' CategoriasYContenidos.IDContenidoST=ContenidosSTRel.IDContenidoST '
SET @String = @String + ' INNER JOIN Contenidos ON '
SET @String = @String + ' CategoriasYContenidos.IDContenido=Contenidos.ID '
SET @String = @String + ' INNER JOIN ContenidosST ON '
SET @String = @String + ' CategoriasYContenidos.IDContenidoST=ContenidosST.ID'
SET @String = @String + ' WHERE '

IF (@TipoDeVisualizacion IS NULL)
BEGIN
SET @String = @String + ' (CategoriasYContenidos.EnLinkHomePage IS NOT NULL AND '
SET @String = @String + ' CategoriasYContenidos.EnLinkHomePage <> 0) '
SET @String = @String + ' OR '
SET @String = @String + ' (CategoriasYContenidos.EnFichaHomePage IS NOT NULL AND '
SET @String = @String + ' CategoriasYContenidos.EnFichaHomePage <> 0) '
END

IF (@TipoDeVisualizacion IS NOT NULL)
BEGIN
SET @String = @String + ' CategoriasYContenidos.' + @TipoDeVisualizacion + ' IS NOT NULL AND '
SET @String = @String + ' CategoriasYContenidos.' + @TipoDeVisualizacion + ' <> 0 '
SET @String = @String + ' ORDER BY CategoriasYContenidos.' + @TipoDeVisualizacion
END

EXEC sp_executesql @String

SET ANSI_WARNINGS ON
SET NOCOUNT OFF

GO

When the data shape executes, it throws an error "invalid syntax near 'CategoriasYContenidos' ". The bad part is that the SP generates a very valid SQL query!!. If I copy the generated query and paste inside of the SP as a plain query, the data shape does work. I tried a little trick
to return the plain query to test it against Query Analyzer and it does work well. The line to return the query was:

SET @String = N'SELECT (1) AS IDCategoriaDeContenido, ' + char(34) + @String + char(34) + ' AS Query'

This returns the query as part of the result.

This scheme is part of a big development for web sites, that is really full of data shaping. It does work perfectly under SQL Server 7, but under SQL Server 2000 does not. This is obviously not very convenient.

Anyone had this problem? is there recommendations for this?

I tested this using ADO 2.1, 2.5 and 2.7 and against two different SQL Server 2000 servers. None of these worked.

Thanks in advance for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources