Microsoft SQL linked servers - a couple of gotcha's

These are very easy to set so that one instance of SQL server can access another however there were a couple of traps that caught me out:

1) You need to set up a ODBC connection for the server (for my simplicity I have used the name for the server as the data source name)

2) You need to do both a linked server stored procedure and a linked server login

3) You need to use a fully 4 part qualified name to get a result e.g. select count(*) from tiger1...all  will fail whereas select count(*) from tiger1.funds.dbo.all will work.

Here is my test code for setting them up - the sql server name is tiger1.  I haven't shown the ODBC setup.

EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname=N'tiger1',@locallogin=NULL
EXEC sp_dropserver   @server='tiger1'
EXEC sp_addlinkedserver  
   @server='tiger1',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='tiger1',
   @catalog='Funds'

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'tiger1',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'sa',
    @rmtpassword=''
exec sp_linkedservers

select count(*) from all
select count(*) from arundel01.Funds.dbo.all

Service is now reliable

(2008-07-17) The service is now running well.  The code crashes or timesout every few days but the error handling below handles the exception and it just keeps on going. My Windows service is still not reliable.  After a few days it seems to just stop in a normal fashion.  I suppose the only thing to do is to keep monitoring.  It currently looks like this (today I have added an error message to exception handling and moved the sleep to be inside the exception handler):

#
# This is the file to run as a daemon.

LOG_FILE = 'C:\\daemon_error.log'

begin
  require 'win32/daemon'
  require 'process_files'

  include Win32

  class DemoDaemon < Daemon
    def service_main
      File.open(LOG_FILE, "a"){ |f| f.puts "Service 2008-07-07 is running #{Time.now}" }
      s = ProcessFiles.new()
      while running?
        begin
         s.getAndProcessOnce
         sleep 10
        rescue Exception => ex
           File.open(LOG_FILE, "a"){ |f| f.puts "Service had an exception #{Time.now} #{ex.class}:#{ex.message}" } 
        end
        #File.open(LOG_FILE, "a"){ |f| f.puts "Service is running #{Time.now}" }
      end
    end

    def service_stop
      File.open(LOG_FILE, "a"){ |f| f.puts "***Service responded to stop and stopped #{Time.now}" }
      exit!
    end
  end

  DemoDaemon.mainloop

rescue Exception => err
  File.open(LOG_FILE,'a+'){ |f| f.puts "***Service Failed #{Time.now} due to #{err.class}:#{err.message}" }
end

I will just have to wait and see if this works more reliably. I have waited and it is looking good! I have some plans to put in a drb client and then allow me to have a local ruby shoes interface to see how it is working.

Scripting Mathematica

Update 2008-07-02

i have been trying to script a mathematica notebook that I have created that creates documents.  I found it not as straightforward as it seems.

 

I started with at test notebook:

My method of getting this scriptable was to save as a .m file.

Then edit it with notepad by removing some of the comments (* to this:

(* ::Package:: *)

(* ::Input:: *)
(*This is a comment.*)

(* ::Input:: *)
(*a = {1,2,3}*)

(* ::Input:: *)
Export[NotebookDirectory[] <> "test.gif",Plot[Sin[x],{x,0,10}]]

(* ::Input:: *)
Exit[]
(**)

Then run it from a batch file like this:

F:
CD "\2008\Mathematica Auto"
"C:\Program Files\Wolfram Research\Mathematica\6.0\math"  <"F:\2008\Mathematica Auto\test.m"

When I ran this didn't work as I couldn't use NotebookDirectory so I had to make the directory explicit:

(* ::Package:: *)

(* ::Input:: *)
(*This is a comment.*)

(* ::Input:: *)
(*a = {1,2,3}*)

(* ::Input:: *)
Export["F:\2008\Mathematica Auto\test.gif",Plot[Sin[x],{x,0,10}]]

(* ::Input:: *)
Exit[]
(**)

And this did run although not very elegant and I don't have a solution for running complex notebooks yet.  When you try and load a notebook Mathematica says it wants a front end.  It may be easier to script this inside Mathematica front end.

Making the service a bit more robust

Windows Services series Update 2008-6-27

Having got the service up and running I wanted to be able to test the new service before running it as a service. I also needed to make it a bit more robust. This is a polled service, so every so often it will run and do something. I had my first version up and running it died but randomly after a few days (I guessed this was due to occasional problems in the guts of it eg FTP failures).

To be able to debug it easily I broke it into two parts. One file has all the guts of the service:

class ProcessFiles

def processOnce()
sleep(3)
end

end

This allows the code to be testing in a simple wrapper and run from the command line:

require 'process_files'

s = ProcessFiles.new()
while true
s.processOnce
puts "Slept for #{sleep 10} seconds"
end

Alternatively here is the a sample daemon code taken from the demo plus I added a timestamp and a comment on close:

LOG_FILE = 'C:\\test.log'
require 'process_files'

begin
require 'win32/daemon'

include Win32

class DemoDaemon < Daemon

def service_main
my_service = ProcessFiles.new()
while running?
begin
my_service.processOnce
rescue Exception => err
File.open(LOG_FILE, "a"){ |f| f.puts "Service had a failure at #{Time.now} error=#{err} " }
sleep 10
end
sleep 3
File.open("c:\\test.log", "a"){ |f| f.puts "Service is running #{Time.now}" }
end
end

def service_stop
File.open("c:\\test.log", "a"){ |f| f.puts "***Service stopped #{Time.now}" }
exit!
end
end

DemoDaemon.mainloop
rescue Exception => err
File.open(LOG_FILE,'a+'){ |f| f.puts " ***Daemon failure #{Time.now} err=#{err} " }
raise
end

Deploying a Windows Service

Just to say I have used InnoSetup in the past and it is again useful for packaging up all the files you need.  See also the Ruby on Windows blog.

Lookback Options payout

A look back option allows you to get a payout assuming perfect hindsight.  These have been used by Fung and Hsieg as a proxy for a perfect trend follower.  The payout function for an asset that varies over time is:

Changing blogging editors

Just giving Windows Live Writer a try out as I had problems with scribefire as I was trying to round trip edits.  So far so good.

Getting the load path right for Windows

I used a startup for my daemon like this

c:\bin\ruby\rubyw.exe c:\mydir\mydaemon.rb

However it didn't work as the default load path was not set up correctly and so it couldn't find the library files however this does work:

c:\bin\ruby\rubyw.exe -C c:\mydir mydaemon.rb

The -C option changing the working directory and so I could get my Ruby service running quietly in the background.

I will leave for another day the problem of getting spaces in the directory path - for the moment I just used a path without spaces.

Windows Service using win32-service and win32/daemon

Windows Services series Update 2008-6-13

Rubyforge has got some good documentation on creating a daemon so I will start with that. The first thing to do is to get the demo code which doesn't come in the gem install of win32-services but can be got by downloading the zipped code.

Run through the demo (on a local drive not a networked share as the networked shares are not visible to services) and see how you go. I extracted a small version and installed with the manual service installer to make sure I could build a service.

Here is a sample daemon code taken from the demo plus I added a timestamp and a comment on close:

LOG_FILE = 'C:\\test.log'

begin
require 'win32/daemon'

include Win32

class DemoDaemon < Daemon

def service_main
while running?
sleep 3
File.open("c:\\test.log", "a"){ |f| f.puts "Service is running #{Time.now}" }
end
end

def service_stop
File.open("c:\\test.log", "a"){ |f| f.puts "***Service stopped #{Time.now}" }
exit!
end
end

DemoDaemon.mainloop
rescue Exception => err
File.open(LOG_FILE,'a+'){ |f| f.puts " ***Daemon failure #{Time.now} err=#{err} " }
raise
end

So I copied the code to NetBeans and tried to run. I had installed win32-services (gem install win32-services), I was using Ruby 1.86 and not Jruby as the interpreter.

Make sure it has include Win32 (which is not in some early versions of the demo code) I get

daemon1.rb:28: undefined method 'mainloop' for #

With I get this due to trying to run the Daemon from the command line:

daemon1.rb:29:in `mainloop': Service_Main thread exited abnormally (Win32::Daemon::Error)

However if you create a service and then run it it will work see the following with sc.

C:\>sc create test1 binPath= "C:\ruby\bin\rubyw.exe -C c:\temp\testDaemon\lib\ main.rb" type= own start= auto
[SC] CreateService SUCCESS

C:\>sc start test1

SERVICE_NAME: test1
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
PID : 5972
FLAGS :
C:\>sc stop test1

SERVICE_NAME: test1
TYPE : 10 WIN32_OWN_PROCESS
STATE : 3 STOP_PENDING
(STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

C:\>

And looking at c:\Test.log you can see the results.

Style conventions

I am going to try and standardise my styles.

Source code should be like this:

puts ">Hello world<"

Commands I type in should be like this:

>hello.rb

Responses from applications should be like this:

>Hello world<

ps I am using ScribeFire and thanks to cfinke now the round tripping of editing preserves quoted characters.

Windows Service using SrvAny

Windows Services series

Microsoft have a good page on debugging Troubleshooting SrvAny using Cmd.exe which I found helpful.
Here is someone who has done the same thing for WEBrick as a Windows Service. (ps the old method of installing parameters seems to work for srvany on XP).

You can write a very small program and make it run.

File.open("c:\\simpleDaemon.log", "a"){ |f| f.puts "Service started #{Time.now}" }
#while true #Would normally use an endless while loop so that service would not finish
(1..3).each {|i|
sleep 3
File.open("c:\\simpleDaemon.log", "a"){ |f| f.puts "Service ran at #{Time.now}" }
}
File.open("c:\\simpleDaemon.log", "a"){ |f| f.puts "Service closed #{Time.now}" }

This can be run happily from the commandline or NetBeans as normal.

So now you can create a service using the command line;

c:>sc create test1 binpath= "C:\Program Files\Windows Resource Kits\Tools\srvany.exe"

or

c:>"\Program Files\Windows Resource Kits\Tools\instsrv" test1 "C:\Program Files\Windows Resource Kits\Tools\srvany.exe"

Now you have to arrange the parameters using Regedit to get the program to run.

However using SrvAny has a number of problems. If the program stops working then you have no way of knowing what the problems was due to. Also the service itself still appears to be running. The solution to this is to use the Daemon class from win32.

Services on Windows series

There seem to be a number of different ways to create services on Windows using Ruby. I am going to try some of them out and see how well each one works. I was using Ruby 1.8.6.
There are two parts to this. The first is managing the service process : creating,stopping ,starting etc
The second part is packaging a Ruby script so that it can be run as that service.

The approaches I will write about are:

• using win32-service to manage the service process and win32/daemon to create a process.
  
• using Microsoft NT Resource Kit instsrv.exe (service creation) and srvany.exe (will run a command)
  
• using Winserv
• using Firedaemon (looks nice but have to pay for each $35 pc)

My plan is to summarise here the strengths and weaknesses and then to write another page for each one on exactly how to make each one work.

Installing Taskr on Windows

I have had problems with the windows Task Scheduler not running applications and so looking for a more reliable replacement as well as something I could make more intelligent I choose Taskr. I started working through the installation script.

On installing:

2. Install the Taskr gem:

gem install taskr


I got this reponse:


C:>gem install taskr
Bulk updating Gem source index for: http://gems.rubyforge.org/
Successfully installed builder-2.1.2
Successfully installed markaby-0.5
Successfully installed picnic-0.6.3
Successfully installed reststop-0.2.0.50
Successfully installed openwferu-scheduler-0.9.16.1404
Successfully installed taskr-0.2.1
6 gems installed
Installing ri documentation for builder-2.1.2...
ERROR: While generating documentation for builder-2.1.2
... MESSAGE: Unhandled special: Special: type=17, text="&lt;!-- HI --&gt;"
... RDOC args: --ri --op c:/ruby/lib/ruby/gems/1.8/doc/builder-2.1.2/ri --title
Builder
-- Easy XML Building --main README --line-numbers --quiet lib CHANGES
Rakefile README doc/releases/builder-1.2.4.rdoc
doc/releases/builder-2.0.0.rdoc do
c/releases/builder-2.1.1.rdoc
(continuing with the rest of the installation)
Installing ri documentation for markaby-0.5...
Installing ri documentation for picnic-0.6.3...
Installing ri documentation for reststop-0.2.0.50...
Installing ri documentation for taskr-0.2.1...
Installing RDoc documentation for builder-2.1.2...
Installing RDoc documentation for markaby-0.5...
Installing RDoc documentation for picnic-0.6.3...
Installing RDoc documentation for reststop-0.2.0.50...
Installing RDoc documentation for taskr-0.2.1...


On checking my standard installation of Ruby I noticed that I hadn't
got the ActiveRecord installed. so when you try to start taskr you get
this:

C:>taskr
c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require': no such file to load -- active_record (MissingSourceFile)
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'
from c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:496:in `require'
from c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:342:in `new_constants_in'
from c:/ruby/lib/ruby/gems/1.8/gems/activesupport-2.0.2/lib/active_support/dependencies.rb:496:in `require'
from c:/ruby/lib/ruby/gems/1.8/gems/taskr-0.2.1/lib/taskr/environment.rb:34
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'
from c:/ruby/lib/ruby/gems/1.8/gems/taskr-0.2.1//lib/taskr.rb:17
from c:/ruby/lib/ruby/gems/1.8/gems/picnic-0.6.3/lib/picnic/cli.rb:107:in `load'
from c:/ruby/lib/ruby/gems/1.8/gems/picnic-0.6.3/lib/picnic/cli.rb:107:in `handle_cli_input'
from c:/ruby/lib/ruby/gems/1.8/gems/taskr-0.2.1/bin/taskr:35
from c:/ruby/bin/taskr:19:in `load'
from c:/ruby/bin/taskr:19

so I installed activerecord

gem install activerecord

And this time taskr did what it was supposed to and created a default configuration file which it put here

3 Run taskr from the command line. You can add the -h flag to see command line options. Also, on a Linux system you'll probably want to run Taskr as root, since by default is stores its configuration in /etc/taskr and its logs in /var/log/taskr.rb:
sudo taskr

(On Windows I was just running from the command prompt)

and I got this file:

C:\etc\taskr\config.yml

4 The first time you run the taskr command, Taskr will create a default configuration file for you in /etc/taskr/config.yml and it will then exit. Have a look inside this file and edit it to your needs. Taskr requires a SQL database, and configuration examples are provided for various database servers (MySQL, PostgreSQL, SQLite, etc.).

I wanted to run SQLite and have installed SQLite Manager into Firefox as a good management tool. I converted the file using unix2dos so that I can read it easily.
I installed the sqlite3-ruby gem

&gt;gem install sqlite3-ruby

which installed without any trouble. It refers to webrick HTTP server and I am assuming that is built in.

I changed the default path from

dbfile: /var/lib/taskr.db

to
dbfile: /etc/taskr/taskr.db

just to keep all the files together.

5 Before running taskr again, make sure that the database you've configured is ready to be used. For example, if you're using mysql, you'll have to create the database first:
mysqladmin -u root -p create taskr

Since I was using sqlite I just used the Firefox add in to create the new data base which happened very easily.

6 You can now run taskr again. You should see it start up, and you
should be able to access the web interface at http://localhost:7007/tasks.

but I got this:

So I downloaded sqlite3.dll from www.sqlite.org - just the dll sqlitedll-3_5_9.zip. I copied both the def and dll to c:\windows\system32 and had another go at running it. This time it said it had started on localhost:7007 and was logging to taskr.log (not sure where yet).

and now it is running:

Converting to a service

The next job is to convert this to a service. There are some notes on this is done for Linux but nothing at the moment for Windows.
My route (simplified) was as follows:

Looked at this guide to using the Windows Resource Kit (2003 for XP) including the link for downloading the service. you are using srvany to run the service. I found that:
a) using the parameters key works with the Windows 2003 service kit (it is like an additional level of hierarchy and compatible with the older 2000 srvany)
b) Log on as local system account and make sure Allow Service to interact with desktop is ticked to make sure you can see what is going on.
c) in the application string you can put all the parameters eg for testing I was using
C:\windows\service32\cmd.exe /k
rather than split into an Application string and a AppParameters string.

One more gotcha - I used a baby script but it had a reference to a mapped network drive and in a service that didn't work it died. However run like this I could see it dying:
c:\ruby\bin\ruby.exe c:\baby.rb
or even
C:\windows\service32\cmd.exe /k ruby c:\baby.rb


When a process dies then the service does not stop. If you wrote a script using the win32 utilities then you could create a much better behaved script.

Taskr started up well but died towards the end. Looks like more debugging is required to get it to run under windows. Need to find what exactly is causing it to die.