Link to home
Start Free TrialLog in
Avatar of qube09
qube09

asked on

using a dynamic database name over a linked server

There two questions here.
I need to query tables over a linked server. The tables all have the following structure
with a number that is the month of the year.
things_1,things_2,things_3.... things_10,things_11,things_12

the basic sql used is of the form (things_n corresponds to 1-12 as above)
select a,b,c from remoteserver.database.dbo.things_n
where convert(char(12),local_time = convert(char(12),getdate()-1)
and item in('a','b','c','d','e','f','g')
order by local_time

I should add that each of the remote databases are quite large.

question 1
I guess that I could construct a dynamic sql based on the desired month particularly if it was local but is there a better method short of a list of If statements?

question 2
Admittedly I have tried a dynamic sql but can not get it to work

declare @sql nvarchar(4000),
             @tblname nvarchar(9)
select @m = 1
select @tblname = 'things_' + @m
set @sql = 'select a,b,c from remoteserver.database.dbo.[' + @tblname + ']
where convert(char(12),local_time = convert(char(12),getdate()-1)
and item in('a','b','c','d','e','f','g')
order by local_time'

Do you have an opinion or alternative for question 1?
What is the correct format for question 2?

thanks
ASKER CERTIFIED SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alternatively, instead of:
and item in (''a'',''b'',''c'',''d'',''e'',''f'',''g'')

Open in new window

you can just use
and item like ''[a-g]''

Open in new window

Avatar of qube09
qube09

ASKER

The dynamic sql works fine. I am wondering however if there is a better method besides dynamic sql or hardcoding. The report will be working over the course of the year and yes I have already set it up to work on a monthly basis or if need be on a parameter basis. But I have become concerned over the possibilities of sql injection.

So is dynamic sql really the only approach in this situation? And is this version as good as it can get to diminish the risk of sql injection?

thanks!
From what I can see, it will be fine to use this method. SQL Server caches query plans for commonly executed queries.

As for SQL Injection, from I see, the only parameter that we need to get from the outside world here is the value of @m... which is an int. The data type is not prone to SQL Injection in a sense. If someone provides a string, the assignment of the value to @m will crash. So I think we are relatively safe from SQL Injection here.

One mechanism you can use to improve the execution performance and further allay any risks from sql injection would be to instead use sp_executesql instead of exec. sp_executesql allows dynamic parameterised queries. This results in better query plan reuse. see http://www.scarydba.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/ and http://msdn.microsoft.com/en-us/library/ms175170.aspx
Avatar of qube09

ASKER

correct me if I am wrong but the basic dynamic sql above has no parameters so sp_executesql can not be used. Only exec in this case
Oh... I was assuming that the user will specify the month that they want to report on and that this code snippet is part of a stored procedure which accepts the value specified by the user into a stored procedure parameter @m.

Nonetheless, seeing as this is not the case, then EXEC should do just fine. He he... you are at least now armed with the various options and can decide what will work best for you :).
Avatar of qube09

ASKER

ok thanks