Solved

SQL Database Connection question

Posted on 2014-02-18
5
263 Views
Last Modified: 2014-02-23
Hi

I have inherited a website designed a long time ago which has a SQL database back end.

I have exported the database from the hosting site and I am now trying to connect to it using the existing code.


The connection to the live server is Data Source=mssql1.servername.com; Initial Catalog=Databasename;  User Id=myuser;   Password=mypassword;

I want to set the connection to my local server.

Data Source=localhost; Initial Catalog=Databasename; Integrated Security = true;

This connects to the database.

However when I run an sql query in the code

"SELECT * FROM tblA" I get an error "Invalid object name 'tblA'."

When I run the query in MSSMS it does not run either.
When I run the query "SELECT * FROM myuser.tblA" it works.

So it has something to do with the logins, but I cannot get it to work. I have tried creating a security login in MSSMS but I cannot attach it to the database.

Instead of the table name showing as dbo.tblA it shows as myuser.tblA.

Can anyone help please. It is relatively urgent as I have to do some work on this website.
0
Comment
Question by:elmbrook
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39868885
just to get you up and running you can always connect as SA to your local, and that should grant you access to all DBs / tables.

You may need to transfer the server logins assuming you have SA on the databse there.  See the link for how to transfer server users as well as DB users:
http://www.experts-exchange.com/Q_28355004.html
0
 

Author Comment

by:elmbrook
ID: 39869031
I have access to the tables and the DB.

This web application was done using SQL commands. It's the SQL commands that do not work on the local machine.

SELECT * FROM tblA (code from the project) does not work.

SELECT * FROM myuser.tblA does work. It just means that I have to change every SQL statement in the project to do my stuff, then back again when I have made my changes which is not what I want to do.

I have just tried the script in the article but my hosting is in a shared hosting environment I do not have access to the Master database
0
 

Accepted Solution

by:
elmbrook earned 0 total points
ID: 39869133
I have generated scripts for the relevant tables and created a new database.
0
 
LVL 18

Expert Comment

by:Dennis Aries
ID: 39874763
Usually, when you create a database, the tables belong to 'dbo' (databaseowner). The way you describe it, the database is available to you, but it is not yours as a user. Check the owner of the database and change it if needed.
0
 

Author Closing Comment

by:elmbrook
ID: 39880410
found solution myself by generating scripts to populate a newly created database.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question