Our database gets updated each week with text files spat out by a mainframe. Previously, the database was in Access; we copied and pasted the text files into Excel, ran macros on them to convert the data, then pasted the results directly into Access and this worked fine for us.
Now that we've moved the tables to SQL Server 2000, we're having problems with the data. We wanted to set up DTS packages for each file to just put them directly into SQL Server. This works for pretty much everything except for the dates.
The way the files are set up, they're comma delimited files with quotes around the text and nothing around the dates. The dates don't have any delimiters; they're just listed like 13012006. Every time we try to import these files into SQL, it gripes about the datatypes; we're trying to put the dates into datetime fields but SQL thinks they're strings. Eek! If we put date delimiters (like 13/01/2006) SQL pulls them in fine, but apparently the mainframe lacks the ability to put these delimiters in by itself and still run everything else OK. The person who writes the extracts has to do it in a language called 'Focus' which I've never heard of and don't know anything about, and he says what I'm asking for can't be done. OK...so now what?
I've tried and tried to convert these strings into dates using both CAST() and CONVERT() and just can't manage to do it. I know I'm missing something really obvious here; does anyone have any tips or advice? Thanks in advance.It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.
One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.
I've worked with this before and it worked pretty well.
Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.
Regards,
hmscott|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.
One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.
I've worked with this before and it worked pretty well.
Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.
Regards,
hmscott
Thanks for the advice; I'll try your first suggestion, since I can't write ActiveX script. I can usually manage SQL and I can almost always figure it out on my own, but this one stumped me. I'm not a certified DBA or anything, I'm still learning. :)|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.
One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.
I've worked with this before and it worked pretty well.
Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.
Regards,
hmscott
We tried using datetime strings and it just refused to work; we had to write ActiveX scripts n the end using DateSerial in VBScript, which was tough for us because neither one of us really knows VBScript, I'm a VB noob and my colleague knows VBA but not VBScript; I know they're basically the same but he doesn't. :) But we did eventually get it to work! So yay!
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Friday, March 30, 2012
Having SQL Profiler while SQL Express Installed
This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C6B584.FD651AD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dear All,
Is it possible for me to have a SQL profiler install in my local while i = have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0008_01C6B584.FD651AD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Dear All,
Is it possible for me to have a SQL profiler install in my local = while i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0008_01C6B584.FD651AD0--This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C6B540.6D9D7D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If you have a copy of sql server and want to install the management =tools you can install them on a machine regardless of the edition of sql =server actually running on the machine. However, I haven't tried running =Profiler against SQL Express, but I don't see why you can't. But you'll =have to actually try that yourself.
Mark
"Connie" <yfchan@.kdu.edu.my> wrote in message =news:%23LrYcIUtGHA.1888@.TK2MSFTNGP03.phx.gbl...
Dear All,
Is it possible for me to have a SQL profiler install in my local while =i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0034_01C6B540.6D9D7D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If you have a copy of sql server and =want to install the management tools you can install them on a machine =regardless of the edition of sql server actually running on the machine. However, I =haven't tried running Profiler against SQL Express, but I don't see why you can't. But =you'll have to actually try that yourself.
Mark
"Connie" wrote in message news:%23LrYcIUtGHA.=1888@.TK2MSFTNGP03.phx.gbl...
Dear All,
Is it possible for me to have a SQL profiler install in my =local while i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0034_01C6B540.6D9D7D50--sql
--=_NextPart_000_0008_01C6B584.FD651AD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dear All,
Is it possible for me to have a SQL profiler install in my local while i = have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0008_01C6B584.FD651AD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Dear All,
Is it possible for me to have a SQL profiler install in my local = while i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0008_01C6B584.FD651AD0--This is a multi-part message in MIME format.
--=_NextPart_000_0034_01C6B540.6D9D7D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If you have a copy of sql server and want to install the management =tools you can install them on a machine regardless of the edition of sql =server actually running on the machine. However, I haven't tried running =Profiler against SQL Express, but I don't see why you can't. But you'll =have to actually try that yourself.
Mark
"Connie" <yfchan@.kdu.edu.my> wrote in message =news:%23LrYcIUtGHA.1888@.TK2MSFTNGP03.phx.gbl...
Dear All,
Is it possible for me to have a SQL profiler install in my local while =i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0034_01C6B540.6D9D7D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If you have a copy of sql server and =want to install the management tools you can install them on a machine =regardless of the edition of sql server actually running on the machine. However, I =haven't tried running Profiler against SQL Express, but I don't see why you can't. But =you'll have to actually try that yourself.
Mark
"Connie"
Dear All,
Is it possible for me to have a SQL profiler install in my =local while i have running SQL Express in my machine?
Please advise, thank you.
--=_NextPart_000_0034_01C6B540.6D9D7D50--sql
Having problems with text datatype!
Hi,
pls can anyone help me to solve the error generated by this query,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spAP_PS_VENDOR_CONVER]
AS
SET NOCOUNT ON
SELECT UPPER(SETID) AS SETID
,UPPER(VENDOR_ID)AS VENDOR_ID
,CONVER_DT
,CONVER_SEQ_NUM
,CNTCT_SEQ_NUM
,UPPER(CONVER_TOPIC) AS CONVER_TOPIC
,UPPER(OPRID)AS OPRID
,REVIEW_DAYS
,REVIEW_DATE
,REVIEW_NEXT_DATE
,UPPER(KEYWORD1) AS KEYWORD1
,UPPER(KEYWORD2) AS KEYWORD2
,UPPER(KEYWORD3) AS KEYWORD3
,CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)) AS DESCRLONG
,PROCESS_INSTANCE
,MAX(EY_SF_UPDATE_DTTM) AS EY_SF_UPDATE_DTTM
,PROCESS_DTTM
,CREATED_DTTM
,UPPER(EY_SF_ACTN_FLG) AS EY_SF_ACTN_FLG
,UPPER(EY_SF_STATUS) AS EY_SF_STATUS
FROM Metastorm.dbo.AP_PS_VENDOR_CONVER
WHERE EY_SF_STATUS='N'
GROUP BY SETID,VENDOR_ID,CONVER_DT,CONVER_SEQ_NUM,CNTCT_SEQ_NUM,CONVER_TOPIC,OPRID
,REVIEW_DAYS,REVIEW_DATE,REVIEW_NEXT_DATE,KEYWORD1,KEYWORD2,KEYWORD3,DESCRLONG
,PROCESS_INSTANCE,PROCESS_DTTM,CREATED_DTTM,EY_SF_ACTN_FLG
,EY_SF_STATUS
SET NOCOUNT OFF
Msg 306, Level 16, State 2, Procedure spAP_PS_VENDOR_CONVER, Line 4
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Regards,
Sg
sorry forgot to tell that the descrlong is a text column
Regards,
sg
|||You couldn't use text, ntext, image datatype in group by.
Try use following statement in group by:
Code Snippet
GROUP BY CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)), .....
|||Hi Konstantin,
Thanks a lot.
Regards,
Sg
Wednesday, March 28, 2012
Having Problem While Importing a Text File
Hello everbody,
Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.
How do you import the file? BCP, BULK INSERT or DTS?
-- SNIP --
>
How do you import the file? BCP, BULK INSERT or DTS?
>
Could you post the CREATE TABLE statement for the table in question?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
hi again...
thanks for your concern... i really appreciated...
we are importing the text file by using DTS
here is the create table statement used by DTS :
The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.
If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.
-- SNIP --
-- SNIP --
-- SNIP --
>
The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.
>
>
If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.
>
Does your input file really have 543 input fields?
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi again...
partitioning the table is one of the solvation but for our production
system unfortunately it is not proper for use. :((
cause we have lots of clients and if we partition the tables for our
each client there are gonna be enourmus number of tables, so it is not
possible to deal with those number of tables right now...
>
-- SNIP --
>
>
-- SNIP --
>
>
-- SNIP --
>
>
Hi!
>
I know I said earlier that you could use long data types, and the table
you're trying to create is in accordance with my statement earlier.
However, I should have added that each column stores a pointer in the
row and the actual data is stored in a different location. If you add
up the sizes of the pointers along with other row data they should be
below the ~8K limit too. I guess I should be more accurate when I say
something in future like those people who speak legal-ese. The DDL
query was really funny to look at, and it's the first time I ever used
the "read more" link on Google Groups.
>
I would suggest that you partition your tables vertically so you have
some of the columns in one table and the other columns in another table
(...or perhaps more than 2 tables, depending on the sizes... I'm not
really good at the math).
>
N.I.T.I.N.
>
PS: I hope I never have to deal with such a monstrosity - a table that
has so many columns. I once had to deal with 36 columns and that was
too much for me as a developer (that was before my days as a DBA). I
split it up into 3 tables though people may say it is less efficient to
have 3 queries instead of one (remember the days when people said you
should use assembly language as the code is smaller & faster?).
>
PPS: No offence to assembly language developers in the last 'PS'. I
totally respect people who still use assembly, but for me it's just a
little too much source code to think straight - I'd spend a whole hour
doing something that I could do in 15 minutes with VB, Java or C# (when
equipped with the right IDE, of course!).|||panic attack (tunc.ovacik@.gmail.com) writes:
That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?
As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.
No. What matters is the actual row size, not the possible max.
No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.
>
That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?
>
>
As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.
>
>
No. What matters is the actual row size, not the possible max.
>
>
No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 4 Aug 2006 01:17:45 -0700, "panic attack" <tunc.ovacik@.gmail.com>
wrote:
The idea was not to partition the table by client, but to normalize it
so that the time periods are rows, not columns (for one example.)
Hopefully there is a key in the data you import. Else you are in dire
straits. Say that columns 1 and 2 are the keys. Then you could define
a view as:
Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.
As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!
Tunc Ovacikpanic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.
>
As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!
How do you import the file? BCP, BULK INSERT or DTS?
Could you post the CREATE TABLE statement for the table in question?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||--SNIP --
Quote:
Originally Posted by
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.
>
As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
-- SNIP --
Good day,
If you're using a large data-type for a column (such as varchar(max),
nvarchar(max), varbinary(max), text, image, & xml), you can go beyond
the 8060 limit.
Alternatively, if you aren't using large data-types, you can vertically
partition the table so some of the columns would be in one table while
the other set of columns would be in another table.
Hope this helps.
Regards,
N.I.T.I.N.|||Erland Sommarskog wrote:
Quote:
Originally Posted by
panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
Our system is using Sql Server 2000 on Windows XP / Windows 2000
We have a text file needs to be imported into Sql Server 2000 as a
table.
But we are facing a problem which is,
Sql Server claims that it has a character size limit ( which is 8060 )
so it cant procceed the import operation if the text file has a record
bigger then 8060.
The records , in the text file, have a size bigger then 8060. So we
wont be able to import the text file.
On the other hand it is said that Sql Server 2005 can get a record
bigger then 8060 but
again we couldnt be able to perform the task.
As a result, i urgently need to know that how may i import the text
file which has a record bigger then 8060 characters.?
Any help is appreciated
thanks a lot!!
>
How do you import the file? BCP, BULK INSERT or DTS?
>
Could you post the CREATE TABLE statement for the table in question?
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
hi again...
thanks for your concern... i really appreciated...
we are importing the text file by using DTS
here is the create table statement used by DTS :
CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
[Col004] varchar (255) NULL,
[Col005] varchar (255) NULL,
[Col006] varchar (255) NULL,
[Col007] varchar (255) NULL,
[Col008] varchar (255) NULL,
[Col009] varchar (255) NULL,
[Col010] varchar (255) NULL,
[Col011] varchar (255) NULL,
[Col012] varchar (255) NULL,
[Col013] varchar (255) NULL,
[Col014] varchar (255) NULL,
[Col015] varchar (255) NULL,
[Col016] varchar (255) NULL,
[Col017] varchar (255) NULL,
[Col018] varchar (255) NULL,
[Col019] varchar (255) NULL,
[Col020] varchar (255) NULL,
[Col021] varchar (255) NULL,
[Col022] varchar (255) NULL,
[Col023] varchar (255) NULL,
[Col024] varchar (255) NULL,
[Col025] varchar (255) NULL,
[Col026] varchar (255) NULL,
[Col027] varchar (255) NULL,
[Col028] varchar (255) NULL,
[Col029] varchar (255) NULL,
[Col030] varchar (255) NULL,
[Col031] varchar (255) NULL,
[Col032] varchar (255) NULL,
[Col033] varchar (255) NULL,
[Col034] varchar (255) NULL,
[Col035] varchar (255) NULL,
[Col036] varchar (255) NULL,
[Col037] varchar (255) NULL,
[Col038] varchar (255) NULL,
[Col039] varchar (255) NULL,
[Col040] varchar (255) NULL,
[Col041] varchar (255) NULL,
[Col042] varchar (255) NULL,
[Col043] varchar (255) NULL,
[Col044] varchar (255) NULL,
[Col045] varchar (255) NULL,
[Col046] varchar (255) NULL,
[Col047] varchar (255) NULL,
[Col048] varchar (255) NULL,
[Col049] varchar (255) NULL,
[Col050] varchar (255) NULL,
[Col051] varchar (255) NULL,
[Col052] varchar (255) NULL,
[Col053] varchar (255) NULL,
[Col054] varchar (255) NULL,
[Col055] varchar (255) NULL,
[Col056] varchar (255) NULL,
[Col057] varchar (255) NULL,
[Col058] varchar (255) NULL,
[Col059] varchar (255) NULL,
[Col060] varchar (255) NULL,
[Col061] varchar (255) NULL,
[Col062] varchar (255) NULL,
[Col063] varchar (255) NULL,
[Col064] varchar (255) NULL,
[Col065] varchar (255) NULL,
[Col066] varchar (255) NULL,
[Col067] varchar (255) NULL,
[Col068] varchar (255) NULL,
[Col069] varchar (255) NULL,
[Col070] varchar (255) NULL,
[Col071] varchar (255) NULL,
[Col072] varchar (255) NULL,
[Col073] varchar (255) NULL,
[Col074] varchar (255) NULL,
[Col075] varchar (255) NULL,
[Col076] varchar (255) NULL,
[Col077] varchar (255) NULL,
[Col078] varchar (255) NULL,
[Col079] varchar (255) NULL,
[Col080] varchar (255) NULL,
[Col081] varchar (255) NULL,
[Col082] varchar (255) NULL,
[Col083] varchar (255) NULL,
[Col084] varchar (255) NULL,
[Col085] varchar (255) NULL,
[Col086] varchar (255) NULL,
[Col087] varchar (255) NULL,
[Col088] varchar (255) NULL,
[Col089] varchar (255) NULL,
[Col090] varchar (255) NULL,
[Col091] varchar (255) NULL,
[Col092] varchar (255) NULL,
[Col093] varchar (255) NULL,
[Col094] varchar (255) NULL,
[Col095] varchar (255) NULL,
[Col096] varchar (255) NULL,
[Col097] varchar (255) NULL,
[Col098] varchar (255) NULL,
[Col099] varchar (255) NULL,
[Col100] varchar (255) NULL,
[Col101] varchar (255) NULL,
[Col102] varchar (255) NULL,
[Col103] varchar (255) NULL,
[Col104] varchar (255) NULL,
[Col105] varchar (255) NULL,
[Col106] varchar (255) NULL,
[Col107] varchar (255) NULL,
[Col108] varchar (255) NULL,
[Col109] varchar (255) NULL,
[Col110] varchar (255) NULL,
[Col111] varchar (255) NULL,
[Col112] varchar (255) NULL,
[Col113] varchar (255) NULL,
[Col114] varchar (255) NULL,
[Col115] varchar (255) NULL,
[Col116] varchar (255) NULL,
[Col117] varchar (255) NULL,
[Col118] varchar (255) NULL,
[Col119] varchar (255) NULL,
[Col120] varchar (255) NULL,
[Col121] varchar (255) NULL,
[Col122] varchar (255) NULL,
[Col123] varchar (255) NULL,
[Col124] varchar (255) NULL,
[Col125] varchar (255) NULL,
[Col126] varchar (255) NULL,
[Col127] varchar (255) NULL,
[Col128] varchar (255) NULL,
[Col129] varchar (255) NULL,
[Col130] varchar (255) NULL,
[Col131] varchar (255) NULL,
[Col132] varchar (255) NULL,
[Col133] varchar (255) NULL,
[Col134] varchar (255) NULL,
[Col135] varchar (255) NULL,
[Col136] varchar (255) NULL,
[Col137] varchar (255) NULL,
[Col138] varchar (255) NULL,
[Col139] varchar (255) NULL,
[Col140] varchar (255) NULL,
[Col141] varchar (255) NULL,
[Col142] varchar (255) NULL,
[Col143] varchar (255) NULL,
[Col144] varchar (255) NULL,
[Col145] varchar (255) NULL,
[Col146] varchar (255) NULL,
[Col147] varchar (255) NULL,
[Col148] varchar (255) NULL,
[Col149] varchar (255) NULL,
[Col150] varchar (255) NULL,
[Col151] varchar (255) NULL,
[Col152] varchar (255) NULL,
[Col153] varchar (255) NULL,
[Col154] varchar (255) NULL,
[Col155] varchar (255) NULL,
[Col156] varchar (255) NULL,
[Col157] varchar (255) NULL,
[Col158] varchar (255) NULL,
[Col159] varchar (255) NULL,
[Col160] varchar (255) NULL,
[Col161] varchar (255) NULL,
[Col162] varchar (255) NULL,
[Col163] varchar (255) NULL,
[Col164] varchar (255) NULL,
[Col165] varchar (255) NULL,
[Col166] varchar (255) NULL,
[Col167] varchar (255) NULL,
[Col168] varchar (255) NULL,
[Col169] varchar (255) NULL,
[Col170] varchar (255) NULL,
[Col171] varchar (255) NULL,
[Col172] varchar (255) NULL,
[Col173] varchar (255) NULL,
[Col174] varchar (255) NULL,
[Col175] varchar (255) NULL,
[Col176] varchar (255) NULL,
[Col177] varchar (255) NULL,
[Col178] varchar (255) NULL,
[Col179] varchar (255) NULL,
[Col180] varchar (255) NULL,
[Col181] varchar (255) NULL,
[Col182] varchar (255) NULL,
[Col183] varchar (255) NULL,
[Col184] varchar (255) NULL,
[Col185] varchar (255) NULL,
[Col186] varchar (255) NULL,
[Col187] varchar (255) NULL,
[Col188] varchar (255) NULL,
[Col189] varchar (255) NULL,
[Col190] varchar (255) NULL,
[Col191] varchar (255) NULL,
[Col192] varchar (255) NULL,
[Col193] varchar (255) NULL,
[Col194] varchar (255) NULL,
[Col195] varchar (255) NULL,
[Col196] varchar (255) NULL,
[Col197] varchar (255) NULL,
[Col198] varchar (255) NULL,
[Col199] varchar (255) NULL,
[Col200] varchar (255) NULL,
[Col201] varchar (255) NULL,
[Col202] varchar (255) NULL,
[Col203] varchar (255) NULL,
[Col204] varchar (255) NULL,
[Col205] varchar (255) NULL,
[Col206] varchar (255) NULL,
[Col207] varchar (255) NULL,
[Col208] varchar (255) NULL,
[Col209] varchar (255) NULL,
[Col210] varchar (255) NULL,
[Col211] varchar (255) NULL,
[Col212] varchar (255) NULL,
[Col213] varchar (255) NULL,
[Col214] varchar (255) NULL,
[Col215] varchar (255) NULL,
[Col216] varchar (255) NULL,
[Col217] varchar (255) NULL,
[Col218] varchar (255) NULL,
[Col219] varchar (255) NULL,
[Col220] varchar (255) NULL,
[Col221] varchar (255) NULL,
[Col222] varchar (255) NULL,
[Col223] varchar (255) NULL,
[Col224] varchar (255) NULL,
[Col225] varchar (255) NULL,
[Col226] varchar (255) NULL,
[Col227] varchar (255) NULL,
[Col228] varchar (255) NULL,
[Col229] varchar (255) NULL,
[Col230] varchar (255) NULL,
[Col231] varchar (255) NULL,
[Col232] varchar (255) NULL,
[Col233] varchar (255) NULL,
[Col234] varchar (255) NULL,
[Col235] varchar (255) NULL,
[Col236] varchar (255) NULL,
[Col237] varchar (255) NULL,
[Col238] varchar (255) NULL,
[Col239] varchar (255) NULL,
[Col240] varchar (255) NULL,
[Col241] varchar (255) NULL,
[Col242] varchar (255) NULL,
[Col243] varchar (255) NULL,
[Col244] varchar (255) NULL,
[Col245] varchar (255) NULL,
[Col246] varchar (255) NULL,
[Col247] varchar (255) NULL,
[Col248] varchar (255) NULL,
[Col249] varchar (255) NULL,
[Col250] varchar (255) NULL,
[Col251] varchar (255) NULL,
[Col252] varchar (255) NULL,
[Col253] varchar (255) NULL,
[Col254] varchar (255) NULL,
[Col255] varchar (255) NULL,
[Col256] varchar (255) NULL,
[Col257] varchar (255) NULL,
[Col258] varchar (255) NULL,
[Col259] varchar (255) NULL,
[Col260] varchar (255) NULL,
[Col261] varchar (255) NULL,
[Col262] varchar (255) NULL,
[Col263] varchar (255) NULL,
[Col264] varchar (255) NULL,
[Col265] varchar (255) NULL,
[Col266] varchar (255) NULL,
[Col267] varchar (255) NULL,
[Col268] varchar (255) NULL,
[Col269] varchar (255) NULL,
[Col270] varchar (255) NULL,
[Col271] varchar (255) NULL,
[Col272] varchar (255) NULL,
[Col273] varchar (255) NULL,
[Col274] varchar (255) NULL,
[Col275] varchar (255) NULL,
[Col276] varchar (255) NULL,
[Col277] varchar (255) NULL,
[Col278] varchar (255) NULL,
[Col279] varchar (255) NULL,
[Col280] varchar (255) NULL,
[Col281] varchar (255) NULL,
[Col282] varchar (255) NULL,
[Col283] varchar (255) NULL,
[Col284] varchar (255) NULL,
[Col285] varchar (255) NULL,
[Col286] varchar (255) NULL,
[Col287] varchar (255) NULL,
[Col288] varchar (255) NULL,
[Col289] varchar (255) NULL,
[Col290] varchar (255) NULL,
[Col291] varchar (255) NULL,
[Col292] varchar (255) NULL,
[Col293] varchar (255) NULL,
[Col294] varchar (255) NULL,
[Col295] varchar (255) NULL,
[Col296] varchar (255) NULL,
[Col297] varchar (255) NULL,
[Col298] varchar (255) NULL,
[Col299] varchar (255) NULL,
[Col300] varchar (255) NULL,
[Col301] varchar (255) NULL,
[Col302] varchar (255) NULL,
[Col303] varchar (255) NULL,
[Col304] varchar (255) NULL,
[Col305] varchar (255) NULL,
[Col306] varchar (255) NULL,
[Col307] varchar (255) NULL,
[Col308] varchar (255) NULL,
[Col309] varchar (255) NULL,
[Col310] varchar (255) NULL,
[Col311] varchar (255) NULL,
[Col312] varchar (255) NULL,
[Col313] varchar (255) NULL,
[Col314] varchar (255) NULL,
[Col315] varchar (255) NULL,
[Col316] varchar (255) NULL,
[Col317] varchar (255) NULL,
[Col318] varchar (255) NULL,
[Col319] varchar (255) NULL,
[Col320] varchar (255) NULL,
[Col321] varchar (255) NULL,
[Col322] varchar (255) NULL,
[Col323] varchar (255) NULL,
[Col324] varchar (255) NULL,
[Col325] varchar (255) NULL,
[Col326] varchar (255) NULL,
[Col327] varchar (255) NULL,
[Col328] varchar (255) NULL,
[Col329] varchar (255) NULL,
[Col330] varchar (255) NULL,
[Col331] varchar (255) NULL,
[Col332] varchar (255) NULL,
[Col333] varchar (255) NULL,
[Col334] varchar (255) NULL,
[Col335] varchar (255) NULL,
[Col336] varchar (255) NULL,
[Col337] varchar (255) NULL,
[Col338] varchar (255) NULL,
[Col339] varchar (255) NULL,
[Col340] varchar (255) NULL,
[Col341] varchar (255) NULL,
[Col342] varchar (255) NULL,
[Col343] varchar (255) NULL,
[Col344] varchar (255) NULL,
[Col345] varchar (255) NULL,
[Col346] varchar (255) NULL,
[Col347] varchar (255) NULL,
[Col348] varchar (255) NULL,
[Col349] varchar (255) NULL,
[Col350] varchar (255) NULL,
[Col351] varchar (255) NULL,
[Col352] varchar (255) NULL,
[Col353] varchar (255) NULL,
[Col354] varchar (255) NULL,
[Col355] varchar (255) NULL,
[Col356] varchar (255) NULL,
[Col357] varchar (255) NULL,
[Col358] varchar (255) NULL,
[Col359] varchar (255) NULL,
[Col360] varchar (255) NULL,
[Col361] varchar (255) NULL,
[Col362] varchar (255) NULL,
[Col363] varchar (255) NULL,
[Col364] varchar (255) NULL,
[Col365] varchar (255) NULL,
[Col366] varchar (255) NULL,
[Col367] varchar (255) NULL,
[Col368] varchar (255) NULL,
[Col369] varchar (255) NULL,
[Col370] varchar (255) NULL,
[Col371] varchar (255) NULL,
[Col372] varchar (255) NULL,
[Col373] varchar (255) NULL,
[Col374] varchar (255) NULL,
[Col375] varchar (255) NULL,
[Col376] varchar (255) NULL,
[Col377] varchar (255) NULL,
[Col378] varchar (255) NULL,
[Col379] varchar (255) NULL,
[Col380] varchar (255) NULL,
[Col381] varchar (255) NULL,
[Col382] varchar (255) NULL,
[Col383] varchar (255) NULL,
[Col384] varchar (255) NULL,
[Col385] varchar (255) NULL,
[Col386] varchar (255) NULL,
[Col387] varchar (255) NULL,
[Col388] varchar (255) NULL,
[Col389] varchar (255) NULL,
[Col390] varchar (255) NULL,
[Col391] varchar (255) NULL,
[Col392] varchar (255) NULL,
[Col393] varchar (255) NULL,
[Col394] varchar (255) NULL,
[Col395] varchar (255) NULL,
[Col396] varchar (255) NULL,
[Col397] varchar (255) NULL,
[Col398] varchar (255) NULL,
[Col399] varchar (255) NULL,
[Col400] varchar (255) NULL,
[Col401] varchar (255) NULL,
[Col402] varchar (255) NULL,
[Col403] varchar (255) NULL,
[Col404] varchar (255) NULL,
[Col405] varchar (255) NULL,
[Col406] varchar (255) NULL,
[Col407] varchar (255) NULL,
[Col408] varchar (255) NULL,
[Col409] varchar (255) NULL,
[Col410] varchar (255) NULL,
[Col411] varchar (255) NULL,
[Col412] varchar (255) NULL,
[Col413] varchar (255) NULL,
[Col414] varchar (255) NULL,
[Col415] varchar (255) NULL,
[Col416] varchar (255) NULL,
[Col417] varchar (255) NULL,
[Col418] varchar (255) NULL,
[Col419] varchar (255) NULL,
[Col420] varchar (255) NULL,
[Col421] varchar (255) NULL,
[Col422] varchar (255) NULL,
[Col423] varchar (255) NULL,
[Col424] varchar (255) NULL,
[Col425] varchar (255) NULL,
[Col426] varchar (255) NULL,
[Col427] varchar (255) NULL,
[Col428] varchar (255) NULL,
[Col429] varchar (255) NULL,
[Col430] varchar (255) NULL,
[Col431] varchar (255) NULL,
[Col432] varchar (255) NULL,
[Col433] varchar (255) NULL,
[Col434] varchar (255) NULL,
[Col435] varchar (255) NULL,
[Col436] varchar (255) NULL,
[Col437] varchar (255) NULL,
[Col438] varchar (255) NULL,
[Col439] varchar (255) NULL,
[Col440] varchar (255) NULL,
[Col441] varchar (255) NULL,
[Col442] varchar (255) NULL,
[Col443] varchar (255) NULL,
[Col444] varchar (255) NULL,
[Col445] varchar (255) NULL,
[Col446] varchar (255) NULL,
[Col447] varchar (255) NULL,
[Col448] varchar (255) NULL,
[Col449] varchar (255) NULL,
[Col450] varchar (255) NULL,
[Col451] varchar (255) NULL,
[Col452] varchar (255) NULL,
[Col453] varchar (255) NULL,
[Col454] varchar (255) NULL,
[Col455] varchar (255) NULL,
[Col456] varchar (255) NULL,
[Col457] varchar (255) NULL,
[Col458] varchar (255) NULL,
[Col459] varchar (255) NULL,
[Col460] varchar (255) NULL,
[Col461] varchar (255) NULL,
[Col462] varchar (255) NULL,
[Col463] varchar (255) NULL,
[Col464] varchar (255) NULL,
[Col465] varchar (255) NULL,
[Col466] varchar (255) NULL,
[Col467] varchar (255) NULL,
[Col468] varchar (255) NULL,
[Col469] varchar (255) NULL,
[Col470] varchar (255) NULL,
[Col471] varchar (255) NULL,
[Col472] varchar (255) NULL,
[Col473] varchar (255) NULL,
[Col474] varchar (255) NULL,
[Col475] varchar (255) NULL,
[Col476] varchar (255) NULL,
[Col477] varchar (255) NULL,
[Col478] varchar (255) NULL,
[Col479] varchar (255) NULL,
[Col480] varchar (255) NULL,
[Col481] varchar (255) NULL,
[Col482] varchar (255) NULL,
[Col483] varchar (255) NULL,
[Col484] varchar (255) NULL,
[Col485] varchar (255) NULL,
[Col486] varchar (255) NULL,
[Col487] varchar (255) NULL,
[Col488] varchar (255) NULL,
[Col489] varchar (255) NULL,
[Col490] varchar (255) NULL,
[Col491] varchar (255) NULL,
[Col492] varchar (255) NULL,
[Col493] varchar (255) NULL,
[Col494] varchar (255) NULL,
[Col495] varchar (255) NULL,
[Col496] varchar (255) NULL,
[Col497] varchar (255) NULL,
[Col498] varchar (255) NULL,
[Col499] varchar (255) NULL,
[Col500] varchar (255) NULL,
[Col501] varchar (255) NULL,
[Col502] varchar (255) NULL,
[Col503] varchar (255) NULL,
[Col504] varchar (255) NULL,
[Col505] varchar (255) NULL,
[Col506] varchar (255) NULL,
[Col507] varchar (255) NULL,
[Col508] varchar (255) NULL,
[Col509] varchar (255) NULL,
[Col510] varchar (255) NULL,
[Col511] varchar (255) NULL,
[Col512] varchar (255) NULL,
[Col513] varchar (255) NULL,
[Col514] varchar (255) NULL,
[Col515] varchar (255) NULL,
[Col516] varchar (255) NULL,
[Col517] varchar (255) NULL,
[Col518] varchar (255) NULL,
[Col519] varchar (255) NULL,
[Col520] varchar (255) NULL,
[Col521] varchar (255) NULL,
[Col522] varchar (255) NULL,
[Col523] varchar (255) NULL,
[Col524] varchar (255) NULL,
[Col525] varchar (255) NULL,
[Col526] varchar (255) NULL,
[Col527] varchar (255) NULL,
[Col528] varchar (255) NULL,
[Col529] varchar (255) NULL,
[Col530] varchar (255) NULL,
[Col531] varchar (255) NULL,
[Col532] varchar (255) NULL,
[Col533] varchar (255) NULL,
[Col534] varchar (255) NULL,
[Col535] varchar (255) NULL,
[Col536] varchar (255) NULL,
[Col537] varchar (255) NULL,
[Col538] varchar (255) NULL,
[Col539] varchar (255) NULL,
[Col540] varchar (255) NULL,
[Col541] varchar (255) NULL,
[Col542] varchar (255) NULL,
[Col543] varchar (255) NULL
)
and DTS is reporting an error like this :
"
Error at destination for row number 13024. Errors encountered so far
in this task: 1.
The statement has been terminated.
Cannot create a row of size 9997 which is greater than the allowable
maximum of 8060.
"
i also tried "nvarchar" , "ntext" ect. but none of them worked. :((
if you need any further information about the proccess please let me
know
i will respond/answer as soon as possible.
thanks a lot again...
Tunc Ovacik|||panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
>...
[Col543] varchar (255) NULL
)
The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.
Quote:
Originally Posted by
i also tried "nvarchar" , "ntext" ect. but none of them worked. :((
If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.
Does your input file really have 543 input fields?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||panic attack wrote:
Quote:
Originally Posted by
Erland Sommarskog wrote:
Quote:
Originally Posted by
panic attack (tunc.ovacik@.gmail.com) writes:
-- SNIP --
Quote:
Originally Posted by
CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
-- SNIP --
Quote:
Originally Posted by
[Col543] varchar (255) NULL
)
-- SNIP --
Hi!
I know I said earlier that you could use long data types, and the table
you're trying to create is in accordance with my statement earlier.
However, I should have added that each column stores a pointer in the
row and the actual data is stored in a different location. If you add
up the sizes of the pointers along with other row data they should be
below the ~8K limit too. I guess I should be more accurate when I say
something in future like those people who speak legal-ese. The DDL
query was really funny to look at, and it's the first time I ever used
the "read more" link on Google Groups.
I would suggest that you partition your tables vertically so you have
some of the columns in one table and the other columns in another table
(...or perhaps more than 2 tables, depending on the sizes... I'm not
really good at the math).
N.I.T.I.N.
PS: I hope I never have to deal with such a monstrosity - a table that
has so many columns. I once had to deal with 36 columns and that was
too much for me as a developer (that was before my days as a DBA). I
split it up into 3 tables though people may say it is less efficient to
have 3 queries instead of one (remember the days when people said you
should use assembly language as the code is smaller & faster?).
PPS: No offence to assembly language developers in the last 'PS'. I
totally respect people who still use assembly, but for me it's just a
little too much source code to think straight - I'd spend a whole hour
doing something that I could do in 15 minutes with VB, Java or C# (when
equipped with the right IDE, of course!).|||ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.
and if you add the other columns the result is 543
as i said i tried various types to get the data in to SQL
nvarchar , varchar , ntext etc.
but none of them worked out.
what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?
NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?
what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?
Tunc
Erland Sommarskog wrote:
Quote:
Originally Posted by
panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
[Col002] varchar (255) NULL,
[Col003] varchar (255) NULL,
...
[Col543] varchar (255) NULL
)
>
The table appears somewhat funny. Does the table really reflect your
business rules? 255 * 543 is 138465 and with a maximum row size of
8060 in SQL Server, this is not like to turn out well.
>
Quote:
Originally Posted by
i also tried "nvarchar" , "ntext" ect. but none of them worked. :((
>
If you tried 543 ntext columns, I can understand why that fails. A ntext
column has a 16-byte point which is in the the row, and the real data is
elsewhere. 543 * 16 is 8688, so you can't have all those text pointers
on a single row.
>
Does your input file really have 543 input fields?
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi again...
partitioning the table is one of the solvation but for our production
system unfortunately it is not proper for use. :((
cause we have lots of clients and if we partition the tables for our
each client there are gonna be enourmus number of tables, so it is not
possible to deal with those number of tables right now...
hence, we need to get the data at once, in one table.
perhaps we can union some columns into one column. but again there will
be some leck of use of the data while manipulating it.
as you can see it seems not good... :((
i hope that erland may advise another solvation about the problem.
or we may upgrade the database to sql server 2005 if it is gonna help
us getting the data into one table without any problems.
i really appreciated for your help.
thanks a lot.
best regards.
Tunc
NiTiN yazdi:
Quote:
Originally Posted by
panic attack wrote:
Quote:
Originally Posted by
Erland Sommarskog wrote:
Quote:
Originally Posted by
panic attack (tunc.ovacik@.gmail.com) writes:
>
-- SNIP --
>
Quote:
Originally Posted by
CREATE TABLE [nwind].[dbo].[DDD] (
[Col001] varchar (255) NULL,
>
-- SNIP --
>
Quote:
Originally Posted by
[Col543] varchar (255) NULL
)
>
-- SNIP --
>
>
Hi!
>
I know I said earlier that you could use long data types, and the table
you're trying to create is in accordance with my statement earlier.
However, I should have added that each column stores a pointer in the
row and the actual data is stored in a different location. If you add
up the sizes of the pointers along with other row data they should be
below the ~8K limit too. I guess I should be more accurate when I say
something in future like those people who speak legal-ese. The DDL
query was really funny to look at, and it's the first time I ever used
the "read more" link on Google Groups.
>
I would suggest that you partition your tables vertically so you have
some of the columns in one table and the other columns in another table
(...or perhaps more than 2 tables, depending on the sizes... I'm not
really good at the math).
>
N.I.T.I.N.
>
PS: I hope I never have to deal with such a monstrosity - a table that
has so many columns. I once had to deal with 36 columns and that was
too much for me as a developer (that was before my days as a DBA). I
split it up into 3 tables though people may say it is less efficient to
have 3 queries instead of one (remember the days when people said you
should use assembly language as the code is smaller & faster?).
>
PPS: No offence to assembly language developers in the last 'PS'. I
totally respect people who still use assembly, but for me it's just a
little too much source code to think straight - I'd spend a whole hour
doing something that I could do in 15 minutes with VB, Java or C# (when
equipped with the right IDE, of course!).|||panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.
That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?
Quote:
Originally Posted by
what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?
As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.
Quote:
Originally Posted by
NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?
No. What matters is the actual row size, not the possible max.
Quote:
Originally Posted by
what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?
No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks for your fast answers.
there is one last thing that i need to ask...!!
now i decided to partition the data vertically
at this point there is one thing i need to ask...
now here is the case :
after partitioning the table it is gonna look like this:
table 1
---------------
column1 column2 ... column250
record1 record2 ... record250
table2
----------------
column1 column2 ... column250
record1 record2 ... record250
at this point i need to combine these tables( mentioned above)
vertically right?
how am i gonna do the combine operation after partitioning the table
into 2 or 3?
i tried to combine them by using "UNION" operator but i guess it works
for combining the tables horizontally.
thanks a lot
best regards.
tunc ovacik
Erland Sommarskog yazdi:
Quote:
Originally Posted by
panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
ofcourse it has 543 columns :)))
the data includes records for about 6 years, 20 quarters and 60 months
back data and for each period it has 6 parameters and some other
columns info(text).
so if you do the math;
(6 * 6) + ( 20 * 6 ) + ( 60 * 6 ) = 516 columns.
>
That sounds like 516 rows rows to me. Not 516 columns. At least with a
proper data model. Or this a staging table?
>
Quote:
Originally Posted by
what do you think Erland? do we have a chance to get over this problem?
or it is not possible to get the data into SQL Server 2000?
>
As NiTiN said, you will have to split the table in two vertically. Note
that it does not have to affect queries, as you can construct views that
combine them. You would then have to use a format file to make it possible
to only selected columns.
>
Quote:
Originally Posted by
NOTE : By the way there is another data that we are importing to Sql
Server 2000.
and it has 124 columns. no problems occur while getting the data into
Sql Server 2000. if we apply the same logic as you did ,
124 * 255 = 31620
so it is also bigger than 8060. but we are doing the operation without
any problems.
it seems that there is a contradiction doesnt it?
>
No. What matters is the actual row size, not the possible max.
>
Quote:
Originally Posted by
what about SQL Server 2005? is there any limitation at sql server 2005
about the row size?
>
No. SQL 2005 is yet another option. SQL 2005 permits rows to span multiple
pages.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 4 Aug 2006 01:17:45 -0700, "panic attack" <tunc.ovacik@.gmail.com>
wrote:
Quote:
Originally Posted by
>partitioning the table is one of the solvation but for our production
>system unfortunately it is not proper for use. :((
>cause we have lots of clients and if we partition the tables for our
>each client there are gonna be enourmus number of tables, so it is not
>possible to deal with those number of tables right now...
The idea was not to partition the table by client, but to normalize it
so that the time periods are rows, not columns (for one example.)
My suggestion is to define multiple staging tables, each with a subset
of the columns. All would have to include the key column(s), then
each would include a different part of the rest. One data import for
each table, of course, selective on columns. Then when the data is
in, JOIN on the keys.
Roy Harvey
Beacon Falls, CT|||panic attack (tunc.ovacik@.gmail.com) writes:
Quote:
Originally Posted by
thanks for your fast answers.
there is one last thing that i need to ask...!!
now i decided to partition the data vertically
at this point there is one thing i need to ask...
>
now here is the case :
>
after partitioning the table it is gonna look like this:
>
table 1
---------------
column1 column2 ... column250
record1 record2 ... record250
>
>
table2
----------------
column1 column2 ... column250
record1 record2 ... record250
>
at this point i need to combine these tables( mentioned above)
vertically right?
>
how am i gonna do the combine operation after partitioning the table
into 2 or 3?
Hopefully there is a key in the data you import. Else you are in dire
straits. Say that columns 1 and 2 are the keys. Then you could define
a view as:
CREATE VIEW united AS
SELECT a.col1, a.col2, ... a.col250,
b.col251, ... b.col543
FROM tbl1 a
JOIN tbl2 b ON a.col1 = b.col1
AND a.col2 = b.col2
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Having issue inserting large text colum into DB
I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs
I have set the colum type in the table to text
I have set the table option for text in row to on
I have set the table option for text in row to 1000
But it is still chopping the text at the 256 char mark on insert.
Anyone have any ideas ?? This is SQL 2000.
ChrisAre you sure it does? How do you check for the length of the inserted value? By doing SELECT?
Try this:
select datalength(<your_text_field>) from <your_table>
And why do you need TEXT IN ROW setting? Are you searching on that field? If that's the case, - you should implement Full-Text Search.|||In Query Analyzer:
1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column
If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!
-PatP|||In Query Analyzer:
1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column
If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!
-PatP
That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?|||That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?You did fine, as far as giving them what they needed to figure out the answer. I just like my solution better because then they can SEE the answer, which is often better than being able to deduce it.
-PatP
This colum is about 800 chars longs
I have set the colum type in the table to text
I have set the table option for text in row to on
I have set the table option for text in row to 1000
But it is still chopping the text at the 256 char mark on insert.
Anyone have any ideas ?? This is SQL 2000.
ChrisAre you sure it does? How do you check for the length of the inserted value? By doing SELECT?
Try this:
select datalength(<your_text_field>) from <your_table>
And why do you need TEXT IN ROW setting? Are you searching on that field? If that's the case, - you should implement Full-Text Search.|||In Query Analyzer:
1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column
If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!
-PatP|||In Query Analyzer:
1) press Shift-Ctrl-O to bring up the Options dialog.
2) Click on the Results tab
3) Check the value of the Maximum Characters per Column
If it is too small, make it larger, but keep in mind that this is a VERY RAM expensive operation in the GUI. Don't make it any larger than 255 unless you really need it!
-PatP
That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?|||That's why I suggested to use DATALENGTH, because it does not rely on this setting. Besides, what if the value that is inserted greater than 8192? Or you'd think that you inserted 8192 characters?You did fine, as far as giving them what they needed to figure out the answer. I just like my solution better because then they can SEE the answer, which is often better than being able to deduce it.
-PatP
Monday, March 26, 2012
Having a report generate with a rtf for data entry
Is there a way to have a report generate so that there is an 'empty' rich
text box for someone to add notes, comments, text to the report after it
generates?
For example, if i have a report generate out to a shared directory, i want
to allow someone to go 'update' that report by adding explanatory bullets to
the report.
any suggestions or direction is welcomed.
Thx.Sounds like you will have to write a custom report exentension control.
This control will render to HTML and you can render the specific HTML you
need to for data entry and postbacks.
Check out http://msdn2.microsoft.com/en-us/library/ms154606(SQL.90).aspx for
information on rendering extensions. I have not had to write any yet but it
looks like you can specify an HTML specific rendering extension.
Craig
"TroyS" <TroyS@.discussions.microsoft.com> wrote in message
news:C0718935-8D1F-4058-876C-764387F4360D@.microsoft.com...
> Is there a way to have a report generate so that there is an 'empty' rich
> text box for someone to add notes, comments, text to the report after it
> generates?
> For example, if i have a report generate out to a shared directory, i want
> to allow someone to go 'update' that report by adding explanatory bullets
> to
> the report.
> any suggestions or direction is welcomed.
> Thx.
text box for someone to add notes, comments, text to the report after it
generates?
For example, if i have a report generate out to a shared directory, i want
to allow someone to go 'update' that report by adding explanatory bullets to
the report.
any suggestions or direction is welcomed.
Thx.Sounds like you will have to write a custom report exentension control.
This control will render to HTML and you can render the specific HTML you
need to for data entry and postbacks.
Check out http://msdn2.microsoft.com/en-us/library/ms154606(SQL.90).aspx for
information on rendering extensions. I have not had to write any yet but it
looks like you can specify an HTML specific rendering extension.
Craig
"TroyS" <TroyS@.discussions.microsoft.com> wrote in message
news:C0718935-8D1F-4058-876C-764387F4360D@.microsoft.com...
> Is there a way to have a report generate so that there is an 'empty' rich
> text box for someone to add notes, comments, text to the report after it
> generates?
> For example, if i have a report generate out to a shared directory, i want
> to allow someone to go 'update' that report by adding explanatory bullets
> to
> the report.
> any suggestions or direction is welcomed.
> Thx.
Friday, March 23, 2012
Have DTS Package prompt for a file name
I have a user in the IT department that wants a process to take his text file and import it into a SQL Server table. Simple enough with a DTS package.
The rub is that he want's to execute the package and have it prompt him at that point for where the file resides. I tried to get him to go into the DTS pacakge and update the connection, but he doesn't want to do it that way. I suggested renaming the file to a common name to be used each time the application runs, and he wasn't interested in that solution either.
Any help you can give would be greatly appreciated. Thanks!
Have a look at this:
http://www.sqldts.com/default.aspx?226
Wednesday, March 21, 2012
Has anyone tried...
I currently have a DTS package that takes a text file source and transforms the data into a table. The transformation does a lookup on a product_code column (char(8)) to transform it to the correct product_id (int) for our system.
I've recently setup a view that matches the file layout and has an instead of trigger that inserts into the target table. It does this by joining the inserted table to a translation table to get the correct product_id for the insert. Instead of a DTS package I created a proc that BULK INSERTS the file into the view.
The view approach is running over twice as fast as the DTS package. Is this approach common or has anyone else tried it. Any feedback would be appreciated.I don't think it is uncommon to see that kind of performance gain. DTS is very good at doing complex things, but it can't compete with BULK INSERT or BCP for doing simple things. The trick is to figure out which tool is best for a given job.
-PatP|||I forgot to mention the file contains 9.9 million records with 7 columns. The DTS package runs in 20 minutes vs. 8 for the view. BCP'ing the file directly into a table with no translation takes 3 minutes.|||DTS is very good at doing complex things...
-PatP
I'm still waiting for my burger and fries....
There's is NOTHING that will beat bcp load to staging table and set process t-sql to fix whatever it is you need to fix...especially if the file to be loaded is in native format...|||Try loading a Notes log file into SQL Server doing LDAP lookups for the VPN derived data. DTS can do it nicely using the API, BCP can't get there from here without something that can at least produce a file first, and nothing I've seen will do that very well.
The jobs that BCP can do, it does VERY well. I don't think that anything can be faster than BCP. The jobs that BCP can't do... it can't do.
-PatP
I've recently setup a view that matches the file layout and has an instead of trigger that inserts into the target table. It does this by joining the inserted table to a translation table to get the correct product_id for the insert. Instead of a DTS package I created a proc that BULK INSERTS the file into the view.
The view approach is running over twice as fast as the DTS package. Is this approach common or has anyone else tried it. Any feedback would be appreciated.I don't think it is uncommon to see that kind of performance gain. DTS is very good at doing complex things, but it can't compete with BULK INSERT or BCP for doing simple things. The trick is to figure out which tool is best for a given job.
-PatP|||I forgot to mention the file contains 9.9 million records with 7 columns. The DTS package runs in 20 minutes vs. 8 for the view. BCP'ing the file directly into a table with no translation takes 3 minutes.|||DTS is very good at doing complex things...
-PatP
I'm still waiting for my burger and fries....
There's is NOTHING that will beat bcp load to staging table and set process t-sql to fix whatever it is you need to fix...especially if the file to be loaded is in native format...|||Try loading a Notes log file into SQL Server doing LDAP lookups for the VPN derived data. DTS can do it nicely using the API, BCP can't get there from here without something that can at least produce a file first, and nothing I've seen will do that very well.
The jobs that BCP can do, it does VERY well. I don't think that anything can be faster than BCP. The jobs that BCP can't do... it can't do.
-PatP
Monday, February 27, 2012
Handling NULLS - help!
I would like to display some text in a textbox conditional on the null/not
null value of a database field (called "ID").
I have tried various versions of the following:
=iif(len(trim(Fields!ID.Value))<1 OR (Fields!ID.Value IS
system.dbnull.value),"ID is null","ID is not null")
but I still get this error:
"The query returned no rows for the data set. The expression therefore
evaluates to null."
any suggestions?Try =IIF(IsNothing(Fields!ID.Value), ...|||Thanks Rose - that worked perfectly :)
null value of a database field (called "ID").
I have tried various versions of the following:
=iif(len(trim(Fields!ID.Value))<1 OR (Fields!ID.Value IS
system.dbnull.value),"ID is null","ID is not null")
but I still get this error:
"The query returned no rows for the data set. The expression therefore
evaluates to null."
any suggestions?Try =IIF(IsNothing(Fields!ID.Value), ...|||Thanks Rose - that worked perfectly :)
Friday, February 24, 2012
Handle TEXT FIELDS in Recordset Using ASP
hi there,
I have a problem when i tried to get data from mssql server using recordset in asp language. For example, i have table containing 5 fields. One of them is text field and the rest are varchar.
here i list the table structure (table name: Info):
Field Name Field Type Length
Name varchar 50
Gender varchar 50
Address text 16
Status varchar 50
Age varchar 50
Sample data inside table info.
Name Gender Address Status Age
Ali male MAL S 26
this is my code:
set rs = server.createobject ("adodb.recordset")
rs.open "select * from info", connectionstring
if not rs.eof then
name = rs("name")
gender = rs("gender")
address = rs("address")
status = rs("status")
age = rs("age")
end if
rs.close
what happend here, i managed to get name = Ali, gender = male but for address, status and age is nothing, just empty string.What was really happened, does anyone knows this problem.
If I change
rs.open "select * from info", connectionstring
to
rs.open "select * from info", connectionstring, 3
i managed to get all the info but i have to do a lot of code change for my program. Is there any solution to this problem?
i'm using win server 2003 and mssql server 2000
thanks for cooperation from you guys.
please accept my apologize for my bad language.
regards
kharulli
Firts you need to move all your BLOB-type fields to the end of the list of your fields in a selection list of the SELECT statement. It means your SELECT should look like
SELECT [Name], Gender , Status, Age, Address FROM ....
Now, after you query data, you need to get GetChunk method of the field to read the data in a case if it is longer than 255 characters. Otherwise you could use Value property
|||thanks VMazur for quick replies,
I tried and its works. But it seems i must do a lot code conversion to overcome this problem. Its this MSSQL weaknesses or blob-type just work like this? Does MSSQL have a solution to this problem. Sorry to cause trouble to your guys in answering my post.
best regards
kharulli.
|||I believe this is how provider handles BLOB fields. They have to be at the end of your selection. Reason for this is, probably, memory allocation|||thanks for the info. it helped me a lot.
Subscribe to:
Posts (Atom)