Checking Data And Emailing The Results....

Jun 28, 2006

Hello,
 
I am trying to use €œSQL Server Business Intelligence Development Studio€? (what is the short name for this) to complete a check and then possibly email me depending on what happens.
 
The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.
 
Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,
 
Thanks,

View 5 Replies


ADVERTISEMENT

Emailing Query Results

Oct 21, 2004

Is there a way to have SQL server email the results of a Select query to someone?

View 1 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Data Checking?

Jul 23, 2005

Hey all,prolly a simple solution, but why isn't the following string working inmy execute sql step within DTS? It produces results, just not the onesI want... What am I doing wrong?select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'It's displaying all the records? It should only be displaying thoserecords that do *not* contain letters or numbers.Thanks in advance!-Roy

View 3 Replies View Related

Checking For No Data To Display

Oct 30, 2007

I am using a filter in a matrix control. I would like to display a "No data" type message if no data is present. The problem I'm running into is the base query is returning data, but the optional filter is removing all rows from display.

So using CountRows in the base dataset doesn't quite cut it. Is there an alternative?

View 4 Replies View Related

Copy Data To SQL Server By Checking (available)

Oct 1, 2001

Hello,

I connect from SQL Server on Windows 2000 to Progress
Database on UNIX.
The database name of SQL Server is cstarsql and the
name is cstarint on UNIX.

I would like to schedule to copy data from
cstarint(Progress) to cstarsql(SQLServer). I did for
one time, but I want to control if the data has
already copied or not. If not, it will copy.

In DTS Query Builder to copy from Progress to SQL
Server,
SELECT * from calls WHERE call_date = TODAY
The name of table of calls is the same for both
database.The above calls is cstarint(Progress
Database)
This command is enough for one time. But I need to
control if the record has already copied.


In SQL Server, I control if the record is available or
not with @@FETCH_STATUS .
Now I would like to mix two queries, but I couldn't.
Can anybody do this?

DECLARE calls_cursor SCROLL CURSOR
FOR SELECT * FROM calls
WHERE call_date = TODAY

OPEN calls_cursor

-- Perform the first fetch.
FETCH NEXT FROM calls_cursor

-- Check @@FETCH_STATUS to see if there are any more
rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch
succeeds.
FETCH NEXT FROM calls_cursor
END

CLOSE calls_cursor
DEALLOCATE calls_cursor

Alice

View 1 Replies View Related

Checking Out Data To Client Users

Sep 29, 2006

I'm trying to resolve an issue that I've run into in my current system.

I have about 10 clients accessing a SQL Server several times per minute (every 10-20 seconds). To have an individual find the next record, I follow the following process:

1. Select the value of the next record in the database to be checked out.
2. Update the record to show that it is checked out to the user.
3. Select the data in the record to display to the user.
3. Update the record to show any changes and to check the record back in after the user edits it.

My issue is that clients can execute at the same time. Right now, with just SQL statements, two clients can get the same value in step #1. That makes them select the same record for editing. Can I use T-SQL to prevent this from happening? If I use a transaction, will the SQL Server 2005 queue up the transactions, or could I still get the same problem of opening up the same record?

Thanks!
Drew

View 3 Replies View Related

StoredProc For Checking For Duplicates And Then Updating The Data.

Mar 22, 2008

Hi

I have a question i hope someone here will solve my problem.

I need a storedproc for checking the duplicates before updating the data.

here i need to pass four parameters to storedprocedure which must be updated with existing row.

but before updating the values. Storedproc should check for 2 colum values in all rows.

if same combination of colum values is present in any other row then the present rows which we tring to update should not be

updated. Can anyone help me with this.

Thankyou verymuch.

View 8 Replies View Related

Emailing From MS SQL

Mar 14, 2004

Hey,

I would like to set up a job to send mail out to clients on a semi-regular basis. Is there a way to mail directly from MS SQL.

or some application which I can set to interface with MS SQL and send these mails out.

I'm currently using asp.net, however, doing it through the web is very time consuming, I would much rather use some application to generate the mails on the server as it currently takes close to 20 min to send through the web interface (and that's only to 900 members, we are looking at having at least 2000 members in a few weeks). What am I looking for is something I can call perhaps with transact sql so I can run it through an ms sql server job, however any solution you have would be fantastic.

