Connecting to SQL2005 from VS2005 RC

Hi all,
Have been using SqlDataSources for a while now. Reinstalled machine last
w, now running Server 2003. Installed both SqlServer 2000 and 2005 with
instance-names "sql2000" and "sql2005". Also installed both Visual Studio
2003 and 2005. VS2005 is the Release Candidate, and SQL2005 is the September
CTP.
During SqlDataSource configuration wizard, i can test connection and all is
fine. I can see table names and views in the "query builder" thingie, but
once i do the "Test Query" things go bad. I get an "Invalid Object Name" on
the table from which the datasource should retrieve its data.
I have posted small video of me producing the error. Any input is welcomed.
I suspect the side-by-side 2000 and 2005 installation, but it _shouldn't_ be
a problem, should it?
The video is here: http://www.netkoder.dk/error.wmv
The projekt can be downloaded as a .zip from http://www.netkoder.dk/test.zip
Again, thanks for any input.
J.Jespersen
DenmarkJ,
Shot in the dark...try qualifying the table using the database.owner.object
notation.
HTH
Jerry
"J. Jespersen" <jdj@.jdj.dk> wrote in message
news:%23UMXcfdzFHA.736@.tk2msftngp13.phx.gbl...
> Hi all,
> Have been using SqlDataSources for a while now. Reinstalled machine last
> w, now running Server 2003. Installed both SqlServer 2000 and 2005 with
> instance-names "sql2000" and "sql2005". Also installed both Visual Studio
> 2003 and 2005. VS2005 is the Release Candidate, and SQL2005 is the
> September CTP.
> During SqlDataSource configuration wizard, i can test connection and all
> is fine. I can see table names and views in the "query builder" thingie,
> but once i do the "Test Query" things go bad. I get an "Invalid Object
> Name" on the table from which the datasource should retrieve its data.
> I have posted small video of me producing the error. Any input is
> welcomed. I suspect the side-by-side 2000 and 2005 installation, but it
> _shouldn't_ be a problem, should it?
> The video is here: http://www.netkoder.dk/error.wmv
> The projekt can be downloaded as a .zip from
> http://www.netkoder.dk/test.zip
> Again, thanks for any input.
> J.Jespersen
> Denmark
>
>|||
> Shot in the dark...try qualifying the table using the
> database.owner.object
> notation.
Right on target. That solved it. But....
Why do I need to fully qualify the table name? Is this an option that can be
disabled, and why doesn't the config-wizard figure this out automatically? I
really like the whole config-wizard with all the parameterizing and stuff,
but if selected table names along the way are invalid, what good is it all?
Thanks,
J.Jespersen

> Jerry
> "J. Jespersen" <jdj@.jdj.dk> wrote in message
> news:%23UMXcfdzFHA.736@.tk2msftngp13.phx.gbl...
>|||J,
Not sure I have not used that tool before. The database owner (DBO) should
really own all objects and in that context the object owner would probably
not need to be qualified - would default to DBO.
HTH
Jerry
"J. Jespersen" <jdj@.jdj.dk> wrote in message
news:eosP7GezFHA.908@.tk2msftngp13.phx.gbl...
>
> Right on target. That solved it. But....
> Why do I need to fully qualify the table name? Is this an option that can
> be disabled, and why doesn't the config-wizard figure this out
> automatically? I really like the whole config-wizard with all the
> parameterizing and stuff, but if selected table names along the way are
> invalid, what good is it all?
> Thanks,
> J.Jespersen
>
>
>
>
>
>|||"J. Jespersen" <jdj@.jdj.dk> wrote in message
news:eosP7GezFHA.908@.tk2msftngp13.phx.gbl...
>
> Right on target. That solved it. But....
> Why do I need to fully qualify the table name?
Because Contact isn't in the default schema for the connection (which is
DBO). Contact is actually in the Person schema, so it must be refered to as
Person.Schema. The Wizard is just not schema-aware. You should qualify the
table name with a schema, but not a database name.

>Is this an option that can be disabled, and why doesn't the config-wizard
>figure this out automatically?
Wizards, despite their name, really aren't actually very smart.

>I really like the whole config-wizard with all the parameterizing and
>stuff, but if selected table names along the way are invalid, what good is
>it all?
>
AdventureWorks agressively factors objects into different schemas
HumanResourses, Person, Production, etc. It does this to demonstrate that
schemas are seperated from users in Sql Server 2005, and to show one way to
use this seperation. The downside to this is that in a database like this
you need to schema-qualify most object names. For your own database you can
either put all the objects in the user's default schema, or create synonyms
in the user's default schema referring to the objects in the other schemas.
If you issue the following
create synonym dbo.Contact for Person.Contact
Your query will succeed, and you can refer to the Contact table without a
schema qualification.
David|||
> Because Contact isn't in the default schema for the connection (which is
> DBO). Contact is actually in the Person schema, so it must be refered to
> as Person.Schema. The Wizard is just not schema-aware. You should
> qualify the table name with a schema, but not a database name.
Thank you! I'll have to read up on this whole Schema thing in SQL Server.
J.Jespersen|||>Wizards, despite their name, really aren't actually very smart.
Got a kick out that!!! :-)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%233SCieezFHA.2132@.TK2MSFTNGP15.phx.gbl...
> "J. Jespersen" <jdj@.jdj.dk> wrote in message
> news:eosP7GezFHA.908@.tk2msftngp13.phx.gbl...
> Because Contact isn't in the default schema for the connection (which is
> DBO). Contact is actually in the Person schema, so it must be refered to
> as Person.Schema. The Wizard is just not schema-aware. You should
> qualify the table name with a schema, but not a database name.
>
> Wizards, despite their name, really aren't actually very smart.
>
> AdventureWorks agressively factors objects into different schemas
> HumanResourses, Person, Production, etc. It does this to demonstrate that
> schemas are seperated from users in Sql Server 2005, and to show one way
> to use this seperation. The downside to this is that in a database like
> this you need to schema-qualify most object names. For your own database
> you can either put all the objects in the user's default schema, or create
> synonyms in the user's default schema referring to the objects in the
> other schemas. If you issue the following
> create synonym dbo.Contact for Person.Contact
> Your query will succeed, and you can refer to the Contact table without a
> schema qualification.
> David
>|||> Thank you! I'll have to read up on this whole Schema thing in SQL Server.
In SQL Server 2005 Books Online, take a look at these topics as a starting
point:
User-Schema Separation
Schemas in AdventureWorks
CREATE SCHEMA
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"J. Jespersen" <jdj@.jdj.dk> wrote in message
news:OpIFihezFHA.1032@.TK2MSFTNGP12.phx.gbl...
>
> Thank you! I'll have to read up on this whole Schema thing in SQL Server.
> J.Jespersen
>
>