Via SQL Server How To Retrieve Text Message That Is In Tab Messages
May 29, 2008
Hi,
I execute a query via queries editor embedded in sql srver studio.then i have query result in results tab and a text message in tab messages. Does it exist a T-SQL sys global variable as of i could to retrieve the text message that is displayed in tab messages?
To be more clear. My problem is :
1) I launched the stataments behing via sql server studio :
Use AutClust
GO
SET NOCOUNT ON;
Set statistics IO ON
GO
select * FROM table_1;
Go
Set statistics IO OFF
GO
2) I have then obtained in tab message the result displayed in text format in tab messages :
Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages.
This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.
I do need a solution for this problem.
Please bring me your help.
Hi, To be more clear. My problem is : 1) I launched the stataments behing via sql server studio : Use AutClust GO SET NOCOUNT ON; Set statistics IO ON GO select * FROM table_1; Go Set statistics IO OFF GO
2) I have then obtained in tab message the result displayed in text format in tab messages : Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages. This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.
I tried tu use @@rowcount it deos not work because this system variable return number of row affacted by last statement.
Does it exist another system variable that could contain the text which is in tab messages?
I do need a solution for this problem. Please bring me your help.
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?
Here is the code I use to create the service broker objects that are being used€¦
RUN THIS ON THE SENDING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE GO
CREATE CONTRACT [MyContract] ( MyMessage SENT BY INITIATOR, MyResponse SENT BY TARGET) GO
CREATE QUEUE [MyInitiatorQueue] with status = ON CREATE QUEUE [MyTargetQueue] with status = ON GO
CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue] GO
CREATE ROUTE [RouteToODS] WITH SERVICE_NAME = N'MyTargetService', BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E', ADDRESS = N'TCP://CONSULT01:4022' GO
CREATE ENDPOINT DWHEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS, ENCRYPTION = DISABLED) GO
RUN THIS ON THE RECEIVING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE GO
CREATE CONTRACT [MyContract] ( MyMessage SENT BY INITIATOR, MyResponse SENT BY TARGET) GO
CREATE QUEUE [MyInitiatorQueue] with status = ON CREATE QUEUE [MyTargetQueue] with status = ON GO
CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract]) GO
CREATE ROUTE [RouteToDWH] WITH SERVICE_NAME = N'MyInitiatorService', BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5', ADDRESS = N'TCP://DWH:4022' GO
CREATE ENDPOINT ODSEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS, ENCRYPTION = DISABLED) GO
SEND A MESSAGE USING THE FOLLOWING:
Declare @ConversationHandle uniqueidentifier
Begin Transaction Begin Dialog @ConversationHandle From Service [MyInitiatorService] To Service 'MyTargetService' On Contract [MyContract] With Encryption = Off, Lifetime = 600;
Send on Conversation @ConversationHandle Message Type [MyMessage] (N'This is a my message')
End Conversation @ConversationHandle Commit Select GET_TRANSMISSION_STATUS(@ConversationHandle)
When I run sp_start_job I get the message 'Job... started successfully!'. As I execute the procesure many times my logg fills up with these messages - is there a way to suppress these text messages?
Hi everybody,i try to send messages longer than 7990 characters from a text fieldin SSQL2000. Unfortunatly the messages get cut off after 7990character.I did everything which is described in BOL (see below). It does notsolve the problem. Upgraded to newest Outlook Client and tried to sendas an attachment also. No success though.Does anybody have a hint before i contact Microsoft.RegardsYorn Ziesche[color=blue]>E. Send messages longer than 7,990 characters[/color]This example shows how to send a message longer than 7,990 characters.Because message is limited to the length of a varchar (less rowoverhead, asare all stored procedure parameters), this example writes the longmessageinto a global temporary table consisting of a single text column. Thecontents of this temporary table are then sent in mail using the@queryparameter.CREATE TABLE ##texttab (c1 text)INSERT ##texttab values ('Put your long message here.')DECLARE @cmd varchar(56)SET @cmd = 'SELECT c1 FROM ##texttab'EXEC master.dbo.xp_sendmail 'robertk',@query = @cmd, @no_header= 'TRUE'DROP TABLE ##texttab
Hello all I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages: 1. "Changed database context to 'DataBaseName'." 2. (2 row(s) affected) How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?
Hello AllI am having a small problem retrieving data from our main Sql Server. The data is coming from a 'text' column in the database. When I pull this data from our testing server and present it on a page, everything is fine. When I pull the same data from the main server, the text is distorted. It seems that the text is that which is enclosed with quotation marks. I assume this has something to do with the db settings. Does anyone know how fix this I'll try to post an example below.Main Server:We use the Russell 3000 Index as our primary benchmark. The Russell 3000 Value Index is sometimes used; however, since we are a “relative valueâ€Â� manager, we sometimes hold “bustedâ€Â� growth stocks that won’t be included in the Russell 3000 Value Index. Furthermore, we are indifferent as to which benchmark a client wishes to use for performance measurement purposes. We have clients that benchmark us against the Russell 3000, the Russell 3000 Value and the S&P 500 index.Testing Server: We use the Russell 3000 Index as our primary benchmark. The Russell 3000 Value Index is sometimes used; however, since we are a “relative value� manager, we sometimes hold “busted� growth stocks that won’t be included in the Russell 3000 Value Index. Furthermore, we are indifferent as to which benchmark a client wishes to use for performance measurement purposes. We have clients that benchmark us against the Russell 3000, the Russell 3000 Value and the S&P 500 index.Thanks for any help
basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.
i read a thread here which is similar to what i am trying to do but it was in xml format..
I am trying to catch and parse the message after passing a transaction to SQL from a clr proc. For example in the below code after passing sp.ExecuteAndSend(qry) how can I receive the message of "(1 row(s) affected)" from the messages tab. I know that I can write to the message tab with sp.Send, but is it possible to read from the output?
Thank you in advance for your assistance
Code Snippet Imports System.Data.SqlClient
Partial Public Class StoredProcedures _ Public Shared Sub prepxml _ (ByVal sInputFile As String) Dim sContents As String Dim sp As SqlPipe = SqlContext.Pipe() Dim qry As New SqlCommand() Dim stmReader As New StreamReader(sInputFile)
Try 'Create a string reader and pass it the input parameter of the 'file path. The sContents variable will receive the content of 'the stream reader sContents = stmReader.ReadToEnd() stmReader.Close() 'Replace single quotes to enclose the quotes so errors aren't raised sContents = sContents.Replace("'", "''''") 'Create a query object and convet the sContents to an xml data 'type qry.CommandText = " SELECT CONVERT(XML, '" & _ sContents & "')" 'Execute the query and pass the result set back to SQL sp.ExecuteAndSend(qry)
sp.Send(qry.CommandText.ToString.Substring(1, 3000)) Catch ex As Exception 'Cath any exceptions and use a sqlpipe to send the error 'message back to sql sp.Send(ex.Message.ToString) End Try End Sub End Class
The error message that comes while I preview the source data:
Error:
"The Preview sample contains embedded text qualifiers. The flat file parser doesnot support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at runtime"
Is there any alternative to remove these text qualifiers from the file. Do we have any utility that can convert these files into text qualifier free CSV file?
Our company sends out sms text mesgs from our MSSQL Database and stores the responses in a table when a reply is received. My problem is now that some mesgs are showing as blank(even when I copy the raw data field out of the query return window) yet when I check the length of the field, it contains data. I set the field to a Temp Variable and converted the contents to ascii and definitely found that it shows data.
We use this reply as a rating system and I have queries and functions that extract the info I need, the problem is that I need to convert this field into a readable field first before I can extract the info.
SQL example:
Select T2.ReceivedData , Datalength(T2.ReceivedData) Length FROM DMZWEB01.[SMS_SERVICE].[dbo].[Sent] T1 LEFT JOIN DMZWEB01.[SMS_SERVICE].[dbo].[Reply] T2 ON T1.ID = T2.ID INNER JOIN Clm_ClaimsRegister T3 ON T1.PolicyNumber = T3.Claim_No Where T1.id = 1158008
This returns a blank field and a datalength of 6.
To prove that the field contains data, a query that can run is:
DECLARE @Temp VarChar(8) = (Select T2.ReceivedData FROM DMZWEB01.[SMS_SERVICE].[dbo].[Sent] T1 LEFT JOIN DMZWEB01.[SMS_SERVICE].[dbo].[Reply] T2 ON T1.ID = T2.ID INNER JOIN Clm_ClaimsRegister T3 ON T1.PolicyNumber = T3.Claim_No Where T1.id = 1158008
[Code] ...
This returns < Line numbers >
1> 2>1 3> 4>0 5>& 6>: 7>
the actual field is pasted between these two comment '' signs below '' see? nothing
I'm new to SQL Server and I'm building an SQL Server 2000 database that will be loaded and accessed via stored procedures.
One of the requirements is that I have to log any errors that occur to an error log table containing the date/time, error code and the exact error text - ie containing problem table name, column name, etc.
The problem I'm having is that I can't see a way of capturing the exact error text in a stored procedure. The only thing that seems available is @@error which only returns the error code - not the actual text. I could go to the sysmessages table using the error code but that'll only return the message template containing parameters - not containing the actual table/column/etc causing the problem.
Does anyone have any ideas how to retrieve the exact error message text?
I am pulling my hair out trying to resolve this issue. I have placed several messages on different message boards and have not received any response to this issue. Can anyone here please help me?
I have installed 'full text index' on a server, initialized it and created a job to full text two fields in a table. I can see that the last refresh was a day ago. I am calling a stored procedure: (the server is version 7 of SQL Server with sp3)
SELECTfaqID, FaqQuestion, FaqText, Status, InputDate FROMfaqs WHEREContains(*, @pSearchString) and Status = 1
All I get is this error message, whether I call this from an asp page or run it directly from query analyzer:
Microsoft OLE DB Provider for SQL Server error '80040e14' Search on full-text catalog 'etr_fts' for database ID 10, table ID 1429580131 with search condition 'faq' failed with unknown result (324bd27c).
My goal: I want to hide some text or table from inside the email message body. I don't want to attach a file with a table neither I want copy/paste it. I'd like to put that Excel table inside the message but the table is quite big.
Before I used Lotus Notes and it had the option to fold of hide some text from user. User could open when it wants but it will not take the space.
Hey All, I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form. I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Hey All, I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form. I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Hello all, I am making an auction system using C#, .NET and MS SQL Server. I have a page to add new products to DB, which works fine.
What I like to have is that, I want to be able to show friendly confirmation and error messages to users.
So it is going to work like this: - user adds a new product - if successful there is a message on the page that reads: The products (product name) was successfully added to the database. And form fields are clear, ready for the next product info to be entered. - if not successful, the message should tell the user and maybe indicates the reason too. Like: the product code used already exists.
Currently for the successful attempts I get the form page with all the fields filled with the entered data and for un-successful one the ASP.NET error page.
Can anybody help please? Does anybody knows about a tutorial or an article or ...?
I was wondering if anybody knows where to get a complete list of SQL Server error messages. I am writing a stored procedure that scans SQL Server Logs for errors and if there are errors in the logs, I get paged.
HI! i need to grab the "server message" from sql server when i execute a sql script. i would retrive all messages , 'print' output too in order to put the messages into a textbox.
We have recently upgraded our SQL Express 2005 server, to SQL Server Standard 2005.
It refused to install a new default instance during install, so i upgraded the SQLEXPRESS instance instead. Im now trying to install Microsoft CRM but when it tries to connect to the SQL server get the following error:
"Setup was unable to verify that the SQL Server Agent (SQLSERVERAGENT) was running."
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
"Microsoft SQL Server Reporting Services
[DBNETLIB][ConnectionOpen (Connect ()).]SQL Server does not exist or access denied."
As far as i can tell, the problem is that when we upgraded, the pipe(?) or sql service name(?) (im not an SQL expert,so unsure of what to call things at this stage!!) is SQLEXPRESS instead of SQLSERVERAGENT. Is there a way of changing what the install routing is expecting? or a way of renaming this within SQL?
I am getting following messages in sqlagent.out. Can anyone help me understand what does these mean.. and what can i do for them?
2006-11-17 15:45:00 - + [235] Job CR QUEUE ON LINE - [HU][10720]20061117 15:44:51 is being deleted (job has delete level 1) 2006-11-20 09:33:20 - + [235] Job CR QUEUE ON LINE - [WM][3293]20061120 09:33:09 is being deleted (job has delete level 1) 2006-11-20 11:24:37 - + [235] Job CR QUEUE ON LINE - [LA][541]20061120 11:24:26 is being deleted (job has delete level 1) 2006-11-20 11:25:24 - + [235] Job CR QUEUE ON LINE - [LA][542]20061120 11:25:16 is being deleted (job has delete level 1) 2006-11-20 11:35:37 - + [235] Job CR QUEUE ON LINE - [HU][10721]20061120 11:35:27 is being deleted (job has delete level 1) 2006-11-20 12:04:22 - + [235] Job CR QUEUE ON LINE - [LB][3920]20061120 12:04:12 is being deleted (job has delete level 1) 2006-11-20 13:30:46 - + [235] Job CR QUEUE ON LINE - [PD][2594]20061120 13:30:35 is being deleted (job has delete level 1) 2006-11-20 15:44:43 - + [235] Job CR QUEUE ON LINE - [LB][3921]20061120 15:44:32 is being deleted (job has delete level 1) 2006-11-21 08:57:46 - + [235] Job CR QUEUE ON LINE - [HU][10722]20061121 08:57:29 is being deleted (job has delete level 1) 2006-11-21 09:00:14 - + [235] Job CR QUEUE ON LINE - [HU][10724]20061121 09:00:05 is being deleted (job has delete level 1) 2006-11-21 09:04:43 - + [235] Job CR QUEUE ON LINE - [HU][10725]20061121 09:04:34 is being deleted (job has delete level 1)
I was wondering if anybody knows where to get a complete list of SQL Server error messages. I am writing a stored procedure that scans SQL Server Logs for errors and if there are errors in the logs, I get paged.
I am using Reporting services through web services using the SOAP API. The problem I am facing is for any errors that occur the messages are getting displayed more than once.
For example : while rendering a report(which takes a parameter) if the parameter is not passed the same error message is displayed thrice.
"This report requires a default or user-defined value for the report parameter 'Num_RP_1'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'Num_RP_1'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'Num_RP_1'. To run or subscribe to this report, you must provide a parameter value."
while calling listChildren() method if the folderpath passed is a non existing one then the same error message is displayed twice.
"The path of the item '/SomeNonExistantPath/' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. ---> The path of the item '/SomeNonExistantPath/' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash."
1. Why some messages are displayed twice and some are displayed thrice ?
2. Is there any way to get the error message only once ?
I am trying to send messages between 2 different server instances. I am getting the following errors in Profiler:
"This message could not be delivered because the user with ID 0 in database ID 14 does not have permission to send to the service. Service name: 'TestService1'."
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
The scripts for object creation and messaging is following at the first server instance:
USE master GO CREATE ENDPOINT SBroker STATE = STARTED AS TCP ( LISTENER_PORT = 1212 ) FOR SERVICE_BROKER ( ENCRYPTION = DISABLED ); GO
USE Test GO CREATE QUEUE TestQueue WITH STATUS=ON
CREATE SERVICE TestService AUTHORIZATION dbo ON QUEUE TestQueue
BEGIN DIALOG CONVERSATION @dh FROM SERVICE [TestService] TO SERVICE 'TestService1','2B7CE76A-9804-46F3-9AE8-0AE59313613A' ON CONTRACT [DEFAULT] WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dh MESSAGE TYPE [DEFAULT] ('this is message1');
DECLARE @status nvarchar(1024); SELECT status = GET_TRANSMISSION_STATUS(@dh);
END CONVERSATION @dh;
on second server instance:
use master GO CREATE ENDPOINT SBroker2 STATE = STARTED AS TCP ( LISTENER_PORT = 1212 ) FOR SERVICE_BROKER ( ENCRYPTION = DISABLED ); GO
in first server, in sys.transmission_queue transmission_status is empty
Both servers in the same domain, and databases on this server has the same owner.
on both servers, select @@version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have installed Sp1 for Sql Server 2005 I also received "locked files" and "reboot required". These errors I can find on all forums and do not worry me anymore. However i still would like to know more about the messages in the log files.
- What does this mean: "Failed to read associated hotfix build information for the following file"...... - What does this men: "Failed to read version information for the following file".........
- Why are some products NOT APPLIED, while in the other log file it says "SUCCES" for the same product? These beneath the 2 log files.
