In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \\indiadb\ftproot\CLIENT_BRANCH_UPLOAD\branchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r \n -c -t "," -q'It doesn't...did you look at BOL?
You can however "fake it out"
It would require a view or using QUERYOUT...
Something like
SELECT 'HEADING1','HEADING2',ect
UNION ALL
SELECT Col1, Col2, ect
FROM yourTable
Just make sure you CONVERT everyhting to varchar...
2012年3月27日星期二
2012年3月22日星期四
BCP process
I have a question about BCP in
When i do a BCP in from a file which has 3 mill records, the BCP insert goes
in very fast with notification showing 1000 records at a time but when all
rows are inserted after 3 mill records it takes considerable time for BCP in
to get over.
What is it doing at each stage '
Ps- Table has a primary key so i assume its a slow BCP
SanjaySanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
But why is it taking that long i thought a commit is just like including a m
arker
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
Also which one is better in speed using -b1000 or doing it in one big transa
ction
Sanjay|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
marker
the disk so that should be the one taking time.
transaction
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
When i do a BCP in from a file which has 3 mill records, the BCP insert goes
in very fast with notification showing 1000 records at a time but when all
rows are inserted after 3 mill records it takes considerable time for BCP in
to get over.
What is it doing at each stage '
Ps- Table has a primary key so i assume its a slow BCP
SanjaySanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
quote:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
quote:|||You mean its doin a COMMIT right...
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>
But why is it taking that long i thought a commit is just like including a m
arker
Checkpoint is actuallly the process which writes committed transaction to th
e disk so that should be the one taking time.
Also which one is better in speed using -b1000 or doing it in one big transa
ction
Sanjay|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
quote:
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
quote:
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
quote:
> Also which one is better in speed using -b1000 or doing it in one big
transaction
quote:|||examnotes (sanjayg@.hotmail.com) writes:
> Sanjay
>
quote:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
BCP process
I have a question about BCP i
When i do a BCP in from a file which has 3 mill records, the BCP insert goes in very fast with notification showing 1000 records at a time but when all rows are inserted after 3 mill records it takes considerable time for BCP in to get over
What is it doing at each stage ?
Ps- Table has a primary key so i assume its a slow BC
SanjaSanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> Also which one is better in speed using -b1000 or doing it in one big
transaction
> Sanjay
>|||=?Utf-8?B?U2FuamF5?= (sanjayg@.hotmail.com) writes:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
When i do a BCP in from a file which has 3 mill records, the BCP insert goes in very fast with notification showing 1000 records at a time but when all rows are inserted after 3 mill records it takes considerable time for BCP in to get over
What is it doing at each stage ?
Ps- Table has a primary key so i assume its a slow BC
SanjaSanjay,
It's doing one big honking transaction.
Use the -b switch to set a batch size upon import.
That way it will commit each batch separately.
Just remember, if it blows off, it's up to you to clean up the mess.
Example:
BCP myserver.dbo.mytable IN
d:\mssql\transfer\MyBCPFile.txt -Smyserver -Umyuser -Pmypassword -n -b1000
James Hokes
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:589E306B-667F-49DF-BE90-4CDCB5713662@.microsoft.com...
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
goes in very fast with notification showing 1000 records at a time but when
all rows are inserted after 3 mill records it takes considerable time for
BCP in to get over.
> What is it doing at each stage '
> Ps- Table has a primary key so i assume its a slow BCP
> Sanjay
>|||Hi Sanjay,
Yes. Each batch will be treated as a separate transaction.
Since you have 3 Million records to load, You can go for -b10000 (Commit
will be made after loading 10000 records),. This will speed up your process
considerably.
Thanks
Hari
MCDBA
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:0D546BCA-DBBB-4B19-BF1E-5A604D9F15D2@.microsoft.com...
> You mean its doin a COMMIT right...
> But why is it taking that long i thought a commit is just like including a
marker
> Checkpoint is actuallly the process which writes committed transaction to
the disk so that should be the one taking time.
> Also which one is better in speed using -b1000 or doing it in one big
transaction
> Sanjay
>|||=?Utf-8?B?U2FuamF5?= (sanjayg@.hotmail.com) writes:
> I have a question about BCP in
> When i do a BCP in from a file which has 3 mill records, the BCP insert
> goes in very fast with notification showing 1000 records at a time but
> when all rows are inserted after 3 mill records it takes considerable
> time for BCP in to get over.
> What is it doing at each stage '
I've seen this too. I have not investigated what it actually does.
Maybe it's rebuilding the index, maybe it is just committing the
data.
Even if using -b10000 will avoid this delay at the end, it does not
mean that this is faster. You will have to benchmark to find out.
My gut feeling is that as long as your log can stand it, having all
in one transaction is the best.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
2012年3月19日星期一
BCP Large Table.
If I use BCP to export a very large table will that table be blocked
for writes during the export process? I don't want to prevent users
from accessing that table during the bcp process?
for writes during the export process? I don't want to prevent users
from accessing that table during the bcp process?
Thank You, TFD.LineVoltageHalogen (tropicalfruitdrops@.yahoo.com) writes:
> If I use BCP to export a very large table will that table be blocked
> for writes during the export process? I don't want to prevent users
> from accessing that table during the bcp process?
A quick test indicates that BCP takes out shared page locks as goes,
and an Intend Share lock on table level. While the BCP was running I
was able to perform an update on the table. A writer that would require
a table lock for his update would be locked, though, I guess. But you
should not have any such writers.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
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.
2012年3月11日星期日
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 problem
Hi,
I am loading a table with 7 million records using bcp in, however, what i noticed it that the bcp in process terminates when it reaches around 2.5 million rows.
I don't feel there should be any such limitation on bcp, however would like to know if anyone have come across this problem and how they have worked around to resolve this problem.
Thanks.
Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Yes i use the -e option however the error file is empty. It runs without error however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that would
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
I am loading a table with 7 million records using bcp in, however, what i noticed it that the bcp in process terminates when it reaches around 2.5 million rows.
I don't feel there should be any such limitation on bcp, however would like to know if anyone have come across this problem and how they have worked around to resolve this problem.
Thanks.
Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number of
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.
|||Yes i use the -e option however the error file is empty. It runs without error however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that would
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
Bcp in problem
Hi,
I am loading a table with 7 million records using bcp in, however, what i no
ticed it that the bcp in process terminates when it reaches around 2.5 milli
on rows.
I don't feel there should be any such limitation on bcp, however would like
to know if anyone have come across this problem and how they have worked aro
und to resolve this problem.
Thanks.Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Yes i use the -e option however the error file is empty. It runs without e
rror however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that woul
d
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
I am loading a table with 7 million records using bcp in, however, what i no
ticed it that the bcp in process terminates when it reaches around 2.5 milli
on rows.
I don't feel there should be any such limitation on bcp, however would like
to know if anyone have come across this problem and how they have worked aro
und to resolve this problem.
Thanks.Hi,
Can you do the BCP IN with '-b' option. This option specifies the number of
rows per batch of data copied. Each batch is copied to the server as one
transaction. SQL Server commits or rolls back ( in the case of failure)
after the completion of each batch.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||Hi hari,
I have tried that too however still having the same issue. Anything else...
Thanks
"Hari Prasad" wrote:
> Hi,
> Can you do the BCP IN with '-b' option. This option specifies the number o
f
> rows per batch of data copied. Each batch is copied to the server as one
> transaction. SQL Server commits or rolls back ( in the case of failure)
> after the completion of each batch.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>|||There must be some problem that is specific to your environment. have you
tried using the -e (for error file) and -o (for output file)?
Most likely some details should be written to one of these files that would
help you diagnose the problem.
--TJTODD
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> Hi,
> I am loading a table with 7 million records using bcp in, however, what i
noticed it that the bcp in process terminates when it reaches around 2.5
million rows.
> I don't feel there should be any such limitation on bcp, however would
like to know if anyone have come across this problem and how they have
worked around to resolve this problem.
> Thanks.|||Yes i use the -e option however the error file is empty. It runs without e
rror however terminates after 2 million records. Any suggestions
Thanks.
"TJTODD" wrote:
> There must be some problem that is specific to your environment. have you
> tried using the -e (for error file) and -o (for output file)?
> Most likely some details should be written to one of these files that woul
d
> help you diagnose the problem.
> --TJTODD
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:A131C4C7-4BEC-4548-B51A-E02EF01E0540@.microsoft.com...
> noticed it that the bcp in process terminates when it reaches around 2.5
> million rows.
> like to know if anyone have come across this problem and how they have
> worked around to resolve this problem.
>
>
BCP import - remove quotes from CSV File
My bcp process is now working. FINALLY.
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:
> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:
> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
2012年2月25日星期六
BCP Converting character ° to ¦
A BCP process is converting a degree character (°) to a pipe character (¦).
This is the only conversion problem that has been identified. Is there a
method to correct this?Try adding -C RAW' to your BCP command. This will prevent code page
conversion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KingRo" <KingRo@.discussions.microsoft.com> wrote in message
news:A0D44DC3-915F-45B8-9BD1-EF16DCA4865D@.microsoft.com...
>A BCP process is converting a degree character (°) to a pipe character (¦).
> This is the only conversion problem that has been identified. Is there a
> method to correct this?
This is the only conversion problem that has been identified. Is there a
method to correct this?Try adding -C RAW' to your BCP command. This will prevent code page
conversion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KingRo" <KingRo@.discussions.microsoft.com> wrote in message
news:A0D44DC3-915F-45B8-9BD1-EF16DCA4865D@.microsoft.com...
>A BCP process is converting a degree character (°) to a pipe character (¦).
> This is the only conversion problem that has been identified. Is there a
> method to correct this?
2012年2月18日星期六
bcp queryout
I have a process that does a BCP "queryout" using xp_cmdshell to a Network
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.
Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:
> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive the
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.
Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:
> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive the
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>
bcp queryout
I have a process that does a BCP "queryout" using xp_cmdshell to a Network
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:
> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive t
he
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>
share. The process is being invoked by a non-sysadmin account. I receive the
error Cannot open BCP file.
Sql7 on a Windows N/T box.
How do you setup permissions to make happen?
Thanks.Hi Andy
If you are running SQL Server Service Account as local system then you will
not have access to the network share, change this to a domain account that
has access and you should be ok.
John
"Andy" wrote:
> I have a process that does a BCP "queryout" using xp_cmdshell to a Network
> share. The process is being invoked by a non-sysadmin account. I receive t
he
> error Cannot open BCP file.
> Sql7 on a Windows N/T box.
> How do you setup permissions to make happen?
>
> Thanks.
>
>
>
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
BCP
Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:
> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?
2012年2月16日星期四
Batch scripts
Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Batch scripts
Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate tha
t
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check ou
t
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with exampleHi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate tha
t
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check ou
t
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Batch scripts
Hi All,
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with example
Hi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Could you please provide me brief description of batch process.
What is batch process?
How to run batch process ? with example
Hi
This really depends on your system and what you are trying to achieve. If
you are trying to load a number of files into tables and then manipulate that
data, or just manipulate a large amount of data already in the database at a
given time of day (say overnight) then you may want to look at DTS in SQL
2000 and SSIS in SQL 2005. If you want top schedule these jobs then check out
SQL Agent.
HTH
John
"kailash" wrote:
> Hi All,
> Could you please provide me brief description of batch process.
> What is batch process?
> How to run batch process ? with example
Batch process for sending mail
Hello everyone,
I want to write a batch process that will run everyday and send emails.
The scenario is as follows:
There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).
I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.
Table structure:
Admin
AdminID
AdminArea
AdminID
AreaCode
Area
AreaCode
AreaName
WebSummit
SummitId
RequestorName
DateOfRequest
RequestStatus
AreaCode
Regards,
Vidya.
Hi,
Kindly advise.
Regards,
Vidya
|||You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.
Roughly, create a SQL Server Agent job to run at 8:00AM
and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.
|||Hi,
Thanks for reading. Some code will be useful.
Regards,
Vidya
|||Here's the basic template for the script to be executed.
You'll need to adjust the queries to be exactly what you need.
I just did a simple query to get the admin who had 5 or more requests present.
Code Snippet
createtable #Admin (AdminID int, Email varchar(50))
insertinto #Admin values(1,'admin1@.mycompany.com')
insertinto #Admin values(2,'admin2@.mycompany.com')
insertinto #Admin values(3,'admin3@.mycompany.com')
insertinto #Admin values(4,'admin4@.mycompany.com')
createtable #AdminArea (AdminID int, AreaCode int)
insertinto #AdminArea values(1, 100)
insertinto #AdminArea values(2, 200)
insertinto #AdminArea values(3, 300)
insertinto #AdminArea values(4, 400)
createtable #Area (AreaCode int, AreaName varchar(50))
insertinto #Area values(100,'Area 100')
insertinto #Area values(200,'Area 200')
insertinto #Area values(300,'Area 300')
insertinto #Area values(400,'Area 400')
createtable #WebSummit ( SummitId int, RequestorName varchar(50),
DateOfRequest datetime, RequestStatus int, AreaCode int)
insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )
insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )
insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )
insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )
insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )
insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )
droptable #to
select a.AdminID, a.Email
into #to
from #Admin a
where a.AdminID in
(
select a.AdminID
from #Admin a
innerjoin #AdminArea aa
on a.AdminID = aa.AdminID
innerjoin #Area b
on aa.AreaCode = b.AreaCode
innerjoin #WebSummit w
on b.AreaCode = w.AreaCode
groupby a.AdminID
havingcount(*)>4
)
declare elist cursorfor
select adminid, email from #to
declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)
open elist
if@.@.cursor_rows> 0
begin
fetch next from elist into @.id, @.email
while@.@.fetch_status=0
begin
set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))
exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,
@.subject='Summit Follow-up'
fetch next from elist into @.id, @.email
end
end
close elist
deallocate elist
|||Thanks Dale.
That was very helpful.
Regards,
Vidya.
订阅：
博文 (Atom)