I am trying to export data from a SQLServer database into a text file
using a stored procedure. I want to be able to read it and debug it
easily; therefore, I want all the columns to indent nicely. This means
I need to append trailing spaces to a text string (such as "Test1 ")
or append leading space in front of a text string that contains a
number (such as " 12.00"). Now, the stored procedure works fine when
I run it in Query Analyzer. But it doesn't work correctly when I run
it using ISQL - All the columns are not indented. I am wondering why
it doesn't work in ISQL.
This is what I want, and this is also what I get when I run the stored
procedure using Query Analyzer:
Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00
This is what I get if I run the stored procedure using ISQL
(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):
Test1, 2,Test1.txt, 1.00, 1.00
Test22, 2,Test22.txt, ,
Test333, 2,Test333.txt, 30.00, 30.00
You can see that the result from ISQL has the following differences:
1. It puts a space in front of each row.
2. It appends enough spaces at the end of each line to make
the line length to be exactly 61 characters.
3. It gets rid of the trailing space from each column.
4. It leaves only one blank space if the column has nothing
but a serie of spaces.
The following is the stored procedure that I am testing:
create procedure MyTest
as
set nocount on
create table #Test
(
Field1 varchar(10) null,
Field2 varchar( 5) null,
Field3 varchar(20) null,
Field4 varchar(10) null,
Field5 varchar(10) null
)
insert into #Test values
( "Test1 ", " 2","Test1.tx t ", " 1.00", " 1.00" )
insert into #Test values
( "Test22 ", " 2","Test22.t xt ", " ", " " )
insert into #Test values
( "Test333 ", " 2","Test333. txt ", " 30.00", " 30.00" )
select Field1 + "," +
Field2 + "," +
Field3 + "," +
Field4 + "," +
Field5
from #Test
drop table #Test
go
Strangely, the differences #3 and #4 only show up when I use the
SELECT statement on a table. They don't show up when I use SELECT
statements to show constant text strings or string variables, like
this:
set nocount on
select "Test1 " + "," +
" 2" + "," +
"Test1.txt " + "," +
" 1.00" + "," +
" 1.00"
select "Test22 " + "," +
" 2" + "," +
"Test22.txt " + "," +
" " + "," +
" "
select "Test333 " + "," +
" 2" + "," +
"Test333.tx t " + "," +
" 30.00" + "," +
" 30.00"
The result is like the following if I use constant text strings or
string variables:
Test1 , 2,Test1.txt , 1.00, 1.00
Test22 , 2,Test22.txt , ,
Test333 , 2,Test333.txt , 30.00, 30.00
I need to run it from ISQL because that is how I run _all_ my other
stored procedures. I don't want to do anything differently just
because I need to run this stored procedure.
Thanks in advance for any suggestion.
Jay Chan 4 22839
"Jay Chan" <ja******@hotma il.com> wrote in message
news:c7******** *************** ***@posting.goo gle.com... I am trying to export data from a SQLServer database into a text file using a stored procedure. I want to be able to read it and debug it easily; therefore, I want all the columns to indent nicely. This means I need to append trailing spaces to a text string (such as "Test1 ") or append leading space in front of a text string that contains a number (such as " 12.00"). Now, the stored procedure works fine when I run it in Query Analyzer. But it doesn't work correctly when I run it using ISQL - All the columns are not indented. I am wondering why it doesn't work in ISQL.
Check out the 'SET ANSI_PADDING' setting in BOL.
Ian.
Jay Chan (ja******@hotma il.com) writes: I am trying to export data from a SQLServer database into a text file using a stored procedure. I want to be able to read it and debug it easily; therefore, I want all the columns to indent nicely. This means I need to append trailing spaces to a text string (such as "Test1 ") or append leading space in front of a text string that contains a number (such as " 12.00"). Now, the stored procedure works fine when I run it in Query Analyzer. But it doesn't work correctly when I run it using ISQL - All the columns are not indented. I am wondering why it doesn't work in ISQL.
This is because with ISQL you get a different setting for ANSI_PADDING.
This setting is OFF by default with ISQL, but ON by default with Query
Analyzer. The effect of this setting is that if it is OFF, SQL Server
trims trailing spaces from varchar data when you insert it.
Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.
The setting is actually saved with the table column, so if you create
the table in QA, it should work in ISQL even with the setting off. (But
I have not tested this.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
> Check out the 'SET ANSI_PADDING' setting in BOL.
Thanks! This switch fixes the problem very nicely. Now, all the
columns indent correctly.
Jay Chan
> This is because with ISQL you get a different setting for ANSI_PADDING. This setting is OFF by default with ISQL, but ON by default with Query Analyzer. The effect of this setting is that if it is OFF, SQL Server trims trailing spaces from varchar data when you insert it.
Use SET ANSI_PADDING ON in ISQL to get ISQL to behave like QA.
Yes, you are right. I add the switch in my stored procedure right
before it starts creating temporary tables, and now it can indent the
column of info quite nicely. Thanks.
The setting is actually saved with the table column, so if you create the table in QA, it should work in ISQL even with the setting off. (But I have not tested this.)
I believe this is correct according to the Help info on that switch. I
also create tables in Query Analyzer; this explains why I haven't come
across this problem until now.
Jay Chan This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Adrian Parker |
last post by:
I'm using the code below in my project. When I print all of these fixed
length string variables, one per line, they strings in questions do not
properly pad with 0s. strQuantity prints as " 4". Six spaces than the
value of intQuantity. This is correct.
But all the others end up being string objects of only 6 characters long
(with...
|
by: ucfcpegirl06 |
last post by:
Hi, I need help getting rid of trailing white spaces. I am searching a
file for various data (not important) and retrieving it. I output the
data if found to a file. An example would be:
HD='Three blind mice '
My output would consist of:
Three Blind Mice (w/ all the white space behind being printed until the...
|
by: ITM |
last post by:
Can anyone tell me how I can prevent Access adding trailing spaces
when I insert a value into a Text column?
For example, if I execute the following statement:
UPDATE Log SET Log.Title = 'Testing'
WHERE Log.Identifier=1;
....then the following statement:
|
by: jt |
last post by:
I can't seem to find a way to concatenate strings that have nulls within the
string.
I have a string that I need another string that has nulls in it and what to
append
the 2nd string, 3 string and so forth to the 1st string.
Any ideas how to go about this?
Thanks,
|
by: Seth |
last post by:
First off, my apologies if this is in the wrong newsgroup, but I hope
I'm close enough.
I'm trying to do some parsing of a CSV file using OleDbConnection, but
for some reason, when I populate my DataSet, it is trimming the trailing
spaces. Anybody know why?
Here is my code:
System.Data.OleDb.OleDbConnection connection = null;
| |
by: rsine |
last post by:
I am developing a program that requires me to read a string of data from
a text field in a database. Data is parsed from the string based upon a
starting position/length. If a piece of data doesn't meet the required
field length, then spaces are added, thus my issue. It appears VB.net
is trimming any trailing spaces. What should be a 250...
|
by: Christian O'Connell |
last post by:
Hello - in Java you have a class called java.util.StringTokenizer that
tokenizes strings, ie
StringTokenizer st=new StringTokenizer("In the beginning "," ",false);
while (st.next())
System.out.println(st.nextToken());
would print out "In", "the", and "beginning", as separate string
tokens, the string being tokenized on the " " (space)...
|
by: tommy |
last post by:
Hi all,
I' m adding strings to some fields in my table via Access. The strings
sometimes have trailing spaces and I really need to have it that way,
but Access truncates trailing spaces. How can I force Access not to
truncate?!
Thanx,
/Toommy
|
by: brian.j.parker |
last post by:
Hey all, I've noticed an obscure little quirk: it appears that if you
use a login with trailing spaces on the name, SYSTEM_USER
automatically trims those trailing spaces in SQL Server 2000, but not
SQL Server 2005.
Anybody know if this change in behavior is documented? If it is
intentional?
Is there a "quick fix" to revert to the old...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |