I did add this but made a syntax error, so it didn't work.
Can you clarify,
a. You corrected a syntax error and now everything is working?
b. You made a syntax error but things are still not working?
A. Everything is working.
Eventually I used the following query in a SQL agent job to calculate a month total and write it back to the beginning of the previous month, I really hated the quotes.
DECLARE @TSQL varchar(200)
DECLARE @VAL float
DECLARE @STRINGVAL varchar(20)
DECLARE @STRINGDATEBEGIN varchar(20)
DECLARE @STRINGDATEEND varchar(20)
-- DEFINE PERIOD
SET @STRINGDATEBEGIN = CONVERT (VARCHAR(50), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),20) --First day of previous month
SET @STRINGDATEEND = CONVERT (VARCHAR(50), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),20) --First day of current month
-- GET TOTALIZER VALUE
SELECT @TSQL = '(SELECT VALUE FROM OPENQUERY(PI_SERVER_T, ''SELECT VALUE FROM piarchive.pitotal
WHERE tag = ''''TAGNAME1'''' AND time BETWEEN ''''' + @STRINGDATEBEGIN + ''''' AND ''''' + @STRINGDATEEND + '''''''))'
DECLARE @t TABLE (aVAL float)
SELECT @VAL = aVAL from @t
--MULTIPLY WITH 24 FOR MONTH TOTAL AND CONVERT TO STRING
SET @VAL = @VAL * 24
SET @STRINGVAL = CONVERT (VARCHAR(50), @VAL,128)
-- CREATE STATEMENT FOR WRITING TOTALIZER DAY VALUE PREVIOUS MONTH
SELECT @TSQL = 'SELECT * FROM OPENQUERY(PI_SERVER_T, ''INSERT piarchive..picomp2 (tag, time, value)
VALUES (''''TAGNAME2'''',''''' + @STRINGDATEBEGIN + ''''',' + @STRINGVAL + ')'')'
I realize this thread is very old, but I tried creating a query based upon your script:
DECLARE @TSQL varchar (400)
SELECT @TSQL= 'SELECT * FROM OPENQUERY([LINKEDPI],
''INSERT piarchive..picomp2 (tag, time, value)
SELECT ''''sinusoid'''', time, value
WHERE tag = ''''cdt158'''' AND time BETWEEN ''''y'''' AND ''''t'''' '')'
When I run it, it says that it executed successfully.
However, none of CDT158's data is showing when I look at Sinusoid's archive data. Any thoughts on what I'm doing wrong?
I copied and pasted your query into my linked server and it works perfectly. Therefore, it is not an issue with your query. I will advise checking the PI Data Archive logs to see if there are any permission issues.
Thanks for trying it and your recommendation. After checking the message logs, it looks like I'm actually getting the 'Snapshot post failed [-11410] Invalid registration ID from buffered source' error.
I found your response to someone who was having a similar issue: Snapshot post failed (1000 events), [-11410] Invalid registration ID from buffered source However, I am running PI Buffer Subsystem 184.108.40.206 and the KB Article you direct to says the issue was resolved back with the 220.127.116.11 release. I tried restarting the PI Network Manager, but that didn't resolve the issue.
Anything else I could try?
1 of 1 people found this helpful
PI OLEDB (Classic) depends on PI SDK. When inserting to piarchive..picomp2 table, you may want to check the PI Buffer Subsystem version and configuration on the PI OLEDB host.
SINUSOID and CDT158 are usually serviced by PI Random Simulator running on the PI Data Archive host. It's uncommon but not impossible PI Random Simulator is configured as buffered source. For this reason, you may want to create a test tag to write to through PI OLEDB linked server and assign an un-used pointsource to avoid conflicts due to PI Buffer Subsystem locking.
Thanks for your reply, Gregor! I ended up reaching out to Tech Support and the product manager provided a workaround, as the KB article's steps did not work.
We're unsure what actually caused our system to have these issues (apparently it's normally caused by cloning a server, but we didn't do that), but I'll definitely keep the information you provided in mind for future testing purposes.
i created linked server by PI-OLEDB and i tested SQL query for insert some data to piarchive. This SQL query below.
@Tagname as varchar(50),
@Timestamp as date,
@Value as float
SET @Tagname = 'TEST'
SET @Timestamp = '2018-07-25'
SET @Value = 9999
INSERT INTO [LINKEDPI].[piarchive]..[picomp2] (tag, time, value) VALUES (@Tagname,@Timestamp,@Value)
but it's show error Error converting data type nvarchar to (null). How can i fix it?