environment variables

Well, having only one disk partition, and only one directory, and running only one application probably ROCKS too, at least for that one application :)
But if you're unfortunate enough to be required to use multiple applications, you might be saddened by certain aspects of environment variables, such as them living in one globally competitive namespace, or them being not covered by the NT security model (AFAIK).
But I grant you, that they solve the portability problem with package configurations, so I was happy to use them nonetheless.
I'd use environment variables (or any other hack in all likelihood), if I could find a solution for the lack of reusability in SSIS -- that is driving us to move as much code as possible outside of SSIS for maintainability. :(

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

|||

Cim Ryan wrote:

We're always looking for product feedback; what are the aspects of your packages that you wish you could re-use? (Also, it sounds like you're replying to another thread... Was this post misplaced?)

Cim/Perry,
I'm jumping in on your thread here, sorry about that.

Here's a list of things that could, IMO, be made reusable:
-Data-Flows (the most obvious one)
-Tasks (e.g. an Execute SQL task that calls a auditing sproc)
-A sequence container that carries out a single unit of work
-A For/ForEach Loop (including all tasks within it)
-A configured component
-A group of configured components (http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx)
The important point to make is that they shouldn't just be made reusable in the same package, they should be reusable in ANY package. In that sense, each one of them could be a deployable object. Currently the only deployable object is a package - why should that be the case? Why not deploy (for example) a task, or even a component, and then use that in any package?

-Jamie

|||To add to Jamie's excellent list at the lower level,
expressions
I say this because copying and pasting transformations from one Derived Column task to another is tedious, and I tend to believe that copy&paste leads to poor maintainability and scalability.

|||Also, the Aggregate defaults most columns to Group By, it usually misses one out of a long list, and for some reason it always misses the ErrorCode and ErrorNumber, when they are added at the bottom of the output column list from a Lookup Error, so when you finish checking all the columns to group by, you track the Validation error back and find that the Aggregate left those columns unspecified -- which means it is in an invalid state -- so you have to manually set those to Group By.
Actually, sounds more like a bug than an RFE to me, but I don't care what list it goes on if it gets fixed :)
sql

Environment Variables

Hi everyone...

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);
}
};

Enumerate Drives and Directories for .NET application

In the past I used the SQLDMO to get drive and directory information for a
target SQL Server. I do not want to use COM components in my .NET
application to accomplish this. What is the ".NET" way of doing this with
the assumption the SQL Server could be on a separate system than the one the
..NET application is running.
As you cannot be sure you have OS permissions to do this:
Run a profiler trace while doing this from your current app, or while EM is doing this, and pick up
the SQL procedures that are executed. Warning: Some of these are undocumented, use at your own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruce Parker" <bparkerhsd@.nospam.nospam> wrote in message
news:D96611AE-0CB4-41E7-92E4-A37F6216110D@.microsoft.com...
> In the past I used the SQLDMO to get drive and directory information for a
> target SQL Server. I do not want to use COM components in my .NET
> application to accomplish this. What is the ".NET" way of doing this with
> the assumption the SQL Server could be on a separate system than the one the
> .NET application is running.

Enumerate Drives and Directories for .NET application

In the past I used the SQLDMO to get drive and directory information for a
target SQL Server. I do not want to use COM components in my .NET
application to accomplish this. What is the ".NET" way of doing this with
the assumption the SQL Server could be on a separate system than the one the
.NET application is running.As you cannot be sure you have OS permissions to do this:
Run a profiler trace while doing this from your current app, or while EM is
doing this, and pick up
the SQL procedures that are executed. Warning: Some of these are undocumente
d, use at your own risk.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bruce Parker" <bparkerhsd@.nospam.nospam> wrote in message
news:D96611AE-0CB4-41E7-92E4-A37F6216110D@.microsoft.com...
> In the past I used the SQLDMO to get drive and directory information for a
> target SQL Server. I do not want to use COM components in my .NET
> application to accomplish this. What is the ".NET" way of doing this with
> the assumption the SQL Server could be on a separate system than the one t
he
> .NET application is running.

EnterpriseManager/SQL Query Analysier (Authentication)

We have a sql 2005 server mirror set that we need to connect to.

I am in one active directory domain (eg DomainA) and the sql 2005 mirror is in another (eg DomainB).

We are not allowed (by the sql 2005 server provider) to put a trust between the two active directory domains and if we use a sql server login then if the mirror fails over then we will lose access since the master does not get mirrored.

Is there a way of logining in to the sql server 2005 mirror in DomainB with out logging my whole machine into DomainB or is there a way to mirror the sql users between the two sql 2005 mirror servers.

Any help would be grreatly apperiated

Hi,

Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de