May 21, 2003 at 3:58 am
Hi all,
does anyone know how to use the messages output in query analyzer ?
for example.....
set statistics time on
select * from master..sysobjects
set statistics time off
we have the query reluts in the grins and the statistics messages in the messages
how can i use this messages ?
can i import, redirect, work with this kind of output ?
please note that example is not my goal....
another example
declare @d varchar(1000)
set @d = (select sum(uid) from sysobjects)
print @d
so the question is...how to use the messages output in query analyzer ?
May 21, 2003 at 9:54 am
To state the obvious, you can copy the output stored in the Messages pane, and paste it into some other app. As far as redirrection goes, I'd suggest checking out the following topic in Books Online:
messages-SQL Server, applications
Not much, I know, but at least it's a pointer in a direction.
RD Francis
R David Francis
May 21, 2003 at 11:25 am
In addition to copy and paste you can change the 'Execute Mode' to save the 'Results to File'.
Everett Wilson
ewilson10@yahoo.com
May 21, 2003 at 1:40 pm
thanks a lot
but the problem is "automatic use" of the messages...
not using query analyzer but with a store procedure, a scheduled job, etc.etc.
The only way that i've found is to run the query or the procedure via isql with the /o (result output file) option, than import the piece of text that i wnat with another job :-/
nothing more simple ?
May 21, 2003 at 1:50 pm
Sorry I'm still not seeing the issue. Why can't you catch the value in a variable, or write it to a temporay table (local or global), or write the value to a table?
In other words, why can't you determine the value of @d in the job where the value is needed?
Everett Wilson
ewilson10@yahoo.com
May 21, 2003 at 3:07 pm
Sorry i'm not a great prog. 🙂
Do you say that , for example, i can assigne to a local variable the text message (not the query result) of
set statistics time on
select * from master..sysobjects
set statistics time off
?
how ?
May 21, 2003 at 3:46 pm
I have a bad feeling that I'm still not seeing what you want.
My quick answer is yes
declare @TestString varchar(250)
set @v-2 = 'set statistics time on ' +
'select * from master..sysobjects '+
'set statistics time off'
\\when time to run the command then:
exec (@TestString)
This works great for building dynamic T-SQL solutions since @v-2 can be built bit by bit.
I'm worried, though, because this is only local to the script running it. It won't be available to a different step in a job.
I hope this helps, and I'll be happy to keep trying to understand if I'm still off.
Everett Wilson
ewilson10@yahoo.com
May 22, 2003 at 1:47 am
ok...thanks a lot....but i try to explain you the real issue
running this
declare @TestString varchar(250)
set @TestString = 'set statistics time on ' +
'select * from master..sysobjects '+
'set statistics time off '
-- when time to run the command then:
exec (@TestString)
i've
1905 as query result
and
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
in the messages of query analyzer gui.
How i can assige this text
(SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.)
to a variable and/or use it directly ? ..if possible...
Thanks in advance
May 22, 2003 at 9:01 am
Are you actually trying to do this in Query Analyzer, or to get Query Analyzer "message" output in another app?
Although someone said you can get the Query Analyzer to output data from the Messages tab to a file, I don't see that option. Maybe someone can clarify that....
If you're trying to set this up for some other app, check the Books Online topic I mentioned earlier; that tells how other apps get the message output, which would let you deal with it.
Realize that, behind the scenes, Query Analyzer is an "other app"; it's (I assume) getting the message output the same way any other app would, and simply displays it in that tab.
RD Francis
R David Francis
May 22, 2003 at 10:31 am
Ah. Cool question, is there a simpler way than: saving the result and then importing it. I hope someone has an idea.
P.S. the save message tab information to a file comment was simply in reference to the 'Results to File' command ('Query' - 'Results to File', or Ctrl+Shift+F, or 'Execute Mode' - 'Results to File').
Everett Wilson
ewilson10@yahoo.com
May 22, 2003 at 1:18 pm
If you are trying to get the "meassages" output from a SQL statement or procedure executed through ADO (version 2.5 or later), you can use the ADODB.Recordset.NextRecordset() to get to the next recordset. However, it depends on what provider you are using for your connection that determines what exactly the "next recordset" is. If you are using the OLE DB Provider for SQL Server, the next recordset will indeed be the "messages" part of the return. This will usually be the "3 records affected" string, but can be warnings, or DBCC messages, for instance.
To be honest, I would highly NOT RECOMMEND using this approach because it is dependent upon the provider to determine what exactly is the next recordset, so your code may break if trying to be provider-agnostic. The only reason I even know about this is because I had to debug a really nasty error resulting from the OLE DB provider interpreting a message result as an actual recordset.
If you just want to retrieve the count of records affected, the best methods are to use the ADODB.Recordset.RecordCount function, or pass in (by reference) an argument into the ADODB.Command.Execute function which allows a referenced parameter to be filled with the number of recorsd affected.
For more information on this behavior, see the following page in MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth02_16.asp
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply