SQL Server 2008 :: Reminder Emails - Records Created With Status

Jun 29, 2015

We have a difficult requirement.

When records are initially created, they are given statuses of New, Pending, Closed

By default, the status is New.

If no action has been taken after 15 days since the records were created, users are sent a reminder.

I have this part working.

The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.

Here is what I am working with:

SELECT [ID] , [Name], [Email],
Status
FROMmyTable
WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending'
AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)

View 2 Replies


ADVERTISEMENT

Auto Reminder Emails?

Mar 30, 2007

I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?

View 4 Replies View Related

SQL Server 2008 :: Send Emails To Individuals In Query Results And Extra?

Jul 15, 2015

I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.

I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.

View 1 Replies View Related

SQL Server 2008 :: In Query - All Rows With Same Status

Oct 28, 2015

I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?

View 6 Replies View Related

SQL Server 2008 :: Getting Mail When A DB Is Created

Jan 6, 2011

On our SQL 2008, we'd like to receive a mail when a DB is created, and, if possible get the name of the newly created database IN the mail.

'till now get get mail I didn't find out how to get the name of the DB in the mail... should I use WMI ?

My database mail is configured and I created a WMI event alert with this query:

SELECT * FROM CREATE_DATABASE
+ Additionnal message in the options of the alert...

But I don't know how to get the name of the newly created db unfortunately.

View 9 Replies View Related

SQL Server 2008 :: Log Shipping Status Report Through Mail?

Feb 29, 2012

i'm writing below this code..

exec msdb..sp_send_dbmail
@profile_name = 'local',@recipients='chintu@www.com'
, @subject ='Log Shipping Job succeeded'
, @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'

I want Log shipping Status Through Mail. Last back up transaction in the day and last restoration trn file in the day...

View 9 Replies View Related

SQL Server 2008 :: Check DB Mail Status On All Servers

Apr 23, 2015

i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information

View 1 Replies View Related

SQL Server 2008 :: Status Suspended On Command Killed / Rollback

Mar 4, 2015

I was running a stored procedure it was suspended for about 11 hours so I decided to kill it now its in Killed/Rollback stage for 12 hours and when check the status of roll back it says "Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." its using up CPUTIME 380000 and DiskIO 970000. How to do I stop this co.mpletely

View 4 Replies View Related

SQL Server 2008 :: Script To Return Replication Status As Small Int

Mar 26, 2015

I need a script that will return the replication status as small int.

View 1 Replies View Related

SQL Server 2008 :: Insert Into Brand New Not Created Table

Mar 13, 2015

why my insert or into is not working in my SQL Server R2 2008. I have a code I am using on an existing table and trying to put the data in a brand new table but it keeps giving me an error

select mCid, caucasian, aa, api, aian, mr, his, max(val)
into memberrand
from MEMBERV2_RAND
cross apply (
select AA union all
select API union all
select AIAN union all
select MR union all
select HIS union all
select CAUCASIAN
) v(val)

group by mCid, AA, API, AIAN, MR, HIS, caucasian
;

The error is: Msg 1038, Level 15, State 5, Line 1..An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.Do I have to actually create this table with no values first and then run the query? I was hoping this was sort of a make a table query

View 2 Replies View Related

SQL Server 2008 :: 2 Emails Being Send From Job When Only 1 Should Send?

Apr 18, 2012

I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server.

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>'

[code]....

View 9 Replies View Related

SQL Server 2008 :: Newly Created Index And Execution Plan

Jun 17, 2015

I run a query

select col1, col2, col3, col4
from Table
where col2=5
order by col1

I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan

The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?

View 7 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

SQL Server 2008 :: Reporting Services Migration - Subscription Jobs Did Not Get Created

Apr 23, 2013

We are moving to a new data center. We have RS install on appweb server that points to different sql server for Reportserver and Reportservertempdb. So, We created a new virtual appweb server and new physical database sql server 2008 r2 at new DC. I did a fresh install of new reporting services 2008 r2 on appweb server. I backed up and restored Reportserver and reportservertempdb dbs from old 2005 sql server to new 2008 r2 database server.

During new RS configuration, I pointed databases to the new restored databases and restored encryption keys. When launched reporting services URL, it works fine.

I stopped and restarted reporting services after configuration.

However, I am not seeing any single job created on new database server. Old server had 20 jobs.

How do I get these subscriptions back to the new install? I thought this should be created with migration and I do not have to do anything.

One odd thing I had to do during config was that:

In scale-out deployment tab in RS configuration, it was showing both the new and old appweb server name as status joined. I was unable to remove the old server name by clicking 'remove server' there. So, I deleted old server name from keys table in Reportsserver as suggested by some forums to have a clean installation. Does it have anything to do with jobs not created?

What do I need to do now to get subscripts jobs created. Ther eare only 20 jobs here but another RS server has over 800 subscriptions that I can not mess up. So, need to find the issue and correct it before I can move on to the another RS instance.

View 3 Replies View Related

SQL Server 2008 :: Find Date Created And Modified Values Of All Files In A Location

Jun 11, 2015

Script to find the details of creation date and modified date of all files located in a path?

I wanna write few custom messages before I delete some files from a path.

View 9 Replies View Related

Query Notification - Subscription Created But Not Firing Service Broker Endpoint In Status SO

Mar 12, 2007

Hi,

SQL Server 2005 SP1. (+ 5 minutes fix installed).

Query notification subscription cretaed (sys.dm_qn_subscriptions).

Subscription does not fire in response to event.

sys.transmission_queue is empty.

profiler does not show errors.

sys.conversation_endpoints shows one endpoint in status SO. (along with a lot of left over garbage - about 300K records - but this should not be a problem other than storage).

I know SP2 is suppose to solve the garbage records problem but this is not our problem. This setup was working and then just stoped.

any ideas ?



Boaz

View 7 Replies View Related

SQL Server Agent - Auto Email Reminder

Jun 17, 2005

Hi Experts,
I have a table with fields:
remindWHO               remindWHEN        remindWHATsomeone@test.com    6/20/05                  do blah blah
I want SQL Server Agent automatically run every morning at 1:00 AM, check against "remindWHEN" date, if match with current date, send out email to "remindWHO" for what they need to do in "remindWHAT"
I was told this can be done by using the "Jobs" feature under SQL Server Agent to do this, but I don't know how to write Transact-SQL-Script under "Job Step".
Please help me. Or any better alternative?I appreciate for any help.

View 1 Replies View Related

SQL Server 2008 :: How To Get Records In One Row

Apr 15, 2015

IdIncident IDIncident Description

2853459140511
2861733118512
2861733118601
2869113140511
2872349118512
2872349118601
2895400140511
2900999140511
2914574140511
2923590140511
2925735274503
2925735274504
2925735274511

View 9 Replies View Related

SQL Server 2008 :: Multiple Records On One Row

Aug 20, 2015

I have a table of customer sales (dbo].[CustomerSales] which has a complete record od sales of our products for the past 5 years. I would like to create a report that extracts all the sales per customer on one row ie [CustomerID] 00011 has had 25 sales in the past 5 years and I would like to have their sales from left to right (starting with their earliest sale date at the beginning [SaleDate] field on the one row. I would also like to include the store location field [Location] along with the date the sale took place. So in other words the extract would look something like:

[CustomerID], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], [SaleDate], [Location], etc etc

Obviously some customers will have had less sales than others in which case I’m assuming these fields would just contain NULL values.

View 4 Replies View Related

SQL Server 2008 :: Query Pulling Too Many Records

Feb 11, 2015

My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.

Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: How To Get Latest Records From Table

Mar 17, 2015

I have a table where i am inserting into temp table, I mean selecting the records from existing table. From this how can i get latest records.

create table studentmarks
(
id int,
name varchar(20),
marks int
)
Insert into dbo.studentmarks values(1,'sha',20);

[Code] ....

How to write a sql query to get the below output

studentname totalmarks

sha 90
hu 120

View 1 Replies View Related

SQL Server 2008 :: Selecting Records Not Between 2 Dates

Jul 22, 2015

I’m trying to extract a list of records that don’t appear between 2 dates. I have 4 tables:

tblCustomers
tblMachines
tblServiceOrders
tblMachinesServiced

tblCustomers contains a CustomerID that is unique to each customer.

tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.

tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.

tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.

What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.

For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.

I’ve tried several different queries but here is an example:

SELECT tblMachines.MachineID,ServiceMachines.ServiceDate
FROM tblMachines
LEFT JOIN
(SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced
WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo
) ServicedMachines
ON tblMachines.MachineID=ServicedMachines.MachineID
WHERE YEAR(ServiceDate) != '2014'

I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.

View 9 Replies View Related

SQL Server 2008 :: Grouping By Records That Are Already Grouped?

Jul 27, 2015

I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...

id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed

Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.

id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed

View 1 Replies View Related

SQL Server 2008 :: Only Show Duplicated Records

Aug 4, 2015

I have a query below to show all the records with joining these two tables.

SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD

My goal is only to show all the duplicate records.

Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)

[code]...

View 9 Replies View Related

SQL Server 2008 :: Records That Don't Match From Two Database

Sep 24, 2015

I am using the following views from two db's to find records that don't match.

My question is can I have output of fields from the second table

SELECT distinct T1.[last name],t1.[first name],ENum
FROM ECLINICIAN_Info T1
WHERE NOT EXISTS(SELECT *
FROM ACLINICIAN_Info T2
WHERE t1.Enum = t2.Anum
and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)

I would like to include t2.Anum in my select ...

View 5 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Transact SQL :: Records Returned To Same Status

Jul 23, 2015

In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.

View 9 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

SQL Server 2008 :: Data Fetching 80 Million Records?

Mar 24, 2015

i have table below

CREATE TABLE [dbo].[DR_Test](
[source_item_id] [int] NOT NULL,
[source_line_no] [int] NULL,
[buyer_id] [int] NOT NULL,
[seller_member_id] [int] NULL,

[code]...

the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique.how to fetch the data fast or need to change the structure of the table

View 3 Replies View Related

SQL Server 2008 :: Where Clause - Look For 5 Records Data In A Table

Apr 14, 2015

I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')

but if i use the query below it will generate more records than these 5 records

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')

View 4 Replies View Related

SQL Server 2008 :: Job To Delete All Records Then Copy All From Another Table?

Apr 22, 2015

I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?

USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END

View 4 Replies View Related

SQL Server 2008 :: Run Query To Retrieve 650 Unique Records

May 14, 2015

I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved