How To Set Up A Regular Job Which Will Put The Quesry Result Into File And Email It?
Oct 17, 2006
Dear forum people,
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
help,a regular text file how to sql 2000 table code ?i have a text file as follow, line with ¡°|¡±and {LF},8|-000000186075919.|+000000000387820.|2008-03-31|20010423|9|-000000000003919.|-000000000123620.|2008-03-31|20010123|8|-000000018623419.|+000000000381230.|2008-05-30|20010423|i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandatesmalldatetime£¬update smalldatetime£¬after to sql table is as follow,sign year month trandate update8 -186075919.000 387820.000 3/31/2008 4/23/20019 -3919.000 -123620.000 3/31/2008 1/1/20018 -18623419.000 387820.000 5/30/2008 4/23/2001could you help me how write the sql code ?
Whats the best way to send a small query result using Send Email Task. I have log table not more then 50 rows with 3 columns I need to query this table and send email.
I tried using Exe SQL Task and in result set I tried to store in variable so that I can attached that variable as email source. I think query result I can't store in string variable like this. Any simple way?
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail @subject = 'Job Summary', @profile_name = 'SQL SMTP',    [code]....
Does anybody know of a way to set up a task which will send an email with an attachment other than using sp_sendmail. Sp_SendMail will run a query and send the results as a text file. But I need to run a stored procedure which generates an Excel spreadsheet then have an email sent with the Excel file as an attachment. The sp_sendmail text file just doesn't work with the information I am generating so I need to find another solution.
I need to extract a attached file which is sent via email. I believe that I can do this via SSIS... but I would like to know how... Could you kindly send me any idea about how can I do it?
I've set up an alert to email me whenever the database is over a certain size. The amount is calculated by taking 80% of the total data file size. The problem I'm having is that it keeps generating a false positive alert because MS SQL seems to treat the currently used value as the total allocated space for the database data file. For example, the data file is 100MB, it's currently using 60MB, and if I enter 80MB in the alert, it generates an email alert claiming the current size is 100MB. Could it be because the data file size is set to 100MB (since autogrowth is disabled)?
I would like to send the contents of a file using xp_sendmail howeverI do not want the file contents to be an attachment.I have no problem sending the file as an attachement.Can anybody give me an xp_sendmail example of how to do this.The results of a query can easily appear in the body of the email butall myattempts to include the contents of a file in the body of the emailhave not worked.TIA
First, I would like to apologize if this is in anyway not related to Transact-SQL. And I would greatly appreciate your help for this is an urgent requirement. I just have a batch file which performs an ftp. Now, if the ftp process aborts, i would like to send out a mail and page the on-call person. I am not sure how to do this - I mean how to send email and send message through pager. My batch file is called by the Windows scheduler.
In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that?
I have the below query which gives me statement to apply the login name, SID and PWD and that I statement I can apply to Dr and secondary server. Incase of failover there will be no connection issue.My query is
SET NOCOUNT ON SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + '''' ,', @defdb = ''' + dbname + '''' ,', @deflanguage = ''' + language + ''''
[code]....
This query output I wanted in a sqlfile so that I can schedule a batch file or sqlcmd and apply the login detail from the new one inserted in the primary.
Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
Hi, I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ? Thanking you...
I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.
Can u help me out in the following two scenario/problems.
1. I want to save the query results (which is generated within execute sql task) to file (which does not exists & should be created at run time). is it possible?
2. Can I define the file name, in Flat File connection Manager, dynamically (by using some variable)?
how can i dynamically specify the name of a report before sending in an email (example @reportName@datetime@parameter1@ServerName)
I have read most of the related articles and i am still curious about this topic - the technology is there so why wouldn't microsoft just add a REPORTNAME field to the email data driven subscription?
Is it possible to save the results of several SP calls in a script, to one file?
Here's what I mean: I want to run these 4 sp calls-- exec EPC_SP1 'aph','live' exec EAUI_SP2 'noble','newswire' exec EAUI_SP3 'noble',1 exec EAUI_SP4 5507,'live'
And save the results of each one of those calls to the same file, in other words, an APPEND