.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

create procedure [User_Name].sp_myprocedure. Error since I use dbo schema

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
I am developing with VS2010. When I generate a store procedure (DATASET>RIGHT CLICK ADD>SELECT>NEW STORE PROCEDURE) for a data table of a dataset from VS2010, the enviroment generates this: IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectQuery' AND user_name(uid) = 'DOMAIN\USER-LOGIN')  DROP PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery GO CREATE PROCEDURE [DOMAIN\USER-LOGIN'].SelectQuery AS  SET NOCOUNT ON; SELECT contract_id, product_id, contract_description FROM dbo.TContracts GO The creation of the procedure ends with erros since my schema is dbo. So far, I work around copying and pasting the store procedure changing the schema and starting again from the beginning: DATASET>RIGHT CLICK ADD>SELECT>FROM EXISTING STORE PROCEDURE. Is there any way to configure VS2100 to generate the scripts avoinding to prefix the supposed schema of my user. Many thanks.

View Complete Post

More Related Resource Links

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

create user wizard login error message dont show in a message box?




i am using create user wizard and capturing other information within content template when a new user

registers. Some of the textboxes are binded to required field validators.


there is a validation control on the page and ShowMessage box is True.

If they dont complete some of the text boxes then the message box pops Up with the error message.

It does not however include information errors like "User already exists" or Email address already exists

from the create user wizard membership  UserName and Password Textboxes


is it possilbe to hook all of these up so I get one message box with all errors including membership ones?




Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



Metadata: Create a Database Schema Repository with Meta Data Services in SQL Server 2000


SQL Server 2000 Meta Data Services is a repository technology that stores and manages metadata for SQL Server. Instead of building database schemas over and over, Meta Data Services allows you to freeze an entire schema for use in other projects. You can also use these schemas for training, testing, or debugging. In this article, the authors will review the various components of Meta Data Services and show how it can be programmed using a Visual Basic client, XML, and XSLT. They will also show you how to manage and manipulate your metadata by generating a simple database schema using a SQL Server repository.

Alok Mehta and Ricardo Rodriguez

MSDN Magazine May 2003

Create My Site for FBA Users Error


Hi Guys,

I am trying to create mysite for FBA Users programatically (FYIP: I have done all the configurations for FBA in SSP and mysite host and I am able to create mysite from UI for FBA Users). The problem exists only when I am creating MySite programatically!!!










Create User Wizard and Loginview error


 When i place the create user wizard control inside the Loginview Anonymous Template, the Create User Wizard gives an error inside the C# file. Here is a snippet

Home.aspx Web Form

 <asp:LoginView ID="LoginView2" runat="server">
        <asp:CreateUserWizard ID="CreateUserWizard1" CreateUserButtonType="Image" 
        CreateUserButtonImageUrl="~/images/signup.png" cssclass="createuser" 
        runat="server" OnCreatedUser="CreateUserWizard1_CreatedUser" 
           <TextBoxStyle CssClass="Ctextbox" />
            <LabelStyle cssclass="lbl" />
                <asp:CreateUserWizardStep ID="CreateUserWizardStep1" runat="server" />
                <asp:CompleteWizardStep   ID="CompleteWizardStep1" runat="server" />
        news here

Here is the C# Code with the error 


Error Executing CLR Stored Procedure "Item has already been added. Key in dictionary"

Hello, I'm consuming a web service through assemblies in a Sql Server 2005 database. The client was made with VB.net 2005. Everything was fine in the deploy phase but once I compile the assembly generated in my sql server database and try to execute the CLR stored procedure defined in the assembly it crashes throwing the following error: Error: There was an error generating the XML document. Inner Exception: Item has already been added. Key in dictionary: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Key being added: 'SqlCifin.InfoComercial.ParametrosConsultaDTO' Where SqlCifin is the name of the assembly, InfoComercial is the web reference namespace and ParametrosConsultaDTO is a complex type defined in the WSDL to encapsulate the request parameters. I tried almost everything but nothing seems to work: Already checked the enviroment variables and . I would appreciate any help you can provide me.  PD: I'm using WSE 3.0. Thanks, Andres Diab.

Windows 7 CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 26

Hi I have migrated to a new computer using Windows 7, 6 gig memory, I7 chip (old machine had xp) and have installed Visual Studio 2008 and  SQL Server 2008 R2.  I get CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262) when I try to create a new database.  Other posts (example:http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/28fee0ed-c7e2-40df-8f79-f513c9848f09/) work for Vista but I cannot find how to grant permissions.  Run as administrator (vista fix) does not work. I have admin privaleges on my machine.  There are no other accounts.    What do I need to do to update privaleges? Thanks and have a nice day.    

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

Get the error 'The Workstation service has not been started' when trying to create a new content d

I am running SharePoint on a standalone machine, and the workstation service is up and running. Restarting the service makes no difference. Rebooting the machine hasn't helped either. No errors are being written to the event log.

how to create XML Schema Collection for SQL 2005

I am generating below schema at run time in C#, Is there any way to create a XML Schema in SQL 2005 for this run time generated schema??? <?xml version="1.0" encoding="utf-16"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Address"> <xs:complexType> <xs:sequence> <xs:element name="Name" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>

Cant create new database / CREATE DATABASE permission denied in database master (error 262)

   I am using SQL express and Visual web developer on windows Vista. When I try to create a new database the following message appears.   CREATE DATABASE permission denied in database master (error 262) I log on to my computer as an administrator. Help appreciated  Prontonet      

Need to create a stored procedure on a db if that db exists..

IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

Need to create a update procedure

I have to update 2 records in a table, sounds simple, but i need to do this after the first record is updated, then using the data from that record, look up the other record thats in the table to update it with bits of the first records data. What it boils down to is that we inherited a database, that wasnt designed all that great..so we are looking to update the 2nd record based on data within the 1st record. How can i build a one time update, to run against the entire table, and it needs to determine what the other similar record is then update that one with the 1st data.Here is a sample of what the table data looks like:t_id s_id due_date date_completed 331724 82043 7/21/2009 NULL 349084 82043 10/21/2009 7/21/2010 378073 82043 1/21/2010 7/2/2010 394205 82043 4/21/2010 7/14/2010 413299 82043 7/21/2010 7/23/2010 432701 82043 10/21/2010 7/23/2010 341201 87188 7/21/2009 9/4/2009 349123 87188 10/21/2009 12/7/2009 380062 87188 1/21/2010 7/22/2010 394382 87188 4/21/2010 3/11/2010 413354 87188 7/21/2010 6/11/2010 432710 87188 10/21/2010 7/23/2010 So what i need to do is in the above example is update t_id 331724 with the date_completed from 341201. But since there is nothing to link them together in this table, how can the update be written so that t

Schema Specific Procedure Calls

Hi , I have two schemas Say S1 and S2 . I have two procedures inside each of this schema say S1.P1 and S1.P2 for S1 and S2.P1 and S2.P2 for S2 . Is it possible to call S1.P2 from S1.P1 ? . it is possible to do this like EXEC [S1].P2 , but Wat I want to know is to 1) call EXEC statement directly without [S1] prefix and execute the target Procedure which belong to the same schema of the calling Procedure ?. Currently wat I am facing is when I try to call EXEC P1 it looks for Default Schema (dbo.P1) and throwing a "procedure not found" error . 2)Is there a way to get the current schema name inside  the SP while the  SP is executing ?

Accessing table in DBO schema from Stored Procedure in other Schema

I am experiencing a strange issue related to schema qualification at a customer. No matter what we've tried we cannot recreate the issue internally. I am hoping someone can help. We create a Stored Procedure on a schema called "Import". In this stored procedure, we have numerous references to tables on the "dbo" schema, but we have not qualified them (I know this is not best practice, but it's legacy code and we're not going to change it right now). This has been used at other customers without issue. This one new customer is getting an issue where it does not seem to find the dbo table when trying to create the stored procedure. If we take the query out of a stored proceure and just run it as a T-SQL script, it works. So, the code below works select * from Import.Dirsync a join Inventory i on a.Audit_InventoryID = i.InventoryID But this does not. It complains that the column "InventoryID" on the table "Inventory" does not exist. create procedure Import.Test as select * from Import.Dirsync a join Inventory i on a.Audit_InventoryID = i.InventoryID My understanding is that any object on the dbo schema will be resolved. Again, this is working on all other servers with our databases. Any suggestions? Thanks Craig Bryden - Please mark correct answers

want to create common Stored procedure

Hello, I have more than 20 tables, for this i want to create a common Stored procedure which fetches data by id Column So I tried like this,but it doesnt work ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID] -- Add the parameters for the stored procedure here @TableName nvarchar(100), @ColumnName nvarchar(100), @ColumnVal nvarchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @ExecQuery nvarchar(100) SET @ExecQuery =@TableName -- Insert statements for procedure here select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal exec (@ExecQuery) END please help me out Regards,
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend