I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.
These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.
It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.
What are the options available for me to be able to query and differentiate the 'deleted' records ?
Knifflige Frage zu Datenbanken:Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und GMID.Soweit ok.Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalischgelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHTangegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz nichtgelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung drin.Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrereDatensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen vonGELOESCHT in den PK geht theoretisch, ist aber gefährlich (daFließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).Hat jemand eine Idee?SWE@KP
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?
Here's my predicament. I changed the filter on an article within a subscription, but not in a meaningful way. I just added "database.dbo." before a column name, but now my subscription is flagged for reinitialization, and won't replicate any transactions unless I start over with a new snapshot. I don't want that to happen. How can I reset the article properties somewhere to get it back the way it was and continue replicating transactions.
(I needed to add "database.dbo" so that another job that looks at the filter info would point to the correct database. Next time maybe I'll just modify the table that stores the filter info)
The reason I don't want to snapshot from the beginning is that I have procedures on the subscribing server that fire triggers from the transactions, so all the jobs "downstream" would get all out of sync.
First of all, let me apoligize if this is a stupid question, but just a few days ago I decided to change my FE/BE database from Ms Access to SQL Server Express. Now I have a FE in ACC2002 and a BE in SQL SERVER (the two connected via ODBC). Anyway, this is my question:
When everything was in Access I had a form retreiving a series of records based on a filter - for understanding purpose let's say that a field must have been 'Not Null'. The operator then filled that field with data. With Access everything was static, and the operator could see all the records, the ones already changed and the ones not changed. With SQL now: everytime the operator fills in the field, the record changes to #Deleted. I assume that that is happening because the query - someway - is "alive" and is "filtering data alive"... or is updating records instantly. Just to make things more clear: is the record changed immediately so it is not considered part of the filtered data by the query, thus being change to #Deleted? What could I do to prevent this?
Is there a way to tell who or what process deleted a record in a SQL table.I know you can setup triggers or Profiler - but is there a way to see how arecord was deleted if you DO NOT have a trigger or Profiler already running?This is SQL 2000 Standard, sp3a with default options and configuration.Thanks
I am using a drop down list box to select values from an SQL 2000 DB. I have put a record into the table which says"...Select Item"I used the dots so that It will apear at the top of the list and therefore display at startup. I also have a gridview to allow users to edit and delete from the same table.I am worried that a user may accidently delete the "...Select Item" row from the table.Can anyone think of a way round this? Is it possible with SQL Server 2000 to stop a single record from being deleted?Any help would be most appreciated.ThanksPaul
I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
First, let me say that I have already reviewed information posted by experts within the chat area.
Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.
Here is what I have tried in the Query Analyzer, but to no avail:
For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:
use master go sp_detach_db 'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'
I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.
use master go sp_attach_db 'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'
Message: Server: Msg 1801, Level 16, State 3, Line 1 Database 'qarun_diamond_48_brett' already exists.
If I use a different db name I get the following error: Server: Msg 5105, Level 16, State 13, Line 1 Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.
use master go sp_attach_db 'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'
Message: Server: Msg 1801, Level 16, State 3, Line 1 Database 'qarun_diamond_48_brett' already exists.
I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.
Hi All In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba
I have detached the database via sp_detach_db in a job, (I was trying to use a job to detach the database, then make copies of the mdf file, then attach the databases as different copies, somehow the job failed due to file locked) I tried to delete the physical files, but they were still locked. I tried several times, two of them I was able to delete the mdf files, but not the ldf file. The other one both mdf file and ldf file can not be deleted. I was not able to see the database via Management studio.
Just found that my tempdb is always full whenever I run a query against a large database. Could please any experts here give me any advices on what is tempdb database used for and how to determine what files can be deleted from it?
I am looking forward to hearing from you and thanks a lot in advance.
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin] GO IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL DROP PROC usp_DeleteBackup GO
Hi guys! I've made a simple INSERT form write some records in a database.... then I need to associate to every record a PDF FILE or a WORD FILE.. so who (a user) insert a record should upload a file .... How could associate the record to the file that an user upload? classical article pubblication problem...do you know some tutorial?
Is there a way to manipulate a date variable into a specific month and day?? For example, I want whatever date the user will enter to be changed to June 30 of the following year. So, if the variable @date is 12/12/1999, I want to change it to 6/30/2000. If the date is 2/1/2001, I want to make it 6/30/2002
I can't use DateAdd because you need to know how many months to add or days to add. Basically, how do I retain the month and day while changing the year the year based on a dynamic field. What I need is a DateChange function that is DateChange(mm, @month, @date) ? Is there something like that?
So I'm querying from a database where one of the fields for each entry is a city. I would like to count the ones in each city. So I'm counting and grouping on city. Unfortunately, the data is not very consistent. Some are listed as Miami, FL and others simply as Miami. I don't need the state so I can just drop it, but I can't figure out how. I was using charindex and substring, but can't get it to work because if there is no ',' then the charindex is zero and substring doesn't work correctly. Any help would be greatly appreciated.
Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..
I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.
I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..
Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..
Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..
I posted a thread here: http://forums.devshed.com/showthread.php?p=1061405 but was told it might be more useful for me to post something here... should have thought about it to begin with!
The picture is in that thread so do please look at it! http://forums.devshed.com/attachmen...tachmentid=7431 should be the URL.
As you see the top 3 records start and finish at the same time, as do the last 3 records. This can then also be repeated for the next day etc etc. What I want to happen is that the top 3 courses to appear on a single line and the 2nd set of 3 to appear on a single line also.
The main SQL to retrieve the records is as follows: Code:
sqlqry = "SELECT * FROM t_sessions a INNER JOIN t_session_times b ON a.session_id = b.session_ID" sqlqry = sqlqry & " WHERE right(left(convert(varchar,a.session_date,113),11),8)='"+displaym +"'" sqlqry = sqlqry & " AND a.session_supplier_id=61" sqlqry = sqlqry & " AND a.session_status ='A'" sqlqry = sqlqry & " ORDER BY a.session_date, b.session_start, b.session_end"
then while this recordset hasn't come to an eof(end of file) I execute: Code:
query = "SELECT course_name,outline_name" query = query & " FROM t_course_ref" query = query & " LEFT JOIN t_it_outlines ON t_course_ref.course_id=t_it_outlines.course_id" query = query & " WHERE t_course_ref.course_id="+cstr(rs.fields(1).value)
I remember I was able to view, add, alter table on the mdf in either sql express 2005 or in vb.net 2005. But right now I can't do either, what do I have to to change
Hi, i want to programmatically manipulate the property 'Name' of a ControlParameter inside a InsertParameters tag. This the aspx code: ------------------ <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<% ......... %>" InsertCommand="INSERT INTO .......)" <InsertParameters> <asp:ControlParameter Name="myname" ControlID="na" PropertyName="text" /> </InsertParameters> </asp:SqlDataSource> code-behind: ------------ Dim a As String a = SqlDataSource1.InsertParameters.Item(0).ToString but 'm stuck here Thanks for help Tartuffe
hi,I have a page Price List with GridView to display only two columns of a table (Product name & price). Page size=20. I use SqlDataSource for it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas? Thanks.
what re the sql functions to manipulate numbers, decimals, dates.. 1/ like if I have 123443.78654 I want to display just 2 decimals : 123443.78 or 3 decimals .. 2/ also if I want to have bankers rounding 3/ how about dates conversion : from string to date type and vice versa and adding dates, substracting dates, getting the day, the month.... what re the SQL functions to do that pls
Good morning, everyone. Maybe I'm just having a brain fart, but I'm totally new to SSIS (I dabbled very little with DTS in the 2000 days) and cannot for the love of me figure out how to achieve my goal with it:
My company needs to extract data from a variety of sources; tab-delim files, Access databases, other SQL tables and the like. I know how to do this. However, I need to perform data manipulation queries on this data before I place them into SQL tables, as I want to avoid having umpteen temporary tables that I'll need to add checking for. My predecessor did everything in Access, and has a 76-step process (yikes!) that basically will grab all the data, do some minor manipulation, and plop it into a temp table (this is still in Access, not SQL), then repeat the same thing dozens of times.
To give you an example, here's a sample of what I want to do:
- Extract several columns of data from a tab-delimited file on the local drive. This I know how to do already. - Perform some data cleanup and manipulation functions on this data (specifically, obtain the lowest value out of three columns, with the added caveat that I make sure it's not zero to begin with). I have the SQL code for this already written. - Store the results of this data somewhere, so I can pull it and apply additional logic to it; for example, take the lowest value I've retrieved, and update the corresponding column in another database table with it.
Basically, is there any way to avoid the use of dozens of temp tables? There's a lot of data which needs to be pulled in, manipulated, and spit back out to be manipulated by something else a little later on, and the way my predecessor did it was, as I said, to use dozens of Access "Make Table" queries for every minor thing. It's not a big deal if I need to do it, just I'm trying to consolidate the steps needed, as the old way is very inefficient. I've been at this job a month and I'm still trying to wade through all of his queries to discover just what they do, and look into combining several of them.
Forgive the slightly newbish question, but as I stated I've not worked with SSIS really. I'm in the process of learning it better, as I'm sure it can fit our needs.
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB "6abc" "0006" "457def" "0457" "7232hij" "7232"
I have implemented a temporary solution using a CASE statement: SELECT ColA, ColB = CASE WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4)) WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3)) WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2)) WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1)) ELSE '' END FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4) DECLARE @num int SET @ColBstring = '' SET num = 1; -- Get the numeric prefix from ColumnA's string WHILE(isnumeric(substring(colA, 1, num)) = 1) @ColBstring = (substring(colA, 1, num) num = num + 1
-- Prepend the ColumnB string with zeros WHILE(LEN(@ColBstring) < 4) @ColBstring = '0' + @ColBstring
After generating one of my reports, I process the XML output through an XSLT stylesheet and export it to a text file. The issue is that after the export, the generated output text file begins with the special Byte-Order-Mark marker "EF BB BF" standard to Unicode files encoded in UTF-8, UTF-16 or UTF-32. I have explicitly set the attributes of the xsl output element to <xsl:output encoding="us-ascii" media-type="text/plain" method="text">, but it seems as though those are ignored when the output file is written. I cannot have these characters, because I am generating a fixed-width file for input into a legacy system.
Any suggestions or thoughts on what is causing the BOM to be written to my file, even though I have set the encoding to be different than UTF-8?
