MS SQL Server :: Split Data In Column

How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers.
The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization.
In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number.
EG: client #4334.1 would become 4334 for client # and 1 for categorization number
or
Client #424561.3 would become 424561 for Client # and 3 for categorization number.
I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns.
Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second.
Is it possible to do this? I really need help on this one.
Thank you :)

View Replies !

Comparing A Column List Split To A Table.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo

View Replies !

How To Split A Delimited Column Into Mulitple Rows In The Dataflow?
I'm sure there is probably a very easy solution that I am just not seeing or can't Google...

I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?

TIA,
Matthew

View Replies !

How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS

I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:

the state table is like this:

StateCode(PK)      stateLegalCode         stateName
============= ==============   =========
1                          01                            Florida

the city table is like this:

citycode(PK)         cityLegalCode          cityname          StateCode(FK)
============    =============        ========        =============
1                          1001                           Quakertown       1

the file has the following information

cityLegalCode            cityName
=============       ========
01-1001                     Quakertown
...

how can I load the file into CITY table:

1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).

how can I do something like that using SSIS???

thanks

View Replies !

Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Hi ,
I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table,
e.g.
ID     Email
--     ----------
1      AS1 AS11
2      AS2 AS3 AS4 AS5
3      AS6 AS7

The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g.
ID      EmailAddress
---     ------------------
AS1      abcstu@emc.com
AS2      abcstu2@emc.com
AS3      abcstu3@emc.com
AS4      abcstu4@em.com
AS5      abcstu5@emc.com
AS6      abcstu6@emc.com
AS7      abcstu7@emc.com
AS11     abcstu8@emc.com
I need to create a stored procedure or function that:
1. Selects an Email from the first table, based on a valid ID,
2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then,
3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array
Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors?

Many Thanks,
probetatester@yahoo.com

View Replies !

How To Split The Data Into Training And Validation Sets When Doing Data Mining?
Could I ask how to spit the data into training and validation sets when doing data mining?

Thanks

View Replies !

Split Data
My company use SQL server 2005 standard. considering deal with huge data, how if we want to split data (date range yearly or monthly) in order to ease transaction. that's simply for us to use query, but how if we want to split data that can be easily execute by operator (non-admin privilege). Is there any another way?

View Replies !

Data Flow Split
I'm trying to figure out how in a Data Flow Transform I can split some data.

I have data coming through that has a PK (col1) and datetime col (col2).
This data may contain multiple rows for the PK, col1.
I want to be able to take the min datetime for each row for col1 and send down one path and all other rows down another path eg. There are 20 rows in the Data Flow coming through. Col1 has value 1 for first 10 rows, value 2 for remaining 10 rows and there are different values for Col2 for all rows. Take the Min value in Col2, where col1=1 and also the min value in Col2, where col1=2 and send down one path. Send all other rows down another path.

I thought of using the Conditional Split Transform but my Expression knowledge isin't experienced enough (I'm looking into this) and I'm not sure if it can be done.

I also though of using the Multi-Cast Transform and then using the Aggregate function, on each data set. While I can do this easily for the first data flow.

SELECT col1, MIN(col2)

FROM tbl1

GROUP BY col1

which is easily done in the Aggregate transform, the other side of the data flow I can't see how can be done using the Aggregate Transform

SELECT col1

FROM tbl1

WHERE col2 NOT IN (SELECT MIN(col2) FROM tbl1 GROUP BY col1).

Are either methods feasilble or is there another way.
I want to avoid putting this data into temp tables in a SQL database and manipulating the data from there. The data has been extracted from a flat file source.
Any help and ideas welcome.

View Replies !

How To Split Data Into Two Rows
I have a query that returns a table similar to:

State        Status          Count
CA          Complete     10
CA          Incomplete   200
NC          Complete     20
NC          Incomplete   205
SC           Incomplete   50

What sort of query will allow me to reformat the table into:

State      Complete     Incomplete
CA         10               200
NC         20               205
SC          NULL         50

View Replies !

Split The Data Into Columns
I have a table called products with the values like

ProductId ProductName
10            A
20           D,E,F,G
30           B,C
40           H,I,J

I need to display each productid's with

ProductId ProductName
10           A

20           D
20           E
20           F
20           G
30           B
30          C
40          H
40          I
40          J

I will be appreciated if you can send me the code.

Thanks,
Mears

View Replies !

T-SQL To Split Data From One Table Into Two Tables?
What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.

Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?

Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.

-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,

ColumnA VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)

-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,

ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,

CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)

View Replies !

Using Condation Split To Update Data
hi all

i using SSIS to update data on tables based on another tables as follow

start connect to DS --> using Lookup to located the key fields on the 2 tables --> on error start insert into the second table else if the recorde is already exist start update

my problem is Performance

--source table rows count almost 60 million

--distination table almost 3 million

my package execute from 2 days and still working till now updated 122,000 and insert 4 million

kindly i need support

thanks & regards

View Replies !

How To Split Data From The MS SQL 2000 Database?
Hi All,

We are working on a project, (C#.Net 2003, MS SQL 2000) where database is growing as 2 to 3 GB per day (Scanned Documents are storing in Database). There is only one table in the database. Currently database size is grown upto 200 GB. For safety reason we are planning to split the database accoring to one key field in the table say Book_no.

Please guide/suggest me how to split the database now on the SQL query like "SELECT * FROM master_records WHERE book_no=1"
And later how to merge/combine all these splitted database into one.
I thought of using "Data Export" and later "Import with Append" but don't know whethere it will effective or not? Any method or tool available to Split the database table and later combine them to make again a single one?

Please help me, its urgent.

Thanks in advance.

View Replies !

Importing Split Data Into Table
Hi,

I have Data split into 3 text files with 3 fields repeated in each to link then (key). I want to import this data into one table.
I used DTS to create 3 tables with the data. Now i want to combine the 3 tables into only one (that i already created). How can i do this? Note: the field names in the source tables are different from the destination table.

Thanks

Guy

View Replies !

Retrieving Data From A DB Based On Output Of A Conditional Split

This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.

Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.

Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.

How can I do this?

Thank you!

Nolan

View Replies !

Deadlock Problem? 3 Way Conditional Split Of Data From One Table To Another Never Completes
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.

Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.

View Replies !

Split DB - Static Controls Versus User Data - Feedback On Plan
In my prototype, I've got i) the (relatively) static control files (various control values) and ii) the user datain the same DB (SQL Server Express). Both types of files are entirely accessed through table adapters.
I'm thinking I'd like to split the data into two databases, one for just the static control files, and the other for the user data. Then split the app into two separate apps, one for user data, one for just the control tables. I'm thinking this will be more secure, easier to maintain and rebuild if something fails, other reasons (...?)
I'm hoping to get some general feedback on the reasonability of doing this, and on my general approach.
My first approximation of a plan to do this would be:0) lot's of backups first and during!A) Copy the current DB, rename as "Control-something, delete the "user" tables from it, then delete the control tables from the original "Data" DB
B) similarly copy, rename, split the app files into "control" and "user" appsC) in the both of the resulting apps 1) create a new connection string to the "new" control DB 2) Edit the tableadapters as necessary to use the new connection string
D) Figure out where there needs to be any "cross over" links between the two apps (dunno right now, users won't touch app controls, though maybe some administrators would....)
E) Plan to deploy both DB's and both apps as appropriate, with separate access URL's for the two different apps.
So the questions are
0) Any real advantages to doing this, for security, maintenance, etc?1) Is this a "commonly used" approach, or do most people just keep the data and controls in one DB?2) Any issues with one app / multiple DB;s (it's going to be a single server app for the first release.)3) Any issues with simultaneously accessing one DB from two apps?4) Are there any big missing pieces in this conversion plan?
Any feedback on this would be appreciated.
Thanks!

View Replies !

Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:

1)       Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt

The txt file going from top to bottom is sort of grouped like this
     Header Row (designated by €˜HD€™)
          Corresponding Detail Rows for the Header Row
           €¦..
     Next Header Row
          Corresponding Detail Rows

     €¦and so on

     http://www.webfound.net/rows.jpg

2)       Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦

Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.

This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.

The filenames of the txt files will vary and be based on one of the column values already in the header table.

Here is a print screen of my package so far:

http://www.webfound.net/tasks.jpg

http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg

http://www.webfound.net/DataFlow_Task_components.jpg

Let me know if you need more info. Examples of the actual data in the tables are here:

http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt

Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg

View Replies !

Moving Files (split From An Existing Thread-SSIS Equivalent To DTS Transform Data Task Properties)
Hi JayH (or anyone). Another week...a new set of problems. I obviously need to learn .net syntax, but because of project deadlines in converting from DTS to SSIS it is hard for me to stop and do that. So, if someone could help me some easy syntax, I would really appreciate it.

In DTS, there was a VBScript that copied a set of flat files from one directory to an archive directory after modifying the file name. In SSIS, the directory and archive directory will be specified in the config file. So, I need a .net script that retrieves a file, renames it and copies it to a different directory.

Linda

Here is the old VBScript Code:

Public Sub Main()

Option Explicit

Function Main()

Dim MovementDataDir

Dim MovementArchiveDataDir

Dim MovementDataFile

Dim MovementArchiveDataFile

Dim FileNameRoot

Dim FileNameExtension, DecimalLocation

Dim CurMonth, CurDay

Dim FileApplicationDate

Dim fso ' File System Object

Dim folder

Dim FileCollection

Dim MovementFile

'======================================================================

'Create text strings of today's date to be appended to the archived file.

FileApplicationDate = Now

CurMonth = Month(FileApplicationDate)

CurDay = Day(FileApplicationDate)

If Len(CurMonth) = 1 Then

CurMonth = "0" & CurMonth

End If

If Len(CurDay) = 1 Then

CurDay = "0" & CurDay

End If

FileApplicationDate = CurMonth & CurDay & Year(FileApplicationDate)

'=====================================================================

' Set the movement data directory from the global variable.

MovementDataDir = DTSGlobalVariables("gsMovementDataDir").Value

MovementArchiveDataDir = DTSGlobalVariables("gsMovementDataArchiveDir").Value

fso = CreateObject("Scripting.FileSystemObject")

folder = fso.GetFolder(MovementDataDir)

FileCollection = folder.Files

' Loop through all files in the data directory.

For Each MovementFile In FileCollection

' Get the full path name of the current data file.

MovementDataFile = MovementDataDir & "" & MovementFile.Name

' Get the full path name of the archive data file.

MovementArchiveDataFile = MovementArchiveDataDir & "" & MovementFile.Name

DecimalLocation = InStr(1, MovementArchiveDataFile, ".")

FileNameExtension = Mid(MovementArchiveDataFile, DecimalLocation, Len(MovementArchiveDataFile) - DecimalLocation + 1)

FileNameRoot = Mid(MovementArchiveDataFile, 1, DecimalLocation - 1)

MovementArchiveDataFile = FileNameRoot & "_" & FileApplicationDate & FileNameExtension

If (fso.FileExists(MovementDataFile)) Then

fso.CopyFile(MovementDataFile, MovementArchiveDataFile)

' If the archive file was coppied, then delete the old copy.

If (fso.FileExists(MovementArchiveDataFile)) Then

fso.DeleteFile(MovementDataFile)

End If

End If

Next

fso = Nothing

folder = Nothing

FileCollection = Nothing

Main = DTSTaskExecResult_Success

End Function

View Replies !

Importing Data From Oracle9i CLOB Column To MS SQL Server Text Column
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com

View Replies !

Insert Data Into A Destination Column Which Doesnt Have An Input Column
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data.
For example:

I have a sql query which bring data of 3 columns

ID | FISRT NAME | LAST NAME
1 MIKE MORGAN
2 SARA JOHANES

So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.

Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.

Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column.
So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it?
Hope I was clear
Thanks for your help.

Beli

View Replies !

Create Multi Column View From Single Column Data
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code         month      value
350                          1             10
350                          2             20
350                          3             30

product_table
product_code   profit_center
350                       4520

result_view

product_code            profit_center            mon1       mon2         mon3
350                             4520                     10            20             30

My current query gives the following result
result_view

product_code            profit_center            mon1       mon2         mon3
350                             4520                     10             0                0
350                             4520                      0            20                0
350                             4520                      0              0              30

Any direction toward a solution would be appreciated.    Am using SS2005.

View Replies !

Column Data To Column Heading By Dynamic Pivot Maybe

Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41

StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41

The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.

View Replies !

Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....

View Replies !

How To Delete Data When Column Have Depency With Other Column?
in Table A Column is
PriKey             No       Name
1                      1        Apple
2                      2       Orange
3                      3        Juicy
in Table B column is
Prikey             ColumnA           Price
1                         1                     10
2                          3                     2
3                          2                     5

TableA.Prikey have Depency with TableB.ColumnA

when I am trying to Delete data from Table A , I got error message becaue the depency
how to delete data when there have depency?

I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted
does there have a way to disable depency and then enable ?

thank you

View Replies !

Copy Column Of Data Into Another Column In The Same Table
I have a column of digits I'd like to copy into another column in thesame table. How would I do this?Thanks,Bill

View Replies !

Column Data Truncation , How To Identify Column?
Hi There

This one has bothered me ever since sql server 2000.

When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.

Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.

The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?

Can this be done in 2005 if not will this information be available in 2008 ?

Thanx

View Replies !

[Flat File Source [8885]] Error: The Column Data For Column &&"CountryId&&" Overflowed The Disk I/O Buffer.

Hi everyone,
I am using SSIS, and I got the folowing error, I am loading several CSV files in a OLE DB, Becasuse the file is finishing and the tak dont realize of the anormal termination, making an overflow.
So basically what i want is to control the anormal ending of the csv file.
please can anyone help me ???

I am getting the following error after replacing the '""' with '|'.
The replacng is done becasue some text sting contains "" wherein the DFT was throwing an error as " The column delimiter could not foun".

[Flat File Source [8885]] Error: The column data for column "CountryId" overflowed the disk I/O buffer.
[Flat File Source [8885]] Error: An error occurred while skipping data rows.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (8885) returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

[DTS.Pipeline] Information: Post Execute phase is beginning.

apprecite for immediate response.

Thanks in advance,
Anand

View Replies !

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View Replies !

Move Data From One Column To Another Column And Row
need some help converting a table which has one row for each year. Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns.

I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows):

Basic Information needed for Life Insurance 25 2005
Basic Information needed for Life Insurance 45 2006
Ea Chairmans Conference 10 2005
EA Chairmans Conference Press Release 33 2005
EA Chairmans Conference Press Release 21 2006
EA Chairmans Inner Circle Press Release 16 2005
EA Chairmans Inner Circle Press Release 46 2006
EA Honor Ring Press Release 13 2005
EA Honor Ring Press Release 35 2006
EA National Conference Press Release 6 2005
EA National Conference Press Release 9 2006

I need this data displayed like this:

Title 2005 2006
Basic Information needed for Life Insurance 25 45
Ea Chairmans Conference 10 10
EA Chairmans Conference Press Release 33 21
EA Chairmans Inner Circle Press Release 16 46
EA Honor Ring Press Release 13 35
EA National Conference Press Release 6 9

What I want to do is to basically say:

If Row 1 Title = Row 2 Title and Row 1 Year = Row 2 (Year + 1)) Then

add to grid Row 1 Title, Row 1 Qty, Row 2 Qty

Skip Row 2 and go to Row 3 (repeat this step until end of data)

End

Is there any way in the SQL querry to retrieve this data in this format so that I can display it in a datagrid?

Is there any way to display the data in this pattern.

Below is the code I am using now, but since the moving of the data is done in code it is very slow.
****************** Global Variables

Dim dbName As String = "Region_Web_Page_Hits"
Dim ColumnCount As Integer

Function ReturnColumnHeaderAndHitsDataSet() As System.Data.DataSet
'******** This will bring back the column Headers and Gross Hits per Column
Dim ColumnHeaderAndHitsQueryString As String = "SELECT DISTINCT TOP 100 PERCENT " & _
"YEAR(dat_Date) AS str_Year, COUNT(str_PageName) AS int_YearHits " & _
"FROM dbo.tbl_web_page_hits " & _
"WHERE (str_URL LIKE N'%press_Release%') " & _
"GROUP BY YEAR(dat_Date) " & _
"HAVING (Not (Year(dat_Date) Is NULL)) " & _
"ORDER BY YEAR(dat_Date)"

Dim ColumnHeaderAndHitsDataSet = LoadDataArray(ColumnHeaderAndHitsQueryString, dbName)
Return ColumnHeaderAndHitsDataSet
End Function

Function ReturnTableDataSet() As System.Data.DataSet
'This query string will return the data needed to display the report.
Dim TableQueryString As String = "SELECT TOP 100 PERCENT " & _
"str_PageName, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year " & _
"FROM dbo.tbl_web_page_hits WHERE str_URL LIKE '%Press_Release%' " & _
"GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName"
Dim TableDataSet = LoadDataArray(TableQueryString, dbName)
ColumnCount = TableDataSet.Tables(0).Columns.Count
Return TableDataSet
End Function

'******* Function to Load the Data Array
Function LoadDataArray(ByVal queryString, ByVal dbName) As System.Data.DataSet
Dim ConnectionString As String = ConnectionStr(dbName)
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString)
Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)
sqlConnection.Close()
Return dataSet
End Function

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

If Not Page.IsPostBack Then
Dim Sort_Field As String = ""
dg_Data.DataSource = QueryMethod(Sort_Field)
dg_Data.DataBind()
End If
End Sub

Currently I have a bunch of Classic ASP code which puts the data in to an array and then into a table with a series of if statments, but it is really slow.

I want to bind it to a data grid and if possible let the SQL statement format the data for me.

View Replies !

Update 1 Column With Data From Another Column
Hello,

I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.

View Replies !

Problem Loading Data From FlatFile Source Data For Column Overflowed The Disk I/O Buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

View Replies !

System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse

View Replies !

Delete Rows With Duplicate Column Data But Unique Row Data
Hello,

This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.

I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.

I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,

Kerry

View Replies !