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'
I have created a database and stored procedure there.I want to execute that store procedure having dot net in my front end,i.e i want to execute store procedure from .net.How can I do that.Pls help me.
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
I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong? My original version that calls a store procedure Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim mySqlCommand As New SqlCommand Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim myPart_id As String mySqlCommand.CommandType = CommandType.StoredProcedure mySqlCommand.CommandText = "getPartId" mySqlCommand.Connection = mySqlConnection mySqlCommand.Parameters.Add(New SqlParameter("@part_supplierserialnumber", part_supplierserialnumber)) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function My Store procedure create procedure getPartId@part_supplierserialnumber as nvarchar(50)as select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @part_supplierserialnumber order by part_datecreated desc
GO The new version I tried which happen the same thing Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim myPart_id As String Dim strSql As String = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc" Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function
I am calling Store Procedure from my C# Code and inside the SP, I am calling my SSIS Package.It is working fine. On my local because I have all rights to run xp_cmdshell COMMAND. Now I have to transfer this SP to QA and Prod and I don't have rights to run xp_cmdshell COMMAND.
Here is my SP.
declare @cmd varchar(1000) SET @ssispath = 'SSIS Package Path where my .dtsx package' set @ExcelF = 'Passing My source file name and full path'
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
I have is a .exe file developed on vb.net. I'm able to run it on QA as exec master..xp_cmdshell 'D:maxCustomMX_SyncAddressBook.exe, then if I run it using a SQL job it shows the error: Executed as user: CAPERSQLAdmin. The process could not be created for step 1 of job 0x9E75D06B9FC4474EB7503A1CCA0F9AAF (reason: The system cannot find the file specified). The step failed. Anyone familiary with sql jobs calling .exe file developed on vb.net. Your help would be appreaciated.
Can anyone give clues as to why the same copy command works inDOS and not in xp_command shell.I am an admin in both servers andin the database.The results indicate one file is copied but no file is foundat destination.Your input is appreciatedVincentocopy "D:Program FilesMicrosoft SQLServerMSSQLBACKUPQMISDEVack1.txt" "\corp156d$Program FilesMicrosoft SQLServerMSSQLBACKUPQMISDEV"works just fine butusemasterexec xp_cmdshell 'copy "D:Program FilesMicrosoft SQLServerMSSQLBACKUPQMISDEVack1.txt" "\corp156d$Program FilesMicrosoft SQLServerMSSQLBACKUPQMISDEV"'
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
I am in the process of writing a stored procedure that takes data from a table, within a cursor, and puts that data to the network server via the xp_cmdshell procedure. The xp_cmdshell command is at the end of the cursor in the SP.
I have worked with the admins on getting the proper permissions to execute this procedure and have tested it by running the procedure directly in Query Analyzer and by calling it in Query Analyzer successfully.
However when I try to call this procedure from another programming envirnoment, once the xp_cmdshell process is called, the procedure locks up and blocks the rest of the process, including the other software programming environment. When I look at the error log, it indicates that correct command, as a matter of fact I have copied the log file command into Query Analyzer and ran the procedure successfully. So for some reason, the statement is not being committed or there is some kind of threading issue I do not know how to address.
If anyone out there has any suggestions or has a simular problem in the past and knows how to address it, it would be greatly appreciated.
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,
I need to execute xp_cmdshell in a trigger and pass a command lineparameter to the .exe programi.e. I have a .exe program c:program filessavedata.exeIn the trigger I have a parameter @Id bigintI need to pass the parameter @Id to savedata.exeIn the trigger, I am tryingEXEC master..xp_cmdshell 'cmd.exe /C "c:program filessavedata.exe "'+ CAST(@Id as varchar)BUT this does not work.Can someone please help me with how to do this?Thanks,db
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
I am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
hi, I have a trigger on a table for insert, once there is new data into that table I want to run a nother store procedure by passing all input from inserted to the store procedure as input parameter. can I do that.
All, I am developing the data mart refresh task in SSIS. I wanted to call the package in command prompt. I need help. Can any one give any tips on it. Thanks and appricate in advance.
Hi! I try to find out how to write an tsql program that sends dmx-ddl to SSAS. this works: select * from openquery(bdjOLAP, 'select <col> from <modelname>.content') because it returns an resultset.
but how to something similar this (would fail, because no result set is returned): select * from openquery(bdjOLAP, 'create mining structure...')
We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate.
The following was inserted for the SQLCommand property:
However, when the Refresh button is pressed we are presented with the error below:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x8004E14 Description: "Invalid parameter number".
If we use SET instead of EXEC (e.g. SET ? = dbo.GetOrderlineStatus(@dt_required = ?, @dt_invoice = ?, @dt_despatch = ?, @ch_status = ?, @si_suffix = ?, @re_quantity = ?, @vc_invoice_id = ?, @vc_order_id = ?)) the following error is produced:
Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
1. (calls an oracle package) - call lpaarchive.pibrdg.setlastbridgeruntime(sysdate);
2. (selects rows) - select timestamp ,
pitag ,
rtlmp from LPAARCHIVE.I_PIBRDG_BUS5MINRTLMPS
where timestamp <= (sysdate + 0.002777778)
order by TIMESTAMP ASC; I need to execute these 2 statements together. I tried using Execute SQL task to call the package and then an OLEDB source that calls a variable with the select statement. But it does not retrieve any rows. It seems like the result of calling the package is used by the select statement to give the final rows. Could anyone please help me resolve this issue.
I've imported an SSIS package into Management Studio (2008 R2) and I've set up a SQL Server Agent job to call the package but it fails due to error code: 0xc00160aa.
As far as I can tell this is because it is unable to read the location of the package despite it being a file system location within Management studio. Also I can run the package manually within Management Studio, but when I try to call it via the job it fails.
I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..
'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.
I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I have a question regarding the extended SPC 'xp_cmdshell'.
Basically I want to determine the username and userdomain in a stored procedure and what I know is that you can get this information in a "DOS-Box" with 'Set username' or 'Set userdomain'.
But if I use the above mentioned extended stored procedure in the following way:exec master..xp_cmdshell 'set username'I don´t get any resultset.
Does anyone know why I get different results depending on the fact if I call the 'set'-command in a "DOS-Box" or with the appropriate stored procedure?
I have a stored procedure that creates an xml file. It executes a SELECTstatement with the FOR XML clause and then writes the xml file using bcp andxp_cmdshell. I am calling this procedure by passing it a parameter via ADO.I have configured the SQL Server Agent with a proxy account so non-SysAdmincan execute xp_cmdshell.I'm concerned about giving non-SysAdmins execute on xp_cmdshell. I'm alsoconcerned about having to maintain the password on my proxy account whenthat users' password changes.Is there a better, more secure way to generate this xml file.Thanks
Is there any way to allow a user to use the xp_cmdshell extendedstored procedure without giving that user execute permissions toxp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as thedbo) create a stored procedure called sp_send_err:CREATE PROCEDURE sp_send_err @CompID varchar(20) ASdeclare @strCMD varchar(255)select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + """ERROR!""', no_output"execute (@strCMD)GONow lest say I give "user1" execute permissions on sp_send_err, but nopermissions on xp_cmdshell. When I run sp_send_error I get thefollowing error:"EXECUTE permission denied on object xp_cmdshell, database master,owner dbo".Why doesn't this work? What else can I do?
