Changing Results View in Query Analyzer

A w ago, I had to change my view to 'Results in Text' in SQL Query
Analyzer. All I did was choose 'Results in Text' from the menu and ran my
query. Now, whenever I open SQLQA, it always puts the results in text,
instead of results in grid (which is what I want). I've looked in the
options window, but see nothing. I've also tried to set the proper option
and save the query I'm working on, but to no avail. Does anybody know how
to get Results in Grid back to the default view option?
Thanks,
BradTools | Options | Results tab | Default results target
These settings apply to the current connection and future connections.
The last connection window closed determines the default when Q/A is
next opened.
To make sure you have the desired default settings, close all session
windows first, then set the options before closing Q/A.
footyplayer wrote:
> A w ago, I had to change my view to 'Results in Text' in SQL Query
> Analyzer. All I did was choose 'Results in Text' from the menu and ran my
> query. Now, whenever I open SQLQA, it always puts the results in text,
> instead of results in grid (which is what I want). I've looked in the
> options window, but see nothing. I've also tried to set the proper option
> and save the query I'm working on, but to no avail. Does anybody know how
> to get Results in Grid back to the default view option?
> Thanks,
> Brad
>|||Hi
Under the tools/options/results menus and tables there is a default results
option which you can choose from a combo.
John
"footyplayer" <anonymous@.discussions.microsoft.com> wrote in message
news:u1WM3G$sFHA.3252@.TK2MSFTNGP10.phx.gbl...
>A w ago, I had to change my view to 'Results in Text' in SQL Query
>Analyzer. All I did was choose 'Results in Text' from the menu and ran my
>query. Now, whenever I open SQLQA, it always puts the results in text,
>instead of results in grid (which is what I want). I've looked in the
>options window, but see nothing. I've also tried to set the proper option
>and save the query I'm working on, but to no avail. Does anybody know how
>to get Results in Grid back to the default view option?
> Thanks,
> Brad
>|||Thanks Trey!
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:esp6wZ$sFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Tools | Options | Results tab | Default results target
> These settings apply to the current connection and future connections. The
> last connection window closed determines the default when Q/A is next
> opened.
> To make sure you have the desired default settings, close all session
> windows first, then set the options before closing Q/A.
> footyplayer wrote:

Changing Result Column Name

How do I change the column name returned in the result set of a For-
Xml-Explicit query?
Currently I get
XML_F52E2B61-18A1-11d1-B10500805F49916B
---
<root><child/>...</root>
I've tried select ( select .....for xml explicit ) AS MyName, but
that doesn't fly with query analyzer.
Cheers,
AedenAre you on SQL 2000 or 2005?
<aeden.jameson@.gmail.com> wrote in message
news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
> How do I change the column name returned in the result set of a For-
> Xml-Explicit query?
> Currently I get
> XML_F52E2B61-18A1-11d1-B10500805F49916B
> ---
> <root><child/>...</root>
>
> I've tried select ( select .....for xml explicit ) AS MyName, but
> that doesn't fly with query analyzer.
> Cheers,
> Aeden
>|||SQL 2k
On Sep 15, 5:48 pm, "Mike C#" <x...@.xyz.com> wrote:
> Are you on SQL 2000 or 2005?
> <aeden.jame...@.gmail.com> wrote in message
> news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
>
> > How do I change the column name returned in the result set of a For-
> > Xml-Explicit query?
> > Currently I get
> > XML_F52E2B61-18A1-11d1-B10500805F49916B
> > ---
> > <root><child/>...</root>
> > I've tried select ( select .....for xml explicit ) AS MyName, but
> > that doesn't fly with query analyzer.
> > Cheers,
> > Aeden- Hide quoted text -
> - Show quoted text -|||The only thing I can think of in SQL 2K would be to put the result into a
temporary table first and select from that. You could probably do something
with an XML variable in 2005, which is why I asked. FOR XML EXPLICIT is
deprecated in SQL 2005 and 2008. If you're pulling the result client-side,
the column name might not matter since you can usually reference the result
set columns by ordinal number.
<aeden.jameson@.gmail.com> wrote in message
news:1189916977.141263.183740@.n39g2000hsh.googlegroups.com...
> SQL 2k
>
> On Sep 15, 5:48 pm, "Mike C#" <x...@.xyz.com> wrote:
>> Are you on SQL 2000 or 2005?
>> <aeden.jame...@.gmail.com> wrote in message
>> news:1189889127.677407.265150@.n39g2000hsh.googlegroups.com...
>>
>> > How do I change the column name returned in the result set of a For-
>> > Xml-Explicit query?
>> > Currently I get
>> > XML_F52E2B61-18A1-11d1-B10500805F49916B
>> > ---
>> > <root><child/>...</root>
>> > I've tried select ( select .....for xml explicit ) AS MyName, but
>> > that doesn't fly with query analyzer.
>> > Cheers,
>> > Aeden- Hide quoted text -
>> - Show quoted text -
>

Changing Query behavior based on local vs. remote context?

Hello,
I'm not sure if this is the right ng for this question, but here goes...
I just installed a product that uses SQL Express. When I run queries
against the installed instance I get different behavior depending on if the
query is run from Management Studio locally or from SSMS running on a remote
machine. In both cases the user is a domain admin and the query is
identical. The local query returns 1000+ rows, the remote query returns
ZERO. If the query returns less than say 100 rows, there is no difference
between remote and local.
Has anyone else seen this or use use/implemented it themselves?
I'm curious; how this behavior is defined/implemented? Is it database or
instance specific? It seems to be instance-wide in my case but I'm not sure
yet. I've been digging around in SSMS but haven't found any settings or
properties that seem to apply to this, so any sugestions on where to look
would be appreciated.
Thanks!
KeithMy guess would be that there is a problem on the remote machine. Have you
tried a 2nd remote query?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||Yes...a dozen or so actually. And I've tried them on different remote
machines as well with SSMS and SSMSe, in addition to using queries from the
query window and "Open Table/View" from the Object Explorer.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> My guess would be that there is a problem on the remote machine. Have you
> tried a 2nd remote query?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Keith" <keith@.alh.com> wrote in message
> news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like larger network packets are getting dropped. Some bridges that
do not do packet splitting can cause this.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||OK then I would have to agree with Geoff in that it sounds like an issue
with the servers communications. If you can run this fine locally and all
other machines have issues it kind of narrows it down to that machine.
Check the event logs and see if there are errors on that server.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:e8xPg6C4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Yes...a dozen or so actually. And I've tried them on different remote
> machines as well with SSMS and SSMSe, in addition to using queries from
> the query window and "Open Table/View" from the Object Explorer.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
>|||Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
Keith's description reminds me of a very weird error that a formed DSL
provider of mine had. I was mainly reading my after a holiday in SSH2
connection to a Unix account. And that worked fine. But then I got the
idea to look at some web site, but I could not access it. Tried another.
Did not work. A third one. Eventually I tried running lynx from the Unix
account. And that hung too! But I could open a new connection and read my
mail.
Finally I came around to put a very short text file on my web site, and
sure enough, this page did turn up in the browser. So I concluded they
had an error where split packets got lost, but as long as the packets
were small, things worked.
(The reason this provider is a former provider, is simply because I moved
to a new flat, and they could not deliver DSL there.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:erXXwEE4HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
I should add that this behavior does not happen running queries against any
other SQL Express, SQL 2005, SQL 2000, or MSDE instance - named or
otherwise, physical or virtual machine - anywhere on my network.
Only this particular instance behaves this way.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> OK then I would have to agree with Geoff in that it sounds like an issue
> with the servers communications. If you can run this fine locally and all
> other machines have issues it kind of narrows it down to that machine.
Unless you install the product on another machine and get the same behavior,
which I have.

> Check the event logs and see if there are errors on that server.
The Event Viewer does not show any errors, nor does the error log in the
instance's data directory.
Considering that this behavior is present along with the installation of
this particular product, and that other named SQL and SQL Express instances
on the same machine do not demonstrate this behavior - even when returning
result sets orders of magnitude larger and on the same physical network -
I'm inclined to believe that this behavior is not machine specific but
rather localized to the named instance installed by this particular product.
Meaning, some configurable property of SQL Server.
I'm totally willing to believe that this is a bug in the product - a
misconfiguration of some kind - but it sounds like most folks don't think
that this type of (mis)configuration is even possible.
Oh, did I mention that this is a Microsoft product's install of SQL Express
;-)
k|||> Unless you install the product on another machine and get the same
> behavior, which I have.

> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
Tidbits like these are always good to know. The answers we gave were based
on the info given. I would ping the makers of the product then and see if
they can provide a clue. Other than some ANSI settings at the connection or
database object level that may return different results I know of no
settings or configuration that would produce this behavior. Things like ANSI
PADDING, ANSI NULLS etc can affect how SQL Server treats the data for joins
and the Where clause which can produce different result sets. But none of
that is based on the size of the result set. I would check to see if the
connection settings are the same for both the local and remote SSMS. But
like I said that doesn't explain all the symptoms you mention.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:O%23h0HtF4HHA.484@.TK2MSFTNGP06.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Unless you install the product on another machine and get the same
> behavior, which I have.
>
> The Event Viewer does not show any errors, nor does the error log in the
> instance's data directory.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
> I'm totally willing to believe that this is a bug in the product - a
> misconfiguration of some kind - but it sounds like most folks don't think
> that this type of (mis)configuration is even possible.
> Oh, did I mention that this is a Microsoft product's install of SQL
> Express ;-)
> k
>
>|||I discovered this when I was a network admin/dba many years ago. We had a
business network and a process control network that had to be splittable for
political reasons. We had an ethernet bridge set up that we could unplug to
isolate the systems. I was setting up Replication (SQL 6.0/6.5, I think)
between two servers. The both had FDDI interfaces , but there was the
ethernet bridge between them. They negotiated large frame sizes since they
were both on FDDI, not considering that the equipment in the middle couldn't
pass that big a packet. Whatever genius designed the netotiation protocol
didn't bother to test and see if an actual large packet could make it
through. Drove me buggy troubleshooting it. Test queries worked. Ping
never failed. Drives mapped. Then replication would fail to sync. I
eventually found the issue and limited the packet size on the
publisher/distributor and never had another problem.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns998F55155F70Yazorman@.127.0.0.1...
> Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Keith's description reminds me of a very weird error that a formed DSL
> provider of mine had. I was mainly reading my after a holiday in SSH2
> connection to a Unix account. And that worked fine. But then I got the
> idea to look at some web site, but I could not access it. Tried another.
> Did not work. A third one. Eventually I tried running lynx from the Unix
> account. And that hung too! But I could open a new connection and read my
> mail.
> Finally I came around to put a very short text file on my web site, and
> sure enough, this page did turn up in the browser. So I concluded they
> had an error where split packets got lost, but as long as the packets
> were small, things worked.
>
> (The reason this provider is a former provider, is simply because I moved
> to a new flat, and they could not deliver DSL there.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.

Changing Number to Text in Query

I have two fields that have either a 0 or 1 in them. When I do a SELECT to
bring the data back, I would like to change the 0 to an "N" and a 1 to a
"Y".
I read up on the CASE statement and had something like but kept getting
syntax errors.
Select Name, Address, City,
CASE field_in_question
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END,
State, Zip from Notes
Can someone help a feebled old brain figure this one out?
Thanks,
Gary>> I read up on the CASE statement and had something like but kept getting
Can you post the error messages here?
Anith|||The error message says "Syntax error (missing operator) in query expression.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OCdGspmAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Can you post the error messages here?
> --
> Anith
>|||Since your original post has no attributions, let us start from simple
issues. Are you use SQL Server or Access? CASE is not supported in Access.
If you are using SQL Server, are you using Query Analyzer? Some other
interfaces have limitations on certain constructs they allow
If you are using Query Analyzer, can you post your CREATE TABLE statement
for the table Notes? For details refer to www.aspfaq.com/5006
Anith|||That's the problem. I am using Access with a Visual Basic front end.
Is there a way to do what I need to do in Access SQL?
Thanks,
Gary
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Since your original post has no attributions, let us start from simple
> issues. Are you use SQL Server or Access? CASE is not supported in Access.
> If you are using SQL Server, are you using Query Analyzer? Some other
> interfaces have limitations on certain constructs they allow
> If you are using Query Analyzer, can you post your CREATE TABLE statement
> for the table Notes? For details refer to www.aspfaq.com/5006
> --
> Anith
>|||Create a stored procedure or a view, and select from that within Access
instead of accessing the table directly.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> That's the problem. I am using Access with a Visual Basic front end.
> Is there a way to do what I need to do in Access SQL?
> Thanks,
> Gary
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
Access.
statement
>|||Oh, and don't use the view designer, because it doesn't support case! Use
Query Analyzer...
http://www.aspfaq.com/
(Reverse address to reply.)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Create a stored procedure or a view, and select from that within Access
> instead of accessing the table directly.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary Paris" <garyparis@.yada.com> wrote in message
> news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> Access.
> statement
>|||I am using Access as the back end and not using SQL server. I don't believe
Query Analyzer comes with Access (although I could be wrong). Would it be
better to post my question in the Access group or is there a way to do what
I want with Access SQL?
Thanks,
Gary
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Oh, and don't use the view designer, because it doesn't support case! Use
> Query Analyzer...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||Sorry, I thought you were using Access to connect to SQL Server. Yes, you
can do this in Access using the IIF function. See the Access documentation
or post to an Access newsgroup. This group is dedicated to programming
in/for SQL Server.
"Gary Paris" <garyparis@.yada.com> wrote in message
news:O3$5VinAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> I am using Access as the back end and not using SQL server. I don't
believe
> Query Analyzer comes with Access (although I could be wrong). Would it
be
> better to post my question in the Access group or is there a way to do
what
> I want with Access SQL?
> Thanks,
> Gary
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
Use
other
>

changing loop

hi,

i have a table #emails containing one coloumn called email. this is a list the contains emails.

i need to run a query that creats emails list like the following:

a@.xxx.com;b@.yyy.com, etc'

I am using the following code:

while exists(select 1 from #emails)
begin
select top 1 @.email=email
from #emails

set @.emails = @.emails + @.email + ';'

delete from #emails
where email=@.email

end

the thing is that when I create this list I get a high CPU usage.

Can someone help by suggesting a different way of creating this list and by that acheiving 'normal' CPU usage?

thx,

tomer

possible alternatives

--SQL SERVER 2005
DECLARE @.str varchar(4000)
SET @.str = (SELECT d.name + ','
FROM HumanResources.Department d
ORDER BY d.name
FOR XML PATH(''))
SET @.str = SUBSTRING(@.str,1,LEN(@.str)-1)
SELECT @.str

--SQL SERVER 2000
DECLARE @.name varchar(50)
DECLARE @.str varchar(4000)
SET @.str = ''
DECLARE Build CURSOR
LOCAL
FORWARD_ONLY
FOR
SELECT d.name
FROM HumanResources.Department d
ORDER BY d.name
OPEN Build
FETCH NEXT FROM Build INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str = @.str + @.name + ','
FETCH NEXT FROM Build INTO @.name
END
CLOSE Build
DEALLOCATE Build
SET @.str = SUBSTRING(@.str,1,LEN(@.str)-1)
SELECT @.str

|||

...forgot this...eternity is a long time

DECLARE @.count int

SET @.count = (SELECT COUNT(*) FROM #emails)

while @.count > 0

begin

select top 1 @.email=email

from #emails

set @.emails = @.emails + @.email + ';'

delete from #emails

where email=@.email

SET @.count = @.count - 1

end

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

changing Field Lenght via query analyzer

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB
its 70 rights now I want change it to 120 its a nvarchar
how can I do it , I am using a ASP page
ThanksALTER TABLE tablename ALTER COLUMN columnname NVARCHAR(120)

You can just pass this statement to the database.

You should check out Books Online. it's free documentation that comes with SQL server.

Changing default folder for saving query text

When I save a query (not a query result) in SSMS, the default root folder is
My Documents\SQL Server Management Studio\Projects
Is there any way to change this default and make it persist beyond the current session?

Hello,

Yes it is possible for you to set the default folder to your prefered location. Open Microsoft SQL Server 2005 Management Studio.

From the Menu select Tools -> Options. Select Query Results noted and enter your prefered location where you want to save your queries in the "Default locations for saving query results" text box.

Thanks

|||Yes, I'm well aware that this setting is available for query results, but I'm asking about the query text itself - how do I change the default save path for the query text?

Changing database query (server, database, tables and query)

I'm working with ASP.NET web forms (using Visual Studio and C# code)
I've now introduced 'SQL Server 2000 Reporting Services' and am impressed
with what I've seen so far (albeit that I've only spent a few hours with it).
:)
1). I need the ability to point the report to a server / database rather
than always have it running against the same target. Similarly I may need to
change the SQL syntax of the query being run (rather than relying on
parameter passing).
It looks as if I need to do this via making use of the web service - but I
can't get my head around this.
Thanks in advance
SteveI've just posted a DPE to
http://workspaces.gotdotnet.com/appworld that may help you.
Regards
Toby
"Steve_S3T" <Steve_S3T@.discussions.microsoft.com> wrote in message
news:4AEF4962-798A-4C11-BFEF-B30E4800ADDA@.microsoft.com...
> I'm working with ASP.NET web forms (using Visual Studio and C# code)
> I've now introduced 'SQL Server 2000 Reporting Services' and am impressed
> with what I've seen so far (albeit that I've only spent a few hours with
it).
> :)
> 1). I need the ability to point the report to a server / database rather
> than always have it running against the same target. Similarly I may need
to
> change the SQL syntax of the query being run (rather than relying on
> parameter passing).
> It looks as if I need to do this via making use of the web service - but
I
> can't get my head around this.
> Thanks in advance
> Steve