Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of thi
s
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:

> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:

>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of th
is
>column will contain a very very long output string that part the output dat
a
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:

> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
>
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>

Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of this
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:
> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> > We are using SQL 2K with Sp4.
> >
> > I am doing a Select statement where it contains a column that composes of
> > a
> > concatenation of various strings and many fields. At time, the output of
> > this
> > column will contain a very very long output string that part the output
> > data
> > is being truncated. I assume it exceeds the limit of an output column in
> > the result panel would allow. Is there a setting in SQL I can change to
> > imcrease the size of a column in the result panel in query anaylzer?
> >
> > Wingman
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of this
>column will contain a very very long output string that part the output data
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:
> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
> >We are using SQL 2K with Sp4.
> >
> >I am doing a Select statement where it contains a column that composes of a
> >concatenation of various strings and many fields. At time, the output of this
> >column will contain a very very long output string that part the output data
> >is being truncated. I assume it exceeds the limit of an output column in
> >the result panel would allow. Is there a setting in SQL I can change to
> >imcrease the size of a column in the result panel in query anaylzer?
> >
> >Wingman
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>> In Query analyzer, go to the menu to Tools, then Options.
>> On the results tab, increase the Maximum characters per
>> column setting.
>> -Sue
>> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
>> <Wingman@.discussions.microsoft.com> wrote:
>> >We are using SQL 2K with Sp4.
>> >
>> >I am doing a Select statement where it contains a column that composes of a
>> >concatenation of various strings and many fields. At time, the output of this
>> >column will contain a very very long output string that part the output data
>> >is being truncated. I assume it exceeds the limit of an output column in
>> >the result panel would allow. Is there a setting in SQL I can change to
>> >imcrease the size of a column in the result panel in query anaylzer?
>> >
>> >Wingman
>>

Outer Join Issues in Sql 2005

Hi,

The following query works fine in Sql 2000 database, but it throws an error ('Query contains an outer-join request that is not permitted.') while executing the query on Sql 2005 database. We have already set the compatibility level of the Sql 2005 database to 'Sql Server 2000(80)'. But still we are getting the error.

select top 2 *
from table1 t1, table2 t2
where t2.col8 =* t1.col1
and t2.col9 =* ( select max(t21.col9) from table2 t21
where t21.col8 =* t2.col8 )

We are planning to convert all our sql 2000 databases to sql 2005 databases. We have used this kind of outer join(=* or *=) in many places in our application. It is also working fine many places in 2005 database, but throw errors in some places only.

Please let me know if any one knows the reason for the error in some places alone. So that we can give priority to those kind of query and change the outer join to 'SQL-92 compliant'. We know that this kind of outer joins are NOT going to be supported in future version of Sql and we are going to change all such outer join. But now we do not have much time to change all such outer join. It will be a great help if any one have answer for this.

Thanks in advance,

Sankar N

Is this error related only to the statements including an outer join on a subquery?

Regards

|||

You will have to modify the query to use the OUTER JOIN syntax. There is no way to make it work in SQL2005 for all cases. The compatibility level doesn't help in this case due to changes in query plan generation in SQL2005 (there are lot of changes, some of the syntax evaluation is more strict, new optimizations etc). The old proprietary *= and =* syntax has lot of ambiguities and it can produce incorrect results. It is best to convert all statements with old syntax or do it on a case-by-case basis which might be problematic.

sql

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentage
If you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:

> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyalty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
> Is there any osql switch that could eliminate these blank lines? I've tried
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentageIf you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:

> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyal
ty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines
).
> Is there any osql switch that could eliminate these blank lines? I've tri
ed
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentageIf you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:
> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyalty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
> Is there any osql switch that could eliminate these blank lines? I've tried
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezGo to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
displayed there should be having write access to the folder you are writing
the log file.
After that just right click and run the task and see.
Thanks
hari
SQL Server MVP
"J. Baez" wrote:
> I have a scheduled task that runs a batch file in windows 2003 server which
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here. I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez|||That user already has full control of that directory. So it still not
working. Any other ideas?
"Hari Prasad" wrote:
> Go to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
> displayed there should be having write access to the folder you are writing
> the log file.
> After that just right click and run the task and see.
> Thanks
> hari
> SQL Server MVP
> "J. Baez" wrote:
> > I have a scheduled task that runs a batch file in windows 2003 server which
> > is running SQL Server 2000 Enterprise.
> >
> > The batch file contains the line:
> >
> > OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> >
> > the 'myscript.sql' file updates a table with another table of the exact
> > number of rows.
> >
> > When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> > affected.
> >
> > If I run this myself from the command line, 22,000 rows are affected which
> > is correct.
> >
> > I am guessing there is some sort of permission/authentication issue here. I
> > am sending the right username/password for SQL and for windows to run the
> > task.
> >
> > Any idea what I could do to fix this?
> >
> > Thanks,
> >
> > J. Baez

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezTry fully using fully qualified path for the input file -
C:\SomeFolder\mysqlscript.sql
or whatever the path is.
-Sue
.
On Tue, 22 Aug 2006 05:26:01 -0700, J. Baez
<JBaez@.discussions.microsoft.com> wrote:

>I have a scheduled task that runs a batch file in windows 2003 server which
>is running SQL Server 2000 Enterprise.
>The batch file contains the line:
>OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
>the 'myscript.sql' file updates a table with another table of the exact
>number of rows.
>When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
>affected.
>If I run this myself from the command line, 22,000 rows are affected which
>is correct.
>I am guessing there is some sort of permission/authentication issue here.
I
>am sending the right username/password for SQL and for windows to run the
>task.
>Any idea what I could do to fix this?
>Thanks,
>J. Baez|||It is really, really helpful if you post ONLY one copy of your request.
Posting in multiple locations WITHOUT crossposting loses you the benefit of
our 'bouncing' ideas off of each other.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"J. Baez" <JBaez@.discussions.microsoft.com> wrote in message
news:90E8F840-025C-4FFE-8926-55FC25F94567@.microsoft.com...
>I have a scheduled task that runs a batch file in windows 2003 server which
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here.
> I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez
>|||Yeah...I noticed that after I replied. I think there are all
different answers, directions trying to help out with this
one on at least a few different groups. And no one knows who
is suggesting what.
It also makes it hard for others to find the answer when
they search things in the future. One post has the answer,
others don't, you only find the one without the solution,
etc.
-Sue
On Tue, 22 Aug 2006 13:19:49 -0700, "Arnie Rowland"
<arnie@.1568.com> wrote:

>It is really, really helpful if you post ONLY one copy of your request.
>Posting in multiple locations WITHOUT crossposting loses you the benefit of
>our 'bouncing' ideas off of each other.

OSQL Batch File Problem

I have a scheduled task that runs a batch file in windows 2003 server which
is running SQL Server 2000 Enterprise.
The batch file contains the line:
OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
the 'myscript.sql' file updates a table with another table of the exact
number of rows.
When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
affected.
If I run this myself from the command line, 22,000 rows are affected which
is correct.
I am guessing there is some sort of permission/authentication issue here. I
am sending the right username/password for SQL and for windows to run the
task.
Any idea what I could do to fix this?
Thanks,
J. BaezGo to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
displayed there should be having write access to the folder you are writing
the log file.
After that just right click and run the task and see.
Thanks
hari
SQL Server MVP
"J. Baez" wrote:

> I have a scheduled task that runs a batch file in windows 2003 server whic
h
> is running SQL Server 2000 Enterprise.
> The batch file contains the line:
> OSQL -i myscript.sql -Umyuser -Pmypassword -Slocalhost -o mytrans.log
> the 'myscript.sql' file updates a table with another table of the exact
> number of rows.
> When the scheduled task runs, the 'mytrans.log' file shows 0 rows were
> affected.
> If I run this myself from the command line, 22,000 rows are affected which
> is correct.
> I am guessing there is some sort of permission/authentication issue here.
I
> am sending the right username/password for SQL and for windows to run the
> task.
> Any idea what I could do to fix this?
> Thanks,
> J. Baez|||That user already has full control of that directory. So it still not
working. Any other ideas?
"Hari Prasad" wrote:
[vbcol=seagreen]
> Go to schedule tasks -- Tasks tab--See the user in RUN AS: . The user
> displayed there should be having write access to the folder you are writin
g
> the log file.
> After that just right click and run the task and see.
> Thanks
> hari
> SQL Server MVP
> "J. Baez" wrote:
>