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

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

Thu, 29 May 2014 10:58:51 GMT

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.

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

Gysbert_Wassenaar — Thu, 29 May 2014 11:03:13 GMT

Change the line to set the variable to this:

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

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

Thu, 29 May 2014 11:11:56 GMT

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

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

tresB — Thu, 29 May 2014 11:16:06 GMT

Yes, use Trace statement, like:

Trace $(YourVariable);

Sleep(2000);

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

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

Thu, 29 May 2014 11:20:46 GMT

Try Using Trace In first connection ...

Regards,

Chinni Kanna

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

Thu, 29 May 2014 13:06:19 GMT

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

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

Thu, 29 May 2014 14:08:15 GMT

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)'];

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

Thu, 29 May 2014 14:16:31 GMT

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