Solved

Stored proc- trouble with string

Posted on 2008-10-07
3
301 Views
Last Modified: 2010-05-18
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
Comment
Question by:sbagireddi
  • 2
3 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 22665027
what is this doing?

drop table #t'''
0
 
LVL 8

Author Comment

by:sbagireddi
ID: 22665135
Dropping the table that was created.

 insert into #t select
                     ''''dbcc sqlperf (logspace)'''' ;
0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 22665184
but with 3 single quotes after the drop table statement?
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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