elmbrook
asked on
SQL Database Connection question
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.c
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.
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.c
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
found solution myself by generating scripts to populate a newly created database.
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:
https://www.experts-exchange.com/questions/28355004/How-to-get-my-database-users-in-new-database-without-a-restore.html