hi, i had been removed the row counts and the column spaces... but i
failed to remove the extra tabs between rows of data return from the
osql output.
how to detect the row delimiter?
i noticed i can use bcp to have a more decent output file, but i don't
want to do too much of file read-write. the programming enviroment is
quite easy to "capture" the output from the comand prompt.
secondary, char(252), 253, 254, 255 is not being read by the command
prompt, for example, 253 turns into 132 when i decode the input.
I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)
Everybody,I've been doing a lot of on-line research and cannot findany reference to the exact problem I'm having.Let me preface this question with the fact that I'm comingfrom an Oracle background so my approach may not be the bestway to tackle this. However, from the research I have donethis approach seems reasonable. Also, I know about theundocumented procedure sp_MSforeachtable. That can give me aresult similar to what I'm looking for but the format of theoutput is not what I need.Now the problem. I'm trying to write a reusable script to giveme a list of all the tables in a database that have 1 or more rows.My approach is to a BAT file (see script 1 below) that calls OSQLtwice, once to call a SQL script (see script 2 below) that uses theInformation_Schema views to generate the SELECT COUNT(*) statementsand fill in all the tables names in the database, write this to atemporary output file and the second OSQL command to read thetemporary output file and generate me the results formatted theway I need.The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.Because of this garbage the 2nd OSQL command blows up! Anyone haveany idea what is generating this garbage?If I manually edit out the garbage and then just run the 2nd OSQLcommandI get similar garbage in the final result file (see 2nd result filebelow).In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manuallythen take its output and copy and paste it to a new query window andrun that it works OK except for generating lots of blank lines wherethe result of the tables that have zero rows are. I am suppressingheadings but am still getting the blank lines but at least it works!Any ideas anybody? Thanks For Any HelpFYI -- SQL Server 2000 with SP3a.Bob================== Script 1 - BAT File to Call OSQL ===============@echo off@echo ************************************************** *************@echo .@echo get_table_count.bat@echo .@echo Before you run this script change to the drive and directory@echo where the input SQL script is located!@echo .@echo Input parameters:@echo 1) SQL Server userid@echo .@echo You will be prompted twice for your password!@echo .@echo The output is written to file TABLE_COUNT_RESULT.TXT@echo .@echo ************************************************** *************pauseosql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -otemp_table_count_query.txt -h-1 -w500osql -U %1 -S devkc-db -d C3T_Architecture -itemp_table_count_query.txt -o table_count_result.txt -h-1 -w500del temp_table_count_result.txt@echo on================================================== ==================================== Script 2 - GET_TABLE_COUNT.SQL Script ===============set nocount onselect 'set nocount on'select 'select ''Table Name Count'''select 'select ''========== ====='''select 'select '''+ table_name+ ''', count(*) from '+ table_name+ ' having count(*) > 0 'from information_schema.tableswhere table_type = 'BASE TABLE'order by table_name================================================== ================================ Partial Result of 1st OSQL Run ==========================1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount onselect 'Table Name Count'select '========== ====='select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE havingcount(*) > 0select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0================================================== ================================ Partial Result of @nd OSQL Run ==========================1> 2> 3> 4> ... I edited out the intervening numbers for this message.... 664> 665> 666> 667> Table Name Count========== =====.... I edited out lots of blank lines in the result for this messagebefore I get to the first table with 1 or more rows ...ARCH 6================================================== ====================
I am trying to use a command line program to run a stored procedurethat generates output in a comma-delimitted format. Somehow, ISQL orOSQL always wrap the lines at 256 characters. I believe this hassomething to do with the column width switch (-w). But enlarging thecolumn width to 800 characters max still doesn't help. The followingis a stored procedure that is essentially doing what my storedprocedure is doing:create procedure MyTest asset ansi_padding onset nocount ondeclare @sTest varchar(300)-- Output three lines. Each line has 259 characters.select @sTest = "1234 6789 ... 1234 6789"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestselect @sTest = "1 3 5 7 9 ... 1 3 5 7 9"print @sTestset nocount offreturn( 0 )I invoke this stored procedure using this command:isql -SMyDbSrv -E -dMyDb -w800 -x800 -h-1 -n -Q"exec MyTest"-oMyTest.txt-- or --osql -SMyDbSrv -E -dMyDb -w800 -h-1 -n -Q"exec MyTest" -oMyTest.txtBut they have the same problem. The output lines all wrap around at256 characters.Strangely, if I store the result in a temporary table, and then useSELECT to output the result from the temporary table, I will not havethat problem. Seem like the "-w" switch only works for output fromtables, but not for output coming from PRINT. Unfortunately, usingthis approach has another set of problems (one blank space in front ofeach line, "number-of-rows affected" shows up at the bottom).Therefore, I would like to stick with using PRINT statements to outputthe result.Please suggest a way to fix this line-wrapping-around problem.Thanks.Jay Chan
Hi Please tell me which delimiter to use. Criterion: 1) The delimiter should appear as if it were space in the Back end 2) while running reports it should not dampen the lay out 3) The delimiter should not be an enterable character
Ex: i tried with chr(10) but while running reports, the characters after the delimiter are shifted to new line.
The delimiter that i use, should not create such undesirable effects.
This is the first record that generates around 41 columns and sorts data as it should, but if the second record is smaller the row delimiter {LF} is ignored and put into one of the columns untill all 41 columns from previous record are filled. It seems to me that columns separator has the priority over the row delimiter which is very wrong. :). If there is a {LF} in the file that should mean that it needs to be in a new row as a new record. I try to keep this all in a SQL 2005 package without using any additional software, i know there might be a solution with the scripting component, but would like to see if theres someone with the similar solution before i start writing any scripts. (i dont like parsing strings with scripts from bulky files:))
i was on with DTS, dumping a flat file to SQL DB and i came across an error which says: "Too many columns...; found whitespaces.."
When I checked the row that caused this, I found out that entries with ""s are split into two columns possibly because the backslashes were read as tab delimiters..
I've tried other delimiters, but they do not seem to fit because most of the entries have them.
Inside a column I have this result: Record 1: Sales;Admins Record 2: ;Sales;Admins Record 3: Sales; Record 4: Admins;
You can see the delimiter ";", it can be everywhere.
Now I want to delete "Sales". Therefor I have to search where the "Sales" is. (records) After that I want to delete the "Sales".
If I delete it the record may not have 2 or more delimiters after each other, like here: Record 1: ;Admins (good, better is to remove the delimiter also) Record 2: ;;Admins (bad) Record 3: ;(good, better is to remove the delimiter also) Record 4: Admins;
My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the delimiter ','.
Data from source goes into target table under same field...
ISSUE IS -
Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)... where(n >=2)...
For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3'
How do we compare the data under this particular field now...
Do we have to use a if then loop for identifying when the ; ends in target data...
Can somebody help me with a delimiter problem I have.I have several PIPE (|) delimted text files which I need to import toSQL.With one of the files I keep encountering the following error;"Error at Source for Row 27753. Errors encountered so far in this task:1. Column Delimter not found."I suspect the problem is that one record (and possibly more) has a PIPE(|) within a field, because some of the fields contain free text.Getting an export of the file again using a different delimter like tabor comma will not work as these characters occur throughout the file.I'm open to suggestions as to how to resolve this and really need toget a solution soon.One solution I was thinking of, but do not know how to execute is tocount the number of PIPEs on each record and then manually change therecords which have count which is inconsistent with the rest of thefile.I've also tried importing to Access first and then SQL, as this hasworked for me in the past, but it did not work.Regards,Ciarán
I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance
Sorry if this thread has been repeated but I cannot find a reference to my problem. I have read that apostrophes can be qualified with double quotes but that doesnt solve my problem. I am using SQL 2000 on a Windows 2000 Server trying to set up a job to email the results of a query. I have had no trouble getting it to run as scheduled but cannot seem to get the results out when I add a clause that filters out a text column. This is the query I am trying to run from a step within a job inside our SQL Agent:
exec master.dbo.xp_sendmail @recipients = 'user@mycompany.com', @subject = 'Overnight Safedata Statistics', @query =' SELECT u.username as BackUpSet , r.requestresultstatus as Status, r.requesttype from users u ,requests r where u.userid = r.userid AND requestdate between getdate()-1 and getdate() AND requestresultstatus <> 'Success' ', @message = 'Results over the last 24 Hours', @attach_results = FALSE, @dbuse = 'BackupServer'
I can successfully run the @query string in query analyzer and it worked fine from SQL mail until I added the last clause to the step (AND requestresultstatus <> 'Success' ). I do not know how to get the 'Success' treated as text.
Any advice would be appreciated. Thanks JT
P.S. I am a network guy trying to give our SQL developers a break.
The input is like this : xxxxxxxx=yyyyy=key_id=12345xyxyx
I would like to use the right function and get 12345xyxyx alone. The function must search for the first '=' symbol from the right of the word and get the characters from the end till the '=' symbol.
Does any one know a simple way of changing the delimiter value for a CSV report delivered via email from SQL Server 2000 Reporting Services? The default is comma. From the research I've done it seems that it can be done by writing a VB script that calls the Render method, but I'm not a developer and it seems crazy to have to go to those lengths just to change one attribute of a rendering extension that is available out-of-the-box. I hoped that I would be able to change a value in a config file (e,g, RSReportServer.config), but this seems not to be the case.
'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '
BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal stå på kortet' as nvarchar
FROM dbo.WAIT_Applicant WA (nolock)
This makes the text (from one record) split up over several lines in the output.
I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore
In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:
BS0520131264202410001130001000000015827207000000000 tekst der skal stå på kortet
But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.
I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
I have a importfile that I need to insert into an db table. The file looks like this: one;two;three;text;moretext one;two;three;text;moretext; one;two;three;text;moretext one;two;three;text;moretext; one;two;three;text;moretext one;two;three;text;moretext;
As you can see some rows contains a delimiter while others dont. There is a programing error on the application that generates the file and this cannot be changes. Is there a way in integration services to remove the delimiter ?
i am running sql server 2000 on windows 2000. i have a need to export a view and delimit the columns with double quotes as it has imbedded commas in the columns, how do i do this??
We use BULK INSERT to load client data into our program. One of our clients uses the character '²' (0xB2) as a field delimiter in their input files. This worked fine in SS2000 but is failing in SS2005. After some testing, it appears that any high-ASCII value has the same problem; if I set the delimiter to anything below 0x80, it works and with any value of 0x80 or higher it fails.
I've verified that the format file we're using is correct for all of the tested delimiter values. (|, , ², ~, and €). The database collation sequence is SQL_Latin1_General_CP1_CI_AS if that matters.
Is there a way I can force acceptance of high-ASCII values as delimiters in SS2005? Do I need to play with the system code pages or the collation sequence?
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5. Desired data format: Name1 Name2 Name3 Name4 Name5. Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally Bush/Jim/Sr/Etal/Trustee
is there a way to define a row delimiter to fixed width files? such as this one:
1122333
4455666
7788999
in this file i have 3 columns that are fixed (col1.width = 2, col2.width = 2, col3.width = 3) but have {CR}-{LF} as the row delimiter. when i try to create a flat file connection to these kind of files- he always reads the CR-LF as part of the file text, and there is no place where i can define the row delimiter if i have.
is there any way to change the delimiter in a FlatFile Source Adapter dynamically? You can do that with the row delimiter but there is no expression for the column delimiter... Is there any other way besides changing the XML code?
I have a text file that loads just fine with DTS 2000, but in SSIS it does not seems to recognize the row delimiter. For example, most rows in the text file have 10 columns, but some have 8 columns. For those with 8 columns, the SSIS is adding the data from next row, not padding the columns with nulls. Please help...
i am working on a project witch involves creating packages on-the-fly to import data from txt/csv/xls files according to some definitions on the database.
so far, i have been doing fine.
now we are planning the ASP.net page that enables the customer to define the input file format that will be imported to the system. we want it to have the same listBox as the FlatFileConnectionManager Editor has to define some properties, such as - column delimeiter.
the code to set the column delimiter looks like this:
SSISRunTime.IDTSConnectionManagerFlatFileColumn90 col
col = myFilecn.Columns.Add(); //.....
string colDelimiter ="|" ; // it actually gets the data from the database... but it is the same thing
col.ColumnDelimiter = colDelimiter;
when we deal with the simple characters- "," , ";" , "|" ... we have no problems with setting the delimiter. but how can i set the delimiter to Tab? or {CR} ? {LF}?
i tried to look at the dtsx- XML , and i see that the column delimiter that is defined when i choose Tab is: _x007C_, but when i try to do something like this:
col.ColumnDelimiter = "_x007C_" ;
it doesn't work. the same happens when I try "{t}" or "Tab".
how do i solve this problem, and enable the user to select Tab as a column delimiter?
I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?
i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.
Any help would be appreciated
I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched
I have a flat file that is row delimited by x00 x0D x0A. Any ideas on how to specify the row delimiter in the Columns section of the Flat File Connection Manager?
