How to fix Invalid object name 'my_table_name' error using VB.NET to MSSQL.

Hi Guys,
I have a MSSQL (.mdf) database that due to versioning issues can only be opened in VS 2008.
I am attempting to transfer the data to an access database however whenever I attempt to fill the DataSet get "Invalid object name 'sa.my_table_name' error.

I have run this query in a button_Click event to ensure user.
fromSQL = "SELECT name, suser_sname(owner_sid) as owner_name FROM sys.databases;"
which returns the value 'sa'

My research on Google tells me that it has something to do with schema names and users however I believe 'sa.table_name' fills that criteria. The million dollar question though; is this criteria appropriate if opening a .mdf from a non MSSQL Server folder?

I would appreciate someone looking over the code below and am happy for the answer to be in VB.NET or C#.

I am using Windows XP Pro, VS 2008 Express and the .mdf can only be accessed via VS 2008 i.e. management utilities on MSSQL are not available.

Thanks FLOG51
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
 Public connSQL As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\flex.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    'this is for Access, change ? to value
    Public connACC As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\flex.mdb;Jet OLEDB:Database Password=xxxxxx")
    Public fromSQL As String
    Public toSQL As String
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub btnMSSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMSSQL.Click
        Dim fromTBL_List As Data.DataTable
        Dim fromROW_List As Data.DataRow
        Dim fromCOL As Data.DataColumn
        Dim tblNAME As String
        fromTBL_List = connSQL.GetSchema("Tables")
        For Each fromROW_List In fromTBL_List.Rows
            'get the table name
            tblNAME = fromROW_List.Item("Table_Name").ToString
            MsgBox("Doing table" & tblNAME)
            'use the table name to build the sql string
            fromSQL = "SELECT * FROM sa." & tblNAME & ";" 
           ' ????? - Exception on line above
            'create an adapter and dataset fill them with the data using the sql string 
            Dim FROMdp1 As SqlClient.SqlDataAdapter
            Dim FROMds1 As New DataSet()
            FROMdp1 = New SqlClient.SqlDataAdapter(fromSQL, connSQL)
            FROMdp1.Fill(FROMds1, "sa." & tblNAME)
            If FROMds1.Tables.Item(0).Rows.Count > 0 Then
                'start to build the toSQL string to insert into Access for this table
                toSQL = "INSERT INTO " & tblNAME & " ("
                'get the names of the columns and use them in the string one by one
                For Each fromCOL In FROMds1.Tables(0).Columns
                    toSQL = toSQL & fromCOL.Caption.ToString & ", "
                'chuck on the final bit of the sql prior to the values
                toSQL = toSQL & ") VALUES ("
etc etc

Open in new window

Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
<< I suspect that VS has some access method that may not use the MSSQL settings >>

Till SQL Server 2000, Schemas are not of much importance but starting from SQL Server 2005 and 2008, Usage of schemas is much more advised.

Issue the below query in SSMS or any query analyzer window and check out the schema name of that table.

WHERE table_name = 'tblNAME'

Prefix that Schema name before you table and make a call from application like schema_name.table_name which should work.
Anurag AgarwalPython DeveloperCommented:
What i can make out from the code & description you have provided is that -
You are connecting to the database using Integrated Security i.e. windows authentication. If you are using windows authentication then no point putting "sa" prior to the table names for fetching the records from database.
Have you tried your code without putting "sa" before the table names, what errors you are getting?
DimitrisSenior Solution ArchitectCommented:
try this
change line 38 of your posted code

fromSQL = "SELECT * FROM sa." & tblNAME & ";"

with this
fromSQL = "SELECT * FROM dbo.[" & tblNAME & "];"
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

FLOG51Author Commented:
Hi anuragal and dankangr
first: Yes I have tried the query before table name, only diff seems to be the error ie. if I use the 'sa' the erro is "invalid object name 'sa.table_name' etc and without the 'sa' error is invalid object name 'table_name'.

2nd: dankangr; Both your fromSQL and my original fromSQL both deliver a valid table name from the .mdf and I have tested previously with a loop msgbox combination that showed all appropriate tables in sequence. So the data table names are being delivered one at a time.

The exception is related to the "FROMdp1.Fill(FROMds1, "sa." & tblNAME)" which I have attempted with these three combinations

FROMdp1.Fill(FROMds1, "sa." & tblNAME)
FROMdp1.Fill(FROMds1,  tblNAME)
FROMdp1.Fill(FROMds1, "dbo." & tblNAME)

all three output the same object error.

I believe that there may be some discrepancy between calling for a mdf from a MSSQL location that uses the Server user info and calling for the .mdf from a location like desktop i.e. I suspect that VS has some access method that may not use the MSSQL settings (this is just a theory as I have limited MSSQL experience)

Raja Jegan RSQL Server DBA & ArchitectCommented:
Its best practice to create Schemas like

CREATE SCHEMA schema_name;

and then create all tables in it, so that these kind of confusions can be avoided. Hence you can refer tables using two-part naming like

FLOG51Author Commented:
Yes I agree,

Prob with this database is that there is a versioning issue and all attempts to open in SQL Server environments have failed. *See previous post 'Export data from SQL Server database file ".mdf"'

I have attempted to attach database to an instance etc and it states that it is already attached however all utilities fail to show this database as an list option to connect to.

So only resolution seems to be to handle all Access from VS 2008
FLOG51Author Commented:
Hi rrjegan17,
In VS I added another piece of code
    Private Sub btnGetSchema_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetSchema.Click

        ' Query to get Schema username VERY IMPORTANT for Visual Studio use
        fromSQL = "SELECT table_schema FROM INFORMATION_SCHEMA.tables WHERE table_name = 'PosProducts'"

        Dim FROMdp2 As SqlClient.SqlDataAdapter
        Dim FROMds2 As New DataSet()
        FROMdp2 = New SqlClient.SqlDataAdapter(fromSQL, connSQL)
        FROMdp2.Fill(FROMds2, fromSQL)

    End Sub

 that used your suggested query for SSMS and I successfully found the table_schema information required to complete the connection.

Thank you to all involved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.