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
>>

out join, need configuration?

Hello:
I tried a query with outer join in SQL Analyzer, but it does not work as
expected. Is there any configuration/setting I need to do in SQL server, in
order to make it work?
Thanks.
QHi,
There is no configuration change need to be made to make outer joins change
its behaviour.
Thanks
Hari
SQL Server MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:7ACD64A0-A4C2-4BF4-BDE0-36C3A26469AD@.microsoft.com...
> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server,
in
> order to make it work?
> Thanks.
> Q|||Hi
There are no setting that are required to be changed.
Probably you require to change the order of the TABLES that you are using.
LEFT / RIGHT OUTER Joins..
Hope this solved your problem
thanks and regards
Chandra
"Q" wrote:

> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server,
in
> order to make it work?
> Thanks.
> Q|||Thanks for all your replies! For some reason LEFT/RIGHT outer does not work
.
But *= or =* does work.
Q
"Chandra" wrote:
[vbcol=seagreen]
> Hi
> There are no setting that are required to be changed.
> Probably you require to change the order of the TABLES that you are using.
> LEFT / RIGHT OUTER Joins..
> Hope this solved your problem
> thanks and regards
> Chandra
>
> "Q" wrote:
>|||My guess is that you are filtering in the WHERE clause and for an ANSI join,
you need to put that
filter in the FROM clause. "Doesn't work" is not a good problems description
. Posting the error
message, or describing in what way you get unexpected result back is a bette
r problem description.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:F911E946-0796-49C9-B665-27525C761E32@.microsoft.com...[vbcol=seagreen]
> Thanks for all your replies! For some reason LEFT/RIGHT outer does not wo
rk.
> But *= or =* does work.
> Q
> "Chandra" wrote:
>

out join, need configuration?

Hello:
I tried a query with outer join in SQL Analyzer, but it does not work as
expected. Is there any configuration/setting I need to do in SQL server, in
order to make it work?
Thanks.
QHi,
There is no configuration change need to be made to make outer joins change
its behaviour.
Thanks
Hari
SQL Server MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:7ACD64A0-A4C2-4BF4-BDE0-36C3A26469AD@.microsoft.com...
> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server,
in
> order to make it work?
> Thanks.
> Q|||Hi
There are no setting that are required to be changed.
Probably you require to change the order of the TABLES that you are using.
LEFT / RIGHT OUTER Joins..
Hope this solved your problem
thanks and regards
Chandra
"Q" wrote:
> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server, in
> order to make it work?
> Thanks.
> Q|||Thanks for all your replies! For some reason LEFT/RIGHT outer does not work.
But *= or =* does work.
Q
"Chandra" wrote:
> Hi
> There are no setting that are required to be changed.
> Probably you require to change the order of the TABLES that you are using.
> LEFT / RIGHT OUTER Joins..
> Hope this solved your problem
> thanks and regards
> Chandra
>
> "Q" wrote:
> > Hello:
> >
> > I tried a query with outer join in SQL Analyzer, but it does not work as
> > expected. Is there any configuration/setting I need to do in SQL server, in
> > order to make it work?
> >
> > Thanks.
> >
> > Q|||My guess is that you are filtering in the WHERE clause and for an ANSI join, you need to put that
filter in the FROM clause. "Doesn't work" is not a good problems description. Posting the error
message, or describing in what way you get unexpected result back is a better problem description.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:F911E946-0796-49C9-B665-27525C761E32@.microsoft.com...
> Thanks for all your replies! For some reason LEFT/RIGHT outer does not work.
> But *= or =* does work.
> Q
> "Chandra" wrote:
>> Hi
>> There are no setting that are required to be changed.
>> Probably you require to change the order of the TABLES that you are using.
>> LEFT / RIGHT OUTER Joins..
>> Hope this solved your problem
>> thanks and regards
>> Chandra
>>
>> "Q" wrote:
>> > Hello:
>> >
>> > I tried a query with outer join in SQL Analyzer, but it does not work as
>> > expected. Is there any configuration/setting I need to do in SQL server, in
>> > order to make it work?
>> >
>> > Thanks.
>> >
>> > Q

out join, need configuration?

Hello:
I tried a query with outer join in SQL Analyzer, but it does not work as
expected. Is there any configuration/setting I need to do in SQL server, in
order to make it work?
Thanks.
Q
Hi,
There is no configuration change need to be made to make outer joins change
its behaviour.
Thanks
Hari
SQL Server MVP
"Q" <Q@.discussions.microsoft.com> wrote in message
news:7ACD64A0-A4C2-4BF4-BDE0-36C3A26469AD@.microsoft.com...
> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server,
in
> order to make it work?
> Thanks.
> Q
|||Hi
There are no setting that are required to be changed.
Probably you require to change the order of the TABLES that you are using.
LEFT / RIGHT OUTER Joins..
Hope this solved your problem
thanks and regards
Chandra
"Q" wrote:

> Hello:
> I tried a query with outer join in SQL Analyzer, but it does not work as
> expected. Is there any configuration/setting I need to do in SQL server, in
> order to make it work?
> Thanks.
> Q
|||Thanks for all your replies! For some reason LEFT/RIGHT outer does not work.
But *= or =* does work.
Q
"Chandra" wrote:
[vbcol=seagreen]
> Hi
> There are no setting that are required to be changed.
> Probably you require to change the order of the TABLES that you are using.
> LEFT / RIGHT OUTER Joins..
> Hope this solved your problem
> thanks and regards
> Chandra
>
> "Q" wrote:
|||My guess is that you are filtering in the WHERE clause and for an ANSI join, you need to put that
filter in the FROM clause. "Doesn't work" is not a good problems description. Posting the error
message, or describing in what way you get unexpected result back is a better problem description.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:F911E946-0796-49C9-B665-27525C761E32@.microsoft.com...[vbcol=seagreen]
> Thanks for all your replies! For some reason LEFT/RIGHT outer does not work.
> But *= or =* does work.
> Q
> "Chandra" wrote:

Other IDE tools

what are some other IDE tools to use with sql server 2000 and 2005? I like
query analyzer a lot and am not crazy about EM or SQL Workbench (or whatever
it is called these days).
What does this community use when you are not using QA, EM or Workbench? I
like to type in T-SQL rather than be using the GUI (but that doesn't mean I
want to use isql/osql :-)
I use QA almost exclusively, and will probably use SQL Server Management
Studio (formally known as SQL Workbench) in SQL Server 2005 exclusively.
I'm happy with both.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kyle Adams" <kadam@.aol.spammersbeware.com> wrote in message
news:udAs5PHjEHA.2556@.TK2MSFTNGP10.phx.gbl...
> what are some other IDE tools to use with sql server 2000 and 2005? I
like
> query analyzer a lot and am not crazy about EM or SQL Workbench (or
whatever
> it is called these days).
> What does this community use when you are not using QA, EM or Workbench?
I
> like to type in T-SQL rather than be using the GUI (but that doesn't mean
I
> want to use isql/osql :-)
>
>
|||I think Management Studio (the IDE formerly known as SQL WorkBench) is going
to turn out very useful, albeit missing features that some users are willing
to bypass upgrading for (e.g. open table). Here are some alternative
management tools you can investigate:
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
"Kyle Adams" <kadam@.aol.spammersbeware.com> wrote in message
news:udAs5PHjEHA.2556@.TK2MSFTNGP10.phx.gbl...
> what are some other IDE tools to use with sql server 2000 and 2005? I
like
> query analyzer a lot and am not crazy about EM or SQL Workbench (or
whatever
> it is called these days).
> What does this community use when you are not using QA, EM or Workbench?
I
> like to type in T-SQL rather than be using the GUI (but that doesn't mean
I
> want to use isql/osql :-)
>
>
|||Kyle Adams wrote:
> what are some other IDE tools to use with sql server 2000 and 2005?
> I like query analyzer a lot and am not crazy about EM or SQL
> Workbench (or whatever it is called these days).
> What does this community use when you are not using QA, EM or
> Workbench? I like to type in T-SQL rather than be using the GUI (but
> that doesn't mean I want to use isql/osql :-)
Imceda Software makes a really nice IDE tool (www.imceda.com)
David G.
|||Hi Kyle,

> what are some other IDE tools to use with sql server 2000 and 2005? I
like
> query analyzer a lot and am not crazy about EM or SQL Workbench (or
whatever
> it is called these days).
> What does this community use when you are not using QA, EM or Workbench?
I
> like to type in T-SQL rather than be using the GUI (but that doesn't mean
I
> want to use isql/osql :-)
You might want to take a look at our Database Workbench -
works with MSDE, 7 and 2000 as well as some other database
engines.
It has GUI tools for table-editting, but no diagramming tools.
In two weeks, we will be releasing a new version with new features
and many GUI improvements.
Check it out at www.upscene.com
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
|||Give Query Analyzer Tool from our website a spin. It's a free tool inclined
towards helping you analyze query plans better.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"Kyle Adams" <kadam@.aol.spammersbeware.com> wrote in message
news:udAs5PHjEHA.2556@.TK2MSFTNGP10.phx.gbl...
> what are some other IDE tools to use with sql server 2000 and 2005? I
like
> query analyzer a lot and am not crazy about EM or SQL Workbench (or
whatever
> it is called these days).
> What does this community use when you are not using QA, EM or Workbench?
I
> like to type in T-SQL rather than be using the GUI (but that doesn't mean
I
> want to use isql/osql :-)
>
>
|||I'd give SQL IDE from imceda a big look at.
http://www.imceda.com
Bryan
"Kyle Adams" <kadam@.aol.spammersbeware.com> wrote in message
news:udAs5PHjEHA.2556@.TK2MSFTNGP10.phx.gbl...
> what are some other IDE tools to use with sql server 2000 and 2005? I
like
> query analyzer a lot and am not crazy about EM or SQL Workbench (or
whatever
> it is called these days).
> What does this community use when you are not using QA, EM or Workbench?
I
> like to type in T-SQL rather than be using the GUI (but that doesn't mean
I
> want to use isql/osql :-)
>
>

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As

someone else said in another post - why have Microsoft removed useful functionality

from the scripting options. It doesn't make sense - were these options

over looked or was it a conscious decision.

When will these

settings be reinstated - (and they need to be able to be set for the

whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options

)

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As someone else said in another post - why have Microsoft removed useful functionality from the scripting options. It doesn't make sense - were these options over looked or was it a conscious decision.

When will these settings be reinstated - (and they need to be able to be set for the whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options )

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

Other Best Practices

As near as I can tell, the SQL Best Practices Analyzer does not have rules
for the following:
row size greater than 8 K
# of columns in a table greater than x
row size greater than x
FK Constraints that are disabled
Other Constraints that are disabled
Triggers that are disabled
Anyone know of a way to analyze this?
Thanks!
Michelle
Thanks Michelle for your feedback. Will definitely consider some of these
for the next release of BPA.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:um%23%23Q9WhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> As near as I can tell, the SQL Best Practices Analyzer does not have rules
> for the following:
> row size greater than 8 K
> # of columns in a table greater than x
> row size greater than x
> FK Constraints that are disabled
> Other Constraints that are disabled
> Triggers that are disabled
> Anyone know of a way to analyze this?
> Thanks!
> Michelle
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.
That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegro ups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>
|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:

> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osql vs Query Analyzer

We have a script with code like this
use master
go
-- do some stuff here
if exists (select name from sysdatabases where name = 'db_name')
begin
use db_name
-- do some stuff
exec storedproc
end
this works fine with Query Analyzer.
With OSQL it creates problem in the line exec storedproc. It says that
the stored procedure is not found.
we call osql as follows
osql -S servername -U username -P Password -i sqlfilename
now if we add the database name to osql
osql -S servername -U username -P Password -i sqlfilename -d db_name
it works fine.
Why should it matter. After all we are using USE statement inside the
script correctly.
How does it work in Query Analyzer.
TIA.That should work. Can you post code which we can run that displays this?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1120679914.292796.77430@.g44g2000cwa.googlegroups.com...
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>|||Hi
You may want to use three part naming for the procedure call.
John
"Data Cruncher" wrote:
> We have a script with code like this
> use master
> go
> -- do some stuff here
> if exists (select name from sysdatabases where name = 'db_name')
> begin
> use db_name
> -- do some stuff
> exec storedproc
> end
> this works fine with Query Analyzer.
> With OSQL it creates problem in the line exec storedproc. It says that
> the stored procedure is not found.
> we call osql as follows
> osql -S servername -U username -P Password -i sqlfilename
> now if we add the database name to osql
>
> osql -S servername -U username -P Password -i sqlfilename -d db_name
> it works fine.
> Why should it matter. After all we are using USE statement inside the
> script correctly.
> How does it work in Query Analyzer.
> TIA.
>

osgl syntax problem

I have a script that runs fine in SQL Query Analyzer but has a syntax error
using osql. It restores several databases and log files. One of the database
s
has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
FROM DISK (BLAH). When run using osql all the databases restore correctly
except for the database with the space in the name. SQL Query Analyzer has n
o
problems with the statement. Is there any way to make this work or do I have
to rename the database with no spaces?
--
Mark Koenig
Tucson, AZIdeally, you should have no spaces in object names, or you get the problem
you encountered. Try:
SET QUOTED IDENTIFIER ON
GO
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"KMK" <kkoenig@.email(DOT)arizona(DOT)edu> wrote in message
news:BFFEDC2D-C3BE-4365-8369-F6F1C7D6D875@.microsoft.com...
>I have a script that runs fine in SQL Query Analyzer but has a syntax error
> using osql. It restores several databases and log files. One of the
> databases
> has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
> FROM DISK (BLAH). When run using osql all the databases restore correctly
> except for the database with the space in the name. SQL Query Analyzer has
> no
> problems with the statement. Is there any way to make this work or do I
> have
> to rename the database with no spaces?
> --
> Mark Koenig
> Tucson, AZ|||It's been awhile since I've used osql but with embedded spaces, you might
need to use brackets. i.e. [My Database] Perhaps, enclose in double quote
marks. Or, single and double quote marks.
If this was a stored procedure, a fully qualified name would be:
[My Server].[My Databse].[My Owner].[sp Select Data]
I did some batch osql about a year ago and I had to send filenames into SQL
Server to generate and execute some bulk insert statements.
In DOS, I passed the filename to a common script that called SQL Server. In
the calling script, I passed the filename as "'C:\Program Files...TXT'".
Note, the single quotes inside the double quotes. Then in the common script
that called SQL Server, I had the following line:
@.osql -E -S %SQLServerName% -d %Database% -Q "exec %1 %~2"
Note: Parameter %1 contained the name of the stored procedure to execute.
Parameter %~2 is the fully qualified name of the input file.
In the stored procedure, I received the filename as varchar(128). When I
concatenated it into the bulk insert statement, I had to enclose it in singl
e
quote marks in order to accomodate embedded spaces. For example, the
statement ended up as BULK INSERT...FROM 'C:\PROGRAM FILES...TXT'. To embed
single quote marks just repeat it. So it's EXEC ('bulk insert ' + @.TableNam
e
+ ' from ''' + @.DataFilename + ''' with (tablock, firstrow = ' +
@.StartRowNumber + ', formatfile = ''' + @.FormatFilename + ''', maxerrors = '
+ @.MaxNumberOfErrors + ')')
Bottom line, you need double quote marks to pass a filename with embedded
spaces from one DOS/batch command script to another. (I don't know if you'r
e
doing this part or not.) Then, it has to be enclosed in single quote marks
for the osql statement. (At least it did for me using the -Q (query)
parameter.) Then, if your concatenating it in a stored procedure, you need
to embed the incoming value in single quote marks.
Hope that helps,
Joe
"KMK" wrote:

> I have a script that runs fine in SQL Query Analyzer but has a syntax erro
r
> using osql. It restores several databases and log files. One of the databa
ses
> has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX"
> FROM DISK (BLAH). When run using osql all the databases restore correctly
> except for the database with the space in the name. SQL Query Analyzer has
no
> problems with the statement. Is there any way to make this work or do I ha
ve
> to rename the database with no spaces?
> --
> Mark Koenig
> Tucson, AZ|||This did work after using SET QUOTED_IDENTIFIER ON. Your response left out
the underline. Thanks for the info!
--
Mark Koenig
Tucson, AZ
"Tom Moreau" wrote:

> Ideally, you should have no spaces in object names, or you get the problem
> you encountered. Try:
> SET QUOTED IDENTIFIER ON
> GO
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "KMK" <kkoenig@.email(DOT)arizona(DOT)edu> wrote in message
> news:BFFEDC2D-C3BE-4365-8369-F6F1C7D6D875@.microsoft.com...
>
>

Orphaned SPID

I have a spid that was killed in the middle of a
rollback. The status message states that the rollback is
100% complete. The query analyzer connection is not there
anymore.
I cannot kill that spid with the kill command. Is there
any way to kill it without bouncing the server?
"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:494701c47ff8$74a7d6b0$a401280a@.phx.gbl...
> I have a spid that was killed in the middle of a
> rollback. The status message states that the rollback is
> 100% complete. The query analyzer connection is not there
> anymore.
> I cannot kill that spid with the kill command. Is there
> any way to kill it without bouncing the server?
Assuming you're talking about bouncing the physical server, stopping and
restarting the mssqlserver service should do it for you...
Steve

Orphaned SPID

I have a spid that was killed in the middle of a
rollback. The status message states that the rollback is
100% complete. The query analyzer connection is not there
anymore.
I cannot kill that spid with the kill command. Is there
any way to kill it without bouncing the server?"canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:494701c47ff8$74a7d6b0$a401280a@.phx.gbl...
> I have a spid that was killed in the middle of a
> rollback. The status message states that the rollback is
> 100% complete. The query analyzer connection is not there
> anymore.
> I cannot kill that spid with the kill command. Is there
> any way to kill it without bouncing the server?
Assuming you're talking about bouncing the physical server, stopping and
restarting the mssqlserver service should do it for you...
Steve