#temp tables

I have the following sproc that has been in production for severa months.
When attempting to set this sproc as my data source I get the following error.
There is an error in the query. Implicit conversion from data type
sql_variant to int is not allowed. Use the CONVERT function to run this query.
If I comment most of the sproc except for the create temp table and insert
into it...then it will work (which does me no good).
Please help! I've tried using global temp tables and even just using a
real table that's being populated...but I sitll get the same error.
CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date datetime = null, @.summary bit = 0, @.GroupVal char(1) as
declare @.LastMonth datetime
if @.date is null
set @.date = getdate()
set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
Cast(year(@.date) as varchar(4)) as datetime)
CREATE TABLE #tmpSalesCounts
(
DayCode tinyint,
SalesRepName varchar(30),
JobCount int,
MailCount int
)
IF @.GroupVal = 'C'
BEGIN
insert into #tmpSalesCounts
select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
sum(jobQuantity) as MailCount
from
(select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
JobQuantity
from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup where
SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
group by CSRName
END
ELSE
BEGIN
insert into #tmpSalesCounts
select 1 as DayCode, SalesRepName, count(*) as JobCount, sum(jobQuantity)
as MailCount
from
(select convert( varchar(14), OrderDate, 107) as OrderDate, SalesRepName,
JobQuantity
from vwJobs where salesrepid in (select SalesRepID from tblSalesRepGroup
where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
group by SalesRepName
END
IF @.Summary = 0
select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
isnull(MailCount, 0) as MailPieces
from #tmpSalesCounts t1
right join (select periodID, PeriodDesc, FirstName+' '+LastName as
SalesRepName from tblReportPeriods cross join tblUsers
where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
order by t2.SalesRepName, PeriodID
ELSE
select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
isnull(Sum(MailCount), 0) as MailPieces
from #tmpSalesCounts t1
right join (select periodID, PeriodDesc, FirstName+' '+LastName as
SalesRepName from tblReportPeriods cross join tblUsers
where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
group by PeriodID, PeriodDesc
order by PeriodID
GOFollowing up on my own message. It seems to have something to do with the
creation of the #temp table. I gave the credentials I'm using owner access
to the db, but that does not seem to help.
"FL Jim" wrote:
> I have the following sproc that has been in production for severa months.
> When attempting to set this sproc as my data source I get the following error.
> There is an error in the query. Implicit conversion from data type
> sql_variant to int is not allowed. Use the CONVERT function to run this query.
> If I comment most of the sproc except for the create temp table and insert
> into it...then it will work (which does me no good).
> Please help! I've tried using global temp tables and even just using a
> real table that's being populated...but I sitll get the same error.
>
> CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date datetime => null, @.summary bit = 0, @.GroupVal char(1) as
>
> declare @.LastMonth datetime
> if @.date is null
> set @.date = getdate()
> set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
> Cast(year(@.date) as varchar(4)) as datetime)
> CREATE TABLE #tmpSalesCounts
> (
> DayCode tinyint,
> SalesRepName varchar(30),
> JobCount int,
> MailCount int
> )
> IF @.GroupVal = 'C'
> BEGIN
> insert into #tmpSalesCounts
> select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
> sum(jobQuantity) as MailCount
> from
> (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
> JobQuantity
> from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup where
> SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> group by CSRName
> END
> ELSE
> BEGIN
> insert into #tmpSalesCounts
> select 1 as DayCode, SalesRepName, count(*) as JobCount, sum(jobQuantity)
> as MailCount
> from
> (select convert( varchar(14), OrderDate, 107) as OrderDate, SalesRepName,
> JobQuantity
> from vwJobs where salesrepid in (select SalesRepID from tblSalesRepGroup
> where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> group by SalesRepName
> END
>
> IF @.Summary = 0
> select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
> isnull(MailCount, 0) as MailPieces
> from #tmpSalesCounts t1
> right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> SalesRepName from tblReportPeriods cross join tblUsers
> where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> order by t2.SalesRepName, PeriodID
> ELSE
> select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
> isnull(Sum(MailCount), 0) as MailPieces
> from #tmpSalesCounts t1
> right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> SalesRepName from tblReportPeriods cross join tblUsers
> where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> group by PeriodID, PeriodDesc
> order by PeriodID
> GO
>
>|||Please answer the following question to enable me to help you.
1. Does it work from Query Analyzer?
2. Do you get this error from the data tab or when you try to view it?
3. Is this an error in development or in production?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
>I have the following sproc that has been in production for severa months.
> When attempting to set this sproc as my data source I get the following
> error.
> There is an error in the query. Implicit conversion from data type
> sql_variant to int is not allowed. Use the CONVERT function to run this
> query.
> If I comment most of the sproc except for the create temp table and insert
> into it...then it will work (which does me no good).
> Please help! I've tried using global temp tables and even just using a
> real table that's being populated...but I sitll get the same error.
>
> CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date datetime => null, @.summary bit = 0, @.GroupVal char(1) as
>
> declare @.LastMonth datetime
> if @.date is null
> set @.date = getdate()
> set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
> Cast(year(@.date) as varchar(4)) as datetime)
> CREATE TABLE #tmpSalesCounts
> (
> DayCode tinyint,
> SalesRepName varchar(30),
> JobCount int,
> MailCount int
> )
> IF @.GroupVal = 'C'
> BEGIN
> insert into #tmpSalesCounts
> select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
> sum(jobQuantity) as MailCount
> from
> (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
> JobQuantity
> from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup where
> SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> group by CSRName
> END
> ELSE
> BEGIN
> insert into #tmpSalesCounts
> select 1 as DayCode, SalesRepName, count(*) as JobCount, sum(jobQuantity)
> as MailCount
> from
> (select convert( varchar(14), OrderDate, 107) as OrderDate, SalesRepName,
> JobQuantity
> from vwJobs where salesrepid in (select SalesRepID from tblSalesRepGroup
> where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> group by SalesRepName
> END
>
> IF @.Summary = 0
> select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
> isnull(MailCount, 0) as MailPieces
> from #tmpSalesCounts t1
> right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> SalesRepName from tblReportPeriods cross join tblUsers
> where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> order by t2.SalesRepName, PeriodID
> ELSE
> select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
> isnull(Sum(MailCount), 0) as MailPieces
> from #tmpSalesCounts t1
> right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> SalesRepName from tblReportPeriods cross join tblUsers
> where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> group by PeriodID, PeriodDesc
> order by PeriodID
> GO
>
>|||1) Yes
2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
datetime, @.summary, @.GroupVal when trying to connect to the data source
3) Development.
I found another related thread and have tried their suggestion. I connect
to a table in the db, just to get myself to the Data tab, then switch the
Commany Type to stored procedure and just enter the sproc name. It seems to
work this way. It's rather strange though, because all of my other reports I
was able to just enter the exec sproc (with parameter fields) in the Query
String box of the new report wizard. I guess it's just a quirk...I don't
know.
"Bruce L-C [MVP]" wrote:
> Please answer the following question to enable me to help you.
> 1. Does it work from Query Analyzer?
> 2. Do you get this error from the data tab or when you try to view it?
> 3. Is this an error in development or in production?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
> >I have the following sproc that has been in production for severa months.
> > When attempting to set this sproc as my data source I get the following
> > error.
> >
> > There is an error in the query. Implicit conversion from data type
> > sql_variant to int is not allowed. Use the CONVERT function to run this
> > query.
> >
> > If I comment most of the sproc except for the create temp table and insert
> > into it...then it will work (which does me no good).
> >
> > Please help! I've tried using global temp tables and even just using a
> > real table that's being populated...but I sitll get the same error.
> >
> >
> >
> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date datetime => > null, @.summary bit = 0, @.GroupVal char(1) as
> >
> >
> > declare @.LastMonth datetime
> >
> > if @.date is null
> > set @.date = getdate()
> >
> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
> > Cast(year(@.date) as varchar(4)) as datetime)
> >
> > CREATE TABLE #tmpSalesCounts
> > (
> > DayCode tinyint,
> > SalesRepName varchar(30),
> > JobCount int,
> > MailCount int
> > )
> >
> > IF @.GroupVal = 'C'
> > BEGIN
> > insert into #tmpSalesCounts
> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
> > sum(jobQuantity) as MailCount
> > from
> > (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
> > JobQuantity
> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup where
> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> >
> > group by CSRName
> >
> > END
> > ELSE
> > BEGIN
> > insert into #tmpSalesCounts
> > select 1 as DayCode, SalesRepName, count(*) as JobCount, sum(jobQuantity)
> > as MailCount
> > from
> > (select convert( varchar(14), OrderDate, 107) as OrderDate, SalesRepName,
> > JobQuantity
> > from vwJobs where salesrepid in (select SalesRepID from tblSalesRepGroup
> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> >
> > group by SalesRepName
> >
> > END
> >
> >
> > IF @.Summary = 0
> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
> > isnull(MailCount, 0) as MailPieces
> > from #tmpSalesCounts t1
> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> > SalesRepName from tblReportPeriods cross join tblUsers
> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> > order by t2.SalesRepName, PeriodID
> > ELSE
> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
> > isnull(Sum(MailCount), 0) as MailPieces
> > from #tmpSalesCounts t1
> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> > SalesRepName from tblReportPeriods cross join tblUsers
> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select SalesRepID
> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> > group by PeriodID, PeriodDesc
> > order by PeriodID
> > GO
> >
> >
> >
> >
>
>|||Good. Glad the problem is solved. I use SP a lot but I don't combine the SP
with the wizard. I tend to start with an empty report (add item instead of
add report). Create the dataset calling the SP. Then drop a table on the
layout and drag and drop fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:83876B6B-96E6-4BE9-8152-68DDF2D5FBA8@.microsoft.com...
> 1) Yes
> 2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
> datetime, @.summary, @.GroupVal when trying to connect to the data source
> 3) Development.
> I found another related thread and have tried their suggestion. I connect
> to a table in the db, just to get myself to the Data tab, then switch the
> Commany Type to stored procedure and just enter the sproc name. It seems
> to
> work this way. It's rather strange though, because all of my other
> reports I
> was able to just enter the exec sproc (with parameter fields) in the Query
> String box of the new report wizard. I guess it's just a quirk...I don't
> know.
> "Bruce L-C [MVP]" wrote:
>> Please answer the following question to enable me to help you.
>> 1. Does it work from Query Analyzer?
>> 2. Do you get this error from the data tab or when you try to view it?
>> 3. Is this an error in development or in production?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
>> >I have the following sproc that has been in production for severa
>> >months.
>> > When attempting to set this sproc as my data source I get the following
>> > error.
>> >
>> > There is an error in the query. Implicit conversion from data type
>> > sql_variant to int is not allowed. Use the CONVERT function to run this
>> > query.
>> >
>> > If I comment most of the sproc except for the create temp table and
>> > insert
>> > into it...then it will work (which does me no good).
>> >
>> > Please help! I've tried using global temp tables and even just using
>> > a
>> > real table that's being populated...but I sitll get the same error.
>> >
>> >
>> >
>> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date
>> > datetime =>> > null, @.summary bit = 0, @.GroupVal char(1) as
>> >
>> >
>> > declare @.LastMonth datetime
>> >
>> > if @.date is null
>> > set @.date = getdate()
>> >
>> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
>> > Cast(year(@.date) as varchar(4)) as datetime)
>> >
>> > CREATE TABLE #tmpSalesCounts
>> > (
>> > DayCode tinyint,
>> > SalesRepName varchar(30),
>> > JobCount int,
>> > MailCount int
>> > )
>> >
>> > IF @.GroupVal = 'C'
>> > BEGIN
>> > insert into #tmpSalesCounts
>> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
>> > sum(jobQuantity) as MailCount
>> > from
>> > (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
>> > JobQuantity
>> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup
>> > where
>> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >
>> > group by CSRName
>> >
>> > END
>> > ELSE
>> > BEGIN
>> > insert into #tmpSalesCounts
>> > select 1 as DayCode, SalesRepName, count(*) as JobCount,
>> > sum(jobQuantity)
>> > as MailCount
>> > from
>> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
>> > SalesRepName,
>> > JobQuantity
>> > from vwJobs where salesrepid in (select SalesRepID from
>> > tblSalesRepGroup
>> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >
>> > group by SalesRepName
>> >
>> > END
>> >
>> >
>> > IF @.Summary = 0
>> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
>> > isnull(MailCount, 0) as MailPieces
>> > from #tmpSalesCounts t1
>> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> > SalesRepName from tblReportPeriods cross join tblUsers
>> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> > SalesRepID
>> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> > order by t2.SalesRepName, PeriodID
>> > ELSE
>> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
>> > isnull(Sum(MailCount), 0) as MailPieces
>> > from #tmpSalesCounts t1
>> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> > SalesRepName from tblReportPeriods cross join tblUsers
>> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> > SalesRepID
>> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> > group by PeriodID, PeriodDesc
>> > order by PeriodID
>> > GO
>> >
>> >
>> >
>> >
>>|||I thought it was fixed...I failed to realize that while my dataset returns
data in the data tab, I can't see any fields for the sproc in the layout view
for the report. Basically, it's attached and returning data in the data
tab, but the fields are accesible in the report. Back to the drawing board.
I don't know if there's a way to refresh this or not...
"Bruce L-C [MVP]" wrote:
> Good. Glad the problem is solved. I use SP a lot but I don't combine the SP
> with the wizard. I tend to start with an empty report (add item instead of
> add report). Create the dataset calling the SP. Then drop a table on the
> layout and drag and drop fields.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:83876B6B-96E6-4BE9-8152-68DDF2D5FBA8@.microsoft.com...
> > 1) Yes
> > 2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
> > datetime, @.summary, @.GroupVal when trying to connect to the data source
> > 3) Development.
> >
> > I found another related thread and have tried their suggestion. I connect
> > to a table in the db, just to get myself to the Data tab, then switch the
> > Commany Type to stored procedure and just enter the sproc name. It seems
> > to
> > work this way. It's rather strange though, because all of my other
> > reports I
> > was able to just enter the exec sproc (with parameter fields) in the Query
> > String box of the new report wizard. I guess it's just a quirk...I don't
> > know.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Please answer the following question to enable me to help you.
> >> 1. Does it work from Query Analyzer?
> >> 2. Do you get this error from the data tab or when you try to view it?
> >> 3. Is this an error in development or in production?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> >> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
> >> >I have the following sproc that has been in production for severa
> >> >months.
> >> > When attempting to set this sproc as my data source I get the following
> >> > error.
> >> >
> >> > There is an error in the query. Implicit conversion from data type
> >> > sql_variant to int is not allowed. Use the CONVERT function to run this
> >> > query.
> >> >
> >> > If I comment most of the sproc except for the create temp table and
> >> > insert
> >> > into it...then it will work (which does me no good).
> >> >
> >> > Please help! I've tried using global temp tables and even just using
> >> > a
> >> > real table that's being populated...but I sitll get the same error.
> >> >
> >> >
> >> >
> >> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date
> >> > datetime => >> > null, @.summary bit = 0, @.GroupVal char(1) as
> >> >
> >> >
> >> > declare @.LastMonth datetime
> >> >
> >> > if @.date is null
> >> > set @.date = getdate()
> >> >
> >> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
> >> > Cast(year(@.date) as varchar(4)) as datetime)
> >> >
> >> > CREATE TABLE #tmpSalesCounts
> >> > (
> >> > DayCode tinyint,
> >> > SalesRepName varchar(30),
> >> > JobCount int,
> >> > MailCount int
> >> > )
> >> >
> >> > IF @.GroupVal = 'C'
> >> > BEGIN
> >> > insert into #tmpSalesCounts
> >> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
> >> > sum(jobQuantity) as MailCount
> >> > from
> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
> >> > JobQuantity
> >> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup
> >> > where
> >> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> >> >
> >> > group by CSRName
> >> >
> >> > END
> >> > ELSE
> >> > BEGIN
> >> > insert into #tmpSalesCounts
> >> > select 1 as DayCode, SalesRepName, count(*) as JobCount,
> >> > sum(jobQuantity)
> >> > as MailCount
> >> > from
> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
> >> > SalesRepName,
> >> > JobQuantity
> >> > from vwJobs where salesrepid in (select SalesRepID from
> >> > tblSalesRepGroup
> >> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
> >> >
> >> > group by SalesRepName
> >> >
> >> > END
> >> >
> >> >
> >> > IF @.Summary = 0
> >> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0) Jobs,
> >> > isnull(MailCount, 0) as MailPieces
> >> > from #tmpSalesCounts t1
> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> >> > SalesRepName from tblReportPeriods cross join tblUsers
> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
> >> > SalesRepID
> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> >> > order by t2.SalesRepName, PeriodID
> >> > ELSE
> >> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
> >> > isnull(Sum(MailCount), 0) as MailPieces
> >> > from #tmpSalesCounts t1
> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
> >> > SalesRepName from tblReportPeriods cross join tblUsers
> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
> >> > SalesRepID
> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
> >> > group by PeriodID, PeriodDesc
> >> > order by PeriodID
> >> > GO
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Yes. Click on the refresh fields button to the right of the ... in the data
tab (it looks like the refresh button for IE).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:4FBB92FE-95CE-4FC5-9918-C9B26081895B@.microsoft.com...
>I thought it was fixed...I failed to realize that while my dataset returns
> data in the data tab, I can't see any fields for the sproc in the layout
> view
> for the report. Basically, it's attached and returning data in the data
> tab, but the fields are accesible in the report. Back to the drawing
> board.
> I don't know if there's a way to refresh this or not...
> "Bruce L-C [MVP]" wrote:
>> Good. Glad the problem is solved. I use SP a lot but I don't combine the
>> SP
>> with the wizard. I tend to start with an empty report (add item instead
>> of
>> add report). Create the dataset calling the SP. Then drop a table on the
>> layout and drag and drop fields.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:83876B6B-96E6-4BE9-8152-68DDF2D5FBA8@.microsoft.com...
>> > 1) Yes
>> > 2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
>> > datetime, @.summary, @.GroupVal when trying to connect to the data source
>> > 3) Development.
>> >
>> > I found another related thread and have tried their suggestion. I
>> > connect
>> > to a table in the db, just to get myself to the Data tab, then switch
>> > the
>> > Commany Type to stored procedure and just enter the sproc name. It
>> > seems
>> > to
>> > work this way. It's rather strange though, because all of my other
>> > reports I
>> > was able to just enter the exec sproc (with parameter fields) in the
>> > Query
>> > String box of the new report wizard. I guess it's just a quirk...I
>> > don't
>> > know.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Please answer the following question to enable me to help you.
>> >> 1. Does it work from Query Analyzer?
>> >> 2. Do you get this error from the data tab or when you try to view it?
>> >> 3. Is this an error in development or in production?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> >> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
>> >> >I have the following sproc that has been in production for severa
>> >> >months.
>> >> > When attempting to set this sproc as my data source I get the
>> >> > following
>> >> > error.
>> >> >
>> >> > There is an error in the query. Implicit conversion from data type
>> >> > sql_variant to int is not allowed. Use the CONVERT function to run
>> >> > this
>> >> > query.
>> >> >
>> >> > If I comment most of the sproc except for the create temp table and
>> >> > insert
>> >> > into it...then it will work (which does me no good).
>> >> >
>> >> > Please help! I've tried using global temp tables and even just
>> >> > using
>> >> > a
>> >> > real table that's being populated...but I sitll get the same error.
>> >> >
>> >> >
>> >> >
>> >> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date
>> >> > datetime =>> >> > null, @.summary bit = 0, @.GroupVal char(1) as
>> >> >
>> >> >
>> >> > declare @.LastMonth datetime
>> >> >
>> >> > if @.date is null
>> >> > set @.date = getdate()
>> >> >
>> >> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
>> >> > Cast(year(@.date) as varchar(4)) as datetime)
>> >> >
>> >> > CREATE TABLE #tmpSalesCounts
>> >> > (
>> >> > DayCode tinyint,
>> >> > SalesRepName varchar(30),
>> >> > JobCount int,
>> >> > MailCount int
>> >> > )
>> >> >
>> >> > IF @.GroupVal = 'C'
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity) as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
>> >> > JobQuantity
>> >> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup
>> >> > where
>> >> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by CSRName
>> >> >
>> >> > END
>> >> > ELSE
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity)
>> >> > as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
>> >> > SalesRepName,
>> >> > JobQuantity
>> >> > from vwJobs where salesrepid in (select SalesRepID from
>> >> > tblSalesRepGroup
>> >> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by SalesRepName
>> >> >
>> >> > END
>> >> >
>> >> >
>> >> > IF @.Summary = 0
>> >> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0)
>> >> > Jobs,
>> >> > isnull(MailCount, 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> >> > order by t2.SalesRepName, PeriodID
>> >> > ELSE
>> >> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
>> >> > isnull(Sum(MailCount), 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> >> > group by PeriodID, PeriodDesc
>> >> > order by PeriodID
>> >> > GO
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||I recall way back when RS was in beta that SET NOCOUNT ON would often fix
this soft of problem, the code you gave in your sproc did not have this I
wonder if you could try adding this and see.
Regards
K Duncan
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:4FBB92FE-95CE-4FC5-9918-C9B26081895B@.microsoft.com...
>I thought it was fixed...I failed to realize that while my dataset returns
> data in the data tab, I can't see any fields for the sproc in the layout
> view
> for the report. Basically, it's attached and returning data in the data
> tab, but the fields are accesible in the report. Back to the drawing
> board.
> I don't know if there's a way to refresh this or not...
> "Bruce L-C [MVP]" wrote:
>> Good. Glad the problem is solved. I use SP a lot but I don't combine the
>> SP
>> with the wizard. I tend to start with an empty report (add item instead
>> of
>> add report). Create the dataset calling the SP. Then drop a table on the
>> layout and drag and drop fields.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:83876B6B-96E6-4BE9-8152-68DDF2D5FBA8@.microsoft.com...
>> > 1) Yes
>> > 2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
>> > datetime, @.summary, @.GroupVal when trying to connect to the data source
>> > 3) Development.
>> >
>> > I found another related thread and have tried their suggestion. I
>> > connect
>> > to a table in the db, just to get myself to the Data tab, then switch
>> > the
>> > Commany Type to stored procedure and just enter the sproc name. It
>> > seems
>> > to
>> > work this way. It's rather strange though, because all of my other
>> > reports I
>> > was able to just enter the exec sproc (with parameter fields) in the
>> > Query
>> > String box of the new report wizard. I guess it's just a quirk...I
>> > don't
>> > know.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Please answer the following question to enable me to help you.
>> >> 1. Does it work from Query Analyzer?
>> >> 2. Do you get this error from the data tab or when you try to view it?
>> >> 3. Is this an error in development or in production?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> >> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
>> >> >I have the following sproc that has been in production for severa
>> >> >months.
>> >> > When attempting to set this sproc as my data source I get the
>> >> > following
>> >> > error.
>> >> >
>> >> > There is an error in the query. Implicit conversion from data type
>> >> > sql_variant to int is not allowed. Use the CONVERT function to run
>> >> > this
>> >> > query.
>> >> >
>> >> > If I comment most of the sproc except for the create temp table and
>> >> > insert
>> >> > into it...then it will work (which does me no good).
>> >> >
>> >> > Please help! I've tried using global temp tables and even just
>> >> > using
>> >> > a
>> >> > real table that's being populated...but I sitll get the same error.
>> >> >
>> >> >
>> >> >
>> >> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date
>> >> > datetime =>> >> > null, @.summary bit = 0, @.GroupVal char(1) as
>> >> >
>> >> >
>> >> > declare @.LastMonth datetime
>> >> >
>> >> > if @.date is null
>> >> > set @.date = getdate()
>> >> >
>> >> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
>> >> > Cast(year(@.date) as varchar(4)) as datetime)
>> >> >
>> >> > CREATE TABLE #tmpSalesCounts
>> >> > (
>> >> > DayCode tinyint,
>> >> > SalesRepName varchar(30),
>> >> > JobCount int,
>> >> > MailCount int
>> >> > )
>> >> >
>> >> > IF @.GroupVal = 'C'
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity) as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate, CSRName,
>> >> > JobQuantity
>> >> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup
>> >> > where
>> >> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by CSRName
>> >> >
>> >> > END
>> >> > ELSE
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity)
>> >> > as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
>> >> > SalesRepName,
>> >> > JobQuantity
>> >> > from vwJobs where salesrepid in (select SalesRepID from
>> >> > tblSalesRepGroup
>> >> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by SalesRepName
>> >> >
>> >> > END
>> >> >
>> >> >
>> >> > IF @.Summary = 0
>> >> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0)
>> >> > Jobs,
>> >> > isnull(MailCount, 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> >> > order by t2.SalesRepName, PeriodID
>> >> > ELSE
>> >> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
>> >> > isnull(Sum(MailCount), 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname = t2.salesrepname
>> >> > group by PeriodID, PeriodDesc
>> >> > order by PeriodID
>> >> > GO
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||If you can return data in the data tab then you will be fine. All you need
to do is click on the (not very discoverable) refresh fields button. It is
to the right of the ..., looks like the refresh button for IE.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"K Duncan" <keith@.sourcearray.com> wrote in message
news:%231xTouL1FHA.2792@.tk2msftngp13.phx.gbl...
>I recall way back when RS was in beta that SET NOCOUNT ON would often fix
>this soft of problem, the code you gave in your sproc did not have this I
>wonder if you could try adding this and see.
> Regards
> K Duncan
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:4FBB92FE-95CE-4FC5-9918-C9B26081895B@.microsoft.com...
>>I thought it was fixed...I failed to realize that while my dataset returns
>> data in the data tab, I can't see any fields for the sproc in the layout
>> view
>> for the report. Basically, it's attached and returning data in the data
>> tab, but the fields are accesible in the report. Back to the drawing
>> board.
>> I don't know if there's a way to refresh this or not...
>> "Bruce L-C [MVP]" wrote:
>> Good. Glad the problem is solved. I use SP a lot but I don't combine the
>> SP
>> with the wizard. I tend to start with an empty report (add item instead
>> of
>> add report). Create the dataset calling the SP. Then drop a table on the
>> layout and drag and drop fields.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:83876B6B-96E6-4BE9-8152-68DDF2D5FBA8@.microsoft.com...
>> > 1) Yes
>> > 2) I was entered in Exec spReports_DailySalesSummary2 @.GroupID, @.date
>> > datetime, @.summary, @.GroupVal when trying to connect to the data
>> > source
>> > 3) Development.
>> >
>> > I found another related thread and have tried their suggestion. I
>> > connect
>> > to a table in the db, just to get myself to the Data tab, then switch
>> > the
>> > Commany Type to stored procedure and just enter the sproc name. It
>> > seems
>> > to
>> > work this way. It's rather strange though, because all of my other
>> > reports I
>> > was able to just enter the exec sproc (with parameter fields) in the
>> > Query
>> > String box of the new report wizard. I guess it's just a quirk...I
>> > don't
>> > know.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Please answer the following question to enable me to help you.
>> >> 1. Does it work from Query Analyzer?
>> >> 2. Do you get this error from the data tab or when you try to view
>> >> it?
>> >> 3. Is this an error in development or in production?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> >> news:A4A39F90-9F48-446E-99DE-E73E9D0A512E@.microsoft.com...
>> >> >I have the following sproc that has been in production for severa
>> >> >months.
>> >> > When attempting to set this sproc as my data source I get the
>> >> > following
>> >> > error.
>> >> >
>> >> > There is an error in the query. Implicit conversion from data type
>> >> > sql_variant to int is not allowed. Use the CONVERT function to run
>> >> > this
>> >> > query.
>> >> >
>> >> > If I comment most of the sproc except for the create temp table and
>> >> > insert
>> >> > into it...then it will work (which does me no good).
>> >> >
>> >> > Please help! I've tried using global temp tables and even just
>> >> > using
>> >> > a
>> >> > real table that's being populated...but I sitll get the same error.
>> >> >
>> >> >
>> >> >
>> >> > CREATE proc dbo.spReports_DailySalesSummary @.GroupID int, @.date
>> >> > datetime =>> >> > null, @.summary bit = 0, @.GroupVal char(1) as
>> >> >
>> >> >
>> >> > declare @.LastMonth datetime
>> >> >
>> >> > if @.date is null
>> >> > set @.date = getdate()
>> >> >
>> >> > set @.LastMonth = Cast( Cast(Month(@.date) as varchar(2)) + '/1/' +
>> >> > Cast(year(@.date) as varchar(4)) as datetime)
>> >> >
>> >> > CREATE TABLE #tmpSalesCounts
>> >> > (
>> >> > DayCode tinyint,
>> >> > SalesRepName varchar(30),
>> >> > JobCount int,
>> >> > MailCount int
>> >> > )
>> >> >
>> >> > IF @.GroupVal = 'C'
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, CSRName as SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity) as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
>> >> > CSRName,
>> >> > JobQuantity
>> >> > from vwJobs where CSRid in (select SalesRepID from tblSalesRepGroup
>> >> > where
>> >> > SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by CSRName
>> >> >
>> >> > END
>> >> > ELSE
>> >> > BEGIN
>> >> > insert into #tmpSalesCounts
>> >> > select 1 as DayCode, SalesRepName, count(*) as JobCount,
>> >> > sum(jobQuantity)
>> >> > as MailCount
>> >> > from
>> >> > (select convert( varchar(14), OrderDate, 107) as OrderDate,
>> >> > SalesRepName,
>> >> > JobQuantity
>> >> > from vwJobs where salesrepid in (select SalesRepID from
>> >> > tblSalesRepGroup
>> >> > where SalesGroupID = @.GroupID ) and orderdate =@.date ) t1
>> >> >
>> >> > group by SalesRepName
>> >> >
>> >> > END
>> >> >
>> >> >
>> >> > IF @.Summary = 0
>> >> > select t2.SalesRepName, PeriodID, PeriodDesc, isnull(JobCount, 0)
>> >> > Jobs,
>> >> > isnull(MailCount, 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname =>> >> > t2.salesrepname
>> >> > order by t2.SalesRepName, PeriodID
>> >> > ELSE
>> >> > select PeriodID, PeriodDesc, isnull(sum(JobCount), 0) Jobs,
>> >> > isnull(Sum(MailCount), 0) as MailPieces
>> >> > from #tmpSalesCounts t1
>> >> > right join (select periodID, PeriodDesc, FirstName+' '+LastName as
>> >> > SalesRepName from tblReportPeriods cross join tblUsers
>> >> > where Reptype like '%' + @.GroupVal + '%' and UserId in (select
>> >> > SalesRepID
>> >> > from tblSalesRepGroup where SalesGroupID = @.GroupID ) )
>> >> > t2 on t1.Daycode = t2.PeriodID and t1.SalesRepname =>> >> > t2.salesrepname
>> >> > group by PeriodID, PeriodDesc
>> >> > order by PeriodID
>> >> > GO
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>