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?
 
RameshSConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.