The keyword GO ends each batch. In this case the CREATE PROCEDURE batch.

Remove the "GO" after the INSERT.

I'm confused:

-- Good query

    SELECT      COUNT(*) AS NumOfRec

    FROM          table_name

    WHERE      (dbo.udf_CheckDates(@varStartDate, @varEndDate, dtmStartDate, dtmEndDate) = 1)

    AND  (field_1= @ID_1)

    AND  ...

>>Have you try it ?

Yep, but here's a small test to convince you that a UDF is a bad idea from a perfomance perspective:

-- Simple function to return...

The error is not due to DATEFORMAT.

What are you expecting this to do ?

@EndDate + '23:59:00.000'

This won't perform a string concatenation, if that's your intention. The "Error converting data type...

>>You can create an user defined function with four

Right, but if I have a 100 million row transaction table and want to pull out a small subset of data for 1...

FYI: Here's a reference:

http://www.sql-server-performance.com/q&a131.asp

>>Thats fair enough PW but the Clustered Index in this case is Unique as it is the Primary Key

Sorry, I should have been clearer. When the Clustered Index is unique, then the...

Try naming the column in your resultset:

SELECT @@IDENTITY As RowID

In the Output Parameters dialog, set the "Output Parameter Type" to "Row Value" and complete the mapping of...

>>Does rebuilding a Clustered Index automatically rebuild all Non Clustered Indexes on a Table?

No.

It depends on whether the Clustered Index (CI) is unique or not.

If you want to be sure,...

You would need Dynamic SQL to achieve what you're trying to do. The comma-separated list in an IN ('val1, val2') construct cannot be the result of another query at runtime.

You'd...

Assuming you're running both queries in Query Analyser ... instead of executing the queries, hit CTRL-L to get the execution plan.

Are the plans the same ?

If plans are not the...

>>Set IDENTITY_INSERT DB.dbo.User_Fields_Tbl On

If you use this SET option, you are telling SQL Server that *you* are going to provide the IDENTITY values. Since your requirement is to "also let...

>>If you do store the path in a table then, from a DTS, that is even easier

This is the crux of the whole DTS catch-22 configuration issue. 

You can`t read a table in...

Too many variables and dependencies to give a single answer.

Examples of factors that influence design & architecture:

- Type of source system - Is it legacy mainframe, or SQL based ?...

My approach to DTS for warehouse ETL has been to use DTS for what it's good at - pulling data from disparate data formats and sources, workflow & scheduling.

However, I...