PRINT statement in T-SQL

Question

Why does the PRINT statement in T-SQL seem to only sometimes work   What are the constraints on using it   It seems sometimes if a result set is generated  it becomes a null function  I assumed to prevent corrupting the resultset  but could it s output not go out in another result set  such as the row count

User · Answer

For the benefit of anyone else reading this question that really is missing print statements from their output  there actually are cases where the print executes but is not returned to the client   I can t tell you specifically what they are   I can tell you that if put a go statement immediately before and after any print statement  you will see it if it is executed

User · Answer

The Print statement in TSQL is a misunderstood creature  probably because of its name  It actually sends a message to the error message-handling mechanism that then transfers it to the calling application   PRINT is pretty dumb  You can only send 8000 characters  4000 unicode chars   You can send a literal string  a string variable  varchar or char  or a string expression  If you use RAISERROR  then you are limited to a string of just 2 044 characters   However  it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library  RAISERROR can also specify an error number  a severity  and a state code in addition to the text message  and it can also be used to return user-defined messages created using the sp addmessage system stored procedure  You can also force the messages to be logged   Your error-handling routines won   t be any good for receiving messages  despite messages and errors being so similar  The technique varies  of course  according to the actual way you connect to the database  OLBC  OLEDB etc   In order to receive and deal with messages from the SQL Server Database Engine  when you   re using System Data SQLClient  you   ll need to  create a SqlInfoMessageEventHandler delegate  identifying the method that handles the event   to listen for the InfoMessage event on the SqlConnection class  You   ll find that  message-context information such as severity and state are passed as arguments to the callback  because from the system perspective  these messages are just like errors   It is always a good idea to have a way of getting these messages in your application  even if you are just spooling to a file  because there is always going to be a use for them when you are trying to chase a really obscure problem  However  I can   t think I   d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application

User · Answer

Do you have variables that are associated with these print statements been output   if so   I have found that if the variable has no value then the print statement will not be ouput

User · Answer

Do you have variables that are associated with these print statements been output   if so   I have found that if the variable has no value then the print statement will not be ouput

User · Answer

I recently ran into this  and it ended up being because I had a convert statement on a null variable   Since that was causing errors  the entire print statement was rendering as null  and not printing at all   Example - This will fail   declare  myID int null print  First Statement      convert varchar 4    myID    Example - This will print   declare  myID int null print  Second Statement       coalesce Convert varchar 4    myID    myID is null

User · Answer

So  if you have a statement something like the following  you re saying that you get no  print  result   select   from sysobjects PRINT  Just selected   from sysobjects   If you re using SQL Query Analyzer  you ll see that there are two tabs down at the bottom  one of which is  Messages  and that s where the  print  statements will show up  If you re concerned about the timing of seeing the print statements  you may want to try using something like  raiserror   My Print Statement   10 1  with nowait  This will give you the message immediately as the statement is reached  rather than buffering the output  as the Query Analyzer will do under most conditions

User · Answer

So  if you have a statement something like the following  you re saying that you get no  print  result   select   from sysobjects PRINT  Just selected   from sysobjects   If you re using SQL Query Analyzer  you ll see that there are two tabs down at the bottom  one of which is  Messages  and that s where the  print  statements will show up  If you re concerned about the timing of seeing the print statements  you may want to try using something like  raiserror   My Print Statement   10 1  with nowait  This will give you the message immediately as the statement is reached  rather than buffering the output  as the Query Analyzer will do under most conditions

User · Answer

So  if you have a statement something like the following  you re saying that you get no  print  result   select   from sysobjects PRINT  Just selected   from sysobjects   If you re using SQL Query Analyzer  you ll see that there are two tabs down at the bottom  one of which is  Messages  and that s where the  print  statements will show up  If you re concerned about the timing of seeing the print statements  you may want to try using something like  raiserror   My Print Statement   10 1  with nowait  This will give you the message immediately as the statement is reached  rather than buffering the output  as the Query Analyzer will do under most conditions

User · Answer

For the benefit of anyone else reading this question that really is missing print statements from their output  there actually are cases where the print executes but is not returned to the client   I can t tell you specifically what they are   I can tell you that if put a go statement immediately before and after any print statement  you will see it if it is executed

User · Answer

I recently ran into this  and it ended up being because I had a convert statement on a null variable   Since that was causing errors  the entire print statement was rendering as null  and not printing at all   Example - This will fail   declare  myID int null print  First Statement      convert varchar 4    myID    Example - This will print   declare  myID int null print  Second Statement       coalesce Convert varchar 4    myID    myID is null

User · Answer

Query Analyzer buffers messages   The PRINT and RAISERROR statements both use this buffer  but the RAISERROR statement has a WITH NOWAIT option   To print a message immediately use the following   RAISERROR   Your message   0  1  WITH NOWAIT   RAISERROR will only display 400 characters of your message and uses a syntax similar to the C printf function for formatting text   Please note that the use of RAISERROR with the WITH NOWAIT option will flush the message buffer  so all previously buffered information will be output also

User · Answer

