SQL Server 2008: How to export SQL data to CSV file with headers using BCP


I created a simple Stored Procedure (SP) that queries a table and renames the column names, and I'm able to push those results out to a CSV file *with* those header names when done manually.

But when I try to export the results to a CSV file by running the same SP using xp_cmdshell with bcp, the header row (Col1, Col2, and Col3) does not appear in the resulting CSV file.

ORIGINAL TABLE:

ColumnName1-----ColumnName2-----ColumnName3
Joe Schmo Customer
Jane Doe Customer
Tim Tiny Musician

QUERY WITHIN SP:

SELECT ColumnName1 AS Col1, ColumnName2 AS Col2, ColumnName3 AS Col3
FROM TABLENAME

QUERY RESULT:

Col1-----Col2-----Col3 <
Posted On: Sunday 30th of December 2012 10:26:50 PM Total Views:  270
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Problem with excel sheet upload into sqlserver

Everyone, I need to upload excel sheet in to the database. Which i am doing with the query Code: SELECT * INTO temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls', 'SELECT * FROM [qry_BA_Controlling (Report)$]') Here C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls is the path from where the excel file needs to be fetched. qry_BA_Controlling (Report) is the name of the worksheet. So on executing the query, a table with name 'temp' is created. With records that are populated from excel. Now here i have a date field in excel. sometimes the values of this field are not uploaded properly into the temp table. The values for this date field are set to NULL eventhough they have values in EXCEL. I have modified my query so, Code: Insert into temp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls', HDR=YES', 'SELECT * FROM [qry_BA_Controlling (Report)$]') Here temp is an existing table, i have defined the date type of the field [creation date] to varchar and uploaded the excel. Then i used **convert** to change the datatype to the correct format.. Code: update temp set [Creation date] = CONVERT (varchar,[Creation date],101) Even now it is populating NULL values..Or this conversion needs to be done while uploading. if so, please let me know or suggest me an alternative approach..
VIEWS ON THIS POST

64

Posted on:

Thursday 25th October 2012
View Replies!

How do i copy a table from one server to another

I need to copy a few tables from a database on one server, to another database on another server. How do I do it I don't want to backup the entire db because it's massive. Just a few tables.
VIEWS ON THIS POST

80

Posted on:

Thursday 25th October 2012
View Replies!

couldn't connect to server.please check error

Is there any reason that anybody can think of that a php script can connect to my MS database on one server and not on the other Other scripts on the offending server connect without issue, and they are using the same username/password/connection process. This is really puzzling me... PHP Code: $HOST="host"; $USER="user"; $PASS="pass"; $NAME="name"; //connectingtodatabase \t$cxn=mssql_connect($HOST,$USER,$PASS) \t\tordie("couldn'tconnecttoserver.".mssql_get_last_message()."pleasecheckerror."); //selectingdb \tmssql_select_db($NAME);
VIEWS ON THIS POST

140

Posted on:

Thursday 25th October 2012
View Replies!

Error using JDBC to connect MS SQL server 2005

Hi , Using JDBC trying to connect MS SQL server 2005. Getting the following error: org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Network error IOException: Connection refused: connectjava.sql.SQLException: Network error IOException: Connection refused: connectjava.net.ConnectException: Connection refused: connectorg.apache.commons.dbcp.DbcpException: java.sql.SQLException: Network error IOException: Connection refused: connectjava.sql.SQLException: Network error IOException: Connection refused: connectjava.net.ConnectException: Connection refused: connect Here is the TCP/IP setting in our SQL server 2005: I disable the Windows 2003 R2 firewall, but that didnt help. In SQL server 2005 for Remote connections: Enabled Local and Remote connections: Using both TCP/IP and Named Pipes In TCP/IP Prosperities: IP Addresses: IP1: Active: Yes Enabled: Yes IP address: the server IP address Example 192.168.0.1 TCP Dynamic port: 0 TCP Port: 1433 /// IP2: Active: Yes Enabled: Yes IP address: the server IP address Example 192.168.0.1 TCP Dynamic port: 0 TCP Port: 1433 //// IP3: Active: Yes Enabled: Yes IP address: the server IP address Example 192.168.0.1 TCP Dynamic port: 0 TCP Port: /// IPALL: Active: Yes TCP Dynamic ports: 1053 TCP Port: For Protocols: Under general: Enable: Yes Keep Alive: 30000 Listen ALL: Yes No Delay: No Please advice.
VIEWS ON THIS POST

87

Posted on:

Thursday 25th October 2012
View Replies!

Where is server's error log?

