Start a new topic

Unexpected Text With Command Line When Writing Results to Output File

[This topic is migrated from our old forums. The original author name has been removed]

DbVisualizer Peeps, Long-time user, long-time fan. Your product is badass. It's the proverbial Chuck Norris of database tools. That being said, I seem to be having issues with the command line interface. I'm trying to run a SQL script that generates a series of insert statements while redirecting the results to an output file which in turn, is executed against a different connection. dbviscmd.sh -connection "foo1" -sqlfile foo1.sql -output result -outputfile foo2.sql dbviscmd.sh -connection "foo2" -sqlfile foo2.sql -output log -outputfile foo2.log Unfortunately, the generated SQL files all seem to contain the text "1 N/A" in the first line of the file: 1 N/A ------------------------------- INSERT INTO Foo (Foo) VALUES ('Foo... Bared?'); ... which aesthetically speaking, isn't likely to be the source of countless nights of mental anguish, but does seem to prompt the rather unsavory response of: "ERROR: 12:20:04 [1 - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42000] Incorrect syntax near '1'." from the SQL Server DB that I'm executing the file against (the first statement in the file fails execution - subsequent statements are executed properly). Under normal circumstances removing the lines in a text editor would be inconvenient, but hardly tragic (although I am getting paid by the hour... so that's basically another $0.25 in my pocket), but this happens to be part of a shell script that automates the creation / execution of several script files. That, and I happen to be extremely lazy... so I'd really just prefer not to. Any ideas?

[This reply is migrated from our old forums.]

Re: Unexpected Text With Command Line When Writing Results to Output File
Hi David, I'm glad to see that you found a solution. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Unexpected Text With Command Line When Writing Results to Output File
By Jove, I think I've got it. What I'm doing here is somewhat unusual... the exported data is a series of concatenated strings representing insert statements. In other words, my select statement for the source SQL is something along the lines of: SELECT 'INSERT INTO Foo (Foo) VALUES (''Foo'');' from Blah; The reason for taking this approach (rather than simply using the handy @export feature) is that the source and target tables are completely different in structure (we're migrating data from a legacy DB). The "1 NA" as it turns out, was due to a non-specified column alias in the source SQL. DbVisualizer was including this as the first line in the file. If the above was rewritten as: SELECT 'INSERT INTO Foo (Foo) VALUES (''Foo'');' as Results from Blah; then the produced output would resemble: RESULTS INSERT INTO FOO(FOO) VALUES ('FOO'); I was able to get around this by aliasing the column as "--" which is kindly ignored when executing the script. Thanks for the help Hans - it's definitely appreciated. David
[This reply is migrated from our old forums.]

Re: Unexpected Text With Command Line When Writing Results to Output File
Hi David, Thanks for all the kind words, really appreciated! I assume that you use a SELECT statement in your foo1.sql script that generates one column with an INSERT statement. There are some different options, the easiest being using the @export command instead with parameters for exporting INSERT statements: http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId448386 E.g.: @export on; @export set format="sql" filename="foo2.sql"; select * from MyTable; Alternatively, you can use the @export CSV format with your current SELECT and the @export parameter CsvIncludeColumnHeader set to false. If you need further help, please post or email your foo1.sql script. Best Regards, Hans