help needed with sp_executesql

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.

help me i have by mistake drop a table in SQL 2005 how recovery

hi ,by mistake we have execute a script (drop table and create table ) on
the good data how recevory all the data?please help me it's very importantrichard wrote:
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important
Hi Richard
Assuming you have a backup schedule, you'll have to restore your latest
FULL backup with the NORECOVERY option. Then apply the log backups also
with the NORECOVERY option. You can then backup you current log file (of
course unless it already has been backed up after the mistake) and then
apply this log file with RECOVERY and STOPAT option. The STOPAT time
should then be just before the "accident" happended.
If you haven't got a valid backup, you're left with getting one of the
log reader tools and then get the data from there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Richard
In addition to Steen's reply... if you don't have an up-to-date backup, but
you do have an old one then that could be restored as a different database
and you could re-create your table and transfer the data from the old
database using (say) the INSERT...SELECT statement. If your data has been
updated since the backup then you will have lost those changes. You may also
want to read "Designing a Backup and Restore Strategy" in Books online.
John
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||... and here's some thoughts on the subject:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
> thanks dear steen,
we are trying the step one: your latest
> FULL backup with the NORECOVERY option
it seems to take very long time (database' name restoring...) does it
normally?|||Hi Steen,
Further to richard's post please note that in this case after analysing the
transaction logs, surprisingly enough we cannot see the transaction entries
for "drop statements", so please suggest how can we recover the data from the
data file (.mdf).
- Joy
"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||richard wrote:
> "Steen Persson (DK)" wrote:
>
>>richard wrote:
>>hi ,by mistake we have execute a script (drop table and create table ) on
>>the good data how recevory all the data?please help me it's very important
>>Hi Richard
>>Assuming you have a backup schedule, you'll have to restore your latest
>>FULL backup with the NORECOVERY option. Then apply the log backups also
>>with the NORECOVERY option. You can then backup you current log file (of
>>course unless it already has been backed up after the mistake) and then
>>apply this log file with RECOVERY and STOPAT option. The STOPAT time
>>should then be just before the "accident" happended.
>>If you haven't got a valid backup, you're left with getting one of the
>>log reader tools and then get the data from there.
>>
>>--
>>Regards
>>Steen Schlüter Persson
>>Database Administrator / System Administrator
>>thanks dear steen,
> we are trying the step one: your latest
>>FULL backup with the NORECOVERY option
> it seems to take very long time (database' name restoring...) does it
> normally?
Hi Richard
When you restore with the NORECOVERY option, the database will not be
operational until you restore the following logfiles where the last one
will have to be with the RECOVERY option. This option tells the database
that no more data will be restored and the database will be operational.
You can read up on RESTORE options in Books On Line - here're also some
explanation to each option.
Regards
Steen|||Joy wrote:
> Hi Steen,
> Further to richard's post please note that in this case after analysing the
> transaction logs, surprisingly enough we cannot see the transaction entries
> for "drop statements", so please suggest how can we recover the data from the
> data file (.mdf).
> - Joy
Hi Joy
I'm not very good at the different log reader tools, but I'm sure you
should be able to see the delete transactions. How much of it that is
logged though depends on how you've deleted the data (i.e. what sql code
did you run?).
When data is deleted, there are no way to recover it from the mdf file.
You can try John's suggestion which was to restore your most recent
backup to a different database and then take the data from there. That
will of course require that the data hasn't changed since then.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

help me i have by mistake drop a table in SQL 2005 how recovery

hi ,by mistake we have execute a script (drop table and create table ) on
the good data how recevory all the data?please help me it's very importantrichard wrote:
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important
Hi Richard
Assuming you have a backup schedule, you'll have to restore your latest
FULL backup with the NORECOVERY option. Then apply the log backups also
with the NORECOVERY option. You can then backup you current log file (of
course unless it already has been backed up after the mistake) and then
apply this log file with RECOVERY and STOPAT option. The STOPAT time
should then be just before the "accident" happended.
If you haven't got a valid backup, you're left with getting one of the
log reader tools and then get the data from there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Richard
In addition to Steen's reply... if you don't have an up-to-date backup, but
you do have an old one then that could be restored as a different database
and you could re-create your table and transfer the data from the old
database using (say) the INSERT...SELECT statement. If your data has been
updated since the backup then you will have lost those changes. You may also
want to read "Designing a Backup and Restore Strategy" in Books online.
John
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||... and here's some thoughts on the subject:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important