Solved

Stored proc- trouble with string

Posted on 2008-10-07
3
305 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

687 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