Server Routine to connect SQL Server

[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.

[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.

[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.

[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)

[chulett]

It specifically writes a message to the log to help determine what went wrong. Post it.

[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.

[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.

[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?

[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).

[ray.wurlod]

Do you mean to be using

Code: Select all

isql 

as the actual command? If so, put that in.

[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)

[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).