Error Using Bulk Insert On A Comma Delimited File
Mar 18, 2008
I am having a problem using the Bulk Insert task. I am getting the msg:
SSIS package "Package.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "You do not have permission to use the bulk load statement.".
Task failed: Bulk Insert Task
SSIS package "Package.dtsx" finished: Success.
I have been granted ownership of the database. I also tried in one of my old databases that I just finished
developing and I got the same msg.
The file I am importing is comma delimited. I am importing it into a table that has 50 bytes allocation for each field (the max input field size is 40 bytes).
The connection is solid;
Format = “Specify�
RowDelimiter = {CR}{LF}
columnDelimiter = Comma {,}
No other options are set.
The data looks like:
"tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000",
Any ideas why I am getting this message?
View 4 Replies
ADVERTISEMENT
Sep 3, 2015
I am struggling on using bcp to import data. Here is my steps:
1. I created a Test database on my localhost
2. In the Test database, I created a Test table, the query is here for your convenience:
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] [varchar](128) NULL,
[IP] [varchar](15) NULL,
[OS] [varchar](128) NULL,
[Code] ....
I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:RXieSQLTest.fmt –T
Here is the format file:
9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
[Code] ...
The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:
network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:RxieSQL20150902FullTest.rpt -f C:RxieSQLTest.fmt -T
I got the following error messages:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
[Code] ...
I do want to mention here is the rpt file contains three BOM characters EF BB BF at the beginning of the file.
View 9 Replies
View Related
Nov 9, 2007
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
it was never a problem in DTS 2000.
any work around.
Thanks,
View 7 Replies
View Related
Jun 29, 2015
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]...
View 5 Replies
View Related
Nov 22, 2013
I have a problem in a stored procedure trying to insert a comma delimited list into rows.The parameters for example would be somthing like....
@GarageID int = 20,
@Cars nVarChar(200) = Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS
I need to split where the comma is for each new row, and separate each value from either side of the '~' like so....
GarageID Make Model
--------------------------------
20 Ford Fiesta
20 BMW 320
20 Volvo 340
20 Jaguar XJS
View 2 Replies
View Related
Oct 13, 2004
I am passing in a string of dates, delimited by a comma.
So
01/01/04, 02/01/04, 03/01/04
etc
I would like to enter each of these values into a table via an INSERT stored procedure.
Does anyone have any code for this?
View 1 Replies
View Related
Jul 20, 2005
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
View 2 Replies
View Related
May 18, 2004
Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.
View 4 Replies
View Related
Aug 4, 2007
I'm trying to upload a small Web application with a one table database. The hosting company, GoDaddy requires that I upload the database as a comma delimited file.
I created the database in Visual Web Developer Express but also have Visual Studio and SQL Server Management Studio Express.
I can't figure out how to export the database into a comma delimited file using any of these tools.
This should be simple like it is in Access but that doesn't seem to be the case. This is holding up deploying my Web Application.
Can anyone help me?
Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi,On SQLServer 2000, I have a table with a following structure:MYTABLEcol1 char,col2 date,col3 numberMy Objective:------------Externally (from a command line), to select all columns and write theoutput into a file delimited by a comma.My method:---------1. Probably will use OSQL or BCP to do this.2. Use the following syntax:select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)from MYTABLE;My 3 Problems:-------------1) If there is a NULL column, the result of concatenating any value withNULL, is NULL. How can I work around this? I still want to record thiscolumn as null. Something like say from the example above, if col2 isnull, would result to: APPLE,,52) The time format when querying the database is: 2003-06-24 15:10:20.However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can Ipreserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost theSS.3) Which utility is better? BCP or OSQL?For OSQL, it has a "-s" flag which gives me the option of putting acolumn separator. But the result is:"APPLE ,14 JUN 2003 , 5"I don't need the extra space.While for BCP, there is no column separator flag.You will notice from my inquiry above that my background in SQLServer isnot very good.Thanks in Advance!!RegardsRicky*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Apr 12, 2006
Hello...
I have a problem... When I insert data from a comma delimited
file using this mehod a flat file connection sorting, merge join and
inserting into the database I get "" around all the data!! The
quotes end up around the column names and everything! I had to go
in and manually remove the quotes in the text file to get some of my
data conversions to work. I know there is a better way. How
do I get SSIS to load the data without the quotes? This is an
example of the data in the file:
"1007","1","A","","Congratulations - No Health Violations Found","11/02/2005","1007"
When I remove the quotes I do not have any problems. How do I
do this without modifying the underlying data? Any ideas would be
greatly appreciated!!
Thank you for your help!
SD
View 3 Replies
View Related
Aug 1, 2007
Hi,
I'm trying to deploy my Web site to GoDaddy. They told me I have to export the SQL Server Express database to a comma delimited file and then upload that file. The export procedure is simple in Access but I don't see any way to do it in SQL Server or from Visual Web Developer or Visual Studio.
Also, I can ask them, but I assume I have to export each table separately and also export the ASPNETDB as well.
Thanks for the help
View 2 Replies
View Related
Oct 19, 2007
Hi,
I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file.
So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited. I tried finding examples but couldn't see what I should do to have a comma delimited file. (I'm getting a bit tired now, so I might be missing something!)
I have created a bat file containing the following code:
bcp "TestDB..GA_FSM_DCSF_Extract" out "C:GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:error.log" -c -T -S srckvzg2j -r
Any help / pointers would be much appreciated.
Thanks,
Henrik
View 8 Replies
View Related
Aug 10, 2006
hi ,
I have 2 sql tables. 1 is the header table and another is the detail table. How can I have the header record being appended in the text file and then have the detail records being appended to a same text file again with comma delimited ?
View 3 Replies
View Related
Aug 28, 2001
I want to join differnet tables and import the data into comma delimited text file. There will be lot of checks like if then else to manipulate data. I want to use stored procedure but don't know how to output to text file. Is there any utility which can be used in stored procedure. In future this will be run as an automated job.
Thanks in advance.
View 1 Replies
View Related
Feb 29, 2004
Hello, i need to load some data from a long comma delimited text file, How can a i do that, using t-sql?, thanks for your help!!!!!
View 5 Replies
View Related
Dec 27, 2007
hi im using the import export wizard to import a file where my recoreds are comma delimited but when i HAVE to use a comma im using quotes to escape
"CN=Administrator,CN=Users,DC=aboneng,DC=local",user,Administrator,Built-in account for administering the computer/domain,"CN=Administrator,CN=Users,DC=aboneng,DC=local",4,20050421014154.0Z,20060518065932.0Z,
with this example
CN=Administrator,CN=Users,DC=aboneng,DC=local === should be the first column
user, ==== the second column
Administrator === third and so forth.
i thought it was a standard to use quotes when escaping your delimiter but sql is bringing in the records as comma delimited as normal is there anyway around this?
View 1 Replies
View Related
Jan 18, 2006
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
DECLARE @FileID INT
SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
View 4 Replies
View Related
Jan 15, 2001
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
View 2 Replies
View Related
Dec 13, 2007
Hi,
I am new to SSIS but i have avg working knowledge in sql.
My problem is as follows ,I have a text pipe dilimited file in some folder and the number of columns and the name of the column is not consistant. It can have N number of column and it can have any column names. I need to load this text file data into a sql table. All that i want is to load this file to SQL Database with some temp name. Once i get the table in SQL Database, i can match the column names of both taget table and this temp table and only push those column which matches with the target table. For this i can frame Dynamic SQL. This part is clear to me.
Now the problem is , I developed a SSIS pacakge to push the text file to SQL Table. I am able to do this. But if i change the column names or added new column SSIS is not able to push the new columns. Is this functionality available in SSIS, is it can be dynamic like this?
I hope i am clear with my prob... if need any clarification please let me know
thanks in advance
Mike
View 3 Replies
View Related
Oct 11, 2007
Using BCP or BULK INSERT you can specify an Error File (-e and ERRORFILE). However this does not seem to be exposed in SSIS via the Bulk Insert Task.
Does anyone know if I'm missing something and the Property is called something else or if can be accessed via script?
Cheers,
-Ryan
View 1 Replies
View Related
Oct 1, 2007
Msg 4862, Level 16, State 1, Server PATH-SQLDEV, Line 2
Cannot bulk load because the file "c:DATABATCHBCPFormat.fmt" could not be read. Operating system error code (null).
Above is the error I get. The problem is I do not know what is causing this error. It occurs when I attempt to use SQLCMD with bulk insert.
I am using SQLserver 2005 and I have a similar set up in a test database that works, why this format file does not is beyond me, but my experience is when the format file has an error in it, such as a mispelled datatype or a incorrect column number, the error will zero in on that, rather then declare the entire file unreadable. Furthermore if I go into the file, change something so where it is incorrect (like a column number) it will zero in on that error. So I know that the format file can be read. If I knew what this error was all about I would at least know where to begin in fixing it. I have also tried using a very small sample file for the data being inserted. Same error.
Please help
View 2 Replies
View Related
Oct 25, 2006
Hello
I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error
"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\ENDUSER-SQLEnduserTextB1020063.txt" does not exist."
The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.
Thanks
Chris
View 12 Replies
View Related
Nov 9, 2014
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
View 0 Replies
View Related
Mar 13, 2006
BCP thru xp_cmdshell from stored procedure:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE
EXEC xp_cmdshell 'bcp database.dbo.table in c:scheduled.csv -S SERVERSQLEXPRESS -T -t, -r -c -e "error.txt"';
This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.
=================================================================
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
=================================================================
Error message when using BULK INSERT as follows:
BULK INSERT database.dbo.table from 'c:unscheduled.csv' with
(FIELDTERMINATOR = ',', ERRORFILE = 'c:error.txt');
Returns the following error message:
=================================================================
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt" could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).
=================================================================
The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.
EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2
View 1 Replies
View Related
Apr 8, 2008
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 1 Replies
View Related
Apr 11, 2007
My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.
bcp <database name>.dbo.<table name> format nul -T -S <server name> -n -f out.fmt
The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".
Progress: The Bulk Insert task is completed. - 100 percent complete
Task Bulk Insert Task failed
Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.
View 7 Replies
View Related
Mar 27, 2007
Hi all,
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!
View 1 Replies
View Related
Mar 27, 2007
Below is an overview of my problem:
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
View 1 Replies
View Related
Jul 2, 2005
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.
Here is an approach I am trying:
TABLE Events(EventID int, Categories varchar(200))
EventID Catetories
--------------------------
1 ‘6,8,9’
2 ‘2,3’
PROCEDURE ListFilteredEvents
@FilterList varchar(200) -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList
Result:
EventID
----------
2
How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.
Can someone suggest an approach?
Any examples would be greatly appreciated!
Gary
View 3 Replies
View Related
Sep 27, 2004
Hi,
Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.
column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int
pId has been set to primary key with auto_increment.
My csv file has 2 columns of data and it looks like follows:
program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"
Now i use BULK INSERT like this
"BULK INSERT ord_programs FROM 'C:datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='', FIRSTROW=2)"
to import data into my table in SQL server and it gives me this error
"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"
I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...
Please help me out guys and please post a snippet of code if you have.
Thank You.
View 2 Replies
View Related
Jun 5, 2015
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
 col1 [varchar](10) NOT NULL,
 col2 [datetime] NOT NULL,
 col3 [date] NOT NULL,
 col4 [varchar](6) NOT NULL,
 col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1Â SQLCHAR 0 Â 10Â "@|@"Â 2Â Col2Â ""
1Â SQLCHAR 0 Â 10Â "@|@"Â 3Â Col3Â ""
2Â SQLCHAR 0 Â 6Â "@|@"Â 4Â Col4Â Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1Â SQLCHAR 0 Â 10Â "@|@"Â 2Â Col2Â ""
Nothing works ....
View 7 Replies
View Related
Jan 17, 2008
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 13 Replies
View Related
