Design For Loading Data Without Knowlged Of Datatype Or Column Count

Jul 17, 2006

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this....

My thanks!

 

 

View 1 Replies


ADVERTISEMENT

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

Sep 10, 2007



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 5 Replies View Related

SQL 2012 :: Disabling Column Store Index And Try Loading Data

Oct 17, 2015

We have a typical issue with Column Store Index, we have a procedure which does 2 activities - Switch & Reverse Switch

Switch:
1. Fetch the Partitions needed to be switched
2. Switch the data from Main Table to Switch table
2. Disable the Column store on Switch table

SSIS Package:
3. Load data to Switch (Insert / Update)

Reverse Switch:
4. Enable the Switch
5. Switch back the data from Switch table to Main table

Issue: Some time the Column store is not getting disabled, and the package fails complaining try disabling the Column store index and try loading data.

If we re-run the procedure, the column store gets disabled.

View 1 Replies View Related

Data Access :: How To Get Column Datatype Using Namespace

Jan 2, 2013

which is to parse a create table script using Microsoft.SqlServer.TransactSql.ScriptDom namespace..I get a list of statements : StatementList SQLStatement = parser.ParseStatementList(StreamReader, out errors);then I cast a relevant statement to the CreateTableStatement type which gives me access to the ColumnDefinition class.

foreach (TSqlStatement T in SQLStatement.Statements)
{ if (T is CreateTableStatement) { CreateTableStatement  TC= (CreateTableStatement)T}}
with foreach (ColumnDefinition CD in TC.Definition.ColumnDefinitions)

I am ready to loop through the columns so far so good. I can get to the column name using CD.ColumnIdetifier.Value, to the data type using CD.DataType.Name.BaseIdentifier.Value, but can't seem to get to the length definition. Meaning, I can get column1 , varchar; but I need column1, varchar(40). where do I get (40) from?I tried using ScriptTokenStream under C.DataType, but it seems to iterate through all tokens not just ones relevant to that fragment.

View 6 Replies View Related

Better Design Than Using A Bit Datatype

May 9, 2001

SQLServer7, NT4. We search on Employees, Locations, things like that. We use a bit column to designate whether or not the status of the employee or location is active or inactive. Knowing that you can't create an index on a bit column or making the datatype an integer and indexing the column would not help the query performance, does anyone have a suggestion on how they have improved their design for searches in cases like this? Thank you in advance for the help.

Mark

View 1 Replies View Related

Data Flow Transformation: Count And Sum The Column Value

Nov 8, 2007

Hi,

I have one data flow control. Source is SQL server and destination is flat file destination. I have one derived column placed in between these two. This functionality works fine. I would like to sum one column data and count total no. of columns and put it in global variable. How can I achieve it?

Thanks,

View 7 Replies View Related

Datatype Is Int.. How Many Can Be Record Count ? :)

Jun 2, 2008

hello friends
i have table1 in aspnetdb.mdf. my cloumname usernumber and my datatype is int.
how may can be usernumber ? what is the max usernamber ?
or there is bigint as datatype.. what is the max usernumber for bigint ?
if you tell me about int32 and int 64, i couldnt find int32 and int64 as datatype in table1 in aspnetdb.mdf
cheers

View 1 Replies View Related

DB Design :: DataType Change In Databases To Support Multi-language

Sep 13, 2015

i need to change the production database structures with multi language Support , datatype from VARCHAR to NVARCHAR in all SQL Objects SQL Objects: Tables, Functions, Stored Procedures...Some of production Tables with 15 Croces records with 10 indexes.how to change the entire databases through SQL scripting

View 3 Replies View Related

DB Design :: How To Get Distinct Count Of Values

Sep 21, 2015

I have the below query,

select 
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column

[Code] ....

o/p: is :

LastEnforcementState NumberOfComputers PComputers

Compliant 7056 91.54
Downloading update(s) 39 0.51

[code]....

I want to add these two rows information into single row,

Failed to Download Updates(s)    131(127+8)       1.66(1.65+0.1)

how to do this in my sql query?

View 12 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Loading Images In A SQL Server Express Database Table At Design Time Using Visual Basic Net Express Editon

Jun 24, 2007

I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me

View 3 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

I use SQL 2000
I have a Column named Bool , the value in this Column is  0ã€?0ã€?1ã€?1ã€?1
I no I can use Count() to count this column ,the result would be "5"
but what I need is  "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is  2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.  
thank you very much
 

View 5 Replies View Related

DB Design :: Create Two Column With Math Function In One Column?

Oct 5, 2015

How Can I Create Two Column with Math Function In one Column  Like Below. 

Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key, 
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total 
)

View 5 Replies View Related

Column Datatype

Jan 9, 2005

Hi folks, need ur help.

I want to convert strings from a textfile and convert em to appropriate datatype for the destination columns, eg if i have a source value in string 11111 and the destination column is MONEY. I want to use CONVERT(functionretruningdatatype,'11111') in my insert statement.

Any guidance!



Howdy!

View 1 Replies View Related

How To Get DataType Of Column In Table ?

Jul 3, 2004

I want get DataType of Column in Table

ex: nvarchar, varchar, etc...

Help me !!

View 2 Replies View Related

Mismatch Of Column Datatype Between EM And QA

Dec 12, 2005

I've changed the width of a column from 128 to 64. How to EM shows 64, while sysobjects and sp_help show 128 ?? Even after disconnecting and re-connecting (just to be extra sure).

View 2 Replies View Related

Changing A Column Datatype

Dec 2, 2006

Hello I am having a table

table1
col1 (bit)

and i want to changethe col1 type for smallint

col1 (smallint)

true will be = 1
and false = 0

how can i do it ??
thank you

View 3 Replies View Related

Sum Float Datatype Column

Feb 12, 2014

I'm using this query to sum the values. The cost column is a float datatype and what could I differently do here to sum the cost. I'm unable to sum the cost.

Also is there any way I change the datatype to int for Cost column without losing the data.

select ID, MAX(Date) Date, SUM(Cost) Cost, MAX(Funding) Funding from Application
group by ID

View 4 Replies View Related

Derived Column In XML Datatype

May 9, 2008

Hi,

I have a data flow task where I have mutiple columns (of different data types) , I want to create a single column using all these columns in XML format and load it into a table with a column of datatype XML. Can I accomplish this using Derived column tranformation?

Thanks

View 17 Replies View Related

Integration Services :: SSIS VB Script Loading Data Into Oracle DB Missing Some Data

Nov 10, 2015

I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below: 

SQL Server:
Oracle:
DDL:

create table Person
(
BusinessEntityID Integer,
FirstName nvarchar2(50),
MiddleName nvarchar2(50),
LastName nvarchar2(50)
);

Result:

I follow up this article: [URL] ....

VB Script: 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

[Code] ..........

View 8 Replies View Related

Master Data Services :: Error Code 8 While Loading Data From MDS Stage To Model

Apr 22, 2015

I am getting ErrorCode 8 while loading the data from stage to model. I have checked my error view it states that "Member Code is Inactive".

Initially I have loaded same set of data in Model from MDS Stage table but then deleted with ImportType = 5 which removed all the data from the MDM model.

Now i want to load it back but its giving the Error Code 8 ..  Before loading the same data i have changed the stage table Importtype to 2 and Importstatusid to 0.

View 6 Replies View Related

Do We Have To Alawys Use Slowly Changing Dimensions (SCD) Component In The Data Flow For The Loading Of Table Data?

Feb 28, 2008

Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,

View 4 Replies View Related

How Can I Find A Column's DataType And Size.

Jan 21, 2007

hi ,How can I find  sql Datatype (like NVARCHAR , DESIMAL & .. ) and size of columns  and  also is it Identity or not in a Table of SQl server?

View 4 Replies View Related

Change Column Datatype $2,000 To 2000

Jul 30, 2007

Hello,How do I change the datatype of a column in a CSV file.  Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV). I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table.  The problem is with columns of money data. EX: "$2,000"  >>  2000The CSV file interprets the "$2,000" as a string of text.  How do I convert it to a decimal so I can insert it into my sql database? 

View 4 Replies View Related

Retreving The Column With Datatype-smalldatetime

Mar 12, 2008

Hi all
   A table consists of a column with name "Createddate" with datatype smalldatetime.
defaultly iam binding getdate() to Createddateso it is storing in tthe below format --
Mar 12 2008 11:38AM
while retreving iam mentioning...
select * from tbl where convert(varchar,createddate,101) = '03/12/2008'
it is showing no records ..even though records r there with --march 12 2008.
plz do needful in this senario.
thanks & regards
kanth
 
 
 
 

View 2 Replies View Related

Could A Datatype Of A Column Be Like Integer Array?

Aug 7, 2004

Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.

View 5 Replies View Related

Changing The DATATYPE Of A PUBLISHED COLUMN

Oct 27, 2003

Hi guys,

Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????

Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.

Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.


MY PROBLEM:
~~~~~~~~~~~~~
'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS.
I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.

IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.

View 3 Replies View Related

Find Column Name And Datatype Of A Given Table

Jul 22, 2006

Hi,

How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.

Thanks
-Sudhakar

View 3 Replies View Related

Update Column Without Changing Datatype?

Oct 15, 2015

I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'

I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?

MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable
USING (SELECT source_code, name, addr1, city, zip FROM
[D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable
ON ([TargetTable].[Code] = [SourceTable].[source_code])

[code]...

View 3 Replies View Related

Inserting Values From Column Datatype XML

Dec 20, 2013

I am having Test table with (id int , DataDescription xml) . I need to read the values from DataDescription column and insert into one table.

View 2 Replies View Related

Source Int/money Datatype To One Column

Jun 14, 2007

Hi,
i am trying to load output of count(X) and sum(salesamt) into the same column. if iam using transformation data task what datatype should i be converting the two outputs to accomidate result as

10.00 --count
234.00 --saleamt
22.00 --count
1000.00 --saleamt

View 3 Replies View Related

How Do I Use SELECT On A Column With Datatype TEXT?

Dec 20, 2005

Hello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.

View 8 Replies View Related

Datatype Casting In Derived Column

Apr 21, 2006

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

View 3 Replies View Related







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