Hi I am trying to figure out a problem I have with MSSQL and googling the problem I was pointed to server's error log. Where is this log located on my computer
VIEWS ON THIS POST

74

Posted on:

Thursday 25th October 2012
View Replies!

Parsinf data from perl to ms sql server

I have a desktop in which i have installed perl. I have another machine with OS as windows server 2003 and install ms sql server 2005. My problem is that i want to send data from my desktop to the window server how do i do that. And help please.
VIEWS ON THIS POST

105

Posted on:

Friday 26th October 2012
View Replies!

Copy mdf files without stopping sql server

hi. how can I copy any mdf file to another folder without stopping sql server
VIEWS ON THIS POST

174

Posted on:

Friday 26th October 2012
View Replies!

Problems with server

Hi. I'm running MsSQL 2000 developers edittion on my home computer. It's running XP Pro. I mainly use it so I could play around with ASP.NET, webservices etc. Anyway after I create tables with Enterprise Manager, and restart the computer the tables I create disapear. Also the users I add to the database I created disapear too. One thing that remains is the database I created. Any idea what is going on I wasn't being able to find any info on this. Probably because I'm not even sure what too look for.
VIEWS ON THIS POST

101

Posted on:

Friday 26th October 2012
View Replies!

across server update and select issues

everybody. I am attempting the following relatively simple SQL. UPDATE Server.db.dbo.table1 SET Value = @Value WHERE Id IN (SELECT Id FROM table2) This update is taking up to 1 minute. However if I remove the select and replace with actual values the update is completed instantly ie UPDATE Server.db.dbo.table1 SET Value = @Value WHERE Id IN (id1, id2, id3) The select is also instant if executed in isolation. But when these two statements are combined time taken is too long Please note that the update is occurring on a different server and that table1 does contain update triggers. Any ideas on why this is happening Cheers!
VIEWS ON THIS POST

153

Posted on:

Friday 26th October 2012
View Replies!

copy table to different server

i need a script that can copy a table from a db on one server to another for me, but i have no idea how.. help much appreciated !
VIEWS ON THIS POST

334

Posted on:

Friday 26th October 2012
View Replies!

MSSQL server is using 851mb of memory?!

Recently here at work we have been having a lot of our web applications time out after a couple minutes. Really doing just basic things like counting the number of records in the database, etc. I went on to the machine itself (win2k) and looked at what MSSQL server was doing in the process list... and it was taking up a mind boggling 851 megabytes of memory. Out of a gig of RAM on that machine there was only 13mb available. We restarted it last night and when I checked it today it has already grown back up to 460mb. Is this normal\t I've never heard of applications taking up this much ram... especially since its not like anyone, or anything, is pulling down 850mb every second from the database. Is there some kind of known memory leak in MSSQL server (8.00.760 SP3) Or is there some other reason our web apps are now timing out, when previously they never did ...
VIEWS ON THIS POST

130

Posted on:

Friday 26th October 2012
View Replies!

Sql server to web server connection

hello, I am working on a job in MS SQL server for creating Email using Microsoft CDOSYS component. In mail creation there is an interface Message.addattachment in CDO which attach failes to the mail. this interface support file:\ http:// & ftp:// connections. When I try to attach file from file:\\ . that vb script work properly from SQL server. But when I am trying to atach file from URL. it display error "Interface not registered". While same script work on another MS SQL server well with URL I think it is some issuse to create connection between SQL server & web server. I will be
VIEWS ON THIS POST

109

Posted on:

Friday 26th October 2012
View Replies!

Create proc full backup when new DB created

how can i create a proc or something else that from now on every time new DB create first make a full backup of that DB into a folder automatically Thx
VIEWS ON THIS POST

85

Posted on:

Saturday 10th November 2012
View Replies!

What is the best way to test T-SQL stored procedures?

what is the best way to test T-SQL stored procedures Do You use some special tools for this task Wojtek
VIEWS ON THIS POST

135

Posted on:

Sunday 11th November 2012
View Replies!

Mapped Credentials

Hiya, I need to use Mapped Credentials in SQL Server (2008), so i'd just like clarification on what i'm doing. I am mapping to Active Directory user accounts. 1) Create the Credential in SQL Server, choosing the AD user account to be mapped 2) Create a SQL auth. User Account (Security > Logins), using the 'Map to Credential', choosing the Credential I created in step 1 However, during the creation of the credential (step 1), it asks me for a dedicated password. I would like for this to use the AD user password as we forcefully change user passwords every 6 weeks, and the fact I have to put something in makes me think it is a seperate password to the AD one. Is that the case
VIEWS ON THIS POST

63

Posted on:

