Once more again I’m confronted with a situation where I needed a SQL, but I don’t have direct access to the data. The machine I’m able to run some stuff on is not only insanely out of date (yay!) but doesn’t have enough disk space for even something like SQL Server 2000.
Enter SQL 4.21a
I “installed” 4.21a on this 32bit 2003 server in much the same way I transplanted 4.21a onto Windows 10. However I did use the srvany utility to load up the SQL Server service, much like how I used it to run an instance of Qemu in the background elsewhere. Now I have my intermediary SQL Server running like a normal service, and set a password for the SA user.
Now for the fun.
First be sure to set your target database for ‘bulk/load’ and I’d also set it for ‘truncate log on checkpoint’. If you don’t set the bulk/load then you cannot BCP data into the database.
Using the SQL Explorer tool I could view the tables I wanted, and export them as ‘SQL CREATE’ giving me the table layout. I then quickly converted them into something acceptable for 4.21a. Now it’s a matter of establishing a connection to the old server from the new.
First I tested with the ISQL command. I needed to copy the DLLs DBMSSOCN.DLL & NTWDBLIB.DLL into the directory to get the command to fire up. Since my strategy here is to do a BCP dump/BCP load the first thing I need to do is purge the data.
isql -Usa -PPASSWORD -S192.168.1.42 -Q"delete network.dbo.firewall_mapping"
This of course assumes that the server address is 192.168.1.42 and that in this case I’m deleting the firewall_mapping table from the network database. If you’ve made it this far that means we are 1/4th of the way there!
I found this ‘one trick’ to get the BCP command from the SQL 4.21a tools to connect to the 2016 server and dump the table as a trusted connection. I’m not sure how much longer this kind of thing will work, but I was pretty amazed it did. I didn’t even bother trying to see if the 4.21a BCP tool could read a 2016 BCP dump. Maybe it would if you keep the formatting the same, but I find ‘like to like’ much easier. I renamed the old BCP.EXE to BCP42.EXE so that they won’t collide in any way causing weirdness. At the same time I run them from a directory that is NOT in the system path.
bcp42 "[Network Database].[dbo].[firewall_rules]" out c:\temp\1.csv -t, -r= -P
The notation looks weird, as my source database name has a space in it. This initially caused endless frustration, but it was just a matter of using the fully qualified name, which is in quotes
"[database].[role].[table]"
I set the field delimiter as a comma, and the row terminator as an equal sign. I tried not setting it but I was getting ‘spiraling data’ as it was not picking up the end of row correctly at first.
The first time you run the BCP without a format file it’ll walk you through the specifics of the fields. I just blindly accepted the defaults, and saved the file as firewall_rules.fmt . Now on subsequent runs, I can run the export like this, which uses the saved formatting:
bcp42 "[Network Database].[dbo].[firewall_rules]" out c:\temp\1.csv -t, -r= -P -ffirewall_rules.fmt
Great so if everything is going well, we have no exported our data! Now the next step is to import the data into our old server. Since we have that format file, this “should” go pretty smoothly. Notice the server is an IP address which implicitly has it connecting by TCP sockets, not named pipes. As such there is no implicit ‘Trusted connection” as there was when talking to the local 2016 server.
bcp42 network.dbo.firewall_rules in c:\temp\1.csv -Usa -PPASSWORD -S192.168.1.42 -t, -r= -ffirewall_rules.fmt
Naturally change PASSWORD to whatever password you have for the SA user.
Starting copy... 1000 rows sent to SQL Server. 1892 rows copied. Network packet size (bytes): 512 Clock Time (ms.): total = 2216 Avg = 1 (853.79 rows per sec.)
And there you have it, all being well you’ll see the program update every 1,000 rows as it inserts data.
Originally I wanted to use the data transformation wizard thing (whatever they renamed DTS to) however the ODBC is limited to the newer .NET 4 stuff, which won’t use the old SQL Server 6.5 ODBC drivers. I really didn’t think the SQL Server 4.21a BCP command to work on a modern server against a new(ish) version of SQL Server, but it did!
I guess you could neaten it up with a command file to drop tables/re-create if you wanted, or at the least delete data/checkpoint and set the load options, dump/load data, and then turn off the load state for the database. I’m not doing reports or anything fancy, just visualizing data as they say.
Although things like ODBC have drifted out, it’s still kind of interesting that ancient BCP can still communicate over named pipes as an implicit trust.
