"Invalid object name" SqlException
-
Here's what I'm trying to do, using C#: 1 - Connect to SQL Server 2 - Check if a database is present. 3 - Create the database if it isn't. 4 - Create a login/user pair for that database. 5 - Store the connection string for future use. I have this done, confirmed by using SQL Server Management Studio Express. The following method throws an SqlException:
private static SqlDataReader executeQuery(String query) { SqlCommand command = new SqlCommand(query, Connection); SqlDataReader reader; try { openConnection(); reader = command.ExecuteReader(); return reader; } catch { throw; } finally { //connection left open because the reader is worked on elsewhere. } }The query: SELECT ReportGroupName FROM dbo.ReportGroups The error message: Invalid object name 'dbo.ReportGroups'. The offending line of code: reader = command.ExecuteReader(); Some lines from the script I used to create the database, tables and user:
CREATE LOGIN [bim] WITH PASSWORD=N'*******', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = N'bim', @rolename = N'dbcreator'
CREATE USER [bim] FOR LOGIN [bim] WITH DEFAULT_SCHEMA=[dbo]
CREATE TABLE [dbo].[ReportGroups]I have a feeling that the problem lies with how I setup the default user, since I'm still learning databases, and it's been pretty hit or miss. However, the application worked previously using the above method with the same query string - the exception has only occurred recently, after creating the database from a script rather than in using the visual editors in SQL Server Management Studio. The script I used to create the database was derived from exports of the original database and tables, with only modifications made to create a login and a user. Any assistance on tracking down how to fix this error would be greatly appreciated.
-
Here's what I'm trying to do, using C#: 1 - Connect to SQL Server 2 - Check if a database is present. 3 - Create the database if it isn't. 4 - Create a login/user pair for that database. 5 - Store the connection string for future use. I have this done, confirmed by using SQL Server Management Studio Express. The following method throws an SqlException:
private static SqlDataReader executeQuery(String query) { SqlCommand command = new SqlCommand(query, Connection); SqlDataReader reader; try { openConnection(); reader = command.ExecuteReader(); return reader; } catch { throw; } finally { //connection left open because the reader is worked on elsewhere. } }The query: SELECT ReportGroupName FROM dbo.ReportGroups The error message: Invalid object name 'dbo.ReportGroups'. The offending line of code: reader = command.ExecuteReader(); Some lines from the script I used to create the database, tables and user:
CREATE LOGIN [bim] WITH PASSWORD=N'*******', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = N'bim', @rolename = N'dbcreator'
CREATE USER [bim] FOR LOGIN [bim] WITH DEFAULT_SCHEMA=[dbo]
CREATE TABLE [dbo].[ReportGroups]I have a feeling that the problem lies with how I setup the default user, since I'm still learning databases, and it's been pretty hit or miss. However, the application worked previously using the above method with the same query string - the exception has only occurred recently, after creating the database from a script rather than in using the visual editors in SQL Server Management Studio. The script I used to create the database was derived from exports of the original database and tables, with only modifications made to create a login and a user. Any assistance on tracking down how to fix this error would be greatly appreciated.
Hrm. It appears that I wasn't setting InitialCatalog when building the connection string, at least not in the right place. You don't have to have an initial catalog when testing a connection to a database, which is code near what I'm doing, but you do have to have it to access database tables. I was worried that it was something simple - turns out it was. Upon reflection, I'm kind of glad.