Error In --&&> EXEC Xp_cmdshell 'bcp SELECT * FROM Tbl A Where A.flag=N Queryout F.txt -c -T'

Aug 7, 2007

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but
like this it saves well "SELECT * FROM tbl where flag=N" and
when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP ---> -c -T , must have some more or less

View 5 Replies


ADVERTISEMENT

I've Tried Exec Master..xp_cmdshell 'bcp DBCC CHECKDB Queryout C:Test.txt'

Mar 7, 2005

Hi,
i Exected...

Exec Master..xp_cmdshell 'bcp "DBCC CHECKDB" queryout "c:Test.txt"'

i've tried through this SQL script in Query Analyser but.
the Result is not storing in the Text file, and getting output this

---------------------------------------
Password:
NULL
Do you want to save this format information in a file? [Y/n]
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column
NULL
BCP copy out failed
NULL
----------------------------------------
Please help me, Successful Query.
Thanks <b>Nosepicker</b>
u r solutions is right...
Which u gave the Query...
Exec Master..xp_cmdshell 'osql -Uuserid -Ppassword -Sservername -ddatabase -Q"DBCC CHECKDB" > "c:Test.txt"'

i've Executed, It's work's fine in the VB6,VB.Net,ASP.Net...
I've done project, i've facility to take Backup and Restore the Database...

View 8 Replies View Related

Using XP_CmdShell To Execute BCP Utility With QUERYOUT. How To Control The OWNER Of The File Created?

Jan 29, 2008

I need the file created by my BCP QUERYOUT command I'm executing from XP_CmdShell in Transact-SQL to have a specific OWNER. I've tried using the sp_xp_cmdshell_proxy_account to setup the correct owner. The owner is always SERVERNAMEAdministrators.

thanks,

jim




View 1 Replies View Related

EXEC XP_CMDSHELL 'DIR \SERVERNAMEFOLDER'

Mar 13, 2008

Hi! I created a stored procedure which uses the xp_cmdshell.
EXEC XP_CMDSHELL 'DIR \SERVERNAMEFOLDER'

If I use it on a local computer it works fine but if I try to access computer on a network, it returns "Access Denied".

I have read a lot of articles which says check the permissions. Can anyone tell me what are those permissions and how do I check them?

Thanks! I would appreciate any help on this...

View 2 Replies View Related

Exec Xp_cmdshell Problem

Feb 19, 2008



Hi,

Inside a stored procedure I'm executing BCP command through

exec xp_cmdshell @str


But when i put this line inside stored procedure, it generates a Output for that.
like how many files copied etc. this output is actually of bcp command.

How can i suppress it's output. ?


My sp is generating two resultsets.



Thanks,
Sandeep

View 3 Replies View Related

SQL SERVER 2000 - EXEC Master..xp_cmdshell Permissions

Jul 20, 2005

Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy

View 2 Replies View Related

Exec Master.dbo.xp_cmdshell N'Xcopy C:@folder1 C:@folder2 /i'

Mar 7, 2008



Hi All,

I am writing a stored procedure which accepts 2 parameters -
Current version(.....for example V53)
UpgradeTo version(.....for example V61)

Using these 2 inputs --I need to create a directory V61 by copying directory V53
for this specific example it would go like this-------------

exec master.dbo.xp_cmdshell N'Xcopy c:V53 c:V61 /i'

Now if i want to make a dynamic query for this ...
how do i go about it?


declare @folder1 varchar(255)

declare @folder2 varchar(255)

declare @xcopytext varchar(255)


SET @folder1 = 'V53'

set @folder2 = 'V61'


set @xcopytext = N'Xcopy "c:@folder1" "c:@folder2" /i'

exec master.dbo.xp_cmdshell @xcopytext

This gives the error :
File Not Foundfolder1

How do I integrate dynamic query with XCopy functionality ?

View 5 Replies View Related

Un-flag Reinitialization Flag On Trans Subscription

Nov 21, 2004

Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.

(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)



The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.

HELP !

View 3 Replies View Related

Can Exec Select But Can't Exec Sp

Oct 31, 2007

I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).

I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.

These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.

Can some one explain why?

View 5 Replies View Related

SQL 2012 :: Trace Flag To Capture Error Messages Like PK Violation

Apr 24, 2015

Is there a dbcc flag that will capture all error messages in the log

e.g. when inserting data into a table, a PK violation occurs throwing, Msg 2627

..similar to trace flag 1222 to capture deadlock info..

View 3 Replies View Related

Exec Pkg Task: Error 0xC0202009 While Preparing To Load The Package. An OLE DB Error Has Occurred. Error Code: 0x%1!8.8X!.

Feb 21, 2007

I cannot execute a package by using Execute Package task.
I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says

Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.



Any clue ?


Thanks,
Fahad

View 1 Replies View Related

SELECT EXEC Statements In SQL

Jun 6, 2008

I  am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
 
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
 FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE  M.EMAIL = @UserEmail
 

View 5 Replies View Related

Exec SP Many Times, From Select?

Aug 5, 2004

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select.... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary T

View 4 Replies View Related

Exec In Select Statement

Jan 2, 2007

hi,

can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..

Select column1, coulumn2, (select EXEC SP_NAme), blabla

thanks in advance... :)

View 6 Replies View Related

Exec(select...), How Supress The Output?

Jul 20, 2005

HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF

View 3 Replies View Related

Exec('select...into #temptable...) Doesn't Work

Mar 11, 2008

Code:

exec('select RIGHT(00000 + CAST(dh.zipcode AS varchar(5)), 5) as zip, '+@fieldname +' as distance into #distance from sumplicity.dbo.t_distancetohospital dh')



This runs normally and returns the number of rows created. Yet when I do a 'select * from #distance' query, I get a message that #distance doesn't exist.

Where is it storing all of this and how do I access it?

View 1 Replies View Related

Need To Pull A Select Few Results From INSERT EXEC

Aug 17, 2006

I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.

I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.

The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.

My question:
Is there a way to INSERT INTO table EXEC dataProc
*Without* making a temp table that takes in every single column the dataProc puts out?

Any help is much appreciated.. thanks!

View 8 Replies View Related

Bcp Errors With Queryout

Apr 19, 2004

I am having problems running bcp with queryout. I believe some of this stems from the fact that I also have the Sybase tools installed on the database servers and it in turn added its binn directory for the Sybase bcp.exe to the environment variables. Seeing this then, I would like to resolve this issue with a solution that does not rely on the order of environment variables.

Here is what is happening:

1) If I just try to run bcp on my server I get...

exec master.dbo.xp_cmdshell 'bcp ?'
/*
output --------------------------------------------------------------
'bcp' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/

2) ...so, I add the full path to the location of the bcp program and this command works:

exec master.dbo.xp_cmdshell '"C:Program FilesMicrosoft SQL Server80ToolsBinncp.exe" ?'
/*
output ------------------------------------------------------------------------------------usage: C:Program FilesMicrosoft SQL Server80ToolsBinncp.exe {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
*/

3) I then attempt a bcp out of a Northwin table... and this works as well

DECLARE @cmdline VARCHAR(4000)
SELECT @cmdline = '"C:Program FilesMicrosoft SQL Server80ToolsBinncp.exe" Northwind.dbo.Categories out C: est.txt -c -T'
EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
/*
output
-----------------
NULL
Starting copy...
NULL
8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 31
NULL
*/

4) I then attempt a queryout and it fails, indicating that it no longer understand the long filename

DECLARE @cmdline VARCHAR(4000)
SELECT @cmdline = '"C:Program FilesMicrosoft SQL Server80ToolsBinncp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C: est.txt -c -T'
EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
/*
output ------------------------------------------------------------------
'C:Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
*/

View 3 Replies View Related

Trouble Using Bcp With Queryout

Oct 22, 2007



I am trying to run the following code:


bcp "SELECT field1, field2, field3, field4*0.001 As field4calc
FROM table

ORDER BY field1, field2, field3"

queryout c:USERSMEDOCUMENTSfolderfile.txt -c -T


and i keep getting the following error:

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near 'queryout'.

I have also tried to use bcp to export a view of the same data with the following code:

bcp [database].dbo.table.view out file.txt -c -T

To which I get the following error:


Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '.'.


What is the proper syntax for either of these? is something else going on?

View 2 Replies View Related

BCP QUERYOUT From A Procedure?

Aug 28, 2007



Anyone ever BCP QUERYOUT from a stored procedure?

Something like this? Is this even possible?
BCP "exec usp_MyProc 1, N'SomeString'" QUERYOUT c:Output.txt -SMyServer -T -N

View 4 Replies View Related

@NUM_MEMBERS = EXEC ('USE ' +@X + '; SELECT COUNT(Member_Name) FROM Logins') -HOW?

Mar 27, 2004

DECLARE @X VARCHAR(10)
DECLARE @NUM_MEMBERS SMALLINT
SELECT @X = 'other_db'


@NUM_MEMBERS = EXEC ('USE ' +@X + '; SELECT COUNT(Member_Name) FROM Logins')

I get an error when I try to store the result in @NUM_MEMBERS. :/ I've been looking all over for the correct syntax. Can anyone help?

ERROR:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@NUM_MEMBERS'.


thx n adv

View 2 Replies View Related

Where&#39;s The &#34;queryout&#34; Option Located?

Oct 25, 2000

Where's the "queryout" option ? I need to insert the results of a query to a .csv file.

