BCP Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam Tjai
Try brackets around the table name?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
|||You can also use the quotename() function
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
Ekrem nsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name?
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

BCP Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam TjaiTry brackets around the table name'
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name'
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net|||You can also use the quotename() function
--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
--
Ekrem Önsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name'
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

BCP issue

Dear gurus,
I can't work out with a problem using our dearest statement, i.e BCP.
Well, I'm trying to import a .DAT file to a table from a DOS session and I
obtain the following error:
C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
-Uusrdts
-Pdts
Where the hell the problem is?
Thanks for any input or advice and regards,On the strenght and bearing on mind that the name of the database is ABS. An
d
ABS is a reserver word in SQL!!!
"Enric" wrote:

> Dear gurus,
> I can't work out with a problem using our dearest statement, i.e BCP.
> Well, I'm trying to import a .DAT file to a table from a DOS session and I
> obtain the following error:
> C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
> -Uusrdts
> -Pdts
> Where the hell the problem is?
> Thanks for any input or advice and regards,|||"the name of the database is ABS."
Then you are also missing the owner or just leave it away:
[ABS]..abs_diariopeticiones
HTH, jens Suessmeyer.|||cheers
"Jens" wrote:

> "the name of the database is ABS."
> Then you are also missing the owner or just leave it away:
> [ABS]..abs_diariopeticiones
>
> HTH, jens Suessmeyer.
>

bcp issue

Dear all,
I've got a little issue and I can't work out with it. Using BCP in order to
export the contains of a .dat file into a table:
C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver -U
us1 -Pdts1
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Se encontró un EOF inesperado en
un archivo de datos BCP
((suddenly error in bcp file))
How do I find out where the eof mark are?
Does anyone ever used or suffered this error?
Regards,Hi,
EOF means End Of File - there's no particular code. Check if data format in
the file is correct
Peter|||Thanks Rogas69. I knew it. Only was I wondering how to solve it of an
automatically way or something like that. Bearing on mind I've got 200 files
to load...
Anyway
"Rogas69" wrote:

> Hi,
> EOF means End Of File - there's no particular code. Check if data format i
n
> the file is correct
> Peter
>
>|||Enric (Enric@.discussions.microsoft.com) writes:
> I've got a little issue and I can't work out with it. Using BCP in order
> to export the contains of a .dat file into a table:
> C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver
> -U us1 -Pdts1
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Se encontr un EOF inesperado
> en un archivo de datos BCP
> ((suddenly error in bcp file))
> How do I find out where the eof mark are?
> Does anyone ever used or suffered this error?
Better to ask if there is anyone who have used BCP and never got any error.
It would at least be easier to count the hands.
These extrmely common error means that the data file does not match the
format specification. BCP finds that the file ends in the middle of a
record.
Since you are using native format, this means that the table definition
does not match the table definition. I have no idea what you expected.
Are the files really in native format? Native format means that data appear
in the field as they appear in SQL Server, that is binary.
Since you mentioned that you had 200 files, I would more expect a text
format...
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|||XXXX..life, yep. I've came to the conclusion that the problem is that these
files were extracted from a 'unknown table' and now they want to load again
into another one...
"Erland Sommarskog" wrote:

> Enric (Enric@.discussions.microsoft.com) writes:
> Better to ask if there is anyone who have used BCP and never got any error
.
> It would at least be easier to count the hands.
> These extrmely common error means that the data file does not match the
> format specification. BCP finds that the file ends in the middle of a
> record.
> Since you are using native format, this means that the table definition
> does not match the table definition. I have no idea what you expected.
> Are the files really in native format? Native format means that data appea
r
> in the field as they appear in SQL Server, that is binary.
> Since you mentioned that you had 200 files, I would more expect a text
> format...
>
> --
> 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
>

BCP Command - Login fails

Dear all,

I have a curious problem using BCP to import and export data. First of all I export data as XML file with the command:

bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T

Now this works fine. The table data is exported as data.xml. I use the exported data in an external tool and then the result of that is a csv file. This file needs to be imported again in the SQL database, so I can run some queries. I use the following commandline;

bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

When I use this command I get the following error;

SQLState = 37000, NativeError = 4060

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.

I tested the following:

