RE: query or storedproc with temporal tables in VB6 against SQL2000

it works well if i use the provider MSDASQL instead of SQLOLEDB but MSDASQL
is deprecated by microsoft and i guess uses more resources and works slower

i would know how to do it using SQLOLEDB

thanks in advance for any comment




"tonimaluco" wrote:

i have troubles running this query from VBasic6 against Ms SQL Server 2000

DECLARE @tbl table(
[área][int],
[zona][nvarchar](15),
[provincia][nvarchar](30),
[apellidos][nvarchar](40),
[nombre][nvarchar](20),
[ant_inicio][int],
[ant_mantenimiento][int],
[ant_total][int],
[inicio][int],
[mantenimiento][int],
[total][int]
)
INSERT INTO @tbl
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_2
WHERE Fecha BETWEEN '1/1/2005' AND '1/2/2005'
UNION ALL
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_1
WHERE Fecha BETWEEN '1/1/2006' AND '1/2/2006'
SELECT área, zona, provincia, apellidos, nombre,
sum(ant_inicio) as AI, sum(ant_mantenimiento) as AM, sum(ant_total) as AT,
sum(inicio) as I,
sum(mantenimiento) as M, sum(total) as T
FROM @tbl
group by área,zona,provincia,apellidos,nombre

when i open the recordset or run an stored prodecure consisted in this query
i receive no error but the recordset object is never created, it passes some
time where server seems to be executing the query or stored procedure but
never returns a recordset, the execution thread passes to the next
instruction without error but the recordset is a no working object (no open)

i open the conection with this code:
*************************************************
connx.Mode = adModeShareDenyNone
connx.CursorLocation = adUseClient
connx.Provider = "SQLOLEDB"
connx.Properties("Data Source").Value = serverName
connx.Properties("Initial Catalog").Value = "espanha"
connx.ConnectionTimeout = 15
If frmLogin.seguridadnt.Value = True Then
connx.Properties("User ID").Value = user
connx.Properties("Password").Value = password
Else
connx.Properties("Integrated Security").Value = "SSPI"
End If
connx.Open

here i use the stored proc:
**********************************
commx.ActiveConnection = connx
commx.CommandType = adCmdStoredProc
commx.CommandText = "procTest"
Set rstx = commx.Execute ---> passes some time doing the stored proc
but never returns an valid object
Print rstx.EOF ---> ERROR because rstx still keeps closed


the stored proc works well called under query analycer or being called from
Crystal Reports (using ODBC conex)

proc test is:
********************
DECLARE @tbl table(
[área][int],
[zona][nvarchar](15),
[provincia][nvarchar](30),
[apellidos][nvarchar](40),
[nombre][nvarchar](20),
[ant_inicio][int],
[ant_mantenimiento][int],
[ant_total][int],
[inicio][int],
[mantenimiento][int],
[total][int]
)
INSERT INTO @tbl
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_2
WHERE Fecha BETWEEN '1/1/2005' AND '1/2/2005'
UNION ALL
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_1
WHERE Fecha BETWEEN '1/1/2006' AND '1/2/2006'
SELECT área, zona, provincia, apellidos, nombre,
sum(ant_inicio) as AI, sum(ant_mantenimiento) as AM, sum(ant_total) as AT,
sum(inicio) as I,
sum(mantenimiento) as M, sum(total) as T
FROM @tbl
group by área,zona,provincia,apellidos,nombre

***************

if i change the proc test deleting the temporal table, obviously works well
*****************************
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_2
WHERE Fecha BETWEEN '1/1/2005' AND '1/2/2005'
UNION ALL
select
área, zona, provincia, apellidos, nombre,
ant_inicio, ant_mantenimiento, ant_total,
inicio, mantenimiento, total
FROM CONSULTA_VENTAS_1
WHERE Fecha BETWEEN '1/1/2006' AND '1/2/2006'
******************************************

it seems to be some trouble in oledb when tries to understand fields that
SQL server will return (from temporal tables)

I WOULD LIKE TO BE ABLE TO USE THIS KIND OF TEMPORAL TABLES, THEY WORK IN
QUERY ANALYCER, THEY WORK WHEN I CALL THEM FROM CRYSTAL REPORTS (USING ODBC
CONEX)


Thanks a lot for any sugestion
tonimaluco


.

Relevant Pages