Page 1 of 2
Connecting to Sql server 2008 from Server routine
Posted: Mon Mar 14, 2011 12:30 am
by 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
Posted: Mon Mar 14, 2011 2:38 am
by 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.
Posted: Mon Mar 14, 2011 11:06 pm
by 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
Posted: Mon Mar 14, 2011 11:11 pm
by chulett
You left out the actual "sqlcmd" bit on the rewrite.
Posted: Mon Mar 14, 2011 11:52 pm
by 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
Posted: Tue Mar 15, 2011 12:45 am
by ray.wurlod
There should be no trailing backslash on the executable name.
Posted: Tue Mar 15, 2011 7:01 am
by 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.
Posted: Tue Mar 15, 2011 8:11 am
by 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
Posted: Tue Mar 15, 2011 11:18 pm
by boppanakrishna
Hi All,
Any more suggestions are welcome
Posted: Tue Mar 15, 2011 11:29 pm
by 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?
Posted: Tue Mar 15, 2011 11:31 pm
by ray.wurlod
Put "debugging" calls to DSLogInfo into your routine to determine which actual statement is the problem.
Posted: Tue Mar 15, 2011 11:47 pm
by 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
Posted: Wed Mar 16, 2011 12:27 am
by ray.wurlod
We can't tell if you've written the correct code unless you post that code!
Posted: Wed Mar 16, 2011 12:46 am
by 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]
Posted: Wed Mar 16, 2011 1:54 am
by 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"?