SQL Server 2012 :: How To Find Out If Double Loaded A File Programmatically
Apr 24, 2015
I was just doing a table import task (right click database name/Tasks/Import Data), not knowing my boss had just loaded the same file. it did not warn me that the table currently existed. It just appended the same information to the same table, doubling it. I fixed that one, but, it seems that I might have done this myself in the last couple of weeks, and I'd like to find that table, and there have been a LOT of table loads.
I'm thinking I could get the difference between tables by comparing:
select distinct count(*) from tblname
against
select count(*) from tblname
But how do I incorporate this into some sort of proc that will go through all the tables and let me know where the issue is? I'm swamped and don't have the time to go through each table manually.
I have code that shows me row counts, and have been able to eliminate a few tables from contention, as they are loading monthly data that should only increase minorly month to month, so, no double jumps there.
View 8 Replies
ADVERTISEMENT
Sep 11, 2000
Hi,
I'm using SQL Server 7.0. I have a table which has had some records double loaded into it and some triple loaded. I need to delete all instances of a record except for 1. The records are identical with no unique identifier. Here is what I'm talking about.
Currently in the table is:
ID DATE STATUS
1 07/07/2000 D
1 07/07/2000 D
1 07/07/2000 D
2 07/07/2000 A
2 07/07/2000 A
What I need in the table is:
ID DATE STATUS
1 07/07/2000 D
2 07/07/2000 A
Does anyone know how I might be able to do this?
Thanks in advance,
Darrin
View 2 Replies
View Related
Apr 13, 2015
I have a query question.
Consider a table with the following structure:
RecordID (PK - int) - RecordDate (DateTime)
I need to find all records that fall within a 7 day period slot based on the first RecordDate of a specific slot.
Example, consider the following records:
RecordID - RecordDate
1 - 2015-04-01 14:00
2 - 2015-04-03 15:00
3 - 2015-04-03 16:05
4 - 2015-04-03 19:23
5 - 2015-04-06 09:15
6 - 2015-04-06 11:30
7 - 2015-04-07 12:00
8 - 2015-04-09 15:15
The result of the query I'd like should look something like this
1
2
5
7
8
So basically I'd like to leave record 3 and 4 out because they fall within 24 hours of record 2 and I'd like to leave record 6 out because it falls within 24 hours of record 5.I'd tried working with a CTE and set a dateadd(d, 1, recorddate), join it on itself and use a between From / To filter on the join but that didn't work. I don't think NTILE will work with this?
View 9 Replies
View Related
Jul 23, 2005
I'm doing some performance reviews and wish to know what tables SQL haspinned in memory and which ones have are loaded through usage ...Is there a way ?Thanks,Craig
View 3 Replies
View Related
Apr 14, 2014
I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
[code]....
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.
View 4 Replies
View Related
Feb 9, 2014
I have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
I want to get only data in double codes in using sql query.
View 9 Replies
View Related
May 19, 2011
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
SELECT CAST(1.0/7.0 AS DOUBLE)
gives "Incorrect syntax near )"
SELECT CONVERT(DOUBLE, 1.0/7.0)
gives "Incorrect syntax near ,"
View 2 Replies
View Related
Jun 29, 2007
Hi,
I have created a package and when i was trying to configure a flat file destination, i am getting the following error:
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.
===================================
Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
Please advise.
Thanks & Regards,
Deepak
View 1 Replies
View Related
Mar 2, 2007
Hi,
i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got
Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.
how can i rectify this error? or how can i ignore the identity column in coding.
thanks & regards,
sivani
View 6 Replies
View Related
Sep 14, 2015
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select *
from myTable
Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
ex: MySentencecontains"FSC"
I have tried Replace with no luck.
View 3 Replies
View Related
Jun 1, 2015
while working on deployment to production server of an SSIS project, I have noticed that development priject has "disappeared" from my development machine which, obviously, was not my intention, at all. I now get the error prompt saying this:
what am I suppose to do in order to RESTORE it on devel machine like it used to be?
View 7 Replies
View Related
Jul 20, 2005
Hi,I'm on SQL Server 2000, say, I have a table named [orders], how I findif there is any column which has a formula in it? In other words, howto identify formula column programmatically? I've looked atinformation_schema.columns view for clue but to no avail.Thanks.
View 2 Replies
View Related
Oct 15, 2007
Hi,
I want to generate a new snapshot using stored procedures. I want to wait for the snapshot files to be created and then execute a stored procedure. What's the best way to determine that the snapshot has completed successfully? I thought of doing something like:
exec msdb.dbo.sp_help_job
@job_name = @job_name
@job_aspect = 'job',
@execution_status = 1
however I can't put the results of that proc into a temp table because I get this error:
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
Any ideas? I'd like a T-SQL solution.
Thanks,
Mark.
View 3 Replies
View Related
Oct 16, 2015
In my SSIS package I have flat files as a source, I have to load numbers of flat files into SQL target table. I am using For each loop container for that. I am doing it correctly. My aim is to validate the source data from all angle before writing it into target sql table. I am using below points to validate the source data , if I found any bad data I am redirecting those data to error output.
To Checking
1. To check whether data type of column.
2. To check whether buisinesskey column null.
Is there any thing which I am missing to validate source data.
Screen shot for reference
View 10 Replies
View Related
Dec 12, 2014
We have an issue with importing a CSV file into SQL where using a double quote " text qualifier is failing. The data is correct but it fails on a particular line, complaining about the qualifier even though the qualifier is in place and previous lines have imported fine.
View 3 Replies
View Related
Sep 10, 2015
How do I delete a post?
View 1 Replies
View Related
Oct 24, 2006
I have a lot of data coming in from CSV files. I have many CSV files (~20), with varying amounts of data- but some are quite large (largest file is ~230mb)
I'm trying to import it into a SQL database via SSIS, but the data is a little bit frustrating.
I have data that looks like this:
"Text from vendor ""Vendor Name, Inc."" blah blah", "Next string", "", 1234
Many things to notice here - as well you can imagine some of the difficulties here.
After parsing, this data should have 4 columns-
Column1 Column2 Column3 Column4
Text From Vendor "Vendor Name, Inc." blah blah Next string <blank> 1234
The biggest problems stem from the double quotes mixed in with the comma because it is a comma delimited file with quotes as the text qualifier. The other problem is the double quotes with blank text ... which prevents me from doing a replace on the double quotes ... I think ...
What would be your suggestions to help me parse this out? (don't forget, some of these are large files with about 260,000 records- )
Thanks in advance,
Rob
View 14 Replies
View Related
Aug 28, 2014
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
View 5 Replies
View Related
Feb 1, 2006
I've an issue with double-quotes in CSV file. One of the columns may contain this kind of value: "STATUS ""H"" "
I've got quote set to "
The file source fails on such records.
I found this thread and Scott tells us there that the file can't contain " in data.
Is this 100% correct?
I've got mutliple text columns and the pain is that I don't know which column might have these cases in future. To create a script means to write my own file parser for all files I use.
Any ideas?
Dima.
View 4 Replies
View Related
Sep 29, 2015
I have scoured the Microsoft forums and the internet to find out how I can generate the output of a CSV report that has double quotes around each value and is comma separated as follows:
"Abcd","123456","Efghi","789012","JKLMN"
If I try to concatenate double quotes around the values in the stored procedure or in the RDL, two double quotes appear around each value as follows.
""Abcd"",""123456"",""Efghi"",""789012"",""JKLMN""
I understand that this is because the default qualifier is double quote. What I see is that every time a double quote appears in a value (along with commas and line breaks), the qualifier will activate. Is there any way to turn this off for double quotes?
If I try to enter: <Qualifier>false</Qualifier>, the word "false" appears as the qualifier instead.
The only way I have found that produces a result similar to what I need ("Abcd","123456","Efghi","789012","JKLMN") is if I add a line break - chr(10) in the RDL in each field. However, this won't work for me because I can't have line breaks in each field in the output.
Note that in SSRS 2005, I was able to produce the report output as I state above by setting the field delimiter and qualifier as follows:
<FieldDelimiter>@?!?@</FieldDelimiter>
<Qualifier>?#^?</Qualifier>
This essentially turned the field delimiter and qualifier off, as the values entered would never appear in the data. I then could add double quotes and commas in the RDL. This used to work in the old version but does not anymore.
View 4 Replies
View Related
Nov 19, 2013
How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
View 6 Replies
View Related
Dec 19, 2013
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
View 9 Replies
View Related
Apr 8, 2014
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
View 8 Replies
View Related
Jul 19, 2015
I want to send an email two ice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
View 1 Replies
View Related
Dec 19, 2013
Consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
[code]....
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges.
View 8 Replies
View Related
Jan 19, 2014
I have created a table but i forget in which database. I know the table name but i dont know the database name..
I have to filter my table from all database ... how can i do and find....
View 5 Replies
View Related
Mar 20, 2014
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
View 8 Replies
View Related
Jul 22, 2014
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day)
-- Primary key is on L4Ident (clustered)
-- nonclustered index on ProcessDate, LinkRL4
DROP TABLE dbo.#Events;
DROP TABLE dbo.#Results;
CREATE TABLE dbo.#Events (
L4Ident int IDENTITY(1,1) NOT NULL,
[Code] ....
View 4 Replies
View Related
Feb 14, 2015
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
View 7 Replies
View Related
Feb 18, 2015
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
View 1 Replies
View Related
Mar 3, 2015
I have the following tables:
--acts as a transaction table
CREATE TABLE #TestData (
id int not null identity(1,1) primary key,
account varchar(10) not null,
deposit int not null
[Code] ....
--desired results
-- within each account group, when a individual record causes the groups running total to exceed the group's budget, show the difference that causes the groups running total to exceed the budget as unbudgeted. The amount of that transaction upto the budget amount show as renewal. For any succeeding individual records in the group, the amount of that transaction is Unbudgeted.
Here's the SQL I have thus far...
SELECT a.id
,a.account
,a.deposit
,a.total
,a.budget
,case when a.total <= a.budget
[Code] ....
View 4 Replies
View Related
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 9 Replies
View Related
Dec 19, 2013
CategoryNos
A 1
A 2
A 3
A 5
A 6
A 9
A 10
B 23
B 24
B 25
B 27
B 28
B 29
B 31
Consider the above data...
I need following output... query ..
CategoryMin RangeMax Range
A 1 3
A 5 6
A 9 10
B 23 25
B 27 29
B 31 31
Logic Behind :
if any number is missing in the continues series the return the min and max of number in that range.
View 1 Replies
View Related
