SQL Runner - 32 Bit

DTM ODBC Manager, SQL runner, Dashboard and other freeware by DTM soft

Moderator: Support Team

SQL Runner - 32 Bit

Postby nmdotcom » Thu Sep 15, 2011 3:11 pm

Hello,

I'm currently using SQL Runner to query a database and output the results to a text file, however, the text file is missing the header information. Is there any way I can include the header information in the text file?

Thanks,

nmdotcom
nmdotcom
 

Re: SQL Runner - 32 Bit

Postby Support Team » Thu Sep 15, 2011 6:28 pm

Unfortunately it has no code that can extract column name from result row set.
We plan to add this option in the future releases.
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby nmdotcom » Thu Sep 15, 2011 7:52 pm

Thanks for replying, I will keep an eye out for future releases!
nmdotcom
 

Re: SQL Runner - 32 Bit

Postby Support Team » Sat Sep 17, 2011 8:10 am

We've added -H command line parameter and 'Header' item to [Settings] section.
Header=1 or Header=true are acceptable.

Please try it out.

PS. it works for Win32 version only, not for x64.
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby nmdotcomm » Mon Sep 19, 2011 3:59 pm

Just tried out the new option for enabling headers and it worked great! Thanks! :D
nmdotcomm
 

Re: SQL Runner - 32 Bit

Postby Nick M » Tue Sep 20, 2011 5:21 pm

I'm using SQL runner mostly just to automate text data extracts and it has more than served its purpose as a great tool. In other query environments, a date/time field is brought back in this format "mm/dd/yyyy 00:00:00";however, with SQL runner it is bringing back "mm/dd/yyyy 00:00:00.000" essentially including hundredths of a second. I've tried editing the SQL query to omit the decimals by converting the date/time to text (using a left statement to grab everything before the decimals), however, it seems SQL runner will still add the decimal seconds at the end as 000. Is there an option to omit hundredths of seconds within SQL runner on date/time columns?
Nick M
 

Re: SQL Runner - 32 Bit

Postby Support Team » Wed Sep 21, 2011 5:50 am

Nick,

The program does not perform any kind of value conversion. It just get value using SQLGetData API call "as text" (SQL_C_CHAR flag) from ODBC driver and save it to output file.
I think you should play with session settings or add some conversion function to your SQL statement.

Igor, support team
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby Support Team » Wed Sep 21, 2011 5:57 am

Nick, please try this simple statement:

select CONVERT(smalldatetime,getdate())

Do you have milliseconds in this case?
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby Nick M » Wed Sep 21, 2011 2:53 pm

Just tried it and it worked perfectly! Thanks!!!
Nick M
 

Re: SQL Runner - 32 Bit

Postby Nick M » Wed Sep 21, 2011 3:13 pm

Actually, it seems I jumped the gun here. It appeared to be working since it removed the decimal part of the seconds, however, it lost the whole seconds as well. All times are in the right format hh:mm:ss but the seconds show up as 00 no matter what - 09/15/2011 09:37:00 PM.
Nick M
 

Re: SQL Runner - 32 Bit

Postby Support Team » Wed Sep 21, 2011 4:48 pm

The CONVERT function has three parameters for 'datetime' type.
Try to look at 3d. It describes required format.
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby Nick m » Wed Sep 21, 2011 5:18 pm

Right, I've tried using the various style ID's and no matter what it will output the time in hh:mm:ss.000 format.
Nick m
 

Re: SQL Runner - 32 Bit

Postby Support Team » Thu Sep 22, 2011 2:54 am

Nick,
Possible reason of the seconds lost is 'smalldatetime' type in our example. If you have 'datetime' or even 'datetime2' for 2008 server it should be Ok.
Support Team
 
Posts: 256
Joined: Sun Mar 26, 2006 3:48 pm

Re: SQL Runner - 32 Bit

Postby Nick M » Thu Sep 22, 2011 6:03 pm

Yeah small date time I found out defaults seconds to 00 regardless. Some things I have tried are:
Convert(datetime,MyColumnDatetime,various style ids)
convert(datetime,Convert(varchar(18),mycolumndatetime),various style ids)
convert(datetime,Right(mycolumndatetime,8),various style ids) brings back time as hh:mm:ss.000 where milliseconds are always zero.
Nick M
 

Re: SQL Runner - 32 Bit

Postby Nick M » Thu Sep 29, 2011 7:17 pm

Just an update: I have had no luck with any SQL query combinations. Any combination used will either lose seconds altogether, or default the milliseconds to 0 and leave them after the decimal. I did notice another program I've been using to extract the SQL has an option to format date/time outputs within the program itself. Unfortunately this program doesn't support command line arguments which I'm using your tool for. Do any of your other tools have a similar function (formatting date/time output when exporting to text file)?
Nick M
 

Next

Return to Free Tools

Who is online

Users browsing this forum: No registered users and 2 guests

cron