Long time no post!
Our application has two parts, a server application that runs centrally & uses SQL Server as its database backend, and the client part, uses SQL Anywhere as an embedded database. As such, the only tool installed with our product from which we can execute ad-hoc scripts for correcting & diagnosing customer issues is DBISQLC.EXE. The product uses our proprietary technology for replicating & synchronizing data between the server & the client.
We recently came across a problem where data that is on the client was out of sync with the server. In order to correct the problem, I had to determine which record on the client was the problem one, which involved opening remote sessions with the server & the client, running a query in SSMS on the server, and running another query in DBISQLC.EXE on the client and painstakingly comparing the results. This was a very time consuming and error prone process. There has to be a better way.
I have been able to write a T-SQL script that runs in SSMS, whose output is a script that can run in DBISQLC on the client & which identifies the problem row(s). What I really need the client script to do is go one step further and output either an entire T-SQL script that can be run in SSMS on the server to correct the problem, or, as an acceptable but less desirable alternative, to output data that can be copy & pasted into a T-SQL script in SSMS.
In the interests of making sure enough information is available, what the script I've written does is execute a sequence of PRINT statements. These statements output the text of a script that creates a stored procedure in SQL Anywhere, executes it, and then drops it.
The stored procedure creates a temporary table with two columns & loads data into it. The data is loaded into the temporary table by SQL like this:
The stored procedure that's built by this process compares the contents of the temporary table to the contents of the table with the problem and determines how many problem rows are in that table. What I'd like it to do is use code similar to that above to output statements for loading data into a temporary table on the SQL Server database that can be used by a script to actually fix the problem. The problems are that:
So how can I get the results I'm looking for?
FWIW, here's a different approach to generate a SQL DML statement based on a SELECT UNION over sample data (here an excerpt from SYSTAB). As the complete query is build by one SELECT statement, you don't have to mess around with several PRINT/MESSAGE statements - you just have to export the query (with according delimiters, apparently).
select txt from (select 0 as line_no, 'Insert into MyTable ( Column1, Column2 ) values ' as txt union all select row_number() over (order by table_name) as row_nr, '(''' || table_id || ''', ''' || table_name || ''')' || if row_nr = count(*) over () then ';' else ',' end if from systab where table_name like 'ix_%' order by 1) dt order by line_no;
The result can be written to a file via
and returns a file with the following contents:
Insert into MyTable ( Column1, Column2 ) values ('669', 'ix_consultant_affected_columns'), ('667', 'ix_consultant_index'), ('668', 'ix_consultant_ixcol'), ('670', 'ix_consultant_log'), ('663', 'ix_consultant_master'), ('666', 'ix_consultant_query_index'), ('665', 'ix_consultant_query_phase'), ('664', 'ix_consultant_query_text');
With some more delimiters/blanks, that may create the SQL statement of your choice... Even adding comments via separate UNIONed query blocks is quite easy.