Professional Web Applications Themes

Error 170 (T-SQL syntax) in procedure - Microsoft SQL / MS SQL Server

Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "_M_" <com> wrote in message news:phx.gbl... This procedure produces an Error 170 (T-SQL syntax error) at or near "backup". Anyone know why ??? CREATE PROCEDURE dbo.Backup AS declare fname varchar(256) select fname = 'e:\SQL backup on demand\' + db_name() + '_DB_' + convert(varchar(100), getdate(),112) + datename(hh,getdate()) + datename(mi,getdate()) --select fname backup database DB1 to disk = fname GO...

  1. #1

    Default Re: Error 170 (T-SQL syntax) in procedure

    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO



    Tom Guest

  2. #2

    Default Re: Error 170 (T-SQL syntax) in procedure

    I cut and pasted your code on my PC and it works fine.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    It is now as follows:

    CREATE PROC
    dbo.bu
    AS
    declare fname varchar(128)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    --select fname
    backup database StarBldr to disk = fname
    GO

    Still get an error 170 on the "backup database...." line....

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO



    Tom Guest

  3. #3

    Default Re: Error 170 (T-SQL syntax) in procedure

    Now that's strange... I get even with copying and pasting the code below... Error 170: Line 9: at or near 'backup'.

    Is there a setting on the server that needs to be checked / changed ????

    This a basic / default SQL 2000 server install.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I cut and pasted your code on my PC and it works fine.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    It is now as follows:

    CREATE PROC
    dbo.bu
    AS
    declare fname varchar(128)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    --select fname
    backup database StarBldr to disk = fname
    GO

    Still get an error 170 on the "backup database...." line....

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO


    _M_ Guest

  4. #4

    Default Re: Error 170 (T-SQL syntax) in procedure

    Suggestion:
    Run this in QA:
    select 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    and post the result.
    "_M_" <com> wrote in message news:phx.gbl...
    Now that's strange... I get even with copying and pasting the code below... Error 170: Line 9: at or near 'backup'.

    Is there a setting on the server that needs to be checked / changed ????

    This a basic / default SQL 2000 server install.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I cut and pasted your code on my PC and it works fine.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    It is now as follows:

    CREATE PROC
    dbo.bu
    AS
    declare fname varchar(128)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    --select fname
    backup database StarBldr to disk = fname
    GO

    Still get an error 170 on the "backup database...." line....

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO


    raydan Guest

  5. #5

    Default Re: Error 170 (T-SQL syntax) in procedure

    Result is correct:

    e:\SQL backup on demand\StarBldr_DB_20030814958.dat

    "raydan" <nospamcom> wrote in message news:phx.gbl...
    Suggestion:
    Run this in QA:
    select 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    and post the result.
    "_M_" <com> wrote in message news:phx.gbl...
    Now that's strange... I get even with copying and pasting the code below... Error 170: Line 9: at or near 'backup'.

    Is there a setting on the server that needs to be checked / changed ????

    This a basic / default SQL 2000 server install.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I cut and pasted your code on my PC and it works fine.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    It is now as follows:

    CREATE PROC
    dbo.bu
    AS
    declare fname varchar(128)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    --select fname
    backup database StarBldr to disk = fname
    GO

    Still get an error 170 on the "backup database...." line....

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO


    _M_ Guest

  6. #6

    Default Re: Error 170 (T-SQL syntax) in procedure

    It returns 60, 65, 70 or 80

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Run sp_dbcmptlevel inside your database. If it is < 70, you will have to make it 70 or 80.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Now that's strange... I get even with copying and pasting the code below... Error 170: Line 9: at or near 'backup'.

    Is there a setting on the server that needs to be checked / changed ????

    This a basic / default SQL 2000 server install.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I cut and pasted your code on my PC and it works fine.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    It is now as follows:

    CREATE PROC
    dbo.bu
    AS
    declare fname varchar(128)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'
    --select fname
    backup database StarBldr to disk = fname
    GO

    Still get an error 170 on the "backup database...." line....

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Backup is a reserved word. Change your proc name to something like dbo.AdHocBackup.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    This procedure produces an Error 170 (T-SQL syntax error) at or near
    "backup". Anyone know why ???

    CREATE PROCEDURE dbo.Backup AS
    declare fname varchar(256)
    select fname = 'e:\SQL backup on demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate())
    --select fname
    backup database DB1 to disk = fname
    GO


    _M_ Guest

  7. #7

    Default Re: Error 170 (T-SQL syntax) in procedure

    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 


    _M_ Guest

  8. #8

    Default Re: Error 170 (T-SQL syntax) in procedure

    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 



    Tom Guest

  9. #9

    Default Re: Error 170 (T-SQL syntax) in procedure

    Ok... how do i change that ??? I could only find the sp_dbcmptlevel in the master database... it's not in any other one

    USE [StarBldr]
    DECLARE RC int
    DECLARE dbname nvarchar(128)
    DECLARE new_cmptlevel tinyint
    EXEC RC = [dbo].[sp_dbcmptlevel] DEFAULT, new_cmptlevel OUTPUT
    DECLARE PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: master.dbo.sp_dbcmptlevel'
    SELECT PrnLine = ' Return Code = ' + CONVERT(nvarchar, RC)
    PRINT PrnLine
    PRINT ' Output Parameter(s): '
    SELECT PrnLine = ' new_cmptlevel = ' + isnull( CONVERT(nvarchar, new_cmptlevel), '<NULL>' )
    PRINT PrnLine

    Valid values of database compatibility level are 60, 65, 70, or 80.
    Stored Procedure: master.dbo.sp_dbcmptlevel
    Return Code = 0
    Output Parameter(s):
    new_cmptlevel = <NULL>

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 


    _M_ Guest

  10. #10

    Default Re: Error 170 (T-SQL syntax) in procedure

    Try:

    sp_dbcmptlevel 'StarBldr', 80
    go


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... how do i change that ??? I could only find the sp_dbcmptlevel in the master database... it's not in any other one

    USE [StarBldr]
    DECLARE RC int
    DECLARE dbname nvarchar(128)
    DECLARE new_cmptlevel tinyint
    EXEC RC = [dbo].[sp_dbcmptlevel] DEFAULT, new_cmptlevel OUTPUT
    DECLARE PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: master.dbo.sp_dbcmptlevel'
    SELECT PrnLine = ' Return Code = ' + CONVERT(nvarchar, RC)
    PRINT PrnLine
    PRINT ' Output Parameter(s): '
    SELECT PrnLine = ' new_cmptlevel = ' + isnull( CONVERT(nvarchar, new_cmptlevel), '<NULL>' )
    PRINT PrnLine

    Valid values of database compatibility level are 60, 65, 70, or 80.
    Stored Procedure: master.dbo.sp_dbcmptlevel
    Return Code = 0
    Output Parameter(s):
    new_cmptlevel = <NULL>

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 



    Tom Guest

  11. #11

    Default Re: Error 170 (T-SQL syntax) in procedure

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Ran the sp_dbcmptlevel again... I still don't see where it list the level at...
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Try:

    sp_dbcmptlevel 'StarBldr', 80
    go


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... how do i change that ??? I could only find the sp_dbcmptlevel in the master database... it's not in any other one

    USE [StarBldr]
    DECLARE RC int
    DECLARE dbname nvarchar(128)
    DECLARE new_cmptlevel tinyint
    EXEC RC = [dbo].[sp_dbcmptlevel] DEFAULT, new_cmptlevel OUTPUT
    DECLARE PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: master.dbo.sp_dbcmptlevel'
    SELECT PrnLine = ' Return Code = ' + CONVERT(nvarchar, RC)
    PRINT PrnLine
    PRINT ' Output Parameter(s): '
    SELECT PrnLine = ' new_cmptlevel = ' + isnull( CONVERT(nvarchar, new_cmptlevel), '<NULL>' )
    PRINT PrnLine

    Valid values of database compatibility level are 60, 65, 70, or 80.
    Stored Procedure: master.dbo.sp_dbcmptlevel
    Return Code = 0
    Output Parameter(s):
    new_cmptlevel = <NULL>

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 


    _M_ Guest

  12. #12

    Default Re: Error 170 (T-SQL syntax) in procedure

    So, can you execute your proc?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:ujX$phx.gbl...
    sp_dbcmptlevel 'StarBldr'

    returns: The current compatibility level is 80.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Try:

    sp_dbcmptlevel 'StarBldr', 80
    go


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... how do i change that ??? I could only find the sp_dbcmptlevel in the master database... it's not in any other one

    USE [StarBldr]
    DECLARE RC int
    DECLARE dbname nvarchar(128)
    DECLARE new_cmptlevel tinyint
    EXEC RC = [dbo].[sp_dbcmptlevel] DEFAULT, new_cmptlevel OUTPUT
    DECLARE PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: master.dbo.sp_dbcmptlevel'
    SELECT PrnLine = ' Return Code = ' + CONVERT(nvarchar, RC)
    PRINT PrnLine
    PRINT ' Output Parameter(s): '
    SELECT PrnLine = ' new_cmptlevel = ' + isnull( CONVERT(nvarchar, new_cmptlevel), '<NULL>' )
    PRINT PrnLine

    Valid values of database compatibility level are 60, 65, 70, or 80.
    Stored Procedure: master.dbo.sp_dbcmptlevel
    Return Code = 0
    Output Parameter(s):
    new_cmptlevel = <NULL>

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 



    Tom Guest

  13. #13

    Default Re: Error 170 (T-SQL syntax) in procedure

    Yes it now works....

    Thanks for all your help...

    Is there a way to write a restore procedure similiar to the backup ????

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    So, can you execute your proc?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:ujX$phx.gbl...
    sp_dbcmptlevel 'StarBldr'

    returns: The current compatibility level is 80.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Try:

    sp_dbcmptlevel 'StarBldr', 80
    go


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... how do i change that ??? I could only find the sp_dbcmptlevel in the master database... it's not in any other one

    USE [StarBldr]
    DECLARE RC int
    DECLARE dbname nvarchar(128)
    DECLARE new_cmptlevel tinyint
    EXEC RC = [dbo].[sp_dbcmptlevel] DEFAULT, new_cmptlevel OUTPUT
    DECLARE PrnLine nvarchar(4000)
    PRINT 'Stored Procedure: master.dbo.sp_dbcmptlevel'
    SELECT PrnLine = ' Return Code = ' + CONVERT(nvarchar, RC)
    PRINT PrnLine
    PRINT ' Output Parameter(s): '
    SELECT PrnLine = ' new_cmptlevel = ' + isnull( CONVERT(nvarchar, new_cmptlevel), '<NULL>' )
    PRINT PrnLine

    Valid values of database compatibility level are 60, 65, 70, or 80.
    Stored Procedure: master.dbo.sp_dbcmptlevel
    Return Code = 0
    Output Parameter(s):
    new_cmptlevel = <NULL>

    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    If sp_dbcmptlevel for the StarBldr is < 70, BACKUP DATABASE will fail.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "_M_" <com> wrote in message news:phx.gbl...
    Ok... this is the procedure so far:

    CREATE PROCEDURE dbo.AcctBackup AS
    declare fname varchar(256)
    select fname = 'backup database StarBldr to disk =
    ''e:\SQL_backup_on_demand\' +
    db_name() + '_DB_' + convert(varchar(100), getdate(),112)
    + datename(hh,getdate()) + datename(mi,getdate()) + '.dat'''
    --select fname
    SELECT fname
    exec (fname)
    GO

    This compiles. The SELECT fname returns the correct statement including
    the correct quotations for the file name. Now when run inside an OSQL line
    it produces the following:

    osql -Sw2ksvr -E -Q"exec StarBldr.dbo.AcctBackup"

    This command returns again the syntax error around the word database (Msg
    156, Level 15, State 1, Server w2ksvr Line 1... incorrect syntax near the
    keyword database). I am assuming it has to do with the quotes that are
    suppose to go around the backup command.

    "_M_" <com> wrote in message
    news:phx.gbl... 


    _M_ Guest

Similar Threads

  1. Stored Procedure Syntax error.
    By Ryan in forum MySQL
    Replies: 2
    Last Post: December 21st, 09:19 PM
  2. Replies: 6
    Last Post: September 2nd, 01:22 PM
  3. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  4. ASP and stored procedure problem (syntax error ?)
    By Steven Scaife in forum ASP Database
    Replies: 4
    Last Post: August 13th, 12:11 PM
  5. stored procedure syntax help
    By anjelina in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 10:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139