[font="Verdana"]Well, I haven't been able to make it work. If you can, let me know! :D[/font]

[font="Verdana"]Reiterate: expecially where holidays are concerned, use a Calendar table.

Although having said that, holidays can differ depending on location. They get a little tricky.

[/font]

[font="Verdana"]No, I think that's eliminated it as being a date format issue. Have you had a look at the query plans both for the query and for running the...

[font="Verdana"]Rather than convert to varchar and back again, can you try the following?

dateadd(dd, 0, datediff(dd, 0 [DateTime]))

I'm not sure whether that will resolve the problem, but it's a better way...

[font="Verdana"]I've been playing around with this for days, and I'm about to give up. I think what I will do is get the packed decimal fields written out as...

[font="Verdana"]So far as I can tell, bulk insert (and bcp) expect all of the records in the file to match the format, irrespective of whether they are actually loaded. ...

[font="Verdana"]Yeah, I forgot... SQL Server just needs white space to separate statements, not actually a semi-colon or carriage return. I'm trying to use semi-colons consistently these days (mostly...

[font="Verdana"]I considered doing something like that, but he said he didn't want to use temporary tables, so I excluded table variables as well.

BTW, don't you need a semi-colon or carriage...

[font="Verdana"]I would use something similar to the following (remembering that it is possible to have more than one log file per database.)

Note code is untested!

declare @databaseName sysname;

declare @sql...

[font="Verdana"]Doh! I need to read questions better. No, that won't work because you are doing a subsequent exec(). You could get it to work by using sp_executesql...

[font="Verdana"]Actually, why do you need the quotes around the @object at all?

set @sql = ''select substring(@object,3,1)''

... should work.

[/font]

[font="Verdana"]I think the "if exists" needs to go in the procedure that calls BCP. So you would change your logic as follows.

Your current procedure will be something like this:

...

exec...

[font="Verdana"]Can I put my hand in the air and recommend a Calendar table? It's a specialised version of a Tally/Numbers table, and you can do all the necessary date...

[font="Verdana"]Probably dependant on the...

[font="Verdana"]You could use substring and UNPIVOT to take the data from the form

User1-AB

to:

User1 A

User1 B

That would then simplify the join. I'd do the UNPIVOT as a CTE.

[/font]