#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
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>

#Temp Tables

Why cant I use the same temptable name i a stored procedure after i have droped it?

I use the Pubs database for the test case.

CREATE PROCEDURE spFulltUttrekk AS

SELECT *
INTO #temp
FROM Jobs

SELECT *
FROM #temp

DROP TABLE #temp

SELECT *
INTO #temp
FROM Employee

SELECT *
FROM #temp[posted and mailed, vnligen svara i nys]

Per (per-eivind-greva.sivertsen@.cgey.com) writes:
> Why cant I use the same temptable name i a stored procedure after i have
> droped it?
> I use the Pubs database for the test case.
> CREATE PROCEDURE spFulltUttrekk AS
> SELECT *
> INTO #temp
> FROM Jobs
> SELECT *
> FROM #temp
> DROP TABLE #temp
> SELECT *
> INTO #temp
> FROM Employee
> SELECT *
> FROM #temp

When SQL Server builds the query plan for a procedure, it builds the
plan for the entire procedure in one go, with one exception. If a
statement refers to a non-existing table, that statement is deferred
until run-time.

So when you create the procedure, SQL Server defers the plan for the
two SELECT statements. When execution hits the deferred statement, SQL
Server recompiles the procedure. And the entire procedure. So when it
finds a SELECT * INTO #temp, it thinks that's bad, because #temp does
already exist. At this point, the DROP TABLE statement has not been
executed, so SQL Server does not know that the table will go away.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

#temp table vs. @temp table variable in stored proc?

I have a complex stored procedure (it's the strip-packing one, Dan) that
uses several #temp tables along the way. There are typically 1000 to 50000
rows inserted into them during use. At the end of the procedure the final
results end up inserted into permanent tables. Concurrency is not a factor
here because the procedure is typically run only once a month.
My question is, could any performance improvement be gained by changing
them from #temp tables to @.temp table variables?http://www.aspfaq.com/2475
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jy8ydt3440yb$.dlg@.rpresser.invalid...
> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to
50000
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a
factor
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?|||Ross,
It is hard to tell you without knowing what kind of operations are you doing
with the temporary tables. SQL Server does not create statistics for table
variables, so for small number of rows this is a good option. The best way o
f
knowing this, is giving it a try and comparing performance results.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"Ross Presser" wrote:

> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to 5000
0
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a facto
r
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?
>|||Thank you both (Aaron, Alejandro) for the links. Given the large amount of
data, I think I'll stick with my #temp tables in this instance.|||yes, My experience is that with Small rowsets, the Table Variables are the
way to go.
TempTables, you can add indexes to, etc.
For large Rowsets, Temp Tables performed better in our situations.
Greg Jackson
Portland, OR

#Temp table question

I'm not sure if I understood this correctly.
If I create a temporary table with the name #SomeTempTab,
can anybody else see this table while I'm using it and before I drop it.
So if two users at the same time trying to create the table with this name
will they be different for each user or there will be a conflict?
Thanks,
Michael
On Fri, 31 Dec 2004 16:12:06 -0800, MichaelK wrote:

>I'm not sure if I understood this correctly.
>If I create a temporary table with the name #SomeTempTab,
>can anybody else see this table while I'm using it and before I drop it.
>So if two users at the same time trying to create the table with this name
>will they be different for each user or there will be a conflict?
Hi Michael,
There will be no conflict. SQL Server will generate a connection-specific
suffix to create a really unique table name in tempdb. Everytime you refer
to #SomeTempTab, SQL Server will append the suffix and look in "your"
version of the temporary table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo.
You were helpfull as well as with my another question.
It's what I was looking for.
Regards,
Michael
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ej7et0980uvbk8n3nheabcvn0lfq57as4a@.4ax.com...
> On Fri, 31 Dec 2004 16:12:06 -0800, MichaelK wrote:
>
> Hi Michael,
> There will be no conflict. SQL Server will generate a connection-specific
> suffix to create a really unique table name in tempdb. Everytime you refer
> to #SomeTempTab, SQL Server will append the suffix and look in "your"
> version of the temporary table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

#temp table - Checking if it exists

Hi,
I'd like to check if a #temp table I created in a previous session still
resides on the DB.
I've used the following code and have had no luck
*************
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]')
)
drop table #tempTry:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacques Victor" <jacques@.ideosphere.co.za> wrote in message
news:%23I0cQMOOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to check if a #temp table I created in a previous session still
> resides on the DB.
> I've used the following code and have had no luck
> *************
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]
'))
> drop table #temp
>

