C# Calling Stored Procedure in MSDB Database

I am writing an application which needs to obtain data from a Stored Procedure.  The SPROC is in the MSDB database.  I've attached the code example below.  There are no parameters.  When I run the code I get an error saying the stored procedure does not exist.  I added a grid and went through the wizard to create the connection to the dbase.  I am not receiving an error but there is no data in my grid.  In the dbase I run EXEC uspGetJobInfo and data is returned but the data does not appear in in my .NET code.

Any ideas?
SqlConnection cn = new SqlConnection(source);
SqlDataAdapter da = new SqlDataAdapter("uspGetJobInfo", cn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet DS = new DataSet();

da.Fill(DS, "JobInfo");

Open in new window

LVL 2
CipherISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
I would guess that the default database for the user you are using is not MSDB. So, you will need to fully-qualify the name of the SP.

Try:
SqlDataAdapter da = new SqlDataAdapter("msdb.dbo.uspGetJobInfo", cn);

Open in new window

CipherISAuthor Commented:
Didn't work.  I'm also using a similar connection string.  

Data Source=DBASEA;Initial Catalog=msdb;Persist Security Info=True;User ID=blahblahblah;Password=yadayadayada;"/>
kenwagersCommented:
Check the ownership of the proc - it might not be owned by 'dbo'.

Check this way:

use MSDB
select ss.name as OwnerName, sp.name as ProcName
from sys.procedures  sp
inner join sys.schemas ss on ss.schema_id = sp.schema_id
where sp.name = 'uspGetJobInfo'

Then, use carl tawn's code, substituting the owner name with the 'dbo'.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

CipherISAuthor Commented:
I am receiving the below error

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.procedures'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.schemas'.

When running the below script

use MSDB
select ss.name as OwnerName, sp.name as ProcName
from sys.procedures  sp
inner join sys.schemas ss on ss.schema_id = sp.schema_id
where sp.name = 'uspGetJobInfo'

kenwagersCommented:
Looks like SQL2000 - here's the syntax for that version to return the object owner:

use MSDB
select ss.name as OwnerName, sp.name as ProcName
from sysobjects  sp
inner join sysusers ss on ss.uid = sp.uid
where sp.name = 'uspGetJobInfo' and
xtype = 'P'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
I figured out the problem.  The stored procedure was not named properly.  Thanks for the help.  I will award points anyway.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.