Problem with BCP command

I am trying to execute following command using xp_cmdshell

EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'

but the error message returned is

Copy direction must be either 'in' or 'out'.

Syntax Error in 'queryout'.

usage: bcp [[database_name.]owner.]table_name[lice_number] {in | out} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n] [-c] [-t field_terminator] [-r row_terminator]

[-U username] [-P password] [-I interfaces_file] [-S server]

[-a display_charset] [-q datafile_charset] [-z language] [-v]

[-A packet size] [-J client character set]

[-T text or image size] [-E] [-g id_start_value] [-N] [-X]

[-M LabelName LabelValue] [-labeled]

[-K keytab_file] [-R remote_server_principal]

[-V [security_options]] [-Z security_mechanism] [-Q]

NULL

Further the same command is running successfully in my DEV environment.

That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.

|||Wrong forum. Moving to Transact-SQL.|||

When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.

The Server name does not need to be in double quotes.

|||

Hi Tom,

I have checked the version on all the three plateform

DEV : 8.00.382

PROD : 8.00.382

TestPROD : 8.00.382

and version are same.

|||

Hi Arnie,

The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.

|||On the production server, does the SQL Agent account have permissions for the file locations?|||

Not Sure how to check this,

BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service

Windows Service

Batch File

Stored Procedure

BCP Command.

|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.

Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.

|||

Hi Tom,

Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.

Thanks again for your help.

|||Good. I am glad you found it.

sql

Problem with attaching database

I try to attach a db by running the following command:

EXEC sp_attach_single_file_db @.dbname = 'test',

@.physname = 'C:\Documents and Settings\renee.wilson\My Documents\PD DD 2.nvp'

But I get the following error message:

File activation failure. The physical file name "C:\Documents and Settings\renee.wilson\My Documents\PD DD 2_log.LDF" may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

It looks like that the db was not detached from the server properly. I know that .LDF file does not exist, therefore, I was hoping by running the sp_attach_single_file_db, a new .LDF file can be created.

Any body has any idea on how to fix the problem? Thanks in advance.

Hi,

one last chance left, here are some instructions for the crashd database:

http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/f584b644548cd68f

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, Jens.

But, I tried both suggestions in

http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/f584b644548cd68f

without much luck.

I am having the following problems:

1. DBCC rebuilt_log does not exist in SQL Server 2005 Express which I am using.

2. When the database is in emergency mode, which I can see via the tool(Microsoft SQL Server Management Studio Express) I am using, I can not view any of its object, for example, I click on Tables(try to see the tables), I get error msg: Could not continue scan with NOLOCK due to data movement, (Microsoft SQL Server, Error:601). For the same reason, I can not generate scripts as suggested.

Any further suggestion? Thanks in advance.

problem with attach database

EXEC sp_attach_single_file_db @.dbname = 'mydatabase',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\'mydatabase'.mdf'

Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 0 in database 'mydatabase'.

Connection Broken

What can I do with such error of my database?
Was my database lost?
Any help would be appreciate!
Best regards,
AdamHi

Does the filename really have quotes in it? ( 'mydatabase'.mdf )

John

"Adam Polech" <polada1@.poczta.onet.pl> wrote in message
news:cd8lbt$fc9$1@.213.17.164.114...
> EXEC sp_attach_single_file_db @.dbname = 'mydatabase',
> @.physname = 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\'mydatabase'.mdf'
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 0 in database 'mydatabase'.
> Connection Broken
> What can I do with such error of my database?
> Was my database lost?
> Any help would be appreciate!
> Best regards,
> Adam

Problem while using xp_sendmail

Hi,
When I use xp_sendmail on my local machine, using parameters like
Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
some message', @.subject = ' some subject ', then it works fine and I get
the mail in my mailbox ...
But when I use the same stuff on any other SQL server (on same network), I
get the error --
xp_sendmail: Procedure expects parameter @.user, which was not supplied.
I've searched in BOL, there is no parameter called @.user for xp_sendmail.
What and where is the problem ?
regards
KPXP_Sendmail has a parameter for set_user, perhaps thewrong message is being
sent ( but not likely)
Check to make sure no one has placed an xp_sendmail in your local
database...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> Hi,
> When I use xp_sendmail on my local machine, using parameters like
> Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> some message', @.subject = ' some subject ', then it works fine and I
> get
> the mail in my mailbox ...
> But when I use the same stuff on any other SQL server (on same network), I
> get the error --
> xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> What and where is the problem ?
> regards
> KP|||So there is a possibility of having 'different' version of xp_sendmail on
other machine (where it does not work). How can I replace a DLL file? Will
simple overwriting help? Pls advice.
"Wayne Snyder" wrote:
> XP_Sendmail has a parameter for set_user, perhaps thewrong message is being
> sent ( but not likely)
> Check to make sure no one has placed an xp_sendmail in your local
> database...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:1616F7CF-3D68-4FAB-A5BD-5B1979494336@.microsoft.com...
> > Hi,
> >
> > When I use xp_sendmail on my local machine, using parameters like
> > Exec master.dbo.xp_sendmail @.recipients = 'my email id' , @.message = '
> > some message', @.subject = ' some subject ', then it works fine and I
> > get
> > the mail in my mailbox ...
> >
> > But when I use the same stuff on any other SQL server (on same network), I
> > get the error --
> > xp_sendmail: Procedure expects parameter @.user, which was not supplied.
> >
> > I've searched in BOL, there is no parameter called @.user for xp_sendmail.
> >
> > What and where is the problem ?
> >
> > regards
> > KP
>
>

Problem while fetching data from Oracle linked server

Hi,

I created a linked server as follows:

EXEC sp_addlinkedserver 'OracleLinkedServer', 'Oracle', 'MSDAORA', 'fcstage'

EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', false, 'SA', 'fc_stage', 'password'

Now I try firing a simple select statement

SELECT FINANCIAL_TRX_INFO_ID FROM

[OracleLinkedServer]..[FC_STAGE].[WFS_FINANCIAL_TRX_INFO]

WHERE SFS_BUSINESS_SEGMENT IS NOT NULL

But I get the following error:

OLE DB provider "MSDAORA" for linked server "OracleLinkedServer" returned message "ORA-01426: numeric overflow

".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OracleLinkedServer".

This seems to be a generic error statement. Can anyone tell me where am I going wrong.

Thanks.

Solved using OpenQuery

SELECT * FROM OPENQUERY([OracleLinkedServer], 'SELECT FINANCIAL_TRX_INFO_ID FROM WFS_FINANCIAL_TRX_INFO') AS Query

problem while creating a linked server from SQL-Server2000 to an Access97 database

I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts

EXEC sp_addlinkedserver

@.server='REMOTE_OFFICE',

@.srvproduct='Jet 4.0',

@.provider='Microsoft.Jet.OLEDB.4.0',

@.datasrc='F:\RealEstate_Office1.mdb'

and

EXEC sp_addlinkedsrvlogin

@.rmtsrvname='REMOTE_OFFICE',

@.useself='false',

@.locallogin='sa',

@.rmtuser='Admin',

@.rmtpassword=NULL

And while querying the linked server from the query analyzer using the following select command

SELECT *

FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK

I got the following error

Server: Msg 7312, Level 16, State 1, Line 1

Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

OLE DB error trace [Non-interface error].

could you explain why this happen

The error indicates that Access does not support database or schema names. Try leaving those parts empty. SELECT * FROM REMOTE_OFFICE...E_GOV_RE_OK.

This post belongs in another forum.
|||Moving to the SQL Server Data Access Forum.|||

Thanks JayH,

after a little search I found that it was an article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database and its link is as follows

http://support.microsoft.com/?kbid=246255

Article ID : 246255
Last Review : February 12, 2007
Revision : 4.3

Thanks all.

Problem using EXEC() to run DBCC DBREINDEX

I am trying to run DBCC DBREINDEX using EXEC(), code is below.
Based upon the error message at the bottom, the @.currenttable variable
receives the value 1 but when @.currenttable is referenece in the DBCC
statement, the value isn't there. Can anyone tell me what I'm doing wrong?
declare @.sqltest varchar(40), @.currenttable int
set @.currenttable = (select table_id from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
Below is the message I get:
1
DBCC DBREINDEX('@.currenttable','',75)
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named '@.currenttable'. Check sysobjects.nosurfdj,
DBCC DBREINDEX expects a table name and there is not table named
'@.currenttable'.
declare @.sqltest varchar(40), @.currenttable int
declare @.tn sysname
set @.tn = (select table_name from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
go
AMB
"nosurfdj" wrote:

> I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||Quote problems around ''@.currenttable''.
Try:
'DBCC DBREINDEX(' + @.currenttable + ','''',75)'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"nosurfdj" <nosurfdj@.discussions.microsoft.com> wrote in message news:2406FBD3-FD2A-4F69-8A
8E-F446DC1473BF@.microsoft.com...
>I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
>
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||I knew it was going to be something simple.
Thanks for your help-that did it.
"Alejandro Mesa" wrote:
> nosurfdj,
> DBCC DBREINDEX expects a table name and there is not table named
> '@.currenttable'.
> declare @.sqltest varchar(40), @.currenttable int
> declare @.tn sysname
> set @.tn = (select table_name from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> go
>
> AMB
> "nosurfdj" wrote:
>