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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

Need to execute ALTER DATABASE inside stored procedure

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

SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?

Thanks.




View Complete Post


More Related Resource Links

Execute SSIS package stored in Database - From Stored Procedure

  
Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Using sp_grantlogin inside a stored procedure that uses EXECUTE AS.

  

Hello to all you oh mighty of the mightiest programming gods!

I am a novice in need of help.

I have a stored procedure

ALTER

 

PROCEDURE [dbo].[vm_upd_user_rights]

@Username

nvarchar(40)

WITH

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.

Update Database Record using GridView and Stored Procedure

  
Hi, I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error. I can populate values from database fine but its produce error when i click on edit link button. Please see the code below. *********** ASP.net GridView Code I am using *****************<asp:GridView ID="GridView1" runat="server" DataKeyNames="RollID, EmpID" AutoGenerateColumns="False" Width="700px"> <Columns> <asp:TemplateField HeaderText="ApplicationID" Visible="false"> <ItemTemplate> <asp:Label ID="AppsID" runat="server" Visible="false"><%# Eval("RollID") %>'</asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="ReleaseID" Visible="false"> <ItemTemplate> <asp:Label ID="rlsID" runat="server" Visible="false"><%# Eval("EmpID") %></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Roll Name"> <ItemTemplate> <asp:Label ID="rlsVersion" runat="server"><%# Eval("Roll_Name")%></asp:Label> </ItemTemplate> <EditI

How to create a second independent transaction inside a CLR stored procedure?

  
I use the context connection for the "normal" work in the CLR procedure.But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.What is the best way to do this inside a CLR procedure?Thanx a lot

Managing TypedDataSets when there is a change in backend Table / Stored Procedure / Database schema

  
Hi All, I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.  I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain  The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again. Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

With Recompile and Execute as clause in SQL 2005 Stored Procedure

  

Hi,

I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.


SQL Command Or Stored Procedure in Execute SQL Task

  

Hi all,

Which is a better way to do in Execute SQL Task : Direct SQL input or Create a stored proc in database and then use that.

In my opinion, Stored Procedure is a better and recommended way for following reasons:

  • Cached execution plan
  • More secure
  • Centralized code
  • Code reuse

Please let me know if this is not the case with SSIS Execute SQL Task.

Thanks!


How to execute DELETE stored procedure programmatically (C#)

  

 I need to execute a stored procedure which is a simple delete (record) query. Programmaticaly, I want to pass in a parameter "ID1".

Assume I will pass in the ID1 parameter on a delete link click event froma GridView control. What would the code be to execute the existing stored procedure?

Also assume:

ID1 - the parameter and primary key of the source database for the record to be deleted

GridView1 - the GridView control

spDeleteRecord - the stored procedure needing the parameter ID1 and to be executed from C# code

Here's my start:

 
protected void GridView1_RowDeleting(object sender, EventArgs e)
{
TableCell cell = GridView1.Rows[e.RowIndex].Cells[1];
int ID1 = System.Convert.ToInt16(cell.Text);
??? - code here to call and execute parameter query
}

C# How to check prammatically if null value exists in database table (using stored procedure)?

  
Folks!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
UserID INT NULL,
Name NVARCHAR(50) NULL,
DateOfBirth DATETIME,
IsActive BIT,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID),
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO

INSERT INT

How to write Stored Procedure for Insert Data & Execute it in MS SQL?

  

How to write Stored Procedure for Insert Data & Execute it in MS SQL?


help with looping inside a stored procedure

  

I wander if its posible to do a loop inside a stored procedure.

 

Say I have a parameter @Quantity int and its value is 3

is there a way that I can loop like

for x = 1 to @Quantity

insertsomething in a table....

next x

all this inside a stored procedure

cheers

 


Database Tuning Advisor, Stored Procedure with datetime parameters, syntax error

  

I receive these error(s) in the Tuning Log. I have several stored procedures and they all use a datetime parameter.

E000    exec <some sproc name> ''2009-01-01 00:00:00:000'',''2010-09-30 00:00:00:000'',N'',N'',NULL,NULL,NULL,NULL,NULL,NULL         1    [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2009'.

I am using SQL 2005. Any suggestions?

Thanks, Reece


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

Stored procedure to do defragment entire database

  
Stored procedure to do defragment entire database and to keep the report in seperate table?

How to execute ssis package from stored procedure

  
how to excute ssis package from stored procedure and get the parameters back from ssis into the stored procedure.
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