SQL and vbscript

Trying to read a sql server 2005 table from a vendor and I'm not having any
luck

I'm getting the error : Invalid Object Name '_SMDBA_._CUSTOMER_' on
execution of the select statement

I'm not real skilled when it comes to attaching to sql. I have the sa as
the authorizing account so I can attach. Ideas?

The vendor has just installed the software and I have to sync up fields
between AD and this app and I have zero experience with SQL 2005. So maybe
there is a setting in sql that has to be configured. I just don't
understand the use if the two decimal name (The FROM name) and if the script
can handle it correctly.

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=SDE;", strUserName, strPassword

objRecordset.CursorLocation = adUseClient

objRecordset.Open "SELECT * FROM _SMDBA_._CUSTOMER_" , _

objConnection, adOpenStatic, adLockOptimistic


--
Paul

David
Wed Feb 28 16:40:54 CST 2007



"Paul Bergson [MVP-DS]" <pbergson@allete_nospam.com> wrote in message
news:#u2AdY4WHHA.3592@TK2MSFTNGP03.phx.gbl...
> Trying to read a sql server 2005 table from a vendor and I'm not having
> any luck
>
> I'm getting the error : Invalid Object Name '_SMDBA_._CUSTOMER_' on
> execution of the select statement
>
> I'm not real skilled when it comes to attaching to sql. I have the sa as
> the authorizing account so I can attach. Ideas?
>
> The vendor has just installed the software and I have to sync up fields
> between AD and this app and I have zero experience with SQL 2005. So
> maybe there is a setting in sql that has to be configured. I just don't
> understand the use if the two decimal name (The FROM name) and if the
> script can handle it correctly.
>

Tables in SQL Server have a three-part name, delimited by '.'. The first
part is the name of the database, the second part is the name of the schema
inside the database, and the third part is the table name.

Every connection has a current database context and that is the default for
that part of the name. Every user has a default schema, which is the
default for the schema part of the name. The default schema for SA is
always dbo, which is also the schema used if you simply omit the schema part
of the name. So assuming you have a table called _CUSTOMER_ the name


_SMDBA_._CUSTOMER_

Would the identifier _SMDBA_ would be a schema name, and the database would
be the current database.

Then name
_SMDBA_.DBO._CUSTOMER_
and
_SMDBA_.._CUSTOMER_

Are equivilent, and would refer to a table called _CUSTOMER_ in the DBO
schema of a database called _SMDBA_.

The name

_CUSTOMER_

would refer to a table called _CUSTOMER_ in the user's default schema in the
current database, and if no such table exists in the user's default schema,
then it would refer to a table called _CUSTOMER_ in the DBO schema.


David

Bob
Thu Mar 01 07:37:15 CST 2007

Paul Bergson [MVP-DS] wrote:
> I'm not real skilled when it comes to attaching to sql. I have the
> sa as the authorizing account so I can attach. Ideas?

In addition to David;'s correct reply, i want to add that it is a huge
security breach to use the sa account from client applications. Instead,
create a sql login with the minimum rights needed to accomplish whatever
task your code is designed to do.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Paul
Thu Mar 01 08:33:06 CST 2007

I agree. I included the sa as an example. I just didn't want an answer
that asked are you sure the user has the permissions to the Table.

--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT

http://www.pbbergs.com

Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u$VG8aAXHHA.5092@TK2MSFTNGP03.phx.gbl...
> Paul Bergson [MVP-DS] wrote:
>> I'm not real skilled when it comes to attaching to sql. I have the
>> sa as the authorizing account so I can attach. Ideas?
>
> In addition to David;'s correct reply, i want to add that it is a huge
> security breach to use the sa account from client applications. Instead,
> create a sql login with the minimum rights needed to accomplish whatever
> task your code is designed to do.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Paul
Thu Mar 01 11:21:06 CST 2007

So I included the database name and it worked
SDE._SMDBA_.._CUSTOMER_

This was very helpful. Thanks!!!

I am unclear why it needed the Database name since this is defined in the
DSN. Is that just a requirement?




--
Paul



"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:%23dLJDm4WHHA.5060@TK2MSFTNGP06.phx.gbl...
>
>
> "Paul Bergson [MVP-DS]" <pbergson@allete_nospam.com> wrote in message
> news:#u2AdY4WHHA.3592@TK2MSFTNGP03.phx.gbl...
>> Trying to read a sql server 2005 table from a vendor and I'm not having
>> any luck
>>
>> I'm getting the error : Invalid Object Name '_SMDBA_._CUSTOMER_' on
>> execution of the select statement
>>
>> I'm not real skilled when it comes to attaching to sql. I have the sa as
>> the authorizing account so I can attach. Ideas?
>>
>> The vendor has just installed the software and I have to sync up fields
>> between AD and this app and I have zero experience with SQL 2005. So
>> maybe there is a setting in sql that has to be configured. I just don't
>> understand the use if the two decimal name (The FROM name) and if the
>> script can handle it correctly.
>>
>
> Tables in SQL Server have a three-part name, delimited by '.'. The first
> part is the name of the database, the second part is the name of the
> schema inside the database, and the third part is the table name.
>
> Every connection has a current database context and that is the default
> for that part of the name. Every user has a default schema, which is the
> default for the schema part of the name. The default schema for SA is
> always dbo, which is also the schema used if you simply omit the schema
> part of the name. So assuming you have a table called _CUSTOMER_ the name
>
>
> _SMDBA_._CUSTOMER_
>
> Would the identifier _SMDBA_ would be a schema name, and the database
> would be the current database.
>
> Then name
> _SMDBA_.DBO._CUSTOMER_
> and
> _SMDBA_.._CUSTOMER_
>
> Are equivilent, and would refer to a table called _CUSTOMER_ in the DBO
> schema of a database called _SMDBA_.
>
> The name
>
> _CUSTOMER_
>
> would refer to a table called _CUSTOMER_ in the user's default schema in
> the current database, and if no such table exists in the user's default
> schema, then it would refer to a table called _CUSTOMER_ in the DBO
> schema.
>
>
> David