How to import a query to a text file

I had long time without posting in this blog but finally I have come up with a usefull tip. The first stop we are making in this travel is to answer a question: Why would you ever import a query to a text file?

There are multiple situations that will require you to send the results of a query to a text file, in example I could mention a recent experience. The company I work for has shut down a distribution center and decided to hand over all the logistic operations to a third party broker, they wanted to get directly connected to our ERP system databases as well as query and push transactions to them, obiusly my immediate answer was "No way!" so I decided to came up wit the idea of send text files thru FTP with shipping details, that was what these guys needed... maybe is not the best approach but I could not afford to have them screwing up the data in our ERP system either, I currently work with Solarsoft's IVp 5.2 and I swear to God that it is a really compelx system and has a very hard to understand database structure.

So, if you face a situation that ends with you having to download data from your SQL server to a text file, this is the place to look in.

What Tools you’ll need.
* A command prompt window
* SQLCMD command line tool

About SQLCMD

Sqlcmd is a client tool which allows you to connect to a SQL Server Database Engine instance and run script against it. You might be familiar with SQL Server Management Studio (SSMS) to run queries, SSMS uses the .NET Sqlclient but SQLCMD will use an oledb provider. If you require furher information about SQLCMD utilty, don't hesitate to visit this link.

Here are some switches and examples of SQLCMD. I did not post an explanation of every single switch used in this command but I am pretty sure that you will be likely use the following ones:

-E Will establish a trusted connection to an instance of SQL Server, this one uses the windows integrated authentication, very usefull if you work have an Active Directory domain in your network and want to ease the task of securing connections to SQL Server

-S This switch indicates the server and instance name to connect, you usually do it in a "servername\instance" fashion

-d Indicates the database name to connect.

-Q Runs a query and then exits SQLCMD, if you do not want to exit just use a lower case q.

-o Redirects the query results to a text file, this switch is the one we are interested on.

These are some examples of the above switches and SQLCMD in action

* SQLCMD -E -S "sqlserver\instancename" -d "dbname" - will connect to a sql server instance using trust connection and use the "dbname" database

* SQLCMD -E -S "sqlserver\instancename" -Q "select * from customer" -d "dbname" - will connect to a SQL Server instance using trust connection, use the "dbname" database, execute a query and then exit.

Finally, this last example will do de trick you want, send the output to a text file, in this example we will run a query, then send it to a text file and exit the utility

* SQLCMD -E -S "sqlserver\instancename" -d "dbname" -Q "select * from customer" - o "c:\textf.txt"

Now you can embed this command into scripts or whatever you think will fit the best, enjoy your new finding and happy coding!

Linking AS/400 from SQL Server

If you ever wanted to link your SQL Server with an AS/400 box, this is how to do so, follow these "easy" instructions and you are done.

If you want to set up a linked server with AS/400, first things first: You must make sure that your SQL Server has the OLE DB provider for DB2, you can get it for free with this link:

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/DB2OLEDB.exe

If you need to check that the OLEDB provider for DB2 is already installed, open your SQL Server Management Studio Console and browse to Server Objects\Linked Servers\Providers, once it is set up, it will be listed as DB2OLEDB.

Other thing you are going to need is an account in the AS/400 box, this will make SQL Server act as a client for the AS/400, be sure to give only the strictly necessary rights to that account, avoid granting open wide privileges to service accounts, like the one you are creating in your AS/400 in this point.

The next step is creating your linked server... finally! open a new query in your Management Studio Console by pressing CTLR+N and copy/paste the following commands, obviusly, you must change the <...> in each parameter accordingly

EXEC master.dbo.sp_addlinkedserver

@server = N'<...>', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'<...>', @provstr=N'Provider=DB2OLEDB;Password=<...>;Persist Security Info=True;User ID=<...>;Initial Catalog=<...>;Network Address=<...>;Package Collection=<...>', @catalog=N'<...>'

Here is a brief explanation of each parameter

@server= This will be the "internal" name to identify your linked server from others you already have.
@datasrc= this is your AS/400 host name
@catalog= This is the database in AS/400 you want to connect to. If you don't know which database is available, either ask to the AS/400 administrator or use the WRKRDBDIRE command, tipically the entry that has a *LOCAL value in the remote location column, is the database to specify in this parameter... Be Extremely carefull when using this command you can screw up the AS/400 and I would not blame its administrator if he wants to kill you after! maybe I would kill him for leaving commands like this available to anyone, so hands off!.
Password= The AS/400 password for the account created for your linked server
User ID= The AS/400 user ID for the account created for your linked server
Initial Catalog= same as @catalog
Network Address= the Ip Address for your AS/400
Package Collection= This is a somewhat equivalent to a schema in SQL Server, you must specify the package where the tables you need are located. If you don't know which package collection specify, use the AS/400 command WRKOBJ and take note of the name in the Library column

I hope this can resolve your problems linking SQL with DB2!