Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gravi98
Starting Member
5 Posts |
 Posted - 2002-06-24 : 20:18:33
|
| We need to catch the db name in SQL script and use it for setting an env.variable.The code is given below.declare @dbname varchar(50), @sqlstmt varchar(255) set @dbname= db_name()set @sqlstmt='SET DBNAME='+@dbnameexec master.dbo.xp_cmdshell @sqlstmtThis doesn't work. Any idea? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-24 : 20:30:43
|
| xp_cmdshell runs its own command shell, and cannot affect the existing Windows environment settings.You can output the dbname to a file and have your application read the file instead. You may even be able to write a batch file that can set the environment variable to the file contents, but it might be too difficult to be practical. |
 |
|
|
gravi98
Starting Member
5 Posts |
Posted - 2002-06-25 : 12:48:08
|
| Actually the problem is slightly more complex as given by the squence below1. A batch file (file1.bat) calls a generic SQL script. The DB on which the script has to act, is passed from this batch file as a parameter (DB1).2. The SQL script in turn calls another batch file (file2.bat) to do some more job on the same DB13. After the second batch is completed, the SQL script continues to do more tasks on DB1.The DB names are not hardcoded anywhere except when the original SQL script is called from the batch file.Because of this scenario, I/O of a third text file is difficult. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-25 : 14:02:13
|
| It sounds as though you're trying to implement some sort of primitive workflow logic here. If that's the case you'd be much better off using DTS. The infrastructure you would get for free ( scheduling, logging, notifications, etc ) is far and away superior to batch file commands.Jonathan Boott, MCDBA |
|
|
|
|
|
|
passing db name to env.variable