Tuesday 20th November 2012
View Replies!

Accessing Sql compact edition through Native C++

Has anyone tried accessing SQL Compact edition file through native c++ code. I am trying to use the .sdf file through native c++ in linux system. I would appreciate a sample code.
VIEWS ON THIS POST

105

Posted on:

Tuesday 20th November 2012
View Replies!

SQL server 2000 problem

hi i m facing this problem that i couldnt start my SQL server after after nov 2010, i need to change my computer time back to before nov den the server can start. for now when i click on the start button it show me no errors msg but the server just wont start. anyone know the solutions
VIEWS ON THIS POST

202

Posted on:

Tuesday 20th November 2012
View Replies!

MSDE And remote connections

We have done a programme that connects to a SQL 2000 SERVER with ADO librarys, and it works ok both using ip address and machine names. But the same programme fails if it trys to connect to a MSDE, if the MSDE and the client programme are in differents machines. On the other hand it works propperly if MSDE and the client programme are in the same machine (DATA SOURCE=(local) in the connection string). Is that normal Should MSDE answer to remote connections Thank you very much for your help.
VIEWS ON THIS POST

159

Posted on:

Tuesday 20th November 2012
View Replies!

Stored procedure to get data into Excel format from MS SQL database

Hi I need to write a procedure to extract data from few tables and put it in Excel format...How do i go about it....EVEn if my procedure works how to put the result in excel format...Please somebody guide me..
VIEWS ON THIS POST

233

Posted on:

Tuesday 20th November 2012
View Replies!

Stored Procedure Security Question

In MS's help article on stored procedures it states: They can be used as a security mechanism. Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly. But I can't find any information as to how this works. I'm trying to run "set identity insert on" within a stored procedure, but get this error when I run it from an account with only datareader/datawriter priveleges: "The current user is not the database or object owner of table 'tablename'. Cannot perform SET operation." Of course when I run this sp with my own account, it runs just fine.
VIEWS ON THIS POST

65

Posted on:

Tuesday 20th November 2012
View Replies!

sql text/memo field type problem

