Transact SQL :: Only Generate Email If Table Contains Data?
Jul 1, 2015
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;
View 3 Replies
ADVERTISEMENT
May 30, 2013
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies
View Related
Oct 13, 2015
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
View 3 Replies
View Related
Oct 31, 2015
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
Eg:
Table 1 has Account Count
Table 2 has Account Count
Table 3 has Account Count
Table 4 has Account Count
Table 5 has Account Count
I want output as:
Account Count Account Count Account Count Account Count Account Count
View 4 Replies
View Related
Oct 19, 2015
I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name | Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name | Last Name | Mothers Name
a b cdef
a b cdfg
a b cdfj
Expected Usernames:
1: a.b
2: a.b.c
3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
View 4 Replies
View Related
Aug 5, 2015
I have some records (approx 100K - 500K) in my table (Name, DOB, Zip, Phone). I want to validate each record and every field as given below.
Name - Should not have numerics
DOB - Should be in yyyy-mm-dd format, should be < 1995
Zip - Should be 5 char zip, allowed 3 or 4 char zip for some States
Phone - Should have 10 numeric digits, should be in xxxx-xxxx-xx format.
After validating these rules, If the record is valid then send email saying valid record, if invalid then send email with validation failure details like which column failed which validation rule suppose DOB might not be in specified format or it might be > 2000 all these details.
How to validate all the records in bulk without any loop or cursor.
View 10 Replies
View Related
Oct 22, 2015
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
View 8 Replies
View Related
Jan 21, 2005
Help with query: I currently cannot be alerted by SQL Mail so I would like to take the script that was generated by SQL server and using the store procedure sp_sqlsmtpmail to generate a email using smtp server to alert me. The store procedure does work. I would like to know if this is possible.
Thanks
Lystra
-- Script generated on 1/21/2005 10:04 AM
-- By: MAMSIsa
-- Server: (local)
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Full tempdb', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'
Then
Exec sp_sqlsmtpmail
@vcTo = 'lwilliams@Huc.com',
@vcBody ='Check out problem Immediataly.',
@vcSubject ='DOCSITE01FDK - Full Tempdb Log'
END
View 2 Replies
View Related
Aug 24, 2005
Hi
i have one problem in database to creating SQL script
i want to copy table strucure with table data
Pls help
Shailesh Kavathiya
http://www.codegroups.com/blog/
View 9 Replies
View Related
Sep 23, 2005
Hello there,
Im alittle stuck
what im trying to find out is:
MS SQL 2000
1. How to Generate Reports from selected Database information
2. then email that report weekly.
Ive heard about Visual Studio .NET 2003: Business Intelligence Projects
but is there away to create the reports directly in Enterprise manager?
any pointers would be great
View 3 Replies
View Related
Jul 24, 2015
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]....
View 3 Replies
View Related
Jul 6, 2015
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies
View Related
Jun 6, 2015
I have requirement of sending table data or result of an query as an email with SSIS package.
View 6 Replies
View Related
Jun 25, 2014
What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
View 4 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Oct 13, 2015
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
View 5 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 Replies
View Related
May 21, 2015
I have this script which generates droptable tablename.
select top 10000 'drop table '+name, * from sys.tables (nolock) where name like '%4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' order by create_date
I would like to generate 'GO' after each drop statement.
View 3 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Oct 5, 2015
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
Actual Data
Expected Result
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
View 3 Replies
View Related
Jul 12, 2007
Hello ALL
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.
Please help me with the best way to do this
Thanks in advance
View 6 Replies
View Related
Oct 22, 2015
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
View 16 Replies
View Related
Sep 29, 2015
Is there any method to get the T-SQL command/ statement using DML triggers?.
i.e. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT.
View 4 Replies
View Related
Apr 22, 2015
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
View 10 Replies
View Related
Jul 2, 2015
I have one PS script; Basically it reads all application logs and give a report. Here is the PS script:-
$logs='C:AppLog*.log'
$ufo=Get-Date -ufo '%Y-%m-%d' # Unidentified Flying Object
$match="^$ufo.+error.+$"
sls $match $logs|
group filename -noe|%{
[pscustomobject]@{Name=$_.name;Date=$ufo;'#Errors'=$_.count}
[code]..
Now I would like to setup a SQL job and want to get the result in email.
View 5 Replies
View Related
May 28, 2015
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
View 6 Replies
View Related
Mar 22, 2011
I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.
Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.
I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.
How can I do this ?
View 8 Replies
View Related
Sep 10, 2015
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
View 2 Replies
View Related
May 24, 2015
I have a dataset which is like:
Month, Day, Location, TotalSales
Jan 1 A 100
Jan 1 B 200
Jan 14 A 120
Feb 2 A 130
Mar 5 B 150
I want to transform the dataset using sql query into the following format:
Month, Day, LocationATotalSales, LocationBTotalSales, TotalSales
Jan 1 100 200 300
Jan 14 120 0 120
Feb 2 130 0 130
Mar 5 0 150 150
View 2 Replies
View Related
Jul 10, 2015
How can I write it in SQL? I have a table that column A has two values of Product and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4 more rows, and add column b with values from 1 to 5 for each rows ( counter).
View 3 Replies
View Related
Aug 22, 2015
we have a table below
TABLE1
ID Roll Name Amount . . . . . . so on
---------------------------------------
1 2 Alex 500
2 5 Jones 600
3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on
---------------------------------------
1 2 Alex 500
3 2 Ales 400
how can I do this in a same sql statement.
View 3 Replies
View Related