Correct syntax

Hello

I have having trouble displaying some simple columns in ascending order.

I know that the database is populated and I can get the more complex code to work if I display like this:

SELECT FName, LName, Town, '<a href="http://links.10026.com/?link=' + url + '">' + Site + '</a>' as Link
FROM Names_DB
WHERE FName = 'Tom'
And url like 'http:%'
ORDER BY LName ASC

But I need a simpler view but I can't get it to work

I have tried this:

SELECT FName, LName

FROM Names_DB

ORDER BY LName ASC

And this

SELECT FName, LName

FROM Names_DB

ORDER BY LName ASC;

And This:

SELECT FName, LName
FROM Names_DB

ORDER BY LName 'ASC'

What is wrong with this syntax?

Thanks

Lynn

Change 'ASC' to ASC,it works well.|||

Hi Jason

Thanks for the reply and the info.

I have been trying various combinations to get it to work. At last I have eventually found a syntax that works also, about the same time I got your reply.

I just put : after the ORDER BY

ORDER BY: LName ASC

How frustrating searching for the single character to make everything happen.

Lynn

Copying MSDE database

I have a MSDE database on a laptop and need to copy it to another machine in order to use Access 2000 to inspect the tables and view the data using an Access project adp file.
Could someone please tell me how to do this and whether there are any relevant issues/problems.
thanksBACKUP and RESTORE work fine. There's just no GUI

You can do interactive tsql by Navigating to

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

THEN type osql -E -r1

------------------------
Handy Instructions

Type in your tsql commands (BACKUP \RESTORE\Whatever)
For the TSQL reference, go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

To execute these commands, on a line by itself type "GO"

to QUIT, on a line by itself type "QUIT"sql

copying a row to new table - not copying one date column

I have two tables, a PendingOrder table and a CompletedOrder table. I have a stored procedure that moves an order from the pending table to the completed table, and I am having an issue with copying over a date field (the date the order was created). Here is what I have....

The store procedure takes in the following:
@.PendOrderKey INT,
@.Status INT

Here is my insert statement:

INSERT INTO CustOrder (CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, Status, OrderTypeKey, CreatedDate, ModifiedDate)
SELECT CustAddrKey, UserKey, ContactName, ContactPhone, CustPONo, VendLocalSupplierKey, Notes, @.Status, OrderTypeKey, CreatedDate, GETDATE()
FROM PendCustOrder
WHERE PendOrderKey = @.PendOrderKey

For some reason, when I run this on an order, instead of taking the value of "CreatedDate" from the pending order table and inserting it into the CreatedDate field of the completed order table, it inserts the current date and time into the completed order table. The "CreatedDate" field in the completed orders table does not allow nulls and has no default value, so I'm confused as to why it's inserting the current date/time. Any help would be greatly appreciated. Thanks!!

Maguidhir:

Check and see if the target table has a trigger that sets the "CreatedDate" field when a new record is inserted into the table.

Dave

|||Thank you so much! It does have a trigger that sets the CreatedDate=GetDate()!

Copy Schema

Hi,

What do I need to do in order to make a copy of a schema. For example lets say my database has 3 schemas - Test, Production and Staging. I want to make an exact copy of Production. ie. complete copy - all objects - tables, views, stored procs and also all the data and probably call it Production-New. Now we have 4 schemas in the database.

This needs to be initiated from the app that the user is currently using. Is there any built in support (TSQL functions/commands/Sps ?) for such an activity ? and if not what may be the direction that I need to look at to take this further ?

Overall I'm looking at being able to take a quick snapshot of a schema and start using that. I must mention that the volume of data will be substancial and the solution devised should be able to do it rather quickly - The user clicks a button in order to take a snap shot. So the wait time has to be reasonable.

Any suggestions/ideas will be great.

Thanks -

Avinash

If you mean an exact copy, then I just do a backup and restore. You can write a batch file to do this pretty easily. Write a backup stored script and a drop database/restore script and execute them with SQLCMD (or OSQL/ISQL for earlier versions)

The reason I suggest using a batch file is that you can run it as a user that has file system rights to the two databases and integrated security to make a copy to a different server.

For more elaborate schemes where you only want part of the data, then SSIS/DTS would be the way to go, but backup/restore is very straightforward.

|||

Many thanks MS Louis Davidson for your response.

Will try out SSIS.

Thanks and Regards,

Avinash