#temp table - Checking if it exists

Hi,
I'd like to check if a #temp table I created in a previous session still
resides on the DB.
I've used the following code and have had no luck
*************
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]'))
drop table #tempTry:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacques Victor" <jacques@.ideosphere.co.za> wrote in message
news:%23I0cQMOOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to check if a #temp table I created in a previous session still
> resides on the DB.
> I've used the following code and have had no luck
> *************
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]'))
> drop table #temp
>

#temp table - Checking if it exists

Hi,
I'd like to check if a #temp table I created in a previous session still
resides on the DB.
I've used the following code and have had no luck
*************
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]'))
drop table #temp
Try:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacques Victor" <jacques@.ideosphere.co.za> wrote in message
news:%23I0cQMOOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to check if a #temp table I created in a previous session still
> resides on the DB.
> I've used the following code and have had no luck
> *************
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[#temp]'))
> drop table #temp
>

#temp query

I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.
I've tried something like the following with no success:
Select * from tempdb..#tempTableu can try these options

select * from #tempTable

select * from tempdb.#tempTable|||From BOL :

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

* A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

* All other local temporary tables are dropped automatically at the end of the current session.

* Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

---------

Hence try using ## (global temp tables), if you need to query outside the stored procedure.|||Can you give me an example in a select statement. The temporary table shows up in tempdb sysobjects as:

#Dup______________________________________________ __________________________________________________ ________________00000000001A|||example

use pubs
go
select * into ##temp from authors
select * from ##temp
--or
select * from tempdb.##temp
--or
select * from tempdb..##temp|||I get "invalid object name" trying any of these. I even tried it straight from the tempdb.|||Give your stored procedure which creates the temp table and explain how you have queried the same outside the procedure.

##Temp tables

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.

CREATE PROCEDURE usp_CreateTakeoff
@.iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 .....
Field2 .....
)

-- Add Structural data
usp_AddStructural @.iEstimateID, 1, 'Structural'
usp_AddForming @.iEstimateID, 2, 'Forming'
...
...
...
GO

Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc..... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike BGlobal and local temp tables are created in tempdb.|||Global and local temp tables are created in tempdb.
Now, if I remember correctly, 1 (#) indicates global and 2 (#) indicates local.

So if multiple users executed the stored proc with ##Temp, then each connection would create a table unique to the connection? So multiple users could execute this proc without interfering with each other?

Mike B|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.|||Your memory fails you young padowan. ## is a global temp table which will cease to exist when the last connection to it ceases to exist. The # temp table is a local table for the duration of the process.
Not the first time, won't be the last I am afraid! :) Thanks for the correction.

Mike

##temp table already exists problem

Hello

I am using a temp table called ##temp in an SProc but often get the
message that the table already exists. Could this be because the SProc
is being run by more than 2 webpages at the same time?
Or is it because the sProc has an error and is not getting to the drop
table line?

I have tried adding a line to test if the object exists and to drop the
table before I create it. If I drop it will it affect another instance
of the sProc that is using the same table name?

If so is there a way around this?

Many thanks
NigelGlobal temp tables are visible to all connections, so if you have
multiple processes, then you can easily get errors like that. And if
you drop the table explicitly, one connection could drop the table
which was created by another connection.

Is there some specific reason why you can't use a local temp table? Or
perhaps you can use a permanent table, with @.@.SPID as part of the key?
If you can explain some more about what you're trying to do, then
someone may be able to suggest an alternative solution.

Simon|||Thanks Simon.
So If I use # instead of ## the table is connection based rather than
global. Id did sort of know that but you have made it clear. I will
change to # instead of ##.

The situation is that I have a website that runs an SProc to get the
content of a banner. This runs for most pages on the site.
nigel