Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks
JP
//Dataset selects Yesterday's snapshot
YesterDatabase:
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;
Try this?
Let vdatabase = Peek('DBName', -1,'YesterDatabase');
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
Are you using variable in that next select script?
Yes and when I copy the text of the error message and run it it SSMS it works fine.
Can you tell what variable is holding?
May be try this?
SQL select * from '$(vdatabase)'.larmstr;
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.
So which one worked?
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;
Thanks
Glad it worked.