'DBCC' is not recognized as an internal or external command

  • Hi,

    Can anyone tell me what am i missing if i tried to test my script on the command prompt and gives me this error

    'DBCC' is not recognized as an internal or external command.

    Below is my script

    DBCC Shrinkfile('ABC_CAS_log',EMPTYFILE )

    DBCC Shrinkfile('ABCLogging_log',EMPTYFILE )

    any help is greatly appreciated

    thanks,

    aguzman

  • How are you executing this? DBCC is a command that SQL Server recognizes but the OS does not. You need to execute the DBCCs like you would execute any other SQL, through a connection to the server.


    And then again, I might be wrong ...
    David Webb

  • i am a sql newbie. i will appreciate if you can provide more details in your reply.

    thanks david.

  • You execute DBCC commands exactly as you execute any database query (select, update, insert, delete, etc). The error suggests you're trying to run them from the OS command prompt, that won't work, they're SQL commands, not OS commands

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail,

    again sorry for the ignorance. HOw should my script execute that query? Given the script.. how should my make it run in DOS command as we need this script to run in DOS command for test before we can place it to BackupAssist software.

    DBCC Shrinkfile('CCGA_CAS_log',EMPTYFILE )

    DBCC Shrinkfile('Logging_log',EMPTYFILE )

    please give us a hint.

    I appreciate your comment.

  • Look up SQLCMD, a utility that can run as a DOS command and read your script as input, connect to the SQL Server and execute your script.

    Here's a link:

    http://msdn.microsoft.com/en-us/library/ms162773.aspx


    And then again, I might be wrong ...
    David Webb

  • Hi David,

    thanks for the feedback.

    i do have a question for you, you said that I need to execute the DBCC command like i am executing sql through a connection to the server,

    can you give me an example on how i can achieved this?

    again thank you..

  • Look up SQLCMD. It's a command-line (runs from OS command console) SQL Server querying tool (runs commands against whatever SQL Server it connects to)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have tried the link you provided earlier and installed the package but can get the sqlcmd running.

    anyway thanks for your help.

  • SQL Server is a service that runs like any other service under the OS. In order to issue commands or SQL statements to the server, you have to connect to the server and send those commands or statements to the server to execute them on your behalf. You can get a connection to the running SQL Server service in several ways. You can connect with SQL Server Management Studio, a graphical user interface for administration and development. You can use several command line utilities, like OSQL or SQLCMD that can be executed as OS level commands but which can take in script files with SQL statements and execute then in 'batch' mode. So in your case (based on what I've seen so far), you'd create a text file with the DBCC commands in it (let's call it dbcc.sql) and then issue something like the following at the OS command prompt (or in a batch file):

    SQLCMD -Sservername -Uuserid -Ppassword -idbcc.sql -ooutput.txt

    This is basic syntax and there are several options ( you might use -E and remove the -U and -P if you are using windows authentication) that you need to choose. The above syntax will read in the dbcc.sql and execute those at the server and write out the results to the output.txt file for later examination.


    And then again, I might be wrong ...
    David Webb

  • thanks David,

    correct me if i'm wrong. I have a batch file dbcc.bat (will .bat ok? as prerequisite to call in our third party software) with the DBCC script mentioned above will the following addition to the script. the dbcc.bat script now should look like this:

    ------------------------------------

    -SQLservername -E -idbcc.sql -ooutput.txt

    DBCC Shrinkfile('CCGA_CAS_log',EMPTYFILE )

    DBCC Shrinkfile('Logging_log',EMPTYFILE )

    -----------------------------------

    i have tried this one, but i got the error message:

    HResult 0x2, Level 16, State 1

    Named Pipes Provider: Could not open a connection to SQL Server [2].

    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failu

    re may be caused by the fact that under the default settings SQL Server does not allow remote connections..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    ****** SHOULD I JUST ENABLE SQL SERVER TO ALLOW REMOTE CONNECTIONS????

    Thanks

  • No, the .bat file should only have the SQLCMD line in it. The actual DBCC commands should be in another file indicated by the -i filename on the SQLCMD command line.


    And then again, I might be wrong ...
    David Webb

  • Yes, the server should be set to allow remote connections. The -E will work if the id of the login you are running is set up as a windows authenticated login inside the server. You need the -U and -P if the id you are using to run the .bat file is set up as a SQL authenticated id inside the server. If neither is true, you need to have your DBA set up the id as one or the other before you can actually make the connection.


    And then again, I might be wrong ...
    David Webb

  • Also since you have not specified a server, that will attempt to connect to the default instance on the machine you are running it on. If that's not the correct server, you need to use -S to specify the correct server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    i received this error message.

    sqlcmd :'dbcc.bat': Invalid filename

    thanks

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply