September 10, 2008 at 1:19 am
I was wondering if it is possible to make the following T-sql more flexible:
SELECT * FROM DBNAME.SCHEMA.TABLE
[/CODE]
Instead of hardcoding the dbname and schema I would like to have this values as variables to be defined only once before starting a batch of sequential scripts:
The following obviously does not work:
DECLARE @CONNECTION VARCHAR(50)
SELECT @CONNECTION = 'SOMEDATABASE'
SELECT * FROM @CONNECTION.dbo.SOMETABLE
[/CODE]
Any thoughts?
September 10, 2008 at 1:31 am
You can do this with dynamic SQL like:
DECLARE @query VARCHAR(MAX)
DECLARE @CONNECTION VARCHAR(50)
SELECT @CONNECTION = 'SOMEDATABASE'
SET @query = 'SELECT * FROM ' + @CONNECTION + '.dbo.SOMETABLE'
EXEC( @query)
another alternative is to use synonyms. On the other hand, if possible, try to avoid both of the above approaches.
Regards,
Andras
September 10, 2008 at 12:41 pm
If you are running the batch of sequential scripts from batch file using SQLCMD try doing this...
SELECT * FROM $(DatabaseName).dbo.tablename
Then you simply set an environment variable in your command file for Database name and make the SQLCMD call.
To test this in your script put the following at the top of the script...
:SETVAR DatabaseName Foo
Don't forget to comment it out when running in batch mode though! 🙂
So your batch file might look like the following...
@echo off
if '%1' == '' goto usage
if '%2' == '' goto usage
SET DatabaseName=%2%
sqlcmd -S %1 -d %2 -E -b -i "ScriptFileName.sql"
if %ERRORLEVEL% NEQ 0 goto errors
sqlcmd -S %1 -d %2 -E -b -i "ScriptFileName2.sql"
if %ERRORLEVEL% NEQ 0 goto errors
goto finish
REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Database
echo Server: the name of the target SQL Server
echo Database: the name of the target database
echo.
echo Example: MyScript.cmd MainServer MainDatabase
echo.
echo.
goto done
REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
Gary Johnson
Sr Database Engineer
September 10, 2008 at 1:02 pm
you could also use sp_executesql.
like this ;
declare @sql nvarchar(2000),
@vars nvarchar(500)
set @sql = N'SELECT * FROM @DBNAME.@SCHEMA.@TABLE'
set @vars = N'@DBNAME nvarchar(255),@Schema nvarchar(255),'+
'@Table nvarchar(255)'
exec sp_executesql @sql,@vars,N'@DBNAME='MyDB',@Schema='dbo',@Table='MyTable'
blom0344 (9/10/2008)
I was wondering if it is possible to make the following T-sql more flexible:
SELECT * FROM DBNAME.SCHEMA.TABLE
[/CODE]
Instead of hardcoding the dbname and schema I would like to have this values as variables to be defined only once before starting a batch of sequential scripts:
The following obviously does not work:
DECLARE @CONNECTION VARCHAR(50)
SELECT @CONNECTION = 'SOMEDATABASE'
SELECT * FROM @CONNECTION.dbo.SOMETABLE
[/CODE]
Any thoughts?
September 10, 2008 at 1:11 pm
Thanks Gary,
With batch I wasn't really thinking of running a command file, but really more of a solution on how to perform a similar action on a collection of tables.
I stumbled upon almost the exact solution I needed here:
[thank you, keith rull]
Now, the beauty of this approach is that it also (almost) works for loading staging tables from one database to another.
The only snag is that the last table that is processed is done twice , resulting in a PK violation.
This is probably due to the @@FETCH_STATUS value and the way it performs in the WHILE loop.
Any way, thanks for your solution!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply