Q: I installed SQL Server 7.0, but now I can't see the master, model, or msdb databases when I run Enterprise Manager, and I can't see the system tables in user databases. What's the problem?
You configured your SQL Server system to not display system objects. To change this setting, right-click the server name in Enterprise Manager and select Edit SQL Server Registration Properties. Select the Show system databases and objects check box.
Although we appreciate that we can choose to see or not see system tables and objects in SQL Server 7.0, we would like Microsoft to extend this granularity. For example, we'd like the option to let users see the system objects, such as the sysobjects table.
Q: SQL Server 6.5's datetime data types make date formatting and date arithmetic difficult. Why didn't Microsoft improve this problem in SQL Server 7.0 or add functions such as LAST_DAY (i.e., last day of the month) and NEXT_DAY (i.e., next weekday)?
We agree that SQL Server 6.5's datetime data types are laborious. For example, SQL Server 6.5 forces you to use the DATEPART() function to perform simple date arithmetic, such as adding 5 days to an existing datetime value. However, wholesale changes in data types wreak havoc with backward compatibility. Microsoft improved this functionality slightly in SQL Server 7.0. The following SQL Server 7.0 code is an example:
DECLARE @datevalue datetime SELECT @datevalue = "1/1/99" PRINT "Let's add 5 days" SELECT @datevalue + 5 PRINT "Now let's add 5.25 days (or 5 days and 6 hours)" SELECT @datevalue + 5.25
SQL Server 7.0 produces the following output:
Let's add 5 days 1999-01-06 00:00:00.000 Now let's add 5.25 days (or 5 days and 6 hours) 1999-01-06 06:00:00.000
As the output shows, SQL Server 7.0 lets you use the addition operator (+) to add time (measured in days) to an existing datetime value. You can also use the Transact SQL (T-SQL) DATEADD command to perform this function, but the simple addition operator is easier to code.
In addition, GETDATE() is one of our favorite functions. You can use it to print the current date and time whenever you print a report, perform comparisons, and timestamp audit trail data. You can also set GETDATE() to be a default value for data entry.
Q: I tried the following method to join a SQL Server 6.5 system with a SQL Server 7.0 system (I have systems administrator—sa—permissions). From the SQL Server 7.0 system, I ran sp_addlinkedsrvlogin. I tried FALSE and TRUE for @useself to create the same login for both servers. I ran sp_addlinkedserver, and I upgraded the SQL Server 6.5 system's stored procedures catalog. However, when I try to perform a distributed heterogeneous query, I get the following error message: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. \[OLE/DB provider returned message: \[Microsoft\]\[ODBC SQL Server driver\]\[Named Pipes\]ConnectionOpen (CreateFile()).\] \[OLE/DB provider returned message: \[Microsoft\]\[ODBC SQL Server Driver\]\[Named Pipes\]Specified SQL server not found.\] Can you tell me what I'm doing wrong?
The best way to tackle this problem is to think about what SQL Server is trying to accomplish and walk through each step to discover what might have gone wrong. In this case, you might not understand the new linked-servers feature, but a distributed heterogeneous query performs many of the same steps that more familiar types of queries perform.
This problem is difficult to diagnose without access to the servers, but NetLib configuration or Windows NT-level account security conflicts are often the culprit. (For more information about NetLibs and logins, see SQL Server Q&A, November 1997.) To simplify the problem, we'll refer to the server running the query as SourceServer and the linked server as TargetServer. The error message tells you that you're using a Named Pipes connection and that the SQL Server 7.0 system can't see the SQL Server 6.5 system on the network.
For the distributed query to run successfully, both servers must be running Named Pipes. In addition, the account running the SQL Server query must have permission to authenticate to the NT Server service on the physical machine that is hosting TargetServer. SQL Server installs Named Pipes by default, so we assume that you have this important NetLib installed on both SQL Server systems and that Named Pipes isn't causing your problem.
If Named Pipes isn't at fault, the account running the query over the TargetServer connection is suspect. When SourceServer runs this query, it impersonates the NT account that runs the MSSQLServer service on SourceServer. Thus, you can narrow the problem down to two conclusions: SourceServer is running MSSQLServer under the LocalSystem account, or SourceServer is running MSSQLServer under a real NT account and that account doesn't have permission to connect to the NT Server service running on TargetServer. The LocalSystem account can't see the network.
In this case, the MSSQLServer service on SourceServer is running under the LocalSystem account. To solve the problem, you need to change the startup account to a domain account with permissions on the remote machine.
Q: I used SQL Server 7.0's Data Transformation Services (DTS) package-design wizard to create a DTS package in Enterprise Manager. I accepted the default naming conventions for my connections, but I want to change the connection names to make the package more self-documenting. How can I change the connection names?
DTS and the DTS package-design wizard are cool new features of SQL Server 7.0. However, the tool's simple gotchas, such as the inability to rename your connection names, are annoying. Fortunately, you can work around these limitations.
For example, suppose you're building a simple package that exports data from the authors table in the pubs database to a flat file, and you don't spend much time documenting the package. To keep the package simple, you accept the default connection names, which come from the name of the OLE DB provider that your connection is based on. In other words, the default connection names don't provide much information about the connection source. When you finish your package, your boss demands that you make it more self-documenting and make it adhere to corporate naming conventions.
The solution seems simple—rename the connection. However, SQL Server 7.0's DTS package-design wizard doesn't let you rename connections. The simplest solution is to use the method in the following example to create a new connection.
First, right-click the offending connection and select Properties. Select the New connection option in the Connection Properties dialog box, which Screen 1 shows. Type the new name in the New connection text box, and click OK. Next, the system asks Do you want the transformations that have been defined using this connection to be reset? Click No. Click No again at the following prompt, and the renaming process is complete.
