Hello,
I wish to execute a simple dynamic sql script using sp_executesql, in which
I am trying to input the name of the database dynamically.
Here is the script -
DECLARE @.databaseName NVARCHAR(100)
set @.databaseName='testdb'
DECLARE @.strSQL NVARCHAR(400)
SET @.strSQL = 'select * from @.dbName..testtable'
EXEC sp_executesql @.strSQL, N'@.dbName nvarchar(100)', @.databaseName
--
The query fails with error -
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Please let me know what am I doing wrong here.
If I write the same query as a static one, it works fine. But, this query is
a nested part of a complex logic which is difficult to modify.
Thanks for your help.Why do you want to reference a database name dynamically? That's a
reasonable thing to do in a DBA admin script but with the right design
there shouldn't be any good reason to do it in a production
application.
The following article explains both how to do this and also some of the
reasons why it isn't a good idea:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||Very nice article David.
But it does not tell me how to acccomplish the task using sp_executesql with
parameters.
Ok, I will explain my requirement -
Our production environment is shared by many products, ours being one of
them.
And, each of our customers has their own database on the server.
I am trying to write an sql script to be run in all/only our customers'
databases - to avoid the task of manually running the script in 100
databases, say.
In this script, I want to avoid concatenating the database name variable
again and again into the sql string.
So, for simplification, I would like to write 1 consolidated sql string and
use parameters to replace the occurances of the database name string.
Is it possible and how?
Thanks.|||Try with
DECLARE @.databaseName NVARCHAR(100)
set @.databaseName='testdb'
DECLARE @.strSQL NVARCHAR(400)
SET @.strSQL = 'select * from ' + @.databaseName + '..testtable'
EXEC sp_executesql @.strSQL, N'@.databaseName nvarchar(100)', @.databaseName
Ana
"Nitin" wrote:
> Very nice article David.
> But it does not tell me how to acccomplish the task using sp_executesql wi
th
> parameters.
> Ok, I will explain my requirement -
> Our production environment is shared by many products, ours being one of
> them.
> And, each of our customers has their own database on the server.
> I am trying to write an sql script to be run in all/only our customers'
> databases - to avoid the task of manually running the script in 100
> databases, say.
> In this script, I want to avoid concatenating the database name variable
> again and again into the sql string.
> So, for simplification, I would like to write 1 consolidated sql string an
d
> use parameters to replace the occurances of the database name string.
> Is it possible and how?
> Thanks.
>
>|||Your solution is correct, Ana.
But as I mentioned -
Concatenating the database name everytime is quite tedious and results in
messy sql.
Thanks.
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:3EC16CBE-CA22-4EC6-B5B7-147F0CC90392@.microsoft.com...
> Try with
> DECLARE @.databaseName NVARCHAR(100)
> set @.databaseName='testdb'
> DECLARE @.strSQL NVARCHAR(400)
> SET @.strSQL = 'select * from ' + @.databaseName + '..testtable'
> EXEC sp_executesql @.strSQL, N'@.databaseName nvarchar(100)', @.databaseName
> Ana
> "Nitin" wrote:
>|||Your architecture inevitably imposes some costs and complexities when
it comes to DB management. For good reasons, static TSQL doesn't
provide any method for parameterizing database names. Some options are:
Create SPs in each database. You could generate these automatically
from the INFORMATION SCHEMA tables, then just call the SP once for each
DB.
Use a host language that will let you parameterize the database name
much more easily - in a connection string for example.
Use DTS - again, it's easier to parameterize a database name in DTS
than in TSQL.
Execute a script via OSQL using a batch file. You can call the script
for each DB by generating a batch file containing each DB name.
David Portas
SQL Server MVP
--|||I have the same kind of setup that you explained. What we did was write a c
#
app that will run something against all the databases on the server that are
for that application. There are also checkboxes for all the databases on th
e
server so we can pick and choose what databases to run the query against.
