Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas

"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql

Regarding BULK insert

I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..
chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Regarding BULK insert

I am doing a bulk insert and i am A DBO on the database and i can't do it u
nless i
added my self to the bulkinsert admin server role.
do any body why is that.why it needs a server role..chinn,
Because of the way BULK INSERT is implemented. The data goes straight
into the server as an OLEDB row set. From Books Online: "the BULK INSERT
statement requires read access to any data on the network and machine
the server is running on." That is why it is locked down.
You might also want to be aware of this gotcha:
BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
http://support.microsoft.com/?id=302621
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
chinn wrote:
> I am doing a bulk insert and i am A DBO on the database and i can't do it
unless i
> added my self to the bulkinsert admin server role.
> do any body why is that.why it needs a server role..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Regarding Bulk Copy in Transactional Replication..

Hi,
I got typical scenario in transacrtional replication. i want to avoid bulk
copy during replication.
Let me explain my scenario.
I’m doing transactional replication between two databases.
When publisher and subscriber created the data going to be bulk copied from
publisher table to subscriber table.
My main intension was to create replication between different tables with
different fields in which I got succeeded.
But main problem is I want to stop this bulk copy from publisher to
subscriber.
Scenario 1: my subscriber table may contain some previous data which will be
replaced with publisher data due to bulk copy.
I don’t want this .I want to avoid this bulk copy and wants to create
procedures(for insert, update and delete transactions) in subscriber which
will take care of replication.
I achieved almost everything but not able to avoid this bulk copy during
the creation of subscriber.
As I know the only way I can stop bulk copy is by creating subscription
without subscription agent. But here without subscription agent the
procedures(for insert, update and delete transactions)
won’t get created in subscriber.
Help me regarding the above scenario and I need it urgently.
Regards
Baji Prasad
I'm not entirely clear about your scenario, but it seems to me that you are
talking about nosync initialisations. Please have a look here and see if this
applies:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||hi paul..
im very thankfull for ur reply.
yes it helped me a lot atleast i got an idea how to avoid bulk copy.
again thanks man...
Regards
Baji Prasad
"Paul Ibison" wrote:

> I'm not entirely clear about your scenario, but it seems to me that you are
> talking about nosync initialisations. Please have a look here and see if this
> applies:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
sql

Regarding Bulk Copy During Transactional Replication

Hi ,

I got a problem in regarding Transactional Replication.

Let me explain my scenario.

I’m doing transactional replication between two databases.

When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

My main intension was to create replication between different tables with different fields in which I got succeeded.

But main problem is I want to stop this bulk copy from publisher to subscriber.

Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.

I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)

won’t get created in subscriber.

Help me regarding the above scenario and I need it urgently.

in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.

Regarding Bulk Copy During Transactional Replication

Hi ,

I got a problem in regarding Transactional Replication.

Let me explain my scenario.

I’m doing transactional replication between two databases.

When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

My main intension was to create replication between different tables with different fields in which I got succeeded.

But main problem is I want to stop this bulk copy from publisher to subscriber.

Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.

I don’t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)

won’t get created in subscriber.

Help me regarding the above scenario and I need it urgently.

in sp_addsubscription, look at paramter @.sync_type, you can specify 'replication support only' and 'initialize with backup'. That allows you to skip the bcp. You can find more information about the latter here: http://msdn2.microsoft.com/de-de/library/ms147834(SQL.90).aspx.

reg Bulk Insert

Hi,
I have a query reg the bulk insert. I have some data in text file with
tab delimetered and using BULK INSERT to get this data into a phycical table
.
i have more columns in the table than in the file. Table is having 10
columns and text file is having only 8 columns.
i am getting the below error ..
'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
Is there any option for Bulk insert to specify about the number columns to
be inserted in the table?
Thanks in advance
BhaskarYou can do this by specifying a FORMATFILE on your BULK INSERT statement.
The format file describes the fields in the text file and which are to be
imported. See 'Using Format Files' the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
> Hi,
> I have a query reg the bulk insert. I have some data in text file with
> tab delimetered and using BULK INSERT to get this data into a phycical
> table.
> i have more columns in the table than in the file. Table is having 10
> columns and text file is having only 8 columns.
> i am getting the below error ..
> 'Bulk insert data conversion error (type mismatch) for row 1, column 8 '
> Is there any option for Bulk insert to specify about the number columns to
> be inserted in the table?
> Thanks in advance
> Bhaskar|||Hi ,
Thanks for the reply. Is there any example with anybody about how to create
the Format file..
Thanks
Bhaskar
"Dan Guzman" wrote:

> You can do this by specifying a FORMATFILE on your BULK INSERT statement.
> The format file describes the fields in the text file and which are to be
> imported. See 'Using Format Files' the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
> news:9AD6481B-C9E5-4676-ADB4-CF00234E944A@.microsoft.com...
>
>|||The Books Online has examples of format files with fewer fields than the
destination table as well as with more fields.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bhaskar" <Bhaskar@.discussions.microsoft.com> wrote in message
news:93DFC97E-B9D7-461E-BD17-702996CA0F96@.microsoft.com...
> Hi ,
> Thanks for the reply. Is there any example with anybody about how to
> create
> the Format file..
> Thanks
> Bhaskar
> "Dan Guzman" wrote:
>sql

Re-Execution of For each Loop Container

I have a For each loop container and inside the same a group of text files are loading to a table using bulk insert task.If there are duplicate files i dont wish to load it into the table. Duplicate check can be done by script task but how do i skip the files and repeat the for loop for the next files.

That means i want to skip some files and load the other files in a for loop.
How do i do that.

Can anybody please help me.

So here is what you do

Loop container has inside a Script task that checks for a duplicate file

and a BULK INSERT task that does what it says on the tin.

The script task and the BI task are joined by workflow.

Declare a variable say something like FileAlreadyThere of type DT_BOOL

(boolean)

In the script task if the file is already there set the value of this

variable to TRUE and if not then FALSE.

The workflow now between the two tasks should be based on an expression

as well as outcome so have a look at this article and you want to set

your expression to something like

@.FileAlreadyThere == FALSE

http://wiki.sqlis.com/default.aspx/SQLISWiki/LogicalOrExample.html

Allan

"Raj Amb@.discussions.microsoft.com"

wrote in message

news:1b962d72-8a6d-4b3c-bdab-e86bfda3270b@.discussions.microsoft.com:

> I have a For each loop container and inside the same a group of text

> files are loading to a table using bulk insert task.If there are

> duplicate files i dont wish to load it into the table. Duplicate check

> can be done by script task but how do i skip the files and repeat the

> for loop for the next files.

>

> That means i want to skip some files and load the other files in a for

> loop.

> How do i do that.

>

> Can anybody please help me.|||Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.

but i want to continue/iterate the for loop till the last file , how do i do that.|||

Raj Amb wrote:

Thank you very much for your post. I would like to know one more thing that in the same for loop if the bulk load of one file fails the whole package(For loop) fails.

but i want to continue/iterate the for loop till the last file , how do i do that.

This is controlled by two properties:
1) FailPackageOnFailure on each task or container - set it to false to ignore errors.
2) Also, each task and container have MaxErrorCount - increase it to desired value.