hi when I try to export data to a fixed width txt file I get the following error:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "postcode" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
even though my sql for postcode is LEFT(postcode,8) and the field length is 8 !
do you now why ?
Hi all.
I'm trying to export some tables in fixed width format for a client that needs it this way. The first time I did this, using Import/Export in Enterprise Manager, integer fields were 12 chars long. However, when I saved this as a DTS and ran it again later, it made integers 4 chars in the text file (which matches the lengths of the datatypes you see in enterprise manager.)
Why is this difference there? What do I need to do differently? Also, is there a list of how wide each data type is when exported as fixed delimited text?
thanks,
Craig
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID Name Course
1 A Java
1 A C++
2 B Java
2 B SQL Server
2 B .Net
2 B SAP
3 C Oracle
My Output should look like below...
ID Name Course(1) Course(2) Course(3) Course(4)
1 A Java C++
2 B Java SQL Server .Net SAP
3 C Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
VALUES (1,'A','Java'),
(1,'A','C++'),
(2,'B','Java'),
(2,'B','SQL Server'),
(2,'B','.Net'),
(2,'B','SAP'),
(3,'C','Oracle')
Hi,
Is it possible to fix the max width of the columns ?? Sometime the report only generates 1 or 2 columns and it looks a bit odd as they're stretched to the width of the page.
Also, when we have a large number of culumns, say 20 odd, the legend text get's real squashed up and un readable, are there any tips/tricks so i can have more flexibility over what's displayed ?
thanks
Steve
True story. Thanks. Dont think that fixed or ragged is going to work for me though. File does not seem to be structured.
I have what appears to be a fixed length file that I would like to import using SSIS. However it does not look loike the spacing between fields is constant. Is there a way to say from character 1 to 10 is X, and 11 to 15 is Y, etc etc? Below is a couple of rows that all pertain to the same record.
01000000225672101242253 55232101242253 5TKTT / 0101 FFVV 5235MCYSNR/CE MUSSETT/BRIAN.A.MR 20071017U7700002277000022 U00000000ZAJNB JNB ZA 00000000 00000000 00000000 SITII Y06 405
02000000235672101242253 55232101242253 5ICE 20071017 1407000017OCT 17OCT
03000000245672101242253 55232101242253 5 PLZPLZ ZAR 1000 ZAR 72ZAZAR 140ZVZAR 562XTZAR 1774 ADT PENALTY APPLIES
04000000255672101242253 55232101242253 50000017740000000100000ZAR200000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 000000000000000000000000000000000ZA 00000007200ZV 00000014000EV 00000001600YR 00000054600 00000000000 00000000000 00000000000 00000000000 00000000000
05000000265672101242253 55232101242253 51 OPLZ DUR CE CE 0633 0633 K K 18OCT18OCT18OCT1625 1740 OK KOW 2 DUR PLZ CE CE 0602 0602 B B 20OCT20OCT20OCT1000 1115 OK BOW
07000000275672101242253 55232101242253 5CCIK5221 000001774005221591015192982 0308M067781 00000000000 00000000000 00000000000
08000000285672101242253 55232101242253 509PLZ CE DUR550CE PLZ450ZAR1000 END CE XT16EV546YR 1 2 3 4
What do you reckon is the best option? SUBSTRING in a Dervied Column? Below is an example of the text.
Hello,
Here is the code as it now stands:
select TECHN, RESCODE,
(CASE
WHEN TECHN IS NOT NULL AND RESCODE IS NOT NULL AND COMPLET IS NULL THEN 'TOTAL RESCODE'
when TECHN IS NOT NULL AND RESCODE IS NULL THEN 'TOTAL TECH'
ELSE CONVERT(VARCHAR, CAST(COMPLETE AS DATETIME), 105) END) COMPLETE, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAMT) CommissionAUT
FROM
(select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 1, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser.dbo.Parser_ocr
Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 1, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 4, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 7, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT (DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 10, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 13, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 16, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;
Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'group'.
Could the error message be in the INNER JOIN?
What do I need to check for next.
Thanks for eveyrone's help so far.
Kurt
I am able to get reports going with tables sized properly. They look fine on the ReportServer website and I adjust the column widths so that the headings and data look nice. When I set up a subscription to be delivered by "Report Server E-Mail," though, the table formattings get completely distorted.
In particular, I have two tables, with some column headers being two short words (e.g. Max Height). When rendering on the site, I adjust the columns so the full column header is visible on one line. When I receive the email and read it in Outlook, the header row is now about twice as tall and everything is scrunched together. Both the headings and the data in the fields do not format the same as on the website.
The two tables tend to actually have the exact same width in the email version, although occasionally they are a little different (in the web version one is about half as wide as the other). I have tried just making the columns bigger and that has not worked. I've tried making the font sizes smaller, which didn't work. If I do that, leaving the columns the same width, the email version just gets scrunched into a smaller area with the same text-wrapping problems.
If I open the email in a browser (in a web mail interface) the report renders perfectly as on the site.
I have almost all the default settings, and haven't been messing around with page sizes and things like this (except after, to see if that would fix the problem).
Any ideas, similar experiences, or suggestions? If there is a book I should read or any reference you could point me to in order to figure this out would be helpful. I haven't been able to understand this either using web searches or the two SQL reporting services books I have.
The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.
View 1 Replies View RelatedHi there. I work in a support department and on great occasion (such as this morning), I am RASd in to a client and try running a SQL trace, only to receive an error when setting it up, 'In order to run a trace against SQL Server you have to be amember of sysadmin fixed server role.'
Today, I even called their DBA and asked him if he could set our userid up with the proper permissions to all us to run traces (I'm debugging a RTE). He stated that he was unfamiliar with the error and didn't know where to assign us to resolve this problem.
Does anyone know how to resolve this?
Many thx.
I trying to import from fixed width text files that may contain one or more empty rows at the bottom of the file (where an empty row is {CR}{LF}). By experimenting, I found it runs successfully with up to 32 blank rows, but with any more I get this:
Warning: 0x8020200F at Copy TBMO files to DW, TBMO text file source [1]: There is a partial row at the end of the file.
Error: 0xC0047038 at Copy TBMO files to DW, DTS.Pipeline: The PrimeOutput method on component "TBMO text file source" (1) returned error code 0x80020005. 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.
Error: 0xC0047021 at Copy TBMO files to DW, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Copy TBMO files to DW, DTS.Pipeline: 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.
Error: 0xC0047021 at Copy TBMO files to DW, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
The data rows look like this (256 wide):
104 8690 100540 0.00 196.27 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Any suggestions on how to bypass these lines? I tried configuring error output, but that didn't seem to help.
Thanks,
I have a report with a fixed header that works properly when the query returns results (or fixedheader = false). However, when the result set is empty, I get tenacious client-side "object required" errors. Obviously I can either turn the fixedheaders off or disable debugging in IE, but is there a Microsoft fix for this problem?
View 1 Replies View RelatedCan someone from MS tell me if this has been fixed in SP1?
http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx
Thanks
Jamie
Hello Friends
I am very glad to tell all u ppl that i ve got the solution to my problem at last
I ve just posted my problem(few mins back) in which i was getting problem with connecting to the ASP.NET Web application. I have made a new user on sql through SQL Enterprise Manager as <machinename>ASPNET, and my problem gets solved
sorry to bother u all
Regards
BSS
philippe writes "Hello ,
i am just starting with sql...
I have a table: Table_user
Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first
I will appreciate some tips...."
Hi everyone,
I'm trying to make a hierarchy in which some nodes are locked, preventing children nodes from being added.
The hierarchy table points to itself (the parent entry). I want to secure this from the DB side. Initially, no nodes have parents but can attach themselves to one.
I'd like my FK contraint to be like:
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, 1) REFERENCES nde_recursion(hierarchy_id, allow_children)
This is so the parent_id can only be set if that entry allows children.
Here's my approach. Any better ideas? I feel like I'm adding an extra field (parent_allow) when I shouldn't.
CREATE TABLE hierarchy(
hierarchy_idINTIDENTITY(1,1) UNIQUE NOT NULL,
allow_childrenBITDEFAULT 1 NOT NULL,
parent_idINT,-- NULL is for root nodes
parent_allowBITDEFAULT 1 NOT NULL,
CONSTRAINT PK_hierarchy PRIMARY KEY (hierarchy_id, allow_children),
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, parent_allow) REFERENCES nde_recursion(hierarchy_id, allow_children),
CONSTRAINT CK_hierarchy CHECK ((nde_status = 'Normal' AND parent_id IS NULL) OR (nde_status != 'Normal' AND parent_id IS NOT NULL)),
CONSTRAINT CK_hierarchy2 CHECK (parent_allow = 1)
)
Hello!
I have a report that the users reach from an url. In the url I have rc:parameters = false. That part works fine. But when I use rc:parameters = false, the property fixed header doesn't seem to work. If I run the report from the report server it works fine, but not from the url.
Any ideas?
/C
Hi
I have a table in a report which has the FixedHeader property set to true. What happens is that when I try to look for a text using the find option, the found text gets hidden under the header row, which then I have scroll back to see that. The user may think that the text was not found. Any solution to be able to bring the found item to somewhere the user can see?
Thank you
I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get data in 5 decimal points? Your help with example would be appreciated. aspx code: <asp:GridView ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt" OnLoad="GridView1_Load" > <Columns> <asp:TemplateField HeaderText="Show"> <ItemTemplate> <asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" /> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:BoundField DataField="Latitude" HeaderText="Latitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="Longitude" HeaderText="Longitude ( ° )" > <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField>
I have a request to produce a SQL report that will be produced with fixed column width. For example see below Position Element LengthField Format 1-2 Column1 2 Alphanumeric 3-4 Column2 2 Alphanumeric 5-13 Column3 9 Chars Any idea how I can produce the report specified above? Thanks
View 1 Replies View RelatedI have a server that was upgrade to 7 several months ago. The cmptlevel was set to 7 once upgraded. I have a developer group as dbo_owner role on a database and now on certain tables (not all) they can add fields and save. But then go back in and remove the field then try to save they get the error message "- Unable to modify table. ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]SETUSER permission denied, database 'XXX',owner 'dbo'."
View 1 Replies View RelatedI am attempting to build our first set of packages populating or DW. All of our source system primary keys are bigints (on the main tables at least). SSIS seems to have a problem dealing with bigint values, i.e. having to assign variables as doubles rather than int64, having to cast returning bigint values as float, or bigint sproc output parameters as double. It is all a bit messy (and possibly the most frustrating part of SSIS for me) - does anyone know if this problem is due to be fixed in any forthcoming release?
View 2 Replies View Related
I would like to add right number of 0 to make the data length of varchar (14)
For example,
before after
1768 -- > 0000001768.000
23456.78 --> 0000023456.780
123679.876 --> 0000123679.876
what is the best way to do this?
I have table like below
ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)
The output should look like this.. How do this either using a sql or cursor?
011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
Thanks.
I created a package to import records from a fixed length 700 byte text file to a table in a SQL Database. I used the wizard to set it up and note the byte where each column ends. I need to customize the process, as the name of the text file will change each night, so I want to be able to set the file name from the VB front end app.
I have tried modifying the Datasource property of the DTS connection to the flat file, without success. I have also tried setting a global variable for the datasource property in DTS, and assigning that from VB, similarly without luck.
Do I have to create a custom package in code from VB? If so, how do indicate where each column in the text file ends? If I can customize the existing package, is there a specific reference that I need to set in my project that will let me control the value of the global?
TIA
Hi,
I try to use Data Transaction Service (SQL Server 7) to copy information from an Sql Server Table to an text file (fixed field). When I run the process, no problem, the text file is created and a Ok message appears. But when I look in my text file, every time a field was NULL or empty in the source table, the following fields are not aligned !
Somebody knows what the problem ?
Thanks
Vincent
Ps: I'm french so excuse me for my english
What is the best way to force a 2 digit decimal place without rounding?
For example select price*UOM returns
47.1294
3.255
.5
8
.49
What i want to be returned is
47.12
3.25
8.00
.50
.49
Thanks,
Jim
I have a request from a vendor to export data out of my SQL Server 2K database view to a 'flat fixed file'.
What kind of file is this exactly, not a .csv ? Does EM have the capabilities through the DTS wizard, by choosing the output to a text file and fixed width ?
Thank you
I need to write data into a fixed column length file and was wondering the best (most efficient) way to tackle this. For example, the first few pieces of the report I'm working on now would be:
PacketID - Starting position 1, Field length 9
TransactionID - Starting position 10, Field length 9
Group number - Starting position 19, field length 10
PID/SSN - Starting position 29, field length 10
For the PID/SSN, if I have a PID it'll be 10 digits and fill the field length, if I don't I use SSN which is only 9 digits and enter a space as the 10th digit. Obviously if I don't have certain pieces of information I'll just need spaces of the specified length to satisfy the file format. I'm using SQL 2005. Thanks in advance for any help provided.
I have approximately 13 columns. Each Column has a start position and end position.. I created this in a table and defined the position, it's still not working for me.
FiceCode char(6), -- starting position 1, field length 6
StateStudID char(10), -- starting position 7, field length 10
CampusStudID char(10), -- starting position 17, field length 10
LastName char(25), -- starting position 27, field length 25
[Code] .....
I need a text output file that will define each start position.I also used: right(replicate('0',25) + cast(last_name as char(25)), 25) in my sql statement.when I add the first_name, I can't get it to start in position 52.
I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)
I want to fill in this table with two fixed values ??and customer_id from another table.
Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.
Select customer_id from tblMainCustomer where customer_nr like 'P%'
Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.
These fixed values ??are:
100-----Gold
101-----Steel
1002----Super Copper
Example: If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:
customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
I am stuck, how do I do this the best way?
Rob writes "We have had an on-going problem in our production database. It appears to all be index corruption. We've opened a PSS with Microsoft, but I figured I'd post here also. We're getting 604,605 errors as well as DBCC CHECKDB errors. All reports are that either REPAIR_REBUILD or REPAIR_FAST will fix the problem. Since putting the database into single_user mode is not an option at this time, we've just been rebuilding the problem indexes with DBCC DBREINDEX. This typically fixes the problem. Microsoft PSS says they believe that it's hardware problems. We're in the process of updating firmware on our storage device. (MSA 1000) This is a clustered machine. Tonight, our index rebuilds completed successfully, but a DBCC CHECKTABLE failed with 8951 and 8955 errors with the REPAIR_FAST option being supplied. I've rebuilt all the indexes on the table, but the DBCC CHECKTABLE still reports with errors. As a test, I DTS'd a copy of this table to make sure that I would at least be able to run a select * on it. That worked okay. My question is the following: Does the repair option for DBCC CHECKDB/TABLE do something that DBCC DBREINDEX doesn't? Thanks in advance! Rob P.S. This is a Siebel environment. :("
View 3 Replies View RelatedHow do I create a text file with fixed length fields from a SQL table using a stored procedure.
View 4 Replies View Related