Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultarla

Estas en el tema de Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultarla en el foro de SQL Server en Foros del Web. Hola Tengo un problema al manejar una tabla temporal para SQL desde una macro en excel. El codigo deveria hacer lo siguiente: 1.- Abre una ...
  #1 (permalink)  
Antiguo 12/08/2011, 14:47
 
Fecha de Ingreso: agosto-2011
Ubicación: Edo. de Mexico
Mensajes: 12
Antigüedad: 9 años, 10 meses
Puntos: 0
Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultarla

Hola Tengo un problema al manejar una tabla temporal para SQL desde una macro en excel.

El codigo deveria hacer lo siguiente:

1.- Abre una conexion a SQL
2.-Con la variable sql toma un script de un textbox para crear la tabla temporal
3.-Con la variable sql1 ejecuta una consulta a la tabla temporal y la descarga en excel
4.-Con la variable sql2 ejecuta otra consulta a la tabla temporal y la descarga en excel
5.-Cierra mi conexion a SQL y por lo tanto se elimina la tabla temporal

Pero al parecer no realiza nada desde el punto 3, es como si mi tabla temporal no existiera; y esto mismo lo hago desde SQL y funciona perfectamente, incluso en el enterprise selecciono la creacion de la tabla y los selects y me manda los resultados sin problemas; pero en la macro si incluyo todo en una misma variable "sql" me manda error.

Este es mi codigo:

Sub Gen_Tabla_Temp(sql As String, consulta As String)
'
' Macro1 Macro
' Macro recorded 01/08/2011 by eblugo
'

