[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Viewing print output from a Stored Procedure in SQL Server Management Studio

Posted on 2009-12-23
7
Medium Priority
?
658 Views
Last Modified: 2012-08-13
So, I've got Microsoft SQL Server Management Studio 2008.

In the "old" days (earlier version, probably Visual Studio 2005), I had PRINT statements in my stored procedure and I could execute the SP and see all the print statements in an output window.

Now, when I execute a SP, I don't see these print statements, even in the Messages window.

Any suggestions?

Thanks,

Steve
0
Comment
Question by:skbohler
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26112040
Hello skbohler,

its still available with the sql server management studio,




Regards,

aneeshattingal
0
 

Author Comment

by:skbohler
ID: 26112058
How?

Where is the output viewed?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26112070
try running the stored procedure from sql server mangement studio,

you can test  even running the following statement

print 'hello world'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:skbohler
ID: 26112100
I think the problem is with print statements that used to work but don't, like:

PRINT CAST(@ITEMID AS CHAR(5)) + ' | ' + CAST(@ITEMTYPEIDENTIFIER AS CHAR) + ' | ' + @ITEMTEXT + ' | ' + CAST(@THRESHOLDLOW AS CHAR) + ' | ' + CAST(@THRESHOLDHIGH AS CHAR) + ' | '  + CAST(@TMPSCORE AS CHAR) + ' | ' + CAST(@UNADJUSTEDSCORE AS CHAR) + ' | ' +  CAST(@RESULT AS CHAR)

If I just try and use:

PRINT @ITEMID + ' | ' @ITEMTYPEIDENTIFIER

it generates an error. That is why I put in the CAST statements, and those work in the output of Visual Studio.

Again, simple PRINT statements do work (like PRINT @ITEMID).

Any ideas why the more complex print statement wouldn't produce any results in Management Studio?

Steve
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26112125
if any of the values for example '@ITEMTYPEIDENTIFIER '  is null , then the string catenation will result in null,

PRINT @ITEMID + ' | ' + ISNULL(  cast( @ITEMTYPEIDENTIFIER as varchar ) , '' )
0
 

Author Comment

by:skbohler
ID: 26112415
I'll try that and let you know. Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 26125095
Also if they are numeric it wont concatenate either, and it is probably important to see if any of them are NULL

best is open a query window (short cut key CTRL+N) do your declarations, then paste the code :

print isnull(convert(varchar,@ITEMID),'NULL') + '|' + isnull(convert(varchar,@ITEMTYPEIDENTIFIER),'NULL')

then when you run it (shortcut key F5) you should get the tab pages down the bottom - at least one of which will be 'Messages' that is where the print will go (as well as any errors, and other messages, etc).

So as an example try pasting the whole lot (below) and press F5 :

declare @ITEMID int
declare @ITEMTYPEIDENTIFIER int

print isnull(convert(varchar,@ITEMID),'NULL') + '|' + isnull(convert(varchar,@ITEMTYPEIDENTIFIER),'NULL')

set @itemid = 1

print isnull(convert(varchar,@ITEMID),'NULL') + '|' + isnull(convert(varchar,@ITEMTYPEIDENTIFIER),'NULL')

set @itemid = 2
set @itemtypeidentifier = 2

print isnull(convert(varchar,@ITEMID),'NULL') + '|' + isnull(convert(varchar,@ITEMTYPEIDENTIFIER),'NULL')

--results should be three lines in the 'messages' tab (without the leading hyphens of course)
--NULL|NULL
--1|NULL
--2|2
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

865 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