June 11, 2003 at 6:35 am
Hi!
Is it possible to read an environment variable (system or user) with TSQL?
(like temp or windir)
Thanx
June 11, 2003 at 8:33 am
You can check out the return values of the extended procedures in the master database, or, if you're up to it, write your own extended procedure. There's information in Books Online under System Procedures - General Extended Procedures.
HTH,
Jay
June 11, 2003 at 9:14 am
I just need to create a windows system environment variable sql_backup_drive="X:\" for each SQL server and use it to determine the location of backup folder for each server in a multiserver job. I planned to use "subst" command for it, but it works really bad, if MSSQLSERVER service uses a domain account.
Of all extended procedures, only xp_cmdshell seems to be able to fetch a windows system environment variable into stored procedure variable, but still I did not found how to do this...
June 11, 2003 at 11:22 am
You can use the (env) command to list all system and user environment
variables. The findstr command will filter out just those vriables
with the word SQL_BACKUP and you redirect the output to a file.
In sql you can use xp_cmdshell like this
Exec Master..xp_cmdshell 'env | FindStr /I "SQL_BACKUP" >> c:\temp_backuplocation.log'
Your file output should look like:
SQL_BACKUP_DRIVE_SRVR1=c:\backup_srvr1
SQL_BACKUP_DRIVE_SRVR2=c:\backup_srvr2
You can insert this in a table for later use by your script
Create Table #Temp_Table (Server VARCHAR(20),
Backup_Location VARCHAR(20))
Bulk Insert #Temp_Table From 'c:\temp_backuplocation.log'
With(CodePage = 'RAW',
FieldTerminator = '=',
RowTerminator = '\n')
Note: Once you add the new environment variables, you need to restart SQL Server
MW
Edited by - mworku on 06/11/2003 8:53:16 PM
MW
June 11, 2003 at 4:14 pm
I assume you really are looking for a way to read a registry value with TSQL. There is an undocumented extended stored procedure that does that. (Undocumented means unsupported...)
EXECUTE xp_regread [@rootkey=]'rootkey',
[@key=]'key'
[, [@value_name=]'value_name']
[, [@value=]@value OUTPUT]
To read into variable @test-2 from the value 'TestValue' from the key
'SOFTWARE\Test' from the 'HKEY_LOCAL_MACHINE', run:
DECLARE @test-2 varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Test',
@value_name='TestValue',
@value=@test OUTPUT
SELECT @test-2
More of these are at: http://www.databasejournal.com/features/mssql/article.php/1441251
June 11, 2003 at 5:35 pm
quote:
I just need to create a windows system environment variable sql_backup_drive="X:\" for each SQL server and use it to determine the location of backup folder for each server in a multiserver job. I planned to use "subst" command for it, but it works really bad, if MSSQLSERVER service uses a domain account.Of all extended procedures, only xp_cmdshell seems to be able to fetch a windows system environment variable into stored procedure variable, but still I did not found how to do this...
Easy, Really...
-- Create a table to hold the output
create table #CMDResults (CMDOutput varchar(500) NULL)
-- Capture the output in the table
INSERT #CMDResults
EXECUTE master.dbo.xp_cmdshell 'ECHO %sql_backup_drive%'
June 12, 2003 at 2:39 am
********
-- Capture the output in the table
INSERT #CMDResults
EXECUTE master.dbo.xp_cmdshell 'ECHO %sql_backup_drive%'
*********
This just returned a row in the temp table with the contents '%sql_backup_drive%'
replacing ECHO %sql_backup_drive% with SET sql_backup_drive should do the trick
On my system it returned a row 'Environment variable sql_backup_drive not defined' but that's what I expected! Using SET with defined variable names pulled back the correct values into the temporary table.
Tony Bater
Tony
June 12, 2003 at 3:38 am
Can I read such an environment variable not into a table, but into a varible, like
declare @back_drive
set @back_drive = EXECUTE master.dbo.xp_cmdshell 'ECHO %sql_backup_drive%' ?
June 12, 2003 at 5:06 am
No not directly. Just take and set the value from the temp table (make sure you account for NULLS as it will return 1 extra row of NULL. But something like this
SELECT @back_drive = CMDOutput FROM #CMDResults WHERE CMDOutput IS NOT NULL
June 12, 2003 at 11:31 am
Of course you have to have the variable defined. The assumption is that on each server you would have a common environment variable defined that points to the backup location. Then, using the logic I submited you would have the value in the table. Of course you would also have to read the table and test to see if it contained %sql_backup_drive%. If so, then you would know that the server had not been set up as expected and an appropriate error would need to be reported.
quote:
********-- Capture the output in the table
INSERT #CMDResults
EXECUTE master.dbo.xp_cmdshell 'ECHO %sql_backup_drive%'
*********
This just returned a row in the temp table with the contents '%sql_backup_drive%'
replacing ECHO %sql_backup_drive% with SET sql_backup_drive should do the trick
On my system it returned a row 'Environment variable sql_backup_drive not defined' but that's what I expected! Using SET with defined variable names pulled back the correct values into the temporary table.
Tony Bater
June 12, 2003 at 8:38 pm
Create Table #Temp_Table (BackUp_Location VARCHAR(40))
Declare @Error INT
Insert Into #Temp_Table
Exec @Error = Master..xp_cmdshell 'env | FindStr /I "SQL_BACKUP"' (Change SQL_BACKUP
to your env. var name)
IF(@Error <> 0)
BEGIN
PRINT 'Error reading env. variable'
PRINT 'Terminating Process !'
RETURN
END
Select *
From #Temp_Table
Where BackUp_Location IS NOT NULL
MW
MW
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply