accessing several databases in a function error

Question

accessing several databases in a function error

marmarhm2002

Valued Member

Points: 59
More actions
December 24, 2011 at 4:57 am

#250464

hello,
I want to access several DBs in my function. I execute this function on different servers so I've tried to
restrict access to specific DBs in each server with IF clauses. I'm sure the execution does not reach the
wrong select statement but I'm getting this error:
Invalid object name 'ACCOUNT_00..IO_STOCK'. I guess SQL checks the existance of all the DBs and tables in my code no matter the execution reaches the specific select statement or not. can I somehow disable this control during exection? I tried to user TRY/CATCH but it seems that they're not applicable insides function. I could not also use transaction. I would appreciate any help.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[fncTest](@stock_code tinyint, @good_code char(10))
returns @tt Table (i_i numeric(10,2))
AS
begin
declare @i_i numeric(10,2), @io_db varchar(20)
select @io_db=ltrim(rtrim(isnull(io_db,''))) from STOCK where stock_code=@stock_code
if @io_db='ACCOUNT'
begin
Select @i_i=sum(amount) from ACCOUNT..IO_STOCK where good_code=@good_code
end
else
if @io_db='ACCOUNT_00'
begin
Select @i_i=sum(amount) from ACCOUNT_00..IO_STOCK where good_code=@good_code
end
else
if @io_db='ACCOUNT_02'
begin
Select @i_i=sum(amount) from ACCOUNT_02..IO_STOCK where good_code=@good_code
end
else
begin
set @i_i = 999999.99
end
set @i_i = isnull(@i_i,0)
Insert @tt (i_i) values (@i_i)
return
end

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

marmarhm2002 (12/24/2011)
hello,
I want to access several DBs in my function. I execute this function on different servers so I've tried to
restrict access to specific DBs in each server with IF clauses. I'm sure the execution does not reach the
wrong select statement but I'm getting this error:
Invalid object name 'ACCOUNT_00..IO_STOCK'. I guess SQL checks the existance of all the DBs and tables in my code no matter the execution reaches the specific select statement or not. can I somehow disable this control during exection? I tried to user TRY/CATCH but it seems that they're not applicable insides function. I could not also use transaction. I would appreciate any help.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[fncTest](@stock_code tinyint, @good_code char(10))
returns @tt Table (i_i numeric(10,2))
AS
begin
declare @i_i numeric(10,2), @io_db varchar(20)
select @io_db=ltrim(rtrim(isnull(io_db,''))) from STOCK where stock_code=@stock_code
if @io_db='ACCOUNT'
begin
Select @i_i=sum(amount) from ACCOUNT..IO_STOCK where good_code=@good_code
end
else
if @io_db='ACCOUNT_00'
begin
Select @i_i=sum(amount) from ACCOUNT_00..IO_STOCK where good_code=@good_code
end
else
if @io_db='ACCOUNT_02'
begin
Select @i_i=sum(amount) from ACCOUNT_02..IO_STOCK where good_code=@good_code
end
else
begin
set @i_i = 999999.99
end
set @i_i = isnull(@i_i,0)
Insert @tt (i_i) values (@i_i)
return
end

I don't encourage you to use a function to accomplish this at all !

Main reason, it hides a join and in this case a multi db join !

Have you tested something like this ?

e.g.

;

With cteSums

as (

Select cast(-1 as tinyint) as Origine

, sum(IOS.amount) as sum_amount

from ACCOUNT..IO_STOCK IOS

where good_code = @good_code

and exists ( select 1

from STOCK

where stock_code = @stock_code

and ltrim(rtrim(isnull(io_db, ''))) = 'ACCOUNT' )

union all

Select cast(0 as tinyint) as Origine

, sum(amount) as sum_amount

from ACCOUNT_00..IO_STOCK

where good_code = @good_code

and exists ( select 1

from STOCK

where stock_code = @stock_code

and ltrim(rtrim(isnull(io_db, ''))) = 'ACCOUNT_00' )

union all

Select cast(2 as tinyint) as Origine

, sum(amount) as sum_amount

from ACCOUNT_02..IO_STOCK

where good_code = @good_code

and exists ( select 1

from STOCK

where stock_code = @stock_code

and ltrim(rtrim(isnull(io_db, ''))) = 'ACCOUNT_02' )

)

Select coalesce(sum_amount, 999999.99) as sum_amount

from cteSums

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

marmarhm2002 Valued Member Points: 59 More actions · Answer 2

thank you, but I've used this function in several stored procedures and it works well in my main server which has all the DBs. but other servers in other offices just have on of the DBs so I get the error 'Invalid object'.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

There's really no way around compiler errors when using the 3 part naming convention.

What I've been doing when developing cross database queries is get rid of the 3 part naming convention altogether and use synonyms. For example there are 3 databases each with an inventory table. The synonyms become Inventory1, Inventory2 and Inventory3. All queries, SProcs and the like use the synonyms instead of 3 part naming for tables.

If database 3 doesn't yet exist on the dev server, then the Inventory3 synonym points to an inventory table in a database that does exist. As soon as database3 exists on the server, then drop and re-create the synonym for Inventory3 which points to the correct database.

The down side of this is maintaining a lot of synonyms.

Todd Fifield

marmarhm2002 Valued Member Points: 59 More actions · Answer 4

Thank you Todd, but it seems that I can't use synonyms inside a function.

I'm receiving the following error:

Invalid use of side-effecting or time-dependent operator in 'CREATE SYNONYM' within a function.

marmarhm2002 Valued Member Points: 59 More actions · Answer 5

Thank you, my problem solved by synonyms 🙂

first I tried to create synonyms inside my function which did not work

and I got the error.

but then I created the synonyms in a query and I used that synonym inside my function.

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 6

marmarhm2002 (12/26/2011)
Thank you Todd, but it seems that I can't use synonyms inside a function.
I'm receiving the following error:
Invalid use of side-effecting or time-dependent operator in 'CREATE SYNONYM' within a function.

You cannot create a synonym inside a function.

Prepare your synonyms before you create your function and you'll be OK.

CREATE SYNONYM Remote_Db1_Schema1_TheObject

FOR db1.Schema1.TheObject;

-- OR

create view RemoteV_Db1_Schema1_TheObject

as

select *

from db1.Schema1.TheObject

-- where 0 = 1 -- for those db that do not exist just point to an existing one and add this where clause

Once there objects are in place, you'll be able to use the solution I posted.

Johan

Learn to play, play to learn !