BULK INSERT And APPLICATION ROLE
I want to add bulkadmin permission to my applicatio role. Is it a
posible.
My windows account havo only public permission on database.
I'm using application role
EXEC sp_approlepassword 'MyRole', 'password';
Therefore I want to BULK some data with BULK INSERT command.
Error is:
The current user is not the database or object owner of table
'tablename'. Cannot perform SET operation.
Thanks in advance.
View Complete Forum Thread with Replies
Related Forum Messages:
Bulkadmin Role (BULK INSERT)
Hello,I am trying to load a simple tab-delimited data file to SQL Server. Icreated a format file to go with it, since the data file differs fromthe destination table in number of columns.When I execute the query, I get an error saying that only sysadmin orbulkadmin roles are allowed to use the BULK INSERT statement. So, Iproceeded with the Enterprise Manager to grant myself those roles.However, I could not find sysadmin or bulkadmin roles using theEnterprise Manager. From what I read from my books, I thought thesewere fixed server roles and that they would be there.So I have a few questions:1) How do I create a user account/role that can issue BULK INSERTcommands?2) Why is BULK INSERT considered a dangerous operation that itrequires special privileges? What are its implications? I have acouple of books that say that a user should be aware of itsimplications before using it, but they don't actually describe whatthose implications might be.3) It seems that I can load the data file using BCP utility, withoutsuch privileges. If so, what is the difference?Thanks!
View Replies !
Application Role, App Role
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) for the given connection string Dim connstring As String = "Data Source=Northwind;Initial Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;" Dim cmd As SqlCommand Dim param As SqlParameter Dim cookie As Byte() Dim cn As New SqlConnection(connstring) If (cn.State = ConnectionState.Closed) Then cn.Open() End If Please help.. Thanking you, Nirmala
View Replies !
Adding Users To A Role In Bulk
I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help. Here is the proc CREATE proc sp_MyProc @DBName varchar(256) As Declare UID_Cursor Cursor For Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name Open UID_Cursor Declare @Name as varchar(256) Declare @TempString as varchar(8000) Fetch Next from UID_Cursor into @Name While (@@Fetch_Status <> -1) Begin Set @TempString = 'sp_addrolemember ''db_datareader'', ''' + @Name + '''' Exec(@TempString) Fetch Next from UID_Cursor Into @Name End Close UID_Cursor Deallocate UID_Cursor
View Replies !
Application Role
I am confused . What is considered an application and how SQL would know ? If I have a web site accessing SQL VIA IIS will SQL Server treat it as an application ? How about MS Excel ? Also , If I was to use the application of Power builder , using app role , how do control which user can use the app ? Thanks
View Replies !
Application Role
With reference to Mr. Eric Charron's article about Implementing Application Roles in SQL7, I have used this tips in my application. But I am facing the following problems. I am looking for some solution from this User's Group. Problem: I am using application (VB6/SQL7) role. I have number of reports. To display reports I follow the following steps. 1) Open Connection 2) Set application role 3) Execute procedure for the report 4) Close the connection Since I have no. of reports, I go through the aboue steps no. of times. For first 2 times it works fine but 3rd time, it gives error ie. application role not set properly. RM Joseph
View Replies !
Application Role
hi all ! first time i created application role with read and write permissions , once application accessing automatically it assigns rd and wr permissions to that users or previously wht permissions are there in logins will it be reflected to that application role. once we create the appliation role , is there any changes we have to do in coding part pls clear my doubt. regards manoj
View Replies !
Bulk Insert - Bulk Load Data Conversion Error
Im 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 ?
View Replies !
Troubleshooting A New Application Role
Hi all, This one is a real X FIle, just without Mulder, Scully or the Lone Gunmen! I have a database, to which access must be restricted via a sole application. So, I have to use an application role. I go in the database and run these statements to add and activate the roles, respectively; Exec sp_addapprole 'Sirius', 'password' (The system confirms the role is created.) Exec sp_setapprole 'Sirius', 'password' 'odbc' (The system confirms the role is activated.) Right, now I should not be able to connect using anything but this role, agreed? But here's where things go wrong. I can then successfully connect from another computer by using MS query from Excel, from a login that is not even a member of the Public Role! I tried again, started and stopped the Server/DTS/Agent services and dropped the old role after each successful login before recreating it. I've checked my syntax exhaustively. I must be doing something wrong, or overlooking something, otherwise MS has a major security problem! (Just hope the Cancer Man doesn't find out!!) Thanks in advance everyone, Jaishel.
View Replies !
Security For Application Role
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database? Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator? Thanks
View Replies !
Application Role And SSRS
Hi dear reader I made an application that uses a Sql Server 2005 Express DataBase. In the database I made a application role. When the user logs into my application I run this procedure: If Not sqlConnectionCR Is Nothing Then If Not sqlConnectionCR.State = ConnectionState.Open Then sqlConnectionCR.Open() SqlConnection.ClearAllPools() ConsultasSqlCommand = New SqlCommand ConsultasSqlCommand.CommandType = CommandType.Text ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap" ConsultasSqlCommand.Connection = sqlConnectionCR ConsultasSqlCommand.ExecuteNonQuery() End If Else.... I understand that this procedure connects to my sqlserver database as my application role Ok, so far no problems in reading and manipulating data. The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access.... I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role). So, my cuestion is: How can I tell Report Server to use the application role to display reports? Thank you for your time and help. Giber
View Replies !
I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?
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!
View Replies !
Use SQL 7.0 Application Role With Crystal 8.0 Crpe32.dll
using vb6 and crystal global32.bas to make function calls to the crpe32.dll and there doesn't seem to be any calls to pass the application role id with password. ap roles must be passed at the ad hoc level, which means i have to execute the system stored procedure for the ap role thru crystals connection to the sql server. HELP....seagate has tried to help, but want me to use odbc dsn, which is exactly what i'm trying to avoid. oledb provider works like a champ, except for app roles with crystal.
View Replies !
Connecting To A Database From VB Using An Application Role
I know how to create an Application role in SQL server 7. Now how do I connect to the database from VB using that Application Role? I can't find anything about this topic anywhere. Is this the purpose of an Application role or am I way off? Thanks for the help Steven Abt StevenA@grsgroup.com
View Replies !
Application Role And SQL Express (2005)
Hello, Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)? Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005 Thanks John
View Replies !
Create Database With Application Role...
Hi, I want my application to create database and I do the following things: 1)Create application role 2)Grant create database to application role 3)Activate application role 4)Create database and I get the answer: CREATE DATABASE permission denied in database 'master'.
View Replies !
Application Role Losing Connection?
Hi, I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server. I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures. The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore. Any ideas? Adrian
View Replies !
Web Application And Role Management In Sql Server 2005
Hi every body I'm developing a web application and i like to use the sql server 2005 role management features istead of developing a role management package in my program, I can do it on my tables and othe database items but I have no idea about using database access rights in my web pages to permit some one viewing or updating a web form... Is there any system table or system stored procedure showing access rights in my data base? or is there another idea to do this?? by Thanks Javaneh
View Replies !
SQL Server 2005 Application Role's Problem
Hi all. I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005. My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables: 1) Table A 2) Table B 3) Table C The USER login is a restricted user which has only SELECT permission to Table A, B and C. I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime". Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A. I have tried for almost 2 days but am still clueless. Any ideas? Thank you so much.
View Replies !
Sending SQL 2005 Dbmail When Using Application Role
I am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights. Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
View Replies !
Get Sql Err Message 15422 When Activating Application Role.
Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX). Developing a desktop application which calls a local instance of ".sqlexpress". This app needs to set data base options and add/del various table columns. When activating the application role, I get the following message: HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level. Anyone know what this message means? I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious. Thanks Gary
View Replies !
Login Failed For 'user' (Application Role)
I've created a database in SQL Express and I have a Windows form attempting to connect to it through SQL Authentication. Connection string: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=SearchAppRole; Password=password;"; The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate". If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails) sp_setapprole 'SearchAppRole', 'password' select * from recipe If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement: private string connString = @"Data Source=.sqlexpress;Initial Catalog=SQLTestDatabase;User ID=sa; Password=sa_password;"; However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error: System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
View Replies !
Pros: How To Bulk Delete And Bulk Insert?
I 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
View Replies !
Application Role How Query A View Whose Reference Table Is In Another Db?
Hi everybody. I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1 I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)). With the following query SELECT USER_NAME() I see that the approle is being used. Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error: The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context. What should I do to make it work? The table in DB2 has the same schema of the view in DB1 which refers to it. I put the DB1 TrustWorthy and both the database have the db_chaining option activated. Any idea on how to solve the problem would be widely appreciated. Thank you very much. Vania
View Replies !
SQL Server 2000 And 2005 - Application Role - Sp_setapprole
Hi All, Is there any limitation for setting password to an approle (like it should be 8 character long, should not start with numbers) ? If so, what are those limitations ? Does the same is applicable for sp_setapprole which uses the same password ? please confirm, for both SQL Server 2000 and 2005 versions. thanks in advance, Regards, Kailai
View Replies !
Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: &&"Cannot Fetch A Row
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table: 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 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).". Task failed: Bulk Insert Task In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative'); What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF} Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
View Replies !
Uploading SQL Express To SQL Server 2005 - Role's Causes Application Error With SSE Provider
I am very frustrated. Everything works on the local host but when I upload to server I can login to the admin role I created, but when I try to access pages that have role priveleges I get the following error: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. The ASPNETDB.MDF database was uploaded using the Database Publishing Wizard. Please help!
View Replies !
Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'
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
View Replies !
Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View Replies !
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT
Hi, i have a file which consists data as below, 3 123|| 456|| 789|| Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below. BULK INSERT TABLE_NAME FROM 'FILE_PATH' WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||') but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting. can anyone help me how to do this? Thanks, -Badri
View Replies !
Compare BULK INSERT Vs INSERT
Hello, I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted. Sincerely, -Lawrence
View Replies !
Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)
Hi~, I have 3 questions about memory based bulk copy. 1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)? For example, how much insert row at below sample?(the max value of nCount) for(i=0 ; i<nCount ; i++) { pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData)); } 2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop) 3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ? BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK); ------------------------------------------------------- My solution is like this. Is it correct? // CoCreateInstance(...); // Data source // Create session m_TableID.uName.pwszName = m_wszTableName; m_TableID.eKind = DBKIND_NAME; DBPROP rgProps[1]; DBPROPSET PropSet[1]; rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED; rgProps[0].colid = DB_NULLID; rgProps[0].vValue.vt = VT_BSTR; rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS; rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK"; PropSet[0].rgProperties = rgProps; PropSet[0].cProperties = 1; PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET; if(m_pIOpenRowset) { if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad))) { return FALSE; } } else { return FALSE; }
View Replies !
Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns. The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL. The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file. So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted). I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task. Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task? From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View Replies !
Something Like A Bulk Insert...
Hi,I've a SP that insert records in one table and then call another insert SP on a second table. The first table is like a master table and the second is like a child table. After inserting the right record in the master table, I've to insert some record in the child table. This records differ each other only by two of about ten field, so what I'd want is not to call the second SP X times, but only one time.. Is it possible??ExampleTable1: Id (identity), Desc;Table2: Id (identity), Id_table1, Id_TableX, Num, Field1, Field2, ... Field10.In Table2 only Id_TableX and Num change every time... the other are all the same (for one record in Table1). How can I do? Probably with a bulk insert and a bulk update?? But, can I make a bulk xxx without a file??
View Replies !
BULK INSERT
hi friends i am using bulk insert cmd using my table name but i am facing error.....SO IS IT POSSIBLE TO USE BULK INSERT WITH TEMPRARY TABLE VARIABLE PLZ HELP ME
View Replies !
Bulk Insert
hi friends i am trying for bulk insert using SQL server 2000using this codebulk insert xyzfrom 'D:authors.txt'WITH (FIELDTERMINATOR = ',') but it gve me error saying thatCould not bulk insert because file 'D:authors.txt' could not be opened. Operating system error code 21(error not found). i check file securityit has given full control to the file can any one give me idea about Operating System error code 21(error not found) thanks
View Replies !
Bulk SQL Insert
I am currently working on a simple page to insert 1.6 million UK postcode records into an SQL server table. The table has three columns for the postcode, longditude coordinate and lattitude coordinate. The data is sourced from a pipe (|) delimited txt file and inserted into the database using a FOR loop. The problem I have is that the page will hang after inserting only 10,000 records, the page displays either an invalid View State error or a page cannot be found error. Now I assume the viewstate error stems from the fact that there is a form on the page which simply contains a button to execute the script and a few labels to show the progress. But without the form and associated viewstate the insert still fails to complete.... any ideas?? Would I be better running this on a thread or should I just do it in stages and be patient. I have now modified the page to read the database on load and pick up from where it crashes?
View Replies !
Bulk Insert
Sorry for the piece-by-piece nature of this post, I moved it from adormant group to this one and it was 3 separate posts in the othergroup. Anyway...I'm trying to bulk insert a text file of 10 columns into a table with12. How can I specify which columns to insert to? I think formatfiles are what I'm supposed to use, but I can't figure them out. I'vealso tried using a view, as was suggested on one of the many websitesI've searched, but I clearly did that incorrectly as well.--------------------------------Update:I'm working with the view, and I've got a view that contains the exactcolumns from the table I want. I ran my bulk insert command,BULK INSERT Test..IV10401 FROM 'c:ulkInsertFile2.txt'and it returned the error:Server: Msg 2601, Level 14, State 3, Line 1Cannot insert duplicate key row in object 'IV10401' with unique index'AK2IV10401'.Note: Bulk Insert through a view may result in base table defaultvalues being ignored for NULL columns in the data file.The statement has been terminated.The AK2IV10401 key is comprised of 3 columns and I'm sure that each ofmy rows in the insert file is unique according to those three. Whatshould I be checking for?-----------------------Update 2:I can only successfully insert 1 row. It seems to be treating each rowas an individual primary key when it should be treating them ascomposite keys. I cannot alter the table, since it was created byGreat Plains Dynamics. Is there some sort of switch that I'm missingin my bulk insert statement or can I suppress the errors?
View Replies !
Bulk Insert
HiI have a text file with this information-BEGIN----------------- tekst.txt-------------10, "firstname", "lastname"11, "Mette", "Larsen"--| |--6 000 000, "Michael", "Houmaark"-END-------------------- tekst.txt-------------I use this SQL-query-BEGIN------------------SQL--------------bulk insert tlf.dbo.bruger_data from 'C:TEKST.txt'with(FIRSTROW = 1,FIELDTERMINATOR = '";"',ROWTERMINATOR = '"')-END--------------------SQL--------------But when the data is in the table its still have the " arround the firstnameand lastnamewhat do I do ?????Best RegardsMichael H
View Replies !
Almost There With Bulk Insert
I have BULK INSERT T-SQL statements that work for all of my basic datatables except for one.Here is the problem statement (with obvious replacement of irrelevantpath info):BULK INSERT igbconts_tmp FROM 'C:\my_code_path\IGBCONTS.txt'WITH (KEEPNULLS,FORMATFILE = 'C:\my_data_path\contacts.fmt');And here is the output from this statement:Msg 8152, Level 16, State 14, Line 3String or binary data would be truncated.The statement has been terminated.(0 row(s) affected)This tells me precisely nothing about where the real problem lies. Iam reluctant to post either the table definition or the format filesince they are large (the table, and thus the data file, has 104fields. However, the first few lines in the format file are:8.01051 SQLCHAR 0 0 """ 0 dummy ""2 SQLCHAR 0 0 "","" 1 contact_id ""3 SQLCHAR 0 0 "","" 2 full_name ""4 SQLCHAR 0 0 "","" 3 last_name ""And here are the last couple lines:104 SQLCHAR 0 0 "","" 103 user_defined_field15 ""105 SQLCHAR 0 0 "" " 104 user_defined_field16 ""The table was created using the string length information given to usby the data provider, and those fields that are not strings consist ofa few datetime values and a moderate number of floating point numbers.The message suggests to me that one of the fields is too small for whatwas actually found in the corresponding column in the data file for atleast one record. But in addition to there being over 100 columns,there are several thousand records in the data file!How do I determine precisely where the problem lies?Thanks,Ted
View Replies !
Bulk Insert
Hi, I am trying to do the data transfer using BULK INSERT from a dat file. And the data is only one row . The bulk insert is giving me the error "Bulk insert data conversion error (truncation) for row 1, column 11 (extension1)" The line given below is that data as it appear in the dat file. '20030715','Logiciel','Taching, Inc.','108 Pierson Ave',,'Edison','NJ','08837','USA','732-603-7877',,,,,,,,,,,,,,,,,,,,'N','N','N','N','N','N',, ,2003/jul/15 11:09:33.718000,,,, And that column 11(name of that column is extension1) has no data and the datatype in SQL server for that column is varchar(1). I am not able to figure out why this error. Can anyone help. :mad: Regards
View Replies !
Bulk Insert
I am trying to Bulk Insert into Database from a program in VC++ on to sql2000 database.My code was successful if i use the credentials of SA user. but i need to execute the same process with different user which has DBO privileges on the database in which i have the table.Can anyone tell me what privileges need to be granted for this dbo user to make bulk insert work. thanks a lot for your help. anna
View Replies !
BULK INSERT Maybe ???
I have a directory with files: X.dbf X.mdx Y.dbf Y.mdx ... These files contain updates for my DB (I don't know their structure). How I can insert them in temporary tables on the SQL server ? Note: I don't want to use Import/Export Tool, cause I will need this insert as scripts...
View Replies !
|
TRACKER
