SQL script and complex passwords

[steveosmith]

I'm having a problem running a sql script when my SQL Express 2005 database has a complex password. I'm trying to run a stored procedure that exists in the database. Here's the SQL script I'm runniing:

EXEC dbo.spInsertNewDatabaseInitialValues

I return the result to a variable. If the database has the sa password set to pass, the variable shows SUCCESS. But if I change the sa password to @qmispass1 (and change the password in the IA script), the variable shows ERROR.

Any thoughts ?

TIA,
Steve.

[Gizm0]

The @ mark is used for variables.. you have to put the password between quotes ( ' ) so sql will treat them as a string and not as variable..
eg '@mypass' and not @mypass..

[steveosmith]

Yes! That did it. Thanks for the help!

[steveosmith]

A related issue. I need to change the existing SQL Server Express sa password from 'pass'. I've tried calling the stored procedure sp_password from my SQL script with the proper parameters, but even though it returns SUCCESS, the password doesn't change. Here's the SQL script to change the password:

EXEC master.dbo.sp_password @old = 'pass', @new = 'foo', @loginame ='sa'
Go

When I run it from within an OSQL prompt it runs fine. I thought perhaps the @ was causing a problem in the SQL script, so I even tried assigning that line to a variable and referencing my variable in the SQL script. Same thing - it returns SUCCESS but doesn't actually change the password.

Any thoughts as to what I'm doing wrong?

[Gizm0]

You propably don't have permissions to do it when you connect to it through your script..
Check the roles of the user you are connecting to the database..
If it runs from the osql tool, it should run from anywhere..

[steveosmith]

I'm not too sure it's a permissions issue. In the script I set the user to sa - same as I do in OSQL. However, I'm going to consider the issue moot. Fortunately, there's always more than one way to accomplish a given task. I created a small executable that changes the password, and I call that from within my script.

Thanks for the help!

Steve.

[MichaelNesmith]

Would taking EXEC out of the first line help - could you try that real quick?

[Gizm0]

It's the same Michael.. Either putting EXEC in front or not, it's absolutely the same..SQL Server executes the sp no matter what!