Page 1 of 1
Server Routine to connect SQL Server
Posted: Sun Nov 07, 2010 6:12 am
by SURA
I am trying to connect SQL Server using server routine. See the below peace of code.
Sql = "will have the connection details for sql server"
Debug = 0
SQL = Sql
Command = SQL
Command := 'select id from Id_table'
and then the output will be captures eventually.
But it is not connecting to DB, but triggering exception.
If any one guide me in this will be great.
Posted: Sun Nov 07, 2010 7:13 am
by ray.wurlod
Could be something to do with the PATH for the sql command. Could be the fact that the statement that sets this should have quotes around the command (SQL = "sql ").
You might also like to investigate the BCI functions. A search of DSXchange will throw up code examples. These mimic the ODBC API and give you complete control over the connection to the database.
Posted: Sun Nov 07, 2010 8:10 am
by chulett
Perhaps it would help if you posted this exception or any other error/warning that you are seeing. You should also DSLogInfo() to write your final 'Command' out to the job's log so you can know that it is formatted correctly.
Still getting error
Posted: Sun Nov 07, 2010 4:24 pm
by SURA
Hi Ray
Thanks for your reply. Yes i did that "Sql", but still getting error. I am getting the user defined error return code of -98. Please guide me, what mistake i did in this.
Code: Select all
RoutineName = 'GetID'
Debug = 0
* Passing this value'sqlcmd OR isql -S DBNAME -U UNAME -P PASSWD' to Sql
SQL = "Sql"
Command = SQL : char(090)
Commnad := 'use Development'
Command := 'select id from id_table'
GoSub ExqtCommand
GetOutput=Trim(Output, @FM)
GetOutput=Ereplace(GetOutput, char(009), '')
If Debug Then Call DSLogInfo(GetOutput, RoutineName:"***DEBUGTHEERROR")
GetOutput=Trim(Output, @FM)
GetAns = GetOutput
Return(Ans)
* Subroutines
*
ExqtCommand:
Call DSExecute('NT', Command, Output, RtnCode)
Message = "Executed sqlserver command."
Message<-1> = "Command=":Command
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=" : Output
If Debug Then Call DSLogInfo(Message, RoutineName:"***DEBUGERROR")
Ans = 0
If Output matches '...ERROR...' Then
Message = "Error executing SQL statement."
Message<-1> = "Command=":SQL
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=":Output
Call DSLogWarn(Message, RoutineName)
Ans = -99
End
If RtnCode <> 0 Then
Message = "Error executing SQL command."
Message<-1> = "Command=":SQL
Message<-1> = "RtnCode=":RtnCode
Message<-1> = "Output=":Output
Call DSLogWarn(Message, RoutineName)
Ans = -98
End
Return(Ans)
Posted: Sun Nov 07, 2010 4:26 pm
by chulett
It specifically writes a message to the log to help determine what went wrong. Post it.
Posted: Sun Nov 07, 2010 4:45 pm
by SURA
Dear chulett
You are right. After i did it, i got the error details. I am trying to correct it. Meanwhile if you can figureout the error pls do help me. - Thanks
Arg1 = sqlcmd -S DBNAME -U UNAME -P PWD
Test failed.
Program "DSU.GetID": Line 7, Variable "Commnad" previously undefined. Empty string used.
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=Sql
> RtnCode=1
> Output='SqlZselect' is not recognized as an internal or external command,
> operable program or batch file.
>
Subroutine "DSLogInfo" called with 1 arguments, 2 were expected"
Program "DSU.GetID": Line 50, Unable to load subroutine.
Posted: Sun Nov 07, 2010 5:25 pm
by SURA
Hi chulett
I corrected the errors. This time after it executed i didnt get any return value at all (hope it hangs).
SQL = "sqlcmd -S DBNAME -U UNAME-P PWD" : char(010)
Command = SQL : char(010)
Command := 'use Development' : char(010)
Command := 'select max(id) from ID_Table' : char(010)
Is there is any mistake in the above code? This is the first time i am working with SQL Server.
Your time and comment is highly appriciatable.
Posted: Sun Nov 07, 2010 8:05 pm
by chulett
And I've never worked with SQL Server, so you'll need to wait for someone who has to know if what you are doing is appropriate for that database.
In the meantime, where exactly are you running this routine? In a Sequence job? Where is the return value used?
Posted: Sun Nov 07, 2010 9:26 pm
by Anantha
Hi chulett
I need to get the id either in job level and / Or squence level as per need. This is something like batch id concept.
Thanks for your time. Let see who else can reply for this. Meanwhile i did it using batch file (inside batch file, will trigger sql).
Posted: Mon Nov 08, 2010 12:54 am
by ray.wurlod
Do you mean to be using
as the actual command? If so, put that in.
Posted: Mon Nov 08, 2010 4:02 pm
by SURA
Hi Ray
I dont know what mistake i am doing, but i will keep on try to make it work. Meanwhlie if anyone find the way, that will be real help.
But when i tried isql i am getting the below error.
TEST #1
*******
DSLogWarn called from : GetID
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
DSLogInfo called from : isql -S DBNAME -U UNAME -P PWD
Message to be logged is...
> Error executing SQL command.
> Command=isql -S DBNAME -U UNAME -P PWD
> RtnCode=1
> Output='isql' is not recognized as an internal or external command,
> operable program or batch file.
>
Result = -98 (This is my return value for errors)
Posted: Mon Nov 08, 2010 4:27 pm
by ray.wurlod
Use the full pathname of the isql command or make sure that isql's parent directory is in your command search list (the PATH environment variable).