February 18, 2008 at 7:26 am
Hi all,
I need to create a view that returns the last balance of all accounts as per period, for multiple databases.
In order to do this, I created a view for every database, that UNION ALL the fields required for every period:
select distinct
A.*
from (
select
cast(ACCTID as NVARCHAR(80)) as GLCode,
cast(FSCSYR as NVARCHAR(20)) as 'FinancialYear',
cast('1' as INT) as PeriodID,
cast(NETPERD1 as MONEY) as 'Value'
from DBNAME..GLAFS
where FSCSDSG = 'A' AND FSCSYR = (select YRCLSLST + 1 from DBNAME..GL01))
UNION ALL
(select
cast(ACCTID as NVARCHAR(80)) as GLCode,
cast(FSCSYR as NVARCHAR(20)) as 'FinancialYear',
cast('2' as INT) as PeriodID,
cast(NETPERD2 as MONEY) as 'Value'
from DBNAME..GLAFS
where FSCSDSG = 'A' AND FSCSYR = (select YRCLSLST + 1 from DBNAME..GL01))
UNION ALL
etc . . . .
etc . . . .
UNION ALL
(select
cast(ACCTID as NVARCHAR(80)) as GLCode,
cast(FSCSYR as NVARCHAR(20)) as 'FinancialYear',
cast('12' as INT) as PeriodID,
cast(NETPERD12 as MONEY) as 'Value'
from DBNAME..GLAFS
where FSCSDSG = 'A' AND FSCSYR = (select YRCLSLST + 1 from DBNAME..GL01))) A
In order to pass all the data to a DTS, I created a view that includes all the views for every database:
Select * from spend_view_db1
UNION ALL
Select * from spend_view_db2
etc . . . .
etc . . . .
UNION ALL
Select * from spend_view_db15
When running the view in SQL Query Analizer, I do get the correct result set, but the created view returns the error: "Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded."
According to the Microsoft kb, this error should have been fixed by installing SP4, but it is still limited to a maximum of 260 db references.
Any suggestions how I can refine my initial views?
Kind regards,
AJV
February 18, 2008 at 9:16 am
Each one looks like a select from one table. There's no way to refine that.
Run multiple queries if the fix doesn't work. run 1 transfer with 200 tables, then another with 200 tables, etc.
October 4, 2011 at 2:50 am
Meke a table value function and call that function from your view.
FUNCTION FunctionName()
RETURNS
@TempTable TABLE
(
ColumnName datatype
)
AS
BEGIN
INSERT INTO @TempTable Values(yourvalue1)
INSERT INTO @TempTable Values(yourvalue2)
--...
RETURN
END
Use this function in your view.
Create VIEW YourView
AS
select * from FunctionName()
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply