Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Trying to write data on a remote SQL Server db with ADO object and OLEDB

Hello,

in my QlikView application I need to upload data from txt/csv files (previously uploaded) and write them on a remote SQL Server DB using OLEDB driver.

Reading this forum I found the utilisation of ADO technology as unique solution, inserting it into a macro.

I tried to create a basic vbscript function to insert one record in db.

Here's my code:


sub dbInsert
dim sConn, oRS, sSQL

set sConn = CreateObject("ADODB.Connection")
url = "Provider=sqloledb;Data Source=server_name;Initial Catalog=db_name;UserId=xxxx;Password=xxxx"
sConn.open url
set oRS =CreateObject("ADODB.Recordset")
sSQL="INSERT INTO STG.STG_PROVA_INSERT (FATTURA_RIGA_ID, FATTURA_ID, PRODOTTO_ID, CONTRATTO_ID, PUNTO_ID, VERSIONE_ID, NUMERO_FISCALE, RIGA_CD) VALUES (0,0,0,0,0,0,"","")"
oRS.Open sSQL, sConn, adOpenDynamic, adLockOptimistic, adCmdUnspecified
rem set oRS = sConn.execute(sSQL)
oRS.close
set oRS = Nothing
sConn.commit
sConn.close
set sConn = Nothing
end sub


The execution fails with command "sConn.open url", I think because the compiler doesn't solve "CreateObject("ADODB.Connection")" call.

Any clues? Tips? Tricks? Would really appreciate it!

Thanks a lot,

Alessandro

erv

Tags (3)
2 Replies
Not applicable

Trying to write data on a remote SQL Server db with ADO object and OLEDB

Only to say you that in the script module I connect succesfully with the remote DB via OLEDB driver.

Not applicable

AW:Re: Trying to write data on a remote SQL Server db with ADO object and OLEDB

Hi,

you got a little error in your connection string. The "UserId" must be typed with a blank to look "User Id"

This line should work for you:

url = "Provider=sqloledb;Data Source=server_name;Initial Catalog=db_name;User Id=xxxx;Password=xxxx"

Helmut

Community Browser