Q14. How do I make a DTS package log to a text file?
A14. Open an existing DTS package. Right click any white area in the package and select Package Properties. Now in the error handling section on the General tab enter a path and file name in the box for error file. Click OK and save the package. Now when you execute the DTS package it will log the success or failure of each task using the task name the DTS assign to each task upon creation. When a failure occurs a description of the failure will be logged in that text file.
Q13. How can I tell what version of SQL Server we are running?
A13. Execute Select @@verion
For a more detailed report you can execute store procedure
master.dbo.xp_msver
Q12. How can I tell what ODBC drivers are available on the server?
A12. Execute store procedure master.dbo.xp_enum_oledb_providers
Q11. When I export my table from SQL Server using fixed width, my records are different lengths. Is the VARCHAR columns causing this?
A11. No, exported VARCHAR columns will be padded with spaces at the each of each column to the maximum length of the VARCHAR column. Most likely what's happening is that there are columns defined a nullable and SQL Server does not export null columns. In other words, if you had the following:
Id CHAR(02) NOT NULL
Name CHAR(10) NULL
Address CHAR(20) NULL
Record #1 Id 01
Name John Doe
Address 123 Someplace
Record #2 Id 02
Name Mary Smith
Address null
Record #3 Id 03
Name null
Address 456 Anywhere
Export File:
Record #1 Position 1-2 01
Position 3-12 John Doe
Position 13-32 123 Someplace
Record #2 Position 1-2 02
Position 3-12 Mary Smith
Position 13-32 end of record at position 12
Record #3 Position 1-2 03
Position 3-12 456 Anywhere
Position 13-32 spaces followed by end of record at position 22
Therefore it is recommended that if your table definition contains columns that are defined as nullable, exported/imported files should use comma delimited files.
Q10. I would like to run my DTS from a batch file. Is there a way to encrypt it since it contains user name and password.
A10. To encrypt your DTS statement:
1. open up MSDOS promt
2. enter your DTS statement followed by /!y /!c
format: c:\>dtsrun /sservername /uusername /ppassword /npackagename /!y /!c
example: server name - biwgdcsql02
username - my_user
password - hello
package - dts_export_file
c:\>dtsrun /sbiwgdcsql02 /umy_user /phello /ndts_export_file /!y /!c
/!y displays the encrpted command without executing it
/!c copies the command to windows clipboard
3. open your editor (i.e. MS Word or Notepad)
4. click Edit
5. click paste
6. remove /!y /!c from the end
7. optionally you may want to decrypt server name and package name
Q09. I can access my view from Query Analyzer, but not from Cognos. The view contains data from another database on a different server. What's going on?
A09. ANSI_WARNING and ANSI_NULL needs to set on for distributed queries. Have your DBA set these on at the database level.
Q08. Same question as Q07, but what if the data is on another server.
A08. Have your DBA link the two servers and then create your view qualifying the table with linked server name provided by your DBA.
example: CREATE VIEW TCO88001V00 AS SELECT * FROM
SQL02_DCO88001.DCO88001.DBO.TCO_CUST
Q07. I have a need to view tables that is another database. Should I replicate the table in my database?
A07. Replication is not necessary. You can create a view in your database to access data that is contained in another database. You will need SELECT permission on the table in the other database. Your view would look something like this:
CREATE VIEW view_name AS SELECT * FROM database_name.DBO.table_name
example: CREATE VIEW TCO88001V001 AS SELECT * FROM DCO8801.DBO.TCO_CUST
Q06. When I schedule jobs to run at a certain time of day, it does not run. I know the job is correct,
because I can manually run them and they work. What's going on?
A06. This is know problem with MicroSoft NT 4.0. MicroSoft's work around is to change the
job owner to a standard SQL Server user (i.e. SQL Server login) or system administrator (i.e. sa).
Q05. I have a job that failed. Where is the error message from the failure?
A05. Your error messages are in the job history. Right click your job and select view job history. Your messages are on the bottom half to the screen.
Q04. I'm getting The file 'filename.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file. Backup or restore operation terminating abnormally.
A04. The server administrator needs full control over the directory.
See answer to question Q03
Q03. I'm getting Error 5123 CREATE FILE encountered operating system error 5(Access is denied) while attempting to open or create the physical file 'file name'. I have full control over the directory in NT.
A03. The server administrator needs full control over the directory also.
In Windows NT Explorer:
1. Right click your directory
2. Click properties
3. Click security tab
4. Click permission tab
5. Click Add button
6. Click List name from drop down box
7. Scroll down and choose your server name
8. Click Administrator
9. Click Add button
10. Click type of access drop down box
11. Click full control
12. Click OK button
Q02. How do I turn off logging, when loading
A02. You turn off logging by turning on SELECT INTO/BULK COPY
From Enterprise Manager:
1. Right click your database
2. Select Properties
3. Select Options tab
4. Click the Select into/Bulk Copy check box
5. Click Apply
6. Click OK
From Query Analyzer
1. Enter
exec sp_dboption 'database name', 'select into/bulk copy', 'true'
2. Click green arrow to run
Q01. I have logging turned off, but it still logs when I'm merging additional rows.
A01. If there an index defined on the table and it is not empty, SQL Server will log regardless SELECT INTO/BULK COPY is turned on or not. You can drop the index, load the additional rows, and then recreate the index. Actually this process is faster than logging when merging many rows.