Operand type clash: int is incompatible with datetime2
Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2837
Printed Date: 04-Jun-2024 at 5:58am
Topic: Operand type clash: int is incompatible with datetime2
Posted By: smi-mark
Subject: Operand type clash: int is incompatible with datetime2
Date Posted: 19-Jul-2011 at 2:07pm
I'm updating an old project that still uses DF Classic. I've created a stored procedure and I'm getting the above error.
There is an issue with mapping procedures with temporary tables, so i have a real table that gets returned.
Here is the procedure:
CREATE PROCEDURE procCreateAccountTrx
@pStartPeriod int,
@pEndPeriod int,
@pStartYear int,
@pEndYear int,
@pStartDate datetime,
@pEndDate datetime,
@pAccounts varchar(4000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @StartPeriod int,
@EndPeriod int,
@StartYear int,
@EndYear int,
@StartDate datetime,
@EndDate datetime,
@Accounts varchar(4000)
SELECT @StartPeriod = @pStartPeriod,
@EndPeriod = @pEndPeriod,
@StartYear = @pStartYear,
@EndYear = @pEndYear,
@StartDate = @pStartDate,
@EndDate = @pEndDate,
@Accounts = @pAccounts
IF OBJECT_ID (N'[tempTransactions]', N'U') IS NOT NULL DROP TABLE [tempTransactions]
CREATE TABLE [tempTransactions](
[AccountId] [int] NOT NULL,
[SubAccountId] [int] NOT NULL,
[OpeningBalance] [money] NOT NULL,
[DebitAmount] [money] NOT NULL,
[CreditAmount] [money] NOT NULL,
[Reference] [varchar] (50) NOT NULL,
[BatchName] [varchar] (50) NOT NULL,
[Period] [int] NOT NULL,
[Year] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL
)
INSERT INTO [tempTransactions]
exec CreateAccountTrx @StartPeriod, @EndPeriod, @StartYear, @EndYear, @StartDate, @EndDate, @Accounts
select a.Number, a.Description, s.Code, t.OpeningBalance, t.DebitAmount, t.CreditAmount, t.Reference,
t.BatchName, t.Period, t.Year, t.TransactionDate
From [tempTransactions] as t
INNER JOIN GLAccount as A ON t.AccountId = a.Id
INNER JOIN GLSubAccount as S on t.SubAccountId = s.Id
END
GO
|
|
Replies:
Posted By: mikewishart
Date Posted: 22-Jul-2011 at 3:40pm
Try adding the following line directly after BEGIN
SET FMTONLY OFF
When trying to get information from a stored procedure, the framework calls the proc with all null parameters and with FMTONLY set to ON. Generally this fails with temp tables.
|
Posted By: smi-mark
Date Posted: 30-Aug-2011 at 1:41pm
Thanks that's great to know! The initial problem was solved by using another server, but this saves me from having to rename my temp tables/comment stuff out while I map it.
|
|