Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table?
Thanks
Steffan
Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table?
> Thanks
> Steffan
>
2012年3月25日星期日
BCP Temporary Tables
Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>
BCP Temporary Tables
Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>
BCP syntax
is there anything wrong with my syntax ?
bcp AGENCY out E:\Spreadsheets\Agency.txt -c -Svicbranch1 -Usqlsvc -Pabc123
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.I can't see anything obviously wrong.
I always specify database.owner.table in the first parameter to bcp, though.|||BCP is run from a command line, rather than from Query Analyzer.|||I would suggest fully qualifing the table eg. dbname.dbo.tablename
Or the problem may be that the table you are trying to access is in a different database to the default database of the username you are using..
Just a thoughysql
bcp AGENCY out E:\Spreadsheets\Agency.txt -c -Svicbranch1 -Usqlsvc -Pabc123
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.I can't see anything obviously wrong.
I always specify database.owner.table in the first parameter to bcp, though.|||BCP is run from a command line, rather than from Query Analyzer.|||I would suggest fully qualifing the table eg. dbname.dbo.tablename
Or the problem may be that the table you are trying to access is in a different database to the default database of the username you are using..
Just a thoughysql
2012年3月19日星期一
bcp Limitation
Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
MadhivananYou can use a view and then bcp from it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982145.425540.79910@.o13g2000cwo.googlegroups.com...
Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
Madhivanan|||Can you post the exact command you are excuting?. BCP expects double
quotation marks around the query and single quotation marks around anything
embedded in the query. For some reason, sql server is interpreting the query
as an identifier and the max length for identifiers is 128. Check database
option "quoted identifier" using sp_dboption or use SET QUOTED_IDENTIFIER in
the batch.
AMB
"Madhivanan" wrote:
> Is there any limitation on the length of the query used? When the query
> exceeds 128 characters, I get the error
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'Select Id,Name,Descrip, Address,
> others,
> case when len((Select distinct Street from mydb..testw2 where w2' is
> too long. Maximum length is 128.
>
> Madhivanan
>
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
MadhivananYou can use a view and then bcp from it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1115982145.425540.79910@.o13g2000cwo.googlegroups.com...
Is there any limitation on the length of the query used? When the query
exceeds 128 characters, I get the error
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'Select Id,Name,Descrip, Address,
others,
case when len((Select distinct Street from mydb..testw2 where w2' is
too long. Maximum length is 128.
Madhivanan|||Can you post the exact command you are excuting?. BCP expects double
quotation marks around the query and single quotation marks around anything
embedded in the query. For some reason, sql server is interpreting the query
as an identifier and the max length for identifiers is 128. Check database
option "quoted identifier" using sp_dboption or use SET QUOTED_IDENTIFIER in
the batch.
AMB
"Madhivanan" wrote:
> Is there any limitation on the length of the query used? When the query
> exceeds 128 characters, I get the error
> Server: Msg 103, Level 15, State 7, Line 1
> The identifier that starts with 'Select Id,Name,Descrip, Address,
> others,
> case when len((Select distinct Street from mydb..testw2 where w2' is
> too long. Maximum length is 128.
>
> Madhivanan
>
2012年2月25日星期六
bcp command
Hi all,
I am trying to run a .bat file with this bcp command.
BCP "database.dbo.state" OUT "C:\TEMP\state.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q
However, it is not producing me a file as I expected.
Is there any other configuration I need to set before it work?
Any help would appreciated.What error messages are being produced? I don't think I've seen BCP fail silently in years. Have you ever done a successful BCP against that SQL Server from the same client maching?
-PatP|||The error is: "Error in attempting to load a pair of translation tables."|||The error is: "Error in attempting to load a pair of translation tables."
Excuse me?
Is it not creating a file, or not creating a file as "expected'
Because if it's the latter and you expect to "see" data, you won't. It's in native format.
You need to lose the -n and use -c|||The error is: "Error in attempting to load a pair of translation tables."That is definitely not an error message generated by BCP. What other programs are you running that might have generated that message?
-PatP
I am trying to run a .bat file with this bcp command.
BCP "database.dbo.state" OUT "C:\TEMP\state.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q
However, it is not producing me a file as I expected.
Is there any other configuration I need to set before it work?
Any help would appreciated.What error messages are being produced? I don't think I've seen BCP fail silently in years. Have you ever done a successful BCP against that SQL Server from the same client maching?
-PatP|||The error is: "Error in attempting to load a pair of translation tables."|||The error is: "Error in attempting to load a pair of translation tables."
Excuse me?
Is it not creating a file, or not creating a file as "expected'
Because if it's the latter and you expect to "see" data, you won't. It's in native format.
You need to lose the -n and use -c|||The error is: "Error in attempting to load a pair of translation tables."That is definitely not an error message generated by BCP. What other programs are you running that might have generated that message?
-PatP
订阅：
博文 (Atom)