The Print statement in TSQL is a misunderstood creature  probably because of its name  It actually sends a message to the error message-handling mechanism that then transfers it to the calling application   PRINT is pretty dumb  You can only send 8000 characters  4000 unicode chars   You can send a literal string  a string variable  varchar or char  or a string expression  If you use RAISERROR  then you are limited to a string of just 2 044 characters   However  it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library  RAISERROR can also specify an error number  a severity  and a state code in addition to the text message  and it can also be used to return user-defined messages created using the sp addmessage system stored procedure  You can also force the messages to be logged   Your error-handling routines won   t be any good for receiving messages  despite messages and errors being so similar  The technique varies  of course  according to the actual way you connect to the database  OLBC  OLEDB etc   In order to receive and deal with messages from the SQL Server Database Engine  when you   re using System Data SQLClient  you   ll need to  create a SqlInfoMessageEventHandler delegate  identifying the method that handles the event   to listen for the InfoMessage event on the SqlConnection class  You   ll find that  message-context information such as severity and state are passed as arguments to the callback  because from the system perspective  these messages are just like errors   It is always a good idea to have a way of getting these messages in your application  even if you are just spooling to a file  because there is always going to be a use for them when you are trying to chase a really obscure problem  However  I can   t think I   d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application

User · Answer

The Print statement in TSQL is a misunderstood creature  probably because of its name  It actually sends a message to the error message-handling mechanism that then transfers it to the calling application   PRINT is pretty dumb  You can only send 8000 characters  4000 unicode chars   You can send a literal string  a string variable  varchar or char  or a string expression  If you use RAISERROR  then you are limited to a string of just 2 044 characters   However  it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library  RAISERROR can also specify an error number  a severity  and a state code in addition to the text message  and it can also be used to return user-defined messages created using the sp addmessage system stored procedure  You can also force the messages to be logged   Your error-handling routines won   t be any good for receiving messages  despite messages and errors being so similar  The technique varies  of course  according to the actual way you connect to the database  OLBC  OLEDB etc   In order to receive and deal with messages from the SQL Server Database Engine  when you   re using System Data SQLClient  you   ll need to  create a SqlInfoMessageEventHandler delegate  identifying the method that handles the event   to listen for the InfoMessage event on the SqlConnection class  You   ll find that  message-context information such as severity and state are passed as arguments to the callback  because from the system perspective  these messages are just like errors   It is always a good idea to have a way of getting these messages in your application  even if you are just spooling to a file  because there is always going to be a use for them when you are trying to chase a really obscure problem  However  I can   t think I   d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application

User · Answer

So  if you have a statement something like the following  you re saying that you get no  print  result   select   from sysobjects PRINT  Just selected   from sysobjects   If you re using SQL Query Analyzer  you ll see that there are two tabs down at the bottom  one of which is  Messages  and that s where the  print  statements will show up  If you re concerned about the timing of seeing the print statements  you may want to try using something like  raiserror   My Print Statement   10 1  with nowait  This will give you the message immediately as the statement is reached  rather than buffering the output  as the Query Analyzer will do under most conditions

User · Answer

Query Analyzer buffers messages   The PRINT and RAISERROR statements both use this buffer  but the RAISERROR statement has a WITH NOWAIT option   To print a message immediately use the following   RAISERROR   Your message   0  1  WITH NOWAIT   RAISERROR will only display 400 characters of your message and uses a syntax similar to the C printf function for formatting text   Please note that the use of RAISERROR with the WITH NOWAIT option will flush the message buffer  so all previously buffered information will be output also

User · Answer

The Print statement in TSQL is a misunderstood creature  probably because of its name  It actually sends a message to the error message-handling mechanism that then transfers it to the calling application   PRINT is pretty dumb  You can only send 8000 characters  4000 unicode chars   You can send a literal string  a string variable  varchar or char  or a string expression  If you use RAISERROR  then you are limited to a string of just 2 044 characters   However  it is much easier to use it to send information to the calling application since it calls a formatting function similar to the old printf in the standard C library  RAISERROR can also specify an error number  a severity  and a state code in addition to the text message  and it can also be used to return user-defined messages created using the sp addmessage system stored procedure  You can also force the messages to be logged   Your error-handling routines won   t be any good for receiving messages  despite messages and errors being so similar  The technique varies  of course  according to the actual way you connect to the database  OLBC  OLEDB etc   In order to receive and deal with messages from the SQL Server Database Engine  when you   re using System Data SQLClient  you   ll need to  create a SqlInfoMessageEventHandler delegate  identifying the method that handles the event   to listen for the InfoMessage event on the SqlConnection class  You   ll find that  message-context information such as severity and state are passed as arguments to the callback  because from the system perspective  these messages are just like errors   It is always a good idea to have a way of getting these messages in your application  even if you are just spooling to a file  because there is always going to be a use for them when you are trying to chase a really obscure problem  However  I can   t think I   d want the end users to ever see them unless you can reserve an informational level that displays stuff in the application

[sql-server] PRINT statement in T-SQL

Examples related to sql-server

Examples related to tsql