August 3, 2012 at 5:00 am
Hi,
I'll preface by saying that I am not very knowledgable about IIS/SQL Server/MS Server.
However, I have been tasked to help one of our clients move their site from our hosting to another host.
After alot of headaches I set-up everything on the new hosts servers but now when running the sites I am getting this error
Invalid_object_name_tablenbame in all of the sql calls from the asp pages.
Note that this is an old site written in ASP classic/vbscript.
I have googled and concluded that adding the databasename to the query will resolve the problem but my issue is that there are over 300 files per site and 4 sites that need migrating/fixing.
So is this a setting in SQL server that I have missed so that it doesn't need the database name in the sql statement? Is it something to do with the way I have set up the users (I have assigned them dbo as default schema and db_owner and database name schemas).
I'll try and provide any further information you request but please help me by telling me where/how to find it - I'm a developer not a sys admin and know just enough to be dangerous.
The new server is IIS7, SQl Server 2008, Windows Server 2008 R2 (64bit).
many thanks.
August 3, 2012 at 12:20 pm
Hunnie (8/3/2012)
Hi,I'll preface by saying that I am not very knowledgable about IIS/SQL Server/MS Server.
However, I have been tasked to help one of our clients move their site from our hosting to another host.
After alot of headaches I set-up everything on the new hosts servers but now when running the sites I am getting this error
Invalid_object_name_tablenbame in all of the sql calls from the asp pages.
Note that this is an old site written in ASP classic/vbscript.
I have googled and concluded that adding the databasename to the query will resolve the problem but my issue is that there are over 300 files per site and 4 sites that need migrating/fixing.
So is this a setting in SQL server that I have missed so that it doesn't need the database name in the sql statement? Is it something to do with the way I have set up the users (I have assigned them dbo as default schema and db_owner and database name schemas).
I'll try and provide any further information you request but please help me by telling me where/how to find it - I'm a developer not a sys admin and know just enough to be dangerous.
The new server is IIS7, SQl Server 2008, Windows Server 2008 R2 (64bit).
many thanks.
You didn't give us much to go on here. We can't see your screen and we don't anything about your environment. It might be as simple as changing your connection string. It might be any number of things.
I have googled and concluded that adding the databasename to the query will resolve the problem but my issue is that there are over 300 files per site and 4 sites that need migrating/fixing.
Have you actually tried this? It sounds like you found some info on a webpage that you think might fix the issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2012 at 4:03 pm
Hi, thanks for the response.
I know my plea for help was rather vague but I really am fumbling in dark in here. I will try and provide as much extra information as I can but I do need some help in being directed as to what you need to know in order to assist me.
Here are the connection strings and sql statement from our servers where everything works ok:
myconn.open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=steelgroup; User ID=steelgroup; Password=xxxxxxxx; Network Library=dbmssocn;"
sql = "SELECT * FROM tblhtml WHERE autoid =" & id
and here is the connection string for the new host and the amended sql statement that works
myconn.Open "Provider=sqloledb;Data Source=127.0.0.1;Initial Catalog=steelgroup;User Id=steelgroup;Password=xxxxxxxx;"
sql = "SELECT * FROM steelgroup.tblhtml WHERE autoid =" & id
To my eye the connection strings are pretty much the same - the new hosts provided the connection string.
The only otherdifference is having to reference the db name in the sql statement and this is really the issue that I am trying to resolve. I'm hoping that I have just configured something incorrectly - either at the db or user/login level - and that I can set a flag or switch or permission somewhere so that all the existing sql statements will work without having to add the database name prefix to the table name.
I'll happily provide screen grabs, code examples, whatever anyone needs to help me - I don't mean to be vague but i really am having to work in an environment in which I have very little experience.
Thanks.
September 7, 2012 at 2:59 am
Hi all,
I was pulled across to another project but now am back to trying to get this issue resolved.
If anybody could shed any light on what I can try and do so that I don't need to edit 1200+ files to add the database name to each sql statement.
Thanks
Hunnie
September 7, 2012 at 3:05 am
The Initial Catalog=steelgroup; should be all that's needed to set the database for the connection. The query you've referenced doesn't reference a database name at all, it references a schema called steelgroup.
What's the default schema for the user in the environment that doesn't work vs the one that does?
Are the objects in the database all part of the steelgroup schema or dbo? E.g. if you look at tables within SSMS, are they all dbo.tablename or steepgroup.tablename?
September 7, 2012 at 3:11 am
Hunnie (8/3/2012)
Hi,Is it something to do with the way I have set up the users (I have assigned them dbo as default schema and db_owner and database name schemas).
This is the problem. If this call works:
sql = "SELECT * FROM steelgroup.tblhtml WHERE autoid =" & id
Then the default schema for the user needs to be steelgroup for it to work with 1 part naming convention.
September 7, 2012 at 5:09 am
Thank you!
You are a scholar and a gentleman and have saved me hours of tedium.
September 7, 2012 at 5:11 am
No problem, Happy Friday
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply