Temp tables and SP

Hi,

I have a StoredProcedure 'ProfitSP' in different databases and the ProfitSP
is same in each database.

If I execute ProfitSP as:

it works fine!

But if I try to create a temp table and then execute the same, it gives me
error as:
"Implicit conversion from data type sql_variant to datetime is not allowed.
Use the convert function to run this query."

---------------------------------------------------------
CREATE PROCEDURE dbo.ProfitSPAll (@OrderStartDate datetime, @OrderEndDate
datetime)
AS
create table #ProfitSPAllSites
( OrderYear datetime,
  OrderMonth datetime,
  Discount decimal(18,2),
  Total decimal(18,2),
  Profit decimal(18,2)
)
insert into #ProfitSPAllSites
Exec DB1.dbo.ProfitAll @OrderStartDate, @OrderEndDate
select * from #ProfitAll
GO
---------------------------------------------------------

I am using temp table because I have to execute SP's from different sites
and generate a report using MS Reporting Services (and I like temp tables). I
have used temp tables earlier but never had problems with 'datetime'. I tried
using CONVERT function but still didn't got it to work.

I will appreciate if you can let me know how to get past this datetime
problem. Thanks in advance.

Can you post the code of one of the ProfitAll stored procedures? Are the
OrderYear or OrderMonth columns sql_variant for some reason? Also, why are
these seperate columns? Why not use a single DATETIME or SMALLDATETIME
column for that data?


--
Adam Machanic
SQL Server MVP
<a rel="nofollow" style='text-decoration: none;' href="http://www.sqljunkies.com/weblog/amachanic" target="_blank">http://www.sqljunkies.com/weblog/amachanic</a>
--


"GJ" wrote in message

 > Hi,
 >
 > I have a StoredProcedure 'ProfitSP' in different databases and the
ProfitSP
 > is same in each database.
 >
 > If I execute ProfitSP as:

 > it works fine!
 >
 > But if I try to create a temp table and then execute the same, it gives me
 > error as:
 > "Implicit conversion from data type sql_variant to datetime is not
allowed.
 > Use the convert function to run this query."
 >
 > ---------------------------------------------------------

 > datetime)
 > AS
 > create table #ProfitSPAllSites
 > ( OrderYear datetime,
 > OrderMonth datetime,
 > Discount decimal(18,2),
 > Total decimal(18,2),
 > Profit decimal(18,2)
 > )
 > insert into #ProfitSPAllSites

 > select * from #ProfitAll
 > GO
 > ---------------------------------------------------------
 >
 > I am using temp table because I have to execute SP's from different sites
 > and generate a report using MS Reporting Services (and I like temp
tables). I
 > have used temp tables earlier but never had problems with 'datetime'. I
tried
 > using CONVERT function but still didn't got it to work.
 >
 > I will appreciate if you can let me know how to get past this datetime
 > problem. Thanks in advance.