I'm trying to create a database with the parent directory being a environment variable,
something like this:
Code Snippet
CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = '%PARENTDIRECTORY%\mydatabase.mdf',
the problem is I don't know to get an environment variable in transact SQL...
I know that in C# we can get it with %PARENTDIRECTORY%...
Thanx in advance
The following batch may help you...
Code Snippet
Create Table #Result
(
Data varchar(8000)
);
Insert Into #Result
Exec master..xp_cmdshell 'echo %TEMP%';
Declare @.ParentDirectory as nvarchar(256);
Select Top 1 @.ParentDirectory = Data From #result;
Select @.ParentDirectory = @.ParentDirectory + '\mydatabase.mdf'
Exec ('CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = ''' + @.ParentDirectory + ''')')
Drop table #result
|||Thanx Manivannan the code above does the job, but doesn't the xp_cmdshell work only for XP users?|||
sqlclr's udf can imprement below,
This assembly's permission need to 'EXTERNAL_ACCESS'.
Usage:
Code Snippet
select dbo.GetEnvironmentVariable('temp');C# Source file:
Code Snippet
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString GetEnvironmentVariable(string variable)
{
string value = Environment.GetEnvironmentVariable(variable);
return new SqlString(value);
}
};
No comments:
Post a Comment