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


Post New Web Links

System Stored Procedure permission

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server
 

Hi,

I am not able to see the permission for the System stored procedure( like sp_add_job) and extended system procedure like xp_execresultset in SQL 2005.  I could see the permission in SQL 2000(right click->properties-> permission tab). 

In SQL 2005, I don't see the Properties when I right click except Modif,custom reports and refresh?

Where to check the permission for the system stored procedures?

Pls let me know.

 

Thanks.

 




View Complete Post


More Related Resource Links

Execute system stored procedure

  
This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

System and Extended stored procedure

  

Hello all.

I am trying to determine which of our applications makes use of system stored procedure or the extended stored procedures. How do I go about getting this information on my database. I use sql server 2000. 

*I have searched this forum but was unable to get help.

Thank you for all your responses.


Incorrect syntax near '%'. When trying to create/alter system stored procedure

  

Sql Server 2005 SP3

After finally figuring out that for whatever reason the sp [sys].[sp_refreshsqlmodule] was not loaded I found the procedure on another machine and tried to create it on the machine missing the sp. I get an error: Incorrect syntax near '%'. The error applies to 2 lines in the sp:

 

EXEC %%Object(MultiName = @name).LockMatchID

SQL Server 2005 Grant User Permission to Create Stored Procedure and Grant Execute Access to another

  

I'm trying to determine the minimal amount of access I can give my developers to be able to run create stored procedure scripts with an inclusion of the grant execute to the application user group.

We have been developing some interesting work arounds, but I think there must be an easier way to grant the access.

Grant Create Stored Procedure to user1 on a specific database

Grant Permission to Grant Execute permission for Stored Procedures on a specific Database to user1 for any user.

 


Stored Procedure stopped working in Visual Studio 2010; System.Data.SqlClient disabled?

  

Hi,

In Visual Studio 2010, I have a webform, with a button that attempts to manipulate a SQL table that has a lot of data (several years & archives of 12 people's emails: emailID, bodies, to, cc, bcc, date/time).  Problem has always been that with so many emails (500,000), I had to break up the manipulation jobs a bit, or VisualStudio/ASP.Net development server would freeze up running those SQL stored procedures.

So I determined, for each (.pst) email store, a unique series that was in each emailID - for instance, person A would have 00000195195xxxxx as their emailIDs, and person B would have 00000123456xxxxxx as their email IDs - and used those 30 [or so] identifying flags to recursively call a function that until yesterday was running my SQL stored procedures just fine. 

YEsterday, while that was running, for giggles, I rebuilt the index on my table, and freaked out the development server by accident.  I had to rollback the database using SQL Server Management Studio, or the database was "Suspect".

Now, my SQL Stored Procedure in that recursively called function won't run, despite the fact that the original SQL Query [that gets the identifying flags into a Datatable, and runs the sproc on each "row"] works just fine.

I think it's a setting, to protect me ... but can't

Stored Procedure stopped working in Visual Studio 2010; System.Data.SqlClient disabled?

  

Hi,

In Visual Studio 2010, I have a webform, with a button that attempts to manipulate a SQL table that has a lot of data (several years & archives of 12 people's emails: emailID, bodies, to, cc, bcc, date/time).  Problem has always been that with so many emails (500,000), I had to break up the manipulation jobs a bit, or VisualStudio/ASP.Net development server would freeze up running those SQL stored procedures.

So I determined, for each (.pst) email store, a unique series that was in each emailID - for instance, person A would have 00000195195xxxxx as their emailIDs, and person B would have 00000123456xxxxxx as their email IDs - and used those 30 [or so] identifying flags to recursively call a function that until yesterday was running my SQL stored procedures just fine. 

YEsterday, while that was running, for giggles, I rebuilt the index on my table, and freaked out the development server by accident.  I had to rollback the database using SQL Server Management Studio, or the database was "Suspect".

Now, my SQL Stored Procedure in that recursively called function won't run, despite the fact that the original SQL Query [that gets the identifying flags into a Datatable, and runs the sproc on each "row"] works just fine.

I think it's a setting, to protect me ... but can't

Send Email from SQL Server Express Using a CLR Stored Procedure

  
Problem
One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

Solution
If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions

  

I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

MyTable
ID (PK)
PersonID (FK)
LocationID (FK)
TotalItemsSold

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPerm

  

Good Day all,

Having an issue with an outside user accessing my IIS7 box. I do not have this problem when running the website from my host machine. I found this post: Http://forums.asp.net/t/1371394.aspx. I assure you that this is not a solution because I am not storing any of my files on a network share. 

What do you think my approach should be. 


I already have read rights to IIS user to my BIN folder. 


Thanks for the help. 


Creating A Stored Procedure Which Searches Team Names

  

Hi

I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.



Sort by gridview SortExpression parameter via Stored Procedure

  

I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                        <Columns>
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                <ItemTemplate>
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />
                            <

Stored procedure generator?

  

Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.


Create stored procedure from asp.net

  

Hi,

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.

 

 


Call SQL Stored Procedure Asyncronously

  

Hello,

I have a C# web page that calls a stored procedure. The page passes few parameters to the stored procedure and call it. The stored procedure does so many time consuming tasks on a huge number of database records but does not return any value.

Since the page is not expecting any return from the stored procedure, I want to execute the stored procedure asyncronously so that the user can continue working on the web page and other web pages while the stored procedure is running in the background. Also, I do not want the web server processes to be busy with the running stored procedure.

Any help, please.


Best regards,

Imran


Unable to select any stored procedure while creating TableAdapters in wizard

  

I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?


Problems connecting stored procedure to Crystal Reports

  

I am working on updating a reporting system that uses Crystal Reports.  All of the 250+ reports were created in CR 8 and I have been opening up the old files in Visual Studio 2005 and updating the database location.  All I have had to do with all connections to views and even a couple of stored procedures is create a new connection to the database and then update the old report's datasource location.  The reason I need to update the location is so that the .NET application will use the correct driver.  Everything has worked fine up until I got to one stored procedure.  The old version of the report works perfectly with the stored procedure, but when I try to run the report using VS 2005 I keep getting errors.

First I connected to the database using the Oracle OLE DB provider, then updated the location of the stored procedure in the report.  It then prompts me for the two parameter values for the stored procedure, both of which I keep as NULL so that the report parameters will be passed to the procedure.  Then I get the following error:

Query Engine Error: 'ADO Error Code: 0x
Source: OraOLEDB
Description: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'

The proce

Categories: 
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