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!

Problem when failing over to second Node

I recently moved a cluster out of a one domain to the
next. Worked through all the issues and I am still having
a problem I can not seem to resolve.
When I try to fail over to the second node, I see that
SQL Network Name(virtualnamegiven)in cluster
administrator fails. When I look in event viewr I get
multiple errors all containing this error, Cluster
resource 'SQL Network Name(virtualnamegiven)' failed.
Has anyone ever seen this or know how to troubleshoot
this problem. Any help would be appreciated. Please email
me at tim.johnson@.instinet.com.
Thanks
When you bring a "name" resource on line we are trying to register the DSN
name with Active Directory. YOu need to find put what is preventing the
Name from coming online on the network.
Try PING by the "name" while it is off-line. You should NOT get a
response.
Also, wee if there is a HOST entry with the name, but with the old IP
address.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.