?
Solved

SQL Database Connection question

Posted on 2014-02-18
5
Medium Priority
?
271 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

771 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