"Order by" clause

I run the script below in sql2005 and am receiving the error msg 209. Do I
need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
them displyed in front.
Thank you.
Charlie
select STATE, TAXAMNT, SUBTOTAL, *
from sop30200
where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
AND SOPTYPE IN (3,4)
AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
--AND TAXEXMT1 = ' '
AND SUBTOTAL <> 0
--AND SOPNUMBE LIKE 'NCINV%'
AND VOIDSTTS = 0
AND SOPNUMBE NOT IN
(SELECT INVNO
FROM VERTEX..REGPRERETURNSTBL
WHERE COMPCD = 'ABS'
and invno between '20070201' and '20070228')
--AND SHIPTOGEOCODE LIKE '31%')
ORDER BY STATE DESC
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STATE'.
While there is no actual ambiguity, the parser is not so sure.
Your select list contains the STATE column twice: once
explicitly as the first column and once again in the *. The
parser does not know whether you want to sort on the
source column sop30200.STATE or on the output column
STATE. They are the same in this query, but there are
queries where they could be different because some
output expression or column is aliased as STATE. The
parser does not try to figure out the meaning of the query.
There are two solutions. One is to specify
ORDER BY sop30200.STATE
and the other is to list all the columns you want in the output
separately and one each, not using the * notation. The second
solution is the better one, at least in production, because neither
* nor duplicate columns in the output are typically a good idea.
But if this is just a quick ad hoc query, the first solution is fine.
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
chas2006 wrote:

>I run the script below in sql2005 and am receiving the error msg 209. Do I
>need a prefix to order by either STATE, TAXAMNT, or SUBTOTAL because I want
>them displyed in front.
>Thank you.
>Charlie
>select STATE, TAXAMNT, SUBTOTAL, *
>from sop30200
>where state in('NJ','NY','WA','CO','CA','ME','OK','VA','PA','T N')
>AND SOPTYPE IN (3,4)
>AND DOCDATE between '2007-02-01 00:00:00.000' and '2007-02-28 00:00:00.000'
>--AND TAXEXMT1 = ' '
>AND SUBTOTAL <> 0
>--AND SOPNUMBE LIKE 'NCINV%'
>AND VOIDSTTS = 0
>AND SOPNUMBE NOT IN
>(SELECT INVNO
>FROM VERTEX..REGPRERETURNSTBL
>WHERE COMPCD = 'ABS'
>and invno between '20070201' and '20070228')
>--AND SHIPTOGEOCODE LIKE '31%')
>ORDER BY STATE DESC
>
>Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'STATE'.
>

"Could not continue scan with NOLOCK due to data" error during Replication Synchronization

I am receiving this message "Agent message code 601. Could not
continue scan with NOLOCK due to data" during the initial
synchronization after setting up a subscriber.
It is a straightforward tarnsactional replication of maybe twenty
tables.
I have never seen that message in the context of replication.
I think the 601 error looks to be slightly misleading as the previous
message in the log looks to indcate a successful snapshot
synchronization:
Delivered snapshot from the 'unc\serverX\20070208105750\' sub-folder
in 519625 milliseconds.
I have tried setting up the subscriber and publisher mulitple times
from scratch but always run into this message. Also all of the
replicated tables have multiple indexes and this is on a SQL Server
2005 SP1 Mirrored database. I have ran checkdb on database with no
problems found. This is halting the database from being replicated -
anyone know a way around this?
On Feb 8, 5:14 pm, "Calculated" <sarahjco...@.gmail.com> wrote:
> I am receiving this message "Agent message code 601.Could notcontinue scan with NOLOCK due to data"duringthe initialsynchronizationafter setting up a subscriber.
> It is a straightforward tarnsactionalreplicationof maybe twenty
> tables.
> I have never seen that message in the context ofreplication.
> I think the 601errorlooks to be slightly misleading as the previous
> message in the log looks to indcate a successful snapshotsynchronization:
> Delivered snapshot from the 'unc\serverX\20070208105750\' sub-folder
> in 519625 milliseconds.
> I have tried setting up the subscriber and publisher mulitple times
> from scratch but always run into this message. Also all of the
> replicated tables have multiple indexes and this is on a SQL Server
> 2005 SP1 Mirrored database. I have ran checkdb on database with no
> problems found. This is halting the database from being replicated -
> anyone know a way around this?
Resolved this - if anyone else has this problem try adding a table at
a time to the publication article and re-initializing the subscription
until you identify the problem table. Then either filter the table to
break down into smaller chunks (largest table in db was causing
problem in my case) or play around with the copy clustered index and
copy nonclustered index options on the specific table.

"Could not continue scan with NOLOCK due to data" error during Replication Synchronization

I am receiving this message "Agent message code 601. Could not
continue scan with NOLOCK due to data" during the initial
synchronization after setting up a subscriber.
It is a straightforward tarnsactional replication of maybe twenty
tables.
I have never seen that message in the context of replication.
I think the 601 error looks to be slightly misleading as the previous
message in the log looks to indcate a successful snapshot
synchronization:
Delivered snapshot from the 'unc\serverX\20070208105750\' sub-folder
in 519625 milliseconds.
I have tried setting up the subscriber and publisher mulitple times
from scratch but always run into this message. Also all of the
replicated tables have multiple indexes and this is on a SQL Server
2005 SP1 Mirrored database. I have ran checkdb on database with no
problems found. This is halting the database from being replicated -
anyone know a way around this?On Feb 8, 5:14 pm, "Calculated" <sarahjco...@.gmail.com> wrote:
> I am receiving this message "Agent message code 601.Could notcontinue scan with NOLOCK due to data"duringthe initialsynchronizationafter setting up a subscriber.
> It is a straightforward tarnsactionalreplicationof maybe twenty
> tables.
> I have never seen that message in the context ofreplication.
> I think the 601errorlooks to be slightly misleading as the previous
> message in the log looks to indcate a successful snapshotsynchronization:
> Delivered snapshot from the 'unc\serverX\20070208105750\' sub-folder
> in 519625 milliseconds.
> I have tried setting up the subscriber and publisher mulitple times
> from scratch but always run into this message. Also all of the
> replicated tables have multiple indexes and this is on a SQL Server
> 2005 SP1 Mirrored database. I have ran checkdb on database with no
> problems found. This is halting the database from being replicated -
> anyone know a way around this?
Resolved this - if anyone else has this problem try adding a table at
a time to the publication article and re-initializing the subscription
until you identify the problem table. Then either filter the table to
break down into smaller chunks (largest table in db was causing
problem in my case) or play around with the copy clustered index and
copy nonclustered index options on the specific table.

"Column name or number of supplied values does not match table definition" when trying to

Hello,
I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @.Gender = 'Male', @.Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a"SELECT * FROM tbl_User WHERE gender = @.Gender AND culture = @.Culture",so why am I receiving this error when both tables have the samestructure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Thanks,
Greg.
hello...
this might be simplified version..
SELECT * INTO #tbl_User FROM tbl_User WHERE Gender='Male' AND Culture='en-GB'
and there might be idenity column on your original tbl_User..?
i tested a table with identity column, same error msg (just guess)|||In addition to busyweb's reply, if you do need to create an empty temptable using SELECT INTO in the future, try the following instead of TOP1 + TRUNCATE:
SELECT *
INTO #Tbl
FROM SomeTable
WHERE 1=0