Execute Xp_cmdshell From Store Procedure (called From Aspx)

May 21, 2004

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:


"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.


MSSQLServer and SQLServerAgent Services


Act as part of the Operating System.


Increase Quotas.


Replace a process level token.


Log on as a batch job."





The above quote was from the following link:


http://support.microsoft.com/default.aspx?scid=kb;en-us;264155





The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:


------------------------------------------------------------


EXEC master..xp_cmdshell 'dir d:BT_importDatasales_option_price_report.csv'





Thanks for your help


ehx5

View 1 Replies


ADVERTISEMENT

Calling Store Procedure From Aspx

Jun 7, 2004

hi , I would appreicate your help, I have created a store procedure in MS sql server 2000 to be called from aspx page.
The store procedure will have multiple functions:
checking if a file exist in c:data
if file exist, it will use zip file command line to unzip the file and then run DTS package to upload the database.

The issue I am having is when I run the store procedure from MS query analyzer,I get a confirmation that the whole procedure ran successfully, but when I call the same procedure from aspx, althought the store procedure ran successfully, but I did not get any confirmation to the user in the aspx page to notify the user that the process successed.... here is my code in where I think is not working,
CREATE PROCEDURE p_on_demand_dts_sales_option_price_report_3_ftest
@id int , @msg_output varchar(28) output
as
declare @varcmd varchar(255),@FileExist int
select @varcmd = null
select @varcmd ='dir :Data_on_demandsales_option_price_report.csv' -- check if file exist
EXEC @FileExist = master..xp_cmdshell @varcmd
set @msg_output = 'The file exists'
Return 44

+++ The above store procedure use command line to check if the csv file exist, its suppose will return a output parameter and a return # BUT it did not. To my surprise, when I modify the above store procedure like this, it work
-----
CREATE PROCEDURE p_on_demand_dts_sales_option_price_report_3_ftest
@id int , @msg_output varchar(28) output
as
set @msg_output = 'The file exists'
Return 44

The second procedure does not do anything, except take input parameter and return a message output and return, this work.

since the second procedure works, it means that the code in aspx is correct, but I have no clue why it does not return output parameter and return # in the first procedure.

I would really appreciate anyone who could help.
thanks

ehx5

View 4 Replies View Related

Store Procedure Called - Timeout

Sep 12, 2005

Hi All,I have a report ASP.NET page that allow users to run a report by clicking a buttion to call a store procedure to generate the report, however, the store procedure is taking a few minutes to return the data, thus I got the 'timeout' error message on my page. How do I extend the time on my page?Thanks

View 3 Replies View Related

How Can I Post Back A Statement From A Store Procedure To The .aspx Page

Oct 17, 2005

Hi all,Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)My store procedure as follow:CREATE PROC NewAcctType(@acctType VARCHAR(20))ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN  PRINT 'The account type is already exist'  RETURN END
 BEGIN TRANSACTION  INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType)
  --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction  IF @@error <> 0 OR @@rowcount <> 1   BEGIN    ROLLBACK TRANSACTION    PRINT 'Insertion failure on AcctTypeCatalog table.'    RETURN   END  ELSE    BEGIN    COMMIT TRANSACTION   ENDENDThanks for all your replies

View 10 Replies View Related

Calling Eventcreate Command From Xp_cmdshell Store Procedure

Feb 15, 2008



Hi,

I want log event in event logger using xp_cmdshell store procedure.
I am calling eventcreate dos command.


xp_cmdshell 'C:> eventcreate /S \servername /L Application /T Success /SO Application Name /ID 1754 /D €œDescription of event€? '

I am getting error....


Incorrect syntax near 'eventcreate' /S \206.245.13.94' /L Application' /T Error' /SO WIPError'/ID 123'/D "Testing"'.


please help me.

thanks,

Chetan S. Raut.

View 5 Replies View Related

How To Access A .asp Page Or .aspx Page From A Store Procedure In Sql Server 2000

Sep 21, 2006

Hi, I wanted to know that can we access a webpagefrom a store procedure in sql server 2000 like we run a exe file from sql server. Bye.

View 1 Replies View Related

Execute Store Procedure

Mar 18, 2002

Hi!!!

i have a problem. I want to execute a sp from a DTS package but it doesn't work correctly. If i execute it from the query analyzer it work fine.
I have configured merge replication with pull subscription on the data base.

If someone can help me i will apreciate.

View 2 Replies View Related

Wont Execute Store Procedure With Parameter

Oct 4, 2007

Hi
Am trying execute a store procedure with a date parameter than simply get back ever record after this todays date. It wont except the value i give. I can just do it in the store procedure as it will passed other values later on.
It works fine if I take the parameter out, of both store procedure and code. It must be a syntax thing but im stuck sorry --- the error i get is: Incorrect syntax near 'GetAppointmentSessions'.
here is my code: ' build calendar for appointment sessions
Dim Today As Date = Date.Now
Dim ConnStr As String = WebConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Dim Conn As New SqlConnection(ConnStr)
Conn.Open()

Dim cmd As New SqlCommand("GetAppointmentSessions", Conn)
cmd.Parameters.Add("InputDate", SqlDbType.DateTime).Value = CType(Today, DateTime)
Dim adapter As New SqlDataAdapter(cmd)



Dim dt As New DataTable

adapter.Fill(dt)

Dim row As DataRow
 Here is the SQL:ALTER procedure [dbo].[GetAppointmentSessions]

@InputDate Datetime
AS


SELECT TOP (5) uidAppointmentSession, dtmDate,
(SELECT strRoomName
FROM tblRooms
WHERE (uidRoom = tblAppointmentSessions.fkRoom)) AS Room,
(SELECT strName
FROM tblHMResources
WHERE (uidHMResources = tblAppointmentSessions.fkHMResource)) AS Clinician,
dtmStartBusinessHours, dtmEndBusinessHours
FROM tblAppointmentSessions

Where
dtmDate > @InputDate

Order By dtmDate  

View 3 Replies View Related

Insert Into Table Execute Store Procedure

Mar 26, 2015

I have a INSERT INTO where i retunr the result from a store procedure. But I want to only insert the data if the row not already exist. How can i do that? (See Where xxxxxxxxxxxx).

I can't use a function as i store data in a temporary table in the store procedure.

--Get Generated Times
INSERT INTO @GeneratedTimes(
ResourceId ,
DateFrom ,
DateTo )
EXEC dbo.P_GenerateTimes @ApplicationId , @EventId , @FromDate , @ToDate , @WeekScheduleId , @FromTimeToBook , @ToTimeToBook
WHERE xxxxxxxxxxxxxxxxxx

View 1 Replies View Related

Store Procedure Fails In Execute SQL Task

Mar 19, 2008

I have a Execute SQL Task that calls a stored procedure. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. It has a date parameter, but hard coding the date parameter does not work either. I checked the permissions on the Stored Procedure and SQL Agent, which are ok. When I change BypassPrepare to True, the query will execute and insert the rows to the required table. If someone has an explanation for this, I would appreciate some sql enlightenment. Thanks.

Dan

View 6 Replies View Related

Will A Store Procedure Execute Faster Than Regular Select ?

Nov 7, 2003

Hello,

Lets say I have a SP that return 1000 records,

do I get any better speed if doing it on a SP instead of just SELECT without an SP ?

if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?


(Why should I use SP if not passing any parameters ?)

View 7 Replies View Related

Which ConnectionType It's More Performance? ADO.NET Or OLEDB For Execute A Store Procedure In A SQL Task?

Nov 20, 2007

Hi, I'd like to know which is the best choice to execute a store procedure that execute transformations from table to files using bcp command, 2 millions rows everyday (i'ts a Data Warehouse database). So in the sql task in connectionType I had choiced ADO.NET is the best option?
Thanks

View 10 Replies View Related

Fail To Execute Store Procedure In OLD DB Source In Data Flow

Jun 20, 2006

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.

View 13 Replies View Related

Execute Store Procedure In Informix From Sql Task Integration Services 2005

Dec 21, 2007



hi,
i need execute a store procedure of Informix from sql server integration services.
i use driver IBM informix provider 3.2 and have linked server Informix and use sql task for execute the procedure
it's posible??

View 2 Replies View Related

Execute DTS Package At Aspx Code Behind

Mar 26, 2008

Hi all,
I am excuting DTS package from code behind and the table is created in the SQL database. However, the data in my excel sheet is not transferred over and Im not sure where the problem lies. Below is my code:
  pkgLocation = @"C:/Program Files/test.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation,null);
pkgResults = pkg.Execute(); Any advice is much appreciated.

View 4 Replies View Related

Execute A *.sql File From One Aspx Page

Mar 12, 2004

Hi,
like subject, is it possible?

bye

View 3 Replies View Related

Post Execute Method Not Being Called - LockReadWriteVariables

Jul 22, 2007

I have a strange bug when trying to run SSIS. I get the message in the output window "

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. " in a script component but the Post Execute method is not being called.

I have placed a firewarning as the first statement to try and debug that the procedure is being called but to no avail as it is not being output

Has anyone come across this kind of issue before or have any suggestion on how to proceed ?



After a long period of time I then got the following messge displayed



at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser90.GetVariables(IDTSVariables90& ppVariables)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.LockReadWriteVariables(String readWriteVariables)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()



Within the package I have two script components one that uses a readwrite variable to set the variable to be a date in the first script tasks PostExecute and then the second component uses the date variable in its PostExecute method. Running the package seemd to imply that the first postexecute write t the variable is somehow locking out the next component from reading the variable.

View 3 Replies View Related

Store The Result Of Xp_cmdshell In A Table

Sep 3, 2004

hai..

is it possible to store the result the following statement in a table

execute master..xp_cmdshell 'dir'

when i execute this in QA i get the result. but i want to store it in a table

thnks in advance

with regards
Sudar

View 6 Replies View Related

How To Grant Execute On Xp_cmdshell??

Apr 2, 2002

Can anyone help me please.
Healp please.
Does anybody know how to make xp cmdshell runnable for users other than Admin. It should be possible to grant execute to others, but i can't figure out how.

This is what i found:

Be aware that when you grant execute permission for xp_cmdshell to users, the users will be able to execute any operating-system command at the Windows NT command shell that the account running SQL Server (typically local system) has privilege to execute.

To restrict xp_cmdshell access to users who have administrator permission on the Windows NT-based computer where SQL Server is running, use SQL Setup or SQL Enterprise Manager to set the server options, selecting the "xp_cmdshell - Impersonates Client" option. With this option selected, only users who have connected to SQL Server via a trusted connection and are members of the local Administrators group on that computer are allowed to use xp_cmdshell. The commands run by xp_cmdshell continue to execute in the server's security context.

View 1 Replies View Related

Execute Xp_cmdshell And Other SA Storedproc

Mar 8, 2004

Hi all,

I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)

Pls tell how to do this, it is quite urgent.

View 1 Replies View Related

Permissions For Non Sa User To Execute Xp_cmdShell

Mar 6, 2008

Hi,    I want to execute BCP in Query Analyser in SQL Server 2005 Express for that i surf on net and find that i should execute BCP under xp_cmdShell, That works good for addministritative account on SQL. But i want the working will be done by a non administrative account or non 'sa' user.How can i assign a non sa User permissions to execute xp_cmdShell? or just tell me any other alternative way to run BCP in Query Analyser or code behined. thanx 

View 1 Replies View Related

Failing To Execute Command Using Xp_CmdShell

Sep 28, 2001

Hi all,

In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command,
operable program or batch file."
This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full..
waiting for reply

View 2 Replies View Related

Xp_cmdshell Execute Rights (2005)

Sep 11, 2006

I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?

I solved it by checking "Control server" under "Properties" > "Securables"
for the login, but I don't actually want this login to have full control.

(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)

View 3 Replies View Related

Execute SSIS Package From SP Without Xp_cmdshell?

Nov 12, 2006

A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.

One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.

Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?

Thanks, Dave

View 1 Replies View Related

Execute SSIS Package From Xp_cmdshell?

Mar 19, 2008

How to execute SSIS package from CMD or using xp_cmdshell?
SSIS package is saved in the folder of the integration services Db.

Thanks in advance.

View 2 Replies View Related

Xp_cmdshell - Need To Execute External Program Asynchonous

Oct 21, 2004

hi,

i need to start an external program (vb.net) from within a trigger. via xp_cmdshell everything works fine except that the execution of the trigger waits for the vb-program to finish.

now i tried a workaround with a "starter application" which only calls the vb.net program (asynchronous) an ends.

this works fine from a dos prompt (command prompt returns immediately and vb.net task is running). but when i call the starter app from the trigger with xp_cmdshell, the behavior is the same as before: execution does not continue until the vb.net task ends.

any other ideas that could solve my problem? submitting an agent-job is not fine for me because i need to supply parameters to the called program and maybe i need call it more than once at the same time.

thanks
thomas

View 2 Replies View Related

Can't Execute Master.dbo.xp_cmdshell Throught A Variable.

Dec 5, 2007



Hi all.

I've previous posted a message with error building the expression of the dtexec.exe string. But now, the problem is other, and I've decided to create other threat.

Here is what I'm doing:
I've a SP that receives every parameter so I can build my dtexec.exe statement to be executed by a variable with something like this exec @rc = master.dbo.xp_cmdshell @CMD


Till now I have no problem executing it, not from a variable, but just with the statement itself, like this:
exec master.dbo.xp_cmdshell 'C:PROGRA~2MICROS~290DTSBINNDTEXEC.EXE /SQ PACKAGE /SET "Package.Variables[PERIOD].Value";20070101'.

But now, I have this SP that builds the string into a variable and then I just want to execute it throught the variable like this: exec @rc = master.dbo.xp_cmdshell @CMD.

When I make a SELECT @CMD just before the exec @rc = master.dbo.xp_cmdshell @CMD, so it prints the builded string, copy it and pasted to exec master.dbo.xp_cmdshell 'copied string' it works fine. But the step of exec @rc = master.dbo.xp_cmdshell @CMD returns the error:
The filename, directory name, or volume label syntax is incorrect.

Why is this, if I copy the generated string and execute it by the other way it works fine, but executing the generated string throught the variable it gives me that error?

View 4 Replies View Related

SQL Insert In Aspx.vb File (or Share Variable With .aspx Page)

Mar 14, 2008

Hi,I have a form page with an insert like this:<asp:SqlDataSource ID="addProductDataSource" runat="server"    InsertCommand="INSERT INTO test(        title1,        firstName1,        lastName1,        dateOfBirth    ) VALUES (        @title1,        @firstName1,        @lastName1        @dateOfBirth    )"     ConnectionString="<%$ ConnectionStrings:yourQuoteCentreConnectionString %>">        <InsertParameters>            <asp:ControlParameter ControlID="dateOfBirth" Name="dateOfBirth" Type="DateTime" />            <asp:ControlParameter ControlID="title1" Name="title1" PropertyName="Text" />            <asp:ControlParameter ControlID="firstName1" Name="firstName1" PropertyName="Text" />            <asp:ControlParameter ControlID="lastName1" Name="lastName1" PropertyName="Text" />        </InsertParameters></asp:SqlDataSource> In default.aspx.vb I have:Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick    'CREATE DATETIME FROM USERS BIRTH DATE        Dim dateOfBirth As New System.DateTime(applicantYear.SelectedValue, applicantMonth.SelectedValue, applicantMonth.SelectedValue, 0, 0, 0)    'INSERT TO DATABASE        addProductDataSource.Insert()End SubI would like to reference the variable dateOfBirth created in default.aspx.vb in the insert query on the page, or ideally move the insert query into the aspx.vb page.  Would this make more sense to do?Thanks,Paul

View 6 Replies View Related

Best Method For Using FTP Command Xp_cmdshell Or Execute Process Task

Dec 8, 2005

Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.


---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,

View 1 Replies View Related

Other Way To Execute The Xp_cmdshell Command On Remote Databases From Local?

Nov 14, 2007

Hi all,


Criteria:

We connect to the remote database servers through the network from loca by using Query Analyzer.
Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data.
But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer
We do not know what happened but i think due to network updates this command is not able to execute...


For ex:
Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute


Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?


Note : we are able to execute this command on remote Query Analyzer but not from local QA

Any suggestions would be very very helpful to me?


Thanks in advance,



View 6 Replies View Related

Stored Procedure To ASPX

Apr 28, 2004

Hello,

I have a stored procedure that is used to create a DataDictionary for the database. Basically, the sproc generates HTML and you can then run it from Query Analyzer by Saving to a file.

What I want to do is to run this from an aspx page.

The sproc uses PRINT to create the text.

Like this.


PRINT '<html>'
PRINT '<head>'
PRINT ' <title>SQL Server Data Dictionary Report - ' + @strDatabase + ' </title>'
PRINT '</head>'
PRINT '<body bgcolor="#FFFFFF" text="#000000" link="#008000" vlink="#008000">'
PRINT '<a name="Top"></a>'
PRINT '<div align="left">'


Is it possible to get the text that is generated from this sproc?

What do I need to do to get the text that has been created and display it on a page?

Thanks a lot.

View 2 Replies View Related

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

Aug 7, 2006

Hi,I am having
some trouble copying data over my workgroup network from my Windows
2003 Server Machine (machineA with SQL SERVER 2005) to one of my
network Machine's drive(MachineB).Here is the T-SQL code that I am trying to execute:EXEC xp_cmdshell 'copy D:Datafile.txt  \MachineBDocuments'Whenever
I tried to execute the above piece of code, I get the error message
"Access is denied", but if I try to copy the file from the Command
Prompt (cmd.exe) with the copy command, the file copies fine over the
network.I have already searched over the internet and I found
out that loads of people have the same issue, and they were suggested
something like this:"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."Well
it sounds plausible, but I don't know what are the exact steps to do
this. How do I know which user is running the MSSQL Server service? Are
they referring to the user which I use to connect to my SQL Server
Database engine throuhg the SQL Server Management Studio?Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.Here
are some details of the user that I use to login. I generally login
into my Windows 2003 Server machine with user called 'User1' and I use
the same 'User1' to connect to SQL Server through the Management Studio
Screen.Should I create a user called 'User1' on my MachineB(Destination Machine)?I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.Thank you very much once again.

View 11 Replies View Related

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

Aug 7, 2006

Hi,

I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:

EXEC xp_cmdshell 'copy D:Datafile.txt \MachineBDocuments'

Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.

I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."

Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.

Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?

I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.

Thank you very much once again.

View 18 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved