Create Stored Proceedure From Table Adapter Wizard VS 2008 using SQLExpress 2008

Hi All,

I am having a problem adding a stored procedure to my SQL 2005 Database by creating a new stored procedure from the Table Adapter.  I am using VS 2008 with SQLExpress 2008 installed.  

The error that I am getting is:  The wizard detected the following problems when configuring the Table Adapter Fill Details.  There was a problem with the SELECT stored procedure.  The stored procedure was not created.   Adding new stored procedure to the database failed.  The specified schema doingitmyway either does not exist or you do not have permission to use it.

I can create a stored procedure from the SQL DB but not from the Table adapter as I have with local db's.  Does anyone have any idea what this message is from and how to resolve the issue.

Thanks,

Jus
The query that I used to create the stored procedure from the Data Table Wizzard was simple:  

SELECT MaikeyID, MaikeyName
FROM Maikeys

Open in new window

Justice75Asked:
Who is Participating?
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.

RameshSCommented:
1. Get the CREATE SCRIPT from 'Preview SQL Script' dialog box and check if it is executed without any error in SQL Server management studio.  Delete the store procedure immediately if you are able to create so that you will not get any issues in further testing.

2. Also check if the account that you uase has dba role in the database.

3. Ensure that you have enabled remote connection for the SQL Server instance. To enable remore connect you need to use SQL Server Surface Area Configuration tool. Check this link to have more details.
http://support.microsoft.com/kb/914277
0

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
Justice75Author Commented:
Thank you for your response.. I am checking those suggestions now.  However, i wanted to let you know that I can add tables to the db.

Jus
0
Justice75Author Commented:
I completed the tasks.

I found that the script that the Table Adapter created was proceeding the table name with the login username to the database instead of dbo.  I replaced the login name with dbo and was able to add the stored procedure from the Management Studio.  It added the SPROC,  Do you have any idea how to correct this from the table adapter or webconfig file?

Thanks,

Jus


WAS:

USE [doglovers]
GO
/****** Object:  StoredProcedure [willywonka].[NewSelectCommand]    Script Date: 04/03/2010 21:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [willywonka].[NewSelectCommand]
AS
	SET NOCOUNT ON;
SELECT        Makeis_ID, Makeis_Name
FROM            dog_Makes



IS:

USE [doglovers]
GO
/****** Object:  StoredProcedure [dbo].[NewSelectCommand]    Script Date: 04/03/2010 21:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[NewSelectCommand]
AS
	SET NOCOUNT ON;
SELECT        Makeis_ID, Makeis_Name
FROM            dog_Makes

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

RameshSCommented:
If  dbo is not replaced with the loginname in Management Studio, Does it allow to create stored procedure. If not allowed, what error is throwing.
0
Justice75Author Commented:
Thanks.... No it will not create the stored procedure.  

The error:  Msg 2760, Level 16, State 1, Procedure NewSelectCommand, Line 5
The specified schema name "willywonka" either does not exist or you do not have permission to use it.


Thanks,

Jus
0
RameshSCommented:
You need to give db_ddladmin database role to the use that you are using to create stored procedure using table adapter. To give that access,

1. Connect to the SQL instance using SQL Server Management Studio
2. Under Security->Logins->Select the user login->Right click and select Properties. It will open Login Properties window.
3. Click User Mapping->select the checkbox agains the database in 'Users mapped to this login' section.
Also select the checkbox 'db_ddladmin' in the 'Database role membershipt for..' section. click Ok button

0
Justice75Author Commented:
ReamshS,

I am using Management Studio Express 2008.  I went to the database in question, selected the Security node however, I dont see a node that is labeled user login.  Please help!

Jus
0
RameshSCommented:
I mean under security tab, expand Logins-> select the name of the SQL Server account that you have used to create stored procedure in TableAdaptper.

To assign a SQL Server role to a Sql server account, you need to connect with database using another account which has appropriate access(such as an account with sysadmin server roles) in the server instance.
0
Justice75Author Commented:
I think that we are almost there however it  is still not working.  I think that I am doing something wrong or missing something.  It is still adding the user name to the beginning of the stored procedure.

I selected the database on the server
Selected the security tab
Selected the user name willywonka
Right clicked a selected properties
Checked db_ddladmin in the Owned Schema section

In addition there is no option to set mapping in SQL Server Management Studio Express 2008 using a SQL 2008 database.

Did I miss something?

Jus
0
RameshSCommented:
Refer the attached file.
pic1.gif
0
Justice75Author Commented:
Thank your for that drawing.  I dont get that window though at all..  I dont have logins available in this 2008.

Jus
0
Justice75Author Commented:
Are you using 2008 SQL Server Management on a remove server?

Jus
0
Justice75Author Commented:
This is a screenshot of the nodes that I have available.


screenshot-MSSMSExpress2008.gif
0
Justice75Author Commented:
I created a local db on my machine and can access the logins tab however I can not for the db on the remote computer with my hosting company.  
0
Justice75Author Commented:
RameshS:,

I found the rebooted and found the login tab. The problem is that Table adapter is still proceeding the stored procedure with the login name instead of dbo.

I followed your stepr exactly.  Please help.

Jus
0
Justice75Author Commented:
I have followed this exactly to your specifications.  To no avail.  VS 2008 is still placing the username (login name) to the stored procedure.  

Jus
0
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
C#

From novice to tech pro — start learning today.