March 14, 2010 at 6:06 pm
Hi All,
I am currently facing a problem with Bulk Insert. I am using the below code:
BULK
INSERT #connectivity
FROM 'F:\Conn\Conn.txt'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '|'
)
GO
The first few lines of the file conn.txt are as follows:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent
When I execute the Bulk Insert statement, it inserts only from the row YYYYYYYYY and skips the row XXXXXXXX..
Is there a way to import from the first line XXXXXXX into the table?
Any help is appreciated.. Thanks..
March 14, 2010 at 9:21 pm
One of the really annoying problems with Bulk Insert (and BCP) is that all of the rows must have the exact same number of delimiters otherwise the line with the different number of delimiters "bleeds" over into the next line and both lines are found in error. I don't know if SSIS will provide a more friendly solution because I don't use it.
Would you post your target table definition (CREATE statement) please? I might be able to help although it certainly won't be as fast
--Jeff Moden
March 15, 2010 at 6:13 am
Jeff makes the point about BULK INSERT being quite restrictive because you have to match up the column list and you cannot manipulate the individual columns very easily. OPENROWSET BULK gives you much more flexibility because you can you use it in the same way as a conventional INSERT
statement and you can match up the column list very simply. However it does require a format file.
Here is a typical format file based on the 6 columns you need.
BTW you will have to edit this slightly and change the uppercase \R\N to the lower case version. It was corrupting the display when I used the correct lower case format which was not very helpful as an example format file!:angry:
7.0
6
1SQLCHAR0255";"1COL1
2SQLCHAR0255";"2COL2
3SQLCHAR0255";"3COL3
4SQLCHAR0255";"4COL4
5SQLCHAR0255";"5COL5
6SQLCHAR0255"\R\N"6COL6
Here I have called the format file grasshopper.fmt and located it on the C drive
This example creates an oversize table with 9 columns but OPENROWSET BULK takes care of it in the INSERT statement.
Of course, your table may only have 6 columns but I give this example for illustration purposes.
I can then remove the pipe character and those pesky carriage return line feeds by means of the REPLACE function.
IF NOT OBJECT_ID('tempdb.dbo.#ConnectivityTest','U') IS NULL
DROP TABLE #ConnectivityTest
CREATE TABLE #ConnectivityTest
(
COL1 VARCHAR(255),
COL2 VARCHAR(255),
COL3 VARCHAR(255),
COL4 VARCHAR(255),
COL5 VARCHAR(255),
COL6 VARCHAR(255),
COL7 VARCHAR(255),
COL8 VARCHAR(255),
COL9 VARCHAR(255)
)
INSERT #ConnectivityTest
(COL1,COL2,COL3,COL4,COL5,COL6)
SELECT REPLACE(REPLACE(REPLACE(COL1,'|',''),CHAR(13),''),CHAR(10),''),COL2,COL3,COL4,COL5,COL6
FROM OPENROWSET (BULK 'C:\ConnTest.txt',
FORMATFILE = 'C:\grasshopper.fmt') AS Z
SELECT COL1,COL2,COL3,COL4,COL5,COL6
FROM #ConnectivityTest
March 15, 2010 at 7:14 am
steve-893342 (3/15/2010)
Jeff makes the point about BULK INSERT being quite restrictive because you have to match up the column list and you cannot manipulate the individual columns very easily. OPENROWSET BULK gives you much more flexibility because you can you use it in the same way as a conventional INSERTstatement and you can match up the column list very simply. However it does require a format file.
Nope... my point is that even that won't solve the problem if the first row doesn't have the same number of delimiters even if you tell it to "skip" the first row.
--Jeff Moden
March 15, 2010 at 7:52 am
As Jeff said, I am not able to get rid of it.. Actually, what happens is, I have a batch file which when executed connected to each server using OSQL utility, takes a SQL script as input file, executes in the server and puts the result of the servers in a notepad.
I am trying to import the contents of the notepad to a table. Sometimes, some of the servers may not connect because of some issues which will write the errors to the notepad.. This is where the problem is.. While importing the contents of the notepad to the table, if it encouters a row without delimiters, it skips not only that line but also the next line. Can we stop OSQL from writing errors to the notepad? I mean can we suppress the errors.
Thanks for all your help and more help will be much appreciated.. 😀
March 15, 2010 at 10:57 am
Sorry folks, I misunderstood where the text file actually started!
Maybe this one will work for you
You will need to create a format file (grasshopper2.fmt) as follows
7.0
1
1SQLCHAR08000"\R\N"1BulkColumn
Again don't forget to replace the \R\N with the lower case equivalent
Hopefully the script below will give you more or less what you are looking for
IF NOT OBJECT_ID('tempdb.dbo.#ConnectivityTest','U') IS NULL
DROP TABLE #ConnectivityTest
CREATE TABLE #ConnectivityTest
(
BulkColumn VARCHAR(8000)
)
INSERT #ConnectivityTest
SELECT BulkColumn
FROM OPENROWSET (
BULK 'C:\ConnTest.txt',
FORMATFILE = 'C:\grasshopper2.fmt',
FIRSTROW = 1 --If you set FIRSTROW to 3 you get rid of the gumph at the top
) AS Z
WHERE NOT BulkColumn = '|'
;
WITH cteTally
AS
(
SELECT TOP 8000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pk
FROM master.sys.All_Columns
CROSS JOIN master.sys.All_Columns AS T2
)
SELECT * FROM #ConnectivityTest
CROSS APPLY
(
SELECT
[1] AS COL1,[2] AS COL2,[3] AS COL3,[4] AS COL4,[5] AS COL5,[6] AS COL6
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(BulkColumn+';', pk, CHARINDEX(';', BulkColumn+';', pk) - pk), '') AS Value
FROM cteTally
WHERE pk < LEN(BulkColumn) + 2 AND SUBSTRING(';' + BulkColumn + ';', pk, 1)=';'
) AS Z
PIVOT
(
MAX(Value) FOR ROW IN
(
[1],[2],[3],[4],[5],[6]
)
)
AS pvt
)
AS Y
March 15, 2010 at 11:19 am
Thanks for the reply Steve... The output file format vaires everytime. Please let me know if your code works for the below scenario that I face.. I have a batch file with the below contents
osql /E /SServerA /iF:\Conn\conn_test.sql /oF:\Conn\serverA.txt -n -w65535 -h-1
osql /E /SServerB /iF:\Conn\conn_test.sql /oF:\Conn\serverB.txt -n -w65535 -h-1
osql /E /SServerC /iF:\Conn\conn_test.sql /oF:\Conn\serverC.txt -n -w65535 -h-1
/* Concatenating the contents of all the files to one file*/
type F:\Conn\serverA.txt >> F:\Conn.txt
type F:\Conn\serverB.txt >> F:\Conn.txt
type F:\Conn\serverC.txt >> F:\Conn.txt
Say, I m facing connectivity issues with Server A. When I execute the above batch file, the output file will contain the below contents.
/*Contents of the output file conn.txt*/
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent
So, when I use the below Bulk Insert statement,
BULK
INSERT #connectivity
FROM 'F:\Conn.txt'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ';',
KEEPNULLS,
ROWTERMINATOR = '|'
)
GO
the following row, gets imported to the table #connectivity
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent .
( Skips the row "XXXXXXXXX;Running;Running;XXXX;All online;Not Sent" for some reason, which I wonder why??)
Next day, if I face issues with the server B, then the output file (conn.txt) will be like
/*Contents of the output file conn.txt*/
ZZZZZZZZ;Running;Running;ZZZZ;All online;Not Sent
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent
This time, when I used bulk insert, the row "ZZZZZZZZ;Running;Running;ZZZZ;All online;Not Sent" will get imported to the table but the row "YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent" will get skipped out...
any ideas on how to fix this use? :w00t:
By Any chance is it possible to suppress OSQL errors?
Thanks for your help 😀
March 15, 2010 at 11:51 am
Yes it should always work up to the first INSERT query viz
INSERT #ConnectivityTest
SELECT BulkColumn
FROM OPENROWSET (
BULK 'C:\ConnTest.txt',
FORMATFILE = 'C:\grasshopper2.fmt',
FIRSTROW = 1
) AS Z
--WHERE NOT BulkColumn = '|'
because all this is doing is inserting the entire text file into a raw table with one column.
The other tally table/cross apply/split/pivot stuff was because I thought you wanted to split out the text separated by the ; into separate columns. If you don't need to do that, then the above query is all you need
March 15, 2010 at 2:16 pm
steve-893342 (3/15/2010)
Yes it should always work up to the first INSERT query vizINSERT #ConnectivityTest
SELECT BulkColumn
FROM OPENROWSET (
BULK 'C:\ConnTest.txt',
FORMATFILE = 'C:\grasshopper2.fmt',
FIRSTROW = 1
) AS Z
--WHERE NOT BulkColumn = '|'
because all this is doing is inserting the entire text file into a raw table with one column.
Actually, you don't need a format file then... all you need to do is identify the end of line terminator as '|' and the column separator (as nothing).
--Jeff Moden
March 15, 2010 at 2:20 pm
karthik -450760 (3/15/2010)
Thanks for the reply Steve... The output file format vaires everytime. Please let me know if your code works for the below scenario that I face.. I have a batch file with the below contentsosql /E /SServerA /iF:\Conn\conn_test.sql /oF:\Conn\serverA.txt -n -w65535 -h-1
osql /E /SServerB /iF:\Conn\conn_test.sql /oF:\Conn\serverB.txt -n -w65535 -h-1
osql /E /SServerC /iF:\Conn\conn_test.sql /oF:\Conn\serverC.txt -n -w65535 -h-1
/* Concatenating the contents of all the files to one file*/
type F:\Conn\serverA.txt >> F:\Conn.txt
type F:\Conn\serverB.txt >> F:\Conn.txt
type F:\Conn\serverC.txt >> F:\Conn.txt
Say, I m facing connectivity issues with Server A. When I execute the above batch file, the output file will contain the below contents.
/*Contents of the output file conn.txt*/
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
XXXXXXXXX;Running;Running;XXXX;All online;Not Sent
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent
So, when I use the below Bulk Insert statement,
BULK
INSERT #connectivity
FROM 'F:\Conn.txt'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ';',
KEEPNULLS,
ROWTERMINATOR = '|'
)
GO
the following row, gets imported to the table #connectivity
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent .
( Skips the row "XXXXXXXXX;Running;Running;XXXX;All online;Not Sent" for some reason, which I wonder why??)
Next day, if I face issues with the server B, then the output file (conn.txt) will be like
/*Contents of the output file conn.txt*/
ZZZZZZZZ;Running;Running;ZZZZ;All online;Not Sent
DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied.
Net-Library error 2: ConnectionOpen (Connect()).
|
YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent
This time, when I used bulk insert, the row "ZZZZZZZZ;Running;Running;ZZZZ;All online;Not Sent" will get imported to the table but the row "YYYYYYYYYY;Running;Running;YYYYY;All Online;Not Sent" will get skipped out...
any ideas on how to fix this use? :w00t:
By Any chance is it possible to suppress OSQL errors?
Thanks for your help 😀
At this point, it would be really helpful if you'd attach one of these files as a txt file to your post. Of course, you should do a quick little review to ensure it contains no logins or passwords.
Also, about 4-5 posts back, I asked you to post the CREATE TABLE statement for the target of the BULK INSERT... would you please do that now?
--Jeff Moden
March 15, 2010 at 2:44 pm
Yes that's right Jeff, you wouldn't need a format file for BULK INSERT. It really depends how much flexibility you need. OPENROWSET BULK allows you to control the SELECT statement in exactly the same way as if you were selecting from a real table. And OPENROWSET BULK demands a format file when used in this context.
March 15, 2010 at 4:53 pm
At this point, it would be really helpful if you'd attach one of these files as a txt file to your post. Of course, you should do a quick little review to ensure it contains no logins or passwords.
Also, about 4-5 posts back, I asked you to post the CREATE TABLE statement for the target of the BULK INSERT... would you please do that now?
Thanks Jeff. I have attached the text file. Below is my create table stmt and Bulk Insert Statement.
create table #connectivity
(
ServerName varchar(30),
SQLServerStatus varchar(20),
SQLServerAgentStatus varchar(20),
RunTime varchar(200),
DatabaseStatus varchar(500),
MailStatus varchar(500)
)
BULK
INSERT #connectivity
FROM 'F:\Conn\Conn.txt'
WITH
(
FIRSTROW=1,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '|'
)
GO
March 15, 2010 at 7:15 pm
steve-893342 (3/15/2010)
Yes that's right Jeff, you wouldn't need a format file for BULK INSERT. It really depends how much flexibility you need. OPENROWSET BULK allows you to control the SELECT statement in exactly the same way as if you were selecting from a real table. And OPENROWSET BULK demands a format file when used in this context.
Agreed but you're still missing the point on this one, Steve. Neither the format file nor Bulk Insert nor OpenRowSet Bulk will work correctly if the lines in the file don't all have the same number of delimiters.
--Jeff Moden
March 15, 2010 at 7:18 pm
karthik -450760 (3/15/2010)
Thanks Jeff. I have attached the text file. Below is my create table stmt and Bulk Insert Statement.
Thanks Karthik... I have to ask though... is that an actual file or one that you made up? I ask because it's missing the terminating pipe on the last row and I haven't seen an actual file with a bunch of X's and Y's in it for this type of status file. I realize those are probably obfuscations of server names and the like but I need to know certain things... like does the last line have a trailing pipe character in it and do you really have blank lines and is each physical line actually terminated with a CrLf, just a Cr, just and Lf, or nothing at all in the real file before you edited it to obfuscate the server names, etc?
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply