Hi, I'd like to do a bulk copy insert inside a dts package but the table i'm
inserting to has more columns than the text file. I know you can change the
format file to take care of this, but how do you insert actual values into
those extra columns instead of just NULLS? The values for those extra column
s
are uniquely identified by the text file.
What I could do is do a bulk insert and then run an execute sql command
afterwards which updates the table by replacing the NULL values with actual
values. However, this db is multi-user with each user having different value
s
to insert, so if two imports are done at the same time, someone could be
updating the rows of another person's import. Locking the table is also not
a
possibility because the imports take a long time and we don't want one user
waiting on another one to finish.
Right now, I'm using an ActiveX script in a transform data task in the DTS
package. So there's two transformations going on in that task: one is the
copy column from the text file, and another is adding values to the extra
columns. This works well, however, it requires row by row processing and thu
s
it takes over 3 times longer than a bulk insert.
Does anyone have a solution to setting values of extra columns in a bulk
insert? Any help is appreciated. Thanks!Where do those extra values come from?
ML|||These extra values are global variables in the dts package
"ML" wrote:
> Where do those extra values come from?
>
> ML|||In that case you can design a set-based solution. You need something like th
is:
1) you need access to the file - one way to achieve this is linking the file
as a linked server. Read more on this here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
http://msdn.microsoft.com/library/d...ma_ini_file.asp
2) you need access to your global variables - the best way would be to
create a procedure that accesses the linked file and accepts the values of
those global variables as parameters;
3) in your procedure you then combine the values from the source file and
the values from the DTS, and insert them into the destination table(s).
You could even use a folder-sniffer to start the job automatically, as soon
as the source file becomes available in the source folder.
ML
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Bulk Insert Task - Where Does The First Row Begin?
Hi,
I am using the Bulk Insert Task to bulk copy data from a flat file to a SQL Server 2005 table. The flat file contains pipe ( | ) delimited columns and several thousand records which are {CR}{LF} delimited. The file also contains a header record and trailer record, which contain several words but also contains a ( | ) symbol among those words.
E.g.:
HEDR | yadi yadi yada
500 | Data Data | More Data | 600
460 | Datum | More More | 705
550 | Data | Data | 603
FOOTR | yadi yadi yada
I need to ignore the header and trailer records and just pickup the proper records. But even though I manually set the First Row property to 2 and the Last Row property to 4, It does not pickup the first true record i.e. the record which begins with 500, and if I set the First Row to 1, it throws me an error citing 'truncation error' or similar. I think it is taking the first record (i.e. header row along with the first row as one, and since there are now more pipes ( | ) the error is thrown)
I've tried setting different values for these properties but to no avail... Any help will be deeply appreciated...
Thanks
Gogula
Bulk insert task expects the header and footer records also to be in the same format as other Data Rows, so to do, what you are trying to do, your data needs to be in this format:
HEDR | yadi | yadi | yada
500 | Data Data | More Data | 600
460 | Datum | More More | 705
550 | Data | Data | 603
FOOTR| yadi | yadi | yada
This is the same behavior as Transact-SQL bulk insert, and if you try something like the following with your file, you will encounter errors as well. Therefore, this is by design, according to what bulk insert supports.
BULK INSERT tempdb.dbo.Table_1
FROM 'C:\\File.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR ='\n',
firstrow = 2,
lastrow = 4
)
|||Hi Rangeeta,Thanks for the reply. But what if in case there might be more than one header and footer (which I programmatic ally count and take into consideration) and those headers cannot be separated by the delimiters (since the client needs to send it to us)? It seems a little unfair that Bulk Insert expects the headers to be in the same format as the other rows...
Are there any alternate methods to do this?
Thanks again
Gogula
Bulk Insert SQL Server in C#
I am searching for an efficient way to do
bulk inserts via C#. It seems there are three
ways of doing that:
1.) Copy values to file and call BCP or BULK INSERT in TSQL
2.) Call the IRowFastLoad Interface of the SQL OLEDB Provider
3.) bcp interface of the ODBC driver
I think 2.) and 3.) are the only viable solutions and I wanted
to ask if anybody out there has already experiences
with these interfaces and the way of invocating them in C#.
Thank you for your help.
Bernhard
------------------
Solution Architect
Management Factory
Vienna/AUSTRIA/EUROPE
http://www.mf.ag
Bernhard.Walter@.mf.agYou may want to look at using SQL-DMO with C#. I've not coded in C# however I have used SQL-DMO with VB, VBScript (in an ASP page) and Perl.|||Thanks for the help.
Using SQL-DMO seems to be quite the same than inserting into a
file and then calling BCP, right ?
What I am looking for is a more efficient approach (memory to
SQL Server), because my routine has to work for 1 record to
millions of records in a fast way.
Bernhard
bulk inserts via C#. It seems there are three
ways of doing that:
1.) Copy values to file and call BCP or BULK INSERT in TSQL
2.) Call the IRowFastLoad Interface of the SQL OLEDB Provider
3.) bcp interface of the ODBC driver
I think 2.) and 3.) are the only viable solutions and I wanted
to ask if anybody out there has already experiences
with these interfaces and the way of invocating them in C#.
Thank you for your help.
Bernhard
------------------
Solution Architect
Management Factory
Vienna/AUSTRIA/EUROPE
http://www.mf.ag
Bernhard.Walter@.mf.agYou may want to look at using SQL-DMO with C#. I've not coded in C# however I have used SQL-DMO with VB, VBScript (in an ASP page) and Perl.|||Thanks for the help.
Using SQL-DMO seems to be quite the same than inserting into a
file and then calling BCP, right ?
What I am looking for is a more efficient approach (memory to
SQL Server), because my routine has to work for 1 record to
millions of records in a fast way.
Bernhard
Bulk Insert Related Tables - PKs
Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:
> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:
> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Tuesday, March 20, 2012
BULK INSERT Query
Hey i am planning to use the BULK INSERT to copy the data into my Server. Th
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:
> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>
Monday, March 19, 2012
BULK INSERT messes up IDENTITY value
I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeralda
Ok, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
> in after restoring the database anew and truncate that table. The identity
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeralda
Ok, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
> in after restoring the database anew and truncate that table. The identity
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda
BULK INSERT messes up IDENTITY value
I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to copy
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeraldaOk, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to cop
y
> in after restoring the database anew and truncate that table. The identit
y
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda
in after restoring the database anew and truncate that table. The identity
column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
PRIMARY KEY CLUSTERED.
The transfer was complete, the last row was 4 digit; but when new rows are
inserted values jump to 9 digit.
Help! Gracias.
LaEsmeraldaOk, if the Identity is not used as reference to Other Tables , i mean
in Query to check data across tables and if there is not harm in
deleting the column, then Drop and identity Column and Recreate it
again then apply the identity to it and LO! it all back again.
Maninder
MCDBA
On Jan 23, 11:43 am, LaEsmeralda
<LaEsmera...@.discussions.microsoft.com> wrote:
> I use bcp to copy out a table and BULK INSERT..WITH (KEEP IDENTITY) to cop
y
> in after restoring the database anew and truncate that table. The identit
y
> column is numeric (9, 0) IDENTITY (1, 1). This column also has CONSTRAINT
> PRIMARY KEY CLUSTERED.
> The transfer was complete, the last row was 4 digit; but when new rows are
> inserted values jump to 9 digit.
> Help! Gracias.
> LaEsmeralda
Sunday, March 11, 2012
BULK INSERT from a saved recordset?
Is there any way to copy data from a previous saved recordset using "BULK INSERT" or bcp ? (the recordset is supposed to be saved using adPersistADTG or adPersistXML)look into adodb.stream object.|||See also ADO command type "file" for a recordset.
Sunday, February 19, 2012
Bulk Edit DTS Packages
I have a Whole Bunch of DTS packages that copy data from Database1 to Database2.
I want to quickly make duplicates of all these packages, except that they will copy data from Database1 to Database3. Everything else stays the same.
Is there an easy way to do this?Refer to this SQL DTS (http://www.sqldts.com/default.aspx?6,105,204,0,0) link.
Or you can use COPY DATABASE wizard to accomplish the task.
I want to quickly make duplicates of all these packages, except that they will copy data from Database1 to Database3. Everything else stays the same.
Is there an easy way to do this?Refer to this SQL DTS (http://www.sqldts.com/default.aspx?6,105,204,0,0) link.
Or you can use COPY DATABASE wizard to accomplish the task.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS database.
‘Archiving’ process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server.
"Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> ‘Archiving Database’ on the same server) and deleted from the OSS database.
> ‘Archiving’ process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.
|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
‘Archiving Database’ on the same server) and deleted from the OSS databa
se.
‘Archiving’ process is taken care by a stored procedure. Also, please no
te
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. Aft
er
> 10 days these requests are supposed to be archived (to a different databas
e
> ‘Archiving Database’ on the same server) and deleted from the OSS data
base.
> ‘Archiving’ process is taken care by a stored procedure. Also, please
note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks ar
e
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk delet
es
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
>
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk Deletes and Bulk Copy
We are currently facing some issues with bulk deletes of records during
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Archiving.
Our application logs all the requests and responses in the log tables. After
10 days these requests are supposed to be archived (to a different database
â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
â'Archivingâ' process is taken care by a stored procedure. Also, please note
that there are certain restrictions (like request must have been
successfully) on a request before it can be archived and deleted.
What is the problem?
We are facing issues both while copying the data to archiving database and
deleting the data from Main database.
1. When records are to be copied from Main to Archiving database, locks are
acquired on the log tables in Main database and the log tables are blocked
there by hindering any further activity.
2. The same problem occurs while deleting the records from log tables.
What are we looking for?
Would be great to have some best practices around archiving and bulk deletes
in SQL server."Sachin Surana" schrieb:
> We are currently facing some issues with bulk deletes of records during
> Archiving.
> Our application logs all the requests and responses in the log tables. After
> 10 days these requests are supposed to be archived (to a different database
> â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> â'Archivingâ' process is taken care by a stored procedure. Also, please note
> that there are certain restrictions (like request must have been
> successfully) on a request before it can be archived and deleted.
> What is the problem?
> We are facing issues both while copying the data to archiving database and
> deleting the data from Main database.
> 1. When records are to be copied from Main to Archiving database, locks are
> acquired on the log tables in Main database and the log tables are blocked
> there by hindering any further activity.
> 2. The same problem occurs while deleting the records from log tables.
> What are we looking for?
> Would be great to have some best practices around archiving and bulk deletes
> in SQL server.
Excerpt from the books online (page 'Lock Compatibility'):
The bulk update (BU) lock is compatible only with schema stability (Sch-S)
and other bulk update (BU) locks.|||Hmm.. i understand, but that is the problem. Do you mean we should use bulk
update lock?
Is there faster way for bulk delete, bulk update?
Regards,
Sachin
"Christian Donner" wrote:
> "Sachin Surana" schrieb:
> > We are currently facing some issues with bulk deletes of records during
> > Archiving.
> >
> > Our application logs all the requests and responses in the log tables. After
> > 10 days these requests are supposed to be archived (to a different database
> > â'Archiving Databaseâ' on the same server) and deleted from the OSS database.
> > â'Archivingâ' process is taken care by a stored procedure. Also, please note
> > that there are certain restrictions (like request must have been
> > successfully) on a request before it can be archived and deleted.
> >
> > What is the problem?
> >
> > We are facing issues both while copying the data to archiving database and
> > deleting the data from Main database.
> >
> > 1. When records are to be copied from Main to Archiving database, locks are
> > acquired on the log tables in Main database and the log tables are blocked
> > there by hindering any further activity.
> >
> > 2. The same problem occurs while deleting the records from log tables.
> >
> > What are we looking for?
> >
> > Would be great to have some best practices around archiving and bulk deletes
> > in SQL server.
> Excerpt from the books online (page 'Lock Compatibility'):
> The bulk update (BU) lock is compatible only with schema stability (Sch-S)
> and other bulk update (BU) locks.
Bulk copying xml child node data
I am trying to bulk copy some XML data into a SQL and am generally doing quite well. The XML data I have been given has a child node called "name" which is the same as in the parent node as shown in the highlight of the XML source below. Now I can retrive the data in the parent node using
bulk.ColumnMappings.Add("name", "Name")
but I cannot get any of the data from the child node "Catagory" or " Catagories". Have you any suggestions on how I can get this data.
<?xml version="1.0" ?>
<products>
<product>
<ProductId>12345</ProductId>
<name>Productname</name>
<description>"This is some description text"</description>
<Categories>
<Category>
<name>Category type</name>
<merchantName>Category subtype</merchantName>
</Category>
</Categories>
<fields/>
</product>
Etc…….
</products>
Many thanks in advance
Simon
How did you map to the "Catagory" or " Catagories" node? Make sure you've made proper mapping. For example if you read the xml into a DataSet, then the "Catagory"/" Catagories" will be in 2nd/3rd table respectively, because it is the 2nd/3rd level node (ignore the root node). I mean you can read the xml data use something like this:
Dim ds As New DataSet
ds.ReadXml("c:\XmlFiles\Products.xml", XmlReadMode.Auto)
bulk copy(batch insert) is failed when the mssql backup the database
Hi All,
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie
bulk copy(batch insert) is failed when the mssql backup the database
Hi All
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie
Bulk copy XML data to SQL data source
Can anyone provide an expample of bulk copying XML data to a SQL table. I am also looking at using column mapping so that I can map fields and also insert a new GUID into the key of the SQL table.
Many thanks
There are some ways, take a look at this link:
Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...): http://msdn2.microsoft.com/en-us/library/ms175915.aspx
And examples for bulking XML data can be found here:
http://msdn2.microsoft.com/en-us/library/ms191184.aspx
bulk copy with an identity column
Hello,
I am using DB-Lib to bulk copy some data into a table that has an identity
column (set up to be used during replication). I don't think I want to
actually bind to an identity column with bcp_bind, do I? Is there any way t
o
skip that column?
Any help would be appreciated.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksHi
I have not used bulk copy and DB-lib, but I would expect that if the data is
included in the data file you still need to bind it but use 0 for the table
column see "Using a Data File with More Fields" in Books Online.
John
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:B4B4E323-3BD0-40BD-A9A0-734B1120A80F@.microsoft.com...
> Hello,
> I am using DB-Lib to bulk copy some data into a table that has an identity
> column (set up to be used during replication). I don't think I want to
> actually bind to an identity column with bcp_bind, do I? Is there any way
> to
> skip that column?
> Any help would be appreciated.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
>|||Hi John,
Thanks for the reply. Unfortunately, it's not that my source data has
columns that I want to ignore, it's that my destination table has columns
that I want to "ignore", in the sense that the column holds uniqueidentifier
values that I don't know how to replace.
I guess I'll keep looking. Thanks again.
Ed
GEDDS Manager
Geophysical Institute
University of Alaska Fairbanks
"John Bell" wrote:
> Hi
> I have not used bulk copy and DB-lib, but I would expect that if the data
is
> included in the data file you still need to bind it but use 0 for the tabl
e
> column see "Using a Data File with More Fields" in Books Online.
> John
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:B4B4E323-3BD0-40BD-A9A0-734B1120A80F@.microsoft.com...
>
>|||Hi Ed
Translating from the BOL topic "Using a Data File with Fewer Fields" I would
expect the varlen to be -1 as well as the table_column being 0.
John
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:B23FF847-2610-4C30-8814-274CCEB63BD8@.microsoft.com...
> Hi John,
> Thanks for the reply. Unfortunately, it's not that my source data has
> columns that I want to ignore, it's that my destination table has columns
> that I want to "ignore", in the sense that the column holds
> uniqueidentifier
> values that I don't know how to replace.
> I guess I'll keep looking. Thanks again.
> Ed
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
>
> "John Bell" wrote:
>|||One possibility you might want to try is to create a view on your
destination table that selects all columns except for the
uniqueidentifer column. You should then be able to bulk copy
directly into this view.
I am using DB-Lib to bulk copy some data into a table that has an identity
column (set up to be used during replication). I don't think I want to
actually bind to an identity column with bcp_bind, do I? Is there any way t
o
skip that column?
Any help would be appreciated.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksHi
I have not used bulk copy and DB-lib, but I would expect that if the data is
included in the data file you still need to bind it but use 0 for the table
column see "Using a Data File with More Fields" in Books Online.
John
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:B4B4E323-3BD0-40BD-A9A0-734B1120A80F@.microsoft.com...
> Hello,
> I am using DB-Lib to bulk copy some data into a table that has an identity
> column (set up to be used during replication). I don't think I want to
> actually bind to an identity column with bcp_bind, do I? Is there any way
> to
> skip that column?
> Any help would be appreciated.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
>|||Hi John,
Thanks for the reply. Unfortunately, it's not that my source data has
columns that I want to ignore, it's that my destination table has columns
that I want to "ignore", in the sense that the column holds uniqueidentifier
values that I don't know how to replace.
I guess I'll keep looking. Thanks again.
Ed
GEDDS Manager
Geophysical Institute
University of Alaska Fairbanks
"John Bell" wrote:
> Hi
> I have not used bulk copy and DB-lib, but I would expect that if the data
is
> included in the data file you still need to bind it but use 0 for the tabl
e
> column see "Using a Data File with More Fields" in Books Online.
> John
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:B4B4E323-3BD0-40BD-A9A0-734B1120A80F@.microsoft.com...
>
>|||Hi Ed
Translating from the BOL topic "Using a Data File with Fewer Fields" I would
expect the varlen to be -1 as well as the table_column being 0.
John
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:B23FF847-2610-4C30-8814-274CCEB63BD8@.microsoft.com...
> Hi John,
> Thanks for the reply. Unfortunately, it's not that my source data has
> columns that I want to ignore, it's that my destination table has columns
> that I want to "ignore", in the sense that the column holds
> uniqueidentifier
> values that I don't know how to replace.
> I guess I'll keep looking. Thanks again.
> Ed
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
>
> "John Bell" wrote:
>|||One possibility you might want to try is to create a view on your
destination table that selects all columns except for the
uniqueidentifer column. You should then be able to bulk copy
directly into this view.
Bulk copy to publication
Hello
I need to copy from excel table to SQL 2005 publication (many record > 1000)
How can i do this. Bulk copy? or ...
I have experience problem with bulk copy so how can i manange it for my own needs.
right click on the database you wish to import data to. Select Tasks, and Import Data, next, and for your data source, select Excel, browse to find your Excel spread sheet, click next, next, next, next, and Finish.Bulk Copy Text Qualifier
Hi,
this is the structure of my file .txt:
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
and this is the structure of my file .fmt:
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
Thanks
Simo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>
this is the structure of my file .txt:
'S012RM4','BBBBKMQBBBB8'
'S012RM4','BBBBKMQBBBB8'
and this is the structure of my file .fmt:
7.0
2
1 SQLCHAR 0 50 "," 1 c_1
2 SQLCHAR 0 50 "\r\n" 2 c_2
I'm using a DTS to import this recods with Bulk Copy.
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record:
column 1 = 'S012RM4'
column 2 = 'BBBBKMQBBBB8'
and not
column 1 = S012RM4
column 2 = BBBBKMQBBBB8
why?
Thanks
Simo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>
Bulk Copy Text Qualifier
Hi
this is the structure of my file .txt
--
'S012RM4','BBBBKMQBBBB8
'S012RM4','BBBBKMQBBBB8
--
and this is the structure of my file .fmt
--
7.
1 SQLCHAR 0 50 "," 1 c_
2 SQLCHAR 0 50 "\r\n" 2 c_
--
I'm using a DTS to import this recods with Bulk Copy
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
column 1 = 'S012RM4
column 2 = 'BBBBKMQBBBB8
and not
column 1 = S012RM
column 2 = BBBBKMQBBBB
why
ThankSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>
this is the structure of my file .txt
--
'S012RM4','BBBBKMQBBBB8
'S012RM4','BBBBKMQBBBB8
--
and this is the structure of my file .fmt
--
7.
1 SQLCHAR 0 50 "," 1 c_
2 SQLCHAR 0 50 "\r\n" 2 c_
--
I'm using a DTS to import this recods with Bulk Copy
This is the "Text qualifier" --> ' ... but the Bulk Copy import this record
column 1 = 'S012RM4
column 2 = 'BBBBKMQBBBB8
and not
column 1 = S012RM
column 2 = BBBBKMQBBBB
why
ThankSimo,
The ' is part of the string as defined. If you can, get the text file
delivered to you without quote marks and tab delimited. (Then you probably
won't need a format file.)
As you have it, you will need to include the ' as part of the field
terminator (e.g. ','). How do you get rid of the first quote? IF I
remember correctly, make field 1 terminate with ' and assign it to no SQL
column. You might read this article on format files:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp
Russell Fields
"Simo" <anonymous@.discussions.microsoft.com> wrote in message
news:E06D2D53-AB35-4450-A089-21909DBCF2D2@.microsoft.com...
> Hi,
> this is the structure of my file .txt:
> --
> 'S012RM4','BBBBKMQBBBB8'
> 'S012RM4','BBBBKMQBBBB8'
> --
> and this is the structure of my file .fmt:
> --
> 7.0
> 2
> 1 SQLCHAR 0 50 "," 1 c_1
> 2 SQLCHAR 0 50 "\r\n" 2 c_2
> --
> I'm using a DTS to import this recods with Bulk Copy.
> This is the "Text qualifier" --> ' ... but the Bulk Copy import this
record:
> column 1 = 'S012RM4'
> column 2 = 'BBBBKMQBBBB8'
> and not
> column 1 = S012RM4
> column 2 = BBBBKMQBBBB8
> why?
> Thanks
>
Subscribe to:
Posts (Atom)