'
'Eliminamos los registros de la consulta anterior para no confundir datos
Sheets("GT GCIA").Select
Range("G3:Q3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("G3").Select
Sheets("GT EDO").Select
Range("G3:R3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("G3").Select
Sheets("Data").Select
Range("A1").Select

Dim Connection1 As Object
Dim rst As Object
Dim rst1 As Object
Dim rst2 As Object
Dim sql1 As String
Dim sql2 As String

HoraInicio = Time()

'On Error Resume Next
Set Connection1 = CreateObject("ADODB.Connection")
Connection1.CommandTimeout = 0


Connection1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=DB;Data Source=SERVER"

If sql <> vbNullString And consulta <> vbNullString Then

Connection1.Open
Set rst = CreateObject("ADODB.Recordset")
rst.Open sql, Connection1, , 1, 0

sql1 = "SELECT Estado, Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW, COUNT(CustomerCode) NumCtes, SUM(CustomerIndustryVolume) CPWINDTOT, SUM(CustomerInternalVolume) CPWPMMTOT, SUBSTRING(GeoTreeAreaCode, 1,2) Gerencia FROM #CtesTmp WHERE SUBSTRING(GeoTreeAreaCode, 1,2) between '31' and '84' GROUP BY SUBSTRING(GeoTreeAreaCode, 1,2), Estado, Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW ORDER BY SUBSTRING(GeoTreeAreaCode, 1,2), Estado, Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW"

Set rst1 = CreateObject("ADODB.Recordset")
rst1.Open sql1, Connection1, , 1, 0

C = 0
F = 0

'recorre las columnas, añade el nombre del campo al encabezado
For i = 0 To rst1.Fields.Count - 1
Sheets(Data).Range(Chr(i + 65) & F + 1).Value = rst1.Fields(i).Name
Next

F = F + 1

' recorre todo el recordset hasta el final
Do While Not rst1.EOF

' recorre los campos en el registro actual del recordset para recuperar el dato
For i = 0 To rst1.Fields.Count - 1
' añade el valor a la celda
Sheets(Data).Range(Chr(C + 65) & _
F + 1).Value = rst1.Fields(C)
C = C + 1
Next

' resetea el indice de las columnas
C = 0
' Referencia al registro actual (incrementa )
F = F + 1
' Siguiente registro
rst1.MoveNext
Loop

On Error Resume Next


sql2 = "SELECT Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW, COUNT(CustomerCode) NumCtes, SUM(CustomerIndustryVolume) CPWINDTOT, SUM(CustomerInternalVolume) CPWPMMTOT, SUBSTRING(GeoTreeAreaCode, 1,2) Gerencia FROM #CtesTmp WHERE SUBSTRING(GeoTreeAreaCode, 1,2) between '31' and '84' GROUP BY SUBSTRING(GeoTreeAreaCode, 1,2), Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW ORDER BY SUBSTRING(GeoTreeAreaCode, 1,2), Canal, SubCanal, TipoNegocio, SubTipoNegocio, HET, Tipo, CPW"
Set rst2 = CreateObject("ADODB.Recordset")
rst2.Open sql2, Connection1, , 1, 0

C = 0

'recorre las columnas, añade el nombre del campo al encabezado
For i = 0 To rst2.Fields.Count - 1
Sheets(Data).Range(Chr(i + 65) & F + 1).Value = rst2.Fields(i).Name
Next

F = F + 1

' recorre todo el recordset hasta el final
Do While Not rst2.EOF

' recorre los campos en el registro actual del recordset para recuperar el dato
For i = 0 To rst2.Fields.Count - 1
' añade el valor a la celda
Sheets(Data).Range(Chr(C + 65) & _
F + 1).Value = rst2.Fields(C)
C = C + 1
Next

' resetea el indice de las columnas
C = 0
' Referencia al registro actual (incrementa )
F = F + 1
' Siguiente registro
rst2.MoveNext
Loop
' cierra y descarga las referencias
On Error Resume Next

Set rst1 = Nothing
Set rst2 = Nothing
Connection1.Close
rst.Close
Set Connection1 = Nothing
Set rst = Nothing
rst1.Close
rst2.Close

End If
MsgBox ("Se completo la consulta exitosamente")

MsgBox "El proceso ha concluido" & vbCrLf & vbTab & _
DateDiff("s", HoraInicio, Time()) & " Segundos", vbInformation, "PROCESO CONCLUIDO"

End Sub

La verdad ya intente muchas formas para manejarlo, incluso quise anidar conexciones y que los selects a la tabla temporal entraran a tempdb, pero parece que no se pueden anidar, como le podria hacer para crear la tabla temporal y consultarla desde la macro?

De antemano gracias por su tiempo.
  #2 (permalink)  
Antiguo 12/08/2011, 15:04
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 14 años, 10 meses
Puntos: 774
Respuesta: Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultar

ya probaste con create table ##temporal? para que te la guarde en memoria y que pueda ser accesible para varias sesiones.Y si este proceso va a ser recurrente porque no creas una tabla fisia??

Saludos!
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 12/08/2011, 15:19
 
Fecha de Ingreso: agosto-2011
Ubicación: Edo. de Mexico
Mensajes: 12
Antigüedad: 9 años, 10 meses
Puntos: 0
Respuesta: Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultar

No he intentado la tabla abierta a varias sesiones, pero lo voy a verificar.

En cuanto a crear la tabla fisica el problema es que los administradores del servidor estan en otro pais, y como tal mi usuario no es del todo administrador, por lo que solicitar la creacion de una tabla es todo un problema para la oficina, y eso si eeessss que existe posibilidad de que si la lleguen a crear, jeje aunque es la mejor opcion en la que pienso tambien.
  #4 (permalink)  
Antiguo 12/08/2011, 16:14
 
Fecha de Ingreso: agosto-2011
Ubicación: Edo. de Mexico
Mensajes: 12
Antigüedad: 9 años, 10 meses
Puntos: 0
Respuesta: Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultar

No, no funciono crear la tabla con sesiones multiples, me sigue mandando el mismo error:

Run-Time error '9'
Script Out Of Range

Lo manda en esta linea:

Sheets(Data).Range(Chr(i + 65) & F + 1).Value = rst1.Fields(i).Name

La cual es la primer linea de insercion de datos en la hoja de excel, despues de llamar la 1° consulta de la tabla temporal, lo raro es que esa parte del codigo lo uso en muchas otras macros que hacen consultas a tablas fisicas y no presenta error alguno.

les envio tambien el contenido del textbox (el script) que llama la variable "sql" la cual crea la tabla tamporal.


SELECT DISTINCT GeoTreeAreaCode, CustomerCode,
ISNULL(CountryDescription, 'SIN ESTADO') Estado,
ISNULL(AddressLevel1Description, 'SIN MUNICIPIO') Municipio,
ISNULL(AddressLevel2Description, 'SIN LOCALIDAD')Localidad,
ISNULL(CustomerTypeDescription, 'SIN CANAL') Canal,
ISNULL(CustomerTypeDetailDescription, 'SIN SUBCANAL') SubCanal,
ISNULL(TradeCategoryDescription, 'SIN TIPO DE NEGOCIO') TipoNegocio,
ISNULL(SubTradeCategoryDescription, 'SIN SUBTIPO DE NEGOCIO') SubTipoNegocio,
"HET" =
CASE
WHEN CustomerRegisteredName LIKE '%OXXO%' THEN 'OXXO'
WHEN CustomerRegisteredName LIKE '%ELEVEN%' THEN 'SEVEN'
WHEN CustomerRegisteredName LIKE '%CORPORACION ALIMENTARIA%' THEN 'SEVEN'
WHEN CustomerRegisteredName LIKE '%CIRCULO%' THEN 'CIRCULO'
WHEN CustomerRegisteredName LIKE '%CCK%' THEN 'CIRCULO'
WHEN CustomerRegisteredName LIKE '%OPERADORA RIO COLORADO%' THEN 'CIRCULO'
WHEN CustomerRegisteredName LIKE '%ADMINISTRADORA DEL COLORADO%' THEN 'CIRCULO'
WHEN CustomerRegisteredName LIKE '%EXTRA%' THEN 'EXTRA'
ELSE 'OTRO'
END,
"CPW" =
CASE
WHEN CustomerIndustryVolume > 25 THEN 25
WHEN CustomerIndustryVolume IS NULL THEN 0
ELSE CustomerIndustryVolume
END,
"TIPO" =
CASE
WHEN GeoTreeAreaCode BETWEEN '410000' AND '899999' THEN 'VTA'
WHEN GeoTreeAreaCode BETWEEN '310000' AND '399999' THEN 'UNI'
ELSE 'CAD'
END,
ISNULL(IndustryClassificationLevelCode, 'SIN') Potencial,
ISNULL(CustomerIndustryVolume, 0)CustomerIndustryVolume,
ISNULL(CustomerInternalVolume, 0) CustomerInternalVolume
INTO ##CtesTmp
FROM Customer
LEFT OUTER JOIN IndustryClassificationLevel ON Customer_IndustryClassificationLevelId = IndustryClassificationLevelId
JOIN CustomerGeoTreeArea ON CustomerId = CustomerGeoTreeArea_CustomerId
JOIN GeoTreeArea ON CustomerGeoTreeArea_GeoTreeAreaId = GeoTreeAreaId
-- AND GeoTreeAreaCode LIKE '760%'
JOIN GeoTree ON CustomerGeoTreeArea_GeoTreeId = GeoTreeId
AND GeoTreeCode IN ('VOLGEOTREE', 'MERCADEODET', 'MERCADEOMAY','MERCHRETAIL','MERCHWS','MERCHMDZ')
JOIN CustomerAddress ON CustomerId = CustomerAddress_CustomerId
AND CustomerAddressCode IN ('MAIN')
JOIN Address ON CustomerAddress_AddressId = AddressId
LEFT OUTER JOIN Country ON Address_CountryId = CountryId
LEFT OUTER JOIN AddressLevel1 ON Address_AddressLevel1Id = AddressLevel1Id
LEFT OUTER JOIN AddressLevel2 ON Address_AddressLevel2Id = AddressLevel2Id
LEFT OUTER JOIN TradeCategory ON Customer_TradeCategoryId = TradeCategoryId
LEFT OUTER JOIN SubTradeCategory ON Customer_SubTradeCategoryId = SubTradeCategoryId
LEFT OUTER JOIN CustomerType ON Customer_CustomerTypeId = CustomerTypeId
LEFT OUTER JOIN CustomerTypeDetail ON Customer_CustomerTypeDetailId = CustomerTypeDetailId
WHERE CustomerStatus = 'A'
-- AND CustomerCode NOT LIKE 'MDZ%'
ORDER BY GeoTreeAreaCode, Customercode
  #5 (permalink)  
Antiguo 12/08/2011, 16:30
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.482
Antigüedad: 13 años, 11 meses
Puntos: 180
Respuesta: Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultar

¿Que no el error Script Out Of Range (9) es de un miembro en un arreglo que no existe?
__________________
MCTS Isaias Islas
  #6 (permalink)  
Antiguo 12/08/2011, 17:17
 
Fecha de Ingreso: agosto-2011
Ubicación: Edo. de Mexico
Mensajes: 12
Antigüedad: 9 años, 10 meses
Puntos: 0
Respuesta: Crear Tabla temporal en SQL 2005 desde una macro en Excel (VB) y consultar

Viendo tu respuesta, me me ayudo para dudar del resto del codigo y encontre un error que ni imaginaba

en la linea:

Sheets(Data).Range(Chr(i + 65) & F + 1).Value = rst1.Fields(i).Name

Al parecer al cambiar el nombre de la hoja me comi las comitas Sheets("Data").Ran...... O_o jajaja y esto me hizo corregir el sistema de conexion como 20 veces y perder como 6 horas....

Bueno ahora solo queda ver el modo de agilizar la consulta, ya que se tarda 26 min en devolverme 19341 registros y se me hace mucho tiempo.

Etiquetas: excel, macro, registros, select, server, sql, tabla, temporal, vb, campos
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 11:37.