1) Run the same command on local PC (SQL Installed) and then no problem at all. So the command seems to be correct.

2) Then I tried to use the –U –P parameters instead of –T. Then the error message changes and states login failed…

3) Changed the command to bcp "[606]..ORTECPlanning" IN "c:\ortec\misc\planning.csv" -c -F1 -t; -S"MMOSSURPBM09" –T

4) Changed the command to bcp [MMOSSURPBM09].[606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

5) Remove –t; (suggestion) but this is causing the same error on the server and causing a new error unexpected EOF on the local machine

But no luck… I’m using the administrator to login and perform the command. Exactly the same user and situation as in the queryout command…. So then the question is what is wrong with the command;

bcp [606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T

Now this works fine

I notice that the commands that fail seem to be hitting a different server ([606]) -NOT [006].

bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T

When I use this command I get the following error;

SQLState = 37000, NativeError = 4060

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.

Did I mis-read that?

batch file to run a query

Dear All,
i need to put a query in a batch file to be easy to use by the user.
jusy only double click on it and it will work.
can any one help?Checkout OSQL.EXE in books on line, that should give you what you need.|||'ISQL' as well as 'OSQL' will do. ISQL is DB-Library applications & OSQL is ODBC oreinted...|||OSQL is OLEDB oriented.|||OLEDB is a successor to ODBC... Such online command tools should be downward compatible... thats why its said ODBC oriented...

http://db.ittoolbox.com/documents/document.asp?i=2412

If you check Sql Server Books Online for OSQL u could see the below infrm...

"The osql utility uses the ODBC database application programming interface (API). It is a replacement for the isql command prompt utility based on the DB-Library API. "|||But how do you assign the result of query to a DOS variable?

for example, how do you assign the result of this query (select top 1 name from employee where empname = 'John') to a DOS variable in a batch program? I read that the use of EXIT with osql returns the result but when I tried to use it, it gave some errors. Does anyone has an exmaple?|||I think that only works for integers;
"You can use the result of a SELECT statement as the return value from isql. The first column of the first result row is converted to a 4-byte integer (long). MS-DOS passes the low byte to the parent process or operating-system error level. "

If it's processing strings you want, I guess you'll need to use an output file and read the file from the batchfile (and delete it afterwards).|||How do you do the dos variable assignment if the select is changed to (select count(*) from employee where empname = 'John'). In this case the result will be integer.

Say, I want to check if an employee already exists in a table.

set /p emp='osql -S DBServ -U sa -P passwd -d master -Q "EXIT(select count(*) from employee where empname = 'John')"'
if "%emp%"=="1" goto :EmpFound

It gives me an error - "count(*) was unexpected at this time".|||Depending on your tolerance for pain, you can get assign SQL result set values to environment variables, but there are better answers. Look at Perl (http://www.perl.org/) or KIX (http://www.kixtart.org/) for examples.

-PatP|||I am just trying to modify some pre-existing dos batch scripts and can't use perl.
I am surprised that a simple variable assignment in dos batch script (from osql single value result) is such a complicated thing!|||This will cause somebody to whirl in their grave, but consider this:echo servername is %sn%
osql -E -S. -Q "DECLARE @.c VARCHAR(1000) SELECT @.c = 'SET sn=' + @.@.servername PRINT @.c" >c:\temp\sqlvoodo.bat
call c:\temp\sqlvoodoo.bat
echo servername is %sn%-PatP

Baster fix on win2000

Dear,
I install the Baster fix on win2000 server laster saturday,
After the fix install, the SQL agents can't start use NT
domain account. It can start only local system account. I
try to un-install the Baster fix but still not work. How
can i resume the agents that run on domain account?
Thanks
JohnYES, Please Help
John
>--Original Message--
>are you talking about MS03-026?
>"John" <acos3ltd@.yahoo.com.hk> wrote in message
>news:07d801c36af3$947d1b50$a301280a@.phx.gbl...
>> Dear,
>> I install the Baster fix on win2000 server laster
saturday,
>> After the fix install, the SQL agents can't start use NT
>> domain account. It can start only local system account.
I
>> try to un-install the Baster fix but still not work. How
>> can i resume the agents that run on domain account?
>> Thanks
>> John
>
>.
>