Where's The "queryout" Option Located?
Oct 25, 2000Where's the "queryout" option ? I need to insert the results of a query to a .csv file.
View 1 Replies
ADVERTISEMENT
Bcp Errors With Queryout
Apr 19, 2004I 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
*/
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?
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
Bcp Queryout Strange Behaviour
Aug 3, 2007I 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 -
Error In --&&> EXEC Xp_cmdshell 'bcp SELECT * FROM Tbl A Where A.flag=N Queryout F.txt -c -T'
Aug 7, 2007some 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
I've Tried Exec Master..xp_cmdshell 'bcp DBCC CHECKDB Queryout C:Test.txt'
Mar 7, 2005Hi,
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...
In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing
Apr 27, 2008Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedUsing XP_CmdShell To Execute BCP Utility With QUERYOUT. How To Control The OWNER Of The File Created?
Jan 29, 2008I 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
SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option
Feb 10, 2015So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
Row Cannot Be Located For Updating
Jul 20, 2005Hi,I am getting "Row cannot be located for updating. Some values may havebeen changed" error when I try to update from visual basic with ado.This happens only when set as default locale on the pc other languagethan English.Anybody can help on this¿Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedSql Problem When I Run My Aspx Located In My IIS 5.0
Sep 10, 2004Hi! i'm dani, from spain. I´m looking for sombody to help me.
The trouble is as i have indicated. I get the next error when my page aspx needs to acced to the SQL Bd:
"Error of beginning of session of the user '(null)'. Reason: it is not associate to a connection of confidence SQL Server."
I hope a reply.
Thanks and greethings.
What File An Object Is Located On
Jun 20, 2001is there a way to find out what data file and object(table) resides on?
View 1 Replies View RelatedWhere TUF File Is Located In Logshipping
Apr 9, 2015where the .tuf file is located in Logshipping ?
View 3 Replies View RelatedSqlmangr.: The Ordinal 29 Could Not Be Located In
Apr 24, 2006Dear all
I've received the following error on the startup of SQL SERVER. Repeating the error when trying to start Service Manager.
sqlmangr.exe : The ordinal 29 could not be located in the dynamic link library odbcbcp.dll
Can someone help me to solve it
Reach SqlServer Located Another Server
Sep 17, 2007Hi,
I have a asp.net website and bind it with sqlserver but this sqlserver is located another server different from the server my website is located. So how can i bind them with eachother.
Thanks in advance...
Procedure For Modifying A Field In The Row Located Two Positions Before The Last One
Sep 15, 2006I want to create a procedure where each time I add a new record to a table I want to set the field “Display” ( BIT ) to “false” in a position that is two rows before the last one. How to do that ?
View 2 Replies View RelatedConnection Between Database && Website Located At Different Places
May 17, 2008I am creating a website. where the data to be stored in my server. I had my webspace from some service provider.
How can i connect the website and thee database.
--> website is deployed at webspace given by the provider.
--> database is at my server( located in our office).
****************How can i connect these website and database.*******************
Data Files Not Located On SQL2000 Server
May 6, 2002My company recently purchase a NetApp FS870 and we want to move the SQL data files for out production DB. The NetApp is a NAS. Without loggining in to the SQL Server and establishing a drive mapping.
Does anyone know how to connect SQL2K to the remote machine?
We would prefer to use UNC naming to access the share, as leaving servers logged in and open with a mapped drive is a huge secutrity hole, and presents a reliability issue too.
Thanks
Greg
Rename All Files Located In Folder But Not Working?
Apr 24, 2015SET nocount ON
DECLARE @fileNameNew VARCHAR(1024) -- filename of new error report
DECLARE @fileNameOld VARCHAR(1024) -- filename of old error report
DECLARE @out_fileDate CHAR(1024)
DECLARE @fileDate DATETIME -- used for file name
[code].....
Slow Stored Procedure - Easy Located But Wtf?
Jul 20, 2005Hi,Plz, I need some info (SQL2000) :)A stored procedure is like this:"Select table1.id, table1.txt, (select table2.nr from table2 wheretable2.fk_table1=table1.id) as nr where table1.id<>10"The essence here is that "select table2.nr from table2 wheretable2.fk_table1=table1.id" returns either the integer in table2.nr, or NULLif there isnt a match. The whole sentence runs EXTREMELY slow...3-4 sec.What is wrong?"select table2.nr from table2 where table2.fk_table1=table1.id" runs quicklyoutside the stored procedure. The original sentence without the "nr" (Selecttable1.id, table1.txt where table1.id<>10) runs quickly too...But together it slows down dramatically..why? I should mention that thesub-query could return NULL if theres no match in table2...But i cant seewhy that should slow things down (remember - it runs fine outside the SP)?Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.518 / Virus Database: 316 - Release Date: 11-09-2003
View 3 Replies View RelatedToolbox Is All Grayed Out And IUIService Could Not Be Located Received
Aug 6, 2006Every time I try to edit a Control flow or Data flow task I am getting the following error. My toolbox options are all grayed out also.
TITLE: Microsoft Visual Studio
------------------------------
The service System.Windows.Forms.Design.IUIService could not be located.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=UnableToLocateService&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any help is appreciated
Gulden
Accessing Remotely Located SQL Server Via Access
May 18, 2007Hi,
I need the code to create a connection between the Sql server using ACCESS form.
Can I do this. If yes . can you help mw with it.
Currently I am accessing the server with remote desktop connection
Regards,
Jay
Attach DB Fails If Not Located In Data Folder
Feb 4, 2008
Hi All:
Using SQL Express 2005.
I want to attach some *.mdf files that are located in a folder other than the SQL Server Data folder (for example, D:MyData). If I do so, then I get an error message that the files can not be updated because they are read only.
If I move the files to the SQL ServerData folder then they attach without errors and work fine.
Is there some way I can tell SQL Server to allow the attachments in my other folder as well?
Drill Through To Reports Located In Multiple Folders
Feb 28, 2008Report A contains a textbox that should navigate to Report B using the Jump To option. Report A and Report B are located in separated folders on the Reporting Server.
SSRS looks for the rdl file within the same folder when using the Jump To option. Is there away to navigate to a report located in a different folder on the same server?
Thanks
SQL2005 Prb1 -Row Cannot Be Located For Updating. Some Values May Have Been Changed
Mar 3, 2006hi ,
I recently moved from SQL 2000 to SQL 2005.
The client side is vb6 and using Dcom dll's hosted on the db server.
I have a table which has oninsert trigger
When the recordset is updated in the com class it throws the foll error :
"Row cannot be located for updating. Some values may have been changed since it was last read."
The same code was working with SQL 2000 !!!
Any clues.
implicit tran are off on the sql 2005 server.
Thanks in advance.
Can SA User Access Folder Located In Local System?
Mar 6, 2014Is there any way sa user can access folder located in local system.
Server Info:
SQL Server 2008 r2
OS: Windows Server 2008
The Module Containing Component Conditional Split (142) Cannot Be Located, Even Though It Is Regi
Sep 1, 2006Hi!
i am getting this error when i execute the package in production environment
Source: Select Tickets DTS.Pipeline
Description: The module containing "component "Conditional Split" (142)" cannot be located, even though it is registered.
End Error
Error: 2006-09-01 14:03:40.36
Code: 0xC004706E
Source: Select Tickets DTS.Pipeline
Description: The module containing "component "Data Conversion" (5863)" cannot be located, even though it is registered.
End Error
Error: 2006-09-01 14:03:40.36
Code: 0xC004706E
Source: Select Tickets DTS.Pipeline
Description: The module containing "component "OLE DB Source" (1)" cannot be located, even though it is registered.
End Error
Error: 2006-09-01 14:03:40.36
Code: 0xC004706E
Source: Select Tickets DTS.Pipeline
Description: The module containing "component "Excel Destination" (4516)" cannot be located, even though it is registered.
End Error
Error: 2006-09-01 14:03:40.36
Code: 0xC004706E
Source: Select Tickets DTS.Pipeline
Description: The module containing "component "Flat File Destination" (6486)" cannot be located, even though it is registered.
End Error
Progress: 2006-09-01 14:03:40.36
Source: Select Tickets
Validating: 0% complete
End Progress
Error: 2006-09-01 14:03:40.37
Code: 0xC0048021
Source: Select Tickets Conditional Split [142]
Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Conditional Split;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All R
ights Reserved; http://www.microsoft.com/sql/support;0".
End Error
Error: 2006-09-01 14:03:40.37
Code: 0xC0047017
Source: Select Tickets DTS.Pipeline
Description: component "Conditional Split" (142) failed validation and returned error code 0xC0048021.
End Error
Progress: 2006-09-01 14:03:40.37
Source: Select Tickets
Validating: 20% complete
End Progress
Error: 2006-09-01 14:03:40.37
Code: 0xC004700C
Source: Select Tickets DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2006-09-01 14:03:40.37
Code: 0xC0024107
Source: Select Tickets
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:03:39 PM
what is the problem? any one who has faced this
any solution
thanks,
jas
How Can I Transfer Text File To Table Located In Different Domains
Dec 26, 2007
Hi,
I have a package which is pulling data from a text file. The text file is located in different domain.
when I copy the text file manually to same domain where my sel server is located then Job is working fine.
How can I avoid this. Do we have any oher options ?
Composing A Reference From Fields Located In Mutiple Tables
Jun 12, 2007Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price
Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.
Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?
SQL Server 2008 :: Copy Tables From A Database Located In Hosting To PC
Oct 6, 2015I would like to know if is possible copy data from a database located in my hosting to my database located in my pc,but using a store procedure,how can i do that ?.What tool i have to use?
Sql server 2008
Flustered... A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.
Dec 5, 2006Hello everyone,
I have been playing with a Standard Version of VS 2005 and SSE 2005 and I just cannot get these two to interact together well. I am sure it is a noob problem but I have seen this error addressed on this forum and I am just not getting it. Here is exactly what I am doing.
I want to create a database within SSE using the Management Studio. Then, I want to connect to it with VS2005. Both SSE and VS2005 are local. I just cant seem to get this to work.
I always seem to get this error "An attempt to attach an auto-named database for file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataSecond.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
One thing... I create the website with Location: HTTP. I don't even know anymore why I do this.. I think because I like to be able to pull up a browser and get to my app. Other than that I am doing nothing special. However, I get the above error when I hit F5 to run the app inside VS2005.
Is there someplace where a complete noob can find a thorough and simple covering of this? Again, I have read through some of the previous posts on this and I don't understand too many of them and it seems as if there are at least 13 causes of which I don't know which one I have. Help!
Thanks.
SQL Server 2012 :: Finding What Table A User-created Statistics Is Located?
Feb 21, 2014I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies View Related