RE: query or storedproc with temporal tables in VB6 against SQL200
- From: tonimaluco <tonimaluco@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Mar 2006 05:18:30 -0800
sorry i meant i works just using provider MSDASQL and using this query as an
stored procedure. Never works using and Recordset based query (recordset
remains closed)
thanks again
"tonimaluco" wrote:
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
- References:
- query or storedproc with temporal tables in VB6 against SQL2000
- From: tonimaluco
- RE: query or storedproc with temporal tables in VB6 against SQL2000
- From: tonimaluco
- query or storedproc with temporal tables in VB6 against SQL2000
- Prev by Date: RE: query or storedproc with temporal tables in VB6 against SQL2000
- Next by Date: Multiple tables in a dataset and only one DataAdapter how do I update the access database
- Previous by thread: RE: query or storedproc with temporal tables in VB6 against SQL2000
- Next by thread: Command.Execute with a SP that has this var DECLARE @tbl table
- Index(es):
Relevant Pages
|
|
