asked on

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
        connSQL.Open()
        connACC.Open()
        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 & ", "
                Next
                'chuck on the final bit of the sql prior to the values
                toSQL = toSQL & ") VALUES ("
etc etc

Open in new window

Anurag Agarwal

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?

Anurag

Dimitris

try this
change line 38 of your posted code

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

with this
fromSQL = "SELECT * FROM dbo.[" & tblNAME & "];"

FLOG51

ASKER

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 R

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

schema_name.table_name

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FLOG51

ASKER

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

FLOG51

ASKER

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
connSQL.Open()

' 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)

MsgBox(FROMds2.Tables.Item(0).Rows(0).Item("table_schema").ToString)
connSQL.Close()
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.