EXECUTE AS question: February 2012

Sunday, February 26, 2012

Execute Process Task; How to use user variable as an argument

I am trying to call a executable that takes an argument. I am using an "execute process task" and have declared a user string variable "file_name" (c:\file.txt)

How do I use this variable so that the executable will see it as an argument.

You can use expressions to define the path and parameters of you executable... Then you don't pass the variable but construct the "command line" with an expression including the parameter (which is the value of your variable)...

Execute Process task: Unexpected exit code

In Executing "E:\EmailDelivery.exe" "EP12A 4" at "", The process exit code was "-532459699" while the expected was "0".
It is your process, so why do you ask this forum why it returned this exit code? The Execute Process task just compares the the process exit code with expected that you set in task properties, and reports an error if the exit code does not match.|||Thanks Michael. Turns out it had to do with the package being executed via a windows service under the local system account and not having the correct credentials for the db connection.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task Error on copy

Hi,

I am trying to run a very simple copy command from an execute process task:

copy O:\myFolder\myFile*.txt D:\myFolder

I have a working directory set also.

However, there is an error icon on the task, and if I attempt to run it I get the following error:

Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:\myFolder\myFile*.txt D:\myFolder".

Error at Execute Process Task: There were errors during task validation.

This command works fine from the command line. What am I doing wrong here?

Thanks

O:\myFolder\myFile*.txt D:\myFolder

The above should go in the arguments parameter box if you're not doing that.|||

It seems that it's looking for an executable, like a .bat file. It doesn't seem to recognize the dos copy command.

for example:

Executable: copy

Arguments: o:\myFolder\myFile.txt D:\myFolder

but it still complains that copy is not an executable.

So, it seems I have to put this in a batch file, which seems dumb, or I need to use the File System Task, but I don't know how to specify which files to copy using this method.

|||Instead, try using the full path to xcopy

c:\windows\xcopy.exe|||

xcopy is not installed

where can one find just the "copy" command exe?

|||

sadie519590 wrote:

xcopy is not installed

where can one find just the "copy" command exe?

xcopy is not installed? You're kidding me? It's distributed with Windows.

There is no "copy.exe" as it's an internal command within "command.exe or cmd.exe."|||

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

|||

sadie519590 wrote:

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

You need to specify the full path to xcopy.exe in the Executable parameter..|||But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.|||

sadie519590 wrote:
But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.

I think the way to do this is to use a foreach loop to scan the source directory for the files you want to copy (you can use a wildcard there) and then have it populate some variables (source filename, etc...). Then, inside the foreach loop, you use a filesystem task to perform the operation for each file found by the foreach loop.

Does that make sense?|||Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?|||

sadie519590 wrote:
Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?

Sure thing! http://connect.microsoft.com/sqlserver/feedback

Execute process task depending on query result

Hi Guys,

I wonder if you can help with the following requirement.

I want to be able to conditionally execute an 'execute process task' depending on the result of a query. I have a table which I will select one record/row from upon each execution, this record has a char 1 'type' field which is the indicator for what process to then execute.

This should be quite a simple package and will be run every 60 seconds so needs to be as efficient as possible.

I am thinking I should go along the lines of using an Execute SQL task to select my row in to a result set, and using a series of precedence expressions to determine what process to execute. But im not really sure how..... Smile

I am a newbie to SSIS and 2005 in general so would appreciate any help you can provide

Chris

You can use your Execute SQL Task to store the column value to a variable. This post explains how to do so (it uses Excel as the data source, but the process is the same for a SQL source) http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1.

Drag a precedence constaint from the Execute SQL to the Execute Process. Double-click the precedence constraint, change the Evaluation option to Expression and Constraint, set Value to success, and set the Expression to @.your_variable=="1" (replace 1 with the appropriate value for this task). Continue adding Execute Process tasks and precedence constraints, altering the expression as appropriate.

Hope this helps.

|||

Thanks for your help!

All works

Execute Process Task Arguments

HI,

Is it possible to provide variables ( multiple variables ) in the arguments parameters of the Execute Process Task?

Thanks

Shafiq

You can use multiple command arguements in one task by using spaces to delimit arguements.

Thanks,
Loonysan

|||To use variables in the process arguments, use Expressions tab and define an expression for Arguments property. You can use multiple variables there.

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I've started playing around with SSIS Logs (OnTaskFailed etc..), but I can find no where to allow me to capture the output from a console app. In SQL 2000 DTS I could capture this by specifying an "output file" on the advanced tab of the job step definition. Is there something as straight forward as this in SSIS ?

I'd be grateful for a few pointers in the right direction.

One way would be to pipe the console output to a text file using appropriate calling syntax (myconsoleapp.exe >> myoutput.txt) , then read it in via a flat file source in a following step. of course the console output might need to be structured or parsed in a way that meets your needs for the further processing of the data, but it's one way.

Ken

|||

Thanks for the suggestions Ken. I see where you are going with it, but it's not that I need to further process the output from console app's, just log what they are producing. In fact, they do already produce a flat file which mirrors the console output it's just that I'd like the whole output from the package to be in one place .

Under SQL 2000, the job steps manage to allow you to easily capture both the SQL server messages for tasks along with the output from an application. It seems that the logging in 2005 is much more complete and varied, but makes this simple task more complex to accomplish. I'm sure it can be done, I just don't know the best avenue for investigation. My last thought was to try using the StandardOutputVariable property on the execute process task to capture the data to a string. I haven't persued this as it seemed a bit of a long shot, but that's where I'm at.

|||Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:
Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.
Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

I just tried setting the StandardOutputVariable and StandardErrorVariable properties, and now the task pops up a window each time the process is executed (it's in a loop), even though I've also set the WindowStyle property to Hidden. Is that to be expected?

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I'd be grateful for a few pointers in the right direction.

Ken

|||

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:
Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.
Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

Execute Process Task & FTP -- NEED HELP 911

Guys!

Need some help on to figure out some solution for my problem. I have Execute process task in my DTS to execute the BAT file to connect to FTP site using simple DOS commands. All, i need, after executing the BAT file using Execute process task, need to send an Email to the administrator about the status of execustion.

The problem occured here, I specifically, take out the password field in the BAT file to connect to the FTP site. In general, the batch file could not connect to FTP site, since i taken out the password field in the BAT file. And after that, in DTS package i tried executing the Execute process Task seperately, and all i can get the messages "command executed successfully".

Based upon the status of the execute process task, I am writing a "Not Successful" message in the table. From that, table i am using the stautus to send an Email to an Administrator.

So Folks please help me out, how can I sql server know the status of the Execute Process task status, whether is it Successful or not.

In my case, this execute process task simply says successful, een though the BAT file could execute the command.

Guys help me out and I am in a little presure to accomblish this ASAP.

Baski.Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Hey Bratt,

I totally understand your way of thought and appriciate it. Great, Since, the DTS which we wrote was completely huge. So any thoughts about error handlimg or pass the error code for Execute process task.

Is there anyway, can i able to check the status of the Execute process task as well as BAT file, that called inside the Execute Process task.

Thanks for your time Guys.

Thoughts are most welcome.

Baski.

Originally posted by Brett Kaiser
Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Check inside the bat file

f errorlevel 1 goto ERROR
if errorlevel 0 goto SUCCESS

:ERROR
echo transfer failed
goto ENDFTMS

:SUCCESS
echo transfer successful
goto ENDFTMS

:ENDFTMS
echo batch program complete|||Bratt,

Good to hear your thought on this. Well, I believe you aware that, the DOS BAT file is executing in FTP location. Also, is there any thought about, CHECKING THE FILE EXISTENCE IN THE FTP LOCATION USING BAT FILE.

Once, i figure out if the xyz.abc file is located in the FTP file, I can pass the parameter or return code as 0/1 to Execute process task to find whether the TASk is successful or not.

Make sence? GUYS IS THERE ANY THOUGHT TO CHECK THE FILE EXISTENCE IN THE FTP LOCATION & RETURN WITH CODE 0 OR 1 AFTER EXECUTING THE BAT FILE.

THANKS IN ADVANCE GUYS.|||Yeah, in a sproc

Delete From Ledger_Folder

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Select @.Command_String = @.FilePath + '\*.*'

-- Select @.Command_String

-- Insert Into Ledger_Folder exec master..xp_cmdshell @.Command_String

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- select * from ledger_folder

Delete From Ledger_Folder_Parsed

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 6
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 7
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- Get File For the 1st Month of a Quarter

If ( Select Count(*)
From Ledger_Folder_Parsed
Where Substring(File_Name,16,2)= @.Month1 And Substring(File_Name,11,4)= @.Proof_Year

) = 0
BEGIN
SELECT @.Error_Loc = 8
SELECT @.Error_Message = 'First Monthly File Not Found. Check Syntax for File Name. '
+ ' Syntax is: ' + @.Fn1 + '_yymmdd.txt'
SELECT @.Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END|||Hey Bratt,

Thanks a Lot and ofcourse, I may not be using the sproc for checking the file existence. Since, we have been trying to upload the file to a MAINFRAME BASED FTP SITE.

So it would be more welcome, if you provide some solution or step based on the DOS mode or command line statements.

Thanks in Advance.

Baski.|||You can also use the ActiveX Script task in dts to test for file existence using the fso object - this would be cleaner. Why don't you use the ftp task in dts rather than using a batch file ?|||The Problem is you may not be using the FTP task for upload or download function in Mainframe based FTP site. SO i have to use the DOS version of file existence as BAT file in Execute process task.

Thanks

Execute process task

I want to pass in a text file to execute process task.Is it possible?

You can pass the location of the file to a process. For example, in the execute process task, you can set the following properties:

Executable: notepad.exe

Arguments: abc.txt

WorkingDirectory: C:\temp

When you execute the task, notepad will attempt to open abc.txt from C:\temp folder.

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.

Execute proceduers from another Proceduer with error handling

I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

Regard JoelCheck the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

<joel.sjoo@.gmail.comwrote in message
news:1166184223.625115.296200@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?
>
I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
>
>
Regard Joel
>

|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

Even better is this check:

IF @.Returcode <0 OR @.@.error <0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(joel.sjoo@.gmail.com) writes:

Quote:

Originally Posted by

I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

I don't relly know what this is supposed to be, but note that the first
GO marks the end of USP_RUNJOB, so the call to SP_TEST2 is not part of
that procedure.

execute proc on stopping mssql server

How execute extended proc on stopping ms sql server (it mean before
stoping).
Is it possible to handle this?
Or is in system proc a'la sp_on_stop?

ThxIndrek Mgi (mkkk@.hotmail.com) writes:
> How execute extended proc on stopping ms sql server (it mean before
> stoping).
> Is it possible to handle this?
> Or is in system proc a'la sp_on_stop?

KILL on the spid in question is the only way I can think of. Short of
rebooting the server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try this,

--Email when database is started

CREATE procedure email

exec sp_sendSMTPmail 'test@.test.com.au', 'Database Rebooted',
@.cc='', @.BCC = '',
@.Importance=1,
@.Attachments='', @.HTMLFormat = 0,@.From =
'test@.test.com.au'

GO
exec sp_procoption N'email', N'startup', N'true'
GO

Thanks,

"Indrek Mgi" <mkkk@.hotmail.com> wrote in message
news:4215cb43_2@.news.estpak.ee...
> How execute extended proc on stopping ms sql server (it mean before
> stoping).
> Is it possible to handle this?
> Or is in system proc a'la sp_on_stop?
> Thx

execute problem

I whan to run this code, but it still doesn't work. :(
DECLARE @.MyText VARCHAR(1000)
SET @.MyText = 'SET IDENTITY_INSERT [Table1] ON ' + CHAR(13) + ' GO'
EXEC(@.MyText)
MSSQL doesn't like the "GO"
ThanksWhy do you need the GO?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

DECLARE @.mySQL99 VARCHAR(1000)
SET @.mySQL99 = 'SET IDENTITY_INSERT myTable99 ON INSERT INTO myTable99(Col1,Col2) SELECT 1,''A'''
+ ' SELECT * FROM myTable99'
SELECT @.mySQL99
EXEC(@.mySQL99)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And why would you do dynamic sql for this anyway?

Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!Shake things up a bit, and try:use test
GO
sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'
GO
create table t ( a integer )
GO
create procedure can_truncate as
truncate table t
RETURN
GO

grant execute on can_truncate to jmartinez-PatP|||I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @.@.VERSION:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)|||Reading through BOL, I found out this:

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?|||You need to run this code after granting rights:

sp_addrolemember 'db_ddladmin', 'jmartinez'|||But then I'm giving the user privileges to basically *destroy* my db..|||Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.|||You might use
Delete
From <TableName>|||You might use
Delete
From <TableName>

The question is about

what this procedure do is to truncate a table on which the user has no access

Execute premmition

Hello there
I've add new user to my sql server.
AS the default he got the public role.
I add db_datawriter and db_datareader to the user.
Now the user can enter data and read data but he can't execute store
procedures.
Is there a role for executing stored procedures?
if not is that mean that i have to create my owm role and pass stored proc
one by one and allow it for the role?
any help would be usefulRoy,
There is not a predefined role for stored procedures, so yes you will need
to create one.
Of course, you could write a little bit of code to generate a GRANT for each
stored procedure in the database to your general role. (Just need to
remember when you add stored procedures to do it again.)
Russell Fields
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...

quote:

> Hello there
> I've add new user to my sql server.
> AS the default he got the public role.
> I add db_datawriter and db_datareader to the user.
> Now the user can enter data and read data but he can't execute store
> procedures.
> Is there a role for executing stored procedures?
> if not is that mean that i have to create my owm role and pass stored proc
> one by one and allow it for the role?
> any help would be useful
>

|||Thankes russell
But i don't like to work hard for this
there is probebly system store procedure that add the execution premmition
for some role.
There is also option to know which procedure is has already grant execute so
the procedure i want to build will allow me to update the role
Can you help me on it?
any help would be useful
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...

quote:

> Roy,
> There is not a predefined role for stored procedures, so yes you will need
> to create one.
> Of course, you could write a little bit of code to generate a GRANT for

each

quote:

> stored procedure in the database to your general role. (Just need to
> remember when you add stored procedures to do it again.)
> Russell Fields
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
proc[QUOTE]
>

|||Here's a script like the one Russell alluded to. This will grant the
specified role execute permissions on all user stored procedures in the
current database:
DECLARE @.MyRole sysname
SET @.MyRole = 'MyRole'
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME(uid)) +
'.' +
QUOTENAME(name) +
' TO ' +
@.MyRole
FROM sysobjects
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

> Thankes russell
> But i don't like to work hard for this
> there is probebly system store procedure that add the execution premmition
> for some role.
> There is also option to know which procedure is has already grant execute

quote:

> the procedure i want to build will allow me to update the role
> Can you help me on it?
> any help would be useful
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
need[QUOTE]
> each
> proc
>

Execute phase Memory Error => dtsx finished: Canceled

Hi,
I have a data flow component that retrieves records from a table. There is about 3 million records. I do sorts on the records, then use a merge join transformation to join that sorted data with the dimension data. However, when I pump 10,000 records through this transformation it works fine. When I try 3 million I get this error:
DTS.Pipeline: Thread "WorkThread1" has exited with error code 0x8007000E. I think this is a memory error.

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

I downloaded the sort compoent from ivolva.com. Very, very easy to download and install. However, that component is cancelling my package. Any ideas? I sent some feed back to Oleg, I hope I hear back.|||I am not sure why it is happening. What message are you getting on the progress page? Ash seems to have tried it out too.

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

Thanks
Oleg|||I would try to go with the Lookup transform... The "problem" with the sort is that it's asynchronous... So it has to load all the records, sort it and send it out again... You don't have that problem with the lookup... You might run into other problems (i.e. the lookup stores everything in memory by default, you can switch that of but then it might be slow...), but I think it's a good alternative to try...|||I've downloaded the sort util as well but it crashes VS 2005 as soon as I try to add it in the toolbox. Has anyone else experienced this?|||

I have the same problem... could you post what you did to fix this?

Execute phase Memory Error

I have 1000 MB of memory 30 GB of hard drive space. I am also running this on a virtual server.

Option 1:
Don't use merge join transformations and use Lookup's instead?
Option 2:
Stop using virtual server and put it on a real box and and beef up the memory and hard drive.

What do you suggest?

This could well be related to Sort component. There is a known issue with Sort. Please have a look at the following 2 posts. May help you.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=21620

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=18421

In these entries there are links to Jamie's blog along with 3rd party providers who has got their own sort component which we can use within SSIS.

Thanks
Sutha

|||Thanks Sutha,

Thanks
Sutha|||

In the Output dialog box the error message reads:

SSIS package "LoadFct_Trans 1.dtsx" finished: Canceled.
Then everything stops.

|||I'll contact you and try to diagnose this problem.

I have the same problem... could you post what you did to fix this?

Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?

execute permissions aspnet sql server 2005

Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error

EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.

as far as im aware ive givne the ASPNET account the correct permissions

EXEC

sp_grantlogin [MachineName\ASPNET]

EXEC

sp_grantdbaccess [MachineName\ASPNET], [ASPNET]

** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.

Is there anything else I need to do ?

Try this:

GRANT EXEC on <storedproc> TO <machinename\user>

EXECUTE Permissions and Cross Database

I have an odd situation. Here are the details:
- I have three databases (A, B, C).
- I have a user that has EXECUTE and SELECT permissions on each
database.
- I have a stored procedure in A and B that does an update in C at one
point
- The stored procedure works fine from database A, but from database B,
it gives me the following UPDATE error: UPDATE permission denied on
object 'MyTable', database 'C', schema 'dbo'
- No dynamic SQL is used
- The database owners are the same as well as the table and stored
procedure owners
Can anyone help guide me on this?
Thanks,
MickyVerify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
for all databases involved.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||Yes, that is set at the server level to allow.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
> for all databases involved.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||Check the database property to allow cross database ownership chaining.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||It is set to false, but it is greyed out because of the server setting
I assume. They are set to compatability level 80 if that matters.
Also, they are all set to the same thing (chaining wise) which is what
stumps me (it works from Database A but not B)
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Check the database property to allow cross database ownership chaining.
>
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||It should be set to true. It can be set on the individual database level.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"MickyM" <javamick@.gmail.com> wrote in message
news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...
> It is set to false, but it is greyed out because of the server setting
> I assume. They are set to compatability level 80 if that matters.
> Also, they are all set to the same thing (chaining wise) which is what
> stumps me (it works from Database A but not B)
> Micky
> Arnie Rowland wrote:
>|||"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
>
Is the login executing the procedure the same in both cases? The login must
have access to database C.
David|||Yes, the login is the same in both cases.
Micky
David Browne wrote:
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>
> Is the login executing the procedure the same in both cases? The login mu
st
> have access to database C.
> David|||ok, this is resolved now. The bad part is I still don't have a clear
understanding of why the error was happening. I know it was related to
ownership chaining, but I don't know why. Here is what I did. I ran
this:
EXEC sys.sp_configure N'cross db ownership chaining', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
and then
EXEC sys.sp_configure N'cross db ownership chaining', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
So, basically I turned it off and then back on (at the server level).
Thanks for the help on this.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> It should be set to true. It can be set on the individual database level.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "MickyM" <javamick@.gmail.com> wrote in message
> news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...|||Hi
Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well
. You cann add them and grant any permissions that you want
"MickyM" <javamick@.gmail.com> wrote in message
news:1153160895.236692.9320@.75g2000cwc.googlegroups.com...
> Yes, the login is the same in both cases.
> Micky
> David Browne wrote:
>

EXECUTE permissions

I need to grant a user execute permissions on 1000 stored procedures. This
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:

> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:

> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

execute permission for stored procedures

Hello SQL Experts,

we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.

A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.

The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.

In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?

The Owner of sp and table is dbo.

Thanks for your replies!You can't enable the user to only have permission to some of the data in the table. What you would need to do is have a view, and a column by which you would differentiate between different users. There is little cost associated with a view, and you can make the view owned by the user, so no one else can use it, or no one else can use it by mistake.

IE. [user].[viewname]

Cheers,
-Kilka|||While you can restrict access to columns using permissions, I don't know of any way to restrict access to rows using permissions.

I think that Kilka101 has the right idea about using a view. You may be able to construct a single view that uses User_Id() or Suser_Sname(), or you may need to resort to separate views for each user.

Keep in mind that as you scale upward, this gets a lot more complicated to manage, especially if you introduce any "third tier" processing like an application server. For two-tier applications this isn't likely to be a problem, but as you grow it can become a real problem.

-PatP|||Thanks for your replies!

I thought about a workaround with views, too. But I'm rather sure to remember that before Windows 2003 and SQL Sp3 it has been sufficient to grant a user the right to execute a procedure which performs a select on a table without having the explicit right to select from the table.

For example:

create proc sp_showusers
as
select * from users
where name= 'abc'
go

grant execute on sp_showusers to [my_user]
go

Though 'my_user' doesn't have granted the select permission on table users, he's got all data from table users by executing the sp.

I suppose it has something to do with the stricter security settings coming up with Windows 2003. Anyway, there must be a way out of it... :confused:|||Yes, when you create a stored procedure, the statements in it are checked against the security permissions of the creator. Permission to execute the procedure can be given to users with different permissions, that can then execute the procedure even though it does things such as your example select that the user couldn't do directly. Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

-PatP|||Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

Oh... okay, I guess this is exactly the point! The stored procedure I got the problems with contains some dynamic SQL... :( So I have to think about views then. ;)

Thank you very much for your help! :)

Execute Permission for Database Role

There are about 300 stored procedures and we are trying to separate EXECUTE
permission for Readers and Writers. Here is the scenario:
1) Two Domain groups created named SQLWriters and SQLReaders consisting of
respective users.
2) Two SQL logins created named SQLWriters and SQLReaders using Domain
groups SQLWriters and SQLReaders respectively. SQLWriters are in
db_datareader and db_datawriter roles and SQLReaders are in db_datareader
role only.
3) Two database roles created (a) db_executor for SQLWriters (b)
db_executor_reader for SQLReaders.
(4) db_executor has execute permission on all the stored procedures that
have Select Statements and Insert, Update, Delete Statements.
(5) db_executor_reader has execute permission on the stored procedures that
only have Select statement in the stored procedure.
When NT domain users from SQLReaders group are executing stored procedure
with Insert statement in it where their role (db_executor_reader) does not
have execute permission, it is still getting executed and doing the insert
and update. Any idea what else may be required here. Thanks.I had to explicitly dely execute premission on the SP. For some reason it
gives execute by default to all the stored procedures to all users. I can't
find documentation on why it is doing that. Will investigate more maybe one
of the MVPs can shed some light on this.
But you can check the effictive premissions to check what each Group has
access to.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fraz" wrote:

> There are about 300 stored procedures and we are trying to separate EXECUT
E
> permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures tha
t
> only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.|||Fraz (Fraz@.discussions.microsoft.com) writes:
> There are about 300 stored procedures and we are trying to separate
> EXECUTE permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures
> that only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.
Since I don't see your database, or know which version of SQL Server you
have, it's sort of difficult to say what is wrong. But I would suspect
that you at some earlier point granted execute rights to public. You can
use sp_helprotect to examine this. It could also be that users are members
of other roles and get permission this way.
To test that you have the actual setup correct, create an empty database
and set up users, procedure and permissions, and test that that works.
Then you can examine what is wrong in the target database.
In the end, DENY as Mohit suggested may be the best way, as it also
protects you against future accidents.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute permission for all sprocs

I am looking to write a script that will grant a specified user execute
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig

Sunday, February 26, 2012

Execute Process Task; How to use user variable as an argument

Execute Process task: Unexpected exit code

Execute Process Task using StandardInputVariable

Execute Process Task using StandardInputVariable

Execute Process Task Error on copy

Execute process task depending on query result

Execute Process Task Arguments

Execute Process Task arguments

Execute Process Task / Console Output

Execute Process Task / Console Output

Execute Process Task & FTP -- NEED HELP 911

Execute process task

Execute Process Task

Execute Process Task

Execute procedure

Execute proceduers from another Proceduer with error handling

execute proc on stopping mssql server

execute problem

Execute privileges on sp

Execute premmition

Execute phase Memory Error => dtsx finished: Canceled

Execute phase Memory Error

Execute Permissions on 400 SPROCs

execute permissions aspnet sql server 2005

EXECUTE Permissions and Cross Database

EXECUTE permissions

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

execute permission for stored procedures

Execute Permission for Database Role

Execute permission for all sprocs

EXECUTE AS question

Blog Archive

About Me