July 30, 2008 at 5:46 pm
Hi guys.....
I have come to an end of trying to fix this and this is my last hope in finding a possible solution.
I basically have to create a weekly report which gives some counts on sales for certain products. This then had to be put into a DTS package and scheduled one per week so we didnt have to send it manually.. ok.. nice and easy - done it a million times..
I got the query working 100%, put it into a stored proc, executed it and everything is as expected. Now when I try to create teh DTS package (or firstly test the export to excel) I get errors I have never seen before...
The report on the error is as follows:
- Setting Source Connection (Error)
Messages
Error 0xc0207015: Source - Query [1]: There is more than one data source column with the name "25/07/2008". The data source column names must be unique.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC0207015 (Microsoft.SqlServer.DTSPipelineWrap)
The sql is dynamic as it needs to be based on the last 7 days.. The query is below:
-- daily report based on a nvarchar value in the table
-- for datetime values, remove conversation code
declare @counter int, @max_count int
declare @sql varchar(max), @sql_total varchar(max)
declare @sql_common varchar(max)
declare @startdate datetime, @enddate datetime
-- set the date range for report
set @startdate = convert(datetime, dateadd(dd,-6,getdate())) -- last week
set @enddate = getdate()
set @sql_common = ''
set @sql = ''
-- set counter values for loop - i.e. number of days
select @max_count = datediff(dd, @startdate, @enddate)
set @counter = 0
--build the core of the sql
while (@counter <= @max_count)
begin
if(@counter <> @max_count)
select @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,@counter, convert(datetime, convert(datetime, @startdate), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + convert(varchar,DATEADD( DD, @counter, DATEDIFF( dd,0,dateadd(dd,0, @startdate) ) ),103) +'], '
else
begin
select @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,@counter, convert(datetime, convert(datetime, @startdate), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + convert(varchar,DATEADD( DD, @counter, DATEDIFF( dd,0,dateadd(dd,0, @startdate) ) ),103) +'] '
end
--incremnet the counter to the next day
select @counter = @counter + 1
end
select @sql = 'select '' Totals'' as ''Product category'', ' + @sql_common + '
from tblgeneric_90 inner join
dbo.tblGenericLookupValue ON dbo.tblGeneric_90.Attr_2420 = dbo.tblGenericLookupValue.ValueID
where ValueID in(1417, 1418, 1422, 1423, 1424, 1425, 1429)
union
select dbo.tblGenericLookupValue.Name as ''Product category', ' + @sql_common + '
from tblgeneric_90 inner join
dbo.tblGenericLookupValue ON dbo.tblGeneric_90.Attr_2420 = dbo.tblGenericLookupValue.ValueID
where ValueID in(1417, 1418, 1422, 1423, 1424, 1425, 1429)
group by dbo.tblGenericLookupValue.Name'
exec (@sql)
I know all the conversion, etc is messy (was required because of different text values which were dates), so dont pay attention to that...
Basically, this provides a very cool report with the dates as the column headers. The first column has specific category names, and the rest of the cells are the data required. It works 100% adn I get exactly what is needed. However when trying to create a DTS package it fails.. I have tried this on different machines and versions of SQL Server 2005 (Developer and Enterprise). The same error occurs.
Even when I print out the output of the dynamic sql and put that into the query for the export, the same error occurs, so its not how the sql is created.
Also, its not related to the UNION of the two queries as I get the same error when removing that...
Incase this helps.. This is the final query that is created by the dynamic sql
select ' Totals' as 'Product category', sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39652 then 1
else 0 end) as [25/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39653 then 1
else 0 end) as [26/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39654 then 1
else 0 end) as [27/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39655 then 1
else 0 end) as [28/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39656 then 1
else 0 end) as [29/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39657 then 1
else 0 end) as [30/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39658 then 1
else 0 end) as [31/07/2008]
from tblgeneric_90 inner join
dbo.tblGenericLookupValue ON dbo.tblGeneric_90.Attr_2420 = dbo.tblGenericLookupValue.ValueID
where ValueID in(1417, 1418, 1422, 1423, 1424, 1425, 1429)
union
select dbo.tblGenericLookupValue.Name as 'Product category', sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39652 then 1
else 0 end) as [25/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39653 then 1
else 0 end) as [26/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39654 then 1
else 0 end) as [27/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39655 then 1
else 0 end) as [28/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39656 then 1
else 0 end) as [29/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39657 then 1
else 0 end) as [30/07/2008], sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when 39658 then 1
else 0 end) as [31/07/2008]
from tblgeneric_90 inner join
dbo.tblGenericLookupValue ON dbo.tblGeneric_90.Attr_2420 = dbo.tblGenericLookupValue.ValueID
where ValueID in(1417, 1418, 1422, 1423, 1424, 1425, 1429)
group by dbo.tblGenericLookupValue.Name
Any help will be appreciated big time!!
thanks,
Conor
July 30, 2008 at 7:35 pm
Did you try using OPENROWSET?
I also have a setting of generating automated reports (daily). What i used is OPENROWSET to load the data from mssql to excel. As for the excel file, I created an excel file with only the columns in it (in case columns are static), used command shell to copy the schema of the excel file to another folder then the copied excel file is the one that i populate.
"-=Still Learning=-"
Lester Policarpio
July 31, 2008 at 9:29 am
Thanks for your reply - it sent me off to do some research which eventually led me back to the where I started. It was interesting but didnt use it in the solution I came up with.
I found a work around to the issue which essentially was the parser was not performing the WHILE loop to create the columns, which it then maps from the source to destination. In the end I had to write the query without the while loop which turned out the required results.
Anyone who is interested, I repalced the above while loop with the following coe
--build the core of the sql
-- export will run on sunday evenings, hence days will remain the same
set @mon = convert(varchar,datename(dw,DATEADD( DD, -6, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @tue = convert(varchar,datename(dw,DATEADD( DD, -5, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @wed = convert(varchar,datename(dw,DATEADD( DD, -4, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @thu = convert(varchar,datename(dw,DATEADD( DD, -3, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @fri = convert(varchar,datename(dw,DATEADD( DD, -2, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @sat = convert(varchar,datename(dw,DATEADD( DD, -1, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
set @sun = convert(varchar,datename(dw,DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, getdate())) ) ),103)
--Monday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-6, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @mon +'],'
--Tuesday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-5, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @tue +'],'
--Wednesday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-4, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @wed +'],'
--Thursday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-3, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @thu +'],'
--Friday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-2, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @fri +'],'
--Saturday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,-1, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @sat +'],'
--Sunday
set @sql_common = @sql_common + ' sum(case convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, Attr_2456), 101)) ) )
)
when ' + cast(convert(int,
DATEADD( DD, 0, DATEDIFF( dd,0,dateadd(dd,0, convert(datetime, convert(datetime, getdate()), 101)) ) )
) as nvarchar(max)) +
' then 1
else 0 end) as [' + @sun +']'
obviously I declared the new variables used here. I didnt hav to use them, but found it easier for readability. Again, ignore overkill of date conversation which could be removed if dealing directly with a datatype of datetime..
happy days 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply