Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts

Thursday, March 22, 2012

A column has been specified more than once in the order by list.

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.

Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.

How can I do this?

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC

These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

Since you are calling a function, and the function 'could' run two times and return the same information both time, it 'could' create a situation where a ORDER BY column is duplicated.

It seems to me that shouldn't be an issue, but it is... (I don't know why SQL Server should care if my query is 'ORDER BY LastName, LastName')

A column has been specified more than once in the order by list.

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.

Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.

How can I do this?

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC

These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

Check here for responses to your duplicate post in SQL Server Database Engine.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Monday, March 19, 2012

A .cmd file can be run on SQL7, but not on SQL2000

Hi,
I have a command file with the following SQL statement:
bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
-U -P -f %formatfile%
I can run this file on a machine with SQL7 installed, but have error when I
run it on a machine with SQL2000. Here is the error:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=2
Warning=[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()).
Does anyone know where is the problem? How can I fix it?
Thanks for the support!
On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:

>Hi,
>I have a command file with the following SQL statement:
>bcp "SELECT * FROM [%dbname%].[dbo].[%1] WHERE DATEDIFF(hour,TimeStamp,
>DATEADD(n, -DATEPART(n,getdate()), getdate())) <=24" queryout %CurDate%.tmp
>-U -P -f %formatfile%
>I can run this file on a machine with SQL7 installed, but have error when I
>run it on a machine with SQL2000. Here is the error:
>SQLState = 08001, Native Error=17
>Error=[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not
>exist or access denied.
>SQLState = 01000, Native Error=2
>Warning=[Microsoft][ODBC SQL Server Driver][Shared
>Memory]ConnectionOpen(Connect()).
>Does anyone know where is the problem? How can I fix it?
>Thanks for the support!
Hi June,
Some things to check:
- Are you sure the SQL Server 2000 service is running, and that the
computer that runs this script and the server where SQL Server is
installled can "see" each other?
- Try adding the "-S servername[\instance_name]" argument
- Try using a trusted connection instead of a SQL Server login (ie
replace the "-U -P" arguments with "-T").
- If you can't use a trusted connection, double-check that SQL Server is
configured to allow "Mixed Mode Authentication". The default is "Windows
Authentication", which allows only trusted connections. Also, add userid
and password to the command string (i.e. replace "-U -P" with "-U userid
-P password").
- Check the Client Network Utility on the workstation and the Server
Network Utility on the server. Are the same protocols enabled? Are the
properties the same? If there are any firewalls between the workstation
and the server, have you made sure that the prots required for SQL
Server are not blocked?
Hugo Kornelis, SQL Server MVP
|||Hi Hugo,
Thanks for your info.
1. Both the command file and SQL server are running on the same machine
(Win2000).
2. I have tried to add -S [instance_name] parameter to the command line, but
I got similar error messages:
SQLState = 08001, Native Error=17
Error=[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, Native Error=53
Warning=[Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen(Connect()).
3. I also tired to replace -T with -U -P, still got the same error.
What else should I try?
Thanks again!
"Hugo Kornelis" wrote:

> On Thu, 30 Mar 2006 09:56:02 -0800, June wrote:
>
> Hi June,
> Some things to check:
> - Are you sure the SQL Server 2000 service is running, and that the
> computer that runs this script and the server where SQL Server is
> installled can "see" each other?
> - Try adding the "-S servername[\instance_name]" argument
> - Try using a trusted connection instead of a SQL Server login (ie
> replace the "-U -P" arguments with "-T").
> - If you can't use a trusted connection, double-check that SQL Server is
> configured to allow "Mixed Mode Authentication". The default is "Windows
> Authentication", which allows only trusted connections. Also, add userid
> and password to the command string (i.e. replace "-U -P" with "-U userid
> -P password").
> - Check the Client Network Utility on the workstation and the Server
> Network Utility on the server. Are the same protocols enabled? Are the
> properties the same? If there are any firewalls between the workstation
> and the server, have you made sure that the prots required for SQL
> Server are not blocked?
> --
> Hugo Kornelis, SQL Server MVP
>
|||On Thu, 30 Mar 2006 14:55:01 -0800, June wrote:

>2. I have tried to add -S [instance_name] parameter to the command line, but
>I got similar error messages:
Hi June,
The correct usage of -S is either
-S server_name
or
-S server_name\instance_name

>What else should I try?
If none of these option work, try posting to a different group. This
group is actually intended for support to the "Microssoft English Query"
tool. Precious few people visit here.
Since your question is about bcp, one of the tools that come with SQL
Server, microsoft.public.sqlserver.tools would be the best place to ask.
Hugo Kornelis, SQL Server MVP

Saturday, February 11, 2012

4 key primary key vs 1 key artificial primary key

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Mattno the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?|||Originally posted by r937
no the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?

Thanks for the search keyword tips. I did indeed find a number of helpful threads.

Matt|||I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

Experts opinions varried:
Moe: Surrogate Keys
Larry: Natural Keys
Curly: Nyuck nyuck nyuck

We are still awaiting final Papal Dispensation on the subject.|||hilarious

i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines

moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
[moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
moe: "what are you doing in there?!"
curly: "so it shouldn't be a total loss, i'm takin a bath"
moe: "a bath?!"
curly: "yeah, a bath"
moe (after a slight pause): "move over"|||For what it's worth:

it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

given

application_id, owner_id, and property_id

being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

owner_id, application_id, and property_id

and so on.

Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.