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?
Rgds
Bertrand
When you do a select statement, you get a message saying "n row(s) affected" where n is the number of rows affected. Is there any way of turning off this message?
View 4 Replies View RelatedHi,
After running BCP to export data from table to text file, we are getting some system generated messages in the log file as below :
output
------------------------------------------------------------------------------NULL
Starting copy...
NULL
81 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 30 Avg 0 (2700.00 rows per sec.)
(6 row(s) affected)
==========================
We want to suppress these messages while generating log file. Is there any option for this ?
Saritha.
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
View 3 Replies View RelatedHi,
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.
Thanks for your help.
Tietie.
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.
Thanks for your help.
Tietie.
Thanks in advance.
Tietie
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?
Hi,I need to get a list of columns and corresponding datatypes for thegiven table name. I am using the query:selecta.name,b.type_namefromsyscolumns a,master.dbo.spt_datatype_info b,systypes c,sysobjects dwherea.xusertype = c.xusertype andb.ss_dtype = c.xtype anda.ID = (SELECT OBJECT_ID(<tablename>)) anda.ID = d.ID andd.ID = (SELECT OBJECT_ID(<tablename>))This returns multiple sets of data for each column. This is becausethe same column name is repeated in multiple tables in primarykey-foreign key relationships. Is there a way in which I can get onlyone row for each column of a table?
View 2 Replies View Related Hi, Please tell me that with SQL Reporting there is some way to suppress printing if no data returned? So if did not want reports to print when an order is cancelled you would just make stored proc return nothing in that scenario and nothing would print?
So if the data set doesnt return ny value , then RDL should not print blank page.
Does anybody faced this, any help willbe highly appreciated.
Thanks
I created a package that will, on it's very first step, create a temporary table that will be used throughout the package. In the final step I drop this table.
Running the package bit by bit works, however the moment I attempt to run the full package (or the container that holds the bulk of it) i get a validation error because the table does not exists.
Anyone knows how can I suppress this validation from occurring? I know the table wont exists until the package starts executing and it telling me it cant proceed because the table does not exists is preventing me from doing anything.
I have one main report which has 5 subreport. i dont want to show all the sub reports all the time. i want to suppress any subreport based on some conditioned. can i do it in case of the SSRS. How?
View 2 Replies View Related
Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:
/* SQL Server & Windows NT login name, security access */
USE master
go
SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME
FROM syslogins
WHERE status != 0
AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))
I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
Thanks,
Paul
I have an execute process task that kicks off gzip to uncompress files within a for each loop. We get a LOT of bad files which causes gzip to throw an unexpected EOF error. This gets bubbled up into SSIS as a Win32 unhandled exception error which then throws up the VS JIT Debugger interface. I know what these errors are and do not want to debug. Is there anyway that I can simply ignore the exception and just throw it away?
View 5 Replies View RelatedHow do I suppress duplicate values from being displayed on a report data region? Is there an option to suppress duplicates and where is it?
Hello, please 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
kind regards,
David Weeden
Database Developer
I don't know if its the same for 6.5...but I created an app that connects to a SQL server (using ADO) to execute a stored procedure.....in this stored procedure I execute commands that change the database....eg use master...
upon typing this command...SQL server returns such things as "The database is changed to master." This is interpreted by ADO as an error. Problem with this is that I can't tell if the script executed properly or not....I can ignore all errors and everything is fine....but my application is dependant on data integrity and I was wondering if anybody knew anyway to suppress these informational messages.
Thanks in advance.
Puneet
Trying to obtain a full list of SQL 6.5 error messages into a file. There are
2,215 messages present in master.sysmessages, but there is a total of 2,733
messages listed in the dialog box within enterprise manager. Message numbers
17026 thru 19020 don't seem to be stored in the sysmessages table.
Anyone know how I could obtain an extract to obtain a full list of messages ?
After sending a message the message is just gone.
Our transmissionand and out our transactionqueue is empty.
This is our CLIENT:
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
SERVER
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
Hopefull you have an idea?
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON
DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.
View 13 Replies View RelatedIs there a way to return the SQL Native error to a Visual Basic program?
View 3 Replies View RelatedHey everyone,
Here is what I'm doing:
exec xp_sendmail @recipients = 'ampx@hotmail.com',
@query = 'select * from information where recid <= 10',
@subject = 'Query test',
@message = 'hello',
@attach_results = 'TRUE',
@width = 250
I get this error:
Msg 2812, Level 16, State 4
Stored procedure 'xp_sendmail' not found.
Anyone know why? isn't xp_sendmail a function of SQL?
Thanks
I have a website with a DB table of messages sent by users.
I want to select the first 3 messages for each unique user.
I found a way to accomplish this by pulling all of the messages from the sql and filtering the results via ASP.net code but it seems to be really really slow.
I hope one of you guys can help me accomplish this via sql .
Messages Table
Mid Username SendDate
110016/07/06 13:00
210017/07/06 14:00
310117/07/06 14:30
410217/07/06 15:00
510318/07/06 16:00
610018/07/06 17:00
710218/07/06 18:01
810319/07/06 18:02
911019/07/06 19:03
1010219/07/06 19:04
1110021/07/06 20:00
1210321/07/06 20:00
1310221/07/06 20:00
Query result:
Mid
1
2
6
3
4
7
10
5
8
12
9
THANK you guys!
Hi,
I have a table that has 4 fields and has free format text messages. The Identification field contains the client id
MNo field has Message Number specific client id. A single messge is split down into multiple rows and Seq field gives the relation which text message belongs to which MNo
Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20
CLIENT-01327002002regarding the Pete relocation, there is concern as to whether this
CLIENT-01327002003sale is good or not. It appears that the prospective buy
CLIENT-01327002004er will be in tommorrow if he does not produce a firm fina
CLIENT-01327002005ncing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle
I need to create a query where i need to join all the text messages that belong to the same MNo and Identification into one field. The output should be like the following:
Output Message table:
IdentificationMNoSeqText
CLIENT-01980001001This is the header record for client ABS.
CLIENT-01426001001This is the Header Record for DAN client #1426
CLIENT-01327001001This is the Header record for Glaxo Client
CLIENT-01327002001Jen: pursuant to my conversation via phone with you and Mel on 9/20 regarding the Pete relocation, there is concern as to whether this sale is good or
or not. It appears that the prospective buyer will be in tommorrow if he does not produce a firm financing commitment by that date.
CLIENT-01327003001Message for client A
CLIENT-09970001001This is the header record for client Nestle
In the above output table for Identification = CLIENT-01327 and Mno = 002 the entire text message has been concantenated into one field.
Is there any way that i can write in a single query to obtain the output file.. Please help.
Hello,I'd like to know if I use DTS. If I use it immediately, I can see the errorin the dialog box, instead, if I use it with scheduling, where Can I checkthe error ?ThanksSaimon(Florence)
View 2 Replies View Relatedwe've gotten this to work from sqlclr and understand the dependence wcf has on the framework but still feel compelled to ask if somehow t-sql can send a message to a wcf endpoint without getting sqlclr involved. I've moved this question over here from the t-sql forum.
View 1 Replies View RelatedHi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).
So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?
Hello,
I'm trying to do a very simple example of sending a message from Initiator queue to Target queue. The result is no messages are delivered.
Here's the code:
DECLARE @conversationHandle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE GmiInitiatorService
TO SERVICE 'GmiTargetService'
ON CONTRACT GmiContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversationHandle MESSAGE TYPE GmiMessage ('test');
END CONVERSATION @conversationHandle
All three queues are empty (Initiator, Transmission, Target). When I comment out the last line ("end conversation"), the messages get stuck in the Initiator queue.
Please help!!
Thank you.
Hey guys
I have another weird looking problem
I have a queue with a certain number of messages waiting in it to be processed. When I try and receive a message from this queue, I get back an empty row; no message. However, when I try and select from this queue, I see all the messages in there. Even sp_spaceused reports that there are rows in this queue. The status of each unreceivable message is 2. What would be the reason why I cannot receive these message?
I should add that newer messages added to this queue are receivable, meaning that if I add another message to the queue, when I do a receive, I get this new message and not the older ones in the list.
Thanks
Hello,
I am trying to figure out how to properly read a single message(poll) at a time from ADO .Net in c# 2005.
I am assuming i need to write a sproc that does a receive, but I wanted to validate if there were already any examples of this already?
I have been reading quite alot but don't see this exactly as an example anywhere and it is probably easier then my brain is allowing it to be.
If you have an example or would help me i would appreciate it. I am "hoping" for more then just "write a sproc and call it" as I believe this is accurate but am hoping for more info.
Happy to read, happy to research, but I am atm, lost for where to go next. I am going to start doing some plain testing, but appreciate any help.
Thanks,
-Mike
[Reposted because no-one answered me before: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1097115&SiteID=1]
Hi,
Quick one for anyone from Microsoft. Does there exist anywhere a definative list of all the new log messages that are being introduced in SP2?
Thanks
Jamie
Greetings
I'm learning SSIS and BIDS. I have extreme difficulty making sense of the error messages that come out.
First of all, what do the numbers mean? Each column, error, etc. is assigned a number that obviously means something yet I cannot relate them to anything. For example: The output column Name (713) on output Test (15) and Component (15) -- My table doesn't have 713 columns in it...
Then there are the error codes that obviously contain something useful. For example:
DTS Error: Microsoft.SqlServer.Dts.Runtime.TaskHost/QueueFuzzyName [33]SIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (46)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (46)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Where would I look up: DTS_E_INDUCEDTRANSFORMFAILUREONERROR and 0xC020907D? I understand that it tried to convert a value in something numbered 46 (no idea what that is) and the conversion failed. But that's it. How do I transmogrify 46 to something I can look at. I'm a little fuzzy on what a Destination Input is. Isn't an output a destination?
Or this one:
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors"
Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
I have absolutely no idea what this means except that many things happened, one of them failed, I have an error of some sort and it's error code is 0x80040E21. It must be important, it's in there twice, but what does it mean?
I'm thinking that in the help somewhere all these error messages are listed and I can look them up, but I can't seem to find it anywhere. Am I supposed to be converting them to decimal first?
Any advice from you experts would be much appreciated.