I have been trying to determine why a stored procedure that was created from code will not execute.


The sub btnCreateSP creates a stored procedure called 'AddContacts' within the newly created data base. The SP looks as follows:

USE [HowToDemo]
ALTER PROCEDURE AddContacts AS
INSERT INTO Contact
(ContactID, FirstName, LastName)SELECT EmployeeID, FirstName, LastName FROM NORTHWIND.dbo.Employees

The btnPopulate procedure then calls the procedure.

I get the following error message:

Invalid object name 'NORTHWIND.dbo.Employees'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[AddContacts].

I am connected to the Northwind database, it has a table named employees, I am even connected in sqlserverexp. Can you tell me why I am getting this error.

(btw, this example is microsofts lesson -- I didn't write the code behind it other than change a reference to sqlexpress. It successfully created a database, created a table, created a stored procedure, created a view, then it fails on execute the stored procedure -which is the first place it attempts to connect to the northwind.mdf)

Thanks

hi there.. did you try to run the query in side the database? do you receive the same error? what if you take out dbo from the path to the database?

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Assuming the query executed properly removing dbo from the path will result in the same error. As Gonzalo has already stated, have you tried running the query in SQL Express?

Also when you say "I am even connected in sqlserverexp" I assume that you mean you are connected to SQL Express via SQL Server Management Studio Express, is that correct? If you are, can you see both the HowTo database and the Northwind database underneath the 'Database' folder?

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

As Gonzalo has already stated, have you tried running the query in SQL Express?

Yes I have tried the above -- same error is generated

Also when you say "I am even connected in sqlserverexp" I assume that you mean you are connected to SQL Express via SQL Server Management Studio Express, is that correct? If you are, can you see both the HowTo database and the Northwind database underneath the 'Database' folder?

Yes I have the above done also -- same error

Here is the create sp procedure, and it does successfully create the procedure

Private Sub btnCreateSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateSP.Click
        ' The SqlConnection class allows you to communicate with SQL Server.
        ' The constructor accepts a connection string as an argument. This
        ' connection string uses Integrated Security, which means that you
        ' must have a login in SQL Server, or be part of the Administrators
        ' group for this to work.
        Dim dbConnection As New SqlConnection(connectionString)

        Dim strSQL As String = _
            "USE HowToDemo" & vbCrLf & _
            "IF EXISTS (" & _
            "SELECT * " & _
            "FROM HowToDemo.dbo.sysobjects " & _
            "WHERE Name = 'AddContacts' " & _
            "AND TYPE = 'p')" & vbCrLf & _
            "BEGIN" & vbCrLf & _
            "DROP PROCEDURE AddContacts" & vbCrLf & _
            "END"

        ' A SqlCommand object is used to execute the SQL commands.
        Dim cmd As New SqlCommand(strSQL, dbConnection)

        Try
            ' Open the connection, execute the command, and close the connection.
            ' It is more efficient to ExecuteNonQuery when data is not being
            ' returned.
            dbConnection.Open()
            cmd.ExecuteNonQuery()

        Catch sqlExc As SqlException
            MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Try
            cmd.CommandText = _
                "CREATE PROCEDURE AddContacts AS" & vbCrLf & _
                "INSERT INTO Contact" & vbCrLf & _
                "(ContactID, FirstName, LastName)" & _
                "SELECT EmployeeID, FirstName, LastName " & _
                "FROM NORTHWIND.dbo.Employees"

            cmd.ExecuteNonQuery()
            dbConnection.Close()

            ' Show the controls for the next step.
            lblArrow3.Visible = True
            lblStep4.Enabled = True
            btnCreateView.Enabled = True

            MessageBox.Show("Stored Procedure 'AddContacts' successfully " & _
                "created.", "SPROC Creation Status", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch sqlExc As SqlException
            MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub


And here is the call to the stored procedure....

Private Sub btnPopulate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPopulate.Click

        Dim strSQL As String = "EXECUTE HowToDemo.dbo.AddContacts"

        Try
            ' The SqlConnection class allows you to communicate with SQL Server.
            ' The constructor accepts a connection string as an argument. This
            ' connection string uses Integrated Security, which means that you
            ' must have a login in SQL Server, or be part of the Administrators
            ' group for this to work.

            Dim dbConnection As New SqlConnection(connectionString)


            ' A SqlCommand object is used to execute the SQL commands.
            Dim cmd As New SqlCommand(strSQL, dbConnection)

            ' Open the connection, execute the command, and close the connection.
            ' It is more efficient to ExecuteNonQuery when data is not being
            ' returned.
            dbConnection.Open()
            cmd.ExecuteNonQuery()
            dbConnection.Close()

            ' Show the controls for the next step.
            lblArrow5.Visible = True
            lblStep6.Enabled = True
            btnDisplay.Enabled = True

            MessageBox.Show("Table successfully populated.", _
                "Data Addition Status", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch sqlExc As SqlException
            MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Thanks for the help...

My apologies for the font sizes, I was trying to make them smaller. but I failed to preview the post first.

A couple other things... at the top of the module is

Imports System.Data.SqlClient
Imports System.Data

And then in the public class is ..

Protected Const SqlConnectionString As String = _
        "Server=superman.\SQLEXPRESS;" & _
        "DataBase=;" & _
        "Integrated Security=SSPI"

    Protected Const ConnectionErrorMessage As String = _
        "To run this sample, you must have SQL " & _
        "installed. For " & _
        "instructions on installing SQL, view the documentation file."

    Protected didPreviouslyConnect As Boolean = False
    Protected didCreateTable As Boolean = False
    Protected connectionString As String = SqlConnectionString

Like I said everything works except the populate procedure...

Is it because I didn't include the path for Northwind in the query when it was created?

cmd.CommandText = _
                "CREATE PROCEDURE AddContacts AS" & vbCrLf & _
                "INSERT INTO Contact" & vbCrLf & _
                "(ContactID, FirstName, LastName)" & _
                "SELECT EmployeeID, FirstName, LastName " & _
                "FROM NORTHWIND.dbo.Employees"


Even tho the database is connected in VS and in SQL Studio?

I discovered that the NorthWind database name has been changed in the download file from microsoft

It is spelled 'NORTHWND.MDF', whereas the code was spelled northwind.

I am still getting the exact same error though as in the original post (the following from executing the sp from sql server management studio):

Msg 208, Level 16, State 1, Procedure AddContacts, Line 2
Invalid object name 'NORTHWND.dbo.Employees'.

(1 row(s) affected)

Hi there.. I think that you need an SQL full installed, not an express version.. Since you are working with a express version, so you can see a database from another database, you have to attach that files to the SQL express proccess, that is done inside the sql manager. As you stated before, is the SP doesn't even run inside the manager, then the problem is that the SQL can't find the northwind database.

To attach a database, just load the SQL manager and add the database to the databases folder.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

I do have it attached inside of the SQL SMS. Strange though how when I attach HowToDemo that the name in the databases folder is 'HowToDemo' ....
But when I attach the NorthWnd.mdf it displays its name as "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\NORTHWND.MDF"

Yet both of these files are in the same path/folder. Why would it display them differently?

Another thing, when I start SQL SMS express I get a .Net message about an unhandled exception: "Cannot create a stable subkey under a volatile parent key"

Maybe something is wrong with the install?

well.. you have to change the name of the database (right now the name is all the path :) )

also, I would try re-installing it if you have an error...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========