T-SQL (SS2K8) :: Agent Job Report Omit Previously Sent Data
Nov 27, 2014
I have this report i setup as an agent job in SSMS; It's setup to run every 7 minutes and it only sends the report if data is present. I'd like to add the ability to omit any rows that were sent in the previous report.
This is what the script looks like:
if exists (select o.ord_billto, o.ord_refnum , o.ord_hdrnumber, o.mov_number, o.ord_status, o.ord_cmdvalue, o.ord_startdate
from orderheader o
where ord_billto in ('A','B','C','D')
and DATEDIFF(minute , o.ord_datetaken, GETDATE())<=7
[Code] ....
Also, why I can't seem to use IF ( Select [...]) > 0? When i try using that instead of IF EXISTS i get this error: "Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
I'm running into the following message, "String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)" when running a sql agent job. I'm attempting to execute a stored procedure through the job. Keep in mind that when I run the stored procedure in a normal query window, it works fine and only fails when running it as a scheduled job. My guess is that it has to do with how SQL Jobs execute procedures (especially long procedures). If I use Set Ansi_Warnings OFF, the job will work fine, however, I don't know what other issues this may cause.
Where it's select in red I want to be able to look in another table with a listing of email domains (@express.com,@express.net,@example.org), and when the are query that I don't view them. I don't know if I need some kind of varible or just lookup in another table. I have windows 2000, and same for SQL. I am trying to write it using SQL Query Analyzer.
Thank You,
Ernie
SELECT FirstName, LastName, Street, city, State, zip, Country, Providence, TypeOfAccount, RemoteComputerName, Timestamp1, PresentEmail, NewEmail, Password, Age, Account_Active FROM dbo.newusers WHERE (FirstName <> ' NULL') AND (LastName <> ' NULL') AND (PresentEmail <> ' LOOKUP in another table') AND (NewEmail <> ' NULL') AND (Password <> ' NULL') AND (Age <> ' NULL') AND (Account_Active <> 'yes') AND (Account_Active <> 'reject') AND (Account_Active <> 'spammer')
how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail report when the run duration exceeds 30 minutes.
The agent job in question kicks off our ETL, and it runs every hour. It's happened before where a job running elsewhere(Not best practice, but we run reports from our datawarehouse) creates a deadlock on 1 of the tables being updated in the ETL, but i only get notified when i get calls from end users saying their reports aren't returning results.
I was thinking of creating a new job that query either the sysjobhistory or sysjobactivty table in msdb, but i would need it to refer to the run duration value of the job as it's still running( I think i can assume it's not updating that value every second...)
Hi Neil, thanks for you advice. The reason I asked the question is because in SQL2000, there was an option which would allow you to export to any file type (including excel). This was done by using the DTS Import/Export interface, does this function exisit in SQL2005Expres? Surely?
It's been a while since I last posted on here, about 4 years maybe more. Anyway I'm happy to be back. I have an existing query that I needed to add two columns to. I did that but now my dilemma is to have the query NOT return NULL values in a particular column (pd.premium_amount). I tried adding WHERE pd.premium_amount IS NOT NULL and that did not do the trick. I was thinking about trying to use CASE statement but didn't really see that as being an option. Below is my code.
USE [AmLink_DW] DECLARE @Bil_Grps TABLE (bil_grp_id int, bil_grp_sname varchar(50)) INSERT INTO @Bil_Grps (bil_grp_id, bil_grp_sname) SELECT * FROM ( VALUES
Hi,I am newbie to SQL Server. I am using SQL Server 2000.I know SQL server compiles the SQL stmt or SP and stores the plan forlater use(I know its not always done. But should be done to reduceexecution time) in memory somewere. I want to capute all the executionplan which are kept in memory. In oracle v$sql_plan can be used toaccess the plan in oracle. I want to know how to do that in SQL Server.I can use profiler, but it contributes some CPU utilization.Thanks,Thiru.WantedToBeDBAWantedToBeDBA {at} gmail {dot} com
Hi guys, please help.What I did is :Step1. create a Database named [Test], it's default owner is [sa]Step2. create a User in Database [Test] named [kimliuTest], which mapsto the Login [kkhadkimliu] on this Database Server, it's a windowsdomain user.Step3. create a Schema in Database [Test] named [schTest], whose SchemaOwner is User [kimliuTest]Step4. created two tables: [dbo].[t1] and [schTest].[t2]Step5. run the scriptALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTestto set the default schema.Now my problem is, when I try to runselect * from schTest.t2, everything is OK, but if I runselect * from t2the error message says:Msg 208, Level 16, State 1, Line 1Invalid object name 't2'.why? I'm loggin in with my [kkhadkimliu] windows domain id , ActiveMonitor also shows that. but the command does not work?Yeah, it's a good habit to write the full name of the Table with Schemaname as prefix, however, we did our development in SQL 2000 and tryingto upgrade to SQL2005, so trying to avoid scanning codes and insertingin thousand of places.How could we omit the Schema name when referrring to Tables? Thanks.ps. I posted in another topic days ago, but nobody is replying, when Itried to bring it forward by replying myself, it failed. please help me, thanks!
I got a mdf file from my co-workerwho just stopped SQL server and delete ldf fileand give me a mdf file.if it was detached, it would work finebut it was just copied.Is there any way to attach this mdf file?I tried sp_attach_db and sp_attach_single_file_dband both failed.thanks,
I have a report parameter textbox in my SSRS report which has the parameter properties as "allow null value" and "allow blank value" checked.
How I can ensure that when the value is entered in textbox, it should accept any character ie. The SQL code I should write so that all the characters inputted via the report parameter are accepted.
If you are having problems installing SQL Server, including Express Edition, and you previously installed one of the Community Technology Preview (CTP) releases, then read on...
Hi All, we are changing some reports from English to German...some of the reports that we have changed generate the message 'An internal error occurred on the report server. See the error log for more details.'
Dear group,I am a DB-Administrator for a Company that creates and maintains asoftware solution based on SQL-Server and Crystal-Reports.The Crystal-Reports part uses the following method to present data.First a specific user (database authentification) is created with afixed password. Then Crystal Reports uses thisuser/password-information to login to the database and present or printthe data.I recently received a database from a customer of ours and i restoredit on my machine. It worked perfectly except that the Crystal Reportspart did not function at all (showing only blank reports).After dropping the specific user and creating the fixed password thething worked.So my question: does restoring a SQL-Server database on a differentmachine alter or eliminate login and user information ?Any reply will be highly appreciated.Greetings from ViennaUli
I'm using some xslt documents to transform the xml output of my Reports but have come across two curiosities where the xslt filter seems to behave unusually.
Firstly, I need the final saved file to have an xml declaration, which I believe it should do by default. Even if I put omit-xml-declaration="no" in the xsl:output tag I don't get an xml declaration. At present we have a custom job that writes these declarations back into the xml after SRS has saved it.
Secondly and more importantly, I need to have some of my output tags wrapped in CDATA sections. I've tried using the cdata-section-elements attribute, again with no luck.
my XSLT looks something like this (simplified for space)
I am creating a report where there are 3 tables in one report which have visibility set as an expression so that when a certain criteria is selected in the "Report" parameter, a different table is shown below. Â This works as expected.
When table 3 is shown, I want a new parameter to be visible to the user called "Ward" where the user will select the ward they are interested in and the table will display data below just for the ward selected.  However, I don't want the ward parameter visible when either of the other 2 options are selected. Is this possible and how to do this?Â
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb; GO DECLARE @Date datetime; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE
I've a SSRS monthly sales report with the sales details for current year and last year. I've 4 paramaters in the report. StartDate1, EndDate1, StartDate2 (hidden), EndDate2 (hidden).
For
StartDate1 - Beginning of Last month - DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 1 2015)
EndDate1 - End of Last month - DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 28 2015)
I have an SSRS Report that I created. I'd like to set up a job that will run every hour, and what the job will do is send an SSRS report to a select group of recipients when a certain criteria is met. How do I go about doing this? My SSRS knowledge is very limited.
Additional Info:I created the report with a one time schedule to a certain email address. I do see the report schedule in the reportserver DB. Because I already have the schedule and the subscription id, I think all I have to do is call that subscription, but that's where I'm lost.
I am having a situation where my SSRS report when exported to PDF shows the exact report & when exported to MS Word, shows some unwanted space in the report column. Also the borders of the report are shown cut when exported.
finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.
Example
SELECT Title from H..Import_Table returns 'PRN - Concord' 'PRN - San Jose' 'SLP - PRN - San Jose' 'San Jose - PT - PRN'
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
When running the snapshot agent for a new publication I get the message:
Message: Query for data failed Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint) at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile) at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider) at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem) at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc() at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc() at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0) Get help: http://help/0 Message: COLV_90_TO_80: Cannot convert parameter param1: Resulting colv would have too many entries. Stack: (Source: MSSQLServer, Error number: 20005) Get help: http://help/20005 Message: The statement has been terminated. Stack: (Source: MSSQLServer, Error number: 3621)
The database has another publication, that was created prior to upgrade to sql server 2005 from sql server 2000 sp4. This publication is working ok, and there are no problems when running the snapshot agent for this publication.
The publications are using filtered articles, and all properties for the publications are the same.
Any help to solve this problem will be much apreciated.
We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:
Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"> Â Â Â Â <OverrideNames> Â Â Â Â Â <Name Language="en-US">CSV (comma delimited)</Name> Â Â Â Â </OverrideNames> Â Â Â </Extension> Â Â Â <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"> Â Â Â Â <OverrideNames> Â Â Â Â Â <Name Language="en-US">Excel (csv)</Name> Â Â Â Â </OverrideNames> Â Â Â </Extension>
Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.
I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports generate dynamically. Since the report project doesn't exist anymore, I can't set the data source through code. How can I set the data source for the whole folder?
I have a problem using Report Viewer With SQL Reporting Services 2005.
I had a really weird issue with a ReportViewer. It would show the little green Loading progress bar image €¦ but it wouldn€™t show any data, just a white background where the output should be. I knew the data was there because exporting to Excel or PDF showed the data just fine.
The problem is report toolbar is display but NO DATA is displayed and the report comes out as EMPTY .
Thanks in advance..
Can you please help me out in resolving this problem.
I have a SSRS report with 4 sub reports embedded. Previously i faced a situation where sub reports was getting displayed with static data even if there is no data being pulled from data set. I solved this problem according to solution provided in my previous post here. Now blank space is getting displayed in place of empty sub report. But still the main report that hosts empty sub report (Which has a header with header name coming from main data set) is getting displayed. Is there any way wherein the whole main report can be suppressed when the sub report  has no data?
I am designing a report which will be used under the subreport control of a master report, now i am using a stored procedure for the subreport which has two parameters.
In the subreports dataset parameters tab, how can i mention the master reports columns, since i do not see the master reports columns here. since it is a different report.
If i use a hardcoded select query then i can just say
select address, city, state, zipcode from customer where (customerid = @customerID)
but i am using a stored proc. how to define the parameters under datasets parameter tab.
