I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank. Thanks in advance for your reply.
Does anyone know how I can, using SQL and/or T-SQL syntax (although I could do a CLR in C# too..), export a SQL table from SQL Server 2005 to a new Visual FoxPro 9 DBF (table)? The DBF would need to be created on the fly too because this will have to basically be able to export any SQL table...
Thanks ahead of time for any and all assistance...
I know that the DTS Wizard is supposed to be able to handle heterognous data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT? HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Using 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.
How can i create a linked server for Microsoft Visual Foxpro databases ?
I'm using Microsoft.ACE.OLEDB.12.0 driver.
I success create a linked server, and can browse all tables from linked server connections, but why when query data using
SELECT * FROM OPENQUERY(PIP_TEST,'select * from tbctrl')
It getting error message
Cannot process the object "select * from tbctrl". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "pip_test" indicates that either the object has no columns or the current user does not have permissions on that object.
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
Hi anyone can tell me how to create a table from an existing table in MSSQL. In oracle we issue " create table <tablea> as select * from <tableb>; " ? Thanks in Advance.
Hi there, Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
I have a query in which I would like to pivot the resultsI presently have my results displaying something like this. OrderNumber Product OrderQuantity--------------- --------------- ----------------------0608 Prod1 30608 Prod2 120608 Prod3 2 What I am after is for the results to display something like this.OrderNumber Prod1 Prod2 Prod3 --------------- --------- --------- --------- 0608 3 12 2 This is using SQL Server ver 8.0
In order to update a MSSQL table, named [Table_1]. The procedure is: (@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1: UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id Method 2 IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id Method 3: DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.
Currently, I would like to do the following (in abstract terms):
declare tmp record select column1 from tableA into tmp for each entry from above selection do insert into tableB values (tmp[column1], 0, 0, 0)
I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:
CREATE FUNCTION dbo.newDay (@mDate datetime) RETURNS int AS BEGIN DECLARE @id int DECLARE item_cursor CURSOR FOR SELECT id FROM tblKitchenCat OPEN item_cursor FETCH NEXT FROM item_cursor INTO @id WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate) FETCH NEXT FROM item_cursor INTO @id END CLOSE item_cursor DEALLOCATE item_cursor RETURN 0 END GO
I get a syntax error next to AS... what is it?
Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.
In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?
I am Using MSSQL and ASP Classic for this project. Let me explain my problem, i have two tables:
Users ID Username
ChangeLog ID Description UserID DateChanged
The Users table will contain one record for each user in the system.
The ChangeLog table can contain any number (including 0) of rows relating to each user, when a change to the users account is made, and entry is put into the ChangeLog table.
The object of my report is to display a list of all users in the system and display the date and description of the last change made to the user.
So far i have written this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM Users LEFT OUTER JOIN ChangeLog ON (ChangeLog.UserID = Users.ID) ORDER BY ChangeLog.DateChanged DESC
However, if a user has got more than one change against it then a row will be returned for each change instead of just showing the latest change. So i then tried this:
Code:
SELECT Users.ID, Users.Name, ChangeLog.Description, ChangeLog.DateChanged FROM ChangeLog LEFT OUTER JOIN Users ON (Users.ID = ChangeLog.UserID) ORDER BY ChangeLog.DateChanged DESC
The problem with this one? It only shows users that have actually had a change made to them, and the report *needs* to display ALL users regardless of wether they have been changed or not.
I'd appretiate any help you can give me at all, thanks in advance!
(i am not sure which forum to post this too) Hi, I imported a table onto my host's server, table_Login which has username password, and userID. userID is Primary Key & identity. When I had my code do insert of new user, I was getting error the Login.userID doesn't allow null values. Which shouldn't have mattered because userID is an identity. After making a diagram from Enterprise Manager, I noticed that I have two Login tables (although only one shows in Enterprise Manager's "Table" node view. The second table, which had none of the constraints that my table of the same name which is listed under the "Table" node does; appears to be the table which is being written to by my code. I can't find this table, Login(dbo), through Enterprise Manager, nor am i sure how to access it through Query Analyzer. Needless to say, I don't like this arrangement. and well tech support for this host seems to leave a lot to be desired. Anyone know what needs to be done so that I can fix it or fight with tech support to convince them that they should?
I have the table creation script and insret record script. This is MySQl Format. What changes I have to do so can I run this scripts into SQL Server 2000. If any body has successfully done it then please tell me the procedure.
Hi,I'm doing an Orcle to SQL port.Simply all I want to do is run a script / DTS package that reads Oraclesequences value and writes the current value of it in to a MSSQL table - ofSequenceName, ValueCan this be done ? If so how ? (Please provide example)Steve
Code Snippet CREATE PROCEDURE au_info @var text AS DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc OUTPUT, @var PRINT CAST(@var as varchar(8000)) -- Use OPENXML to provide rowset consisting of customer data. --INSERT Customers SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers') -- WITH Customers GO
Hi,how to:using dts convert row in table to column.i have table:col1,col2b1 , 1b2 , 2b1 , 4b3 , 3b2 , 5and i want using dts convert/rewrite this table to another table:b1,b2,b31,null,nullnull,2,null4,null,nullnull,null,3null,5,null
Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName ( column_name data_type ) But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey? Hope really for help, because this is a importen thing, and I cant find the answer? :S
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer column Name = text column eBay = number column PriceGrabber = number column Amazon = number
So, any idea how I can update my Products Table with this?
How can I query (using TSQL) the data space used by a table in SQL Server 2005?
This is possible using SQL Serve Management studio. I can right click on table name and check the proeprties. But I want to write a TSQL script to check disk space used by all the individual tables in the database. How can I do that?
