I have a reference table in SQL that automatically updates so that you can pick yesterday's snapshot - UNIXAKCCollection.dbo.KC_Named. However, when I run the code below and try to use a variable and the Peek command the field results are null. Any help would be greatly appreciated.
//Dataset selects Yesterday's snapshot
SQL Select PhysicalName as DBName
From UNIXAKCCollection.dbo.KC_Named as N
Inner Join UNIXAKCCollection.dbo.KC_Databases D on N.NameID = D.NameID
WHERE N.DatabaseID =1
and N.DatabaseTag =1;
Let vdatabase = Peek('DBName','YesterDatabase'); //However vdatabase variable is null
SQL select * from $(vdatabase).larmstr;
That worked well. Thanks. However, I have a new error when I try the next select clause:
ODBC read failed
SQL select * from UNIXASnapshot20170403.dbo.larmstr
Note that I added in the dbo in the table name.
Thanks, Vishwarath. That worked great. Thanks for the additional input. I realized that I had omitted a change in my code which I have corrected and now all parts of the script work.
This syntax worked to assign the variable
Let vdatabase = Peek('DBName', -1,'YesterDatabase');
And this syntax worked for the query:
SQL select * from $(vdatabase).dbo.larmstr;