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

Delete Double Loaded Recs...??

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

SQL Server 2014 :: Find Double Records Within DateTime Range

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

How Can I Find What Tables Are Loaded In Memory ?

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

SQL 2012 :: Find Out Number Of Columns In Flat File Before Process That Particular File

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

SQL Server 2012 :: Query Get Data In Double Codes In String

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

SQL Server 2008 :: DOUBLE Precision For Calculations / Convert To Double?

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

Flat File Destination Could Not Be Loaded

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

Import Flat File To Sql Server Programmatically

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

T-SQL (SS2K8) :: Find All Instances Of String That Contain Certain Letters - Escape Double Quote

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

Integration Services :: SSIS Error - The Project File Cannot Be Loaded

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

How To Find Formula Column From A Table Programmatically?

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

How To Programmatically Find Out Whether Snapshot Agent Has Finished?

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

Integration Services :: Validating Data Loaded From Flat File Into Table Points

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

SQL 2012 :: SSIS Double Quote Text Qualifiers

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

SQL 2012 :: Renaming Columns Surrounded In Double Quotes?

Sep 10, 2015

How do I delete a post?

View 1 Replies View Related

Double Quotes In CSV File

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

DB Engine :: Double Row Added From A Source Table That Did Not Have Double Record

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

Double Quotes In ASCII File

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

Reporting Services :: SSRS 2012 - CSV Rendering / Turn Off Double Quote Qualifier

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

SQL Server 2012 :: How To Find Whether Object Is Used By Any SP / View

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

SQL Server 2012 :: Find First Value Greater Than Specific Value

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

SQL Server 2012 :: Find Numbers From A String

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

SQL Server 2012 :: How To Find When Email Is Queued

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

SQL Server 2012 :: How To Find Continues Number Ranges

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

SQL Server 2012 :: How To Find Particular Table Is Present In Which Database

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

SQL Server 2012 :: How To Find A Missing Sequence In A Column

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

SQL Server 2012 :: Find Changed Rows (and Row Prior To Changes)

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

SQL Server 2012 :: Find All Tables Used In Any Stored Procedure

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

SQL Server 2012 :: Find Names Of Tables For A Particular User

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

SQL Server 2012 :: Find Record(s) That Exceed Budget Value

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

SQL Server 2012 :: Find Out If Whole Column Of Data In A Table Is Empty?

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

SQL Server 2012 :: Find Missing Numbers In Continues Series

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







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