declare table problem in SQL 2005
-
I am having the following problem with SQL 2005. In a stored procedure I am declaring a table variable and for whatever reasdon the 'create procedure' or 'alter procedure' statement hangs when it is run. If I change the table variable to a temporary table it doesn't hang (this is what I ahve done to temporarily resolve the issue). When it does hang the only error that I get is when I cancel the alter/create query, otherwise it just hangs.
The only error that I get is:
Msg 1750, Level 16, State 0, Procedure ztestprocedure, Line 6
Could not create constraint. See previous errors.
The database was a sql 2000 datbase that has been converted to sql 2005 and is running in 9.0 compatibility mode. Sql 2005 is the developer edition with sp2a applied. Any assistance will be appreciated.
Here is the create procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[ztestprocedure]
AS
BEGIN
SET NOCOUNT ON
declare @lcl_table table
(
[Client_AcctType_ID] [int] NULL,
[Client_ID] [int] NULL,
[IEDate_ID] [int] NULL DEFAULT ((0)),
[IEType_ID] [int] NULL DEFAULT ((0)),
[ListOrder] [int] NULL DEFAULT ((0)),
[UseAve] [int] NULL DEFAULT ((0)),
[Amt] [decimal](18, 2) NULL,
[IESource] [char](5) NULL DEFAULT (' '),
[AcctType_ID] [int] NULL,
[RecType] [char](2) NULL,
[CustomerIESum_ID] [int] NULL DEFAULT ((0)),
[CustomerDebtIESum_ID] [int] NULL DEFAULT ((0)),
[CustomerIEAcct_Descr] [char](60) NULL DEFAULT (' '),
[CustomerIEAcct_OrderField] [int] NULL,
[CustomerIEAcct_ID] [int] NULL DEFAULT ((0)),
[CustomerDebtIEAcct_ID] [int] NULL DEFAULT ((0)),
[AcctsIE_ID] [int] NULL DEFAULT ((0)),
[AcctsDebtIE_ID] [int] NULL DEFAULT ((0)),
[AcctDebtIEList_ID] [int] NULL DEFAULT ((0)),
[AcctIEType_ID] [int] NULL DEFAULT ((0)),
[AcctsIE_Descr] [char](60) NULL DEFAULT (' '),
[AcctIEList_ID] [int] NULL DEFAULT ((0)),
[LangType_ID] [int] NULL DEFAULT ((0)),
[UseAcct] [int] NULL DEFAULT ((0)),
[AcctIEList_Descr] [char](60) NULL DEFAULT (' '),
[TransDescr] [char](60) NULL DEFAULT (' '),
[AcctIEList_OrderField] [int] NULL DEFAULT ((0)),
[Comm_NetSales] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_COGS] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_GrossPRofit] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_OperExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_OperIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NonOperExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NonOperExp_IncStmt] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_Gain_Loss] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_EBIT] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_EBIT_IncStmt] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_EBITDA] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NIBeforeTax] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NIBeforeTax_IncStmt] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NetIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NetIncome_IncStmt] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_IntExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_EndCash] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_Inflows] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_Outflows] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_BegCash] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_OwnerWith] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_NetIncAfterOwnerWith] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_AgNetIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_PersNetIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_IncomeAccruals] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_ExpenseAccruals] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_TotOperIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_GrossMargin] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_TotOperExp] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_NetOperIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_MarginDebtService] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_EndCash] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_Inflows] [decimal](18, 2) NULL DEFAULT ((0)),
[AG_OutFlows] [decimal](18, 2) NULL DEFAULT ((0)),
[Ag_BegCash] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_TotIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_DedExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_EBIT] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_IntExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_NIBeForeTax] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_OthExp] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_Taxes] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_NetIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_PrinPay] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_MarginDebtService] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_Finance] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_Capital] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_EndCash] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_InFlows] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_OutFlows] [decimal](18, 2) NULL DEFAULT ((0)),
[Pers_BegCash] [decimal](18, 2) NULL DEFAULT ((0)),
[OrderList] [int] NULL,
[RTPSort] [char](25) NULL,
[DescrFarm] [char](25) NULL,
[DescrComm] [char](25) NULL,
[DescrPers] [char](25) NULL,
[AveCnt] [int] NULL DEFAULT ((0)),
[cnt_AcctType] [int] NULL DEFAULT ((0)),
[To_Amt] [decimal](18, 2) NULL,
[To_AG_TotOperIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[To_Comm_NetSales] [decimal](18, 2) NULL DEFAULT ((0)),
[To_Pers_TotIncome] [decimal](18, 2) NULL DEFAULT ((0)),
[Comm_Finance] [decimal](18, 2) NULL DEFAULT ((0)),
[Ag_Finance] [decimal](18, 2) NULL DEFAULT ((0)),
[Auto_Manual] [tinyint] NULL DEFAULT ((0)),
[PercentChg] [decimal](18, 2) NULL DEFAULT ((0)),
[HideAvg] [int] NULL,
[HideVar] [int] NULL
)
RETURN
End
-
this proc creates fine on my system: standard 2005 sp1
---------------------------------------
elsasoft.org -
OK, unfortunately when I tested it I tested it on a local sql 2005 express sp2a version and then a remote sql 2005 developer sp2a version and was able to replicate the results.
I have since restarted the remote sql 2005 developer sp2a server as well as the local sql service and am able to run the sp without issue.
I feel much better.... not.
Sorry for not trying the restart previous to posting, I've just gotten used to not having to restart SQL server to resolve issues.
-
don't be sorry - it was a valid question
---------------------------------------
elsasoft.org
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply