Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    11

    Unanswered: How to show more verbose information when running a ddl sql script in "Microsoft SQL

    Hi All:

    I am using Microsoft Sql 2005. I ran my ddl scripts in "Microsoft SQL Server Management Studio" and when the script
    is completed and successfull, all I get is the following messages:

    If DBCC printed error messages, contact your system administrator.

    Is there a way to ask "Microsoft SQL Server Management Studio" to show more information or verbose?

    Yours,

    Frustrated.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can add print statements, if you like.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What DDL were you running, that you got DBCC output?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2009
    Posts
    11

    How to show more verbose information when running a ddl sql script in "Microsoft SQL

    Quote Originally Posted by rdjabarov View Post
    What DDL were you running, that you got DBCC output?
    Hi:

    I am using Microsoft Sql 2005 Server. I ran the ddl using "Microsoft Sql Server Management Studio".
    My ddl I used create Database, login, user, user mapping, tables,NONCLUSTERED index. I placed "PRINT" statements before and after each ddl. When I ran the ddl it print this statement:
    BEGIN Begin Step 1 Create Database

    It wait for a perio of time and when it finish it print the below statements. Is it possible to make the print the statements print out as it run each step instead of printing out all of the statements when it is finish.


    DDL CODE:
    /** Begin Step 1 Create Database **/
    PRINT 'BEGIN Begin Step 1 Create Database '
    USE [master]

    GO
    /****** Object: Database [JOHNSMITH] Script Date: 08/26/2010 15:48:49 ******/
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'JOHNSMITH')
    DROP DATABASE [JOHNSMITH]

    GO
    /****** Object: Login [jadeite100] Script Date: 08/26/2010 15:43:13 ******/
    IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'jadeite100')
    DROP LOGIN [jadeite100]

    GO
    /****** Object: Database [JOHNSMITH] Script Date: 08/24/2010 14:01:33 ******/
    CREATE DATABASE [JOHNSMITH] ON PRIMARY
    ( NAME = N'JOHNSMITH', FILENAME = N'c:\SQL\JOHNSMITH\JOHNSMITH.mdf' , SIZE = 1961856KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
    LOG ON
    ( NAME = N'JOHNSMITH_log', FILENAME = N'c:\SQL\JOHNSMITH\JOHNSMITH_log.ldf' , SIZE = 3460224KB , MAXSIZE = 2048GB , FILEGROWTH = 20%)
    COLLATE SQL_Latin1_General_CP1_CI_AS
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'JOHNSMITH', @new_cmptlevel=90
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [JOHNSMITH].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    GO
    ALTER DATABASE [JOHNSMITH] SET ANSI_NULL_DEFAULT OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ANSI_NULLS OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ANSI_PADDING OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ANSI_WARNINGS OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ARITHABORT OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET AUTO_CLOSE OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET AUTO_CREATE_STATISTICS ON
    GO
    ALTER DATABASE [JOHNSMITH] SET AUTO_SHRINK OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET AUTO_UPDATE_STATISTICS ON
    GO
    ALTER DATABASE [JOHNSMITH] SET CURSOR_CLOSE_ON_COMMIT OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET CURSOR_DEFAULT GLOBAL
    GO
    ALTER DATABASE [JOHNSMITH] SET CONCAT_NULL_YIELDS_NULL OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET NUMERIC_ROUNDABORT OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET QUOTED_IDENTIFIER OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET RECURSIVE_TRIGGERS OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ENABLE_BROKER
    GO
    ALTER DATABASE [JOHNSMITH] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET DATE_CORRELATION_OPTIMIZATION OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET TRUSTWORTHY OFF
    GO
    ALTER DATABASE [JOHNSMITH] SET ALLOW_SNAPSHOT_ISOLATION ON
    GO
    ALTER DATABASE [JOHNSMITH] SET PARAMETERIZATION SIMPLE
    GO
    ALTER DATABASE [JOHNSMITH] SET READ_WRITE
    GO
    ALTER DATABASE [JOHNSMITH] SET RECOVERY FULL
    GO
    ALTER DATABASE [JOHNSMITH] SET MULTI_USER
    GO
    ALTER DATABASE [JOHNSMITH] SET PAGE_VERIFY CHECKSUM
    GO
    ALTER DATABASE [JOHNSMITH] SET DB_CHAINING OFF
    GO
    PRINT 'End Begin Step 1 Create Database '
    /** End Step 1 Create Database **/

    /** Begin Step 2 Create Login **/
    PRINT 'BEGIN Begin Step 2 Create Login '
    /****** Object: Login [jadeite100] Script Date: 08/24/2010 15:31:32 ******/
    /****** Object: Login [jadeite100] Script Date: 08/24/2010 15:31:32 ******/
    CREATE LOGIN [jadeite100] WITH PASSWORD=N'mychau1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'sysadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'securityadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'serveradmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'setupadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'processadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'diskadmin'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'dbcreator'
    GO
    EXEC sys.sp_addsrvrolemember @loginame = N'jadeite100', @rolename = N'bulkadmin'
    GO
    ALTER LOGIN [jadeite100] ENABLE
    GO
    PRINT 'END Begin Step 2 Create Login '
    /** End Step 2 Create Login **/


    PRINT 'Begin Step 3 Create User '
    /** Begin Step 3 Create User **/
    USE [JOHNSMITH]
    GO
    /****** Object: User [jadeite100] Script Date: 08/25/2010 13:26:14 ******/
    GO
    CREATE USER [jadeite100] FOR LOGIN [jadeite100] WITH DEFAULT_SCHEMA=[dbo]
    PRINT 'End Step 3 Create User '
    /** End Step 3 Create User **/

    PRINT 'Begin Step 4 User Mapping '
    /** Begin Step 4 User Mapping **/
    use [JOHNSMITH]
    go
    exec sp_addrolemember N'db_accessadmin', jadeite100
    go
    exec sp_addrolemember N'db_backupoperator', jadeite100
    go
    exec sp_addrolemember N'db_datareader', jadeite100
    go
    exec sp_addrolemember N'db_datawriter', jadeite100
    go
    exec sp_addrolemember N'db_ddladmin', jadeite100
    go
    exec sp_addrolemember N'db_denydatareader', jadeite100
    go
    exec sp_addrolemember N'db_owner', jadeite100

    PRINT 'End Step 4 User Mapping '
    /** End Step 4 User Mapping **/

    PRINT 'Begin Step 5 Create Tables '
    /** Begin Step 5 Create Tables **/
    USE [JOHNSMITH]
    GO
    /****** Object: Table [dbo].[test2] Script Date: 08/19/2010 14:05:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[test2](
    [ID] [char](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TYPE] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TEMPLATE_GROUP] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TEMPLATE_FORM_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XML_FORM_DATA] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LAST_UPDATE_TS] [datetime] NOT NULL,
    [LAST_UPDATE_BY] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_GENERIC_FORM] PRIMARY KEY CLUSTERED
    (
    [ID] ASC,
    [TYPE] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    USE [JOHNSMITH]
    GO
    /****** Object: Table [dbo].[test1] Script Date: 08/19/2010 14:06:43 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[test1](
    [ID] [char](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TYPE] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TEMPLATE_GROUP] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [TEMPLATE_FORM_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [XML_FORM_DATA] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [LAST_UPDATE_TS] [datetime] NOT NULL,
    [LAST_UPDATE_BY] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_FORM_DATA] PRIMARY KEY CLUSTERED
    (
    [ID] ASC,
    [TYPE] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    PRINT 'End Step 5 Create Tables '

    /** End Step 5 Create Tables **/

    /** Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON**/
    PRINT 'Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON '

    ALTER DATABASE [JOHNSMITH] SET ALLOW_SNAPSHOT_ISOLATION ON;
    GO
    PRINT 'End Step 6 set ALLOW_SNAPSHOT_ISOLATION ON '
    /** End Step 6 ALLOW_SNAPSHOT_ISOLATION ON **/

    PRINT 'Begin Step 7 NONCLUSTERED INDEX test2 '
    /** Begin Step 7 NONCLUSTERED INDEX test2**/
    USE [JOHNSMITH]
    GO
    /****** Object: Index [IX_test2] Script Date: 08/23/2010 15:06:51 ******/
    CREATE NONCLUSTERED INDEX [IX_test2] ON [dbo].[test2]
    (
    [LAST_UPDATE_TS] ASC
    )
    INCLUDE ( [ID],
    [TYPE]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];

    PRINT 'End Step 7 NONCLUSTERED INDEX test2 '
    /** End Step 7 NONCLUSTERED INDEX test2 **/

    PRINT 'Begin Step 8 NONCLUSTERED INDEX test1 '
    /** Begin Step 8 NONCLUSTERED INDEX test1 **/
    USE [JOHNSMITH]
    GO
    /****** Object: Index [IX_test1] Script Date: 08/23/2010 15:11:02 ******/
    CREATE NONCLUSTERED INDEX [IX_test1] ON [dbo].[test1]
    (
    [LAST_UPDATE_TS] ASC
    )
    INCLUDE ( [ID],
    [TYPE]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];

    PRINT 'End Step 8 NONCLUSTERED INDEX test1 '
    /** End Step 8 NONCLUSTERED INDEX test1**/

    OUTPUT OF DDL:
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    End Begin Step 1 Create Database
    BEGIN Begin Step 2 Create Login
    END Begin Step 2 Create Login
    Begin Step 3 Create User
    End Step 3 Create User
    Begin Step 4 User Mapping
    End Step 4 User Mapping
    Begin Step 5 Create Tables
    End Step 5 Create Tables
    Begin Step 6 set ALLOW_SNAPSHOT_ISOLATION ON
    End Step 6 set ALLOW_SNAPSHOT_ISOLATION ON
    Begin Step 7 NONCLUSTERED INDEX test2
    End Step 7 NONCLUSTERED INDEX test2
    Begin Step 8 NONCLUSTERED INDEX test1
    End Step 8 NONCLUSTERED INDEX test1

    Yours,

    Frustrated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That's a pretty big database. The print statement will wait until the creation of the database is done. There is no way to do that asynchronously.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    raiserror ('Message text', 0, 1) with nowait
    There is no way to do that asynchronously.
    Actually, PRINT statement is buffered and processed semi-asynchronously, while RAISERROR ... WITH NOWAIT will output after each operation.
    Last edited by rdjabarov; 08-27-10 at 17:24. Reason: comment to the command
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •