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
ADVERTISEMENT
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
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
Jun 10, 2004
Hello everyone.
I'm currently trying to figure out a good way to keep my Local and Remote Databases in sync. Whenever I make changes to one or the other I spend a lot of time manually adjusting the other to match. I want to be able to set up an update page that accesses my Remote database and Local database and Updates the two accordingly.
I really don't know where to start with this one. I'm not sure at all as to how to simultaneously connect to two different databases, on two different servers, and Udate from one another. Does anyone know any good articles to get me started?
Thanks ahead for your replies.
-Alec
View 1 Replies
View Related
Nov 21, 2006
Hi all,
I would be very glad if someone can suggest me what techniques I should use in the following scenario:
I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.
Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:
DB1.users, DB1.orders,... etc are managed by "webapplication"
DB1.products ... are not managed by "webapplication" : in fact only used to read from
DB2.products, DB2.customers, .... etc are managed by "winapplication"
DB2.orders,....are partially managed by "winapplication"
Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :
- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)
- the products data is only (at the moment) added, edited and deleted on the local server
I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?
Any suggestions are appreciated!!
O.
View 1 Replies
View Related
Feb 23, 2006
Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:
cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment
I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.
SSIS:
I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.
Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP
View 7 Replies
View Related
Jul 20, 2005
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"'
View 3 Replies
View Related
Feb 23, 2004
Hello,
how may use xp_cmdshell to call winzip with the command,
i wrote this but it doesn't work ;
declare @cmd varchar(2000)
select @cmd = 'c:program_fileswinzipWzunzip.exe c:AttatchmentsTEST_PJ.zip c:AttatchmentsTEST_PJ.zip'
exec master..xp_cmdshell @cmd
View 3 Replies
View Related
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
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
Nov 26, 2002
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.
Thanks!
View 4 Replies
View Related
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
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
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
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
Jul 20, 2005
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
View 1 Replies
View Related
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
May 30, 2002
I want to set up remote backup device on "serverCentral" for 50 MSDE SQL2k servers so that they could use one backup to create(restore) any new customer databases. This way, I could keep one resource with many coming updating on procs and triggers...
I could create remote backup device with serverCentralackupackupdeviceName...
The problem is the remote device is "access denied" when I issued a "restore headeronly from backupDeviceName" sql query.
Neither I could issue xp_cmdshell 'dir serverCentralackup' --"Access Denied" from server49
Though I could dir serverCentralackup*.* from server49 command prompt.
It looks security permission is OK otherwise I could not dir from command line.
thanks for the help
David
View 1 Replies
View Related
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
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
Nov 10, 2006
I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
View 7 Replies
View Related
Jun 20, 2007
I am using XP_CMDSHELL to run 2 Windows commands (from a SQL 2005 SP2 server) that access files on a remote server (called webserver). The two commands are:
1. cscript.exe \webserverwwwrootEur_SaveProductImages.vbs
2. dir \webserverwwwroot
Up until very recently this worked fine. Now I am getting Access Is Denied errors:
For #1:
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
CScript Error: Loading script "\webserverwwwrootEur_SaveProductImages.vbs" failed (Access is denied. ).
For #2:
Access is denied.
This happens if the above commands are run through a job, or directly with Query Analyzer as SA.
I understand that XP_CMDSHELL commands are run in the security context of the SQL Server startup account. The SQL Server startup account is called SQLServer and this account exists on both webserver and the database server. On both servers the username & password are identical and the user is in the local Administrators group. The two servers are in a workgroup.
Things I have tried (with no effect):
1. Restarted the servers
2. Deleted and recreated the SQLServer login on both servers
3. Changed the startup account for SQL Server through the SQL Server Configuration Manager to another account and back to SQLServer.
The most revealing tests are these:
1. If I log into the database server myself using the SQLServer account I can run the above commands in a Command Prompt with no problem
2. I installed SQL 2005 Express SP2 on webserver using the same startup account (SQLServer). If I run the same XP_CMDSHELL commands as above but accessing files on the database server, the commands work!
So, an XP_CMDSHELL command run under SQL Express using the local SQLServer account to access a remote server works, but an XP_CMDSHELL command run under SQL Server using a local SQLServer account (that is supposedly identical to the same named account on the other server) does not work !!?!?!?
It is almost as if XP_CMDSHELL commands run under SQL Server are NOT using the SQLServer startup account, but using some other weaker account. How can I tell if this is true?
How can I get this back working (as it was a week ago). The only thing I can think that happened on the servers to cause this is the installation of Windows Updates.
Thanks
View 7 Replies
View Related
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
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 7 Replies
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 2 Replies
View Related
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
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
Jan 29, 2008
I need the file created by my BCP QUERYOUT command I'm executing from XP_CmdShell in Transact-SQL to have a specific OWNER. I've tried using the sp_xp_cmdshell_proxy_account to setup the correct owner. The owner is always SERVERNAMEAdministrators.
thanks,
jim
View 1 Replies
View Related
Jun 19, 2007
I have a remote database on a server.
I am an authenticated user to a database on the server.
I want to get the data in the database to my local computer
I don't have a shell (or any other way) access to the directory
related to sql server
so what can i do to get a copy of the data?
View 1 Replies
View Related
Nov 9, 2007
I had both SQL server 2000 & 2005 on my machine. I recently un-installed 2000 for some reason, and now the SQL 2005 does not show local databases. It cannot update local server information or it doesnt let me local as a new server. How do i go about installing local databases in this situation?
Thanks
View 3 Replies
View Related
Jan 22, 2008
i have a local SQL Server Express 2005 on my local machine and i want my software to update from the SQL Expres to the SQL Server when the user click the save button to keep an image of the local database on the remote server without having to backup a file and restore it?
*** I am using C# and SQl Server 2005
View 4 Replies
View Related
May 6, 2007
I am new to programming in SQL and in VB. I use VWD to create web applications with a Hosted SQL database, locally though the databases are created in SQLExpress through Visual Web Developer.
I have used the SQL Publishing Wizard to upload my local data to the web sql server. But, I'd like to have the same local database as is on the web, which is being updated by users, both data and user / membership data but am not sure how to create an easy, or even programmatical way to update both databases periodically.
Thank you for your help.
Cjsteury
View 4 Replies
View Related
Jun 25, 2006
Hi All,
I am completely new to VS2005 and .net2.0 but I am learning fast! But one thing that I am struggling with is the deployment of my website, or to be more precise the deployment of the data that was created during development on my local machine.
I have used VS2005 to create a simple personal site. I have created a single user (myself) in an admin and friends role. So far so good. The site works great on my local machine, all the default photos provided with the site display correctly etc and I can log in OK.
My remote server (ie my web hosts) provide me with a separate SQL server on which to install my databases. I have created a database on this server and configured it so that it contains all the correct tables and schemas etc. I have modified my web config file to connect to this database and when I browse to my default.aspx page it displays in my browser fine...EXCEPT that none of the data ( ie the photos and user data) is in the database so therefore no pictures display and I can't log in!!
What I can't figure out is how do I get all the data from my local app_data folder into my new remote database!!?? I can't find anything that explains how to do this. Surely this is one of the most important operations of all since data is what .net2.0 is all about!
Thanks in anticipation
View 2 Replies
View Related
