Solved

Stored proc- trouble with string

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now