Maximum Width property for rows.

Maximum Width property for rows.

Post by Bill Luca » Fri, 12 Jan 2001 02:58:11



Ok I know the Title isn't the most informative here is my problem.

I have a Query which will concat all of the fields in a row together and
seperate the by a delimiter.  I am then using ISQL to write this to a
results file for users to download.  this basically creates a delimited list
for import into excle access etc.  The problem I am having while doing this
unfortunately is that each line is being truncated at 255 characters.  now
in Query Analyzer you can set a property in the connection options on the
maximum width of the result set in charecters.  I would like to be able to
set this same property  in my exporting proc so I can make sure I have
enough room for the enitre results set.  I have set the -w in ISQL to 10000
but I still get truncated at 255 charecters so I am assuming that it has
some properties during the select that I need to change.

If any one knows how to change this setting on the fly in a stored procedure
please let me know.

Thanks.

I have included further information about the Query and its results as well
below.

Regards,
Bill

Here is my ISQL execution statement.



I am including an example of the Query  (not all of it as it is rather long
but enough for you to see what I am doing (I cut out a lot of the columns
being tacked on and complete other tables being selected as well)

SELECT 'Equipment Master'



   + (Case CalcType
    When 0 Then  'Date of Event'
    When 1 Then 'Current Due Date'





   + (Case ReminderPeriod
    When 0 Then  'Weekly'
    When 1 Then 'Monthly'

   + Cast(ReminderNumber as varchar(10))
  FROM tblEquipment

Here is an example delimited list  just one row

 9,C4F385B1-E9A0-48A6-AF8D-388370C34527,Asset-100,Updated Piece of
Equipment,Bin 5,,123456-A,Procedure  128A-4 Rev 1.4,Asset S120,Retired,This
equipment is used for test purposes.,DD,30,Date of

Mo should read Monthly (corresponds to ReminderPeriod In the Select so as
you can see it isn't even getting to the last field)

 
 
 

Maximum Width property for rows.

Post by lindawi » Fri, 12 Jan 2001 14:04:30


The problem is that you are using ISQL, which uses dblib.  From BOL:

"DB-Library remains at the SQL Server version 6.5 level of functionality.
DB-Library applications, such as isql, do not support some SQL Server 7.0
features. For example, they cannot retrieve Unicode ntext data. The osql
utility has a user interface modeled on isql and supports the full set of
SQL Server 7.0 features."

One of the features that dblib does not support is varchar columns greater
than 255 characters.  So, if you are on SQL Server 7.0 or above, you need to
use OSQL not ISQL.  Refer to the topic "osql Utility" in BOL.

By the way, you shouldn't be executing this as dynamic SQL.  Just build your
command line in a variable and pass that to xp_cmdshell.




Linda


> Ok I know the Title isn't the most informative here is my problem.

> I have a Query which will concat all of the fields in a row together and
> seperate the by a delimiter.  I am then using ISQL to write this to a
> results file for users to download.  this basically creates a delimited
list
> for import into excle access etc.  The problem I am having while doing
this
> unfortunately is that each line is being truncated at 255 characters.  now
> in Query Analyzer you can set a property in the connection options on the
> maximum width of the result set in charecters.  I would like to be able to
> set this same property  in my exporting proc so I can make sure I have
> enough room for the enitre results set.  I have set the -w in ISQL to
10000
> but I still get truncated at 255 charecters so I am assuming that it has
> some properties during the select that I need to change.

> If any one knows how to change this setting on the fly in a stored
procedure
> please let me know.

> Thanks.

> I have included further information about the Query and its results as
well
> below.

> Regards,
> Bill

> Here is my ISQL execution statement.



> I am including an example of the Query  (not all of it as it is rather
long
> but enough for you to see what I am doing (I cut out a lot of the columns
> being tacked on and complete other tables being selected as well)

> SELECT 'Equipment Master'



>    + (Case CalcType
>     When 0 Then  'Date of Event'
>     When 1 Then 'Current Due Date'





>    + (Case ReminderPeriod
>     When 0 Then  'Weekly'
>     When 1 Then 'Monthly'

>    + Cast(ReminderNumber as varchar(10))
>   FROM tblEquipment

> Here is an example delimited list  just one row

>  9,C4F385B1-E9A0-48A6-AF8D-388370C34527,Asset-100,Updated Piece of
> Equipment,Bin 5,,123456-A,Procedure  128A-4 Rev 1.4,Asset
S120,Retired,This
> equipment is used for test purposes.,DD,30,Date of

> Mo should read Monthly (corresponds to ReminderPeriod In the Select so as
> you can see it isn't even getting to the last field)


 
 
 

Maximum Width property for rows.

Post by Bill Luca » Fri, 12 Jan 2001 23:42:50


Linda you rock thanks for the advice.  I appreciate the reply.

Thanks,
Bill


> The problem is that you are using ISQL, which uses dblib.  From BOL:

> "DB-Library remains at the SQL Server version 6.5 level of functionality.
> DB-Library applications, such as isql, do not support some SQL Server 7.0
> features. For example, they cannot retrieve Unicode ntext data. The osql
> utility has a user interface modeled on isql and supports the full set of
> SQL Server 7.0 features."

> One of the features that dblib does not support is varchar columns greater
> than 255 characters.  So, if you are on SQL Server 7.0 or above, you need
to
> use OSQL not ISQL.  Refer to the topic "osql Utility" in BOL.

> By the way, you shouldn't be executing this as dynamic SQL.  Just build
your
> command line in a variable and pass that to xp_cmdshell.





> Linda



> > Ok I know the Title isn't the most informative here is my problem.

> > I have a Query which will concat all of the fields in a row together and
> > seperate the by a delimiter.  I am then using ISQL to write this to a
> > results file for users to download.  this basically creates a delimited
> list
> > for import into excle access etc.  The problem I am having while doing
> this
> > unfortunately is that each line is being truncated at 255 characters.
now
> > in Query Analyzer you can set a property in the connection options on
the
> > maximum width of the result set in charecters.  I would like to be able
to
> > set this same property  in my exporting proc so I can make sure I have
> > enough room for the enitre results set.  I have set the -w in ISQL to
> 10000
> > but I still get truncated at 255 charecters so I am assuming that it has
> > some properties during the select that I need to change.

> > If any one knows how to change this setting on the fly in a stored
> procedure
> > please let me know.

> > Thanks.

> > I have included further information about the Query and its results as
> well
> > below.

> > Regards,
> > Bill

> > Here is my ISQL execution statement.



> > I am including an example of the Query  (not all of it as it is rather
> long
> > but enough for you to see what I am doing (I cut out a lot of the
columns
> > being tacked on and complete other tables being selected as well)

> > SELECT 'Equipment Master'



> >    + (Case CalcType
> >     When 0 Then  'Date of Event'
> >     When 1 Then 'Current Due Date'

> >    + IsNull(Convert(varchar(20), LastCalDate, 101), '<NULL>') +




> >    + (Case ReminderPeriod
> >     When 0 Then  'Weekly'
> >     When 1 Then 'Monthly'

> >    + Cast(ReminderNumber as varchar(10))
> >   FROM tblEquipment

> > Here is an example delimited list  just one row

> >  9,C4F385B1-E9A0-48A6-AF8D-388370C34527,Asset-100,Updated Piece of
> > Equipment,Bin 5,,123456-A,Procedure  128A-4 Rev 1.4,Asset
> S120,Retired,This
> > equipment is used for test purposes.,DD,30,Date of

> > Mo should read Monthly (corresponds to ReminderPeriod In the Select so
as
> > you can see it isn't even getting to the last field)

 
 
 

Maximum Width property for rows.

Post by Bill Luca » Sat, 13 Jan 2001 00:10:51


Linda one other real quick Question.  Is there anyway to get rid of the the
line of dashes that are outputed into the resulting file set.  In addition
to this if I output the format as XML I get a GUID that comes out at the top
of the output file.  Is there any way to prevent this?

Thanks for your help again.  oSQL did the trick  everything is working fine
now with the exception of the dashes outputting as column names i.e
----------------------------------------------------------

Regards,
Bill
<snip>

 
 
 

Maximum Width property for rows.

Post by lindawi » Sat, 13 Jan 2001 01:03:09


Bill,

Use the -h-1 command line switch to suppress column headers.

From BOL (osql Utility):

-h headers
Specifies the number of rows to print between column headings. The default
is to print headings one time for each set of query results. Use -1 to
specify that no headers will be printed. If using -1, there must be no space
between the parameter and the setting (-h-1, not -h -1).

I'm afraid, I'm not really following you with the GUID.  Perhaps you can
post some code and sample output to illustrate your problem.

Linda


Quote:> Linda one other real quick Question.  Is there anyway to get rid of the
the
> line of dashes that are outputed into the resulting file set.  In addition
> to this if I output the format as XML I get a GUID that comes out at the
top
> of the output file.  Is there any way to prevent this?

> Thanks for your help again.  oSQL did the trick  everything is working
fine
> now with the exception of the dashes outputting as column names i.e
> ----------------------------------------------------------

> Regards,
> Bill
> <snip>

 
 
 

Maximum Width property for rows.

Post by Bill Luca » Sat, 13 Jan 2001 01:11:19


Wow you are quick.  I did read through it and the -h-1 did the trick for
both. The Guid I mentioned is returned using the FOR XML Explicit but
the -h-1 switch took care of that as well.  Sorry for the really basic
questions first time around with the this i/oSQL Stuff.  Things are looking
good here as soon as I figure a couple of more things I 'll be all good your
help was invaluable thanks.

Bill

 
 
 

Maximum Width property for rows.

Post by lindawi » Sat, 13 Jan 2001 14:21:44


Bill,

OK, I have been trying a few things out with the OSQL -s (column separator)
switch trying to eliminate the trailing spaces.  I was hoping to produce the
same behavior that we see in Query Analyzer.  As you may know, you can go
into the Options Dialog, Advanced tab and set the output format to
tab-delimited.  The effect is that trailing spaces on columns are removed,
which is just what you want.  However, this doesn't seem to be an option
with OSQL, or I am missing something.  I suppose if we could Gert Drapers'
attention, he could tell us if this is even possible.

So now I am thinking that maybe you should just use BCP to create the files.
If I understand you correctly, your stored procedures prepare the output and
store it in an export staging table.  You could just bcp the contents of
this table out to a file.  Either syntax form shown below would work in this
type of situation.

C:\PubsStaging>bcp "select * from pubs..authors" queryout
authors.dat -S%computername% -Usa -Pnone -c

Starting copy...

23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1 Avg        0 (23000.00 rows per sec.)

C:\PubsStaging>bcp pubs..authors out
uthors.dat -S%computername% -Usa  -Pnone -c

Starting copy...

23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1 Avg        0 (23000.00 rows per sec.)

Why don't you give bcp a try and if you have any problems you can post your
procedures.

Linda


> Linda,

> It appears I spoke too soon.  I now have the exact opposite problem and it
> seems to me i tried the ideas that made the most sense.  This is what I am
> running into and I am not sure if I will explain it exceptionally well but
I
> will do my best.  Then point of all of this is to export a users data and
> email it to him.  I am using a stored proc that inserts its in parms into
a
> table and then uses osql to execute the proc thats output will be written
to
> the file. Their are two formats delimited and xml.  now here is what I
think
> is happening.

> 1.  I use oSQL to to execute a stored proc with only 1 in param and no out
> params
> 2. This stored proc (sp_ExportXML or sp_ExportCSV) gets all of its
> information from a row in a tbl via its PK
> 3.  the stored procedure then will go through and assemble the output to
be
> written to the file.
> 4. oSQL writes the file an exits.

> This all works  except that the resulting file has a ton of white space in
> it.  (I am attaching one in case it will help).  have spent all morning
> messing with different options like -w and SET TEXTSIZE.  None of them
seem
> to help.  If I set -w smaller I get the same amount of white spaces just
in
> fewer columns.  IF I change TEXTSIZE nothing seems to happen. I am
> suspecting that this is because it is going out of scope and therefore
> resuming the default values.  I tried modifying my /Q parameter to
something
> like

> that didn't help.

> I need some more detailed information on how this utility works if you
> happen to have a resource you would reccomend that would be great.  I
looked
> myself but there is so much junk to weed through.

> So anyway as I can see it  I have these options.

> 1.  Write a COM+ object to go through and strip out all of the carriage
> returns and preceding whitespaces in the resulting file
> 2. return the file as an ADODB Stream and write the file from the middle
> teir
> 3. Write a wrapper proc that will take a text parameter and massage the
text
> on the DB
> 4. Ask for help and hope someone smarter than me has already delt with
this
> and has an opion I haven;'t thought of.

> I thought I would work my way backwards through the list.

> If anyone has any ideas on how to prevent all of the white spaces from
being
> written to the file I would love to hear them.  If anyone wants to see the
> scripts for the procs I can arrange for that too.

> Sorry aboput the attachment but I think it will illustrate what is
happening
> very well.

> Thanks for any help (and sorry for being so dense)
> Bill

 
 
 

Maximum Width property for rows.

Post by Bill Luca » Sun, 14 Jan 2001 00:16:40


Thanks for all of your effort Linda.  I really appreciate it.  I'll  let you
know how I make out.

That which does not kill us makes us stronger.   Well either that or gives
us a headache.

Thanks again.

Bill


> Bill,

> OK, I have been trying a few things out with the OSQL -s (column
separator)
> switch trying to eliminate the trailing spaces.  I was hoping to produce
the
> same behavior that we see in Query Analyzer.  As you may know, you can go
> into the Options Dialog, Advanced tab and set the output format to
> tab-delimited.  The effect is that trailing spaces on columns are removed,
> which is just what you want.  However, this doesn't seem to be an option
> with OSQL, or I am missing something.  I suppose if we could Gert Drapers'
> attention, he could tell us if this is even possible.

> So now I am thinking that maybe you should just use BCP to create the
files.
> If I understand you correctly, your stored procedures prepare the output
and
> store it in an export staging table.  You could just bcp the contents of
> this table out to a file.  Either syntax form shown below would work in
this
> type of situation.

> C:\PubsStaging>bcp "select * from pubs..authors" queryout
> authors.dat -S%computername% -Usa -Pnone -c

> Starting copy...

> 23 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total        1 Avg        0 (23000.00 rows per sec.)

> C:\PubsStaging>bcp pubs..authors out
> uthors.dat -S%computername% -Usa  -Pnone -c

> Starting copy...

> 23 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total        1 Avg        0 (23000.00 rows per sec.)

> Why don't you give bcp a try and if you have any problems you can post
your
> procedures.

> Linda



> > Linda,

> > It appears I spoke too soon.  I now have the exact opposite problem and
it
> > seems to me i tried the ideas that made the most sense.  This is what I
am
> > running into and I am not sure if I will explain it exceptionally well
but
> I
> > will do my best.  The point of all of this is to export a user's data
and
> > email it to him.  I am using a stored proc that inserts its in parms
into
> a
> > table and then uses osql to execute the proc so its output will be
written
> to
> > the file. There are two formats delimited and xml.  now here is what I
> think
> > is happening.

> > 1.  I use oSQL to to execute a stored proc with only 1 in param and no
out
> > params
> > 2. This stored proc (sp_ExportXML or sp_ExportCSV) gets all of its
> > information from a row in a tbl via its PK
> > 3.  the stored procedure then will go through and assemble the output to
> be
> > written to the file.
> > 4. oSQL writes the file an exits.

> > This all works  except that the resulting file has a ton of white space
in
> > it.  (I am attaching one in case it will help).  have spent all morning
> > messing with different options like -w and SET TEXTSIZE.  None of them
> seem
> > to help.  If I set -w smaller I get the same amount of white spaces just
> in
> > fewer columns.  IF I change TEXTSIZE nothing seems to happen. I am
> > suspecting that this is because it is going out of scope and therefore
> > resuming the default values.  I tried modifying my /Q parameter to
> something
> > like

but
> > that didn't help.

> > I need some more detailed information on how this utility works if you
> > happen to have a resource you would reccomend that would be great.  I
> looked
> > myself but there is so much junk to weed through.

> > So anyway as I can see it  I have these options.

> > 1.  Write a COM+ object to go through and strip out all of the carriage
> > returns and preceding whitespaces in the resulting file
> > 2. return the file as an ADODB Stream and write the file from the middle
> > teir
> > 3. Write a wrapper proc that will take a text parameter and massage the
> text
> > on the DB
> > 4. Ask for help and hope someone smarter than me has already delt with
> this
> > and has an opinion I haven't thought of.

> > I thought I would work my way backwards through the list.

> > If anyone has any ideas on how to prevent all of the white spaces from
> being
> > written to the file I would love to hear them.  If anyone wants to see
the

> > scripts for the procs I can arrange for that too.

> > Sorry about the attachment but I think it will illustrate what is
> happening
> > very well.

> > Thanks for any help (and sorry for being so dense)
> > Bill

 
 
 

Maximum Width property for rows.

Post by Don Arsenaul » Fri, 19 Jan 2001 06:29:37


Quote:>>> Then point of all of this is to export a users
>>> data and email it to him.

Besides using bcp, isql, and osql to create files from SQL Server, you can
use the sp_OAxxxx OLE Automation stored procedures to access the
Scripting.FileSystemObject object.  It gives you lots of flexibility in
directly writing files from a stored procedure at the cost of some learning.
The Scripting.FileSystemObject is installed with Internet Explorer 5+.

However, if you're creating multi-megabyte files, the performance of
compiled applications like bcp may be significantly better than the
sp_OAxxxx routines.

Don.

 
 
 

Maximum Width property for rows.

Post by Bill Luca » Fri, 19 Jan 2001 23:32:10


Thanks Don,

In the end it just became another class in the COM + Object.  We used an ADO
Stream (for XML) and ADO Recordsets (for delimited format),  this was at our
clients request.  They wanted to launch the site with an IIS Box in their
DMZ while keeping the SQL Server behind their firewall.  The consequence of
this is that the files needed to be written on the web server, as they also
want an option where the user can download the file instead of having it
emailed.

Anyway it is all done now and I guess everythig is ok.

However the spOA stuff was he vein I was working in when they cam back and
asked if we could modify it.

Thanks,
Bill


Quote:> >>> Then point of all of this is to export a users
> >>> data and email it to him.

> Besides using bcp, isql, and osql to create files from SQL Server, you can
> use the sp_OAxxxx OLE Automation stored procedures to access the
> Scripting.FileSystemObject object.  It gives you lots of flexibility in
> directly writing files from a stored procedure at the cost of some
learning.
> The Scripting.FileSystemObject is installed with Internet Explorer 5+.

> However, if you're creating multi-megabyte files, the performance of
> compiled applications like bcp may be significantly better than the
> sp_OAxxxx routines.

> Don.

 
 
 

1. Maximum row width returned

(I may have posted this already but just in case.<G>)

Could anyone tell me what the maximum size of the row returned
in an SQL SELECT result is permitted to be under Informix SE 5.0
We are currently using 4GL RDS 4.1 with this but would also like
details for I-Net & ODBC if they differ.

If this is documented somewhere (I have looked but can't find this)
then please point me to the appropriate spot.

Any help much appreciated.

Cheers, Ian.

PS. The answer to this may yet prompt a change from Ingres to Informix
for some applications. (added incentive<G>)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Ian Timms  (DBA/Administrator)         % Disclaimer: All opinions expressed %

% Ph. 61+3+634-9144                      % ==__( pretty piccy goes here )__== %
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

2. To NULL or not To NULL

3. Recordset.Properties("Maximum Open Rows") conundrum

4. dbcc dbreindex clarification

5. Row width limit when importing fixed width files (SQL 7.0)

6. VB4 - ODBC - MS SQLServer Data Access Problem

7. Getting the maximum (character) width of column

8. UK Terminals Needed Please?

9. Maximum report page width

10. Maximum result width

11. Maximum result width 2

12. Maximum width query result 2008

13. Maximum width query result 2008??