Hello to everyone,
I want to ask if there is a way ,maybe through a strored procedure or sql
script
to change massive in 200 databases for example the recovery model from full
to simple ?
If anybody has an idea or knows i will appreciate it.
Thanx a lot.
Cipher.
See ALTER DATABASE in BOL
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Are you asking for a way to change this value against 200 different
databases?
"Cipher" <cipher@.aol.com> wrote in message
news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hello to everyone,
> I want to ask if there is a way ,maybe through a strored procedure or sql
> script
> to change massive in 200 databases for example the recovery model from
> full to simple ?
> If anybody has an idea or knows i will appreciate it.
> Thanx a lot.
> Cipher.
>
|||Yes,exactly.
I want to change massive all the 200 databases recovery
model option from full to simple but i dont want to achieve this manually
because of the huge overhead.
For that reason i asked ,if theres a more quicker and better way to achive
this.
Thnx a lot.
Cipher.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23DV67OlZHHA.348@.TK2MSFTNGP02.phx.gbl...
> Are you asking for a way to change this value against 200 different
> databases?
> "Cipher" <cipher@.aol.com> wrote in message
> news:eIh22lkZHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
|||Tibor,
I will even keep MSDB in simple recovery mode and takes the differential
backup twice a day.
SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
FROM sys.databases
WHERE name NOT IN('master', 'tempdb','MSDB')
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> No version posted, assuming 2005:
> Modify below to your liking, run it, take the result, tidy up and verify,
> and execute it.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM sys.databases
> WHERE name NOT IN('master', 'tempdb')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Morgan" <morgan@.heaven.com> wrote in message
> news:%23V1czMnZHHA.1400@.TK2MSFTNGP06.phx.gbl...
|||First,thnx a lot guys for your support.
I run this sql script through Query Analyzer :
use master;
select 'alter database' +name+ 'set recovery simple'
from dbo.sysdatabases
where name not in('master','tempdb','msdb','model')
go
but the weird is that the script runs succefully without errors ,but it
didnt make any change
to any databases!I check the recovery model and remains unfortunately full
in all databases..
i dont understand..
Also because i forgot to mention it,the SQL version is SQL 2000 SP 4
Cipher.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Tibor,
> I will even keep MSDB in simple recovery mode and takes the differential
> backup twice a day.
> SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
> FROM sys.databases
> WHERE name NOT IN('master', 'tempdb','MSDB')
> Thanks
> Hari
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eSAEbjnZHHA.5044@.TK2MSFTNGP05.phx.gbl...
>
|||Yes,it works fine now.
The script achieve a massive recovery model change!
Thx a lot Tibor!
Cipher.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKlFswtZHHA.4808@.TK2MSFTNGP04.phx.gbl...
> The script generates your ALTER DATABASE commands for you. Take the result
> of the script, paste in into a query window and execute it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cipher" <cipher@.aol.com> wrote in message
> news:eZOTxetZHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Tibor,
I agree with you...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OJpY2gtZHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Hi Hari,
> We all feel differently about msdb. That is why I said "Modify below to
> your liking". I prefer to do log backups for msdb, and have msdb as the
> last database for each backup execution. This way I always have backup of
> the most recent backup history. I might not be the one who is doing the
> restore, and if somebody restore msdb and then the other databases based
> on backuphistory, they won't get the databases as up to date as the
> backups would allow. Many find this overkill, though. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVZMFxpZHHA.1400@.TK2MSFTNGP06.phx.gbl...
>
Showing posts with label example. Show all posts
Showing posts with label example. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
Recover Image From SQL Server CE Database
Hello,
Im using the same DB used in northwindoledb example, i need a similar app but in C#, not C++, so i use this DB and make a form for Pocket PC 2003 SE Emulator in Vista (device application) with visual studio 2005, i use a function to recover the image and display in a PictureBox but when this image create a BitMap give me an error "Value does not fall within the expected range.", i seek a lot in internet and don't find a solution, the function is:
public static Image Bytes2Image(byte[] bytes)
{
if (bytes == null) return null;
MemoryStream ms = new MemoryStream(bytes,0,bytes.Length );
Bitmap bm = null;
ms.Read(bytes, 0, bytes.Length);
bm = new Bitmap(ms);
return bm;
}
If someone can help me i will appreciatte
How about:
Code Snippet
public System.Drawing.Image BytesToImage(byte[] bytes)
{
if (bytes == null) return null;
System.IO.MemoryStream ms = new System.IO.MemoryStream(bytes);
return System.Drawing.Image.FromStream(ms);
}
|||Erik,
System.Drawing.Image.FromStream(ms);
Don't exist in Compact Framework, all post suggest the use of new Bitmap(ms) instead, but this code give the error... Other user have similar error but cannot find the solution, the C++ version of the program work ok in the emulator, but in C# don't work (i'm using different aproach, i don't understand C++), some have a similar program in C# than manipulate images from SQL 2005 CE for Pocket PC?
Recover Image From SQL Server CE Database
Hello,
Im using the same DB used in northwindoledb example, i need a similar app but in C#, not C++, so i use this DB and make a form for Pocket PC 2003 SE Emulator in Vista (device application) with visual studio 2005, i use a function to recover the image and display in a PictureBox but when this image create a BitMap give me an error "Value does not fall within the expected range.", i seek a lot in internet and don't find a solution, the function is:
public static Image Bytes2Image(byte[] bytes)
{
if (bytes == null) return null;
MemoryStream ms = new MemoryStream(bytes,0,bytes.Length );
Bitmap bm = null;
ms.Read(bytes, 0, bytes.Length);
bm = new Bitmap(ms);
return bm;
}
If someone can help me i will appreciatte
How about:
Code Snippet
public System.Drawing.Image BytesToImage(byte[] bytes)
{
if (bytes == null) return null;
System.IO.MemoryStream ms = new System.IO.MemoryStream(bytes);
return System.Drawing.Image.FromStream(ms);
}
|||Erik,
System.Drawing.Image.FromStream(ms);
Don't exist in Compact Framework, all post suggest the use of new Bitmap(ms) instead, but this code give the error... Other user have similar error but cannot find the solution, the C++ version of the program work ok in the emulator, but in C# don't work (i'm using different aproach, i don't understand C++), some have a similar program in C# than manipulate images from SQL 2005 CE for Pocket PC?
Saturday, February 25, 2012
Records per page
Hi.
I want to know how many records there are per page.
For example there may be a textbox showing the count of records per page.
What is the expression for this?
Any help would be appreciated.
I don't know if there is anything predefined in SQL for this. You may have to calculate it yourself by knowing the size of the report viewer and the size of your textbox fields.Records are not listed in order
Hi,
When I used SQL Server Management Studio to open several tables (SQL 2005),
some records are not listed in the order - for example, listed by orderid,
customerid, or so on.
Is there a way that I can have them listed in order?
Many thanks in advance.Hi,
If you do not have a clustered index then data will be displayed based on
the way it gets stored. If you want
any specific order / any batch seeks then go for a clustered index based on
the requirement.
Thanks
Hari
SQL Server MVP
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||The ANSI SQL standard states that data will not be stored in any particular
order.
The exception is when there is a 'clustered' index, in which case the data
is ordered by the index.
Always add a [ORDER BY] clause to your queries to retrieve data in a
determined order.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||Hi,
Thanks for both of your explanations.
Just to ensure that I understood correctly, what you meant is that it's
perfectly normal for records not displaying in any particular order if
tables are opened with tool such as Management Studio?
If I wish to have it displayed in any particular order, it has be done by
using queries either by writing one or done with application or tools.
Just one final question about the SQL Server Management Studio and that is,
does it come with any functions similar in Access that can with one click to
have data displaying ascending (A-Z) or descending (Z-A)?
Many thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The ANSI SQL standard states that data will not be stored in any
> particular order.
> The exception is when there is a 'clustered' index, in which case the data
> is ordered by the index.
> Always add a [ORDER BY] clause to your queries to retrieve data in a
> determined order.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "xfile" <cou-cou@.remove.nospam.com> wrote in message
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>|||xfile wrote:
> Hi,
> Thanks for both of your explanations.
> Just to ensure that I understood correctly, what you meant is that it's
> perfectly normal for records not displaying in any particular order if
> tables are opened with tool such as Management Studio?
> If I wish to have it displayed in any particular order, it has be done by
> using queries either by writing one or done with application or tools.
That's right.
Just to expand on what Hari and Arnie said. A clustered index
implements some ordering in an internal index structure (not
necessarily at the level of the data rows or data pages) but it does
not directly control the order in which row will be returned from a
query. For that you need to use an ORDER BY clause.
> Just one final question about the SQL Server Management Studio and that is
,
> does it come with any functions similar in Access that can with one click
to
> have data displaying ascending (A-Z) or descending (Z-A)?
>
No. SSMS is a management tool. It isn't really designed with that kind
of application in mind. Returning every row and then dynamically
sorting them wouldn't be a very sensible feature to have in a client
server environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can right click on the column and select Pane > Criteria to sort the
result.
KL.
"xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||You can only guarantee the order of the results by using an
order by clause in the SQL statement.
There is no A-Z or Z-A functionality in SQL Server - that's
just a MS Access thing. All it does is an order by "under
the covers" for you.
When you use an order by clause, you can specify ASC for
ascending or DESC for descending. If you don't specify the
ordering, ASC is the default that will be used.
-Sue
On Tue, 22 Aug 2006 15:50:27 +0800, "xfile"
<cou-cou@.remove.nospam.com> wrote:
>Hi,
>Thanks for both of your explanations.
>Just to ensure that I understood correctly, what you meant is that it's
>perfectly normal for records not displaying in any particular order if
>tables are opened with tool such as Management Studio?
>If I wish to have it displayed in any particular order, it has be done by
>using queries either by writing one or done with application or tools.
>Just one final question about the SQL Server Management Studio and that is,
>does it come with any functions similar in Access that can with one click t
o
>have data displaying ascending (A-Z) or descending (Z-A)?
>Many thanks.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
>|||Hi,
Thanks again for all of your kind explanations.
I did find the pane-criteria, and tried a few times and happy with the
results even I have to add all columns into criteria.
I am totally unfamiliar with database and accustomed to Access simple
graphic interface. We started using SQL 2000 last year (from hosting
company) and all queries are built-in with applications, and we recently
upgraded to a new version of the application and also moved to SQL 2005.
SQL 2005 is fast!!!
The purpose is to open some tables and delete testing data.
Again, many thanks for all of your kind explanations.
"KL" <noname@.nowhere.com> wrote in message
news:OWDzCvexGHA.980@.TK2MSFTNGP04.phx.gbl...
>
> You can right click on the column and select Pane > Criteria to sort the
> result.
> KL.
>
> "xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>
When I used SQL Server Management Studio to open several tables (SQL 2005),
some records are not listed in the order - for example, listed by orderid,
customerid, or so on.
Is there a way that I can have them listed in order?
Many thanks in advance.Hi,
If you do not have a clustered index then data will be displayed based on
the way it gets stored. If you want
any specific order / any batch seeks then go for a clustered index based on
the requirement.
Thanks
Hari
SQL Server MVP
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||The ANSI SQL standard states that data will not be stored in any particular
order.
The exception is when there is a 'clustered' index, in which case the data
is ordered by the index.
Always add a [ORDER BY] clause to your queries to retrieve data in a
determined order.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||Hi,
Thanks for both of your explanations.
Just to ensure that I understood correctly, what you meant is that it's
perfectly normal for records not displaying in any particular order if
tables are opened with tool such as Management Studio?
If I wish to have it displayed in any particular order, it has be done by
using queries either by writing one or done with application or tools.
Just one final question about the SQL Server Management Studio and that is,
does it come with any functions similar in Access that can with one click to
have data displaying ascending (A-Z) or descending (Z-A)?
Many thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The ANSI SQL standard states that data will not be stored in any
> particular order.
> The exception is when there is a 'clustered' index, in which case the data
> is ordered by the index.
> Always add a [ORDER BY] clause to your queries to retrieve data in a
> determined order.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "xfile" <cou-cou@.remove.nospam.com> wrote in message
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>|||xfile wrote:
> Hi,
> Thanks for both of your explanations.
> Just to ensure that I understood correctly, what you meant is that it's
> perfectly normal for records not displaying in any particular order if
> tables are opened with tool such as Management Studio?
> If I wish to have it displayed in any particular order, it has be done by
> using queries either by writing one or done with application or tools.
That's right.
Just to expand on what Hari and Arnie said. A clustered index
implements some ordering in an internal index structure (not
necessarily at the level of the data rows or data pages) but it does
not directly control the order in which row will be returned from a
query. For that you need to use an ORDER BY clause.
> Just one final question about the SQL Server Management Studio and that is
,
> does it come with any functions similar in Access that can with one click
to
> have data displaying ascending (A-Z) or descending (Z-A)?
>
No. SSMS is a management tool. It isn't really designed with that kind
of application in mind. Returning every row and then dynamically
sorting them wouldn't be a very sensible feature to have in a client
server environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can right click on the column and select Pane > Criteria to sort the
result.
KL.
"xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||You can only guarantee the order of the results by using an
order by clause in the SQL statement.
There is no A-Z or Z-A functionality in SQL Server - that's
just a MS Access thing. All it does is an order by "under
the covers" for you.
When you use an order by clause, you can specify ASC for
ascending or DESC for descending. If you don't specify the
ordering, ASC is the default that will be used.
-Sue
On Tue, 22 Aug 2006 15:50:27 +0800, "xfile"
<cou-cou@.remove.nospam.com> wrote:
>Hi,
>Thanks for both of your explanations.
>Just to ensure that I understood correctly, what you meant is that it's
>perfectly normal for records not displaying in any particular order if
>tables are opened with tool such as Management Studio?
>If I wish to have it displayed in any particular order, it has be done by
>using queries either by writing one or done with application or tools.
>Just one final question about the SQL Server Management Studio and that is,
>does it come with any functions similar in Access that can with one click t
o
>have data displaying ascending (A-Z) or descending (Z-A)?
>Many thanks.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
>|||Hi,
Thanks again for all of your kind explanations.
I did find the pane-criteria, and tried a few times and happy with the
results even I have to add all columns into criteria.
I am totally unfamiliar with database and accustomed to Access simple
graphic interface. We started using SQL 2000 last year (from hosting
company) and all queries are built-in with applications, and we recently
upgraded to a new version of the application and also moved to SQL 2005.
SQL 2005 is fast!!!
The purpose is to open some tables and delete testing data.
Again, many thanks for all of your kind explanations.
"KL" <noname@.nowhere.com> wrote in message
news:OWDzCvexGHA.980@.TK2MSFTNGP04.phx.gbl...
>
> You can right click on the column and select Pane > Criteria to sort the
> result.
> KL.
>
> "xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>
Records are not listed in order
Hi,
When I used SQL Server Management Studio to open several tables (SQL 2005),
some records are not listed in the order - for example, listed by orderid,
customerid, or so on.
Is there a way that I can have them listed in order?
Many thanks in advance.Hi,
If you do not have a clustered index then data will be displayed based on
the way it gets stored. If you want
any specific order / any batch seeks then go for a clustered index based on
the requirement.
Thanks
Hari
SQL Server MVP
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||The ANSI SQL standard states that data will not be stored in any particular
order.
The exception is when there is a 'clustered' index, in which case the data
is ordered by the index.
Always add a [ORDER BY] clause to your queries to retrieve data in a
determined order.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||Hi,
Thanks for both of your explanations.
Just to ensure that I understood correctly, what you meant is that it's
perfectly normal for records not displaying in any particular order if
tables are opened with tool such as Management Studio?
If I wish to have it displayed in any particular order, it has be done by
using queries either by writing one or done with application or tools.
Just one final question about the SQL Server Management Studio and that is,
does it come with any functions similar in Access that can with one click to
have data displaying ascending (A-Z) or descending (Z-A)?
Many thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The ANSI SQL standard states that data will not be stored in any
> particular order.
> The exception is when there is a 'clustered' index, in which case the data
> is ordered by the index.
> Always add a [ORDER BY] clause to your queries to retrieve data in a
> determined order.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "xfile" <cou-cou@.remove.nospam.com> wrote in message
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>|||xfile wrote:
> Hi,
> Thanks for both of your explanations.
> Just to ensure that I understood correctly, what you meant is that it's
> perfectly normal for records not displaying in any particular order if
> tables are opened with tool such as Management Studio?
> If I wish to have it displayed in any particular order, it has be done by
> using queries either by writing one or done with application or tools.
That's right.
Just to expand on what Hari and Arnie said. A clustered index
implements some ordering in an internal index structure (not
necessarily at the level of the data rows or data pages) but it does
not directly control the order in which row will be returned from a
query. For that you need to use an ORDER BY clause.
> Just one final question about the SQL Server Management Studio and that is,
> does it come with any functions similar in Access that can with one click to
> have data displaying ascending (A-Z) or descending (Z-A)?
>
No. SSMS is a management tool. It isn't really designed with that kind
of application in mind. Returning every row and then dynamically
sorting them wouldn't be a very sensible feature to have in a client
server environment.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can right click on the column and select Pane > Criteria to sort the
result.
KL.
"xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||You can only guarantee the order of the results by using an
order by clause in the SQL statement.
There is no A-Z or Z-A functionality in SQL Server - that's
just a MS Access thing. All it does is an order by "under
the covers" for you.
When you use an order by clause, you can specify ASC for
ascending or DESC for descending. If you don't specify the
ordering, ASC is the default that will be used.
-Sue
On Tue, 22 Aug 2006 15:50:27 +0800, "xfile"
<cou-cou@.remove.nospam.com> wrote:
>Hi,
>Thanks for both of your explanations.
>Just to ensure that I understood correctly, what you meant is that it's
>perfectly normal for records not displaying in any particular order if
>tables are opened with tool such as Management Studio?
>If I wish to have it displayed in any particular order, it has be done by
>using queries either by writing one or done with application or tools.
>Just one final question about the SQL Server Management Studio and that is,
>does it come with any functions similar in Access that can with one click to
>have data displaying ascending (A-Z) or descending (Z-A)?
>Many thanks.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
>> The ANSI SQL standard states that data will not be stored in any
>> particular order.
>> The exception is when there is a 'clustered' index, in which case the data
>> is ordered by the index.
>> Always add a [ORDER BY] clause to your queries to retrieve data in a
>> determined order.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "xfile" <cou-cou@.remove.nospam.com> wrote in message
>> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>>
>|||Hi,
Thanks again for all of your kind explanations.
I did find the pane-criteria, and tried a few times and happy with the
results even I have to add all columns into criteria.
I am totally unfamiliar with database and accustomed to Access simple
graphic interface. We started using SQL 2000 last year (from hosting
company) and all queries are built-in with applications, and we recently
upgraded to a new version of the application and also moved to SQL 2005.
SQL 2005 is fast!!!
The purpose is to open some tables and delete testing data.
Again, many thanks for all of your kind explanations.
"KL" <noname@.nowhere.com> wrote in message
news:OWDzCvexGHA.980@.TK2MSFTNGP04.phx.gbl...
>
> You can right click on the column and select Pane > Criteria to sort the
> result.
> KL.
>
> "xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>
When I used SQL Server Management Studio to open several tables (SQL 2005),
some records are not listed in the order - for example, listed by orderid,
customerid, or so on.
Is there a way that I can have them listed in order?
Many thanks in advance.Hi,
If you do not have a clustered index then data will be displayed based on
the way it gets stored. If you want
any specific order / any batch seeks then go for a clustered index based on
the requirement.
Thanks
Hari
SQL Server MVP
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||The ANSI SQL standard states that data will not be stored in any particular
order.
The exception is when there is a 'clustered' index, in which case the data
is ordered by the index.
Always add a [ORDER BY] clause to your queries to retrieve data in a
determined order.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"xfile" <cou-cou@.remove.nospam.com> wrote in message
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||Hi,
Thanks for both of your explanations.
Just to ensure that I understood correctly, what you meant is that it's
perfectly normal for records not displaying in any particular order if
tables are opened with tool such as Management Studio?
If I wish to have it displayed in any particular order, it has be done by
using queries either by writing one or done with application or tools.
Just one final question about the SQL Server Management Studio and that is,
does it come with any functions similar in Access that can with one click to
have data displaying ascending (A-Z) or descending (Z-A)?
Many thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The ANSI SQL standard states that data will not be stored in any
> particular order.
> The exception is when there is a 'clustered' index, in which case the data
> is ordered by the index.
> Always add a [ORDER BY] clause to your queries to retrieve data in a
> determined order.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "xfile" <cou-cou@.remove.nospam.com> wrote in message
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>|||xfile wrote:
> Hi,
> Thanks for both of your explanations.
> Just to ensure that I understood correctly, what you meant is that it's
> perfectly normal for records not displaying in any particular order if
> tables are opened with tool such as Management Studio?
> If I wish to have it displayed in any particular order, it has be done by
> using queries either by writing one or done with application or tools.
That's right.
Just to expand on what Hari and Arnie said. A clustered index
implements some ordering in an internal index structure (not
necessarily at the level of the data rows or data pages) but it does
not directly control the order in which row will be returned from a
query. For that you need to use an ORDER BY clause.
> Just one final question about the SQL Server Management Studio and that is,
> does it come with any functions similar in Access that can with one click to
> have data displaying ascending (A-Z) or descending (Z-A)?
>
No. SSMS is a management tool. It isn't really designed with that kind
of application in mind. Returning every row and then dynamically
sorting them wouldn't be a very sensible feature to have in a client
server environment.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You can right click on the column and select Pane > Criteria to sort the
result.
KL.
"xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
> Hi,
> When I used SQL Server Management Studio to open several tables (SQL
> 2005), some records are not listed in the order - for example, listed by
> orderid, customerid, or so on.
> Is there a way that I can have them listed in order?
> Many thanks in advance.
>|||You can only guarantee the order of the results by using an
order by clause in the SQL statement.
There is no A-Z or Z-A functionality in SQL Server - that's
just a MS Access thing. All it does is an order by "under
the covers" for you.
When you use an order by clause, you can specify ASC for
ascending or DESC for descending. If you don't specify the
ordering, ASC is the default that will be used.
-Sue
On Tue, 22 Aug 2006 15:50:27 +0800, "xfile"
<cou-cou@.remove.nospam.com> wrote:
>Hi,
>Thanks for both of your explanations.
>Just to ensure that I understood correctly, what you meant is that it's
>perfectly normal for records not displaying in any particular order if
>tables are opened with tool such as Management Studio?
>If I wish to have it displayed in any particular order, it has be done by
>using queries either by writing one or done with application or tools.
>Just one final question about the SQL Server Management Studio and that is,
>does it come with any functions similar in Access that can with one click to
>have data displaying ascending (A-Z) or descending (Z-A)?
>Many thanks.
>
>"Arnie Rowland" <arnie@.1568.com> wrote in message
>news:%23OLHKNZxGHA.1272@.TK2MSFTNGP05.phx.gbl...
>> The ANSI SQL standard states that data will not be stored in any
>> particular order.
>> The exception is when there is a 'clustered' index, in which case the data
>> is ordered by the index.
>> Always add a [ORDER BY] clause to your queries to retrieve data in a
>> determined order.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "xfile" <cou-cou@.remove.nospam.com> wrote in message
>> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>>
>|||Hi,
Thanks again for all of your kind explanations.
I did find the pane-criteria, and tried a few times and happy with the
results even I have to add all columns into criteria.
I am totally unfamiliar with database and accustomed to Access simple
graphic interface. We started using SQL 2000 last year (from hosting
company) and all queries are built-in with applications, and we recently
upgraded to a new version of the application and also moved to SQL 2005.
SQL 2005 is fast!!!
The purpose is to open some tables and delete testing data.
Again, many thanks for all of your kind explanations.
"KL" <noname@.nowhere.com> wrote in message
news:OWDzCvexGHA.980@.TK2MSFTNGP04.phx.gbl...
>
> You can right click on the column and select Pane > Criteria to sort the
> result.
> KL.
>
> "xfile" <cou-cou@.remove.nospam.com> skrev i meddelandet
> news:eL4rtqYxGHA.5056@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> When I used SQL Server Management Studio to open several tables (SQL
>> 2005), some records are not listed in the order - for example, listed by
>> orderid, customerid, or so on.
>> Is there a way that I can have them listed in order?
>> Many thanks in advance.
>
Subscribe to:
Posts (Atom)