View 1 Replies View Related

Bcp Queryout Strange Behaviour

Aug 3, 2007

I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with '
'.

I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.

This is my bcp command:

bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:BeBackDetail.dat" -f "D:BeBackDetail.fmt" -e "D:BeBackDetailErrors.dat" -U user -P pass -S server

And format file:

8.0
4
1 SQLCHAR 0 2 "" 1 DIVCODE ""
2 SQLCHAR 0 3 "" 2 COMMCODE ""
3 SQLCHAR 0 10 "" 3 CONTACTID ""
4 SQLCHAR 0 512 "
" 4 NOTES ""

Has anyone seen this?

Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.

Thanks for any ideas -

View 1 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Xp_cmdshell Error In 6.5 For Non SA Login - Error 1326

Mar 17, 2000

I have a stored proc that attempts to execute xp_cmdshell using a login other than 'sa'. The permissions have been given to the login to execute xp_cmdshell and the stored procedure executes perfectly within the query analyzer in EM. However, when attempting to execute the same stored proc in ISQL_W under a login with permissions for everything as dbo (other than Master) I get error 1326 for LogonUser on line 359. This is with the "xp_cmdshell - Use SQLExecutiveCmdExec Account for Non SAs" disabled (as described as a "workaround" on the technet). Microsoft acknowledged this as being a 'bug' to 'fix' and put a 'fix' in Service Pack 5a. The server that is experiencing this problem is running service pack 5. Is there any difference between the service packs and if so, will installing service pack 5a help me? And if not, what can be done to alleviate this problem?

View 1 Replies View Related

EXEC Stored Procedure For Every Line Of SELECT Result Table - How?

Jul 23, 2005

Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert

View 2 Replies View Related

Qusetion About Return Values From EXEC('select Count(*) From XTable')

Aug 23, 2006

Hello everybody!

As the topic:

Can i get the value "count(*)" from EXEC('select count(*) from xTable')

Any helps will be usefull! Thanks!

View 6 Replies View Related

Xp_cmdshell Error

Feb 26, 2001

I'm trying to run a .vbs file through the xp_cmdshell sp. It works fine, but if there's an error in the vbscript, the xp_cmdshell will just hang.

When I run the file from the command line, an error dialog box comes up expecting you to click ok. Sql just hangs waiting for this to be resolved.

Anyone have any ideas about how to get around this?

thanks

View 2 Replies View Related

Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps

May 18, 2006

Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Is any easy way to do this?

A database role and then assing that role to the user?

View 4 Replies View Related

Error Running Xp_cmdshell

May 24, 2000

Hi everybody,

I am getting an error while running the XP_CMDSHELL 'NET USER /DOMAIN'.
But when I run the same command for sp_cmdshell 'net user' it works fine. It giving me the local users that are available on the my local pc. But now i want the domain users on my network which is not working with the xp_cmdshell..


Thanks in advance......

View 1 Replies View Related

Error Job Task And Xp_cmdshell

Jan 16, 2001

I'm trying to run a SQL Server 7.0 scheduled job that executes: Exec master..xp_cmdshell 'd:raining.bat'

If I run this in qry analyzer it runs fine. If I try to run it as a scheduled job it says 'can't find the specified file'

I'm a member of the sysadmins group and sqlexecagent is part of the NT admin group.

Can someone tell me how to fix this problem?

View 1 Replies View Related

Xp_cmdshell && WrapperRead() Error

Nov 14, 2004

Hi all,

SQL Server 2000 SP3a, SAN Connection, Standard Edition, 2000 Server edition OS.

I've implemented a log shipping program on one set of sql servers already, the SVR1 set, which are working fine.
I am implementing this on the SVR2 set and I have the wrapperread() problem.
All using SP3a, with MDAC version outlined below. All are reported to be running the SP1 Refresh.

SVR1 - 2.7 SP1 Refresh
SVR1_DR - 2.7 SP1 Refresh

SVR2 - 2.7 SP1 / 2.7 SP1 Refresh
SVR2_DR - 2.7 SP1 / 2.7 SP1 Refresh

When executing the command below in T-SQL I get the error below.
EXEC @sentok = master.dbo.xp_cmdshell @cmd

[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0

Any help would be gratefully accepted.

Regards,
JS

View 1 Replies View Related

Urgent Xp_cmdshell Error

Jul 10, 2007

i am getting followig error while using xp_cmdshell
can anyone help regarding this

Message

Description: [SQL-DMO]The name 'SPS90HFD1DSQLServer2005MSFTEUser$RAJServer$MSSQL' was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try again. [SQLSTATE 01000] (Message 0) An error occurred during the execution of xp_cmdshell. A call to 'CreateProcess' failed with error code: '1455'. [SQLSTATE 42000] (Error 15121). The step failed.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved