SQL Function

[Former Member]

Hello Everyone,

I have a function in SQL Server which i use in one of my UDO Screens.This works fine in current company. But when i make an addon and run it in new company error occurs as to the function was not found. Now i know that the function written in current company is not available in new company.

My question is how to package the function in my coding so that when i create an Addon,the function is automatically created in whichever company i run it in. Hope i'm clear in explaining the problem.Please help as this is the final hurdle in creating Addon.

[Nussi · Accepted solution]

Hi Raghu,

usually i did this with an ADO Recordset in past, because

than you can create a Query that creates the Function.

i help you as much as i can and provide you this vb6 sample:

    Dim oDBConnection As ADODB.Connection
    Dim oDBRecordSet As ADODB.Recordset

    Dim oDBConnection As ADODB.Connection
    Dim oDBRecordSet As ADODB.Recordset
    Dim sDBServerName As String
    Dim sDBCompanyDBName As String
    Dim sDBUserName As String
    Dim sDBUserPasswd As String
    Dim sConnectionString As String
    Dim pCommand As String
    
    Set oDBConnection = New ADODB.Connection
    Set oDBRecordSet = New ADODB.Recordset
        
    sDBServerName = oCompany.Server
    sDBCompanyDBName = oCompany.CompanyDB
    sDBUserName = B1C_DBUser
    sDBUserPasswd = B1C_DBPasswd
    
    sConnectionString = "driver={SQL Server}; Server=" & sDBServerName & "; Initial Catalog=" & sDBCompanyDBName & "; User ID=" & sDBUserName & "; Password=" & sDBUserPasswd
    oDBConnection.open sConnectionString

    pCommand = "CREATE - INSERT YOUR SQL CODE"
    DBRecordSet.open pCommand, oDBConnection

important is that you have the Microsoft Active X Data Objects 2.7 Library included !

much luck

David

[Former Member · Accepted solution]

Thanks for the reply David....

I have another silly doubt on this.My function is atleast 25 lines.How can i give it in pcommand as a string.pls give an example. Also if i create an addon for this, i have to add Activex 2.7 Data objects in addtion to SAP bobs and UI dll.

Thanks and Regards

Ra

[Nussi · Accepted solution]

Hi Raghu,

put a enter in the string

in vb6 it would be:

pCommand =  "CREATE proc [dbo].[MY_PROCEDURE]" + vbCrLf + _
                      " @object_type nvarchar(20),       " + vbCrLf + _
                         .
                         .
                         .

ado is usually every installed - i never faced a problem with this

i hope it helps

lg David

[Former Member · Accepted solution]

Thanks David for the timely help.........

[Former Member · Accepted solution]

Hi David,

Your suggestion worked fine...till now.I have a new server for which the password for "sa" user is different.I have given password as

company.Dbusername
company.Dbpassword

Now the error is that "Login failed for use sa".Wat can i do to resolve this.

Welcome any suggestions.

[Nussi · Accepted solution]

Hi Raghu,

quite strange,

did you change the servername ? maybe you're refering to the wrong server.

on the other side the error message also looks like the sa password is wrong

or the sa user doesn't exist.

lg David

[Former Member · Accepted solution]

Raghu,

without correct password you arent able to create the function on sql server. My suggestion is create it with the password you have and in case, that the error from sql server is incorrect passwrod, show to user inputbox for writing correct password and connect the recordset with given password.

[Former Member · Accepted solution]

Actually the first time i hardcoded the password.Now that the client has a new server the sa password is different. So i dont want this siutation to reoccur. But if i pass the password as

company.dbpassword

its only picking up ********* and not the password.Since i use this function in one of my addons i need to create this function in whichever company or server the client connects to.Any suggestion otherwise.

[Nussi · Accepted solution]

well ...

the problem is you can't use company.dbpassword in your connection string,

cause the password is encrypted in there.

you have to hardcode the sa passwort in the ado-connection string or save it

somewhere extern.

lg David

[Former Member · Accepted solution]

is there any other way whereby i can get the SQL Server password through SAPbobs recordset by way of query,is the password for SQL Server strored anywhere else? If not getting the password for sqlserver from client at startup as Petr the only way i think.

[Former Member · Accepted solution]

Hi guys

A Sapbobs Recordset did the trick.

i Forgot that recordset would execute watever query u give it.

So i gave it like this

rs.DoQuery(pcommand)

where pcommand has the function i need.Just that before this i need to check from sysobjects if such a function exists. Why didnt i think of this before.

Thanks any way guys you opened a new vista.

Also please do look into the Visual source safe XML corruption and throw some light on it