?
Solved

SQL Database Connection question

Posted on 2014-02-18
5
Medium Priority
?
280 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
  • 3
5 Comments
 
LVL 41

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

593 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