Exporting Simple English Data Into FoxPro 2.6 Dbf
Mar 9, 2008Could any one help me in
Writing code/query to export simple English text data from MSSQL Sever2000 to FoxPro2.6 dbf
aamir_net68@yahoo.com
ADVERTISEMENT
Error Exporting FoxPro 2.5 DOS Table To SQL Server
Mar 1, 2006Using the Import/Export Data Wizard, I'm trying to export a FoxPro 2.5 DOS (as dBase III) table of 15,000 records to SQL Server 2000. I keep getting this error message:
Insert Error, Column 32 ('PROG_START',DBTYPE_DBTIMESTAMP), Status 6: Data Overflow.
Invalid character value for cast specification.
I have SQL Server create the table each time I run the wizard. The new table allows NULLS in this column and I made sure to overwrite the empty date fields in the FoxPro table with blanks to make sure it would result in NULL. Originally SQL Server tried to put this as SMALL DATETIME, but when I got the message earlier, I changed it to DATETIME.
Any suggestions?
Deletion Of Data Other Than English Language
May 23, 2006Anil Chauhan writes "Hello experts,
I have a table in sql server 2000 which has over 94000 records.
I have to delete a record from table ,which record having a language other than english .
I need to clean the table by removing all the data which are in other language .
My main table has 12 fields .
Thanks in advance."
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
Mar 10, 2005I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
Data Mining Add-Ins Doesn't Work With Non-English Regional Settings
Dec 23, 2006I tried Data Mining Add-Ins for Office 2007 - CTP December 2006.
Test settings: Windows XP SP2 english with Italian regional settings, Office 2007 english (RTM), SQL Server 2005 Developer (with SP2 CTP Dec06) and Data Mining Add-ins for Office 2007 (CTP Dec06).
If I keep regional settings in Italian, I get error like this:
Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
If I change regional settings to English, the Add-in works.
I found this description as the possible cause of the problem: http://msdn2.microsoft.com/en-us/library/ms178780(vs.80).aspx - if this is the issue, it would be necessary to change the ExcelLocale1033Attribute on the component.
Is there another workaround other than to install the Office 2007 MUI?
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Transfer Data From Foxpro To Sql Server
Jun 11, 1999i have an old database in foxpro. The table in foxpro now has been broken into more than tables in sql server 6.5 . how do i append the data to sql server database to the respective tables from the foxpro database.
vineet
Foxpro To Sql Server Data Conversion
Apr 25, 2007Hi everyone,
I have worked soley with Sybase for almost ten years! Now I have been tasked with converting a bunch of data currently in a FoxPro database to a Microsoft SQL Server database. Short of writing some routines myself (which I don't mind doing), is there a shortcut for doing this? Any specifics would be great as it seems this could be mind boggling as far as dates go and so many other things!
Thanks so much!
Rachel
Query Data From The Linked Foxpro Database
Aug 2, 2007hello,guys,
my question is :
how do I query data from the linked foxpro database?
more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.
sample:
select * from fox..sysu
then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'
I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.
Thank you very much
How To Access Visual FoxPro Data From SQL Server ?
Jul 23, 2005I'm trying to access Visual Fox Pro Data from Sql Server 2000, Cananyone have any Idea ?Thanks
View 1 Replies View RelatedInsert Data To Database Foxpro Using Open Query
Feb 24, 2014Insert data to database foxpro using open query. I already create connection like below
@server = 'EXIMBIL_LINK',
@provider = 'MSDASQL',
@srvproduct = '',
@provstr = 'Driver={Microsoft Visual FoxPro Driver}; UID=;SourceDB=D: raining_testdata;SourceType=DBF ;Exclusive=No;BackgroundFetch=Yes'
when i try to show table van with command :
select * from openquery(EXIMBIL_LINK,'select * from van')
It show query running well but when i try to insert new row to table van with command :
INSERT into OpenQuery(EXIMBIL_LINK, 'SELECT c_user_id,c_user_name,full_name from van')
VALUES('1000007668','IDMTEST2','IDMTEST2')
throws error like this : Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done
How To Use INSERT INTO.... SELECT FROM Query To Export MS SQL Data In FoxPro 2.6 DBF
Nov 28, 2006Hi all,
This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.
Thanks in Advance.
Regards,
Rajeev Vandakar
Foxpro .net Odbc Data Provider Misidentifying Strings As Unicode
Nov 23, 2006Since my foxpro OLE driver has been rendered useless by service pack 1 for sql server 2005 I am forced to use the .net data provider for odbc.
I am importing a number of tables.. each time I add the DataReader Source to the dataflow and connected it to the OLE DB Destination I get a load of the good old "cannot convert between unicode and non-unicode string data types" errors...
So I'm having to do derived column transforms, for each and every column that it coughs up on.
Using expressions like (DT_STR,30,1252)receivedby to convert the "recievedby" column to a DT_STR,
Some of these tables have 100 string columns.. so I'm getting a bit sick of the drudgery of adding all these derivations...
Is there any way to tell this provider to stop deciding that the strings in the foxpro tables are unicode?
Thanks
PJ
EXTRACTING DATA FROM FOXPRO/ACCESS 2000 INT SQL SERVER 2005
Mar 5, 2007Dear Sir/Madam,
I have some data which is stores in Foxpro dbf files & Access 200 MDB file. Can i extract import these files into SQL Server 2005
Synchronizing Foxpro .DBFs Data With SQL Server Tables (realtime)????
Mar 16, 2008
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
Thanks for any and all assistance.....
Dave
Problem Loading Data From Foxpro Tables Under Multiple Folders
Oct 17, 2007
Hi,
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Dim conObj,DSNGosfbill,comObj,objRs,HostServer
Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil
Dim fso, folderObj,subFolderList,dFolderObj
Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033
Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB
Set conObj = CreateObject("ADODB.Connection")
HostServer =DTSGlobalVariables("gvServer").Value
'DSNGosfbill ="DRIVER={SQL Server};SERVER=" & HostServer & ";DATABASE=GOSFBILL"
DSNGosfbill ="DRIVER={SQL Server};SERVER=" & HostServer & ";UID=syntelhum;PWD=syntel123;DATABASE=GOSFBILL"
conObj.open DSNGosfbill
sFolder =DTSGlobalVariables("gvSSIUnzipPath").Value
CheckFile =DTSGlobalVariables("gvSSIBatchPath").Value
set comObj=CreateObject ("adodb.command")
set comObj.ActiveConnection =conObj
Function Main()
Dim Dir_Name,DirFlag
Dir_Name = ""
DirFlag = "N"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then
Else
Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Set folderObj = fso.GetFolder(sFolder)
Set subFolderList = folderObj.SubFolders
For Each subFolderOccur in subFolderList
DirFlag = "Y"
Dir_Name = subFolderOccur.Name
Call Process_Dir(1,subFolderOccur.Name)
Next
If DirFlag = "N" Then
Details = "***** No directories to process in SSI UNZIP folder*****"
Call Write_Log
End If
If DirFlag = "Y" Then
Call Process_Dir(2,Dir_Name)
If objRs.Eof Then
Details = "***** No directories to process in SSI UNZIP folder*****"
Call Write_Log
End If
While not objRs.EOF
set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name"))
Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****"
Call Write_Log
Call Update_Process_Flag("L",objRs("zip_file_name"))
'*******Execute the package for each directory****************'
'********* Call the Package**************'
Set objPackage = CreateObject("DTS.Package")
Set objPackage_1 = CreateObject("DTS.Package")
'objPackage.LoadFromSQLServer HostServer, , ,256, , , , "GOSFBILL_SSI_STAGING_LOAD"
objPackage.LoadFromSQLServer HostServer,"syntelhum","syntel123", , , , , "GOSFBILL_SSI_STAGING_LOAD"
'objPackage_1.LoadFromSQLServer HostServer, , ,256, , , , "GOSFBILL_SSI_Update_FileSource"
objPackage_1.LoadFromSQLServer HostServer,"syntelhum","syntel123", , , , , "GOSFBILL_SSI_Update_FileSource"
Set ConnObj_001 = objPackage.Connections("SSIPATH001")
ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002")
ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004")
ConnObj_004.DataSource = sFileFolder
Set ConnObj_031 = objPackage.Connections("SSIPATH031")
ConnObj_031.DataSource = sFileFolder
Set ConnObj_032 = objPackage.Connections("SSIPATH032")
ConnObj_032.DataSource = sFileFolder
Set ConnObj_033 = objPackage.Connections("SSIPATH033")
ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB")
ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV")
ConnObj_HProv.DataSource = sFileFolder
Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS")
ConnObj_Hids.DataSource = sFileFolder
Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES")
ConnObj_HCodes.DataSource = sFileFolder
Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN")
ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB")
ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute
For Each oStep In objPackage.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Next
Call Update_Process_Flag("X",objRs("zip_file_name"))
objPackage_1.GlobalVariables("gFileSource").Value = objRs("zip_file_name")
objPackage_1.Execute
For Each oStep_1 In objPackage_1.Steps
If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Next
'********************************************'
Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****"
Call Write_Log
objPackage.Uninitialize
objPackage_1.Uninitialize
Set objPackage = Nothing
Set objPackage_1 = Nothing
sFileFolder = ""
sFileFolderDBF = ""
sFileFolderFPT = ""
objRs.MoveNext
Wend
objRs.Close
End If
Call Close_Conn
Main = DTSTaskExecResult_Success
End Function
Sub Process_Dir (Para_cntl,Dir_Name)
comObj.CommandText ="dbo.Usp_Process_Dir"
comObj.commandtype = 4
comobj.parameters.Refresh
comobj.parameters("@Para_Cntl")= para_cntl
comobj.parameters("@Dir_Nm")= Dir_Name
comobj.parameters("@File_Type")= "SSI"
If (Para_Cntl = 1)Then
comObj.Execute()
Else If Para_Cntl = 2 Then
Set objRs = comObj.Execute()
End If
End If
End Sub
Sub Update_Process_Flag(P_Flag,Dir_Name)
comObj.CommandText ="dbo.Usp_Process_Flag"
comObj.commandtype = 4
comObj.parameters.Refresh
comObj.parameters("@Process_Flag")= P_Flag
comobj.parameters("@Dir_Nm")= Dir_Name
comObj.Execute()
End Sub
Sub Write_Log
comObj.CommandText ="dbo.usp_etl_write_log"
comObj.commandtype = 4
comobj.parameters.Refresh
comobj.parameters("@Text")= Details
Comobj.parameters("@NDC_SSI_IND")= "SSI"
Comobj.parameters("@Process_Stage")= "Staging"
comObj.Execute()
End Sub
Sub Close_Conn
Set comObj = Nothing
Set objRs = Nothing
conObj.Close
Set conObj = Nothing
Set fso = Nothing
Set folderObj = Nothing
Set subFolderList = Nothing
End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
Thanks in advance
Gijo
SQL Triggers: Transfer Data From SQL Server 2000 To Visual FoxPro DBase
Jul 23, 2005Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
View 1 Replies View RelatedDTS Works, Job Fails!(data Source Foxpro And Destination SQL Server 2000
Sep 26, 2006The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0
View 1 Replies View RelatedExport Wizard Disturb The Order Of Data While Exporting Data To Acess 2003 From SQL Server 2005
Feb 24, 2007
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
Exporting Data - Unable To Append All Data To The Table
Jun 28, 2012I have a sql server 2008 backend with an Access 2007 frontend database. Each time I export a query I get the following error:
Code:
Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables. Do you want to proceed anyway?
I don't know what if anything is actually missing because of the amount of data is more thant 6000 records. It seems everything exported but I would have to comb through the data to be sure.
Exporting Data From A Merge Join From One Data Flow To Another
Mar 1, 2006Hi,
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
Transfer Data From A Foxpro Database File To Sql Server Database
Jun 11, 1999i have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5.
vineet
EXPORTING DATA
Mar 21, 2001hi all,
I am getting problem while importing data from excel file.
I am bale to do the same with flat files. But when i do with excel files
its throwing error : format error.
pls help me in this regard.
and How to export data into export files from query analyser..
Exporting Data
Feb 1, 2005Hello everybody,
I was wondering if there is a way to export and recode data at the same time with SQL.
For example I have gender information coded as 1 or 2 in my table and I need to upload the information to a different application that needs M or F. Is there a way to export to a new table and recode at the same time ?
I'm still pretty new to it.
Thank you.
-Seb
Exporting Data
Apr 20, 2004I would like to export data to a excel file using a stored procedure. I'm not sure how to go about this or if it is even possible. Can someone point me to a link to show me how to do this?
View 1 Replies View RelatedExporting Data
Jan 17, 2007Hi all,
I recently found out that there is no easy way to distribute a MSSQL 2005 database to MSSQL Server 2000. Most forums that I have read say to use the DTS facility to import the data into the SQL 2000, however this is not an option (for a variety of reason I won't go into).
The next best option in my mind is to script the entire database, including the data into one or a few script files then run them on the SQL 2000 server to recreate the database. Unfortunately, exporting the data, what I thought would be a fundamental feature, isn't part of SQL 2005.
So does anybody know of a good (free) scripting program that will allow me to export the entire database from a server? I've tried:
- Free program from the CodeProject.com (program dies when there's more than 5000 lines) http://www.codeproject.com/dotnet/ScriptDatabase.asp
-SQL Scripter www.sqlscripter.com (Doesn't script table which don't have primary keys, and produces a script for each object instead of just a single file)
I'd probably like a program which only creates a single script, as the database has over 200 tables and I don't want to have to go through this process everytime I need to distribute the DB (which will be often).
The only other option I can think of is a program which converts an SQL 2005 backup to a 2000 version.
Thanks for your help!
Exporting Data
Jul 5, 2007Guys,
I'm trying to export data from SQL. Can anyone help. I have commented my script below and have managed to create a table, insert using 'bulk insert' - now I want to export the data out. I'm getting an error message saying 'cannot use the output option when passing to a stored procedure'.
comment
-----------
drop table for re-runs !
-----------
drop table JET
go
comment
------------
create a table to match the .txt file importing
----------
create table JET
( [USER] char( 25),
[DESC] char (20),
SYSDATE datetime,
SYSVALUE MONEY ,
POSTDATE datetime,
POSTVALUE MONEY,
GLCODE CHAR (20)
)
comment
---------------
import using pipe delimited .txt file
--------------
bulk insert JET from 'D:Documents and SettingsmpeetSQL Test.txt'
with
(
fieldterminator = '|',
firstrow = 2
)
comment
-------
check results
-------
select *
from JET
NEW BATCH
bcp JET out 'D:Documents and SettingsmpeetSQL Testexport.txt'
I want extract the data to a .txt file would anyone know the syntax?
Cheers
Michael
Exporting Data
Sep 12, 2007I am using sql server 2005
I want export data from Excel to new SQL Server table,
select *
into mytable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:sample1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
But i am getting following error message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Exporting Data From SQL
Jan 17, 2008I'm very new to SQL - please bear with me.
1. I need to be able to export data from a database to an excel sheet (I have written the query and tested it works, so I don't need to know this stage :^)). What is the best way of doing this? Could you send me a link of a howto doc?
2. Once the data is export to excel, it then needs to be manipulated so that a final sheet is created. During the manipulation I need to add the values of certain columns to give me an end result.
Should I use excel to manipulate the data or can sql add the values of certain columns and then export to an excel sheet?
What's the best way please.
Many thanks
Exporting Complex Data To CSV
May 12, 2007I want to export data to CSV that is nested.
eg.Poll 1,val1,val2
Question1,Q1val1,Q1val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1
AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2
Question2,Q2val1,Q2val2
AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1
AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
AnswerQ2A3,Q2A3val1,Q2A3val2, TallyQ2A3
Best practices-wise, what should my data end up like?
Since it's nested surely it can't be simply a list of identically-formatted rows, one for each answer:
Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,Poll1,val1,val2,Question1,Q1val1,Q1val2, AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,Poll1,val1,val2,Question2,Q2val1,Q2val2, AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
Do I end up with three separate tables?
Poll1,val1,val2
Question1,Q1val1,Q1val2, Question2,Q2val1,Q2val2
AnswerQ1A1,Q1A1val1,Q1A1val2, TallyQ1A1,AnswerQ1A2,Q1A2val1,Q1A2val2, TallyQ1A2,AnswerQ2A1,Q2A1val1,Q2A1val2, TallyQ2A1,AnswerQ2A2,Q2A2val1,Q2A2val2, TallyQ2A2
If that's so, do I simply concatenate them in the final file? Or do I have three separate files? And do I label them uniquely such as Poll1Main, Poll1Questions and Poll1Answers so that I can export several polls side-by-side without overwriting each other?
Exporting Data Into .CSV Format
Jan 17, 2008Hi
I am trying to export data from SQL which is ok and I can do that part. However from the result of the query I need to add the values of certain columns. Because I'm a newbie to SQL I'll explain and try and show images as best as I can as we go....
So far I have written a query
select Stockitem.code, Warehouseitem.ConfirmedQtyInStock, Warehouseitem.UnConfirmedQtyInStock, Warehouseitem.QuantityAllocatedStock, Warehouseitem.QuantityAllocatedBOM, Warehouseitem.QuantityAllocatedSOP, WarehouseItem.WarehouseIDfrom WarehouseItem INNER JOIN StockItem ONWarehouseItem.ItemID = Stockitem.itemidINNER JOIN BinItem ONBinitem.itemid = stockitem.itemidwhere WarehouseItem.WarehouseID = '3403' AND BinItem.BinName LIKE 'S%' AND BinItem.BinName <> 'S' OR BinItem.BinName LIKE 'T%' AND BinItem.BinName <> 'T'order by stockitem.code
This returns the following results....
Code ConfirmedQtyInStock UnConfirmedQtyInStock QuantityAllocatedStock QuantityAllocatedBOM QuantityAllocatedSOP WarehouseID
12345 96.00000 .00000 .00000 3.00000 13.00000 3403
Now I am trying to find out the amount of freestock available for everything with the WarehouseID 3403. Therefore in the example above to be able to workout how many '12345' (Code) we have in stock, the equation used to find out the free stock available is as follows....
Free Stock = (ConfirmedQtyInStock + UnConfirmedQtyInStock) – (QuantityAllocatedStock+ QuantityAllocatedBOM + QuantityAllocatedSOP)
Therefore if I manually use the equation above the amount of freestock available =
Free Stock = (96+0) - (0+3+13) = 80 (free stock).
Then once I have the equation to be able to output the 'freestock' quantity, I need to export the results into an excel spreadsheet with the following layout...
ColumnA
Code,FreeStock
12345,80
Hope I've explained this in a good manner, any help would be gratefully received.
Simba
Exporting Data, Urgent!!
Jul 24, 2001Hi All,
I need to export data from one database to another and I want to automate this process. The problem is I only have to select specific tables of data to be exported. Any help will be greatly appreciated
Thank you!!
Exporting Data From A Query To A Csv Or Xls Via T-sql
Jan 12, 2001Is there any way in SQL 7/2000 to export a query result to a csv or xls via t-sql?
Thanks a lot for your help,
Yelena
Importing && Exporting Data
Dec 21, 2004may i know where to find more detail like how to importing and exporting data in ms sql server.?
Hope can get the more detail about it and also teach me step by step. (hope can include the photo).