Hi everyone one,
I'm very new to SQLExpress and I'm having difficulty with a procedure I
found on the net
to import multiple files into a table:
http://www.databasejournal.com/feat...cle.php/3325701
The method I'm using is method one.
I've done everything as outlined, however when running the procedure within
SQLexpress I receive the error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
I'm presuming it has something to do with the Fieldterminator remark, the
double quote and comma specified in the bulk insert statement:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
I've tried running the bulk insert with single quotes and it runs fine..with
double quotes not so good I get the same error so thats why I think its a
syntax error...but when I replace single quotes in the procedure, the
procedure doesn't compile...i.e.
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
....I'm at a loss as to how to fix it!
Could someone help please, thanks so much.Try using single quotes instead of double quotes to delimit literals so that
you aren't sensitive to your QUOTED_IDENTIFIER setting. When you need a
quote within a literal, specify 2 single quotes:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+ @.Filepath+@.Filename+'''
WITH
( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
> Hi everyone one,
> I'm very new to SQLExpress and I'm having difficulty with a procedure I
> found on the net
> to import multiple files into a table:
> http://www.databasejournal.com/feat...cle.php/3325701
> The method I'm using is method one.
> I've done everything as outlined, however when running the procedure
> within SQLexpress I receive the error message
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near ','.
> I'm presuming it has something to do with the Fieldterminator remark, the
> double quote and comma specified in the bulk insert statement:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
> I've tried running the bulk insert with single quotes and it runs
> fine..with double quotes not so good I get the same error so thats why I
> think its a syntax error...but when I replace single quotes in the
> procedure, the procedure doesn't compile...i.e.
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
> ....I'm at a loss as to how to fix it!
> Could someone help please, thanks so much.
>
>|||Thanks Dan, that worked, now on to my "real" data...
the fields are separated by a comma but enclosed in a double quote i.e.
"12345","67899"
How do I get the field terminator set correctly? I just know you're going
to
tell me to remove the double quotes...(sigh)...theres a ton of files...
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
> Try using single quotes instead of double quotes to delimit literals so
> that you aren't sensitive to your QUOTED_IDENTIFIER setting. When you
> need a quote within a literal, specify 2 single quotes:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
>|||You need to create a format file when you have a more complicated file
format. Sample SQL 2000 format file:
8.0
3
1 SQLCHAR 0 1 "\"" 0 quote ""
2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
Specify the format file using FORMATFILE in your BULK INSERT statement
instead of FIELDTERMINATOR and ROWTERMINATOR:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
@.Filepath+@.Filename+'''
WITH
( FORMATFILE = ''' + @.FormatFilename + ''')'
See the Books Online for format file details.
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan, that worked, now on to my "real" data...
> the fields are separated by a comma but enclosed in a double quote i.e.
> "12345","67899"
> How do I get the field terminator set correctly? I just know you're going
> to
> tell me to remove the double quotes...(sigh)...theres a ton of files...
> Thanks again
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dan...this certainly got complicated fast!! I'm guessing this format
file is likened to a specifications file in msaccess.
At any rate, when trying to run the bcp utility:
bcp adventureworks.humanresources.department format nul -T -n -f
importtest-f-n.txt
I receive an error, unable to open a connection...that remote access may not
be enabled? I've checked using the surface configuration tool and remote
looks to be enabled.
Secondly if I get this bcp utililty to work..can it be run against a "txt"
file (haven't been able to get this to work either)..it doesn't make sense
to me to run the utility against the table rec'ving the data if it isn't the
source of the import?...I can't see any other way to create this format file
other than manually?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
> You need to create a format file when you have a more complicated file
> format. Sample SQL 2000 format file:
> 8.0
> 3
> 1 SQLCHAR 0 1 "\"" 0 quote ""
> 2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
> 3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
> Specify the format file using FORMATFILE in your BULK INSERT statement
> instead of FIELDTERMINATOR and ROWTERMINATOR:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FORMATFILE = ''' + @.FormatFilename + ''')'
> See the Books Online for format file details.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
>|||> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
Since you haven't specified a server, BCP will connect to the default
instance on the local machine. Is that your intent?
> it doesn't make sense to me to run the utility against the table rec'ving
> the data if it isn't the source of the import?
A format file describes the format of your text file (source or target).
When you run BCP using the 'format' option, the table is neither a source
nor target; the table schema is used to facilitate creating a format file
with one field per column. Since you have specified '-n', the generated
format file will be appropriate only for native file format rather than the
comma-delimited/quoted field format you specified in your original post.
> I can't see any other way to create this format file other than manually?
Although BCP can't directly create a format file for a
comma-delimited/quoted field format, you can use BCP to create a default
character format file and then change the generated to match your actual
file. That way, you at least won't need to enter all the columns. For
example:
bcp adventureworks.humanresources.department format
nul -T -c -fimporttest-f-n.txt -SMyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:uMsEFo7DGHA.516@.TK2MSFTNGP15.phx.gbl...
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess.
> At any rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled.
> Secondly if I get this bcp utililty to work..can it be run against a "txt"
> file (haven't been able to get this to work either)..it doesn't make sense
> to me to run the utility against the table rec'ving the data if it isn't
> the source of the import?...I can't see any other way to create this
> format file other than manually?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again Dan
I agree it would be easier to run the bcp utility w/correct syntax for the
purposes intended. However it seems...I cannot get it to run:
C:\>bcp adventureworks.humanresources.department format nul -T -n -f
importtest-
f-n.txt -<servername>
Warning: Packetsize size must be between 512 and 65535 -- defaulting to
4096.
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
con
nection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]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.
I have checked everything that a newbie can think of, it looks to me like
remote connections are enabled, connect via tcp/ip or named pipes, I've even
specified a port 3308 to connect on via firewall...
Ok as silly as it sounds, does bcp work with SQLexpress?...|||Hi Dale
You are missing -S<servername> is this a typo?
If you are using the default server then you can miss out specifying
completely
John
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]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.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||In addition to John's response, the instance needs to be restarted after you
enable remote connections. You might try restarting the instance to make
sure the change isn't pending.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
Is this the default or named instance? How did you configure port 3308?
Note that for a named instance, you'll need to enable/start the SQL Browser
service in order to remotely connect by server\instance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]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.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:574887
Dale (dale@.nospam.com) writes:
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess. At any
> rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled
You probably need to add:
-S .\SQLEXPRESS
if you leave out the server name, BCP tries to connect to the default
instance. But by default SQL Express installs as a *named* instance with
the name SQLEXPRESS. (You can have several instances of SQL Server on
your machine, but only one can be the default instance.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
