bcp utility stringing

ok. so, i've gotten the bcp utility to work and now i'd like to string all the prompts together and place them in a job to be scheduled to autorun. i don't have much experience in this arena and appreciate any help or suggestions. I'd love to be able to run (from the command prompt) a file takes care of synchronizing all my tables.

I have an additional concern. I'd like to retain the integrity of my primary keys but I notice that if i delete the information then add new, my primary key starts where it leaves off (in other words, if del. then synch. a table with 6 records, after the synch. my primary key increments starting at7). Is there a way, using the bcp util. to update records based on the primary key, copy the key verbatim, or a way to configure the table to help out with this? I know if i drop and recreate the tables as part of the job, i'm good to go but is there away around that step?

Hi DJ,

You could write all your bcp commands into a script file and then use the Windows Task Scheduler to call the bcp utility and pass in the script file. There should be informaiton on how to pass a file to bcp in Books Online, once you have it working manually, it's pretty straight forward to do the same thing using Task Scheduler.

You use of the work "job" indicates you might be familiar with SQL Agent, which is used in other Editions of SQL to schedule tasks. SQL Agent is not included in SQL Express, so that is not available to you in the Express context.

As far as your question about Primary Keys, the behavior you're seeing is by design. By definition, a Primary Key value is never repeated once used. Under some conditions there is cause to override this behavior. One way to do that is to use SET IDENTITY_INSERS ON, which is documented at http://msdn2.microsoft.com/en-us/library/ms188059(SQL.90).aspx. You can also check out the topic about keeping identity values during a bulk insert at http://msdn2.microsoft.com/en-us/library/ms186335(SQL.90).aspx.

Regards,

Mike Wachal

BCP using Win Authentication

Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thanks
Hi,

> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
TRY Task Scheduler.
(It protect your password!)
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005
|||> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
The '-T' parameter works with all BCP modes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
> Hi
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
> Thanks
>
|||You CAN NOT provide an Integrated Login. To work with Windows
Authentication, your "Batch" must be "Logged In" to the network. Now, there
are several ways to do this:
Create an application service.
Write your batch as DCOM or COM+ component.
Use Windows Task Scheduler.
Sincerely,
Anthony Thomas

"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thanks

BCP using Win Authentication

Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
ThanksHi,
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
TRY Task Scheduler.
(It protect your password!)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005|||> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
The '-T' parameter works with all BCP modes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
> Hi
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
> Thanks
>|||You CAN NOT provide an Integrated Login. To work with Windows
Authentication, your "Batch" must be "Logged In" to the network. Now, there
are several ways to do this:
Create an application service.
Write your batch as DCOM or COM+ component.
Use Windows Task Scheduler.
Sincerely,
Anthony Thomas
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thankssql

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
TIA,
ChrisR
Chris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
sql

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

bcp permissions for non SA

A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons
.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:
> Hi,
> I need make a scheduling job for bcp export data from databases' table to a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chensql

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:

> Hi,
> I need make a scheduling job for bcp export data from databases' table to
a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chen

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary).
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.
My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>
sql

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary)
.
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>

BCP out from warm standby DB

We are trying to BCP out of a warm standby, read only DB (logShip Secondary).
We put a hold on the restore job for Log Shipping, bcp out, then resume
Log shipping all using a login account with Sysadm priviledges(SQL
authentication). My question is can we do this with a login account(SQL
authentication) not having
Sysadm priviledges, and if so how?
Thanks.My guess is probably. I would think that you would need an account with
enough privileges to stop and start the restore job itself as well as read
from the DB and write out a file somewhere.
Rick
"John" <John@.discussions.microsoft.com> wrote in message
news:3506E5E7-5D45-4F84-963E-E1D9079CDBAE@.microsoft.com...
> We are trying to BCP out of a warm standby, read only DB (logShip
Secondary).
> We put a hold on the restore job for Log Shipping, bcp out, then resume
> Log shipping all using a login account with Sysadm priviledges(SQL
> authentication). My question is can we do this with a login account(SQL
> authentication) not having
> Sysadm priviledges, and if so how?
> Thanks.
>

bcp out

hi all
I want to bcp out whole database (tables, sps, fns, triggers)
is this possible and is it possible to make it a job
tia
ZarkoBCP out is used only to export the data. You can use EM to script the table,
procedure and trigger etc. Or if you want to automate, look for sp_OACreate,
sp_OAMethod in BOL. There are several code snippets available in the internet.
Thanks
Chinna.
"Zarko Jovanovic" wrote:
> hi all
> I want to bcp out whole database (tables, sps, fns, triggers)
> is this possible and is it possible to make it a job
> tia
> Zarko
>
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million row
s
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time th
e
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server
.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is bei
ng
> truncated? How is the BCP process (the one that is misbehaving) called?
Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million rows
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time the
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!
No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!
|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is being
> truncated? How is the BCP process (the one that is misbehaving) called? Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP Error handling

I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.Are your BCP statements in a stored procedure?|||Here is something I hope will help:

declare
@.FILENAME_C nvarchar(255),
@.sqlstmt2 nvarchar(255),
@.outputcatch varchar(1000)

SET @.FILENAME_C= 'c:\it.txt'
SET @.SQLSTMT2='BCP "SELECT claimnumber from demo.dbo.wcclm1" queryout '+@.FILENAME_C+' /c /t, /r \n /U demo /P demo /S '+@.@.servername
EXEC @.outputcatch=MASTER.DBO.XP_CMDSHELL @.SQLSTMT2, no_output --@.outputcatch=OUTPUT

select @.outputcatch

Note that this sample has a simple select statement. If I change claimnumber to clamnumber @.outputcatch is 1 due to failure. Put it back to claimnumber and it returns 0 which indicates success.

Just keep looking at @.outputcatch and if you get a 1 RETURN.

BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG

Batch Printing

I have a report called Report1 that has 1 parameter called Parm1 and there
are 296 choices for Parm1. Is there anyway to create a batch job to run
this report for all 296 choices and send to .PDF?You could create a data driven subscription that could accomplished
this, although that may be a bit of work. Is the requirement to have
seperate PDF's for all 296? A document map would allow easy navigation
to the 296 different parm values inside one report, and document maps
export over to PDF's.
Matt A
www.reportarchitex.com