SQL Server 2012 :: Query If Extract Failed And / Or Succeed The Same Day
Nov 19, 2014
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime
Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.
Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.
USE [SSISDB] GO ALTER PROCEDURE [dbo].[spGetIspacFile] @project VARCHAR(255) , @environmentFolder VARCHAR(50) , @ispacTempFolder VARCHAR(100) , @ispacFilePath VARCHAR(200) OUTPUT
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom ( PatientID INT NOT NULL, Cycle TINYINT NOT NULL, SymptomName VARCHAR(20) NOT NULL, -- from the source column *name* Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int; DECLARE @object_id int; SET @db_id = DB_ID(N'SCRIDB'); SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox'); SELECT name AS column_name , column_id AS col_order FROM sys.all_columns WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply] AS SELECT PatientID , Toxicity , MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT ES.login_name AS LoginName, L.request_session_id AS BlockedBy_SPID, DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec, DB_NAME(L.resource_database_id) AS DatabaseName,
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account], sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy], [DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
Hello,I have a SQL Server Agent job that has two steps. The job fails when I run it normally, with both steps. However, when I tried to troubleshoot, I ran each step individually, and both succeeded without errors. The first step runs an SSIS package under a proxy. The second is a T-SQL statement that simply runs a stored procedure. (There is no option for "Run As" for the T-SQL statement.) Again, each step runs fine when I run it under SQL Server Agent; also, the SSIS package runs fine when I run it from the file system, and the T-SQL statement completes successfully when I run it as a query.Does anyone have an idea why this is happening? Thanks!
I have added an email task to the ON Error Event of my SSIS package, so that I will always know when there are errors. However I would like the SQL Server job executing the package to succeed even if the package fails. What setting do I change in the SSIS packageto achieve this? MaximumErrorCount?
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Enabled DAC and when connecting, I get a "Failed to connect to server ADMIN:SERVERINSTANCE"; however query window shows I'm connected on ADMIN:SERVERINSTANCE and checking tcp_endpoints + dm_exec_sessions, I'm definitely on the DAC.
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl] ( [ID] NUMERIC(18,0) NOT NULL, [Name] VARCHAR(500), [PartStructureKey] NUMERIC(18,0) NOT NULL, [PartNumber] VARCHAR(500) NOT NULL, [ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
I am attempting to create a new trace but I get the following error message: "failed to start a new trace".
I have been doing some digging and as I understand it, I had to find the directory Profiler uses for temporary files. So, I typed the following in the command window "SET TMP" and I received the following reply:
C:UsersRossAppDataLocalTemp
Now, according to the forum: [URL] ...
I am supposed to check that the system folder pointed to by the TMP environment variable exists and is not crammed with files.
Well, when I went to the directory C:UsersRossAppDataLocalTemp, it is indeed full of both files and directories. The size is 16.3 MB and has 133 files and 63 folders.
When I had a look at the Environment Variables window and chose TMP the value is "%USERPROFILE%AppDataLocalTemp" which according to my limited understanding is the equivalent to C:UsersRossAppDataLocalTemp.
So, what I am wondering is am I supposed to totally clear out this directory? I am not too keen on doing this because I don't want to stuff my PC up.
I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.
<code> DECLARE @tablename AS nvarchar(max) DECLARE @SQLQuery AS NVARCHAR(MAX) DECLARE @ParameterDefinition AS NVARCHAR(100) DECLARE @startdate datetime
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
I've SQL statement that fail when I execute it on the server. I get the below message:
Msg 50000, Level 18, State 1, Procedure USP_GTL_Transform, Line 226 A transformation of an important table failed
When I execute the sql statement in the SSIS Control flow 'Execute SQL Task" the task succeed and paint in green. When I check in db log its still failing but only presented succeeded in the SSIS
Why the task didnt fail? how can i make it fail and paint in red?
First I recieve this error when I upload a photo and click on the save button. 'ODBC - update on a linked table 'PersonMisc' failed.'
Then this error appears after I click ok.
[Microsoft][ODBC SQL Server Driver][SQL Server] The READTEXT and WRITETEXT statements cannot be used with views[#285][Microsoft][ODBC SQL Server Driver] Warning: Partial insert/update The insert/update of a text or image did not succeed.[#0].
Is there a size limit on the pictures I am uploading?
Hi i have a query select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual, 12*year(CURRENT DATE) + month(CURRENT DATE) as ending, 12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting from "SQLUSER"."BTT120" where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')
which gives me ourput
Actual ending Starting 24094 24097 24094
My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.
SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR FROM "SQLUSER"."BTT120" where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between (12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and (12*year(CURRENT DATE)) + month(CURRENT DATE)
everything looks good but iam not getting output, there is data for the date range
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
I'm having a strange problem with SSRS 2005 that I'm hoping someone might be able to help with. I've reinstalled SSRS (multiple times) and even reinstalled IIS hoping to resolve the problem, but it still exists.
The problem is that trying to view a report via Report Manager is failing. All other operations within Report Manager work fine (browsing folders and report lists, viewing folder and report properties, changing properties, setting permissions, etc.) Only trying to view reports fails. Whenever I try to view a report, I get the following error:
The request failed with HTTP status 401: Access Denied.
I can view reports via other methods just fine. For example, if I browse to the http://localhost/reportserver URL directly, I can view any report just fine. And if I connect to SSRS via SSMS, I can view reports there as well. I believe that both of those methods use a direct reportserver URL versus using the http://localhost/Reports/Pages/Report.aspx page within the Report Manager virtual directory.
Here's a bit of configuration information that may or may not be helpful to someone:
1. This is on a laptop running Windows XP SP2 (32-bit). SSRS (and all SQL Server components) are installed as default instances and SQL Server SP2 has been applied to everything.
2. All of the latest updates and fixes for Windows, SQL Server, Office, etc. have been installed via Windows Update.
3. This laptop is a member of a domain, but I am typically not connected to the domain (not in the office very often). I am, however, logging into the laptop using a domain account (with cached credentials). Local user accounts are frowned on by the domain and IT administrators involved (except for running local services). My domain account has full admin permissions within Windows and within SQL everywhere.
4. As far as I can tell, everything with regards to IIS, asp.net, and SSRS are configured correctly (ASPNET account seems to be set up correctly, all SQL services including the SSRS service are running under a local account with admin permissions, the Reports and ReportServer virtual directories in IIS are set to use Integrated Security, etc.)
I'm not an expert on SSRS, so this one has me stumped. I've reviewed all of the config files that I can think of looking at for SSRS and I've looked at the SSRS logs. Nothing really jumps out at me in terms of changes that might be required (and the SSRS logs don't really say much other than repeating the error). For example, I see the following lines in the ReportServerWebApp__xxx.log file:
aspnet_wp!ui!8!5/14/2008-13:00:17:: e ERROR: The request failed with HTTP status 401: Access Denied. aspnet_wp!ui!8!5/14/2008-13:00:17:: e ERROR: HTTP status code --> 500 -------Details-------- System.Net.WebException: The request failed with HTTP status 401: Access Denied.
Anyone have any ideas? I've searched Google for quite a while trying to find something that would help me figure this out, but thus far I've not been able to come up with anything. Any help (or pointers to something on teh web somewhere that I might have missed) would be appreciated!!
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120)) insert into #temptable SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10 --column name declare @cname varchar(30)
Hi, I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query: select * from table1 tb1, table2 tb2 where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode 1 999 Barry White BW13 8GS 2 999 <null> <null> BW13 9GS 3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode 999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
I am having trouble coming up with a query to extract the min and max time for each Task.. I am using Teradata SQL. The data below shows two tasks which both have the same File and Task ID. Each task starts with a Submitted Status and ends with a Rejected Status. What I need is to get the min and max time for each one of those tasks.. Unfortuneately my data below consists of values that don't have a unique identifier that I can use to split the tasks.
Hi , is it possible extract file name from script executed in query analyser ?
Example steps
1. open QA 2. open c:mySuperScript.sql code in c:mySuperScript.sql use master select @filename as [file executed] select count(*) as xfrom syslogins 3. run
-- result file executed ------------ c:mySuperScript.sql
x -- 112
Why we need it. Each database update created using sql compare tool from red-gate Script saved in Vss When script executed in production using QA we save output as proof of deployment currently we add to each script
select 'script= put you file name'+ char(13)+ 'server = ' + cast(@@servername as char(20))+ char(13)+ 'dbname= ' + cast(DB_NAME()as char(20)) + char(13) + 'execution date= '+ cast(getdate()as char(24))+ char(13)+ 'executed by = '+ system_user
We want wrap everything in procedure that will extract executed file name and other values
this procedure should be first line of code in each script
I am getting a really weird error message when executing a full-text query on SQL server 2005:
------------------------- Microsoft OLE DB Provider for SQL Server error '80040e14' The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider." -------------------------
Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.
The query is not particular complex:
SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID
It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.
Has anybody come across this error before? Any ideas on how I could fix it?
Below is what I have so far Also I am getting other FRID besides 0 as well.
Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID From Parts As P Right Join Vend2Part As V On P.PartID = V.PartID And P.FRID = V.FRID Left Join companies As C
