Post reply

2000 -> 2005 select into schema error

  • March 3, 2010 at 10:41 pm

    #211809

    newbie to 2005. converting fairly generic, open db from 2000.

    have a user sp that generates a filename (based on a passed in string and date/time (eg 'bc20100303114512')

    Asi recall 2000 needed an owner before the table could be created. Enter 'theSQLUser'. that user is a standard/public sql user with no more rights than public and is a user of the db.

    the following statement:

    select pid,last from clients into theSQLUser.bc20100303114512 where pid='000001'

    worked fine in 2000

    but yields: The specified schema name "theSQLUser" either does not exist or you do not have permission to use it.

    Now i never created a schema for the user, but because other efforts I've deleted the user and recreated it. There was at least one time that, when i dropped the user via vs2005 and the delete key, it asked if i wanted to lose two objects: the schema & user. Not knowing any better I did both. It no longer asks me about a schema when i want to drop the user.

    Do I need that schema? CAn it be recreated? what was in it - i didn't create it? Is its absence part of my problem? 2000 never complained.

    thanks for any insights

  • March 6, 2010 at 5:55 pm

    #1129699

    This issue is resolved (at least for _use__) if not why.

    I found that using [servername].[dbname].[dbo].bc20100... avoided errors

    ie _really_ telling SQL where to go. Which is strange because the connection string is to a ODBC system file that explicitly sets

    [servername] and [dbname]

    haven't yet gone back to the more std. user: theSQLuser and tried it with the "fully pathed" table reference.

  • March 7, 2010 at 3:53 am

    #1129741

    There were no schemas in SQL 2000. One of the things that SQL 2005 did was separate out users and schemas.

    In SQL 2000, if you say

    SELECT * FROM SomeUser.sometable

    you're asking for the table named sometable, owned by SomeUser

    In SQL 2005, if you say

    SELECT * FROM SomeUser.sometable

    you're asking for the table named sometable, in the SomeUser schema. If the SomeUser schema does not exist (which is quite likely), that will fail. The user SomeUser can exist, that doesn't automatically mean the schema exists

    Unless you really need schemas, rather use the dbo schema for everything

    So

    select pid,last from clients into dbo.bc20100303114512 where pid='000001' No need for server and database names. 2 part is more than adequate.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply