what is wrong in my code to call a stored procedure

Question

I Have created a Stored procedure dbo.test as follows

    use sample 
    go
    create procedure dbo.test as

    DECLARE @command as varchar(1000), @i int
    SET @i = 0
    WHILE @i < 5
    BEGIN
      Print 'I VALUE ' +CONVERT(varchar(20),@i)
    SET @i = @i + 1
    END

Now i am created a c# console application to call the stored procedure as follows

   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Data;
   using System.Data.SqlClient;
   namespace AutomationApp
   {
     class Program
     {
       public void RunStoredProc()
   {
 SqlConnection conn = null;
 SqlDataReader rdr  = null;



 try
 {

  conn = new SqlConnection("Server=(TEST\\SQL2K5EXPRESS);DataBase=sample,IntegratedSecurity=SSPI");
  conn.Open();
  SqlCommand cmd  = new SqlCommand("sample.dbo.test", conn);
  cmd.CommandType = CommandType.StoredProcedure;
        //cmd.ExecuteNonQuery();
  rdr = cmd.ExecuteReader();
  while (rdr.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
              rdr[0], rdr[1]));
        }
       }
           catch(Exception ex)
        {
          Console.writeLine(ex.message);
         }   
 finally
 {
  if (conn != null)
  {
   conn.Close();
  }
  if (rdr != null)
  {
   rdr.Close();
  }
 }
    }
    static void Main(string[] args)
    {
        Console.WriteLine("Hello World");

        Program p= new Program();
        p.RunStoredProc();   
        Console.Read();
    }

}

}

   O/P:
   Hello World
   //Could not find stored procedure 'test'.
   A network-related or instance-specific error occurred while establishing a conne

ction to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Serve r/Instance Specified)

But when i try to run the program it was closing so i debugged the program then at exactly at executeReader() method it was showed that can not find the stored procedure "dbo.test" and when i give the "EXEC dbo.test" at SSMS it display the result as i expected.

waht is wronng with this any Help greatly Appreciated.

Answer 1

Have you tried just using "test" instead of "dbo.test"?

Answer 2

I would suggest it is related to teh permissions granted to the stored procedure. Check out "GRANT EXECUTE <storedProc> TO <User>" you could also check the permissions in SSMS by right clicking on the stored proc.

I understand this is a test stored proc but it is not optimal to create stored procedures in the master database.

All the best :)

Answer 3

Why are you looking in the master database?

Also, your code needed some cleanup:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace AutomationApp
{
    class Program
    {
        public void RunStoredProc()
        {
            Console.WriteLine("\nTop 10 Most Expensive Products:\n");

            using (SqlConnection conn = 
                new SqlConnection(
                    "Server=(local);DataBase=master;IntegratedSecurity=SSPI"))
            {
                conn.Open();
                using (SqlCommand cmd = 
                      new SqlCommand("dbo.test", conn) {
                          CommandType = CommandType.StoredProcedure})
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Console.WriteLine(String.Format("{0}, {1}",
                                                            rdr[0], rdr[1]));
                        }
                    }
                }
            }
        }
    }
}

Answer 4

Just change your connection string:

conn = new SqlConnection("Server=(local);DataBase=master;IntegratedSecurity=SSPI");

You're hitting the master database. You want to hit whatever your database is called.

Alternatively, call the stored proc as dbname.dbo.test.

Answer 5

Your stored procedure doesn't return any results. Change your PRINT into SELECT. You may be getting the error because ADO.NET doesn't know how to react to the status messages the print statement causes.

Answer 6

First of all - there's typo in your connection string - you need to use all semicolons - not semicolon once and commas the other time. Between DAtaBase= and IntegratedSEcurity, you have a comma - plus, it has to be "Integrated Security" (with a space!). Check out www.connectionstrings.com for the details on how to properly create a connection string.

Your original connection string in your post:

conn = new SqlConnection(
  "Server=(TEST\\SQL2K5EXPRESS);DataBase=sample,IntegratedSecurity=SSPI");

should really be:

 conn = new SqlConnection(
   "Server=(TEST\\SQL2K5EXPRESS);DataBase=sample;Integrated Security=SSPI");

Once that would work, I think the main problem is that you're trying to pass in a SQL statement in the parameter @command to the stored procedure, which you execute inside the stored procedure (not in your current post, but in others you've posted), and you want to read out the rows returned by that statement.

But you have no way of being sure (short of parsing the SQL statement you're passing in) whether or not that SQL statement will actually indeed return values or whether it's a DDL statement like INSERT, CREATE TABLE or something.

So you have ADO.NET, calling a stored proc, which dynamically executes an arbitrary SQL statement, and you still want to be able to retrieve those results....

In my opinion, you need to rearchitect your approach - this will never work reliably - find another way to do what you're trying to do - there must be a way to do this with less dynamic execution of SQL inside a stored proc and stuff........ there's gotta be an easier way!

Marc

Answer 7

The SQL error you see is connecting to the server. It doesn't even get that far, so it isn't related to permissions at all. As far as your client goes, you'd get the same error if you connected to "Server=GroverCleveland\Instance1" (assuming it doesn't exist on your network).

I think that the problem is that you are wrapping the server name in parens, if you can connect to it fine using other SQL clients on the same client box. Try the following:

conn = new SqlConnection("Server=TEST\\SQL2K5EXPRESS;DataBase=sample;IntegratedSecurity=SSPI");

Marc is right about the semicolon, btw. Check out connectionstrings.com for details on that...