March 11, 2009 at 12:38 pm
Anyone up for a challenge?
Stored procedure that combines files outside of SQL and bulk inserts them into a SQL table. (cannot use SSIS because once I get this to work I must incorporate into ERP system)
-xp_cmdshell turned on
-xp_cmdshell given proper rights
-cmdexec proxy created with valid domain account
SQL user ID DBSBATCH, proxy user is DOMAIN\SQLAgentProxy, DBSBATCH has bulkadmin serverrole.
Here is the stored procedure, when it reaches the @bulkcmd that is when this error occurs:
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'domain\SQLAgentProxy'.
NULL
The password to the domain SQLAgentProxy account has been reset in active directory and also modified in the credentials window
Any avenues of advice would be greatly appreciated.
The stored procedure....
USE [DBSallc]
GO
CREATE PROCEDURE
[dbo].[HOPE_alloc_step3]
AS
DECLARE
@copy nvarchar(1000),
@bulkcmd nvarchar(1000),
@PJCT nvarchar(1000),
@PJLN nvarchar(1000),
@combine nvarchar(2000)
BEGIN
SET @copy = 'copy e:\Allocations\alloc_gl_load\journal.ojb e:\Allocations\alloc_gl_load\journal.txt'
SET @bulkcmd = 'BULK INSERT DBSallc.dbo.HOPE_ALLOC_JRNL
FROM ''e:\Allocations\alloc_gl_load\journal.txt''
WITH (FIRSTROW = 2, MAXERRORS = 1,
ERRORFILE = ''e:\Allocations\alloc_gl_load\bulkinsert.err'')'
SET @PJCT = 'bcp DBSallc.dbo.HOPE_PJCT_LOAD out e:\Allocations\alloc_gl_load\PJCTLOAD.txt -e e:\Allocations\alloc_gl_load\PJCTLOAD.err -c -T -V 80'
SET @PJLN = 'bcp DBSallc.dbo.HOPE_PJLN_LOAD out e:\Allocations\alloc_gl_load\PJLNLOAD.txt -e e:\Allocations\alloc_gl_load\PJLNLOAD.err -c -T -V 80'
SET @combine = 'copy e:\Allocations\alloc_gl_load\PJCTLOAD.txt + e:\Allocations\alloc_gl_load\PJLNLOAD.txt e:\Allocations\before_proj_jrnl_load\projload.txt /A'
/* Rename the Ledger Allocation output file from .ojb to .txt extension */
EXECUTE master.dbo.xp_cmdshell @copy
PRINT 'Rename and Copy complete'
/* Truncate working tables */
DELETE FROM DBSallc.dbo.HOPE_ALLOC_JRNL
DELETE FROM DBSallc.dbo.HOPE_PJCT_LOAD
DELETE FROM DBSallc.dbo.HOPE_PJLN_LOAD
PRINT 'Truncate tables complete'
/* Insert Ledger Allocation Output file into Alloc_jrnl table */
EXECUTE (@bulkcmd)
PRINT 'Bulk insert into table complete'
/* INSERT PJCT rows from Alloc_jrnl into PJCT_LOAD table */
INSERT INTO DBSallc.dbo.HOPE_PJCT_LOAD (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6,
FIELD7, FIELD8, FIELD9, FIELD10, FIELD11, FIELD12, FIELD13, FIELD14, FIELD15, FIELD16,
FIELD17, FIELD18, FIELD19, FIELD20)
SELECT 'PJCT' AS FIELD1,
'A' AS FIELD2,
'PHUSA' AS FIELD3,
a.jrnl_id AS FIELD4,
a.jrnl_seq_nbr AS FIELD5,
'B' AS FIELD6,
'N' AS FIELD7,
' ' AS FIELD8,
a.trans_curr_code AS FIELD9,
'Y' AS FIELD10,
'Y' AS FIELD11,
CONVERT (varchar(30), a.eff_date, 101) as FIELD12,
' ' AS FIELD13,
' ' AS FIELD14,
' ' AS FIELD15,
' ' AS FIELD16,
a.descp AS FIELD17,
' ' AS FIELD18,
'Y' AS FIELD19,
' ' AS FIELD20
FROM DBSallc.dbo.HOPE_ALLOC_JRNL AS a
WHERE a.jrnl_line_nbr = 1-- Insert uses jrnl_line_nbr to obtain unique jrnl IDs
PRINT 'PJCT rows insertion complete'
/* Insert PJLN rows from Alloc_jrnl into PJLN_LOAD table */
INSERT INTO DBSallc.dbo.HOPE_PJLN_LOAD (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6,
FIELD7, FIELD8, FIELD9, FIELD10, FIELD11, FIELD12, FIELD13, FIELD14, FIELD15, FIELD16,
FIELD17, FIELD18, FIELD19, FIELD20, FIELD21, FIELD22, FIELD23, FIELD24, FIELD25,
FIELD26, FIELD27, FIELD28, FIELD29, FIELD30, FIELD31)
SELECT
'PJLN' AS FIELD1,
'A' AS FIELD2,
'PHUSA' AS FIELD3,
a.jrnl_id AS FIELD4,
a.jrnl_seq_nbr AS FIELD5,
a.jrnl_line_nbr AS FIELD6,
a.dbs_proj_code AS FIELD7,
a.dbs_funding_source AS FIELD8,
a.prim_dr_cr_code AS FIELD9,
a.descp AS FIELD10,
' ' AS FIELD11,
' ' AS FIELD12,
' ' AS FIELD13,
' ' AS FIELD14,
' ' AS FIELD15,
' ' AS FIELD16,
'N' AS FIELD17,
' ' AS FIELD18,
' ' AS FIELD19,
' ' AS FIELD20,
' ' AS FIELD21,
' ' AS FIELD22,
a.dbs_gl_account + '1' AS FIELD23,
a.amt_class_1 AS FIELD24,
ABS (a.trans_amt) AS FIELD25,
ABS (a.trans_amt) AS FIELD26,
'Y' AS FIELD27,
'Y' AS FIELD28,
ABS (a.trans_amt) AS FIELD29,
'Y' AS FIELD30,
'Y' AS FIELD31
FROM DBSallc.dbo.HOPE_ALLOC_JRNL AS a
PRINT 'PJLN rows insertion complete'
/* Export PJCT_LOAD table to file output PJCTLOAD.txt */
EXECUTE master.dbo.xp_cmdshell @PJCT
PRINT 'PJCT export complete'
/* Export PJLN_LOAD table to file output PJLNLOAD.txt */
EXECUTE master.dbo.xp_cmdshell @PJLN
PRINT 'PJLN export complete'
/* Combine PJCT_LOAD.txt and PJLN_LOAD.txt files */
EXECUTE master.dbo.xp_cmdshell @combine
PRINT 'File combine complete'
PRINT 'Step 3 file conversion successful'
END
March 11, 2009 at 1:32 pm
Ok I pieced through the stored procedure and found the culprit..it isn't the bulk insert it is the use of bcp
This set command
SET @PJCT = 'bcp DBSallc.dbo.HOPE_PJCT_LOAD out e:\Allocations\alloc_gl_load\PJCTLOAD.txt -e e:\Allocations\alloc_gl_load\PJCTLOAD.err -c -T -V 80'
gives the error
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'HOPE\SQLAgentProxy'.
NULL
When executed
EXEC xp_cmdshell @PJCT
Will read up on bcp to see if there is an additional permission...
March 11, 2009 at 1:41 pm
Found the problem...
the bcp command is using -T for trusted connection..this is a mixed SQL server environment and thus the -U and -P parameters must be used.
Once used I am able to run with my proxy user
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply