Hi there
I'm still finding my way in SQL server so the problem might be very simple (hopefully...).
Would anybody have any idea why:
grant select on table1 to ReadGroup
works fine, and
grant create table to ReadGroup
works fine, yet
grant select to ReadGroup
results in
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'to'.?
Any help would be immeasurably appreciated
Cheers!
GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?
Is there a way when you issue a grant select to a table or a view to not also grant select for each column.
The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.
The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.
Hi,
I am using SQL 2k5
I have a table with the follwing schema
Table BulkTest
(
ID nvarchar(100),
SubDate Datetime
)
And i have data in a file as follows ( column delimeter is | and row deleimeter is new line character ( windows ) )
4000000|10/25/2007 6:07:32 AM
4000001|10/25/2007 6:07:32 AM
4000002|10/25/2007 6:07:32 AM
4000003|10/25/2007 6:07:32 AM
4000004|10/25/2007 6:07:32 AM
BULK INSERT dbo.BulkTest FROM 'c:\insert.dat' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR='
')
When i execute this statement its failing with the follwing message.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (SubDate ).
Can any one please let me know whats wrong in this and what should be done to make it work
Quick response will be appreciated
Thanks much
~Mohan
Howdy y'all, what the hey am I doing wrong here?
I am trying to suck in a HUGE flat file that is tab-delimited and each row ends with a hex :0D:0A.
The first few lines of the file are:
00000000h: 31 30 30 30 32 09 32 30 30 33 2D 30 31 2D 32 39 ; 10002.2003-01-29
00000010h: 20 30 30 3A 30 30 3A 30 30 2E 30 30 30 09 32 30 ; 00:00:00.000.20
00000020h: 2E 33 39 30 30 09 31 39 2E 38 30 30 30 09 32 30 ; .3900.19.8000.20
00000030h: 2E 33 34 30 30 09 34 32 31 33 37 09 31 2E 30 30 ; .3400.42137.1.00
00000040h: 30 30 0D 0A 31 30 30 30 32 09 32 30 30 33 2D 30 ; 00..10002.2003-0
00000050h: 31 2D 33 30 20 30 30 3A 30 30 3A 30 30 2E 30 30 ; 1-30 00:00:00.00
00000060h: 30 09 32 30 2E 33 35 30 30 09 31 39 2E 38 30 30 ; 0.20.3500.19.800
00000070h: 30 09 31 39 2E 38 37 30 30 09 33 33 39 33 33 09 ; 0.19.8700.33933.
here is my table script:CREATE TABLE [dbo].[HSF_Staging_TEST] (
[OSID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[High] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Low] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Volume] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Splits] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GOHere is my BULK INSERT statement:osql -S(local) -Uusername -Ppassword -Q "BULK INSERT Trades.dbo.HSF_Staging_Test FROM '\devserverinputfilesDataDataHSF.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', TABLOCK)" -o".HSF_Staging_Test_LOG.txt" -e".HSF_Staging_Test_ERR.txt"yeah, dang near perfect code, eh? ;)
well...here is the error I get...Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 1, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 2, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 3, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 4, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 5, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 6, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 7, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 8, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 9, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 10, column 8
(Splits).
Msg 4863, Level 16, State 1, Server TRADES, Line 1
Bulk insert data conversion error (truncation) for row 11, column 8
(Splits).
Msg 4865, Level 16, State 1, Server TRADES, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Msg 7399, Level 16, State 1, Server TRADES, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.Is the problem something about the :OD:0A at the end of each row, rather than just the :0A??? If so, how the heck do I specify that? I think based on my testing so far (also tried '
' as the rowtermination param, but then it gives me a truncation error for the first row only, leading me to think it cannot find the end of the row that way.
I am still looking through archives and on the web, but have not seen anything specific to my issue yet...and cannot believe that I am the first to BULK INSERT this kind of data.
Help is appreciated!
~Paul
Hello everybody,
I'm trying to setup transaction replication between 2 servers. This is a
one-way replication: Server A to Server B, not Server B to Server A.
I am able to replicate all the tables except one. I added
commands to the agent so that it would create an output file, possibly with
more or better information.
Here is a portion of the error causing the failure
Agent message code 20037. The process could not bulk copy into table
'"tblSuppContractFee"'.
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01,
0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 65,
Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6
Message: Replication-Replication Distribution Subsystem: agent
01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into
table
'"tblSuppContractFee"'.[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_repl_alert(3, 4, 65, 14151, ?, 6, N'01sqlft003', N'EDGE',
N'01SQLFT004',
N'EDGE_REPLICATION', ?)}
ErrorId = 65, SourceTypeId = 1
ErrorCode = ''
ErrorText = ''
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_repl_error(65,
0, 1, ?, N'', ?)}
[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec
dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process
could not bulk copy into table
''"tblSuppContractFee"''.'
Can somebody help me in finding a solution for this error? I don't see any
Error Text and there are no resources available for the error code throwing up
in the log file.
Thanks in advance.
I have a dataflow step (flat file -> Sql Server Destination), with a batch size of 2500 records. It fails consistently around 3.6 million records in, with only this error -
[SQL Server Destination [4076]] Error: Unable to prepare the SSIS bulk insert for data insertion.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SQL Server Destination" (4076) failed with error code 0xC0202071. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202071. There may be error messages posted before this with more information on why the thread has exited.
[Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
How can I debug this further to see what's going wrong?
This is a newbie question but, does anyone know how to grant select on all tables within a given database to public with a SQL statement.
View 1 Replies View RelatedHi Gurus, Can any one tell me how to grant select permisision all tables in a DB
like Grant all on [all tables] to myuser.
Thanks in advance.
Srinivas varanasi
grant SELECT on all_columns to yogi_bear;
grant SELECT on all_objects to yogi_bear;
grant SELECT on all_views to yogi_bear;
but it doesn't work, why? I know how to do it with a cursor or dynamic sql, but why won't these sql statements work?
i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.
is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
i am newbie
i created a database in mssql2000 testdb1 and copied data and views from another testdb2 located on another server on testdb1 when i execute select * from table1 i am getting
Invalid object name 'table1'.
if i give
select * from xxx.table1;
xxx being owner name. it works.
how to make
select * from table1
work
Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal
View 7 Replies View RelatedIm having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
Hi, folks:
View 18 Replies View RelatedI have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
Any comments are appreciated,
-Marc
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
Hi,
I was wondering, how do I make a user for my php app, like I do in MySQL?
so here i go explaining my problem: I create an empty database. then I use the .net aspregsql tool to make the tables and the procs and everything, and it does. when i view my database it has the 11 empty tables the sprocs and the roles and everything. BUT when I open up the "asp website configuration" to add some users, it gives the following error:EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'masfeni_fakebetsdb', owner 'dbo'.
View 6 Replies View RelatedI have created a table with ..
username, password, grant
username and password are nvarchar
grant is a bit
I want to check the availability of the username and password and then check if it is granted to access or not ..
so I made a selection query for getting the grant when username ==x and password = y
x=data come from another page
y=data come from another page
then put the selected grant or deny ( true or false) in a data set ..
and then check if it is grant ( true) to complete what I want >>>
else ( deny ....( false )) to return a reponse of deny username or password...
but everytime the dataset is empty ..
this is my code ...
sqlConnection1.Open();
SqlDataAdapter da=new SqlDataAdapter("select grant_deny from users where username='"+Request.QueryString["username"]+"' and password='"+Request.QueryString["password"]+"'",sqlConnection1);
da.Fill(ds,"users");
sqlConnection1.Close()
if (ds.Tables[0].Rows[0]["grant_deny"].ToString()=="true")
{
.
.
.
.
}
else
{
Response.write("error");
}
what is wrong ..??
and if there is anyone has another solution for what I want .. I appriciate any sample code,...
thanks in advance..
Hello All,
I have 2 logins
1. Login1 ( has dbo permissons)
2. Login2 ( Data reader )
I have Created a table Tbl1 using Login1 ,
I have used grant statement to give select permissons to Login2
When I connect to the database using Login2 and use the T-Sql :- Select *
from Tbl1 , I get an error saying Invalid object name 'Tbl1'.
Thanks in Advance
Can someone explain ..what is the basic difference between TSQL Stmts Grant and Revoke?
A reply will be highly appreciated
Thanks
Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.
how to grant alter table and set identity insert column permission in sql server 2005,
help is apprecitate
Hi,
I want one of my database users to be able to grant execute to procedures, but I don't want the user to have sys admin rights.
Is this possible. Kind of a statement like the following:
GRANT EXECUTE ON [GRANT EXECUTE] TO myuser
Thanks in advance
I want to Grant permission for a table which is residing at another server's database. I have added the server as a linked server to my database and i am able to do all DML (Insert, Update, Delete) operations from the source server to the target server's table. But i am not able to Grant permission for the table.
View 11 Replies View Related