Generate A Text File From SQL Server
Feb 23, 2006
Hi
I need to generate a text file from SQL Server. The task automatically runs daily.
The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "". The number of char includes the data parts and excludes a few word of "the number of char".
For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.
1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161
I am no idea about this problem. Please give me some suggestions. Thanks a lot
regards
Alex
For your information
I use SQL Server 2000 in Windows 2000 Server environments
View 8 Replies
ADVERTISEMENT
Mar 23, 2008
I am trying to generate a text file with current timestamp uisng SSIS. What is the best way to do this? can you show me some examples?
View 3 Replies
View Related
Jul 10, 2006
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
thanks.
View 5 Replies
View Related
Feb 10, 2004
Generate an html-excel file with any table. Does not need Excel to generate , because does not use Excel automation.
http://www.databasejournal.com/scripts/article.php/3300831
View 2 Replies
View Related
Aug 2, 2015
I have data in Sql table , I want to convert it to xml using xsd using script component in ssis.
View 0 Replies
View Related
Jul 23, 2005
Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?
View 8 Replies
View Related
Jul 20, 2005
Is there an easy way to loop through every stored procedure in adatabase and create a file containing all of the SP code?lq
View 9 Replies
View Related
May 16, 2008
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
View 7 Replies
View Related
Mar 20, 2008
Hi,
I am looking for a way to combine two text files into one file. I am thinking of using a batch file (DOS command ) to do it. Any suggestion please?
View 6 Replies
View Related
May 2, 2006
hi all,
how to generate txt file from sql table. I want text file with respective columns without any seperator between columns. and if for an example a column of int having 5 digits but i want it in text file with 10 digits with remaining all as blanks. guide me how to do this.
thnks in adv
View 6 Replies
View Related
Apr 28, 2008
This is my first time on sqlteam forum so a big hello to everyone!!
Firstly I am very noive user of sql to say the least, but i have be requested to create a .txt file
Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far, like i said i am a very novice user so try to help me and not condem me for my lack of knowledge!
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not to how to do that and how to generate it in a txt file?!
Keith
View 5 Replies
View Related
Apr 6, 2004
Hi,
Can some one give me some suggest?
Thanks!
View 2 Replies
View Related
Jul 18, 2006
Hi all.
I need to create a Linked Server in SQL 2000 to a text file. Importing the file is not an option. It has to be linked. I tried creating a ODBC link to it but I must be doing something wrong because it didn't work.
Please help.
Thanks,
ODaniels
View 2 Replies
View Related
Dec 4, 2013
How to link a text file in sql server and how to save/reuse the import specifications.
View 7 Replies
View Related
Feb 17, 2008
I have a text file and moved the data to sql server using import and export data. These are step I am doing.
1. Open Microsoft Sql Server and select DTS Import Export Wizard
2. Using the wizard , I am selecting the Data Source as Text File and provided the file name.
3. In the next option, I am choosing Delimited , followed by Tab option.
4. Then, I am selecting the required Destination and the required Database also.
5. I am choosing the files specified and Run the file.
This method works perfectly.
However, I want a method, where I could avoid steps 1 thru 5 and provide the user a singe step. I mean just an execute of a stored procedure should do the steps 1 thru 5. Is that possible.
Thanks.
View 8 Replies
View Related
Dec 20, 2001
Hi everybody
EM has ability to Generate SQL Scripts to file(S)
I traced it with profiler but can not see how Sql server output objects to file(s)
I want to run DTS and sript all procedures to files every night
I don't want to use
xp_cmdshell 'osql -UTheUser myPass-P -Qsp_helptext 'Mystproc -oc:est.txt'
Could it be done another way ?
View 3 Replies
View Related
Apr 23, 2003
i want to generate a sql script file along with the data
how to generate it?
View 12 Replies
View Related
May 22, 2006
I am a Newbie to programming and databases and would like to create a simple program for doing full text searching on a SQL database.
I have downloaded and installed Visual Basic 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express. I have watched several hours of video tutorials and done numerous tutorials. I feel I am making progress and having a lot of fun. My machine is running XP home edition.
I have found the following resource that looks like a good place for me to start with creating a full text search program:
€œSQL Server 2005 Books Online
Item Finder Sample
http://msdn2.microsoft.com/en-us/library/ms160844.aspx
However I am stuck at the beginning with trying to €œgenerate a strong name key file.€? I don€™t know how to €œopen a command prompt€? (I did confess to being a Newbie!!). I searched the database for this forum and found a relevant thread stating that €œSQLCMD is the command-line utility for SQL Server and is included with SQL Server 2005 Express Edition.€? I found the SQLCMD program on my computer but when I opened it, it does appear to provide the options listed below in the tutorial (i.e., €œclick start,€? €œPoint to all programs,€? etc.).
Initially here is what I need based on the first section of the tutorial:
Building the Sample
If you have not already created a strong name key file, generate the key file using the following instructions.
To generate a strong name key file
Open a Microsoft Visual Studio 2005 command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
-- or --
Open a Microsoft .NET Framework command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.
Note:
To determine the folder where samples are located, click theStartbutton, point toAll Programs, point toMicrosoft SQL Server 2005, point toDocumentation and Tutorials, and then clickSamples Directory. If the default installation location was used, the samples are located in <system_drive>:Program FilesMicrosoft SQL Server90Samples.
At the command prompt, run the following command to generate the key file:
sn -k SampleKey.snk
Important:
For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
How do I complete this initial step of generating a strong name key file for the tutorial? I guess I am missing something simple and once I get past this I will be able to complete the tutorial. Any help will be greatly appreciated.
View 7 Replies
View Related
May 23, 2008
hi i was trying to generate a .xml file by using this command below
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
DECLARE @Table varchar(20),@filename varchar(50), @SQL nvarchar(4000)
SET @Table ='Authors'
SELECT TOP 1 @Filename = au_fname FROM Authors
SET @FileName = 'C:' + @Table+'_'+@FileName+ '.xml'
SELECT @FileName
SET @SQL = 'EXEC master..xp_cmdshell '+ ''''+'bcp'+ ' "SELECT ''au_fname '' AS ''Name/First'', au_lname AS ''Name/Last '' FROM pubs.dbo.authors ORDER BY au_lname FOR XML PATH (''Books''), ROOT(''Authors''), TYPE, ELEMENTS " queryout '+ @FileName + ' -c -SlocalHost -Usa -P'+ ''' '
SELECT @SQL
EXEC(@SQL)
i got an error saying
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'au_fname'.
i could no tfigure it out my mind window is closed.can any one help me put to find a bug.
many many thanks
View 5 Replies
View Related
Mar 25, 2008
Hi All,
I Need to load the text(CSV) files into sql server using text reader. Please can any one give me the code for that.
I want read that file in web page only. I can't use Bulk Insert.
First I will read the file into data set. Then i wanna update that in sql server table.
Thank you,
View 1 Replies
View Related
Aug 10, 2005
Hello,I am trying to load a text file into SQL Server but the text file seems to be in an unsual format that SQL Server is having a problem reading. I have tried the various options for delimited and fixed file formats.Any ideas would be appreciated.Sample of the file: Dn DCHB;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Î &Î BRTA_UA46 200508082345079999 BANNER PAGE ;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Î &Î BRTA_UA46 20050808234507 420 ;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Î &Î BRTA_UA46 20050808234507 425 ;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &Î &Î BRTA_UA46 20050808234507 440
View 1 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
Dec 12, 2000
Is there a way to save the results of a query (run in the Query Analyzer) to a text file?
View 4 Replies
View Related
Dec 16, 2004
I am writing a .NET service, and I need to insert files in a SQL DB for temporary storage.
I have never inserted a file into a SQL database before. I am thinking about using the image field type. Has anyone done this before? How did you do it?
View 9 Replies
View Related
Apr 7, 2006
Hello folks!
I have written a query that I need to save results of. Is there some statement that I can place at the top of my query (like we can do in Oracle through spool command) to specify the directoryfilename to save query results to? Eventually I need to schedule this to run daily via a DTS package.
Please let me know if you've come across this before?
Thanks so much!
-Parul
View 3 Replies
View Related
Dec 26, 2006
How do I tranfer a flat file (text) to SQL Server using DTS? Please point me in the right direction.
View 19 Replies
View Related
Jul 20, 2005
Hi there,When importing a text file using SQL Server, how do I specify the pathto a file on the same remote server that SQL Server is running on? Itried //c:/filename but that doesn't seem to work.Also, once the import is working, how do I write a DTS package tofirst delete all rows in a table, then repopulate with the text file,or is it easier to drop the table, re-create it and then repopulate?The table will contain approximately 30,000 records.thanks for your help,K
View 4 Replies
View Related
Jul 20, 2005
Hi All,I have to write a stored procedure which will send/Receive text filefrom/to a server by using FTP.Is anybody have done anything on it? or know about it.If yes, I would like to know about it. (also provide the storedprocedure code if possible)Thanks in Adv.T.S.Negi
View 3 Replies
View Related
May 8, 2008
I want to be able to import data from a text file, into SQL Server 2005, using OPENROWSET. Can you pl give the the syntax for this. What I HAVE IS
select * --into #tmp1
From OpenRowSet
( 'Microsoft.Jet.OLEDB.4.0',
'Text;\ABC.TXT
)
View 2 Replies
View Related
Oct 2, 2006
I have a table in a database has the same filed with a text file:
RBW_AR_BOT_REC
Size
Comment
Justification
Padding
record_id
1
'R'
None
None
date
6
MMDDYY
None
None
time
4
HHMM
(24 hour time)
None
None
cust_num
8
1
- 9999999
Right
Zero
odometer
6
0
- 999999
Right
Zero
receipt
8
1
- 99999999
Right
Zero
exception_customer
1
'0' = No
'1' = Yes
None
None
valid_void
1
'0' = valid
'v' = void
None
None
Justification
Padding
None
None
So that, after export data from the table, the file file must has that struct
Tell me, how can i do???
None
None
None
None
Right
Zero
Right
Zero
Right
Zero
None
None
None
None
Justification
Padding
None
None
None
None
None
None
Right
Zero
Right
Zero
Right
Zero
None
None
None
None
View 8 Replies
View Related
Oct 20, 2012
I want to create an excel file with .xls by using VB script.
Because i want to create a DTS package for our requirement. DTS accepts excel files which has .xls extension only.
View 1 Replies
View Related
Jul 20, 2005
I need to develop some crystal reports on some .NET ado datasets.This is easy to do if I actually had data to work with. It is mucheaser creating reports with you have data.Therefore, I would like to run the stored procedure that the .NET adodataset is built on and generate an XML file. I could run the projectand try to figure out where the developers are making the call to thestored procedure and insert a line to writetoxmlfile. I would rathernot have to mess with their code.Is there a way working with SQL Server (either query analyzer orenterprise manager, dts, or whatever) that I can generate an xml file.I see that I can run a stored procedure and get an xml style return inquery analyzer, but I don't know how to save that as an actual file.Thanks for the help.Tony
View 3 Replies
View Related
Sep 18, 2007
Hi Guys,
I've one Dafta flow task where I'm getting data from OleDb source and then doing some scripting using script component and then generating a file.
Now I would need to get the same data and apply some different things and generate another file.
Can I used this same task for doing the secondry work? If yes how woulld I put the thing in place, I would need to get the same data but I would need to use a seperate scripting and generate a seperate file?
TA
Gemma
View 1 Replies
View Related
