invalid object name after upsize

Hi guys

I've just upsized my database from Access 2003 to SQL Server, and uploaded
it to my webhost. They setup the database for me, and I've changed the
connection string.

However, I'm getting the following error...

[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'pages'.

page IS a table in my database, but SQL doesn't seem to think so. What am I
doing wrong?

My connection string is as follows...

set dConn = Server.CreateObject("ADODB.Connection")
dConn.Open("Driver={SQL
Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")

IT's not the conenction string I don't think, cos obviously it's connecting,
and I've just tried with a DSN, and I get the same error.

Help appreciated!

Cheers


Dan

Re: invalid object name after upsize

"Dan Nash" wrote in message
news:23909B13-FCA4-4D4A-8916-A05B448E1116@microsoft.com...
> Hi guys
>
> I've just upsized my database from Access 2003 to SQL Server, and uploaded
> it to my webhost. They setup the database for me, and I've changed the
> connection string.
>
> However, I'm getting the following error...
>
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'pages'.
>
> page IS a table in my database, but SQL doesn't seem to think so. What am
I
> doing wrong?
>
> My connection string is as follows...
>
> set dConn = Server.CreateObject("ADODB.Connection")
> dConn.Open("Driver={SQL
> Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
>
> IT's not the conenction string I don't think, cos obviously it's
connecting,
> and I've just tried with a DSN, and I get the same error.
>
> Help appreciated!
>
> Cheers
>
>
> Dan

This may not be your problem but "pages" is different than "page".

Re: invalid object name after upsize

Dan Nash wrote:
> Hi guys
>
> I've just upsized my database from Access 2003 to SQL Server, and
> uploaded it to my webhost. They setup the database for me, and I've
> changed the connection string.
>
> However, I'm getting the following error...
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]

Nothing to do with this particular problem, but:
http://www.aspfaq.com/show.asp?id=2126

> Invalid object name
> 'pages'.
>
> page IS a table in my database, but SQL doesn't seem to think so.
> What am I doing wrong?
>
> My connection string is as follows...
>
> set dConn = Server.CreateObject("ADODB.Connection")
> dConn.Open("Driver={SQL
> Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
>
> IT's not the conenction string I don't think, cos obviously it's
> connecting, and I've just tried with a DSN, and I get the same error.
>

Totally irrelevant. It's your query that's failing. Why not show us your
query?

Anyways, as McKirahan says, 'page' is different from 'pages'.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: invalid object name after upsize

Hi

Let me clarify.. i forgot the s :o)

pages is the name of the table. It was my mistake in the post, thanks though.

If it helps my select statement is

SELECT * FROM pages WHERE page_code = ' & page_code & '

where page_code is "PG_HOME", which IS a record in the table pages.

Hope someone can help wtih this - it's driving me mental!

Cheers


Dan

"McKirahan" wrote:

> "Dan Nash" wrote in message
> news:23909B13-FCA4-4D4A-8916-A05B448E1116@microsoft.com...
> > Hi guys
> >
> > I've just upsized my database from Access 2003 to SQL Server, and uploaded
> > it to my webhost. They setup the database for me, and I've changed the
> > connection string.
> >
> > However, I'm getting the following error...
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'pages'.
> >
> > page IS a table in my database, but SQL doesn't seem to think so. What am
> I
> > doing wrong?
> >
> > My connection string is as follows...
> >
> > set dConn = Server.CreateObject("ADODB.Connection")
> > dConn.Open("Driver={SQL
> > Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
> >
> > IT's not the conenction string I don't think, cos obviously it's
> connecting,
> > and I've just tried with a DSN, and I get the same error.
> >
> > Help appreciated!
> >
> > Cheers
> >
> >
> > Dan
>
> This may not be your problem but "pages" is different than "page".
>
>
>
>

Re: invalid object name after upsize

My query, as shown in my reply to McKirahan, is as follows

SELECT * FROM hitcounter WHERE page_code = 'PG_HOME'

worked fine on access, so i assume its some difference with SQL, or
something that the upsizing wizard has done....


"Bob Barrows [MVP]" wrote:

> Dan Nash wrote:
> > Hi guys
> >
> > I've just upsized my database from Access 2003 to SQL Server, and
> > uploaded it to my webhost. They setup the database for me, and I've
> > changed the connection string.
> >
> > However, I'm getting the following error...
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
>
> Nothing to do with this particular problem, but:
> http://www.aspfaq.com/show.asp?id=2126
>
> > Invalid object name
> > 'pages'.
> >
> > page IS a table in my database, but SQL doesn't seem to think so.
> > What am I doing wrong?
> >
> > My connection string is as follows...
> >
> > set dConn = Server.CreateObject("ADODB.Connection")
> > dConn.Open("Driver={SQL
> > Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
> >
> > IT's not the conenction string I don't think, cos obviously it's
> > connecting, and I've just tried with a DSN, and I get the same error.
> >
>
> Totally irrelevant. It's your query that's failing. Why not show us your
> query?
>
> Anyways, as McKirahan says, 'page' is different from 'pages'.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: invalid object name after upsize

SQL satement should read...

SELECT * FROM pages WHERE page_code = 'PG_HOME'

dunno why i put hitcounter - thats entirely different :p


"Dan Nash" wrote:

> My query, as shown in my reply to McKirahan, is as follows
>
> SELECT * FROM hitcounter WHERE page_code = 'PG_HOME'
>
> worked fine on access, so i assume its some difference with SQL, or
> something that the upsizing wizard has done....
>
>
> "Bob Barrows [MVP]" wrote:
>
> > Dan Nash wrote:
> > > Hi guys
> > >
> > > I've just upsized my database from Access 2003 to SQL Server, and
> > > uploaded it to my webhost. They setup the database for me, and I've
> > > changed the connection string.
> > >
> > > However, I'm getting the following error...
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> >
> > Nothing to do with this particular problem, but:
> > http://www.aspfaq.com/show.asp?id=2126
> >
> > > Invalid object name
> > > 'pages'.
> > >
> > > page IS a table in my database, but SQL doesn't seem to think so.
> > > What am I doing wrong?
> > >
> > > My connection string is as follows...
> > >
> > > set dConn = Server.CreateObject("ADODB.Connection")
> > > dConn.Open("Driver={SQL
> > > Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
> > >
> > > IT's not the conenction string I don't think, cos obviously it's
> > > connecting, and I've just tried with a DSN, and I get the same error.
> > >
> >
> > Totally irrelevant. It's your query that's failing. Why not show us your
> > query?
> >
> > Anyways, as McKirahan says, 'page' is different from 'pages'.
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
> >

Re: invalid object name after upsize

According to :
http://www.aspfaq.com/show.asp?id=2080

"Pages" is a reserved keyword that should be avoided when naming your
database objects. We recommend that you change the name, but if you can't
for some reason, you will need to remember to delimit the word with brackets
[] when using it in sql statements:

SELECT * FROM [pages]

HTH,
Bob Barrows

Dan Nash wrote:
> SQL satement should read...
>
> SELECT * FROM pages WHERE page_code = 'PG_HOME'
>
> dunno why i put hitcounter - thats entirely different :p
>
>
> "Dan Nash" wrote:
>
>> My query, as shown in my reply to McKirahan, is as follows
>>
>> SELECT * FROM hitcounter WHERE page_code = 'PG_HOME'
>>
>> worked fine on access, so i assume its some difference with SQL, or
>> something that the upsizing wizard has done....
>>
>>
>> "Bob Barrows [MVP]" wrote:
>>
>>> Dan Nash wrote:
>>>> Hi guys
>>>>
>>>> I've just upsized my database from Access 2003 to SQL Server, and
>>>> uploaded it to my webhost. They setup the database for me, and I've
>>>> changed the connection string.
>>>>
>>>> However, I'm getting the following error...
>>>>
>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>>>
>>> Nothing to do with this particular problem, but:
>>> http://www.aspfaq.com/show.asp?id=2126
>>>
>>>> Invalid object name
>>>> 'pages'.
>>>>
>>>> page IS a table in my database, but SQL doesn't seem to think so.
>>>> What am I doing wrong?
>>>>
>>>> My connection string is as follows...
>>>>
>>>> set dConn = Server.CreateObject("ADODB.Connection")
>>>> dConn.Open("Driver={SQL
>>>> Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
>>>>
>>>> IT's not the conenction string I don't think, cos obviously it's
>>>> connecting, and I've just tried with a DSN, and I get the same
>>>> error.
>>>>
>>>
>>> Totally irrelevant. It's your query that's failing. Why not show us
>>> your query?
>>>
>>> Anyways, as McKirahan says, 'page' is different from 'pages'.
>>>
>>> Bob Barrows
>>>
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap
>>> so I don't check it very often. If you must reply off-line, then
>>> remove the "NO SPAM"

--
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.

Re: invalid object name after upsize

I usually see that error when the account executing the SQL query does not
have select permission on the table. Did you upsize the database under the
same account that you are using to connect to it? If not, the table needs to
be owned by the DBO and the account you are using needs to be GRANTED access
as in:

GRANT INSERT,UPDATE,SELECT,DELETE ON PAGES TO MYUSER;

Also note the other responses regarding PAGES being a reserved word (change
table name or put in square brackets - "[pages]"). I think you get a syntax
error for that but I could be wrong.

I have also found that when upsizing from Access to SQLServer it is safer to
create the table structure separately and then use DTS to transfer the data.
DTS (I presume that is what the upsizing wizard uses) tends to change column
datatypes and lose constraints.

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com


"Dan Nash" wrote in message
news:23909B13-FCA4-4D4A-8916-A05B448E1116@microsoft.com...
> Hi guys
>
> I've just upsized my database from Access 2003 to SQL Server, and uploaded
> it to my webhost. They setup the database for me, and I've changed the
> connection string.
>
> However, I'm getting the following error...
>
> [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'pages'.
>
> page IS a table in my database, but SQL doesn't seem to think so. What am
I
> doing wrong?
>
> My connection string is as follows...
>
> set dConn = Server.CreateObject("ADODB.Connection")
> dConn.Open("Driver={SQL
> Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
>
> IT's not the conenction string I don't think, cos obviously it's
connecting,
> and I've just tried with a DSN, and I get the same error.
>
> Help appreciated!
>
> Cheers
>
>
> Dan

Re: invalid object name after upsize

Hi Bob

Thanks for that. I didn't know Pages was a reserved word. However I had
tried [] and have since tried a different table name, both to no avail.

Having run SQL Ent Manager on the server, it appears that my web hosts have
named the db wrong, connected it to the wrong file and not added any tables.
This might have something to do with it :o)

Thanks for your help - I'll keep you posted.

Cheers


Dan


"Bob Barrows [MVP]" wrote:

> According to :
> http://www.aspfaq.com/show.asp?id=2080
>
> "Pages" is a reserved keyword that should be avoided when naming your
> database objects. We recommend that you change the name, but if you can't
> for some reason, you will need to remember to delimit the word with brackets
> [] when using it in sql statements:
>
> SELECT * FROM [pages]
>
> HTH,
> Bob Barrows
>
> Dan Nash wrote:
> > SQL satement should read...
> >
> > SELECT * FROM pages WHERE page_code = 'PG_HOME'
> >
> > dunno why i put hitcounter - thats entirely different :p
> >
> >
> > "Dan Nash" wrote:
> >
> >> My query, as shown in my reply to McKirahan, is as follows
> >>
> >> SELECT * FROM hitcounter WHERE page_code = 'PG_HOME'
> >>
> >> worked fine on access, so i assume its some difference with SQL, or
> >> something that the upsizing wizard has done....
> >>
> >>
> >> "Bob Barrows [MVP]" wrote:
> >>
> >>> Dan Nash wrote:
> >>>> Hi guys
> >>>>
> >>>> I've just upsized my database from Access 2003 to SQL Server, and
> >>>> uploaded it to my webhost. They setup the database for me, and I've
> >>>> changed the connection string.
> >>>>
> >>>> However, I'm getting the following error...
> >>>>
> >>>> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >>>
> >>> Nothing to do with this particular problem, but:
> >>> http://www.aspfaq.com/show.asp?id=2126
> >>>
> >>>> Invalid object name
> >>>> 'pages'.
> >>>>
> >>>> page IS a table in my database, but SQL doesn't seem to think so.
> >>>> What am I doing wrong?
> >>>>
> >>>> My connection string is as follows...
> >>>>
> >>>> set dConn = Server.CreateObject("ADODB.Connection")
> >>>> dConn.Open("Driver={SQL
> >>>> Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
> >>>>
> >>>> IT's not the conenction string I don't think, cos obviously it's
> >>>> connecting, and I've just tried with a DSN, and I get the same
> >>>> error.
> >>>>
> >>>
> >>> Totally irrelevant. It's your query that's failing. Why not show us
> >>> your query?
> >>>
> >>> Anyways, as McKirahan says, 'page' is different from 'pages'.
> >>>
> >>> Bob Barrows
> >>>
> >>> --
> >>> Microsoft MVP - ASP/ASP.NET
> >>> Please reply to the newsgroup. This email account is my spam trap
> >>> so I don't check it very often. If you must reply off-line, then
> >>> remove the "NO SPAM"
>
> --
> 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.
>
>
>

Re: invalid object name after upsize

Hi guys

Just thought I'd update you as to what was actually going on, now we've
sorted it.

To be honest, I think a number of factors contributed to the problems.
Firstly, I was using MSDE2000 to upsize to, and I'd set a couple of fields to
"bigint". They then proceeded to put my database on SQL Server 7.

This meant that when they restored my database, no tables were restored
(hence my invalid object name error).

Anyway once they realised the error, they've moved it and I've got rid of
that error! It's taken a week and a half, and they've certainly gone down in
my estimations, but at least it's fixed!

Thanks for all your help guys


Dan

"Mark Schupp" wrote:

> I usually see that error when the account executing the SQL query does not
> have select permission on the table. Did you upsize the database under the
> same account that you are using to connect to it? If not, the table needs to
> be owned by the DBO and the account you are using needs to be GRANTED access
> as in:
>
> GRANT INSERT,UPDATE,SELECT,DELETE ON PAGES TO MYUSER;
>
> Also note the other responses regarding PAGES being a reserved word (change
> table name or put in square brackets - "[pages]"). I think you get a syntax
> error for that but I could be wrong.
>
> I have also found that when upsizing from Access to SQLServer it is safer to
> create the table structure separately and then use DTS to transfer the data.
> DTS (I presume that is what the upsizing wizard uses) tends to change column
> datatypes and lose constraints.
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> www.ielearning.com
>
>
> "Dan Nash" wrote in message
> news:23909B13-FCA4-4D4A-8916-A05B448E1116@microsoft.com...
> > Hi guys
> >
> > I've just upsized my database from Access 2003 to SQL Server, and uploaded
> > it to my webhost. They setup the database for me, and I've changed the
> > connection string.
> >
> > However, I'm getting the following error...
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
> 'pages'.
> >
> > page IS a table in my database, but SQL doesn't seem to think so. What am
> I
> > doing wrong?
> >
> > My connection string is as follows...
> >
> > set dConn = Server.CreateObject("ADODB.Connection")
> > dConn.Open("Driver={SQL
> > Server};Server=server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;")
> >
> > IT's not the conenction string I don't think, cos obviously it's
> connecting,
> > and I've just tried with a DSN, and I get the same error.
> >
> > Help appreciated!
> >
> > Cheers
> >
> >
> > Dan
>
>
>

Re: invalid object name after upsize

<<
page IS a table in my database, but SQL doesn't seem to think so. What
am I doing wrong?
>>

Don't know if this hopefully might help at all, but if your SQL
statement contains any table names or field names that you think might
be reserved words, be sure to put brackets around them.

strSQL = "SELECT [pages].[pagename] FROM [pages]"

What are reserved Access, ODBC and T-SQL keywords?
http://www.aspfaq.com/show.asp?id=2080

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!