• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Stored proc- trouble with string

I am getting the following error with the stored proc below.
I am  trying to modify the proc to use sp_msforeachdb, so that it can be run on multiple servers, but am having trouble parsing the '@cmd ; string.


********************************************************************************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE procedure [dbo].[usp_get_dbstats]
      @p_FragmentedLimit decimal = 30.0
as
      declare @err    int,
            @rc                int,
            @cmd        varchar(8000),
            @cmd2       varchar(8000),
            @ServerName sysname
      
      set nocount on

      -----------------------------------------------------------------------
      -- Truncate the central table with the fragmentation information     --
      -----------------------------------------------------------------------

      truncate table dbstats



      -----------------------------------------------------------------------
      -- Loop on Server list and find fragmented tables                    --
      -----------------------------------------------------------------------

      declare SrvCursB cursor for
      select ServerName
       from ServerList
       where IsActive = 1

      open SrvCursB

      fetch SrvCursB into @ServerName

      while @@FETCH_STATUS = 0
      BEGIN
            -- Create a table in tempdb that will be populated at the remote
             -- server and later be accessed from the central database
             -- (Command passed to osql should be in one line, no <CR>):
            select @cmd = 'if exists (select 1 from tempdb.dbo.sysobjects ' +
                            'where type = ''U'' AND NAME = ''dbstats'') ' +
                            'drop table tempdb.dbo.dbstats;' +
                           'CREATE TABLE tempdb.dbo.dbstats (' +
                                                   'ServerName sysname,' +
                                       'DatabaseName sysname,' +
                                       'ID int IDENTITY (1, 1),' +
                                       'RECORD_TYPE int,' +
                                       'DBNAME char (50),' +
                                       'DATA_SIZE,' +
                                       'DATA_USED,' +
                                       'LOG_SIZE,' +
                                       'LOG_USED,' +
                                       'STAT_DATE,)'
                                       
              -- My central server is SQL 2000, you can use SQLCMD instead of
              -- osql:
            select @cmd2 = 'osql -E -S"' + @ServerName + '" -Q "' +
                            @cmd + '"'
            exec master..xp_cmdshell @cmd2

            select @err = @@error
            IF @err <> 0
            begin
                  select 'ERROR Creating the table in tempdb for SERVER - '+
                           @ServerName
                  return -1
            end

            -- Query the fragmenation data into remote table
             -- (In the remote server, for each database, except for the
             -- system databases, loop on the tables, insert fragmentation
             -- information into a temporary table; insert fragmentation
             -- information including server name and database name into
             -- the remote table created in tempdb. This table will be later
             -- read from the central database).
             -- You can print the @cmd variable to view its content:
select @cmd =
            'exec [' + @ServerName + '].master.dbo.sp_msforeachdb ' +
            '''use [?]; print ''''?'''';
             if ''''?'''' in (''''tempdb'''',''''msdb'''',''''master'''',
                              ''''model'''',''''Northwind5'''',''''pubs'''')
                        return
            create table #t (cmd varchar(1000));
            insert into #t select
                     ''''dbcc sqlperf (logspace)'''' ;
            declare @cmd varchar(1000)
            declare curs_tmp cursor for select cmd from #t
            open curs_tmp
            fetch curs_tmp into @cmd
            while @@fetch_status = 0
            begin
            
                   select dbname,data_size,data_used,log_size,log_used,stat_date
                   into #dbstats from tempdb.dbo.dbstats
                  insert into #dbstats exec (@cmd)
                  insert into tempdb.dbo.dbstats
                          select ''''' +  @ServerName + ''''',
                                 db_name(),* from #dbstats '''''
                                                       
                   drop table #dbstats
                  fetch curs_tmp into @cmd
            end
            deallocate curs_tmp
            drop table #t'''

            exec (@cmd)

            select @err = @@error
            IF @err <> 0
            begin
                  select 'ERROR Collecting fragmentation information for ' +
                           'SERVER - ' + @ServerName
                  return -1
            end

            -- Insert the fragmentation details to the central (local) table
            select @cmd = 'insert into DBStats select * from ['+
                    @ServerName + '].tempdb.dbo.dbstats'
            exec (@cmd)

            select @err = @@error
            IF @err <> 0
            begin
                  select 'ERROR selecting data from remote table for ' +
                           'SERVER - ' + @ServerName
                  return -1
            end


            -- Drop the remote table in tempdb
             -- (Command passed to osql should be in one line, no <CR>):
            select @cmd =
                  'if exists (select 1 from tempdb.dbo.sysobjects ' +
                              'where type = ''U'' AND NAME = ''dbstats'') ' +
                                 'drop table tempdb.dbo.dbstats'
            select @cmd2 = 'osql -E -S"' + @ServerName + '" -Q "' + @cmd +
                            '"'
            exec master..xp_cmdshell @cmd2

            select @err = @@error
            IF @err <> 0
            begin
                  select 'ERROR dropping remote table for SERVER - ' +
                           @ServerName
                  return -1
            end
      
            fetch SrvCursB into @ServerName
      END
      deallocate SrvCursB

********************************************************************************************************************


Error when I try to create the proc:



Msg 170, Level 15, State 1, Procedure usp_get_dbstats, Line 103
Line 103: Incorrect syntax near ''

            exec (@cmd)

            select @err = @@error
            IF @err <> 0
            begin
                  select '.
Msg 170, Level 15, State 1, Procedure usp_get_dbstats, Line 116
Line 116: Incorrect syntax near '
            exec (@cmd)

            select @err = @@error
            IF @err <> 0
            begin
                  select '.
Msg 170, Level 15, State 1, Procedure usp_get_dbstats, Line 131
Line 131: Incorrect syntax near ' +
                              '.
Msg 170, Level 15, State 1, Procedure usp_get_dbstats, Line 133
Line 133: Incorrect syntax near '
            select @cmd2 = '.
Msg 105, Level 15, State 1, Procedure usp_get_dbstats, Line 140
Unclosed quotation mark before the character string ' +
                           @ServerName
                  return -1
            end
      
            fetch SrvCursB into @ServerName
      END
      deallocate SrvCursB

'.

0
sbagireddi
Asked:
sbagireddi
  • 2
1 Solution
 
QPRCommented:
what is this doing?

drop table #t'''
0
 
sbagireddiAuthor Commented:
Dropping the table that was created.

 insert into #t select
                     ''''dbcc sqlperf (logspace)'''' ;
0
 
QPRCommented:
but with 3 single quotes after the drop table statement?
0

Featured Post

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now