Why doesn't this execute?

I'm running this command line:

C:\src\Data\Create Scripts>CreateDB.bat localhost test

and get this output, which isn't what I want.  Notice this part: Incorrect syntax near ')'.  I'm only trying to create this user.  I have similar scripts running like this without issue.  I've stripped everything away to make it very simple to trouble shoot.   Can someone try this on their machine and let me know what might be wrong?  Here's the output:

"drop procedure sp_CreateUnitTestUser"

C:\src\Data\Create Scripts>osql -S localhost -d
 Master -E -Q "drop procedure sp_CreateUnitTestUser"
Msg 3701, Level 11, State 5, Server brettu, Line 1
Cannot drop the procedure 'sp_CreateUnitTestUser', because it does not exist or
you do not have permission.
"ref sp_CreateUnitTestUser.sql"

C:\src\Data\Create Scripts>osql -S localhost -d
 Master -E -i sp_CreateUnitTestUser.sql
1> 2> Msg 102, Level 15, State 1, Server brettu, Line 1
Incorrect syntax near ')'.
1> "exec sp_CreateUnitTestUser @DB=test"

C:\src\Data\Create Scripts>osql -S localhost -d
 Master -E -Q "exec sp_CreateUnitTestUser @DB=test"
Msg 2812, Level 16, State 62, Server brettu, Line 1
Could not find stored procedure 'sp_CreateUnitTestUser'.

C:\src\Data\Create Scripts>GOTO END

C:\src\Data\Create Scripts>
--sp_CreateUnitTestUser--
CREATE LOGIN [myunittester] WITH PASSWORD='mypass', DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 
--createdb.bat--
@Echo off
IF "%1" == "" GOTO FAIL
IF "%2" == "" GOTO FAIL 
set server=%1
set db=%2
set dbpath=%3
 
IF (%dbpath%) == ("") Set %dbpath%="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
 
@Echo on
osql -S %server% -d Master -E -Q "drop procedure sp_CreateUnitTestUser"
osql -S %server% -d Master -E -i sp_CreateUnitTestUser.sql	
osql -S %server% -d Master -E -Q "exec sp_CreateUnitTestUser @DB=%db%"
GOTO END
:Fail
:END

Open in new window

brettrAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
OK so that also worked... it created a login.

cannot do the third and final step because it is trying to exec the sp that was deleted in the first job, and the second job does not create a sp - it creates the login.

Where to from here ? Did you try with the change to dbpath ?
0
 
Mark WillsTopic AdvisorCommented:
OK, seems like it is not in the default database. and you cannot mention the database prefix as a full qualifier when using things like drop procedure.

so, are you very sure that it does exist and you are logging into master ?
0
 
Mark WillsTopic AdvisorCommented:
might be worthwhile running the batch to do a select db_name()

also not using a specific person, so using windows authentication - does that user have the right permissions to drop stuff out of master, or even access master  ?

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
brettrAuthor Commented:
What do you mean by this statement, "might be worthwhile running the batch to do a select db_name()"?

myunittester isn't granted any permissions so it isn't executing anything.  Maybe didn't understand the last part of what you said.  I'm still not sure why this one procedure is a problem when others in the batch using the same syntax work fine.
0
 
brettrAuthor Commented:
I can log onto sql server and run the CREATE LOGON script listed above without issue.  Why is it a problem when running from the batch file?
0
 
Mark WillsTopic AdvisorCommented:
It wither doesn't know who you are, or, the proc is not in the default database where you have logged into (ie requested master in the osql) or, don't have permission. two things to try - one being the simple select db_name(), second, try adding the -Uuser -Ppassword for maybe sa and isolate which bit is not working...
0
 
Mark WillsTopic AdvisorCommented:
what i meant was :

osql -S %server% -d Master -E -Q "select db_name()"

osql -S %server% -d Master -E -Q "drop procedure sp_CreateUnitTestUser" -U sa -P sapassword

0
 
brettrAuthor Commented:
Here's the output after replacing the batch with the statements you provided:

C:\src\Data\Create Scripts>CreateDB.bat localhost test

CC:\src\Data\Create Scripts>osql -S localhost -d
 Master -E -Q "select db_name()"

 -------------------------------------------------------------------------------
        -------------------------------------------------
 master
(1 row affected)

C:\src\Data\MindBodyData\Create Scripts>osql -S localhost -d
 Master -E -Q "drop procedure sp_CreateUnitTestUser" -U sa -P sapassword
Error: Conflicting switches : -U and -E
C:\src\Data\MindBodyData\Create Scripts>GOTO END
C:\src\Data\MindBodyData\Create Scripts>
0
 
Mark WillsTopic AdvisorCommented:
my bad... -E means use a trusted connection instead of requesting a password... sorry about that - the hazards of copy and paste... kill the -E
0
 
brettrAuthor Commented:
I tried that and get a login failed for sa.  I removed the U and P parameters and went with -E.  I got this:

C:\src\Data\Create Scripts>CreateDB.bat localhost test

C:\src\Data\Create Scripts>osql -S localhost -d
 Master -E -Q "select db_name()"
 -------------------------------------------------------------------------------
        -------------------------------------------------
 master
(1 row affected)

C:\src\Data\Create Scripts>osql -S localhost -d
 Master -E "drop procedure sp_CreateUnitTestUser"
1>
1>
2>
3>
4>
5>
6>


That last part is a result of me hitting the <enter> key.  I never did get a c prompt back.  I don't think we're really making any progress.
0
 
Mark WillsTopic AdvisorCommented:
ummmmm.... now I really feel sheepish....

osql -Slocalhost -dMaster -E  -Q"drop procedure sp_CreateUnitTestUser"
0
 
brettrAuthor Commented:
Ok, that's something I was already doing.  Hence, we're back to my original problem:

Incorrect syntax near ')'.
0
 
Mark WillsTopic AdvisorCommented:
Here is the batch file I ran - notice the setting of dbpath:
IF "%1" == "" GOTO FAIL
IF "%2" == "" GOTO FAIL 
set server=%1
set db=%2
set dbpath=%3
 
IF (%dbpath%) == ("") Set dbpath="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
 
pause
 
osql -S %server% -d Master -E -Q "drop procedure sp_CreateUnitTestUser"
 
GOTO END
:Fail
:END

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
in fact, would have thought setting dbpath s/b:

IF "%dbpath%" == "" Set dbpath="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

0
 
brettrAuthor Commented:
If you go a little further and add this line:
osql -S %server% -d Master -E -i sp_CreateUnitTestUser.sql      

you'll get the error in question:
Incorrect syntax near ')'

which is really an upside down capital U instead of close parentheses.
0
 
Mark WillsTopic AdvisorCommented:
OK, except I do not have the script, so, will probably not mean much...
0
 
brettrAuthor Commented:
Yes you do.  Please see original post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.