Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.

 Any help on this would be appreciated.

View 1 Replies


SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

Stored Procedure In Database X, Executes Stored Procedure In Database Y, Wrapped In Transaction?

Jul 20, 2005

Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?

View 1 Replies View Related

Stored Procedure And Transaction

Jun 22, 2006

I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!

View 2 Replies View Related

Stored Procedure And Transaction

Nov 10, 2006

I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back)    Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean        Dim bSuccess As Boolean = False        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("spNewGBEntry", MyConnection)        Dim fBeginTransCalled As Boolean = False        Dim myTrans As SqlTransaction        Try            MyConnection.Open()            myTrans = MyConnection.BeginTransaction()            cmd.CommandType = Data.CommandType.StoredProcedure            cmd.Transaction = myTrans            fBeginTransCalled = True            Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner)            Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner)            cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender))            cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text))            cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate))            cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner))            cmd.ExecuteNonQuery()            myTrans.Commit()
            If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then                bSuccess = True                fBeginTransCalled = False            End If        Catch ex As Exception            GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message)        Finally            If fBeginTransCalled Then                Try                    myTrans.Rollback()                Catch e As System.Exception                    GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message)                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 1 Replies View Related

Transaction On Stored Procedure

Oct 17, 2007

 i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure.  my question is, does it lock the table?  actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done.  do i still need to use tablockx to manually lock the table or does the transaction handles the locking?
please help

View 2 Replies View Related

Transaction Through Stored Procedure

Jul 20, 2005

i have to update two tables from ASP pages with same data but i wantthat both of them should be updated at one time. If either of them isnot updated then my transaction should roll back.I want this thing tobe in a stored procedure. so that i have to write an execute statementonly on the ASP page and pass the parameters.Looking forward for ur replyDEEPAK

View 2 Replies View Related

HELP! Stored Procedure And Transaction Problems

Aug 21, 2006

I have a stored procedure I want to use in a transaction...I have no idea where the problem is, since I dont receive an error...but tblMessages and tblUsersAndMessages are not updated..(hence something is wrong ;)) might be the sp or just my's all the data:STORED PROCEDUREset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[spNewMessage]@MessageID int,@UserIDSender uniqueidentifier,@MessageTitle nvarchar(50),@MessageContent text,@MessageType int,@UserID uniqueidentifier
ASBegin Set NoCount on DECLARE @WhateverID INT 
INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType)
INSERT INTO tblUsersAndMessages(MessageID,UserID)VALUES (@WhateverID,@UserID)
TABLE DEFINITIONtblUsersAndMessages                                            allow nullsMessageID   int                     falseUserID           uniqueidentifier falseNew              bit                  false               *default set to ((1)) 
tblMessages                                               allow nullsMessageID       int                     false         *PKUserIDSender    uniqueidentifier falseMessageTitle       nvarchar(50)     trueMessageContent text                  trueSentDateTime    datetime           false      * default set to (getdate())MessageType       int                  false
THE CODE   Public Shared Function SendMessage(ByVal SenderName As String, ByVal To As String), ByVal MessageTitle As String, ByVal MessageContent As String, ByVal MessageType As String) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("spNewMessage", MyConnection)        Dim fBeginTransCalled As Boolean = False        Dim myTrans As SqlTransaction        Try            MyConnection.Open()            myTrans = MyConnection.BeginTransaction()            cmd.CommandType = Data.CommandType.StoredProcedure            cmd.Transaction = myTrans            fBeginTransCalled = True
            Dim UserIDSender As Guid = UserFunctions.GetUserID(SenderName)            Dim UserIDReceiver As Guid = UserFunctions.GetUserID(To)
            'create message            cmd.Parameters.Add(New SqlParameter("@UserIDSender", UserIDSender))            cmd.Parameters.Add(New SqlParameter("@MessageTitle", MessageTitle))            cmd.Parameters.Add(New SqlParameter("@MessageContent", MessageContent))            cmd.Parameters.Add(New SqlParameter("@MessageType", CInt(MessageType)))            'userid receiver            cmd.Parameters.Add(New SqlParameter("@UserID", UserIDReceiver))
            cmd.ExecuteNonQuery()            myTrans.Commit()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception        Finally            If fBeginTransCalled Then                Try                    myTrans.Rollback()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 3 Replies View Related

Using Multiple Transaction In Stored Procedure?

Oct 15, 2014

I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?

USE [recruit]
/****** Object: StoredProcedure [dbo].[Import] Script Date: 10/15/2014 17:13:11 ******/
ALTER Proc [dbo].[Import]
@CustId int


View 2 Replies View Related

Rollback Transaction To Previous Stored Procedure?

May 6, 2007

Hi all, I have a program that needs to delete records, then re-insert new records to a table. But I need to rollback the transaction IF the insert is not success (error occured). The delete and insert are in 2 difference stored procedure (which have rollback transaction) that calling from 1 stored procedure. My problem is that if Insert is not successful, but the records already deleted previously. How can we rollback the delete transaction when insert is not successful?
Note: if possible, I don't want to delete the records AFTER the insert is successful, or create a temp table to stored the deleted records 
create stored procedure combine_sp
       call delete_sp -- have rollback transaction in the delete_sp
      -- what to do if following has error occured, but we already deleted the records above?
       call insert_sp -- have rollback transaction in the insert_sp
Thanks a lot.

View 3 Replies View Related

SQL 2012 :: Rollback Transaction In Nested Stored Procedure?

Nov 24, 2014

create proc proc1 (@param1 int)


begin try
declare @param2 int
begin transaction
exec proc2 @param2
commit transaction
end try
begin catch
if @@trancount > 0
rollback transaction
end catch

i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

View 3 Replies View Related

SQL Server 2012 :: Run Stored Procedure Without Writing To Transaction Log?

Feb 26, 2015

Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.

The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.

Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?

View 6 Replies View Related

How To Create A Second Independent Transaction Inside A CLR Stored Procedure?

Nov 29, 2005

I use the context connection for the "normal" work in the CLR procedure.

View 7 Replies View Related

Insert Stored Procedure With Error Check And Transaction Function

Jul 21, 2004

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?


My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],

if not exists (select studentid
from student
where studentid = @studentId)
print 'studentid is not a valid id'
return -1

if not exists (select contactId
from contact
where contactId = @contactId)
print 'contactid is not a valid id'
return -1
begin transaction

insert into contract

/*Error Check */
if @@error !=0 or @@rowcount !=1
rollback transaction
print ‘Insert is failed’
return -1
print ’New contract has been added’

commit transaction
return 0

View 1 Replies View Related

How To Manage Stored Procedure Transaction Involving Update In Several Tables

Jul 22, 2004

I am running a vba procedure ( adp file ) that executes successively 5 stored procedures . however it happens that the execution breaks at the middle of the code thus giving a situation where only 2 tables among 5 are updated.

Is it any solution to rollback transactions update already done before
the code breaks due to error ?

I was thinking about combining all stored proc on a big one and use
Begin transaction - commit transaction and rollback transaction ... however i am not sure wheter updates involving several tables can be handled on one transaction.

Any advise highly appreciated !

View 2 Replies View Related

Linked Server Exec Stored Procdure Results In Transaction Context In Use By Another Session.

Nov 3, 2005

I am using sql 2005 beta

View 23 Replies View Related

Why Is It Called Stored Procedure Instead Of Stored Sets?

Jul 23, 2005

Since RDMBS and its language SQL is set-based would it make more senseto call a given stored process "Stored Sets" instead of currenttheorically misleading Stored Procedure, as a measure to prodprogrammers to think along the line of sets instead of procedure?

View 4 Replies View Related

SqlDataSource And Stored Procedure Not Getting Called

Feb 23, 2007

Im using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ?  Ive got a Sql 2005 trace window open and NO sql statements are coming through  "ds" runat="server" ConnectionString="&lt;%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">

"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />


View 2 Replies View Related

Update Stored Procedure Not Working When Called From C#

Jul 11, 2007

OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work:
 1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record.
So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results.
Here is my code (some parts left out for brevity):1 int result = 0;
2 if (!String.IsNullOrEmpty(icaoCode))
3 {
4 icaoCode = icaoCode.Trim().ToUpper();
5 try
6 {
7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName);
8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName;
9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName);
10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
11 cmd.Connection = mConnection; // connection has been opened already, not shown here
12 cmd.CommandType = CommandType.StoredProcedure;
13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1
14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value;
16 }
17 catch (Exception ex)
18 {
19 result = -1;
20 }
21 }

 And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName]
2 -- Add the parameters for the stored procedure here
3 @Id bigint = null,-- Airport Id, OR
4 @Icao char(4) = null,-- ICAO code
5 @AirportName nvarchar(50)
6 AS
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
12 -- Parameter checking
15 RETURN -1;-- Did not specify which record to change
16 END
17 -- Get Id if not known given the ICAO code
20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao);
21 --PRINT @id
24 RETURN -2;-- No airport found with that ICAO Id
25 END
26 END
27 -- Update record
28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id;
30 END

 As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).

View 2 Replies View Related

How To Check When A Stored Procedure Was Last Called/executed

Jul 23, 2005

HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.

View 7 Replies View Related

How To Get The Output Parameter From An Internally Called Stored Procedure???

May 29, 2005

Hi all,i have a problem and couldnt find anything even close  to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane

View 8 Replies View Related

Gettings Warnings Sent From A Stored Procedure Called With Jdbc

Dec 6, 2007

When running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure?

the code used is below,

the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings()
warning raised in stored proc with

Code Block

RAISERROR ('Error', 16, 1)with nowait;

there are no results for the stored procedure, I am just wanting to get the warnings

Code Block


Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........);

CallableStatement s = con.prepareCall("{call procedure_name}");


//running in seprate thread

SQLWarning warn = s.getWarnings();
while (m.running) {
if(warn == null) {
warn = s.getWarnings();
if(warn != null)
warn = warn.getNextWarning();

}the code is not complete, but should show what is happening

it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server.

it is retreiving warning if the statement is a raiseerror instead of a call to the proc

Code Block

CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;");

this thread is quite related, but doesnt offer a working solution
Getting messages sent while JDBC Driver calls stored procedure

any help much appreciated,


View 1 Replies View Related

Debugging A CLR Stored Procedure That Is Being Called From An SSIS Package

Mar 3, 2008

I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.

View 4 Replies View Related

Dynamic Security Stored Procedure Repeatedly Called

Jan 26, 2007

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?

View 20 Replies View Related

DB Engine :: Can Find A Record Of Stored Procedure Being Called?

Jul 15, 2015

I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.

Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005

USE YourDb;

SELECT qt.[text] AS [SP Name],
qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = DB_ID()
AND objectid = OBJECT_ID('YourProc')

The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?

View 3 Replies View Related

Debugging A CLR Stored Procedure That Is Being Called From An SSIS Package

Mar 3, 2008

I need help debugging a CLR stored procedure that is being called from an SSIS package. I can debug the procedure itself from within Visual Studio by using "Step into stored procedure" from Server Explorer, but really need to debug it as it is being called from SSIS.

View 3 Replies View Related

Can A Stored Procedure Called From An Inline Table-Valued Function

Oct 5, 2006


I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View 4 Replies View Related

Identifying Results Sets When Stored Procedure Called Multiple Times

Oct 18, 2005

I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!

View 3 Replies View Related

Question On Internal Activation Stored Procedure Security Context

Jun 25, 2006

CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";


I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

View 13 Replies View Related

Stored Procedure : How To Output Parent Child Etc Data

Mar 9, 2006

Q: How to Output Parent Child Data using 2, 3 or more related tables.

say you have 3 related tables where the Bold HouseID is the one to many relationship




Loop For each HouseID

step through the database
first get the ParentID from the Houses table
return that data

then get the related from the house owners table
return that data

then get the related correspondents from the House Correspondent table
return that data

end loop

ie return the date like below


HouseID | Name | add1
1 House1 abc Address
1 Mr abc Mr abc Ower Address
1 Mr abc Mr abc Correspondent Address

2 House2 def Address
2 Mr def Mr def Ower Address
2 Mr def Mr def Correspondent Address

3 House3 ghi Address
3 Mr ghi Mr ghi Ower Address

I hope ive explained well enough

View 5 Replies View Related

Analysis :: Stored Procedure Ratio To Parent Truncating Data

Jun 11, 2015

I'm trying to use the Ratio to Parent sproc - [URL] .... The problem is that the stored proc appears to be pegged at 4 decimal places (which translates to ##.##%). This is losing precision and isn't adding up to 100% when the ratios are summed individually.

Can this be fixed within SSAS or will i need to modify the assp code? .NET decimals are meant to automagically scale, so not sure why it's coming out as (5,4) but my .net coding knowledge is fairly poor.I get the correct behaviour if hardcode a ratio to parent MDX calc (the resulting ratio has as many decimal points as are required)

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?

View 1 Replies View Related

Copyrights 2005-15, All rights reserved