Control Characters Output In Attachments From Xp_sendmail
Jul 20, 2005
I have been testing our SQL Mail setup in SQL Server 2000 (sp3a) and
have found that when I attach results as a file, every other character
is a control character which causes each real character output on a
separate line. I have no idea why this is happenening, I've never seen
it before.
The code looks like this;
When I view the result text file with an advanced text editor I can
see that every other character is a control - these characters are not
in the data, I have already checked this, so it looks like its they
are being created by SQL Server or the mail system? Any advice much
appreciated.
Beyond my control: I am finding control characters (likely tab) ismaking its way into address fields of our operational system. This ismessing me up when I load the data into our warehouse w/ BCP (fieldsget shifted).Is the a nifty way to strip control characters from data?TIARob
Hi guys, I have a gridview and textbox. I can filter the grid just fine with the textbox, but I would like to allow the user to type in part of a name. I thought something like this would work but it doesn't: Emp_Name LIKE '%'+@Emp_Name+'%' Thank you.
Hi All !Is it possible to get rid of these dash symbols which are underliningthe column name when recordset is returned after query execution ?For example, using isql.exe:SELECT 'blah'goproduces the following results:----blahWhat I want to achieve is justblahI know that SET NOCOUNT ON switches the "X row affected" thing. Buthow about column headers ?Thanks for your time,Seeker
I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.
CREATE PROC proc_Emp
@Sel_Country AS BEGIN
SELECT DISTINCT employee, hrid FROM emp_table WHERE country = @Sel_Country
@SEND_RESULTS = CASE WHEN row_count = 0 THEN
SELECT 'No Country', 4444444444 ELSE
(send original results) END
END
I need to:
Query for the employee list
If the row count is zero/null then return fixed values
Else return the orginal data from Step 1. I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.
Hi all, I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row. The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.
Is there any way i could do this?
Exmaple: In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!
Procedure dbo.AddressInsert(....) as BEGIN
Insert INTO Adress ....
SELECT * FROM AdressView WHERE AddressID = @@identity END
Procedure dbo.CustomerInsert(...) as begin -- transcation begin...
EXEC dbo.AddressInsert
INSERT INTO Customer....
-- end transcation
SELECT * FROM Customer WHERE CustomerID = @@identity
I'm trying to take the output of the Render method and bind it to the ReportViewer control. First of all, is this possible? If so, what is the best way to render the report (format: XML,CSV, NULL, etc), and then how to I 'bind' the result to the control?
I'm proving some code to demonstrate how I'm rendering the report: ... string format = "XML"; string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"; ReportExecutionService rs = new ReportExecutionService(); ExecutionInfo execInfo = new ExecutionInfo(); ExecutionHeader execHeader = new ExecutionHeader(); byte[] result= null;
rs.ExecutionHeaderValue = execHeader; execInfo = rs.LoadReport(path, null); string SessionId = rs.ExecutionHeaderValue.ExecutionID; result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
Thanks, any help would be greatly appreciated. Peter
We are experiencing a problem where a server report is rendered in the ReportViewer control successfully but when the user chooses to print the content, the print output is incomplete (ex. only 18 of 23 pages are printed, etc.).
This is happening consistently and has been reproduced for multiple different reports. It does not happen if the same report is printed via the Report Manager web interface.
I have also found that if I switch to Print Layout mode and then initiate the print from the ReportViewer that the problem does not occur. This is our current workaround, but it would be nice to know what is causing this and have it fixed.
Has anybody else seen this? Is this a known issue with my current version (SQLRS 2005 with SP1 applied).
I am trying to use a command line program to run a stored procedurethat generates output in a comma-delimitted format. Somehow, ISQL orOSQL always wrap the lines at 256 characters. I believe this hassomething to do with the column width switch (-w). But enlarging thecolumn width to 800 characters max still doesn't help. The followingis a stored procedure that is essentially doing what my storedprocedure is doing:create procedure MyTest asset ansi_padding onset nocount ondeclare @sTest varchar(300)-- Output three lines. Each line has 259 characters.select @sTest = "1234 6789 ... 1234 6789"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestset nocount offreturn( 0 )I invoke this stored procedure using this command:isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"-oMyTest.txt-- or --osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txtBut they have the same problem. The output lines all wrap around at256 characters.Strangely, if I store the result in a temporary table, and then useSELECT to output the result from the temporary table, I will not havethat problem. Seem like the "-w" switch only works for output fromtables, but not for output coming from PRINT. Unfortunately, usingthis approach has another set of problems (one blank space in front ofeach line, "number-of-rows affected" shows up at the bottom).Therefore, I would like to stick with using PRINT statements to outputthe result.Please suggest a way to fix this line-wrapping-around problem.Thanks.Jay Chan
I have just setup a new Reporting Service Server. I loaded a page then I created a subscription which will email and excel document of the report out.
Each time the subscription runs I am getting this error below:
Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.
When I take out the attachment on the email and just have it send the link the email runs fine with no errors. Is there some type of setup that needs to be done with the server side to have it send attachments?
Hi, I have developed a web app where documents may be uploaded onto a server, and then opened of downloaded from the server by the users. Every type of file can be opened and downloaded apart from .sql. The main types of files that are uploaded are .txt, .dox, .pdf, .xls and .psd (photoshop), they all work apart from .sql just wondering does somthing different have to be done for .sql files?
the uploading is working, it is not that the file doesnt exist on the server
I can create subscriptions with attachments in my dev environment but cannot do so in the production environment. Subs with links send out fine in Prod but SRS seems to fail when we want to add the report as attachment.
Could there be an issue with the installation in what service accounts were used. I did not do the initial installation in Production, only development and we used the SQLServerSrvc domain account there. I tried changing the service accounts in Prod but subs with attachments still do not work.
I did verify that the server can communicate with our SMTP server and the SMTP server isn't limiting attachments etc.
I thought I'd share this with you a) because I'm amazed, and b) to hopefully save anybody wasting the amount of time I did.
I created a report called 'Attachments by Account', and deployed it to reporting server. Then when testing it, I'd always get a File Download prompt for an html file, rather than the rendered content. After an enormous amount of messing with the report content, stripping out actions and hyperlinks and script etc., I uploaded a copy called 'aa' out of laziness, and it worked fine. Then I renamed it back to 'Attachments by Account', and I'd get the File Download prompt again. Remove the word 'Attachments' from the name and it works.
How on earth does something like that happen ?
p.s. This is SQL 2000 RS, sp 2. I've not got round to testing SQL 2005
We currently email out reports as either PDF or Excel attachments using Data Driven subscriptions in SSRS 2005. I've been asked to be able to encrypt the attachments and am wondering if anyone has done this or can point me in the right direction.
There's been a couple of mentions about "encrypted email" -- but I don't know where to find further information. Is this possible using SSRS subscriptions?
i need to send a report as e-mail. so i'm trying it through email subscription. i'm rendering the report in MHTML format. I'm able to get the report in mail but the problem is the images (i,e bar graph,charts and embedded images) in the report are getting displayed below the actual report and all images in the report are added as attachment. i need only the generated report as mail and not these extra images that gets embedded in the email.Is there is any way to get this done??
I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?
Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.
CREATE TABLE dbo.[CM_Attachment] ( [Id] int IDENTITY (1, 1) NOT NULL , [Change_Request_Id] int NOT NULL , [Attachment] varbinary(max) not null, [Created_Date] datetime NOT NULL DEFAULT GetDate() , [Created_By] varchar(30) NOT NULL , [Modified_Date] datetime NOT NULL , [Modified_By] varchar(30) NOT NULL ) GO
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By) SELECT 6, BulkColumn , '', GetDate(), '' FROM Openrowset( Bulk 'C:TestingTest Doc #1.txt', SINGLE_CLOB) -- Does not work -- FROM Openrowset( Bulk 'C:TestingTest Doc #2.xls', Single_Blob) -- Works -- FROM Openrowset( Bulk 'C:TestingTest Doc #3.jpg', Single_Blob) -- Works as ChangeRequestAttachment
I have a DTS that creates multiple .zip files in a folder. Each zip file has a different name. My goal is to attache all the zips to a single email in a Send Mail Task. I have tried to do so with something like "C:Folder*.zip", but have had no luck. Any suggestions?
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link: http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.
My company uses database mail extensively for alerting and the like, and most emails sent have attachments. We've put in place procedures for removing emails older than a month from the MSDB database, however we've now had a client server completely run out of the disk space due to attachments being stored in the C:Documents and SettingsUserLocal SettingsTemp folder!?!
When I check the sysmail_delete_mailitems_sp sproc, it only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.
Does anyone know why SQL Server stores them in the temp directory of the service account user, as well as how we can remove them (apart from manually deleting the files), as we need to have an automated process for cleaning up emails, and at the moment SQL Server 2005 doesn't handle this very well.
I've checked this on our test server as well, and we're getting the same there, and this server is running the latest SP2 + hot fixes.
i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?
I need to email a report that contains a date in the report name. Can I build the file name with script and use a variable in the attachments field on the send mail task. Any advice or suggestions would be appreciated.
Hi All, I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
The script is working fine if i manually provided file path to @file_attachments. it is showing error i passes the variable @Loc3 to @file_attachments. Below is the error if i passes the variable to @file_attachments parameter.
(1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 22051, Level 16, State 1, Line 0 Attachment file 'E:DatabaseTannaFinanceReportDetail_Reimbursement_1_15_Aug2015.csv is invalid.
I am using the below code to send HTML Email body to multiple recipients and CC, its working fine. Now i have to attach multiple files in that mail. Is there any possibilities to attach multiple files with the below code else provide any other code to achieve this task.
Code: /*   Microsoft SQL Server Integration Services Script Task   Write scripts using Microsoft Visual C# 2008.   The ScriptMain is the entry point class of the script.
Hi everybody, I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
