Help,a Regular Text File How To Sql 2000 Table Code ?
Apr 5, 2008
help,a regular text file how to sql 2000 table code ?
i have a text file as follow, line with ¡°|¡±and {LF},
8|-000000186075919.|+000000000387820.|2008-03-31|20010423|
9|-000000000003919.|-000000000123620.|2008-03-31|20010123|
8|-000000018623419.|+000000000381230.|2008-05-30|20010423|
i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandate
smalldatetime£¬update smalldatetime£¬after to sql table is as follow,
sign year month trandate update
8 -186075919.000 387820.000 3/31/2008 4/23/2001
9 -3919.000 -123620.000 3/31/2008 1/1/2001
8 -18623419.000 387820.000 5/30/2008 4/23/2001
could you help me how write the sql code ?
View 1 Replies
ADVERTISEMENT
Feb 15, 2008
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
DESIGN:
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD1.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD2.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD3.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD4.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD5.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD6.jpg
RUNTIME:
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD7.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD8.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD9.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD10.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD11.jpg
I can provide more details if needed... but really this is just a basic test.
Any assistance would be appreciated!
View 9 Replies
View Related
Apr 29, 2006
Does anybody have the code to import a text file into a a Sql Server table using the SqlBulkCopy object?
View 1 Replies
View Related
Mar 23, 2006
Hi,I trying to write a select statement that will return each of my salesmen a region code based on a table of post codes using wildcards... eg.MK1 1AA would be matched in the region code table to MK1%SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,rc.POST_CODE, dn.POSTAL_CODEFROM REGIONAL_CODES rc CROSS JOINDEALER_NAW dnWHERE (dn.POSTAL_CODE LIKE rc.POST_CODE)The above statement works BUT there are some post code areas such asour friends in Milton Keynes that are split into two regions... eg MK1is region id 2 and MK10 is region 3.So a dealer with post code MK10 1AA would be matched to both rowsreturning duplicatesPOST_CODE REGION_IDMK1% 2MK10% 3I think the answer would lie in a subquery which returns the ID of theregion with the longest length of the postcode match (e.g.len(POST_CODE) for the rc table... return only the MAX....any ideas????Any help muchos appreciated, and I apologies now for the naming of thedealers name as a reserve word... not me!Ct
View 2 Replies
View Related
Sep 7, 2007
I'm trying to read a text and load the data from the text file and import the data into the database.please help!!
View 2 Replies
View Related
Nov 12, 2007
I'm trying to use the following as an expression in the format of a text box. (Reporting Services 2005)
=System.Text.RegularExpressions.Regex.Replace(First(Fields!JOB_NBR.Value, "main"), "(w{2})(w{4})(w{1})","$1-$2-$3")
however in the edit expression window, I have a red line under the word Replace.
The mouse over tells me it is a "unrecognized identifier"
Any idea's
View 5 Replies
View Related
Jul 20, 2005
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
View 7 Replies
View Related
Oct 17, 2006
Dear forum people,
I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.
I am appreciate every answer.
Thank you.
Ann
View 3 Replies
View Related
Feb 7, 2008
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like:
Dim x as integer = sqlQuery("Select count(*) FROM ...")
Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
View 9 Replies
View Related
May 12, 2008
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
View 2 Replies
View Related
Mar 31, 2008
Hi,
Can anyone please help me on how to export data from SQL server 2000 to text file using C#. I could use bcp command to directly import data, but there are some changes need to be made to some codes from other tables in database and the data to be downloaded is also very huge. probably 10 million records.
View 4 Replies
View Related
Mar 29, 2007
hi all,
While importing into sql server 2000 from a text file i am getting an error message like not enough disk space available to perform this operation but there is enough space (around 18gb). Please advice why this is happenning as my work is stuck
thanks and regards
jk
View 4 Replies
View Related
Oct 15, 2007
hi,
i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files.
the file are named as:
ff_inbound_20071008_1.csv
ff_inbound_20071008_2.csv
where for file ff_inbound_20071008_1.csv:
ff => flat file
inbound => dataset of this csv
20071008 => date
1=> file count
having in mind that they are store in the variable as a full path:
z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv
i need to extract the dataset and the date for each file.
how can i extract these terms from the file name?
many thanks,
nicolas
View 4 Replies
View Related
Jul 20, 2005
Hello,I am receiving a text file that is produced from a mainframe that isout of my control. I am attempting to find a (hopefully clean) way toimport it into a SQL Server database in an automated fashion. I amnot really concerned about how many tables it requires or what theschema looks like as long as the data remains related and ends up inits respective fields (I will probably use scratch tables for this).The data is given to me in a format that is meant to be printed outand read by human eyes (in a text file). The format looks somethinglike this:Begin File:-------------------------------------------------------------------------------1234 1234 1234 1234 XYZ Company 01/01/2003.......More stuff related to XYZ company for a couple of lines ..............(this stuff can easily be parsed by position).......MCARD VISA AMEX DISC-------------------------------------------------------------------------------TOTAL 11111.11 4444.44 5555.55 30.01TRANS FEE .20 .20 .15 .15TRANS AMOUNT 2222.22 888.89 833.33 4.50DISC .0165 .0165 .0365 .0355-------------------------------------------------------------------------------ANOTHER HEADER............More stuff related to XYZ Company................End File:Well, this isn't the exact format, but just an example. The point isthat all of the data in each column is related and should end up inthe same record which is related to the parent record of XYZ Company(or all in a single record in a single table if that is the closest Ican get).Also, the rows are not always present. For example, if TRANS FEEdoesn't apply to anything in the row, then the entire row willcollapse and TRANS AMOUNT would be the next line after TOTAL.I was looking at the bcp utility and dts, but dts doesn't seem to havethe performance capabilities (or reliability for that matter) I amlooking for. Bcp seems like it might work if there is some advancedformatting commands that I can't find in the documentation - Anyone?The best I can come up with is to use a high level language such as C#or VB.NET to parse the text file into another text file that is commadelimited, and then use the bcp utility (or bulk insert) to import itinto SQL Server where I can then use TSQL to manipulate it how I want.I am trying to eliminate the high level language parse and just gostraight from file to database. Does anybody know an easier route?TIA
View 1 Replies
View Related
Feb 21, 2007
Hi everyone, I'm fairly new to sql and right now I am struggling with a script. I am trying to extract data from a normal table into a temporary table, update it in the temporary table, then put it back into the normal table. I'll display my code, let me know what you think, any suggestions are appreciated. Thanks a lot.
Create table scripts (
UserID int,
UserName char(50),
ScrRan char(50),
StartTime datetime default getdate(),
EndTime datetime);
Create table errors (
ID int,
UserName char(50),
UserLogin char(50),
ErrorNumber int,
Message char(100),
TimeOfError datetime default getdate());
declare @error int
declare @msg varchar(100)
declare @startTime datetime
declare @endTime datetime
select @startTime = getDate()
SELECT *
INTO #Temp
FROM Publisher
WHERE pub_Name = 'Scene Publishing'
UPDATE #Temp
SET pub_Name = UPPER(pub_Name)
SELECT *
INTO Publisher
FROM #Temp
--Begins Error Checking Routine
select @error = @@error
IF @error <> 0
BEGIN
select @msg ='error: ' + convert(varchar(7), @error) +
''
insert into errors values (@@SPID, USER, USER_NAME(), @error,
@msg, getDate())
END
ELSE
BEGIN
select @endTime = getDate()
insert into scripts values (@@SPID, SYSTEM_USER, @startTime, @endTime)
END
select * from errors
select * from scripts
lost and loaded.
View 2 Replies
View Related
Jul 20, 2005
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies
View Related
Feb 15, 2008
Hi Experts,
I have to find the latest file in a folder and export data to a table in sql server.
The code should be something that has to be incorporated into a t-sql stored procedure.
The file name would for example abc_defYYYYMMDD.xls.
would i be able to find the latest file in the folder using the the datestamp (YYYYMMDD) in the filename.
Please note i would have files in other format and names with datestamp attached to it, so the code has to pick specific file for which the file name starts with 'abc_def'
and export data to a table.
Any help would be highly appreciated
View 10 Replies
View Related
May 23, 2008
let's say I have this one, no problem:
Code Snippet
CREATE PROCEDURE au_info
@var text
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@var
PRINT CAST(@var as varchar(8000))
-- Use OPENXML to provide rowset consisting of customer data.
--INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/ROOT/Customers')
-- WITH Customers
GO
Code Snippet
EXEC au_info @var = '
<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
but what if I only have the xml text in a temporary table:
Code Snippet
create table #t1 (col1 text)
insert into #t1
Values('<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>')
if i do this:
Code SnippetEXEC au_info @var = (Select col1 from #t1)
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Code SnippetEXEC au_info (Select col1 from #t1)
Server: Msg 201, Level 16, State 3, Procedure au_info, Line 0
Procedure 'au_info' expects parameter '@var', which was not supplied.
View 9 Replies
View Related
Apr 6, 2004
Hello,
I have an access database (access 95 Version7)dumping a delimited text file onto my server. I am then using DTS in SQL 2000 to import the file into a table.
My issue is that each time the DTS runs, it imports the whole text file each time, this is causing duplicate records.
So I created a transformation script as follows :
Function Main()
If DTSSource("counter") <= DTSDestination("counter") Then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("counter") = DTSSource("counter")
DTSDestination("serialdata") = DTSSource("serialdata")
DTSDestination("serialdata1") = DTSSource("serialdata1")
DTSDestination("date") = DTSSource("date")
DTSDestination("time") = DTSSource("time")
Main = DTSTransformStat_OK
End If
End Function
The theory behind the If statement, is if it sees that the counter field is less than or equal to what is there, it will skip the record and move forward. For some reason this is not working.
Does anyone have a workaround or another solution to this problem
View 3 Replies
View Related
Jun 27, 2005
I have a SQL SERVER database which has Articles Table. This table
contains "Description" field which is of type "text". I am trying to
insert 800- 1000 words of data into this field. This data also contains
code snippets. I dont know for some reason it only inserts one or two
lines and thats it. No error is being thrown. I am using multiline
textbox to enter the data into the database. any ideas
It displays something like this:
test 1
By AzamSharp
Creating XML Men // This is very long text. Actually its the whole article but it only displays three words
any ideas !
Thanks,
View 6 Replies
View Related
Jul 27, 2006
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
View 7 Replies
View Related
Jul 23, 2001
I have portions of data coming in as text files containing new records and updates of existing records. The solution I've figured out till yet is to import a portion of data into some intermediate table and then run a stored procedure to migrate the data into the real table.
Any ideas how to do this in a more efficient way?
Thanks in advance,
Asnate
View 3 Replies
View Related
Feb 12, 2001
I am familiar with the MySQL Load Data command to load an external ascii file into a database table, but am having trouble finding a T-Sql command that is equivalent without creating an executable...any help would be appreciated...
View 1 Replies
View Related
Dec 5, 2000
Anbody please help
I am trying to export a text file to a table using enterprise manager
and all tasks
But the process keeps adding strange charater like squares at the end
of each line and also replaces each empty line in the text file with a record in the table with that square type character. I used the following code to delete all rows with that character (as a work around) but no joy. I am losing hope.
Code is
DELETE FROM table1
WHERE column1 = ' '
View 1 Replies
View Related
Dec 8, 2004
How to convert a SQL table into Text file? I have a table and I want to extract the values with the field names above to a text file. The query should also allow me to define the starting position of the fields in the text file.
email: vijay.prasad@in.ibm.com
View 1 Replies
View Related
May 13, 2008
Hello:
I want to copy one table not whole database to text file. How to do it? Using DTS could not allow me to select the specific table.
Thanks,
Snow:rolleyes:
View 3 Replies
View Related
Nov 1, 2007
I do have a comma separated text file.
"firstname","lastname","ssn","dob"
"firstname1","lastname1","ssn1","dob1"
The text file can have 50000 records. What is the best way to upload the file into a table. The table structure looks like:
Table1:
Fname varchar(50)
lname varchar(50)
ssn varchar(50)
dob varchar(50)
Any thoughts???
View 4 Replies
View Related
Nov 27, 2007
Is there an example anywhere of how to output selected fields in a sql table to a text file with fixed length fields. ie pad data out to required length.
View 2 Replies
View Related
Apr 19, 2006
1 4/19/06 abc3100UD:Dcod25-uss1 PAGE 1
REPORT ON xxxx cs PREVIOUS DAY
-
PREV REP
LOAN # BORROWER STAT STAT APP DATE INITIAL ORD DT LOANAMT CLIENTNAME
0 0123454681 xxxx, xxxxxxxxxx 10 9 06/04/05 abc 060418 $310,000.00 ABC temp plus
1 $310,000.00
0523468431 xxxxx, xxxxx xx 14 13 06/04/04 efg 060418 $127,120.00 cAPITAL MANAGEMENT INC
1 $127,120.00
0542616443 xxxxx, xxxx xxxxxx 14 13 05/12/01 hij 060418 $200,000.00 fRIENDS CLUB
1 $200,000.00
0516545461 xxxxx, xxxxxxxx x 205 204 06/03/02 klm 060418 $283,992.00 MICROTECH TECHNOLOGY
0135554455 xxxxx, xxxxx xx 115 114 06/04/04 060418 $230,000.00 ABC TEMP PLUS
2 $513,992.00
0151981313 xxxxxxxx, xxxx xxxxxx 205 204 06/04/05 nop 060418 $80,000.00 INTERNAL REFERRAL
1 $80,000.00
0111111111 xxxxx, xxxxx 115 114 06/03/05 qrs 060418 $86,800.00 gMA INC
0222222222 xxxx, xxxxxxxxxx 115 114 06/04/03 060418 $156,720.00 INTERNAL
0333333333 xxxxx, xxxxxx 205 204 06/04/03 060418 $156,720.00 HOME
3 $400,240.00
TOTAL 9 $8,005,672.00
1 abc351Uab:cod5K-SUM
-
5a
INITIAL COUNT
0 ABC 1
EFG 1
HIJ 1
KLM 2
NOP 1
QRS 3
How can I load the above txt file into a table with following schema?
Loan(BorrowerName, Loan#)
All I need is the borrower name which are xxxxx, xxxx and loan numbers.
View 3 Replies
View Related
Dec 15, 2000
Hello
I am writing program in VC++ through SQl-DMO calls.My problem is when i when i tranfer(import) a text file(comma seperated) into SQl server through a SQl-DMO method called ImportData which is a method of Bulk copy object.Its is not able to convert the data field in the text file to corresponding value datetime in SQl server whereas other data types are working perfectly.
This is the record i need to convert:
90,MichaelB,Wintriss,Inspection,Paper,11,Job101,1, {ts '2000-12-10 15:54:56.000'},D:public233 and 247233.mcs,
and this is the date field
{ts '2000-12-10 15:54:56.000'}
Whereas if i export a table in SQl server in Binary mode and then import the file back it works but when do it as text it gives the above error
Pls help me in this i would be very thankful to you.
Note: I am using SQL Server 7.0 version
Regards
Jitender Singh
View 1 Replies
View Related
Apr 22, 2002
I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.
My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?
View 1 Replies
View Related
Dec 24, 2004
I have an assignment and need to dosomething that should be simple, basically output the contents of a table to a text file.
I have been trying this syntax:
bcp "dbo.items_with_constraints_tbl" out "J:items.txt" -c
But I keep on getting this error message:
Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.
I am completely lost!! :confused:
Can anyone help me please?
View 12 Replies
View Related
Apr 24, 2006
Hello everyone:
I create a temperal table to load data in a stored procedure. At last I want to export this temp table to a text file.
Any suggestion will be great appreciated.
ZYT
View 1 Replies
View Related