Hi all, I am new to the SQL server and had transferred data from an Access database. went well except one field - a memo type. Scenario: I had copied-and-pasted MS Powerpoint slide text to database. (Didn't want to retype data) As you know (from a MS user standpoint)this causes a minor problem with extra spaces and the curly quotes. The database formats these 2 things as question marks. Not too many to retype; however, on larger text fields I am "locked" out of the records that are relatively large. I am confused as to why I am able to alter the smaller (but same memo tyoe field data) I have it set up for nvchar 4000. These are Powerpoint texts. The text is no where near 4000 but I wasn't able to edit the larger input Please tell me where I am going wrong. I am used to a simple Access datase with either 1. text field or 2. a 64k memo field. What should I set this field to and perhaps I am missing some other setting on SQL server.
VIEWS ON THIS POST

247

Posted on:

Tuesday 20th November 2012
View Replies!

Time Based Sql Trigger - send email

Can this be done with MS SQL Server 2000 Periodically (every 15 minutes) I would like to check a table...if the record submit datetime is older than 30 days, and the record status is 'pending', I want to trigger an email reminder. I know I can send an email...I just don't know if it can be triggered by something besides an INSERT, DELETE, OR UPDATE.
VIEWS ON THIS POST

118

Posted on:

Tuesday 20th November 2012
View Replies!

Query for latest blog record for each user

I have a simple blog system by where users can leave an entry in a diary. I want to list all users along with their latest entry. It seems simple but I just can't do it. DISTINCT doesn't work because I want to get more than 1 field returned but only want the UserID to be distinct. There is a users table, and a diary table eg. Users ID Name 1 Tom 2 Jane Diary ID UserID Entry 1 1 Test 2 2 Test2 3 2 Test3 4 1 Test4 So I want the returned records to be like: ID UserID Entry 3 2 Test3 4 1 Test4 Ie, Each user with one latest diary entry. Any tips would be gratefully received!
VIEWS ON THIS POST

127

Posted on:

Tuesday 20th November 2012
View Replies!

SP to insert IF new UPDATE IF existing

I have a SP that inserts a record: Code: IF @action = 'add' BEGIN INSERT INTO OrderDetail (orderid, itemid, iquantity) VALUES (@orderid, @itemid, @itemQ) END I want to add a nested IF statement to check to see if a record exist WHERE orderid AND itemid = @orderid AND @itemid. I am thinking I should use something like: Code: SET @orderlineid int = SELECT orderlineid FROM OrderDetail WHERE orderid = @orderid AND itemid = @itemid IF (@orderlineid NOT NULL) BEGIN UPDATE OrderDetail SET iquantity = @itemQ WHERE orderlineid = @orderlineid ELSE INSERT INTO OrderDetail (orderid, itemid, iquantity) VALUES (@orderid, @itemid, @itemQ) END Is there a better way
VIEWS ON THIS POST

183

Posted on:

Tuesday 20th November 2012
View Replies!

Start and Stop SQL??

I am trying to setup my SQL server to backup several databases, however when i try to run the backup it says cannot start back up because server is running. Is there a way i can stop the sql server, run the backups then start it again using a schedule, so i can set it for early morning
VIEWS ON THIS POST

62

Posted on:

Tuesday 20th November 2012
View Replies!

two servers 1 being remote

There is a remote server being set up that is a database (sql2K) This server will store paths to its folders containing scanned images (images around 250k each) I have a website that has a calendar of events, the user of the calendar can have the ability to select supporting documents for the event they create on the calendar (so the calendar event needs a link to the documents on the other server. The website is fully under a ssl key and the remote db server is not. I don't was to inconvience users / give up security by having them click a link to load files from an unsecure location. How would it be best to keep the security of the ssl and create a link from the secure web calendar of events to unsecure supporting documents on another server. When the user uses the calendar of events, since it is dynamic, the users login information is passed along so the user views their information. This is what i was thinking, would it be possible to pass the users Login information to a store procedure in remote server (which would only be listening for the web servers ip address) THis procedure would then pass back to the website (at a different ip) the urls to the images. My main issue is avoiding the security prompts created from importing unsecure info and clicking a link to unsecure info FROM a secured. Any suggestions on how i can do this
VIEWS ON THIS POST

222

Posted on:

Tuesday 20th November 2012
View Replies!

Calculating datediff on without using weekends, etc.

I use the following query to gather info for a report ... as you can see I calculate the amount of time bewteen open and closed (days, hours, minutes) and open and onsite the same way. Which, btw, works perfectly. [highlight=SQL] select t.ticketnumber as [Ticket #], c.fName+' '+c.lName as Contact, t.openeddate+' '+t.openedtime as [Date Open],t.closeddate+' '+t.closedtime as [Date Closed], case when datepart(yyyy,closeddate) '1900' then cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)/60/24 as varchar(10))+' day(s) '+ cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)/60%24 as varchar(5))+' hr(s) '+ cast(datediff(mi, openeddate+openedtime, closeddate+closedtime)%60 as varchar(5))+' min(s) ' else '' end as [Time to Close], t.problem as [Problem], t.resolution as [Resolution], t.strOnsite as [Onsite Date], case when t.strOnsite is not null then cast(datediff(mi, openeddate+openedtime, strOnsite)/60/24 as varchar(10))+' day(s) '+ cast(datediff(mi, openeddate+openedtime, strOnsite)/60%24 as varchar(5))+' hr(s) '+ cast(datediff(mi, openeddate+openedtime, strOnsite)%60 as varchar(5))+' min(s) ' else '' end as [Time to Onsite] from ticket_master t, contact_master c where c.company = 4 and t.contactID = c.id and openeddate >= dateadd(mm,-1,getdate()) and openeddate < dateadd(dd,-1,getdate()) or c.company = 4 and t.contactID = c.id and closeddate > dateadd(mm,-1,getdate()) and closeddate < dateadd(dd,-1,getdate()) order by t.openeddate, t.openedtime [/highlight] Now they only want to count Mondays through Fridays (Saturday/Sunday) should not be used in the calculation, nor should anything outsite of 8:00AM t0 5:00pm ... Here's my question, can this be done I have a function that will count business days, and I guess I can then convert that to hours ... but how will I get the number of days, hours, minutes ... based on 5 days a week and 8 hours a day as opposed to 7 days a week and 24 hours a day.
VIEWS ON THIS POST

117

Posted on:

Monday 26th November 2012
View Replies!

Force primary ID

How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc. Pretty much how to force it being on number I would like to begin.
VIEWS ON THIS POST

103

Posted on:

Monday 26th November 2012
View Replies!

VB and SQL: Insert data into the DB ? ID doesn?t generate beyond 10?

Can someone tell me what is wrong in this code Why the ID doesnt generate beyond 10 in SQL table The data inserts into the db fine. When it is up to 10, then it keeps generating 10s. It doesnt create/generate a new ID like 11,12,13, so on PHP Code: Try \t\t\t\t\t\t\t\tDimsqlAsString="INSERTEvents(ID,FirstName,LastName,Address)VALUES"_ \t\t\t\t\t\t\t\t\t\t&"(@ID,@FirstName,@LastName,@Street)" \t\t\t\t\t\t\t\tUsingcmdAsSqlCommand=NewSqlCommand(sql,con) \t\t\t\t\t\t\t\t\t\tcmd.Parameters.Add(NewSqlParameter("@ID",GetMax(con,"Events","ID"))) \t\t\t\t\t\t\t\t\t\tcmd.Parameters.Add(NewSqlParameter("@FirstName",txtFirstName.Text)) \t\t\t\t\t\t\t\t\t\tcmd.Parameters.Add(NewSqlParameter("@LastName",txtLastName.Text)) \t\t\t\t\t\t\t\t\t\tcmd.Parameters.Add(NewSqlParameter("@Street",txtStreet.Text)) \t\t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t\t\tcmd.ExecuteNonQuery() \t\t\t\t\t\t\t\tEndUsing PrivateFunctionGetMax(ByValconAsSqlConnection,ByValtableNameAsString,ByValfieldAsString)AsInteger \t\t\t\tUsingcmdAsSqlCommand=NewSqlCommand(String.Format("selectISNULL(MAX({0}),0)from{1}",field,tableName),con) \t\t\t\t\t\tReturnCInt(cmd.ExecuteScalar())+1 \t\t\t\tEndUsing \t\tEndFunction
VIEWS ON THIS POST

105

Posted on:

Monday 26th November 2012
View Replies!

Printing Message when count is zero

Have the below code which nicely displays all the user tables in a db and a record count for each table. However, if there are no tables nothing is displayed but the column headers. If there are no tables I would like the message "No User Tables Exist" to appear in the Table Name column and a "0" to appear in the Record Count column. I think I need to use an IF Exists in combination with a PRINT statement, but all my attempts at doing so return errors. Any ideas how I can do this Code: IF OBJECT_ID('tempdb..#cnt') IS NOT NULL drop table #cnt create table #cnt ([Table Name] varchar(256), [Record Count] int) go sp_msforeachtable 'insert #cnt select '''' as [Table Name],count(*) as [Record Count] from ' go select * from #cnt go drop table #cnt
VIEWS ON THIS POST

69

Posted on:

Monday 26th November 2012
View Replies!

two databases

I have two databases inside sql server 2000 both have access 2000 as the GUI user interface. How can I make an interface between the two of them. Like a command button that take you to a table in a different database how do I go about doing that\t can anyone help\t
VIEWS ON THIS POST

186

Posted on:

Monday 26th November 2012
View Replies!

Temporary Tables

Does anyone know how to re-write this query so I can create a temporary table to insert information as oppose to inserting info into the Sclassnoprice table and deleting its contents each time. It is taking up to much space on the server by doing this and i've been told to create temporary tables but not sure how to CREATE PROCEDURE dbo.ByClassNoPrice ( @t_week_end_date smalldatetime, ) AS SET NOCOUNT ON DELETE FROM dbo.Sclassnoprice INSERT INTO Sclassnoprice(t_row_type, t_account_ref_no, account_no, agent_name, publication, publication_name, edition_name, date, t_quantity, t_allowance, calculated_price, vat, t_week_end_date, day_of_week, t_sort_col, timestamp, Type) SELECT dbo.cms_out.t_row_type, dbo.cms_out.t_account_ref_no, dbo.cms_out.account_no, dbo.cms_out.agent_name, dbo.cms_out.publication, dbo.cms_out.publication_name, dbo.cms_out.edition_name, dbo.cms_out.date, dbo.cms_out.t_quantity, dbo.cms_out.t_allowance, dbo.cms_out.calculated_price, dbo.cms_out.vat, dbo.cms_out.t_week_end_date, dbo.cms_out.day_of_week, dbo.cms_out.t_sort_col, dbo.cms_out.timestamp, dbo.dbo_Nagents_classification.Type FROM dbo.dbo_Nagents_classification RIGHT JOIN dbo.cms_out ON dbo.dbo_Nagents_classification.account_no = dbo.cms_out.account_no WHERE (dbo.cms_out.t_row_type = 'S') AND (dbo.cms_out.t_week_end_date = @t_week_end_date); GO
VIEWS ON THIS POST

33

Posted on:

Monday 26th November 2012
View Replies!

storesprocedure variables

Hi! I have a storedprocedure question. Can a use a parameter as a SQL-Command. I tried something like this with out success. Code: @sortorder = 'DESC' SELECT * FROM tblUsers ORDER BY usrName @sortorder
VIEWS ON THIS POST

126

Posted on:

Monday 26th November 2012
View Replies!