Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Queries (2003)

    Sorry if this is in the wrong place, but: I can't find the correct forum.
    I am trying to move a series of queries into SQL. The syntax for the Access query doesn't work in Enterprise Manager. The SQL query looks like:

    UPDATE
    WSH18Week.dbo.TblAppointmentsWithPPI
    SET
    WSH18Week.dbo.TblAppointmentsWithPPI.PPIIssuer = WSHPCTRerrals.dbo.VwF2Inwards.ReferrerCode,
    WSH18Week.dbo.TblAppointmentsWithPPI.PPI = WSHPCTRerrals.dbo.VwF2Inwards.ReferrerUPI
    FROM
    WSHPCTRerrals.dbo.VwF2Inwards
    WHERE
    (WSHPCTRerrals.dbo.VwF2Inwards.Specialty = WSH18Week.TblAppointmentsWithPPI.SpecCode)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.ClinicCode = WSH18Week.dbo.TblAppointmentsWithPPI.Clinic)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.ClinicDate = WSH18Week.dbo.TblAppointmentsWithPPI.AppDate)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.CRN = WSH18Week.dbo.TblAppointmentsWithPPI.CRN);

    This parses OK, but I get an error or:
    The column prefix 'WSH18Week.TblAppointmentsWithPPI' does not match with a table name or alias name used in the query.
    What am I doing wrong!
    Thanks
    Allison

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Queries (2003)

    This is the right forum - since Access and SQL Server are closely related, we handle SQL Server questions here.
    You have WSH18Week.TblAppointmentsWithPPI.SpecCode but it should be WSH18Week.dbo.TblAppointmentsWithPPI.SpecCode.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Queries (2003)

    Thanks - it had to be something simple.
    BUT I now get the error "Cannot resolve collation conflict for equal to operation."

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Queries (2003)

    Try this:

    ...
    WHERE
    (WSHPCTRerrals.dbo.VwF2Inwards.Specialty COLLATE DATABASE_DEFAULT = WSH18Week.dbo.TblAppointmentsWithPPI.SpecCode COLLATE DATABASE_DEFAULT)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.ClinicCode COLLATE DATABASE_DEFAULT = WSH18Week.dbo.TblAppointmentsWithPPI.Clinic COLLATE DATABASE_DEFAULT)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.ClinicDate COLLATE DATABASE_DEFAULT = WSH18Week.dbo.TblAppointmentsWithPPI.AppDate COLLATE DATABASE_DEFAULT)
    AND (WSHPCTRerrals.dbo.VwF2Inwards.CRN COLLATE DATABASE_DEFAULT = WSH18Week.dbo.TblAppointmentsWithPPI.CRN COLLATE DATABASE_DEFAULT);

    The addition COLLATE DATABASE_DEFAULT my not be necessary for all four conditions, but it won't hurt. If you like, you can experiment by omitting it for one or more conditions.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Queries (2003)

    Thanks - that's done it. why does that work?
    Allison

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Queries (2003)

    Collation is the way data are sorted and compared (for example: does A equal a, and does equal e? In SQL Server collation can be set at the column level; if the collation for columns in the WHERE part of your query is different, SQL doesn't know how to compare the values. Adding COLLATE DATABASE_DEFAULT forces SQL to use the database default settings; this enables comparison even if the column settings are different.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Queries (2003)

    Thanks, that's a clear explaination - I have had a look round the internet and couldn't figure out what it meant. Does that mean there is something wrong - ie set up incorrectly - with one of the tables?
    Allison

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Queries (2003)

    Have the tables been imported from other databases? That might explain why they have different collation settings.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Queries (2003)

    Yes, I am using 2 different databases, so that accounts for it - thanks again. (I expect I'll have more queries!)
    Allison

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •