mssql and specifying a schema name?

[Lukasz Szybalski]

Hello,

I'm trying to autolaod my table "image" but it keeps complaining that the table doesn't exists.

I've enabled the echo = true and I see that you specify in the query:

SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] 

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] 

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION]

INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] 

FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] 

WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION]

2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image', 'MyDatabase'

)

But my schema name is "dbo"?

Where do I specify that?

On create_engine?

or?

import sqlalchemy

e = sqlalchemy.create_engine("mssql+pyodbc://Me:myPassword@SQLServer2008")

#e.echo=True

e.echo=False

metadata=sqlalchemy.MetaData(e)

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=e, autoflush=True, autocommit=False)

session = Session()

from sqlalchemy.orm import mapper

#-----------

image_table = sqlalchemy.Table('image', metadata, autoload=True)

???Where do specify my schema "dbo"? so instead of sending 'image', 'MyDatabase'...you send 'image','dbo'?

Thanks,

Lucas

[Michael Bayer]

the default schema name is determined by:

SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'

for some reason on your system it's coming up as "MyDatabase". You'd want to fix that so that it comes up with "dbo".

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Lukasz Szybalski]

On May 30, 1:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> the default schema name is determined by:
>
>             SELECT default_schema_name FROM
>             sys.database_principals
>             WHERE name = (SELECT user_name())
>             AND type = 'S'
>
> for some reason on your system it's coming up as "MyDatabase".  You'd want to fix that so that it comes up with "dbo".

I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.

Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
"autoload=True"?

Thanks,
Lucas

[Lukasz Szybalski]

On May 30, 1:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> the default schema name is determined by:
>
>             SELECT default_schema_name FROM
>             sys.database_principals
>             WHERE name = (SELECT user_name())
>             AND type = 'S'
>
> for some reason on your system it's coming up as "MyDatabase".  You'd want to fix that so that it comes up with "dbo".

Actually,
Let me try a different username.

That works. The username I was using was "admin"...so I logged in as
me and it has schema name of dbo.
Thanks,
Lucas

[Michael Bayer]

On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:

>
>
> On May 30, 1:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> the default schema name is determined by:
>>
>> SELECT default_schema_name FROM
>> sys.database_principals
>> WHERE name = (SELECT user_name())
>> AND type = 'S'
>>
>> for some reason on your system it's coming up as "MyDatabase". You'd want to fix that so that it comes up with "dbo".
>
> I can't.
> The database was design for a specific application and I cannot make
> changes to it structure, other then my records.
>
> Is there a _somevalue that stores this in my engine, so that I can
> overwrite it? or is it automatically get pulled every time I do a
> "autoload=True"?

then you'd need to put "schema='dbo'" into each of your Table objects. the autoload should work fine with that.

[Lukasz Szybalski]

On May 30, 2:35 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
>
>
>
>
>
>
>
>
>
>
>
> > On May 30, 1:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> >> the default schema name is determined by:
>
> >>             SELECT default_schema_name FROM
> >>             sys.database_principals
> >>             WHERE name = (SELECT user_name())
> >>             AND type = 'S'
>
> >> for some reason on your system it's coming up as "MyDatabase".  You'd want to fix that so that it comes up with "dbo".
>
> > I can't.
> > The database was design for a specific application and I cannot make
> > changes to it structure, other then my records.
>
> > Is there a _somevalue that stores this in my engine, so that I can
> > overwrite it? or is it automatically get pulled every time I do a
> > "autoload=True"?
>
> then you'd need to put "schema='dbo'" into each of your Table objects.   the autoload should work fine with that.
>
>

Where would I put the "schema='db'"?

somewhere inside this statement ?

image_table = sqlalchemy.Table('image', metadata, autoload=True) ?

or?



[SQL Server]Login failed. The login is from an untrusted domain and
cannot be used with Windows authentication.

[Michael Bayer]

On May 30, 2012, at 3:56 PM, Lukasz Szybalski wrote:

>
>
> On May 30, 2:35 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> On May 30, 1:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>>>> the default schema name is determined by:
>>
>>>> SELECT default_schema_name FROM
>>>> sys.database_principals
>>>> WHERE name = (SELECT user_name())
>>>> AND type = 'S'
>>
>>>> for some reason on your system it's coming up as "MyDatabase". You'd want to fix that so that it comes up with "dbo".
>>
>>> I can't.
>>> The database was design for a specific application and I cannot make
>>> changes to it structure, other then my records.
>>
>>> Is there a _somevalue that stores this in my engine, so that I can
>>> overwrite it? or is it automatically get pulled every time I do a
>>> "autoload=True"?
>>
>> then you'd need to put "schema='dbo'" into each of your Table objects. the autoload should work fine with that.
>>
>>
>
> Where would I put the "schema='db'"?
>
> somewhere inside this statement ?
> image_table = sqlalchemy.Table('image', metadata, autoload=True) ?
>
> or?
>
>
>
> [SQL Server]Login failed. The login is from an untrusted domain and
> cannot be used with Windows authentication.

if you're already using a different username that solves the problem, stick with that.

otherwise yes, Table('image', metadata, schema='dbo', autoload=True)

using "schema" can make things more complicated so don't use it if you don't have to.

[Michael Schlenker]

Am 30.05.2012 20:03, schrieb Michael Bayer:
> the default schema name is determined by:
>
> SELECT default_schema_name FROM sys.database_principals WHERE name =
> (SELECT user_name()) AND type = 'S'
>
> for some reason on your system it's coming up as "MyDatabase". You'd
> want to fix that so that it comes up with "dbo".

Default Schema Name can be empty if the user logs in via an AD Group.
(in fact a Group user cannot be assigned a default schema name before
SQL Server 2012...).

See for example
http://dba.stackexchange.com/questions/8318/sql-2008-r2-creates-user-schema-when-windows-user-creates-tables
for some other instance of this happening.

Michael

--
Michael Schlenker
Software Architect

CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Stra�e 1-3 Fax: +49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Gesch�ftsf�hrer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215