I use this in a script window in SSMS:
master.dbo.xp_cmdshell 'bcp myDB.dbo.DBlist out C:myCSV1.txt
-c -t -Uusrtest -Pmypassword,'
I get this error:
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
any help pls
I have two servers A and B, both running SQL 7.0 SP3 on NT4 (SP6a). I need to run an hourly job on Server A that copies a file to Server B using xp_cmdshell. (I'm trying to set up log shipping where Server B will be a warm standby). As a test I tried running this cmd from my workstation (I have Sql Server installed on my workstation) and it worked: xp_cmdshell 'dir ServerBDmssql7ackup'. However, when I log in to Server A using sa account and attempt to run the same query, I get the error 'Access Denied'. The MSSQL service on Server A is running under the system account.
Anyone have any ideas?
Thanks in advance.
Hi all,
I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:
I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:
EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.
So, I changed the statement to point to dtexec.exe 32bits:
EXEC master.dbo.xp_cmdshell 'C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
But executing this statement it does not even run. The errors are:
'C:Program' is not recognized as an internal or external command
operable program or batch file
This was very wird to me, because this was very mentioned by the moderators.
So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.
Now my questions are:
1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?
2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.
Thanks in advance.
Marco Francisco (Portugal)