|
|
| Meenal Dhody 2005-09-16, 3:23 am |
| Hi,
In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
that enough to use bulk insert to a dbo table? I am getting the following
error with the current permissions:
Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
operation.
I wanted to avoid giving the sql account dbo privileges.
thanks
Meenal
| |
| Andrew J. Kelly 2005-09-16, 9:23 am |
| You mention a dbo table but the error suggests it is not dbo. In any event
can you post the actual code you are trying to execute?
--
Andrew J. Kelly SQL MVP
"Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
news:u7laRHnuFHA.3256@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> In SQL 2000 if a sql account has bulkadmin, datareader and datawriter it
> that enough to use bulk insert to a dbo table? I am getting the following
> error with the current permissions:
>
> Currently user is not the owner of GDS.HOLDINGS_IMPORT. Cannot perform SET
> operation.
>
> I wanted to avoid giving the sql account dbo privileges.
>
> thanks
> Meenal
>
>
| |
| Meenal Dhody 2005-09-16, 11:23 am |
| The code looks something like this - (if I give the sql account db_ddladmin
then this executes without errors)
CREATE PROCEDURE [dbo].& #91;sp_Custodian_Imp
ortHoldings]
@BatchId VARCHAR(25),
@FilePath VARCHAR(200),
@FormatFile VARCHAR(200),
@CustodianId INT
AS
/ ********************
********************
********************
***************
****
Procedure: sp_Custodian_ImportH
oldings
This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_I
MPORT
********************
********************
********************
****************
***/
DECLARE @statusMsg VARCHAR(255)
SET NOCOUNT ON
-- Delete from CUSTODIAN_HOLDINGS_I
MPORT for the Custodian Source before
doing a bulk insert
DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@Custodi
anId
SET @statusMsg = 'Loading CUSTODIAN_HOLDINGS_I
MPORT'
-- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_I
MPORT and update
CUSTODIANID,GDSSOURC
EID
BEGIN TRANSACTION trans_BulkInsert_Upd
ate
-- Bulk insert into the CUSTODIAN_HOLDINGS_I
MPORT table
EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @FilePath + '''' +
' WITH (FORMATFILE = '''+ @FormatFile + ''')')
IF ( @@ERROR <> 0 )
BEGIN
SET @statusMsg = 'CUSTODIAN_HOLDINGS_
IMPORT - Bulk insert failed'
PRINT @statusMsg
ROLLBACK
RAISERROR('HOLDINGS_
IMPORT_FAILURE', 16, 1)
RETURN -1
END
thanks
Meenal
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23V5q3csuFHA.4020@TK2MSFTNGP12.phx.gbl...
> You mention a dbo table but the error suggests it is not dbo. In any
event
> can you post the actual code you are trying to execute?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
> news:u7laRHnuFHA.3256@TK2MSFTNGP09.phx.gbl...
following[color=dark
red]
SET[color=darkred]
>
>
| |
| Andrew J. Kelly 2005-09-16, 1:23 pm |
| Maybe it has to do with the fact you are using EXEC as well. If you hard
code the filename and format file will that run?
--
Andrew J. Kelly SQL MVP
"Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
news:uLb0JgtuFHA.3596@TK2MSFTNGP15.phx.gbl...
> The code looks something like this - (if I give the sql account
> db_ddladmin
> then this executes without errors)
> CREATE PROCEDURE [dbo].& #91;sp_Custodian_Imp
ortHoldings]
> @BatchId VARCHAR(25),
> @FilePath VARCHAR(200),
> @FormatFile VARCHAR(200),
> @CustodianId INT
> AS
> / ********************
********************
********************
***************
> ****
> Procedure: sp_Custodian_ImportH
oldings
> This stored procedure does a bulk insert into CUSTODIAN_HOLDINGS_I
MPORT
> ********************
********************
********************
****************
> ***/
>
> DECLARE @statusMsg VARCHAR(255)
> SET NOCOUNT ON
>
> -- Delete from CUSTODIAN_HOLDINGS_I
MPORT for the Custodian Source before
> doing a bulk insert
> DELETE FROM GDS.DBO.HOLDINGS_IMPORT WHERE CUSTODIANID=@Custodi
anId
>
> SET @statusMsg = 'Loading CUSTODIAN_HOLDINGS_I
MPORT'
>
> -- Transaction to do Bulk insert into CUSTODIAN_HOLDINGS_I
MPORT and update
> CUSTODIANID,GDSSOURC
EID
> BEGIN TRANSACTION trans_BulkInsert_Upd
ate
>
> -- Bulk insert into the CUSTODIAN_HOLDINGS_I
MPORT table
> EXEC ('BULK INSERT GDS.DBO.HOLDINGS_IMPORT FROM ''' + @FilePath + '''' +
> ' WITH (FORMATFILE = '''+ @FormatFile + ''')')
>
> IF ( @@ERROR <> 0 )
> BEGIN
> SET @statusMsg = 'CUSTODIAN_HOLDINGS_
IMPORT - Bulk insert failed'
> PRINT @statusMsg
> ROLLBACK
> RAISERROR('HOLDINGS_
IMPORT_FAILURE', 16, 1)
> RETURN -1
> END
>
>
>
> thanks
> Meenal
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:%23V5q3csuFHA.4020@TK2MSFTNGP12.phx.gbl...
> event
> following
> SET
>
>
| |
| Meenal Dhody 2005-09-16, 8:23 pm |
| I tried a simpler statement like this:
BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
and got the following message:
The current user is not the database or object owner of table
'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
Is the only option to maybe create the table that you need to bulk insert to
under that login?
thanks
Meenal
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:eCqBnCuuFHA.2064@TK2MSFTNGP09.phx.gbl...
> Maybe it has to do with the fact you are using EXEC as well. If you hard
> code the filename and format file will that run?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
> news:uLb0JgtuFHA.3596@TK2MSFTNGP15.phx.gbl...
/ ********************
********************
********************
***************[colo
r=darkred]
********************
********************
********************
****************[col
or=darkred]
update[color=darkred
]
+[color=darkred]
perform[color=darkre
d]
>
>
| |
| Andrew J. Kelly 2005-09-17, 8:23 pm |
| It seems as if this is a BUG.
http://support.microsoft.com/defaul...kb;en-us;302621
You can add the ddl_admin like it states or try using BCP through
xp_cmdshell. I don't believe you will have the same issue with bcp.
--
Andrew J. Kelly SQL MVP
"Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
news:u8mOJ$uuFHA.3500@TK2MSFTNGP09.phx.gbl...
>I tried a simpler statement like this:
>
> BULK INSERT gds.dbo.tmpAccountAddress FROM 'd:\data\nalist.dat'
>
> and got the following message:
>
> The current user is not the database or object owner of table
> 'gds.dbo.tmpAccountAddress'. Cannot perform SET operation.
>
> Is the only option to maybe create the table that you need to bulk insert
> to
> under that login?
>
> thanks
>
> Meenal
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:eCqBnCuuFHA.2064@TK2MSFTNGP09.phx.gbl...
> / ********************
********************
********************
***************
> ********************
********************
********************
****************
> update
> +
> perform
>
>
| |
| Meenal Dhody 2005-09-20, 3:23 am |
| Thanks - I went with creating the tables under the sql login so I would not
have to give the login ddladmin rights.
Meenal
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:eecpIS8uFHA.2568@TK2MSFTNGP15.phx.gbl...
> It seems as if this is a BUG.
> http://support.microsoft.com/defaul...kb;en-us;302621
>
> You can add the ddl_admin like it states or try using BCP through
> xp_cmdshell. I don't believe you will have the same issue with bcp.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Meenal Dhody" < meenal_dhody@hotmail
.com> wrote in message
> news:u8mOJ$uuFHA.3500@TK2MSFTNGP09.phx.gbl...
insert[color=darkred
]
hard[color=darkred]
/ ********************
********************
********************
***************[colo
r=darkred]
********************
********************
********************
****************[col
or=darkred]
failed'[color=darkre
d]
any[color=darkred]
>
>
|
|
|
|