January 2, 2013 at 10:25 am
Hello All,
I am trying to configure a new server so that I can call dtexec via xp_cmdshell
Here is how I am attempting to call dtexec.
DECLARE @PackageString varchar(600)
DECLARE @ReturnCode int
SET @PackageString = 'dtexec /sq Test /ser MyServer'
EXEC @ReturnCode = xp_cmdshell @PackageString
The error I am getting is:
Description: The file name "\\server\Folder1\Folder2\Test.txt" specified in the connection was not valid.
The package should create a test file in that folder.
I have created a proxy account and used sp_xp_cmdshell_proxy_account
CREATE LOGIN MyDomain\myproxy FROM WINDOWS;
EXEC sp_xp_cmdshell_proxy_account 'MyDomain\myproxy','strongpass'
I then added myproxy as a user in the master database and gave the account execute on xp_cmdshell.
I gave the myproxy user modify/read/write permissions on 'folder1' in my path.
Still the same error.
If I use SSMS to to connect to integration services and then navigate to stored packages I can run the package manually without error.
Any troubleshooting suggestions?
Thanks
January 2, 2013 at 10:33 am
Chrissy321 (1/2/2013)
Hello All,I am trying to configure a new server so that I can call dtexec via xp_cmdshell
Here is how I am attempting to call dtexec.
DECLARE @PackageString varchar(600)
DECLARE @ReturnCode int
SET @PackageString = 'dtexec /sq Test /ser MyServer'
EXEC @ReturnCode = xp_cmdshell @PackageString
The error I am getting is:
Description: The file name "\\server\Folder1\Folder2\Test.txt" specified in the connection was not valid.
The package should create a test file in that folder.
I have created a proxy account and used sp_xp_cmdshell_proxy_account
CREATE LOGIN MyDomain\myproxy FROM WINDOWS;
EXEC sp_xp_cmdshell_proxy_account 'MyDomain\myproxy','strongpass'
I then added myproxy as a user in the master database and gave the account execute on xp_cmdshell.
I gave the myproxy user modify/read/write permissions on 'folder1' in my path.
Still the same error.
If I use SSMS to to connect to integration services and then navigate to stored packages I can run the package manually without error.
Any troubleshooting suggestions?
Thanks
dont the files that DTEXEC runs need to have the Dtsx extension and I think you also have to put the fiel name in double quotes, though I may be wrong.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 2, 2013 at 10:49 am
Chrissy321 (1/2/2013)
The error I am getting is:
Description: The file name "\\server\Folder1\Folder2\Test.txt" specified in the connection was not valid.
based on the error it looks like a permissions issue to the folder. I have never used sp_xp_cmdshell_proxy_account but you can verify whether the proxy account is being user by running this statement
exec xp_cmdshell 'echo %username%'
January 2, 2013 at 10:54 am
This returns two records, one is NULL and the other is the SQL Server server account.
January 2, 2013 at 10:56 am
Chrissy321 (1/2/2013)
This returns two records, one is NULL and the other is the SQL Server server account.
In that case it doesnt look like your xp_cmdshell proxy account is being used. If you add read/write permission to the folder to the SQL Server Service account it should work.
January 2, 2013 at 10:56 am
>>dont the files that DTEXEC runs need to have the Dtsx extension and I think you also have to put the fiel name in double quotes, though I may be wrong.
I believe the syntax is correct. It works on other servers. In this case I am calling a package stored on the server not a package stored as a file.
January 2, 2013 at 11:02 am
>> If you add read/write permission to the folder to the SQL Server Service account it should work
That is what I tried first which didn't work leading me down the proxy path.
I re-added the SQL Server Service account and gave it modify/read/write.
Same error.
January 2, 2013 at 11:07 am
January 2, 2013 at 11:10 am
Works fine. xp_cmdshell can read the contents of that directory.
Seems to be something specific to calling dtexec...
January 2, 2013 at 11:14 am
does the dtexec command work if you manually execute it in command prompt? taking xp_cmdshell out of the equation.
also will this create a file
exec xp_cmdshell 'dir \\server\Folder1\Folder2 > \\server\Folder1\Folder2\test.txt'
January 2, 2013 at 11:19 am
It does work at the commend prompt directly on the server.
dtexec /sq Test /ser MyServer
Thanks for your help on this!
January 2, 2013 at 11:24 am
Getting this error
'gt' is not recognized as an internal or external command.
I can copy a file successfully.
exec xp_cmdshell 'COPY ............test.txt...test3.txt
January 2, 2013 at 11:29 am
Try running the DTexec command in the command prompt but open the command prompt in the context of the SQL server Service account. On windows 2003/xp you can right-click on command prompt and choose run as, on windows 2008/7 ctrl+shift+right-click on command prompt and choose run as different user. When asked for credentials enter username and password of SQL Server Service account.
January 2, 2013 at 12:53 pm
Could not load package ".." because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user.....). The SQL statement that was issued has failed.
There are two dtexec executables...
E:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
E:\Program Files\Microsoft SQL Server\100\DTS\Binn
Let me try calling each one separately...
January 2, 2013 at 1:04 pm
Same result when I call the 32 or 64 bit executable.
I'll continue to research the error.....
Could not load package ".." because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user.....). The SQL statement that was issued has failed.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply