Web Exclusive Q & A

Answers about troubleshooting client connections, converting Paradox projects, and importing image data.

Richard Waymire

July 31, 1999

7 Min Read
ITPro Today logo in a gray background | ITPro Today

I installed Outlook 98 on a SQL Server 7.0 server to set up the Exchange mail profile for SQLmail use, but the Named Pipes client connection was broken. When I tried to register this server from my workstation, I got the error "Client unable to established connection [connection open[createfile()]]." When I typed net view \sfsql12 as a DOS command, I got the error "System error 51 has occurred. The remote computer is not available." When I tested makepipe, readpipe /Ssfsql12 /D.., I got the error "Failed to open pipe, status=51." I can ping this server, and register it using a static IP address from the client workstation. I searched different resources for system error = 51, but had no luck.

The error message implies that the server wasn't found. If you think it's OK in WINS and DNS (i.e., if you can start and run \sfsql12c$), reapply Service Pack 5 for Windows NT. I've had this problem before and reapplying SP5 fixed the named pipes problem. If \sfsql12c$ works then your name resolution mechanisms (WINS and DNS) are probably OK and the problem may not be a networking problem.

I am trying to convert a Paradox 7 project that prints claim checks. My problem is that for certain checks, the logos and signatures need to be printed. Can you provide a code example on how to load logos and signatures into a SQL Server 7.0 database table? I tried using bulk copy program (bcp) to load a logo into an image field. The result was 937 rows for a 9KB file. I looked at the Pub database, but I don’t understand how to work with the logofield.

You might want to load the images with the appendchunk APIs from a program. Details on how to do this are in the Microsoft Developer Network (MSDN) library. You can also use bcp to load images, as you see in the following example Administering SQL Server, Importing and Exporting Data, Copying Data Using bcp or BULK INSERT, Common bcp issues, Importing image data from SQL Server Books Online (BOL).

Importing Image Data


It is possible to bulk copy a data file as image data into Microsoft® SQL Server™. The command to load the data file Test.doc into the bitmap table in the pubs database using the bcp utility is:

bcp pubs..bitmap in test.doc -Usa -Ppassword -Sservername

bcp prompts:

Enter the file storage type of field c1 [image]:Enter the prefix length of field c1 [4]: 0Enter length of field c1 [4096]: 5578Enter the field terminator [none]:

In this example, the data file will be loaded into column c1 and 5578 is the length of the data file.

Using the BULK INSERT statement, a format file needs to be created first and then used to provide the format information. To create the format file, use the bcp utility:

bcp pubs..bitmap out c:bitmap.txt -Sservername -Usa -Ppassword

The bcp utility prompts for the file storage type, prefix length, field length, and field terminator of each column of bitmap. For the c1 column, the values are listed in this table.

PromptValue

File storage type

image

Prefix length

0

Field length

5578

Field terminator

none



The bcp.fmt file:

7.011 SQLBINARY 0 5578 "" 1 c1

Using the BULK INSERT statement to bulk copy the Test.doc data file into the bitmap table in the pubs database, execute from a query tool such as SQL Server Query Analyzer:

BULK INSERT pubs..bitmap FROM 'c:test.doc'WITH (FORMATFILE = 'c:bcp.fmt')

Note: It is not possible to bulk copy data into text, ntext, and image columns that have default values.

When I run SQL Server 7.0 Enterprise Manager on my client machine, an HPx86 AT- compatible with 64MB RAM, the response time of the interface is extremely slow. Also, when I run with a Data Transformation Services (DTS) package to copy schema and data from one database (about 15MB) to another on the same server, my machine's hard disk thrashes away. This process takes nearly two hours to do what would have taken SQL Server 6.5 Enterprise Manager about 5 minutes. The SQL Sever 7.0 installation is on similar hardware with 128MB RAM. No major applications that could create such performance are running on either machine. Can you explain why I have bad performance?

The database scripted is in SQL Server 6.5 or 6.0 compatibility mode; turn it to 7.0 compatibility mode. This dramatically speeds up the scripting. Microsoft is aware of this performance problem and is working on a better solution.

Do I need to work with Microsoft Search Service (SQL Server 7.0) or with Index Service 2.0 (Windows NT 4.0 Option Pack) to search text? What is the difference? Is having the data in files or in databases better when you search? I have read two white papers about this, but I haven’t seen anything about the difference.

They are complementary technologies. However, the version that comes with SQL Server 7.0 is the latest version of Search Service. The Index Server can't search data stored in SQL Server, but the full-text Search Service, which uses the Index Server Engine, can.

So, if you want to use SQL to retrieve data and join information from the text searches with other data in your database, using a SQL Server full text search makes the most sense. If you just want a feature such as find in files, then use the Index Service.

If you install Microsoft Terminal Server 4.0, and then want to install SQL Server 7.0 on it, will SQL Server 7.0 have a compatibility problem on Terminal Server 4.0?

SQL Server 7.0 Service Pack 1 introduced support for Microsoft Terminal Server (with Service Pack 4). Please see the Service Pack 1 readme.txt file for additional information.

What are the prerequisites and procedures for access to a SQL Server located 30 kilometers from our site, with an Internet connection at both ends and Windows NT and SQL Server installed at both ends. I want to access SQL Server, remotely, so I can use the database and tables on that server to solve day-to-day problems and execute remote procedures as required. Can I connect to the remote SQL server to carry out DBA activities?

As long as you have network connectivity, you can fully administer SQL Server remotely. I administer SQL Server at my office by dialing in from home over a 28.8 connection and it works fine (albeit a little bit slowly).

My SQL Server 6.5 server computer has two Pentium II 266MHz processors and 128MB of RAM, and has a RAID 5 configured with a Mylex controller (cache 8MB). With Borland/Inprise tools (Delphi, Paradox, Interbase), I finished my first Delphi application that connects to a SQL Server database; users have to provide a login to connect to the SQL Server databases. I created a main group of users, which has all permissions on all objects in the database. Each user can connect to the SQL Server database with my application, which provides one of the group user names (only the same in this case). The first step, logon, is always successful. Then the application prompts the user for another logon (I store and manage all user permissions directly in my database tables.) This is the second step and final logon, which uses two views and one stored procedure.

For the first test of my program, I tried to log in to my application (the second-step logon must be successful) on several client computers. At the eighth computer (the application is logged on successfully on the first seven computers and still remains on the screen), when I submit the second-step logon, the server gives a SQL general error message.

When I quit the application on one of the first seven computers, the eighth computer can log in. If I launch the previously quit application, I get the same SQL error message. I don't know why this occurs. Is there any resource allocation problem?

The value of the user connections is the problem. In SQL Server 6.5 the value is static.

The default value of 15 specifies a maximum of 15 connections into SQL Server. Because SQL Server and the SQL Executive use six or so, what's happening to you makes sense. Bump this number up until you don't have this problem. But watch out, because each user connection uses up to 24KB. Don’t forget to stop and restart SQL Server after you change the value.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like