Solved

Stored proc- trouble with string

Posted on 2008-10-07
3
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

734 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