Connect to SQL Server Personal edition through ODBC from PB7 script.

sorry,
there is one mistake in first option..it's of single quot
use this

ls_database='siddiqsons'
ls_srv_name= 'sdmsrv'
ls_latest_dsn = 'siddiqsons'

sqlca.dbms  = "ODBC"
sqlca.dbparm = "Connectstring='DSN="
sqlca.dbparm = sqlca.dbparm + ls_latest_dsn+";UID=sa;PWD=ali',disablebind=1"
connect;

IF SQLCA.SqlCode <> 0 THEN
   MessageBox ("Cannot Connect to Database", sqlca.sqlerrtext)
  RETURN
END IF

Mateen,
make a database profile in Powerbuilder... from the preview tab copy the database connection syntax....

paste this in the appication event  or wherever u want it....

try it this way....

i use ODBC... never faced such a problem...
nothing to do with Personal Edition.....

Cheers,
Rosh

Hi Sandeep:

Thanks for your correct answer.

If I use this

// Profile test_profit1
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"
SQLCA.Database = "siddiqsons"
SQLCA.ServerName = "sdmsrv"
SQLCA.LogId = "sa"
SQLCA.LogPass="ali"
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

then I assume that I will not need manual creattion of DSN ODBC.

Now How to check from within pb7 which sql server version is running.
When Professional edition then I would connect through window authentication
and
when personal edition I would connect through
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"

Mateen:

Mateen,

MSS is the native interface to connect to SQL Server. Did you previously use ODBC and trying to convert to use Native drivers.?

Will you need to use the same datawindows using ODBC or Native Drivers? SQL Syntax may be lillte different in each cases. I would personally use either ODBC or Native Interface, but not mix them up.

If you need to use ODBC and connect to SQL server using windows authentication. You will not need to supply user_id, passwords. But need to check on 'Integrated Security' check box. That would translate to ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON' within DBPARM.

// Profile team
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = True
SQLCA.DBParm ="ConnectString='DSN=mss',DelimitIdentifier='No',InsertBlock=22,DisableBind=1,StaticBind=0,ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON'"

I am not sure if this is what you are trying to do. But this s one way you can obtain version info.

select @@Version should provide more specific info on which version you are running.
You can create a dw out of 'select @@Version' and read the first row first column. Call the dw be d_version

datastore lds_ds
lds_ds = create datastore
long ll_rowcount
string ls_version

lds_ds.dataobject = 'd_version'
lds_ds.SetTransObject(SQLCA)
ll_rowcount = lds_ds.retrieve()

if ll_rowcount > 0 then
  lds_ds.GetItemString(1,1)
else
 MessageBox("Error", "Unable to determine MS Version")
end if

regards-

BTW, you will not need to create ODBC DSN if you connect thru Nativer Driver Interface MSS. But you will need to distribute PBMSSXX.dll with your application. Where XX is the version number.

Regards-

Sandeep Patel comments is accepted as answer as he provided the solution first as per my initial question.

Namase: U gave me remarkably beautiful link http://www.connectionstrings.com. I really liked that.

Mostly our systems are run in multi environment (Locally Connected).
When our system goes to outside the city (two to three case so far) to be run in one computer which has window 98 and sql personal edition installed and where "WINDOW AUTHENTICATION " is not possible..

I asked my question in this scenario.

BTW.<You can create a dw out of 'select @@Version' and read the first row first column. >
I have never been able to create such dw.

Mateen:

>BTW.<You can create a dw out of 'select @@Version' and read the first row first column. >
>I have never been able to create such dw.

You can do this. I created a datawindow out of 'select @@version' and called it d_version. Once the datawindow is created i renamed 'compute_001' to 'version'. I will not be able to do this using graphical interface. When you get a dislog to select tables during dw creating, cancel 'select tables' dialog box and the select 'design/convert to syntax' menu. Type 'select @@version'. Click on the 'Return' toolbaritem and the dw will be created.

$PBExportHeader$d_version.srd
release 8;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=84 color="536870912" )
table(column=(type=char(300) updatewhereclause=yes name=version dbname="compute_0001" )
 retrieve="select @@Version" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="5" y="4" height="76" width="3511" format="[general]" html.valueishtml="0"  name=version visible="1" edit.limit=300 edit.case=any edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" netscapelayers="0" )

regards-
 

Thanx Namasi: