Problem when passing parameter to Execute SQL Task

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?\ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?\ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?\ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?

Thank you!

Did you set the "BypassPrepare" to True on the execute task? That gets me every time I use variables.

If you don't set that, it treats the ? as a literal and fails.|||

Hi!

Yes, BypassPrepare is set to true...

Thank you!

|||Setup another variable to hold your full string so that in your SQL you only have the ? placeholder instead of ?/ASPNET.

So, in my test, I created a new variable, set its expression to concatenate the User::ServerName variable and the /ASPNET string. Set the new variable to EvaluateAsExpression and then use it in the parameter mapping of the ExecuteSQL task.

Does that do what you're looking for?|||

Hi!

I tried what you suggest and I still have the error... Then I tried something simpler, CREATE LOGIN ? WITH PASSWORD = 'lalala', and I got the following error:

Executing the query "CREATE LOGIN ? WITH PASSWORD = 'lalala'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And if I change it to CREATE LOGIN test WITH PASSWORD = 'lalala' it works fine, so I guess it's not a connection problem. I guess I'm missing something silly!

Thank you!

|||What type of connection are you using? OLE DB, ADO, ADO.NET, ODBC, etc..

The connection type that you use will dictate how you name the parameter and then use a parameter in your SQL.|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx|||

I'm using an OLE DB connection, and it seems the parameter name is ok... Posting the code would help?

Thank you!

|||I have a variable (User::Test) set as a string with a value of "P0160".

In the Execute SQL Task editor, my sql statement is:
update client set clientdesc = 'testing' where client = ?

Then, in the parameter mapping section, I added a variable and selected User::Test as the variable name. Its data type is VARCHAR. The Parameter Name is simply, 0.

That's it. I run the task and it works correctly.|||It works now. Thank you very much for you help!