Archiving logging db

I'm fairly new to SQL. I've inherited a logging db that records the actions taken on a web service. One of the first questions I'm being asked to solve is to explain how I should concatenate the db and save older entries to a archived db.

I'm sure this is a pretty simple action - just identify the date I want to archive from and develop a query that copies all entries from before that time period to a separate db. I'm looking for some guidlines for this kind of action, but there's so much info on SQL out there, I'm having trouble parsing out the noise and finding the answers I'm looking for. Can anyone point me in the right direction, or give me advice?

Thanks

Levi

One way to do this is to create an SSIS package:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

|||

Create a archiving table (Arch_Table) in the same db or different db with same schema.

insert into dbname..Arch_Table

select * from Table where dtColumn <= dateadd(mm, -3, getdate()) -- two archive 3months and older data

while 1 = 1

begin

set rowcount 100 delete 100 rows at a time... if you are using sql 2005 you can use top clause check bol for details

delete from Table where dtColumn <= (select max(dtColumn ) from dbname..Arch_Table )

If @.@.rowcount = 0

Break

end

sql

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

sql

Archive & Restore design question

Hi,

I have a design question.

So, I am designing an web application that will store lots of data.

Because of that we need to allow administrator users to archive some of the data.

However, the user needs the ability to view the archive data (just to know that it is there)

The administrator user can restore the data by using the web application.

My design includes 2 databases, one for the actual data, and the second for the archive data, and I manipulate it by moving data from one database to the other. What do you think about that?

Another thing is the fact that every month administrator will want to empty the archive database and save it on a disk. What is the best way of doing so? and lets say i want to take one of these backup file and restore several records from it into the archive database, what method should I be using?

Finally is there any best practices articles for this kind of issues?

Thanks,

Are you using SQL Server 2005? If you are, you can take advantage of table partitioning. But if not, then you will have to create an application that uses distributed transactions on the two databases you mentioned. Do you have to do all of this in your web application?|||

10x for the response,

All of that should be achived via the web application!

I am using sql 2005.

I am trying to figure out what are the alternatives and what is the best practice for it.

My idea, if using the current design of 2 databases, is to add 3rd database that will handle the restore from file.

Another option is to create a design that will actually to Import/Export to file.

|||

I too have a similar requirement as you have. I wonder if you have found a good design.. Please let me know how are going about diong this task.

Archival logic/algoritm

We are in need of finalizing an archival approach for one of our Web and Client server application. The major requirements are



a) User can click on Web front end to start archival process.

b) The system should move the related data to archived space in a backup location.

c) Could be a batch process.

d) The relation between tables is extensive i.e. > 30 tables need to be managed for archival one component.

e) Database size is not very huge < 10 MB.





We were planning to have a table to store archival flag, which will be set when user click on Archival. Then a batch program will copy the database in to a backup location and delete the entries from archived database where archive flag is not set and delete entry from master database where archive flag is set. The problem is how to synchronize the changes when archival process runs next time i.e. the master database would have changed so how to put that data in archival database with out removing existing data.



Any other approach/practical solutions will be very helpful.





Regards,

Mridul MishraWant to reread that and tell us if you think that makes sense?

Archive what? How do you know what to mark?

Maybe a simple explination:

"I build widgets...and when a widget is shipped I need to archive the order..."

What are you doing?|||Hmmm...Sorry for being not clear enough. Some how i thought every one is bugged with the problem as much as i do...;)

Well archival is more an event based task...Let me try to give more relevant information here..it's normal microsoft project plan which are stored and displayed for users. In most of the cases users might decide on their own whims(as most of the users are ;) ) when they feel data need to be moved to archive.

Thanks and regards
Mridul

Architects.......

I have tables in a web site database which customers fill their
preferences to receive emails regarding some topics. New customers are
added everyday and existing customer update their profile. I have also
an internal system for internal employees. The table in the website and
the one in the internal system are similar but with minor differences
in their column name (just for general info...this is not my concern at
this time).I am trying to synch this two databases. I want to do an
automatic update from the web db to the internal database. After
importing the tables to the internal system I want to run a stored
procedure which adds/updates new in-house codes (unique to the internal
db) into the imported tables. Update will be done only for those
records which don't match the records in the internal system (i.e.
for those who are new).
Currently I am using a DTS to import data from the web site, T-Sql
query to do matching b/n the web db and add the internal codes to the
imported tables.
But now I want to do it automatically: I know i have a lot of options.
Jobs, DTS with stored procedures ... Can any one suggest a way how to
integrate my options and do it correctly.
Any suggestion would be appreciated
If I need to elaborate the question please email me.
Thanks.>Currently I am using a DTS to import data from the web site, T-Sql
>query to do matching b/n the web db and add the internal codes to the
>imported tables.
>But now I want to do it automatically: I know i have a lot of options.
>Jobs, DTS with stored procedures ... Can any one suggest a way how to
>integrate my options and do it correctly.
First put all the T-SQL into stored procedures. Then, since half the
work is already being done in DTS, it would seem simple enough to
expand the DTS to also execute the T-SQL procs.
Roy

AppSettings with Report Designer?

Where do I put appsettings for Report Designer?
It works fine on the Report Server (where I put them in web.config), but I
would like it to work in Visual Studio as well.
I have developed an Data Extension Provider to get data from a Business
tier, instead of directly from a database. The Business tier calls a Data
tier, which needs the connectionstring from appsettings. (I cannot change
this behaviour.)
Regards MaranI solved it.
Put the appsettings in Visual Studio's "devenv.exe.config" and they are read
by the IDE and can be used in Report Designer by the Data Extension.
**********************
"Maran" wrote:
> Where do I put appsettings for Report Designer?
> It works fine on the Report Server (where I put them in web.config), but I
> would like it to work in Visual Studio as well.
> I have developed an Data Extension Provider to get data from a Business
> tier, instead of directly from a database. The Business tier calls a Data
> tier, which needs the connectionstring from appsettings. (I cannot change
> this behaviour.)
> Regards Maran|||A couple of thoughts:
1. Create your own App.config file, or any anyfile.anyfile - place your
config setting there.
Embed that file as a part of assembly.
next -
in your app:
Assembly asm....;
Stream stream = null;
string manifest = null;
foreach(string tmp in asm.GetManifestResourceNames())
{
if(tmp.IndexOf("App.config")>0)
{
manifest = tmp;
break;
}
}
....
stream = asm.GetManifestResourceStream(manifest);
XmlDocument configXml = new XmlDocument();
configXmlReader = new XmlTextReader(stream);
configXml.Load(configXmlReader);
XmlNodeList configNodes = configXml.GetElementsByTagName("appSettings");
....
and so on
2. machine.config is always available.
Oleg Yevteyev,
San Diego, CA
It is OK to contact me with a contracting opportunity.
"myfirstname"001atgmaildotcom.
Replace "myfirstname" with Oleg.
--
"Maran" <Maran@.discussions.microsoft.com> wrote in message
news:13D0CA98-7A7F-4023-B617-2917DC2B0AA3@.microsoft.com...
> Where do I put appsettings for Report Designer?
> It works fine on the Report Server (where I put them in web.config), but I
> would like it to work in Visual Studio as well.
> I have developed an Data Extension Provider to get data from a Business
> tier, instead of directly from a database. The Business tier calls a Data
> tier, which needs the connectionstring from appsettings. (I cannot change
> this behaviour.)
> Regards Maransql

APPS for SSIS

Help!
Does anyone know where can i find some windows or web applications using ssis for manipulations?
Are you looking for case studies? Or actual applications (executables) that incorporate SSIS?|||Hi,
an actual application (code sample) that use or manipulate ssis, hope you can help me on this one

Thanks|||

arsonist wrote:

Hi,
an actual application (code sample) that use or manipulate ssis, hope you can help me on this one

Thanks

This link should help: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

Applying SSIS in Application

Is there anyway I can integrate the ssis in Web applications or Windows applications?

Thanks

Yes. There are API's available that allow you to execute packages programatically and also to consume data from them (which is a very cool feature). These APIs allow the sort of integration that you're probably after.

This is a good place to start:

Integration Services Developer InfoCenter
(http://msdn2.microsoft.com/en-us/library/ms137709.aspx)

-Jamie

Applying Encryption Keys in a Web Farm

I wonder if someone can help clarify this one..?
If Iâ've implemented Reporting Services across a Web Farm, and saved a copy
of the encryption key for each node â' If encryption is broken, which key do I
apply?
Thanks,
MarkThe key is the same for each node. There is only one symmetric key for a
farm. The key is just encrypted differently for each node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> I wonder if someone can help clarify this one..?
> If I've implemented Reporting Services across a Web Farm, and saved a copy
> of the encryption key for each node - If encryption is broken, which key
do I
> apply?
> Thanks,
> Mark
>|||Thanks Daniel
Does that mean that if I want to recover I run â'rskeymgmt â'a..â' on one (any)
node â' or on all nodes in the Farm?
"Daniel Reib [MSFT]" wrote:
> The key is the same for each node. There is only one symmetric key for a
> farm. The key is just encrypted differently for each node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > I wonder if someone can help clarify this one..?
> >
> > If I've implemented Reporting Services across a Web Farm, and saved a copy
> > of the encryption key for each node - If encryption is broken, which key
> do I
> > apply?
> >
> > Thanks,
> > Mark
> >
>
>|||You would need to do that to each node that was failing with an error about
not be able to unencrypt data. You should rarely have to use this tool.
Only if any passwords get reset or users that the service are running as
change.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> Thanks Daniel
> Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
(any)
> node - or on all nodes in the Farm?
>
> "Daniel Reib [MSFT]" wrote:
> > The key is the same for each node. There is only one symmetric key for
a
> > farm. The key is just encrypted differently for each node.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > I wonder if someone can help clarify this one..?
> > >
> > > If I've implemented Reporting Services across a Web Farm, and saved a
copy
> > > of the encryption key for each node - If encryption is broken, which
key
> > do I
> > > apply?
> > >
> > > Thanks,
> > > Mark
> > >
> >
> >
> >|||OK - Thanks Daniel - Appreciate your swift response.
"Daniel Reib [MSFT]" wrote:
> You would need to do that to each node that was failing with an error about
> not be able to unencrypt data. You should rarely have to use this tool.
> Only if any passwords get reset or users that the service are running as
> change.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> > Thanks Daniel
> >
> > Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
> (any)
> > node - or on all nodes in the Farm?
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > The key is the same for each node. There is only one symmetric key for
> a
> > > farm. The key is just encrypted differently for each node.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > > I wonder if someone can help clarify this one..?
> > > >
> > > > If I've implemented Reporting Services across a Web Farm, and saved a
> copy
> > > > of the encryption key for each node - If encryption is broken, which
> key
> > > do I
> > > > apply?
> > > >
> > > > Thanks,
> > > > Mark
> > > >
> > >
> > >
> > >
>
>

Application Role

I am confused . What is considered an application and how SQL would know ?

If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ?

Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ?

ThanksAn application role is activated for a connection by running the sp_setapprole stored procedure. The idea is you can activate the application role by putting the password in the compiled code of your application, while people using MS Access, and Query Analyzer can not get the same permissions because they do not know the password for the application role. Look up sp_setapprole in BOL.

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

Append only unique records in SQL table

I have a stored procedure that appends data from a temp table to a destination table. The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.

The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?

Thanks in advance.
SMc

Lets say your destination table is called Dest and you temporary table is called Temp.

Both tables have two fields, A and B where A is the one that needs to be unique in Dest, but it's not necesarily unique in Temp (or Temp contains rows with a value of A that already exists in Dest).

insertinto Dest

select Temp.A, Temp.Bfrom Temp

where Temp.Anotin(select Dest.Afrom Dest)

|||

I was just fighting with similar, but a lot of simpler problem. Maybe these posts will help.

http://forums.asp.net/t/1185782.aspx

And IF NOT EXISTS(SELECT * FROM table WHERE .....

Hope it helps

Leif

Anyway to emulate LIMIT Start,Count?

Does anyone know of a way to emulate the LIMIT clause that is available in Oracle and MySQL?

I could greatly improve my performance on my web application if I could figure out how to do it. I am using TOP right now to just pull back the records needed, but as with any page with many results, once you get 10 pages in you're pulling way too many records accross the wire.

The LIMIT clause works great in the other DBMS's, but MS SQL 2K does not have it. :(set rowcount 100|||That doesn't deal with the offset right??|||It just limits the number of rows returned to the client to 100 (or whatever number you specify)

Don't forget to set it to 0 to restore the default functionality - RETURN ALL ROWS.|||I already get that with TOP so there is no need for this.|||if you want LIMIT 30,20 (start at offset 30, i.e. 31st row, and return 20 rows) like this:

select *
from (
select top 20
foo
, bar
from (
select top 50
foo
, bar
from yourtable
order
by bar desc
)
order
by bar asc
)
order
by bar desc|||http://www.dbforums.com/t994143.html|||I like the multiple subselect idea but don't seem to be able to get it to work. The sql errors out when trying to do an order by on a subselect.

For example - this errors out:

select top 20 AccountID,[Name] from (
select top 50 AccountID,[Name]
from CRDACCOUNT
order by AccountID desc
) order by AccountID

Give an "Incorrect syntax near the keyword 'order'." on the last line.

Ideas?

PS: Thanks for the great replies so far everyone!!|||I'm 99 5/8% certain that you are just missing an alias, something like:SELECT TOP 20 AccountID, [Name]
FROM (SELECT TOP 50 AccountID, [Name]
FROM CRDACCOUNT
ORDER BY AccountID DESC) AS zz
ORDER BY AccountID-PatP|||wow, I just posted a question asking nearly the identical thing. So far it doesn't look good. My options are:

- Using an ORDER BY on column and maintain a bookmark position. This works but I can't get decent performance out of it.
- Paging techniques. Having trouble getting these to work right as well.

Let me know if you find an acceptable solution.|||Yep - it was the alias. Here is the first query formatted with the aliases:

select *
from (
select top 20
foo, bar
from (
select top 50
foo, bar
from yourtable
order
by bar desc
) as tbl1
order
by bar asc
) as tbl2
order
by bar desc

I guess the trick now is to figure out how to have multi fields in the ORDER BY clause.

Thanks for the help that has been given!

Anything wrong with this query?

For a given table, I want to know all the columns that are included in
an index. I have looked on the web and come up with this, which seems
to work, but just wanted some verification. Are there any reasons why
I should be using the metadata functions like OBJECT_NAME?

Thanks
Bruce

SELECT
DISTINCT c.name
from sysusers u,
sysobjects o,
syscolumns c,
sysindexes i,
sysindexkeys k
WHERE o.uid = u.uid
AND u.name = user
AND o.name = 'ing_customer'
AND o.id = i.id
AND i.indid = k.indid
AND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0
AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )
AND i.indid BETWEEN 1 And 250
AND k.id = o.id
and k.colid = c.colid
and c.id = o.id
ORDER BY c.nameBruce (sandell@.pacbell.net) writes:
> For a given table, I want to know all the columns that are included in
> an index. I have looked on the web and come up with this, which seems
> to work, but just wanted some verification. Are there any reasons why
> I should be using the metadata functions like OBJECT_NAME?

object_name() is just a quick-step to sysobjects.name, which is a
documented column, so which one you use, is no big deal.

On the other hand, the information returned by objectproperty() and
indexproperty() is hidden in undocuemented columns, so in this case,
you should definitely use the metadata functions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp