EXCEL Issue retriving data from SQL
-
Hello ppl,
The first thing of all i apologize myself for my bad English, i already speak Spanish.
Ok, a little bit of history, i write a VBA macro for EXCEL that retrieves information from an SP in SQL Server 2K, and is not the first i write, i already writed a lot of this macros but this is the first time i get this issue, and i can't figure it out how to solve it.
Ej.
I have a query that retrieve information from some tables, it group the results, and make some math’s and return a single row per document, i have this view tested and works fine when you execute it from Query Analyzer, or the MMC
The following image is an example of the data returned by the query in the query analyzer.
As you can see there is only one row returned by the SP, also you can notice that I’m using the tools from SQL Server 2008, but the server is 2K (this is just a note)
Now i execute the same stored procedure from my macro in EXCEL, and sometimes not always i get some changed rows, and not all, only a one or two records.
The following image shows the same record i already show in the last image but in EXCEL the row is deagruped, why? How? i don’t know. also you can notice that if you sum the values of the COLS SubTotal, IVA, Importe, you get the same values that i get in the Query Analyzer. (also the image showed uses office 2007 pofessional, but i get the same result in Office 2K, and 2003)
So what the things I have already tried,
- I change the way I import the data from SQL to EXCEL, I used the instruction varHoja.Range().CopyFromRecordset rstResultados, also i write a routine that gets value by value the result and copy it in the EXCEL Sheet. I change the way i connect to the DB, ODBC, ADO, ADO.NET
- I rewrite the query thinking that it was the cause but i already change it two time, and i get the same result.
:angry:So at this point i feel a little bit frustrated, and i hope someone in this forum can help me to figure it out this issue.
Thanks in advance and best regards.
Lic. Juan Alfredo Hernández Vázquez
VYCMEX S.A. De C.V.
Departamento De Sistemas
Tel. (81) 83486320, Ext. 108
Cel. 044 81 81622706
E-Mail: alhernandez@vycmex.com.mx -
We'll need to see the code from the stored procedure and the VBA.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Hey RBarryYoung thanks for the fast reply,
The store procedure uses query that is very large and include like five tables, and i think that the ppl who read it will need a deatiled explanation on how it works, but the resume is that the SP returns only one row, and when execute the SP from EXCEL, returns two.
The Marco is calling the store procedure exactly as i show in the image of the Query Analizer, so if the query analizer returns only one row, that means the query is being executed correctly, so why EXCEL is ungrouping it, if i'm not making any operation with the recordset, only wirting it on the Worksheet.
I think that if exist a problem with the data, the results from the SP will show ungrouped (Like EXCEL) two records instead of one, or what do you think?
And here is the version of the code that uses .CopyFromRecordset (The comments are in spanish):
Private Function dfImportarRegistros(ByRef conConexion As ADODB.Connection, ByVal strSucursal As String, ByVal strSQL As String, ByRef varHoja As Variant, _
ByVal lngRenglonInicial As Long, ByVal bolLimpiarHoja As Boolean, Optional bolEncabezados As Boolean = True) As Integer
' Declaración de variables
Dim lngColumna As Long
Dim lngRenglon As Long
Dim rstResultados As ADODB.Recordset
' Asignar el valor falso a la funcion en caso de un error
dfImportarRegistros = -1
' Activar la captura de errorres
On Error GoTo RutinaError
' Cerrar la conexión
If Not conConexion Is Nothing Then
conConexion.Close
Set conConexion = Nothing
End If
' Inicializar el objeto de conexión
Set conConexion = New ADODB.Connection
' Establecer los valores de la conexion y abrirla
conConexion.CommandTimeout = 0
conConexion.Open "Provider=sqloledb;Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;" & _
"Initial Catalog=" & strSucursal & ";User ID=XXX;Password=XXX;"
' Inicializar el recordset
Set rstResultados = New ADODB.Recordset
rstResultados.Open strSQL, conConexion, adOpenStatic
' Evitar que la pantalla parpade cuando se actualiza la información
Application.ScreenUpdating = False
' Limipiar la el contenido de la hoja destino
If bolLimpiarHoja Then
varHoja.Activate
varHoja.Cells.Select
Selection.ClearContents
End If
' Si se encontraron resultados, vaciar en la hoja destino
If Not rstResultados.EOF And Not rstResultados.BOF Then
' Si se activa la opción de encabezados mostrarlos u omitirlos
If bolEncabezados Then
' Establecer el titulo de las columnas
For lngColumna = 0 To rstResultados.Fields.Count - 1
varHoja.Cells(1, lngColumna + 1) = rstResultados.Fields(lngColumna).Name
Next
' Formatear el encabezado
varHoja.Range(Cells(1, 1), Cells(1, lngColumna)).Select
dfFormateaEncabezado True
End If
' Establecer el renglon inicial en el que se iniciara el volcado de información
lngRenglon = lngRenglonInicial
' Volcar el contenido del query en las celdas
varHoja.Range("A" & CStr(lngRenglon)).CopyFromRecordset rstResultados
' Obtener el regnglon final del volcado
lngRenglon = lngRenglon + rstResultados.RecordCount
' Ajustar el tamaño de las celdas
Cells.Select
Cells.EntireColumn.AutoFit
varHoja.Range("A1:A1").Select
Else
' Salir de la función
Exit Function
End If
' Cerrar los objetos de conexion
rstResultados.Close
Set rstResultados = Nothing
conConexion.Close
Set conConexion = Nothing
' Asignar el valor de exito a la función
dfImportarRegistros = lngRenglon - 1
' Desactivar el control de errores y salir de la función
On Error GoTo 0
Exit Function
RutinaError:
' Desactivar el control de errores
On Error GoTo 0
End Function
Lic. Juan Alfredo Hernández Vázquez
VYCMEX S.A. De C.V.
Departamento De Sistemas
Tel. (81) 83486320, Ext. 108
Cel. 044 81 81622706
E-Mail: alhernandez@vycmex.com.mx -
OK, two things, first, I need to see the call in your EXCEL sheet to this function, because the values are all parametrized here.
Second, if you do not want to show us the stored procedure, then you should use the profiler to trace your EXCEL application's calls to the SQL Server. Record the following information from Profiler:
1) The Login/User of the connection
2) The current database of the connection
3) the exact SQL statement executed.
4) the total number of rows returned.
Now, using Query analyzer, execute the SQL command and compare both the total rows and the row(s) for the specific case that you are having a problem with.
Next, login to the account in (1) above, goto the same database (2) and then cut and paste the command from (3) into your query analyzer and execute it. Compare the total number of rows returned to (4) and to your own account above.
Let us know what the results are.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Sure Bro,
Here is the call, to the function that import the data
' Establecer el query o stored procedure a ejecutarstrSQL = "EXECUTE sp_LibroVentas '" & Format(dteFechaInicial, "MM-DD-YYYY") & "', '" & Format(dteFechaFinal, "MM-DD-YYYY") & "'"
' Importar la información a la hoja
lngRegistros = dfImportarRegistros(gconBD, "VXMETRO", strSQL, Hoja2, lngRegistros + 1, True, bolBandera)
Where,
- gconBD, is the database connection objet
- VXMETRO is the data base where the SP will be executed
- strSQL is the previusly set query or stored procedure
- Hoja2 is the WorkSheet where the resultset will be copied
- lngRegistros + 1, is the row where we are going to start to cpy the results
- True, is a bolean value indicating if the WorkSheet will be cleaned before, the copy
- bolBandera, is a bolean value indicating if the COL Headers will be show or not
Now the Stored Procedure,
CREATE PROCEDURE [dbo].[sp_LibroVentas]/* DECLARACION DE VARIABLES */
@FechaInicial varchar(15),
@FechaFinal varchar(15)
AS
/* QUERY PRINCIPAL */
SELECT
'MONTERREY' AS Sucursal, [Clave Cliente], [Nombre Cliente], Fecha, Documento, [Clave Movimiento], [Orden de venta], SubTotal,
IVA, Importe, [Costo Venta], (ABS(SubTotal) - ABS([Costo Venta])) / ABS(SubTotal) AS Utilidad, [Clave Vendedor], [Nombre Vendedor], [T.C.], Completo
FROM
/* USER_VW_LIBRO_VENTAS_BASE */
(SELECT
dbo.TRAN_CORR.NUME_CLIE AS [Clave Cliente], dbo.CLIE_NTES.NOMB_CLIE AS [Nombre Cliente],
dbo.TRAN_CORR.FECH_DOCT AS Fecha, dbo.TRAN_CORR.NUME_DOCT AS Documento, dbo.TRAN_CORR.CLAV_MOVI AS [Clave Movimiento],
ISNULL(VW_ORTR_ORVT.NUME_ORDE, '') AS [Orden de venta], SUM(ROUND((dbo.LINE_ITEM.CANT_MOVI * dbo.LINE_ITEM.COST_UNIT)
* (1 - dbo.LINE_ITEM.DSTO_LINE) * SIGN(dbo.TRAN_CORR.IMP_PAGO), 2)) AS SubTotal,
SUM(ROUND((dbo.LINE_ITEM.CANT_MOVI * dbo.LINE_ITEM.COST_UNIT) * (1 - dbo.LINE_ITEM.DSTO_LINE)
* dbo.LINE_ITEM.TASA_IMPU * SIGN(dbo.TRAN_CORR.IMP_PAGO), 2)) AS IVA, SUM(ROUND(((dbo.LINE_ITEM.CANT_MOVI * dbo.LINE_ITEM.COST_UNIT)
* (1 - dbo.LINE_ITEM.DSTO_LINE)) * (1 + dbo.LINE_ITEM.TASA_IMPU) * SIGN(dbo.TRAN_CORR.IMP_PAGO), 2)) AS Importe,
ISNULL(USER_VW_COSTO_COMPRAS.[Costo Total], 0) AS [Costo Venta], ISNULL(dbo.TRAN_CORR.CLAV_VEND, 'N/A') AS [Clave Vendedor],
dbo.VEND_EDOR.NOMB_VEND + ' ' + dbo.VEND_EDOR.PATE_VEND + ' ' + dbo.VEND_EDOR.MATE_VEND AS [Nombre Vendedor],
dbo.TRAN_CORR.IMPO_TIPO AS [T.C.], '__________' AS Completo
FROM
dbo.LINE_ITEM INNER JOIN
dbo.CLIE_NTES INNER JOIN
dbo.TRAN_CORR ON dbo.CLIE_NTES.NUME_CLIE = dbo.TRAN_CORR.NUME_CLIE ON
dbo.LINE_ITEM.NUME_DOCT = dbo.TRAN_CORR.NUME_DOCT AND dbo.LINE_ITEM.CLAV_MOVI = dbo.TRAN_CORR.CLAV_MOVI INNER JOIN
dbo.COND_CLIE ON dbo.CLIE_NTES.NUME_CLIE = dbo.COND_CLIE.NUME_CLIE LEFT OUTER JOIN
dbo.VEND_EDOR ON dbo.TRAN_CORR.CLAV_VEND = dbo.VEND_EDOR.CLAV_VEND LEFT OUTER JOIN
/* USER_VW_COSTO_COMPRAS */
(SELECT
CLAV_MOVI AS [Clave Movimiento], NUME_DOCT AS Documento, SUM(ULMO_COST * CANT_MOVI) AS [Costo Total]
FROM
dbo.CAPA_HIST
GROUP BY
CLAV_MOVI, NUME_DOCT
HAVING
(CLAV_MOVI IN ('NCG', 'FAC', 'NCR'))) USER_VW_COSTO_COMPRAS ON
dbo.TRAN_CORR.NUME_DOCT = USER_VW_COSTO_COMPRAS.Documento AND
dbo.TRAN_CORR.CLAV_MOVI = USER_VW_COSTO_COMPRAS.[Clave Movimiento] LEFT OUTER JOIN
/* VW_ORTR_ORVT */
(SELECT
NUME_DOCT, NUME_CLIE, NUME_ORDE, NUME_ORTR
FROM
dbo.ORDE_TRAB
GROUP BY
NUME_CLIE, NUME_ORDE, NUME_ORTR, NUME_DOCT) VW_ORTR_ORVT ON
dbo.TRAN_CORR.NUME_DOCT = VW_ORTR_ORVT.NUME_DOCT
WHERE
(dbo.TRAN_CORR.CLAV_MOVI IN ('FAC', 'NCG', 'NCR'))
GROUP BY
ISNULL(VW_ORTR_ORVT.NUME_ORDE, ''), dbo.CLIE_NTES.NOMB_CLIE, dbo.TRAN_CORR.NUME_DOCT, dbo.TRAN_CORR.NUME_CLIE,
dbo.TRAN_CORR.FECH_DOCT, dbo.TRAN_CORR.CLAV_MOVI, ISNULL(dbo.TRAN_CORR.CLAV_VEND, 'N/A'), dbo.LINE_ITEM.NUME_ASOC,
dbo.VEND_EDOR.NOMB_VEND + ' ' + dbo.VEND_EDOR.PATE_VEND + ' ' + dbo.VEND_EDOR.MATE_VEND,
ISNULL(USER_VW_COSTO_COMPRAS.[Costo Total], 0), dbo.TRAN_CORR.IMPO_TIPO
HAVING
(dbo.TRAN_CORR.FECH_DOCT >= CONVERT(DATETIME, @FechaInicial, 102)) AND
(dbo.TRAN_CORR.FECH_DOCT <= CONVERT(DATETIME, @FechaFinal, 102))) USER_VW_LIBRO_VENTAS_BASE
ORDER BY
[Clave Cliente], Documento, [Clave Movimiento]
GO
The user that i use for the conecction and the execution of the SP, is the administrator sa account.
I will do the test you ask, and let you know the results.
Lic. Juan Alfredo Hernández Vázquez
VYCMEX S.A. De C.V.
Departamento De Sistemas
Tel. (81) 83486320, Ext. 108
Cel. 044 81 81622706
E-Mail: alhernandez@vycmex.com.mx
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply