Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loading Reult Of SQL query into Variable to be used in connection string

HI I am trying to use the reult of a query (A database name) into a qlikview varible so that I can then use it to create a connection string to that databse.

Below is an example of what I have been trying. Any ideas how to load the result of a SQL query into a variable

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog='Qlikview_MiltipleDBsTest1';Data Source=$(Vdatasource)];

DatabaseName: SQL SELECT top 1 DatabaseName  FROM dbo.COnfigurations  ;

LET  VdatabaseName =  [DatabaseName.DatabaseName]  ;

DISCONNECT ;

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=$(VDatabaseName);Data Source='LGA1']

Thanks for any help.

7 Replies

Re: Loading Reult Of SQL query into Variable to be used in connection string

Change the line to set the variable to this:


LET  VdatabaseName = peek( 'DatabaseName','DatabaseName')  ;


talk is cheap, supply exceeds demand
Not applicable

Re: Loading Reult Of SQL query into Variable to be used in connection string

Thanks

Still not working though. I have created a text box on a tab to display the variable value but it is not being populated and I do not think the variable is being asssigned correctly.

Was not sure if the variable persists after run time or not so tried to pull data from a table in the databse and got an error connecting to the table.

Is there a way to view the variable value at runtime?

Thanks

MVP
MVP

Re: Loading Reult Of SQL query into Variable to be used in connection string

Yes, use Trace statement, like:

Trace $(YourVariable);

Sleep(2000);

Note: variable value persists after load if not cleared forcefully.

Not applicable

Re: Loading Reult Of SQL query into Variable to be used in connection string

Try Using Trace In first connection ...

Regards,

Chinni Kanna

Not applicable

Re: Loading Reult Of SQL query into Variable to be used in connection string

Thanks for the help but the varible is populating as Blank. I have displayed the field though and is being populated correctly.

Any other ideas?

Thanks

Not applicable

Re: Loading Reult Of SQL query into Variable to be used in connection string

Please check the below query return the value ?

DatabaseName:

SQL SELECT top 1 DatabaseName  FROM dbo.COnfigurations  ;

If yes, try below

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog='Qlikview_MiltipleDBsTest1';Data Source='$(Vdatasource)'];

Not applicable

Re: Loading Reult Of SQL query into Variable to be used in connection string

I am trying to create a variable for the databse name I already have it working with a qvs file for the server name but need to read the database name from a sql table.

The query does return the correct databasename and it is a valid database.

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog='$(VDatabaseName)';Data Source='$(Vdatasource)']


But i can not get the value from the query into the variable '$(VDatabaseName)


Thanks

Community Browser