August 8, 2008 at 8:12 am
i didn't see any error message...
August 8, 2008 at 8:38 am
Still getting error messages, whether I use my code or yours.
August 8, 2008 at 8:44 am
jason.griffith (8/8/2008)
Still getting error messages, whether I use my code or yours.
you do realize that if you'd go back to your original code and replace @holidayTbl with @HolidayTbl (as noted by Jack earlier), you'd be done and on to the next task.
August 8, 2008 at 8:45 am
Can you post the error message you are getting? Are you getting the error in BIDS/SSRS or iin SSMS/Query Analyzer?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 8:54 am
antonio.collins (8/8/2008)
jason.griffith (8/8/2008)
Still getting error messages, whether I use my code or yours.you do realize that if you'd go back to your original code and replace @holidayTbl with @HolidayTbl (as noted by Jack earlier), you'd be done and on to the next task.
Antonio,
That may be true, but I think the benefits gained by encapsulating in a stored procedure and re-factoring as needed will make the effort worthwhile. If the report is needed now, then sure make the quick fix, but don't settle for the quick fix.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 8:54 am
antonio.collins (8/8/2008)
jason.griffith (8/8/2008)
Still getting error messages, whether I use my code or yours.you do realize that if you'd go back to your original code and replace @holidayTbl with @HolidayTbl (as noted by Jack earlier), you'd be done and on to the next task.
I tried that as soon as I saw it - still getting error messages both as a proc and not. Just tried it again, and I still get errors.
August 8, 2008 at 8:56 am
depends on how many reports he has to do and when they have to be done. since the sql is coming from existing code, making 'improvements' may open up an entirely new can of worms.
😉
August 8, 2008 at 8:57 am
Closed visual studio and reopened it, and now it works...
August 8, 2008 at 9:04 am
antonio.collins (8/8/2008)
depends on how many reports he has to do and when they have to be done. since the sql is coming from existing code, making 'improvements' may open up an entirely new can of worms.😉
True, but I looked at the SQL and it could use some improvement. It has the potential to be a real server killer which doesn't surprise me since it is a report from an ERP system.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 11:56 am
Alright, now I have a new problem with it.
Every time I try to run it, it's asking for parameters to be input for Range, EndDate, and HolidayCnt. All of these are supposed to be calculated in the code itself, not passed to it.
August 8, 2008 at 12:07 pm
There are already more worms that I care to think about. We could all go fishing for months and still have a healthy supply.
August 8, 2008 at 12:08 pm
go into the form's parameters and remove them:
layout tab
report menu
report parameters
August 8, 2008 at 12:15 pm
Alright, I got the parameters taken away, but now I'm back to getting this error message:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while retrieving the parameters in the query.
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "Declare @startDate SMALLDATETIME, @endDate SMALLDATETIME, @range INT, @holidaycnt INT
Declare @HolidayTbl table (holidayDate SMALLDATETIME)
-- Fill Start/End dates to obtain date range
SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)
SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today
--Fill Holiday dates based on facility/State/Country
insert into @HolidayTbl (holidayDate)values ('20080101') -- New Years
insert into @HolidayTbl (holidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)
insert into @HolidayTbl (holidayDate)values ('20080704') -- 4th of July
insert into @HolidayTbl (holidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)
insert into @h...", the current limit of "4" is insufficient.
------------------------------
ADDITIONAL INFORMATION:
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "Declare @startDate SMALLDATETIME, @endDate SMALLDATETIME, @range INT, @holidaycnt INT
Declare @HolidayTbl table (holidayDate SMALLDATETIME)
-- Fill Start/End dates to obtain date range
SET @StartDate = cast(cast(datepart(month, dateadd(mm, -0, getdate())) as varchar) + '/' +
cast(datepart(day, dateadd(day, -datepart(day, getdate())+1, getdate()))as varchar) + '/' +
cast(datepart(year, dateadd(mm, -1, getdate())) as varchar) as datetime)
SET @EndDate = getdate()-1 -- Stops today from being counted as one of the days. Remove the -1 to include today
--Fill Holiday dates based on facility/State/Country
insert into @HolidayTbl (holidayDate)values ('20080101') -- New Years
insert into @HolidayTbl (holidayDate)values ('20080526') -- Memorial Day (UPDATE YEARLY)
insert into @HolidayTbl (holidayDate)values ('20080704') -- 4th of July
insert into @HolidayTbl (holidayDate)values ('20080901') -- Labor Day (UPDATE YEARLY)
insert into @h...", the current limit of "4" is insufficient. (System.Data)
------------------------------
BUTTONS:
OK
------------------------------
August 8, 2008 at 12:19 pm
Can you zip the rdl and attach it to the thread?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2008 at 12:21 pm
from the error message, it looks almost like you've still got the query in the dataset where command type = text.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply