?
Solved

Why doesn't this execute?

Posted on 2008-11-19
17
Medium Priority
?
281 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:brettr
  • 10
  • 7
17 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23001476
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23001482
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
 

Author Comment

by:brettr
ID: 23001596
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:brettr
ID: 23001598
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23001661
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23001670
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
 

Author Comment

by:brettr
ID: 23001727
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23001853
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
 

Author Comment

by:brettr
ID: 23003770
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23003897
ummmmm.... now I really feel sheepish....

osql -Slocalhost -dMaster -E  -Q"drop procedure sp_CreateUnitTestUser"
0
 

Author Comment

by:brettr
ID: 23004110
Ok, that's something I was already doing.  Hence, we're back to my original problem:

Incorrect syntax near ')'.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23004364
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23004383
in fact, would have thought setting dbpath s/b:

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

0
 

Author Comment

by:brettr
ID: 23004667
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23005037
OK, except I do not have the script, so, will probably not mean much...
0
 

Author Comment

by:brettr
ID: 23005524
Yes you do.  Please see original post.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 23024463
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question