addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Adding expression in Data Flow Task

Hi

I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.

Thanks in advance,

Vipul

Try using the Derived Column tool.|||

Hi TGnat

I am looking for some apis which will help me to write a code. I am not sure abt Derive Column Tool as there are some of the things which are not supported in it and i am looking to enhance the functionality for my use.

Thanks

Vipul

|||

Can you give an example of what you are trying to do.

When I first started out I thought I would need a script task or a custom control for everything... Now I find that the stock components can meet most of my needs!

|||

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

|||

Vipul123 wrote:

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

"Stock components" simply means the stuff that is already in the toolbox.

If you want to build something that does the same as a LOOKUP and a DERIVED COLUMN, why not just use the LOOKUP and the DERIVED COLUMN?

-Jamie

|||

Also... Your lookup can return multiple columns, one or more for the look up match, and then you can select other columns to be added to your data flow. Follow this up with a derived column that implements your logic and you may have the functionality you need.

|||

Jamie:

Thanks for the reply. I have few more queries. In the OLEDB Command, there is a tab called "Column Mapping". In here we can map the input columns to the output columns.

I am writing this code in the function :

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

ComponentMetaData.Name = "Test";

ComponentMetaData.Description = "Test";

ComponentMetaData.ContactInfo = "Test";

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "Input";

input.Description = "Input Desc";

input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSRuntimeConnection90 rtc = ComponentMetaData.RuntimeConnectionCollection.New();

rtc.Name = "Source";

rtc.Description = "Source Desc";

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "Output";

output.Description = "Output desc";

output.ExternalMetadataColumnCollection.IsUsed = true;

}

I am not able to connect(link) the input to the output ports. I am able to add new output columns in "Input and Output Properties" tab. The output ports which i am adding in this tab are not visible in "Column Mapping" tab. Please let me know if i am missing something in the component properties which will enable the linking of the input to output ports.

Thanks,

Vipul

|||

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

|||

Vipul123 wrote:

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

So you want to be able to reuse functionality? Currently the only way to do this is to write your own component (very difficult) or good old copy-and-paste (very easy). I know which I would choose.

Incidentally, there is functionality probably coming in Vnext that would allow you to do exactly what you want to do (i.e. build reusable collections of components) without writing code.

-Jamie