|
|
You're in the
sql server reporting services
group:#temp tables
sql server reporting services:
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
Following 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. [quoted text, click to view] "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 > > >
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. [quoted text, click to view] "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.... [quoted text, click to view] "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 > >> > > >> > > >> > > >> > > >> > >> > >> > >
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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 >>> >> > >>> >> > >>> >> > >>> >> > >>> >> >>> >> >>> >> >>> >>> >>> > >
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 [quoted text, click to view] "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 >> >> > >> >> > >> >> > >> >> > >> >> >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |