2

I have a Dataman high speed ID scanner which I need to communicate with a SQL Server 2008 database. There are a few different ways for it to communicate, but the only one that isn't an industrial protocol is Telnet. How can I have the database communicate with the scanner over Telnet?

If you have any other suggestions for how to communicate with it, I'd love to hear them too.

SaintWacko
  • 854
  • 3
  • 14
  • 35
  • I agree with Steve, I would build an application that acts as an interface between the database and the scanner. I'm not sure why you think the code has to be inside a stored procedure... – Aaron Bertrand Apr 24 '12 at 03:24
  • @AaronBertrand I'd prefer to stick with the languages I'm already using (Access VBA and SQL Server 2008) instead of adding in a new one. I'm also trying to keep as much code on the server as possible, for efficiency. – SaintWacko Apr 24 '12 at 17:10
  • Careful that efficiency doesn't come at the cost of maintainability – Steve Mayne Apr 24 '12 at 17:24
  • Well, that's why I was hoping to find an easy way to create a Telnet connection on the server :) – SaintWacko Apr 24 '12 at 17:28

3 Answers3

3

You will need to write an application which uses Telnet to talk to the scanner, and can then read/write from the database accordingly.

You could, for example, write something in C# (using Visual Studio) and use the following library for Telnet access:

http://www.codeproject.com/Articles/19071/Quick-tool-A-minimalistic-Telnet-library

If you really, really have to get the database to execute the code, then you might be able to write something in C# which is then picked up using CLR integration within SQL Server 2008. But I would recommend keeping your application separate from the database (for ease of development and testing purposes).

Steve Mayne
  • 22,285
  • 4
  • 49
  • 49
  • Is there no way to do it as a stored procedure within the database? – SaintWacko Apr 23 '12 at 22:41
  • I have updated my answer - I'm afraid there's no integrated way to do this (that I'm aware of). – Steve Mayne Apr 23 '12 at 22:50
  • 1
    I'm trying this out. We'll see how it works. Have to learn a bit about C# first, though – SaintWacko Apr 25 '12 at 20:51
  • Spent the last week learning all about SQLCLR and C#. Everything's working, I just have to get the assembly to stop spamming the table with nulls. Well, they're not actually nulls. I don't know what they are. – SaintWacko May 04 '12 at 18:50
1

You can create a telnet client in a SQLCLR stored procedure and then call the CLR procedure from a regular TSQL stored procedue. SQLCLR is not very commonly used, but it is more robust than many people assume, and I've talked to teams that have had good success with it.

One thing to keep in mind is that, if you go the SQLCLR route, you'll be writing the same .NET code that you would have written if you had decided to take the advice of other answers to this question and implement an external utility that pushes data into SQL server. The only difference is that with SQLCLR the stored procedure can actively trigger the telnet interaction.

SQLCLR stored procedures require some special techniques, but Visual Studio database projects make it pretty easy to create one.

Paul Keister
  • 12,851
  • 5
  • 46
  • 75
0

Well SQL Server can't do this natively, so you're going to have to go outside of your comfort zone a little in any event. Also by "on the server" do you mean on the same physical server where SQL Server is running, or do you mean "inside the SQL Server instance"?

You could certainly call a local PowerShell script from xp_cmdshell inside a stored procedure, or as part of a SQL Server Agent job step if you need it to be asynchronous (and if you're not running Express Edition, which doesn't have Agent). Here is a PowerShell script that wouldn't take ultimate PowerShell mastery to adapt to your needs:

Automating Telnet with PowerShell

In order to call this from within a stored procedure, you'll first need to be sure that xp_cmdshell is enabled:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
GO

As a caution, a lot of people view exposing xp_cmdshell as a bird-flu-like pandemic. To me it's all about controlling access to the machine not about controlling what the machine can and can't do to fulfill your business requirements.

Here is a quick sample of consuming the output of whatever you send to xp_cmdhsell. This is just a standard command-line call, so you'll have to adapt it for calling PowerShell scripts, but it should work largely the same:

CREATE TABLE #tmp(i INT IDENTITY(1,1), x VARCHAR(2048));
INSERT #tmp(x) EXEC master..xp_cmdshell 'dir C:\Users\';
SELECT x FROM #tmp ORDER BY i;
DROP TABLE #tmp;

Results:

 Volume in drive C has no label.
 Volume Serial Number is 50D3-008B
NULL
 Directory of C:\Users
NULL
01/10/2012  09:20 AM    <DIR>          .
01/10/2012  09:20 AM    <DIR>          ..
01/19/2011  11:54 PM             1,444 SomeFile.txt
.... other files...
               3 File(s)       28,918,500 bytes
              18 Dir(s)    19,367,292,416 bytes free
NULL

But once you've spent more than an hour messing with this to get it to work, I think you're really better off writing an application that either runs as a service or on an interval, checks a queue table for new telnet processing to, does the work, then writes the results to SQL Server. Your RDBMS shouldn't be treated like a batch scripting host IMHO.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Will that be able to receive data from the other end of the telnet connection and return it to the stored procedure? I've never used PowerShell before, not really sure how it works. – SaintWacko Apr 24 '12 at 17:33
  • You can insert the output into a #tmp table, which is a typical kludgy workaround. I'll add an example to the post. – Aaron Bertrand Apr 24 '12 at 17:37