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.
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
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?
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
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
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?!
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.
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.
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 ?
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.
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.
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,
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
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.
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?
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?
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
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
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 )
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
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?
