Connecting to Sql server 2008 from Server routine

[boppanakrishna]

Hi All,
I need help in connecting to SQL server from datastage after job sub routine

I am using the following command in the routine to connect, but couldn't connect

SQLCmd='SQLCmd -S DATABASENAME -U USERNAME -P PWD -q "' : InsertStmt : '"'

Error message:

QueryOutput'SQLCmd' is not recognized as an internal or external command,
operable program or batch file.

Please coorect me if i went worng in the syntac

Regards
Krishna

[ray.wurlod]

More likely Windows can't find the sqlcmd executable because its parent directory is not mentioned in your PATH environment variable and you're not executing in its parent directory. You can get around this by specifying the full pathname of the sqlcmd executable.

[boppanakrishna]

Hi Ray,

SQLCMD executable file is present the following location on the server at which datbase is installed

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

I modified the code to

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

But still no luck.

Please correct me if i went wrong

Regards
kris

[chulett]

You left out the actual "sqlcmd" bit on the rewrite.

[boppanakrishna]

Hi Chullet,

I have added SQLCMD to th epath but still no luck

below is the error message

QueryOutput'C:\Program' is not recognized as an internal or external command,
operable program or batch file.

It seems like its consiedring the SPACE after Program

Belwo is the syntax for the code i have added

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD\ .......

Please let me know the other ways of implementation if there are any

[ray.wurlod]

There should be no trailing backslash on the executable name.

[chulett]

DOS pathnames with spaces need to be enclosed in single quotes, that or you need to switch back to the internal 8.3 format:

C:\Progra~1\Micros~1\90\Tools\Binn\SQLCMD

No guarantees that the second one is "~1" as it de-dups after 6 characters, so it could be "~2" or "~3" etc.

[boppanakrishna]

Hi Chulett,

I have give the following code as per your instructions

InsertStmt="INSERT INTO DATABASE.SCHEMANAME.TABLENAME values ('" : SequenceName: "');"

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

Call DSExecute('NT',SQLCmd,Output,RtnCode)

The job is hanging at AFTER JOB ROUTINE.

Actually my task is to inser some values that Routines generates into a SQL server Table.

Please let me know if there are any other commands that serves the purpose

Regards
Kr

[boppanakrishna]

Hi All,

Any more suggestions are welcome

[chulett]

You need to determine where, exactly, it is hanging up. Have you had your DBA monitor the database, see if the issue is there? Do you even get that far?

[ray.wurlod]

Put "debugging" calls to DSLogInfo into your routine to determine which actual statement is the problem.

[boppanakrishna]

Hi ,

I tried to debug the routine, also noticed its not connecting to database.

Is there any command to check if the connection is succesful or not?


Please let me know if i had written the correct code.

In meanwhile i am exploring things from DBA side also to see what went wrong

[ray.wurlod]

We can't tell if you've written the correct code unless you post that code!

[BIuser]

Hi Ray,

Please see the below code

Code: Select all

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) 

PPlease check and let me knwo where i went wrong .
Infact this code was taken from one of the post

Regards
Kris[/b]

[ray.wurlod]

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?

And we seem to have a widespread team working on it - one in Mumbai and one in South Africa!

What was the final executed statement prior to the "hang"?