Converting a SQL table into Text file

How to convert a SQL table into Text file I have a table and I want to extract the values with the field names above to a text file. The query should also allow me to define the starting position of the fields in the text file.


Posted On: Friday 26th of October 2012 12:04:58 AM Total Views:  334
View Complete with Replies

Related Messages:

converting datetime from character string   (241 Views)
Can anyone tell me what this error message means and how I can correct it on my statement: Code: Server: Msg 241, Level 16, State 1, Line 1 Syntax error converting datetime from character string. Here's my query statement: Code: SELECT animalid AS "Animal ID", name AS "Name", categoryid AS "Category ID", DATENAME(MONTH, dateBorn) + ' ' + DATENAME(DAY, dateBorn) + ', ' + DATENAME(YEAR, dateBorn) AS "Date of Birth" FROM animal WHERE categoryid = 'Cat' AND dateBorn = 'May%' I know it has something to do with: dateBorn = 'May%' because when I took out Quote: AND dateBorn = 'May%' I get results, but I need to narrow it down to list the cats born in May.
Copying data from one table to another table   (286 Views)
I need to copy data from one table into another table. The field names are NOT the same: I need to INSERT Table 1 fields: cdescurl cname cdescription cimageurl category cprice INTO Table 2: product_url name description image_url category price How do I do this Thank you in advance for any help you can give me!
copy table to different server   (330 Views)
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 !
aggregates from two unrelated tables   (361 Views)
Hi I have similar problem and i explained in detail here Pls help me to fix this sql Regds Bala
Combining info from two tables?   (391 Views)
, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other. So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written Sorry if this is a little confusing but im having a hard time visualising how to do this.
Help with MS SQL Server - updating data from 2 tables   (322 Views)
Hi all, Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables If yes- what would be the query structure for that Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it Thank you in advance.
problem w/ linked table permissions   (314 Views)
This question involves SQL server as well as Access, so I hope that someone can follow. I'm trying to give someone read and write access to a table in SQL server through a linked table scenario in Access. I set up a new user account with datareader and datawriter permissions and made sure it went into the particular table as well. I also re-created the DSN on the user's local machine using this new login information. However, each time you open up the table after logging in using this info and try to edit a field, it gives a "Write Conflict" error and gives three choices: Save Changes, Copy to Clipboard, Drop Changes and of course, Save Changes is disabled. We also tried logging in using the db admin account and it won't work. Anyone have similar experiences with this If so, is there a work-around so that you can edit data through Access
two select statements on one table   (310 Views)
Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this. SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14) Any ideas greatly appreciated.
Open table in Query Analyzer   (285 Views)
In query Analyzer you can right click on a table and select open. When you do it opens the table as if you were in Enterprise Manager(you can edit the data). Does anyone know the syntax of how to write that in a query so you do not have to right click and select open
Help please - Conversion failed when converting the varchar to bit   (190 Views)
Hi I am trying to bulk upload a csv file (with 47 columns) into SQL database but when the insert is executed it generates an error : "Conversion failed when converting the varchar value '""True""' to data type bit. " And thats the same with all fields that are not varchar - datetime, int etc etc. Here is the code i use: Code: csv_to_read="../vendorfeed/" & newsletter.Fields.Item("feedname").Value set fso = createobject("scripting.filesystemobject") set act = fso.opentextfile(server.mappath(csv_to_read),1,False) dim sline dim sSeg Do Until act.AtEndOfStream sline=act.readline sSeg=split(sline,",") dim strsql strsql="INSERT INTO vendor_products (productcode, smalldes, fulldes, description, vendorid, productcost, originalprice, currentprice, openingquantity, currentquantity, hideshow, categoryid, subcategoryid, showhometop, showhomesecondary, poshomesecondary, showsecondpage, showpickmonth, showfeature, possecondpage, productimage, productimagelarge, productimageth, manu_name, ManufacturerID, productweight, stock, Price, Break1, Price2, Break2, Price3, Break3, InStock, TimeKey, Special, dimensions, overridereturntext, overridereturn, overridepricemargin, overridepricecurrent, newpricemargin, newcpmargin, freeshipping, discontinued, productsource, pagetitle)" strsql=strsql & "VALUES('"&sSeg(0)&"', '"&sSeg(1)&"', '"&sSeg(2)&"', '"&sSeg(3)&"', '"&sSeg(4)&"', '"&sSeg(5)&"', '"&sSeg(6)&"', '"&sSeg(7)&"' , '"&sSeg(8)&"', '"&sSeg(9)&"', '"&sSeg(10)&"', '"&sSeg(11)&"', '"&sSeg(12)&"', '"&sSeg(13)&"', '"&sSeg(14)&"', '"&sSeg(15)&"', '"&sSeg(16)&"', '"&sSeg(17)&"', '"&sSeg(18)&"', '"&sSeg(19)&"', '"&sSeg(20)&"', '"&sSeg(21)&"', '"&sSeg(22)&"', '"&sSeg(23)&"', '"&sSeg(24)&"', '"&sSeg(25)&"', '"&sSeg(26)&"', '"&sSeg(27)&"', '"&sSeg(28)&"', '"&sSeg(29)&"', '"&sSeg(30)&"', '"&sSeg(31)&"', '"&sSeg(32)&"', '"&sSeg(33)&"', '"&sSeg(34)&"', '"&sSeg(35)&"', '"&sSeg(36)&"', '"&sSeg(37)&"', '"&sSeg(38)&"', '"&sSeg(39)&"', '"&sSeg(40)&"', '"&sSeg(41)&"', '"&sSeg(42)&"', '"&sSeg(43)&"', '"&sSeg(44)&"', '"&sSeg(45)&"', '"&sSeg(46)&"')" objconn.execute strsql loop act.close set act=nothing I know that this can be done and i have done it with access before and it worked but with sql it is not working Please help me.
ARGH! Error converting data type varchar to datetime   (165 Views)
this is quite possibly the strangest thing. It makes no sense to me the function calls a stored procedure sp_u_tracking_loan which is a basic update sp_u_tracking_loan Code: CREATE PROCEDURE dbo.sp_u_tracking_loan @Loan_ID int, @loan_type_id int = NULL, @loan_origin varchar(20) = NULL, @loan_tpv_id int = NULL, @loan_relationship_id int = NULL, @facility_id int, @loan_application_number varchar(50) = NULL, @Loan_File_Number varchar(50) = NULL, @loan_processor_id varchar(20) = NULL, @loan_credit_analyst_id varchar(20) = NULL, @loan_officer_id varchar(20) = NULL, @loan_closing_tpv_id int = NULL, @loan_lo_given_date datetime='01/01/1900', @loan_ca_given_date datetime='01/01/1900', @user_id varchar(20) = NULL, --the user updating -- Output Parameter @tran_status varchar(7) output AS BEGIN SET NOCOUNT ON DECLARE @errorsproc varchar(100) SET @errorsproc = OBJECT_NAME(@@procid) DECLARE @errormsgid int /*** UPDATE LOAN ***/ IF @loan_lo_given_date='12:00:00 AM' SET @loan_lo_given_date='01/01/1900' IF @loan_ca_given_date='12:00:00 AM' SET @loan_ca_given_date='01/01/1900' BEGIN TRANSACTION upd_loan SAVE TRANSACTION upd_loan BEGIN UPDATE tbl_loan SET loan_type_id=@loan_type_id, loan_origin=@loan_origin, loan_tpv_id=@loan_tpv_id, loan_relationship_id=@loan_relationship_id, loan_application_number=@loan_application_number, Loan_File_Number=@Loan_File_Number, loan_administrator_id=@loan_processor_id, loan_underwriter_id=@loan_credit_analyst_id, loan_officer_id=@loan_officer_id, loan_closing_tpv_id=@loan_closing_tpv_id, modified_by=@user_id, modified_date=Getdate(), loan_lo_given_date= CASE WHEN convert(datetime,@loan_lo_given_date) = '01/01/1900' THEN loan_lo_given_date ELSE convert(datetime, @loan_lo_given_date) END, loan_ca_given_date=CASE WHEN convert(datetime,@loan_ca_given_date) = '01/01/1900' THEN loan_ca_given_date ELSE convert(datetime, @loan_ca_given_date) END WHERE loan_id = @loan_id END -- check for errors IF (@@error 0) BEGIN ROLLBACK TRANSACTION upd_loan set @tran_status='error' RETURN END ELSE BEGIN COMMIT TRANSACTION upd_facility set @tran_status = 'success' END END GO Code: Public Function Loan_Tracking_Modify(ByVal strUser As String) As String Dim cmdObject As New ADODB.Command Dim strTranStatus As String On Error GoTo ERR_HND If Trim(LCase(v_loan_origin)) = "direct" Then v_loan_tpv_id = 0 End If '*** Open Connection to the DB *** If ConnectionOpen Then '*** Set properties and execute command object *** With cmdObject .CommandType = adCmdStoredProc .ActiveConnection = objConn .CommandText = "sp_u_tracking_loan" '.CommandText = "sp_u_tracking_loan_test" .CommandTimeout = 750 .Parameters.Append .CreateParameter("@loan_id", adInteger, adParamInput, , v_loan_id) .Parameters.Append .CreateParameter("@loan_type_id", adInteger, adParamInput, , v_loan_type_id) .Parameters.Append .CreateParameter("@loan_origin", adVarChar, adParamInput, 18, v_loan_origin) .Parameters.Append .CreateParameter("@loan_tpv_id", adInteger, adParamInput, , v_loan_tpv_id) .Parameters.Append .CreateParameter("@loan_relationship_id", adInteger, adParamInput, , v_loan_relationship_id) .Parameters.Append .CreateParameter("@facility_id", adInteger, adParamInput, , v_facility_id) .Parameters.Append .CreateParameter("@loan_application_number", adVarChar, adParamInput, 50, v_loan_application_number) .Parameters.Append .CreateParameter("@loan_file_number", adVarChar, adParamInput, 50, v_loan_file_number) .Parameters.Append .CreateParameter("@loan_processor_id", adVarChar, adParamInput, 20, v_loan_processor_id) .Parameters.Append .CreateParameter("@loan_credit_analyst_id", adVarChar, adParamInput, 20, v_loan_credit_analyst_id) .Parameters.Append .CreateParameter("@loan_officer_id", adVarChar, adParamInput, 20, v_loan_officer_id) .Parameters.Append .CreateParameter("@loan_closing_tpv_id", adInteger, adParamInput, , v_loan_closing_tpv_id) .Parameters.Append .CreateParameter("@user_id", adVarChar, adParamInput, 20, strUser) 'Added by Mike Cohen 12/21/05 .Parameters.Append .CreateParameter("@loan_lo_given_date", adDBTimeStamp, adParamInput, 8, v_lo_given_date) .Parameters.Append .CreateParameter("@loan_ca_given_date", adDBTimeStamp, adParamInput, 8, v_ca_given_date) .Parameters.Append .CreateParameter("@tran_status", adVarChar, adParamOutput, 7) Call writeToLog("HIGHT", "LMSCom.dll", "Loan_Tracking_Modify", 0, "v_lo_given_date: " & v_lo_given_date & vbCrLf & "v_ca_given_date: " & v_ca_given_date) '*** Execute command *** .Execute '************************************************************** '*** Get strTranStatus output from sql server that means: '*** Success=error - Operation aborted '*** Success=success - Entries saved successfully '************************************************************** If objConn.Errors.Count > 0 Then GoTo ERR_HND strTranStatus = .Parameters("@tran_status").value End With Loan_Tracking_Modify = strTranStatus '*** Close Command Object *** cmdObject.Cancel Set cmdObject = Nothing '*** Close Connection to the DB *** Call clConnect.ConnectionClose Else Loan_Tracking_Modify = "error" End If Exit Function ERR_HND: Loan_Tracking_Modify = "error" Call writeToLog("HIGHT", "LMSCom.dll", "Loan_Tracking_Modify", Err.Number, Err.Description, Err.HelpFile) End Function the two lines in red are new fields added and are the fields in question. Both are datetime fields in the SQL DB I feel as though I've tried 50 different things, but I keep receiving the following error in my custom log: Loan_Tracking_Modify|-2147217913|[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime. and if I change the two date fields to strings/varchar throughout, I receive this error: -2147217913|[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string. HELP!
Arithmetic overflow error converting numeric to data type numeric   (213 Views)
Hi I Have StoreProcedure With By Run:Output Error: Msg 8115, Level 16, State 8, Procedure BD_SiteTrack_Reports_Hourly_WebTraffic, Line 33 Arithmetic overflow error converting numeric to data type numeric. I Help Me
Error converting data type nvarchar to uniqueidentifier   (175 Views)
Hi all, when I runn my VB.NET application, I got "Error converting data type nvarchar to uniqueidentifier" The procedure is: CREATE PROCEDURE SessionsUpdate @newID uniqueidentifier AS UPDATE Sessions set time_stamp = getdate() where newID = @newID GO Any one can give me some idea
Syntax error converting the nvarchar value   (167 Views)
We have just upsized an Access database to a MSSQL database, our website is built using ASP/VBscript and we used to use the following SQL statement(in dreamweaver): SELECT *, (link_url &'ID='& ID) AS NewURL FROM dbo.menu_mnu WHERE visible_mnu = 1 ORDER BY order_mnu ASC Which doesnt work with a MS SQL database so we changed it to: SELECT *, (url_link_mnu +'ID='+ id_mnu) AS NewURL FROM dbo.menu_mnu WHERE visible_mnu = 1 ORDER BY order_mnu ASC However this gives this error Syntax error converting the nvarchar value 'link.aspID=' to a column of data type int. Anyone got a solution Steve
[Help!!]error converting data type nvarchar to datetime   (272 Views)
hello , iam not familiar with sql or .net, so iam looking for someones expertise! Iam using Microsoft dynamics POS 2009 on windows 7. along with the point of sale package comes SQL Server 2008 & Visual Studio 2005. when trying to generate a sales report i get this error; "Unable to retrieve report data for the report. Error Details: Error converting data type nvarchar to datetime." It was working before i reinstalled windows 7 (up from win 7 Prem to Ultimate) i also done windows updates which included updates for the SQL server package. i had a search around to see if i could find answers but its all a bit over my head, walk through instructions would be a help. if anyone would like to give a helping hand, that would be amazing!
Syntax error converting the varchar value 'SELECT   (180 Views)
hi, I was wondering if some one could help me or check my stored procedure for me, i keep getting this error:- Syntax error converting the varchar value 'SELECT DISTINCT dbo.tbl_Work_Requests.Work_Request_PK, dbo.tbl_Work_Request_Status.Work_Request_Status_Ti tle, dbo.tbl_Line_Of_Business.Line_Of_Business_Name, dbo.tbl_Work_Requests.Work_Request_Number, dbo.tbl_Work_Requests.Work_Request_Title, dbo.tbl_Work_Requests.Work_Request_Rank, This is my stored procdure:- Code: CREATE PROCEDURE FilterProcMain ( @ReferenceNumber varchar(25)=null, @Title varchar(1000)=null, @owner int=null, @status int=null, @team int=null, @lineofbusiness int=null, @requestor varchar(50)=null ) AS Set NoCount ON If (@ReferenceNumber = '') AND(@ReferenceNumber 'N/A') SET @ReferenceNumber = NULL If (@Title = '') AND(@Title 'N/A') SET @Title = NULL If (@owner = '') AND (@owner 1000000) SET @owner = NULL If (@status = '') AND (@status 1000000) SET @status = NULL If (@team = '') AND (@team 1000000) SET @team = NULL If (@lineofbusiness = '') AND (@lineofbusiness 1000000) SET @lineofbusiness = NULL If (@requestor = '') AND(@requestor 'N/A') SET @requestor = NULL /* Variable Declaration */ Declare @SQLQuery AS Varchar(4000) Set @SQLQuery = 'SELECT DISTINCT dbo.tbl_Work_Requests.Work_Request_PK, dbo.tbl_Work_Request_Status.Work_Request_Status_Title, dbo.tbl_Line_Of_Business.Line_Of_Business_Name, dbo.tbl_Work_Requests.Work_Request_Number, dbo.tbl_Work_Requests.Work_Request_Title, dbo.tbl_Work_Requests.Work_Request_Rank, dbo.tbl_Work_Requests.Date_Requested, dbo.tbl_Work_Requests.Work_Request_Requestor, dbo.tbl_Work_Requests.Line_Of_Business_FK, dbo.tbl_Work_Requests.Work_Request_Status_FK FROM dbo.tbl_Line_Of_Business left JOIN dbo.tbl_Work_Request_Status left JOIN dbo.tbl_Work_Request_Tasks left JOIN dbo.tbl_User_Groups left JOIN dbo.tbl_Users ON dbo.tbl_User_Groups.User_Group_PK = dbo.tbl_Users.User_Group_FK ON dbo.tbl_Work_Request_Tasks.User_ID_FK = dbo.tbl_Users.User_ID_PK right JOIN dbo.tbl_Work_Requests ON dbo.tbl_Work_Request_Tasks.Work_Request_FK = dbo.tbl_Work_Requests.Work_Request_PK ON dbo.tbl_Work_Request_Status.Work_Request_Status_PK = dbo.tbl_Work_Requests.Work_Request_Status_FK ON dbo.tbl_Line_Of_Business.Line_Of_Business_PK = dbo.tbl_Work_Requests.Line_Of_Business_FK WHERE (1=1) ' If @ReferenceNumber Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Number like %' + @ReferenceNumber + '% ' If @Title Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Title like %' + @Title + '% ' If @owner Is Not Null Set @SQLQuery = @SQLQuery + ' AND user_id_fk = ' + @owner +'' If @status Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Status_FK = '+ @status + '' If @team Is Not Null Set @SQLQuery = @SQLQuery + ' AND user_group_fk = '+ @team + '' If @lineofbusiness Is Not Null Set @SQLQuery = @SQLQuery + ' AND Line_Of_Business_FK = '+ @lineofbusiness + '' If @requestor Is Not Null Set @SQLQuery = @SQLQuery + ' AND Work_Request_Requestor like %' + @requestor + '%' Execute FilterProcMain @SQLQuery If @@ERROR 0 GoTo ErrorHandler Set NoCount OFF Return(0) ErrorHandler: Return(@@ERROR) GO
Error converting data type varchar to int error?   (205 Views)
l, hope you can help. got this error: Microsoft OLE DB Provider for SQL Server (0x80040E07) Error converting data type varchar to int. /admin/stores/index.asp, line 520 but no idea what to do what it is line 520 is below: Code: Sub UpdateZoneDisplay() sSQL = "EXEC spUpdateZones " & iStoreID & ",'" & sfk_zoneID & "," & sfk_storeID & "'" SET oRS = oDB.execute(sSQL) istoreID = oRS("storeID") End Sub any help would be brill kind regards MG
Syntax error converting datetime from character string   (241 Views)
I urgently need some help I have written a .NET windows service that processes some XML's and updates some fields in an SQL Server 2000 DB. It works ok on my workstation. But at the Client's site it causes an exception with the following error: "Syntax error converting datetime from character string" I heard it might have something to do with regional settings for the workstation and SQL Server Language Settings but im not sure. Could somebody please help with this issure!
Error converting data type datetime to smalldatetime   (248 Views)
In my table "end_date" is smalldatetime data type, when I use in edit.asp, then use endDate = formatDateTime(Request.Form("end_date"),vbshortdat e) in edit_submit.asp, It will give me an error of " Microsoft OLE DB Provider for SQL Server error '80040e07' Error converting data type of datetime to smalldatetime", but if I directly use endDate=rs("end_date") in the edit_submit.asp, it works fine, I cannot figure out why, out with this
Syntax error converting datetime from character string   (249 Views)
hi I'm having problem with sql query i'll explain u what i'm trying to do when a user sends enquiry for a product it goes to table propertyhittable where hitid the index. if the receiver replies to the enquiry, his reply goes to table mailbox and all subsequent mail correspondence goes to mailbox. now on my Inbox Page i have to dispaly all the enquiries like gmail (each mail clustered with all past correspondence) I used the following query: select HitId,hitid as mailid,Login_Id,Star,Subject,Prop_Code,visitdate, replydate,mailread from propertyhittable where login_id'71727' and deleted=0 and prop_code in (select prop_code from resi_sell where login_id='71727' union select prop_code from comm_sell where login_id='71727' union select prop_code from land_sell where login_id='71727') union (select mb1.Login_Id,mb1.Star,mb1.Subject,mb1.Prop_Code,mb 1.maildate as visitdate, mb1.maildate as replydate,mb1.mailread,mb2.hitid,mb2.mailid from (select hitid,max(mailid) as mailid from mailbox group by hitid) as mb2 inner join mailbox as mb1 on mb1.hitid = mb2.hitid and mb2.mailid = mb1.mailid where login_id71727 and deleted=0 and mb2.hitid in (select hitid from propertyhittable where login_id='71727') ) order by replydate desc i got Server: Msg 241, Level 16, State 1, Line 1 Syntax error converting datetime from character string.