DSXchange

Posted: **Wed Mar 16, 2011 3:19 am**

HI Ray,

ErrorCode = 0 ; * set this to non-zero to stop the stage/job 

$INCLUDE DSINCLUDE JOBCONTROL.H 
$IFNDEF JOBCONTROL.H 
$INCLUDE DSINCLUDE JOBCONTROL.H 
$INCLUDE UNIVERSE.INCLUDE ODBC.H 


$ENDIF 



*---- Start processing 

strOutPath = DSGetParamInfo(DSJ.ME, "RP", DSJ.PARAMVALUE) 
paths=DSGetParamInfo(DSJ.ME, "Patha", DSJ.PARAMVALUE) 


*---- Opening the File 

CntFilename = DSGetParamInfo(DSJ.ME, "JPM_ROUTINE_FILENAME", DSJ.PARAMVALUE) 
strFileName = strOutPath:"/":CntFilename 


*--Call DSLogInfo("Input link":strFileName,DSJ.ME) 

OpenSeq strFileName To objFileVar 

Else 

Create objFileVar 

Else 

ErrorCode = 1 


WeofSeq objFileVar 

End 

End 

WeofSeq objFileVar 

*---- Getting the link count for Input link 

Call DSLogInfo("Input link",DSJ.ME) 
iReadInputRecCount=0 
InputStageName1 = DSGetParamInfo(DSJ.ME,"JPM_INPUT_STAGE", DSJ.PARAMVALUE) 
InputLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_INPUT_LINK", DSJ.PARAMVALUE) 
iReadInputRecCount1 = DSGetLinkInfo(DSJ.ME,InputStageName1,InputLinkName1,DSJ.LINKROWCOUNT) 
iReadInputRecCount=iReadInputRecCount1 

*----Checking if reject link exists or not 

Reject_Status = 0 

Reject_Status = DSGetParamInfo(DSJ.ME, "JPM_REJECT_STATUS", DSJ.PARAMVALUE) 

If(Reject_Status >0) Then 

*---- Getting the link count for Reject link 

Call DSLogInfo("Reject link",DSJ.ME) 
iReadRejCount = 0 
RejectStageName1 = DSGetParamInfo(DSJ.ME,"JPM_REJECT_STAGE1", DSJ.PARAMVALUE) 
RejectLinkName1 = DSGetParamInfo(DSJ.ME, "JPM_REJECT_LINK1", DSJ.PARAMVALUE) 
iReadRejCount1 = DSGetLinkInfo(DSJ.ME,RejectStageName1,RejectLinkName1,DSJ.LINKROWCOUNT) 
Call DSLogInfo("Reject Link1:" : iReadRejCount1,DSJ.ME) 

iReadRejCount = iReadRejCount1 

End Else 

iReadRejCount = 0 

End 

Call DSLogInfo("--------------JOB STATISTICS-------------",DSJ.ME) 

Call DSLogInfo("Input Record Count :":iReadInputRecCount,DSJ.ME) 

Call DSLogInfo("Reject Record Count :":iReadRejCount,DSJ.ME) 

*---- Getting the required counts from the Output link 

ProcessStageName= DSGetParamInfo(DSJ.ME,"JPM_OUTPUT_STAGE", DSJ.PARAMVALUE) 
ProcessLinkName = DSGetParamInfo(DSJ.ME, "JPM_OUTPUT_LINK", DSJ.PARAMVALUE) 
iProcessRecCount = DSGetLinkInfo(DSJ.ME,ProcessStageName,ProcessLinkName,DSJ.LINKROWCOUNT) 

strRecCount = FMT(iProcessRecCount,"10L") 

Call DSLogInfo("Output Record Count : " :strRecCount, DSJ.ME) 


iTotalRecordCnt = iProcessRecCount+iReadRejCount 

If ((iTotalRecordCnt = iReadInputRecCount) ) Then 

fthresholdSts= "SUCCESS" 

End Else 

fthresholdSts = "FAILURE" 

End 

*---- Other Parameters 

JobName = DSGetJobInfo (DSJ.ME, DSJ.JOBNAME) 
JobStartTime = DSGetJobInfo (DSJ.ME,DSJ.JOBSTARTTIMESTAMP) 
JobEndTime = DSGetJobInfo (DSJ.ME,DSJ.JOBLASTTIMESTAMP) 
ProjectID = DSGetParamInfo(DSJ.ME,"$HCP_SHARP_DS_PROJ", DSJ.PARAMVALUE) 
SequenceName = DSGetParamInfo(DSJ.ME,"JPM_DS_SEQ", DSJ.PARAMVALUE) 
MetricDescription= DSGetParamInfo(DSJ.ME,"JPM_METRIC_DESC", DSJ.PARAMVALUE) 

*---- Wrting the Reonciliation count into a file in format ProjectID,SequenceName,Job name,JobStartTime,JobEndTime,Input Records,Success records,Reject records,MetricValue,MetricDescription,Comments 

strReconCount = ProjectID:',':SequenceName:',':JobName:',':FMT(JobStartTime,"20L"):',':FMT(JobEndTime,"20L"):',':FMT(iReadInputRecCount,"10L"):',':FMT(iProcessRecCount,"10L"):',':FMT(iReadRejCount,"10L"):',':fthresholdSts 

WriteSeqF strReconCount To objFileVar Then 

End 

*---- Check for threshold and abort the job 

If ((iTotalRecordCnt = iReadInputRecCount) ) Then 

Call DSLogInfo("The records matched with the Input and Output and Rejects ",DSJ.ME) 

End Else 

Call DSLogFatal ("The records count did not match with the Input and Output and Rejects ",DSJ.ME) 

End 

*---- Check for Zero Reject Record count 

if iReadRejCount<=0 then 

Call DSLogInfo("No 'Reject Records' are present") 


End 


*-- Load into Database 
*-- Path=C:\'Program Files'\'Microsoft SQL Server'\90\Tools\Binn 

InsertStmt="INSERT INTO DATABASENAME.dbo.t values ('" : SequenceName: "');" 
SQLCmd='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S DATABASENAME -U USERNAME -P PASSWORD -q "' : InsertStmt : '"' 
Call DSExecute('NT',SQLCmd,Output,RtnCode)

Everythin in the code is working except connecting to Database.

Routine works 100% fine while loading the data to a sequential file and also to the Log

Please check the last 3 lines of the code and suggest

Many thanks in advance

Posted: **Wed Mar 16, 2011 7:28 am**

ray.wurlod wrote:This would be so much easier to read if it were wrapped in Code tags rather than Quote tags. Can you please edit that last post accordingly?

Done... didn't really help, though, as no formatting survived the journey.

Posted: **Wed Mar 16, 2011 3:37 pm**

Was there any output or return code from DSExecute?

If the INSERT statement itself is hanging in SQL Server, have your DBA look at the session there and see if there's anything to be seen.

If you're using a Microsoft ODBC driver you might consider rewriting the last three lines to use BCI functions, at least to determine exactly where the problem is occurring (for example on connection, on preparing the SQL, on executing the SQL, etc.)

Posted: **Wed Mar 16, 2011 10:56 pm**

Hi Ray,

I did check with my DBA, and it seems like its not connecting to Database.

None of the activity is triggered after the routine has started running.

It seems like its problem with the statement

SQLCmd='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD" -S DATABASENAME -U USERNAME -P PASSWORD -q "' : InsertStmt : '"'

I am not familiar with BCI functions,

If possible please help me out with these.

Is there any other way/syntax to connect SQL server from Routine.

Many thanks for your time

Posted: **Wed Mar 16, 2011 11:42 pm**

Not sure what DATABASENAME is in your context. Is it an ODBC data source name?

Search DSXchange for examples of how to use BCI functions to execute SQL over an ODBC connection.

Posted: **Thu Mar 17, 2011 2:41 am**

Hi Ray,
"DATBASENAME" in my context is actual database name in my SQL Server

which is 'GMFIT"

In my actual coding i have used this GMFIT,

I just modifed to DATABASENAME while posting so that there won't be any confusion.

If possible can you let me know the syntax to use ODBC connection for connecting to SQL server from the Routine

Regards
Kris

Posted: **Thu Mar 17, 2011 3:49 pm**

I would use BCI functions.
ErrCode = SQLAllocEnv(hEnv)
ErrCode = SQLAllocConnect(hEnv,hDBC)
ErrCode = SQLSetConnectOptions(...) ; * as required
ErrCode = SQLConnect(hDBC,DSN,username,password)
etc.

Search DSXchange for examples about how to use BCI functions.

Posted: **Thu Mar 17, 2011 10:25 pm**

boppanakrishna wrote:Hi All,

Any more suggestions are welcome

Hi Friend

Right now i started to work with SQL Server, The reason for hanging is -q. Take that and replace with -Q.

simple

DS USER

Posted: **Fri Mar 18, 2011 3:42 am**

Hi Sura,

I have used the following code to connect to sql serve rdatabase

Code: Select all

ErrorCode = 0      ;* set this to non-zero to stop the stage/job
$INCLUDE DSINCLUDE JOBCONTROL.H 
Call DSLogInfo("STARTD",DSJ.ME) 
InsertStmt="INSERT INTO DATABASENAME.dbo.T values ('AB');"
Call DSLogInfo(InsertStmt,DSJ.ME) 
SQLSTMT='"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S  SERVERNAME -U USERNAME -P PASSWORD -Q "' : InsertStmt : '"'  
Call DSExecute("NT",SQLSTMT,Output,RtnCode)
Call DSLogInfo(RtnCode,DSJ.ME) 
Call DSLogInfo(Output,DSJ.ME)

The output of the routine is :
Rtncode=1
Output='C:\Program' is not recognized as an internal or external command,
operable program or batch file.

In my current environment, SQL Server , IIS are on different boxes

Is that the possible cause for this?

Is there any environmental variable that needs to be set up to access the database remotely

Posted: **Fri Mar 18, 2011 7:08 am**

Same dang problem as before, the space in the pathname.

DSXchange

Connecting to Sql server 2008 from Server routine