If you don't have specific details, that's fine, I'm not even sure what apps etc to look into, even a name of some technology to use (or does MS SQL have a way of doing all of this itself?)

Thanks a lot for your help, life saver ;-)

View 8 Replies View Related

Possible To Disable Welll-formedness Checking When Inserting XML Data.

Mar 20, 2006

Hi All,Can anyone help with the following problem? I have a database whichcontains a table with a 'text' field, and the text field contains anxml document - typically 50-100K. Now I'd like to make use of SQLServer 2005s XMLData type. To do this I have created a new field ofthe 'xmldata' datatype, and run an SQL statement to update the contentsfrom one field to another - hoping to end up with a complete table ofxml (based on the old text field).The problem I have is after a minute or so, it must come across anbadly-formed xml fragment because I get the following message:Msg 9436, Level 16, State 1, Line 1XML parsing: line 1, character 67640, end tag does not match start tagCan I turn off the checking during the update, or is it not possible toadd badly formed data to the xmldata field. Any help appreciated asthe table runs into tens of thousands of rows, so I can't really checkthe contents of each!Many thanks,Duncan.

View 1 Replies View Related

Emailing The Outputs

Apr 13, 2000

I need to send the output of a report generated, automatically through email as an attachment to some customer.Is it possible?Can anyone help?

View 1 Replies View Related

Emailing From SQL Server

Dec 8, 2004

Hi everyone,

I am running SQL Server Enterprise Manager and i would like to have a query run everynite and send the results returned by the query off to an email address....

How can i do this?

View 3 Replies View Related

Emailing Reports

Nov 5, 2004

Hi everyone I have a database that I am creating for another department. He emails applications to potential vendors. And I was wanting sql to do this for him. I was reading about xtended procedures XP_Sendmail, but this is the first time I have created an extended stored procedure. I need for it to email an attachment (the report)

View 1 Replies View Related

Emailing An Attachment From DTS

Feb 7, 2006

I have a DTS package that runs 4x a day and generates an excel spreadsheet, renames that spreadsheet with a datetime stamp and then places it into a folder on our network. I have been asked to email that spreadsheet to someone everytime the package runs.

My question is what would be the best way to handle this and how do I ensure that whatever process I define grabs the correct file? The folder that the Excel file is being placed into has multiple files in it. Is there a way to tell SQL Server which file to grab and email?

Any thoughts or suggestions would be greatly appreciated! Thanks!



Frank

View 1 Replies View Related

Emailing Errors

Feb 19, 2007

I'm sort of new to SSIS so I apologize if this is a trivial question:

I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.

But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?

View 4 Replies View Related

Emailing Report

Apr 5, 2007

I need to export a view into excel and mail to my manager daily morning. Is there a way i can automate this process. I know how to convert view to excel but i need a better solution to automate this process with emailing to my mgr. Please suggest a better approach of doing this.



Thanks,

View 5 Replies View Related

SQL Server 2014 :: Best Design For Multiple Validation Checking Of Data

Sep 17, 2015

I want to do something with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.

To deal with this I'm currently thinking of this approach:

1. Create a view pulling all of the various data together from the multiple tables.
2. Create an empty 'errors' data table.
3. Create an Excel file with a button to call a Check for Errors Script

Then in the the script:

1. Clear the 'errors' data table
2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes).
3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.

Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.

View 5 Replies View Related

Emailing From Stored Procedure

Jan 5, 2001

Whenever a certain stored procedure executes I need to email both admin and clients (different messages). I have created an error within the SP and can RAISE the error and thus notify admin staff but how can i email the client also ? I can obtain the cient email address from thye system but how can i email them ? Can i use xp_sendmail within a stored procedure ? If so, how as i can't seem to get it working ?

thanks for help

View 1 Replies View Related

Auto Emailing From With In SQL2000

Oct 20, 2002

Hi there!
Is there a way, I can setup create a script that I can schedule, which runs through my user databases and if e.g.:
1:) there is less than 20% free space it emails me.
and/or
2:) there is less than 200MB free space
it emails me.

Cheers
Henrik Hansen

View 6 Replies View Related

Emailing After A Record Has Been Added

Jan 8, 2008

Hi,
Is there a built in capability with sql server 2005 which sends emails to users upon a record insertion.
Thanks

View 2 Replies View Related

Emailing An Excel File

Aug 31, 2007



Hi ,


Is it possible for us to send an excel file generated in the package to the users using Sendmail task or anyother task using SSIS?

Any help is greatly appreciated.

Thanks,
SVGP.

View 5 Replies View Related

Emailing Long Messages From SQL2000

Oct 26, 2006

Hello!
The problem is this.

I have many short messages in a table. I need to tigh them together in one long email message and email to the users.
But xp_sendmail is limited to 7,790.
How could I send longer messages? Or how could I devide the long message and send it in parts (i.e. separate consequtive emails)?

This doesn't work, it still cuts the messages off at around 7,790:
E. Send messages longer than 7,990 characters

This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @query parameter. 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 7 Replies View Related

Continuation Of Emailing An Excel File

Sep 12, 2007

Hi Rafael,

I need to create a new excel file daily and then need to mail it.
The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.
Any idea why this is happening?So iam unable to schedule as a job...

Thanks,
Vani.

View 3 Replies View Related

Automatically Running Reports Then Emailing Out

Feb 4, 2008



Hi All

I'm hoping someone here will be able to guide me in the right direction. I am trying to figure out a way of looping through table columns or a flat file in which there will be three parameters every time. With these parameters I am then wanting to run a report using two of these parameters to feed into the report and then email this out to an email address which would be my third parameter. Does anyone here have any idea how I would go about doing this?

Many thanks in advance.

View 5 Replies View Related

SQL 2012 :: Scheduling And Emailing Custom Reports?

Jun 19, 2014

I have downloaded MS's performance dashboard report and installed. I wanted these report should automatically send the performance report to my email.

View 1 Replies View Related

SQL Server 2008 :: Custom Alert Is Not Emailing Operator?

Apr 8, 2015

I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.

1) I created an operator - me.

2) I created a job which runs a query.

INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC

3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.

4) I've set the alert to email and delay is between responses 2 minutes.

It history tables says number of occurences is 18964. However, I don't receive an email.

Shouldn't the alert send an email? Do I need to include email code in the job?

View 6 Replies View Related

How To Send Report Through Email One Time I.e. Without Subscription For Recurrent Emailing?

Jul 10, 2007



I want to send a deployed report (report manager), through email, by calling reportingServices webservice. But the requirement is that, time is not fixed for sending this email, so I want to trigger the sending of report as a one time event, no subscription to a daily time...

How should I go about it?

View 1 Replies View Related

Data Results To Variables

Feb 28, 2007

Hello, 
I am using VWD, SQL EE, VB
I have a select statement with a varaiable in it (as below). Rather than displaying the results directly in a DetailsView I want to add the results to a set of variables (Dim XXX as String etc) so I can check to see if each variable contains data or not, before then displaying only those lines which contain data.  Can anyone suggest how I go about linking each data field to a variable?
 
 SELECT     PID, Postcode, Address1, Address2, Address3, Address4, Address5, Town_City, County, Postcode AS Expr1FROM         postcodeWHERE     (Postcode = @Postcode) 
 
cheers
marco gwiliani

View 2 Replies View Related

Multiple Rows Of Data Into 1 Row Results?

Dec 10, 2003

Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.

I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).

Thanks!

View 5 Replies View Related

Preview Data, It Gives You Incorrect Results

Apr 25, 2007

I am reposting this to a different forum, becasue I got not response...
Using VS05 SP1 Pro SQL Express€¦
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob

View 3 Replies View Related

Do Data Mining Results Store At Some Where?

May 9, 2006

Do data mining results store at some files or in the database? I need the mining results at later time for reporting purpose.

View 1 Replies View Related

Can The Data Results Screen Be Hidden?

Jul 20, 2007

Can the results screen on the data tab be permanently hidden?



Thanks

BobP

View 2 Replies View Related

Transact SQL :: Emailing Session Events For Extended Events

Feb 3, 2014

I am using this code for LongRunning Queries.

CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000

[Code] ...

Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?

View 2 Replies View Related







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