SQL 2012 :: BCP Script To Export Results To CSV - Native Error
Oct 16, 2015
I'm currently trying to using this bcp script to export results to a csv.
USE crm001_spark
GO
EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [datebasename].dbo.table name]" queryout "C:Customers.csv" -c -b 10000 -t , -T -S [server]datebasename'
and the ERROR that is coming out is
SQLState = 08001, NativeError = -1
NULL
Error = [Microsoft][SQL Native Client]Login timeout expired
Error = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
SQLState = 08001, NativeError = -1
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLState = S1T00, NativeError = 0
I have checked the server config manager TCP port and it is pointing to the correct port 1433. What else could be causing the error ?
I'm trying to export some tables from SQL Server 2012 into .xml format in a local drive and have run into a problem when reading the file in Excel for example.
SET @FileName = 'D:SQLextractsdbo.vwPOLICIES_Test'+'.xml' SET @bcpCommand = 'bcp "SELECT pvTransactionID, pvPolicyID, pvParentPolicyID, pvProductTypeName, pvClientID, [Code] ...
I've gone through each of the fields exported and the column pvInsuredName is what's causing the error. If I include column pvInsuredName in the script I end up with the Excel error message 'invalid file reference. the path to the file is invalid, or one or more of the referenced schemas could not be found' .
I'm guessing there's an issue with some of the characters contained within the column pvInsuredName causing the error.
I'm wondering if I can take native SQL log backup in addition to DPM backups. We're using DPM for both databases (daily) and logs (every 15min). So, if I take SQL log backup separately, will it break anything in DPM and we won't be able to perform point it time recovery?
Native backups to NAS do not complete.We have been experiencing an issue whereby our native backups are hanging with status': SUSPENDED/ RUNNABLE...I ran select * from sys.sysprocesses. All of the backup SPIDs processes show BACKUPTHREAD/PREEMPTIVE_OS_FILEOPS
This first occurred last Wednesday evening. When I discovered this on Thursday, I attempted to kill the backup jobs. This also hung with 0% completed/0% time remaining. Backups hung on more than one instance.That evening, I attempted to restart the instance which also failed with something along the lines of: could not start MASTER file in use.
I then restarted the server--which I really did not want to do--and this cleared it. I was also able to manually kick off maintenance plans (DBCC CHECKDB and full backup) without issue.I was off Friday and the weekend. I came in this morning and found the maintenance plans (diff/tlog backups) did not complete in some of the instances--in one case, the instance affected now was not affected before. They appeared to have hung on their next scheduled kickoff which was later that night after I went home
Remembering the "file in use" error, I have run process monitor to see if anything unusual had a lock on any files. I saw only SQL Server and Double-Take processes accessing log files.Being a relatively new DBA, I am user where to go next in trying to track down the cause of this issue. This is fairly urgent as one of the instances that has had this problem both times is our production SharePoint environment.
ENVIRONMENT:
SQL version: Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) May 22 2013 17:10:44 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
In our packages, we are using Microsoft SQL Server Native Client 10.0 provider to connect our SQL Server 2008 DBs, which is working fine till we are migrating 2008 DB server to 2012. after the upgrade, some of our packages are working fine, some are not. I'm just curiuos to know will the old provider(Microsoft SQL Server Native Client 10.0) will work fine for connecting 2012 DBs? or I need to update all our packages to re-point to new 2012 Provider.
The following (VB.Net) code causes exceptions at seemingly random times. Any suggestions? Not sure if the problem is in .Net's ODBC support or in Sql Native Client.
If MARS is off, usually after less than 100 loops:
Code Snippet
System.Data.Odbc.OdbcException was unhandled ErrorCode=-2146232009 Message="ERROR [HY000] [Microsoft][SQL Native Client]Connection is busy with results for another command" Source="SQLNCLI.DLL" Turning MARS is on bypasses that error, so it will sometimes survive a thousand or so runs before hitting:
Code Snippet
System.Data.Odbc.OdbcException was unhandled ErrorCode=-2146232009 Message="ERROR [23000] [Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'Number', table 'tempdb.dbo.#TempTable___..(shortened to fit).. _________000000002F3F'; column does not allow nulls. INSERT fails. Source="SQLNCLI.DLL" Code here: (Console Application, empty database, Visual Studio 2005, .Net 2.0, Windows XP,SQL Server 2005 or SQL Express 2005, Local or Remote)
Code Snippet
Module Module1
Sub Main()
'Dim connection As New SqlClient.SqlConnection("database=TestDB;server=.sqlexpress;Integrated Security=SSPI;") 'Doesn't crash 'Dim connection As New OleDb.OleDbConnection("Provider=SQLNCLI;database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Doesn't crash
'Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;") 'Crashes Dim connection As New Odbc.OdbcConnection("Driver={SQL Native Client};Database=TestDB;Server=.sqlexpress;Trusted_Connection=yes;MARS_Connection=yes") 'Crashes
VBMath.Randomize()
Dim run_count As Integer = 1 connection.Open() Console.WriteLine("Connected!!")
While True 'connection.Open()
Dim testcmd As New Odbc.OdbcCommand("CREATE TABLE [#TempTable] (Number int PRIMARY KEY)", connection) testcmd.ExecuteNonQuery() testcmd.Dispose() testcmd = Nothing
Dim dtTemp As New DataTable Dim daTemp As New Odbc.OdbcDataAdapter("SELECT * FROM [#TempTable]", connection) daTemp.MissingSchemaAction = MissingSchemaAction.AddWithKey daTemp.Fill(dtTemp) Dim cbTemp As New Odbc.OdbcCommandBuilder(daTemp) Dim viewTemp As New DataView(dtTemp, Nothing, "Number", DataViewRowState.CurrentRows)
Dim i As Integer For i = 1 To 1000 Dim test_number As Integer = CInt(Rnd() * 2000) If viewTemp.Find(test_number) = -1 Then 'Keep it unique Dim new_temp_row As DataRowView = viewTemp.AddNew() new_temp_row("Number") = test_number new_temp_row.EndEdit() End If Next
daTemp.Update(dtTemp)
'daTemp.Dispose() 'daTemp = Nothing
'cbTemp.Dispose() 'cbTemp = Nothing
'dtTemp.Dispose() 'dtTemp = Nothing
'viewTemp.Dispose() 'viewTemp = Nothing
Dim testcmd2 As New Odbc.OdbcCommand("DROP TABLE [#TempTable]", connection) testcmd2.ExecuteNonQuery() testcmd2.Dispose() testcmd2 = Nothing
'connection.Close() 'GC.Collect()
'Console.Write(".") run_count += 1 End While
End Sub
End Module
Usually the ".Update" triggers the exception, but sometimes the other sql commands do it. Have tried various experiments with disposing of objects immediately when finished, but no effect. Opening and closing the connection each loop seems to delay the errors, but it still happens eventually.
PDA info: .NET CF 1.0 env-string... MS SQL client MS SQLCE 2.0 dev MS SQLCE 2.0
DESKTOP info: Microsoft SQL Server Management Studio Express 9.00.2047.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
copied an app from http://www.codeproject.com/netcf/PocketPCwithSQLCE/PocketPCwithSQLCE_Src.zip modified its CONFIG.XML to... <?xml version="1.0" encoding="utf-8" ?> <Configuration> <DatabaseServer>192.168.1.89</DatabaseServer> <DatabaseName>D1Temp</DatabaseName> <DatabaseLogin>sa</DatabaseLogin> <DatabasePassword></DatabasePassword> <SQLCEURL>http://192.168.1.89/SqlMobile/sqlcesa30.dll</SQLCEURL> <LocalDBConnect>Provider=Microsoft.SQLServer.OLEDB.CE.2.0; Data Source=My DocumentsMyFav.sdf;</LocalDBConnect>
Hi, Based on the "SQL Book Server Online" from MSSQL 2000 I wrote the following codes in order to export the search results in XML format. se pubsselect 1 as tag, null as parent, stor_id as [store!1!stor_id], stor_name as [store!1!stor_name], null as [Order!2!Ord_Num], null as [Order!2!ord_date] from storesunion allselect 2 as tag, 1 as parent, sa.stor_id, null, sa.ord_num, sa.ord_datefrom sales sajoin stores ston sa.stor_id = sa.stor_idorder by [store!1!stor_id], [Order!2!Ord_Num]for xml explicit The problem is that I want to results to be something like: <stores> <store> <stor_id>6380</stor_id> <stor_name>Eric the Read Books </stor_name> <order> <ord_num>A2976</ord_num> <ord_date>1994-09-14 00:00:00.000</ord_date> </order> <order> <ord_num>722a</order_num> <ord_date>1994-09-13 00:00:00.000</ord_date> </order> </store> <store> <stor_id>7066</stor_id> <stor_name>Barnum's</stor_name> <order> <ord_num>6871</ord_num> <ord_date>1993-05-24 00:00:00.000</ord_date> </order> <order> <ord_num>QA7442.3</ord_num> <ord_date>1994-09-13 00:00:00.000</ord_date> </order> </store></stores> How can I get the results in this format? And, also, how can I export them directly into an xml file and save it on the disk? Thank you in advance for your answers.
I am experiencing some quirks when exporting to PDF. I have read all the other posts about page sizes, margins, etc and my situation seems to be a bit different.
I have a report that contains a bar chart (Height = 15cm, Width = 19cm) and below that a rectangle that contains two text boxes with totals information (Left = 11.6cm, Width = 7.4cm, Height 0.5cm)
Now most of the time it exports to PDF perfectly - all on one page as would be expected
If however the chart has more than say 20 items on the y-axis then the rectangle gets pushed onto an new page.
For extra information, but not to confuse the issue I also have a page header and footer that have items lined up right to the edge (left + width = 19cm) and they all display fine on the one page. It is just this rectangle that is having the problem.
When I move the rectangle to the left a bit (11.4cm) so 0.2cm away from the right hand edge then it works fine.
It is just very strange because I cant see how it is getting shifted only when the chart contains a lot of items.
I'm new in this forum and I 'm Italian, so my English won't be perfect!
If i want to connect to my SQL Server database in prompt i type:
"osql -U sa" and then i type the password. So, I get an error that says:
"[Sql Native Client] Provider Via: Impossible to find the specified module. [Sql Native Client] Access timeout expired [Sql Native Client] An error occured during the attempt to estabilish a connection to the server. During the connection to SQL Server this error may be caused by pre-defined settings that don't ......"
How i can solve this problem? Who can help me? Thank you!!
I'm new in this forum and I 'm Italian, so my English won't be perfect!
If i want to connect to my SQL Server database in prompt i type:
"osql -U sa" and then i type the password. So, I get an error that says:
"[Sql Native Client] Provider Via: Impossible to find the specified module. [Sql Native Client] Access timeout expired [Sql Native Client] An error occured during the attempt to estabilish a connection to the server. During the connection to SQL Server this error may be caused by pre-defined settings that don't ......"
How i can solve this problem? Who can help me? Thank you!!
I am running a SQL stored procedure which runs 3 queries on 3 different SQL tables. What is my best option to export the results of these 3 queries to excel?
If it matters they are all SELECT queries, and at most will return < 500 rows.
I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.
The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.
Can this be done using DTS or do i have to try another method?
I am starting to see this error appear randomly? This is on an application that uses ASP, but its been working fine for months... I'm using SQLExpress 2005 on Win2003 IIS6Microsoft SQL Native Client error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.I'm trying to figure out why its happening all of a sudden? Any ideas very appreciated...
As long as the output file is written to the local client, it completes successfully. But after changing the UNC path to another server with or without fully qualified names it is unable to open BCP host data-file. (sample shown below) ----------------------------------------------------------------- DECLARE @FileNamevarchar(2000), @bcpCommandvarchar(2000)
SET @FileName =REPLACE('\stladminy_drive ransfer maffrandauthors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand ='bcp "SELECT * FROM FKIAcctReceivable.dbo.PurchaseOrder" queryout "' SET @bcpCommand =@bcpCommand + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
--------------------------------------------------- xp_cmdshell has been enabled and the user has sysadmin rights.
Anybody have the same problem and willing to help?
Svr2: Windows 2003 R2 SP2 Standard IIS 6.0 SQL 2005 Standard SP2 WSUS 3.0 SP1 SCCM 2007 RTM
Svr2 query works fine, Srv1 query returns this error:
An error occurred when the report was run. The details are as follows: The query has been canceled because the estimated cost of this query (2147483647) exceeds the configured threshold of 100000. Contact the system administrator.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 8649
This is the query language:
SELECT DISTINCT dbo.v_R_System.Netbios_Name0 AS [Machine Name], dbo.v_R_System.Resource_Domain_OR_Workgr0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System Caption], dbo.v_R_System.Operating_System_Name_and0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.Version0 AS [Operating System Version], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Version], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [System Manufacturer], dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [System Model], dbo.v_GS_PC_BIOS.SerialNumber0 AS [System Serial Number (if Available)], dbo.v_GS_PROCESSOR.MaxClockSpeed0 AS [Processor Speed (GHz)], dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Number of Processors (or Cores)], dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], dbo.v_GS_LOGICAL_DISK.Size0 AS [Disk Space (MB)], dbo.v_GS_LOGICAL_DISK.FreeSpace0 AS [Free Disk Space (MB)], dbo.v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address], dbo.v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address], dbo.v_GS_PC_BIOS.Description0 AS [BIOS Description], dbo.v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [SM BIOS Version], dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0 AS [Windows Install Directory]
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_IPAddresses ON dbo.v_RA_System_IPAddresses.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_MACAddresses ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_LOGICAL_DISK ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_FullCollectionMembership ON (dbo.v_FullCollectionMembership.ResourceID = v_R_System.ResourceID)
WHERE (dbo.v_GS_LOGICAL_DISK.DeviceID0 = 'C:') AND dbo.v_FullCollectionMembership.CollectionID = 'in00000f' Order by dbo.v_R_System.Netbios_Name0
The below error happens when trying to use the SSIS Transfer SQL Server Objects Task to transfer objects. Related to (Bug?? Transfer SQL Server Objects Task - error when copying Views )?
--------------------
Error: 0xC002F325 at Transfer Data (NEW) Pre, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".
Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
Hello All, I am trying to create a local database.............using a simple tool called BuildDatabase.cmd for example this is what i pass in the command prompt C:My.databaseBuildDatabase SAMPLE1SQLEXPRESS MyTestDatabase this is the error i am getting........ [SQL Native Client]SQL Network Interfaces: Error Locating Server/InstanceSpecified [xFFFFFFFF].[SQL Native Client]Login timeout expired[SQL Native Client]An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may be caused bythe fact that under the default settings SQL Server does not allow remoteconnections. WARNING! Drop/Create Errors can someone please help me out....how to fix this. Thanks a lot
I am getting the above error if i try to access sqlce3.0 database from an application which already uses sqlce2.0, this error happens while initializing the sqlcecommand object I hope the reason might be executing the the sqlcecommand through a sqlce2.0 engine on a sqlce3.0 database,
But I tried installing the sqlce3.0 in the PDA , still I am getting the same error
I've got a query that returns the data I need. I want to put the query in a stored procedure such that, when the SP runs I get a pipe delimited text file on disk. I don't really want to mess with SSIS, etc. Is there a Q&D way to do this?
For both OLEDB destiantions (and hopefully for the forthcoming ADO.NET destination adapter) it would be useful to have the following two output columns: NativeErrorCode and NativeErrorMessage.
For SQL Server, this would allow you distiguish between multiple errors which all roll up to the SSIS error "the value violated the integrity constraints of the column", which is way too generic for proper decision making (via conditional split).
For example, like SQL Server replication, you should be able to ignore duplicate key errors (error number 2627) indicating the record existed, but error out on nullability constraint errors (number 515) in which the record could not be inserted. If you had a native error code, you could make this decision, while the SSIS error for two different native errors is precisely the same.
There is a known and accepted race condition between a lookup transform and subsequent OLEDB dest insert attempt (assuming a non-transacted container and a common component target table), which is why the 2627 can be safely ignored in certain instances, while a 515 should not be.
I need you help badly. Iam a student and iam working on "Creating a Mobile Application with SQL Server Compact Edition" http://msdn2.microsoft.com/en-us/library/ms171908.aspx . This tutorial works fine until
Create the publication snapshot
In SQL Server Management Studio, in Object Explorer, expand the (local) computer node.
Expand the Local Publications folder, select the publication name, right-click SQLMobile, and then click View Snapshot Agent Status.
In the View Snapshot Agent Status dialog box, click Start.
Make sure that the snapshot job has succeeded before you continue.
When I try to create a snapshot iam getting the following error "An unspecified error had occurred in the native SQL Server connection component." . I have no clue what to do next please help me out.
How to avoid a SQL Native Client Instalation (a corrupted package ? from MSDN Disc !?) during MS SQL 2005 Server Standard instalation on MS Windows Vista Ultimate RC1 ?!?
Generally after this error all instalation failed !
