LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » SQL Server Security » How to show more verbose information when running a ddl sql script in "Microsoft SQL Server Management Studio"

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

Options

Previous Topic · Next Topic

jadeite100

Posted: Friday, August 27, 2010 12:19:53 PM

Rank: Newbie

Joined: 8/24/2010
Posts: 4
Points: 12

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?
I can use print statements but is it possible to ask the "Microsoft SQL Server Management Studio" to generate addtonal information when running the ddl sql scripts in "Microsoft SQL Server Management Studio".

Yours,

Frustrated.

Scott Whigham

Posted: Saturday, August 28, 2010 7:24:45 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Many DBCC statements have options for showing more verbose messages - you need to look up the specific DBCC command for that. I don't really know what else to suggest since you haven't given us an idea of what the code is that you are running

jadeite100

Posted: Tuesday, August 31, 2010 9:17:29 AM

Rank: Newbie

Joined: 8/24/2010
Posts: 4
Points: 12

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 period 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.

Scott Whigham

Posted: Wednesday, September 01, 2010 7:08:08 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

jadeite100 wrote:

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.

Well, you aren't really doing anything that takes any amount of serious time, right? You are running basic scripts that each take 1/100th of a second to execute. SQL Server looks at it and says... "Hmmm - I could run this whole thing and, if I just send the PRINT back at the end, I'm finished in 2.347567485 seconds. Or.... I could send the PRINT statements back in a separate network packet each time as he listed them and it would take me 9.34876 seconds and use 10x the resources. He's a smart guy and he's expecting max performance from me - I'll go with option #1."

You can't force SQL Server to send you acknowledgment like that when things go so quickly. SQL Server sends the PRINT return when you have a network packet ready to send. I suppose you could lower your network packet size to something smaller than the default but still you probably won't get what you want.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.