I'm having a network connections problem with my SQL Server 7.0 server, which runs on Windows NT 4.0. A Novell NetWare 3.11 server runs on the same physical network. To insert data from a text file that resides on the NetWare server to my SQL Server machine, I wrote a T-SQL stored procedure that uses the Data Transformation Services (DTS) Bulk Insert task. I experience no problems when I run the stored procedure by using the text file on a local NT hard disk, but the stored procedure fails when I run it against the source text file from the NetWare server. I mapped the NetWare share onto NT's O: logical drive, which is fully accessible to my login and appears in Network Neighborhood. The failure generated the following error message:
Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file 'O:\OLTDATA\oltm.01 could not be opened. Operating system error code 86 (The specified network password is not correct.).
Why does the stored procedure fail when I use the source text file from the NetWare server?
You've mapped the O: logical drive to your interactive login rather than to the service account's logical environment, so SQL Server doesn't see the O drive. For the stored procedure to work, you need to use Universal Naming Convention (UNC) paths so that SQL Server will know where the network resource is, or you can copy the file to the local SQL Server system.
How can I enter information in SQL Server 7.0 that spans many related tables?
In SQL Server 7.0 and earlier, you can create a view that updates multiple tables, then update one table at a time by referencing the view in the UPDATE statement. However, you must specify only columns from a single table in the UPDATE statement. (You can find documentation for additional constraints under UPDATE in SQL Server Books Online—BOL.) You can also create a stored procedure with parameters that might affect multiple tables, then modify each table one at a time through the stored procedure. In SQL Server 2000, you can use INSTEAD OF triggers on a view as another option.
My SQL Server 7.0's table keys are integers, but I'm thinking of changing the integer keys to universally unique identifiers (UUIDs) that are 35-byte varchar columns. What sort of performance hit would the switch create? Is SQL Server 7.0 optimized to handle UUIDs?
In general, the length of the column matters as much as the column type. So, upgrading from a 4-byte key to a 35-byte key will slow down some joins and increase the size of your non-clustered indexes.
Why does my inline or embedded SQL run faster than my stored procedures?
Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:
SELECT * FROM dbo.mytable
This technique helps you reuse plans and prevent cache misses.
Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at questions@sqlmag.com.
End of Article
Prev. page
1
[2]
next page -->
