How can I create an order in SQL that accepts "0001-01-01" in a date field type without generating a conversion error?
SQL Server dates start at January 1, 1753. To use an earlier date, you need to use a char or numeric data type and do the appropriate conversions yourself. For additional information about the datetime data types, see SQL Server Books Online (BOL), under Accessing and Changing Data, Transact-SQL Syntax Elements, Using Date and Time Data.
Forty SQL Server client terminals are accessing SQL Server 6.5. The server becomes unable to open SQL Enterprise Manager, and the system hangs. (I don't have problems connecting through ISQL/w.) I checked the networking and protocols in use at both ends. I use standard security and everybody has sa permissions.
Sometimes users can't access Enterprise Manager through the server, but restarting the server solves the problem. So if users want to transfer or create a database through Enterprise Manager, they have to restart the server and affect other users’ work. I checked the sp_configure parameters. I have 60 connections with 64MB of RAM, which is a lot even if one connection takes 50KB! No replication or other utility is running on the system. Also, I checked the virtual memory default.
You might be running out of user connections. Look at sp_configure for user connections. When you're in such situations, if you can, log in as sa and run sp_who to see how many connections are open. If you run out of connections, SQL Server won't let anyone but sa log in. Each connection takes about 50KB of RAM away from SQL Server, so be careful not to increase the value too much. (How much you can increase the value depends on the installation.)
Besides using the DBCC CHECKIDENT(tablename) command, how can I check for an identity column in a table?
You can run the following statement:
SELECT OBJECTPROPERTY(object_id('mytable'), 'TableHasIdentity')
This code tells you whether a given table has an identity column (0 = No, 1 =Yes). Note that this solution works only in SQL Server 7.0. In SQL Server 6.5, you can look at the Syscolumns table directly with the following code:
USE pubs GOCREATE table example (c1 int not null identity, c2 char(5) not null)
SELECT name, status / 128 FROM syscolumns WHERE id = object_id('example')
The results will look like Table 1:
| TABLE 1: A Look at the Syscolumns Table | |
| name | |
| ---------- | ---------- |
| c1 | 1 |
| c2 | 0 |
| (2 row(s) affected) | |
I've had problems with SQL Server 6.5 since I loaded Service Pack 5a (SP5a). Can I uninstall this service pack?
See http://support.microsoft.com/support/kb/articles/q216/4/21.asp for how to uninstall SP5 or 5a.
Where can I get SP1 for SQL Server 7.0?
The beta release is available to download at ftp://ftp.microsoft.com/ softlib/mslfiles. This service pack contains only bug fixes reported since SQL Server 7.0 began shipping. SP1 includes no new features. A complete list of bug fixes will be available when SP1 is released.
If I attend only one conference every year to learn everything about SQL Server, which conference do you recommend?
At http://www.sqlpass.org, you can find information on COMPASS '99, which will take place September 29 through October 2, 1999, in Chicago. Microsoft’s SQL Server development team is putting effort behind this event, so I support this conference. Also, SQL Server Magazine is a sponsor of COMPASS ’99 and several authors, including Kalen Delaney and Brian Moran, are speaking at the conference.
What happens when SQL Server receives a signal to shut down from the Service Control Manager?
If you log in with Transact-SQL (T-SQL) and issue a shutdown command, you wait for all existing users’ work to complete gracefully. No new logins are allowed. This is a nice shutdown. If you issue a shutdown request from the Service Control Manager (Net stop mssqlserver), abort all open work (kick out the users), issue a checkpoint in every database, and then shut down, this is an orderly shutdown. Again, no new logins are allowed. If you issue a shutdown with no wait in T-SQL or press Ctrl+C when SQL Server is running in a command window, the shutdown aborts all open work and stops. No checkpoints and no new logins are allowed. This approach is a rude shutdown.
SQL Server 7.0 let me compile a stored procedure that included an update statement that updated a table that does not exist in the database! I checked this several times by changing the table names to different non-existent names, and the compile still happens. Will SP1 fix this behavior?
This product feature is known as deferred name resolution. SQL Server 7.0 doesn't resolve object names to IDs until runtime. SQL Server 6.5 resolved names at create time; 7.0 defers resolution until runtime. So if you create a stored procedure that accesses a table that doesn't exist and then create the table, the stored procedure works in SQL Server 7.0. For example, if you write a stored procedure that references a temp table that is created outside the stored procedure, you won’t get errors at create time now because deferred name resolution won’t check for the table’s existence until runtime.
The following SQL statements in SQL Server 6.5 work differently than in SQL Server 7.0.
DROP PROC abcxGO CREATE PROC abcx AS CREATE TABLE #x(a int,c int)
INSERT #x VALUES (1,2) SELECT * FROM #x GO CREATE TABLE #x(b int) INSERT #x VALUES (1) EXEC abcx SELECT * FROM #x
These SQL statements give results in SQL Server 6.5 for both of the select statements, whereas SQL Server 7.0 gives an error. Does SQL Server 6.5 treat temp tables differently from the way SQL Server 7.0 treats them?
SQL Server 6.5 used to execute create tables at compile time and persist bound-query trees on disk. Because SQL Server 7.0 does neither, the binding to temp tables works in a slightly different way. While deferred name resolution takes care of query binding for most cases when temp tables are not yet created, the example here does not work because both tables of the same name are visible to the abcx scope as they always have been. Query binding sees the wrong table (the other one doesn't exist yet!) and fails with the above error. So, you need to change your code.
I ran DBCC CHECKALLOC TempDB and got the following error message.
MSG: 2545 Level 16 State 1 Table Corrupt: Extent#=552 on allocation pg#=512 was used but object ID was 0.
I could not find an explanation in Books Online (BOL) and Help. What does this error mean?
It sounds like some corruption occurred. From what the message says, you deallocated a table but didn't deallocate the space. To solve this problem in either SQL Server 6.5 or 7.0, simply shut down and restart SQL Server to create a new tempdb. If this problem happens frequently, look at your hardware to verify that it's working. If you’re using SQL Server 6.5, make sure you have the latest service pack installed (SP5a).
