CODEWITHSUNDEEP
sqlsql-server-2008export-to-excel
Soph
Soph

Reputation: 2915

Error using the export data option in SQL Server 2008 with function

This is probably an easy question to answer, but I can't figure it out.

I have this query which I want to export to Excel. I followed instructions detailed here, but the sql query is not right because of the use of a function I defined earlier.

This is the function I defined:

CREATE FUNCTION FullMonthsSeparation 
(
    @DateA DATETIME,
    @DateB DATETIME
)

RETURNS INT
AS
BEGIN
DECLARE @Result INT

DECLARE @DateX DATETIME
DECLARE @DateY DATETIME

IF(@DateA < @DateB)
BEGIN
    SET @DateX = @DateA
    SET @DateY = @DateB
END
ELSE
BEGIN
    SET @DateX = @DateB
    SET @DateY = @DateA
END

SET @Result = (
    SELECT 
    CASE 
        WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
            THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
        ELSE DATEDIFF(MONTH, @DateX, @DateY)
    END
)
RETURN @Result
END
GO

This is the SQL Query that I want to export to Excel:

SELECT DISTINCT soc.idSocio,
                (SELECT Count(*)
                 FROM   [BaseSR].[dbo].[Movimiento] m
                 WHERE  m.idSocio = soc.idSocio
                        AND m.TipoMovimiento = 1) AS CantidadDeCompras,
                nombre,
                (SELECT TOP 1 fecha
                 FROM   [BaseSR].[dbo].[Movimiento] m
                 WHERE  m.idSocio = soc.idSocio
                        AND m.TipoMovimiento = 1/*Consumos*/
                 ORDER  BY fecha DESC) AS UltimoMovimiento,
                dbo.Fullmonthsseparation(soc.fechaAlta, Getdate()) AS MesesDesdeAltaEnPrograma
FROM   [BaseSR].[dbo].[Socio] soc
WHERE  soc.idTarjeta LIKE '2001%'

And this is the error message I get:

The statement could not be parsed. Additional information: Deferred prepare could not be completed. Statement(s) could not be prepared. Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FullMonthsSeparation", or the name is ambiguous.

However, when I execute the query it runs without any problem.

If I changed the sql query to [BaseSR].dbo.FullMonthsSeparation(... then the error is:

The statement could not be parsed. Additional information: Deferred prepare could not be completed. Statement(s) could not be prepared. Cannot find either column "BaseSR" or the user-defined function or aggregate "BaseSR.dbo.FullMonthsSeparation", or the name is ambiguous.

Any ideas?

Upvotes: 0

Views: 1042

Answers (2)

Jānis
Jānis

Reputation: 2266

I would suggest to take easy way- create view and export data from there! :)

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

You've justified out the database names everywhere, which suggests the execution context is not [BaseSR], which would explain why the function might not be found.

Try doing the same for the function:

[BaseSR].dbo.FullMonthsSeparation(...

Upvotes: 1

Related Questions