WebSQL Q & A - 01 Oct 1999


Answers From Microsoft

SQL Server has reported several errors related to the User ID probe and performance monitor. The error reports occur every 3 minutes after the probe has successfully logged in.

Message 18455 : Login succeeded- User: probe.

To remedy the problem, I ran a SQL Upgrade on the server, and now I get the error messages:

Event id: 4005; Source: perfctrs; Category: None
Load of INETMIB1.DLL failed. Make sure the DLL file is in the PATH. WIN32
Error number is returned in the data. Data = 0000: 7f 00 00 00

Event id: 1008; Source: perflib; Category: None The Open Procedure for service "TCP/IP" in DLL "Perfctrs.dll" failed.
Performance data for this service will not be available. Status code returned is DWORD 0. Data = 0000: 7f 00 00 00

What do these error messages refer to?

Probe is the account of the performance monitor you’re logged in with in SQL Server 6.5. If you turn on Successful audit logins when configuring your server in Enterprise Manager, these messages regularly appear. They aren’t indicating errors; the messages refer to a problem with your Windows NT performance counters. See TechNet or the Microsoft support Web site (http://support.microsoft.com/support) for more information on this error.

I’m running SQL Server 7.0 with 1.3GB of physical RAM, a virtual memory of about 300MB, and the default Dynamically configure the SQL server memory. It seems unlikely that the entire physical RAM is being used because the same applications run on other SQL boxes with only 650MB of RAM. I ran the Performance Monitor and found that the total server memory consumes only 368MB, and that 829MB are available. When SQL Server service starts, does SQL Server use any amount of memory that is available, or does it consume only the memory it needs to answer queries? And are the available bytes in a memory pool available to both SQL Server and Windows NT?

SQL Server uses only the memory it needs to answer queries. Unless you set a minimum memory amount that you need, the amount will grow over time if additional memory is available. Available bytes refer to the memory that is available to an entire operating system, such as NT and any applications, such as SQL Server.

In SQL Server 6.5, I wrote some triggers in the syslogins table, but when I upgraded from 6.5 to 7.0, the triggers didn’t transfer to SQL Server 7.0. When I tried to create a trigger in syslogins in SQL Server 7.0, an error occurs: "Invalid object name syslogins."

If I add a new login to the syslogins table, the login is inserted as a record between the previous login names in ascending alphabetical order of the login name (column name: name). The login ID (column name: suid) was changed during upgrade for the previous login names, so whenever I add a login, the user ID(suid) changes. Because I have an application that is linked with the syslogins table in the Master database, I seem to have problems with the syslogins in SQL Server 7.0. How do I solve these login ID problems and write a trigger in syslogins?

The suid isn’t a field used internally in SQL Server 7.0; only the view syslogins shows the suid, then it's a dynamically calculated value. The SID column is the proper value that matches entries in sysusers in each database. SQL Server 7.0 doesn’t allow triggers on system tables, so this is why you’re getting an error message.

I created a Data Transformation Services (DTS) package to transfer data from Access to SQL Server and saved the package as a file, as Listing 1 shows. Using the DTSPackage Object library, I can load the DTS file and execute it. But, because I have different clients on different machines, I need a way to tell the package SERVER=MYSERVER;DATABASE=MYDATABASE, etc. The database will always be the same, but the server name will always differ. I don’t know if I can change the database and server settings because of the datasources defined and saved in my package. How do I tell the package what database and server to transform the data to?

In your code sample after you load the package into your Visual Basic (VB) application, you have full access to the DTS Object Model. Because you have full access, you only need to reset the Connection object's DataSource property to the server you want to use. When you reset the DataSource property, the database and server that transform the data change. You need to name your connection (I use the name MyData for the example); then you can use Listing 2 to reset the connection.

I’m trying to implement a solution very similar to Don Awalt and Brian Lawton’s examples in the articles "Unleash the Power of DTS" (May 1999) and "Pump Up the Power of DTS" (June 1999), except that the source is a text file rather than another SQL Server database. I can’t find any documentation on the DTS COM object that is specifically about creating a new package connection to a text file (e.g., OLE DB providers string, delimiters, file location, record delimiter). Where might I find this information?

From the DTS Object Model standpoint, the Connection object's basic properties are the same regardless of the data provider. Microsoft is using SQL Server as the provider, SQLOLEDB.1, and the providers differ (each provider has a separate set) in their Properties collection where you can find the specifics for your driver. You’re right; I don't believe anything is published on the OLE DB text file driver that ships with DTS. If you don't want to try to reverse engineer the properties, you might try Microsoft's SQL Server news groups, specifically the microsoft.public.sqlserver.datawarehouse news group. Also, see SQL Server Magazine’s discussion forum at http://www.sqlmag.com/ Resources/Forums/.

I’m unable to import a simple fixed-length text file into an existing empty SQL Server 7.0 table that I designed in Enterprise Manager. My text file contains only 10 columns: Cols 1 through 4 are char data types and cols 5 through 8 are money data types. In column 9, which contains a six-character date in the source file, I get the following error message when I use the DTS import wizard:

DTSTransformCopy: ValidateSchema failed; see Extended Error information.

TransformCopy 'DirectCopyXForm' validation error: Source column too narrow to contain a valid value of destination column's datatype for column pair 9(source column 'COL009' (DBTYPW_STR), destination column "TranDate'(DBTYPE_DBTIMESTAMP)).

I unsuccessfully tried bulk copy program (bcp) and BULK INSERT. Besides using the char data type, can I use any date-type data type for this column if the data is only six characters (mmddyy)? (Y2K noncompliance isn’t a problem in this application.)

If you want to use DTS, you need to use an ActiveX transformation rather than a copy for the column. CDate in ActiveX allows date formatting. Also, look at bcp with a format file (SQL Server Books Online–), which might be easier because your data is a simple fixed-length file; bcp handles this type of data well.

In SQL Server 6.5, I often get Open Data Services (ODS) error code 17832. I’ve sniffed the entire network and I can’t find a problem. Now the event log is full of ODS messages with the same error code. What can I do?

The ODS messages are just informational; you can’t do anything to stop them from appearing. Look at article Q109787 at http://support.microsoft.com/ servicedesks/msdn/ for more detailed information about this error. Article Q109787 says that a variety of Microsoft SQL Server communication-related errors are possible and that, in general, these don’t indicate a problem with SQL Server, but rather a network, network configuration, or a client application problem.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.