Default Data Types in SQl Server

Is there a way to change the default data type in SQL Server? If I import a database into SQL Server it makes every non-numeric field a nvarchar field. Is there a way to change this so that these fields would be varchar or char by default? I cannot find any way to redo these as a batch/group update and have to modify every single field manually. Any insights greatly appreciated.If I import a database into SQL Server it makes every non-numeric field a nvarchar field.

What method are you using to import the database?

Regards,

hmscott|||And what is your datasource? Are you talking about importing delimited text files to tables?|||I use the DTS wizard (ad-hoc, not a scheduled job). I usually import MS-Access tables, but have the same problem with Sybase tables or flat files - every non-numeric field is created as nvarchar. I thought the model database might be the place to override/change this behavior, but no luck there either. Thanks.|||I use the DTS wizard (ad-hoc, not a scheduled job). I usually import MS-Access tables, but have the same problem with Sybase tables or flat files - every non-numeric field is created as nvarchar. I thought the model database might be the place to override/change this behavior, but no luck there either. Thanks.

You will probably have to define the destination data types in the data pump destination tab. If you are importing to a new table, click on the "create" tab and a sample create table script will appear. Edit the destination columns to be of the desired data type.

Regards,

hmscott

Debugging DTS Errors

Hi,

I have a small import application that just calls a stored procedure which calls a DTS which imports a .csv file. the file usually has about 300 records and is created every day or so. Sometimes the DTS fails because one of the records has already been imported (primary key contraint). and the only error handling I have returns a message from the stored procedure if the DTS has failed or not. What I would like to be able to do is tell the user which line or record the DTS failed on. So that they can remove the line and import the file again. I don't want to just skip over the line in the DTS because it may have failed because there was an extra double quote or something like that. Any ideas?

Here is my code for the stored procedure which is calling the DTS:
CREATE PROCEDURE [dbo].[sp_Run_USPS_DTS]
@.strError nvarchar(100) OUTPUT

AS
DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
SELECT @.strError = 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, '\\DBServer\DTS_Files\USPSReports.dts', ''
IF @.hr <> 0
BEGIN
SELECT @.strError = 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
SELECT @.strError = 'Execute failed'
DECLARE @.MaxNum int
SELECT @.MaxNum = max(ReportNum) FROM USPS_Reports
DELETE FROM USPS_Export WHERE ReportNum = @.MaxNum
DELETE FROM USPS_Reports WHERE ReportNum = @.MaxNum
RETURN
END
GOI've implemented quite a number of data import processes, and its usually a multi-step process. What you need to do is separate out import file validation (the possible extra double quotes) from import file import..bringing the data into the dB.

Don't assume your data is good. Assume it is bad. Invoke a "validation" process and ensure it passes before you ever try to import the data. CSV files are great because you can write a little managed class that just opens and parses the file line by line looking for errors. You could even trigger the Stored Procedure to fire from within this framework only if validation passes.

Alex