Solved

SQL Database Connection question

Posted on 2014-02-18
5
252 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 39

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach 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!
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now