I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
Hello, this seems simple but I've been banging my head a while.
I have a data field that is formated like this: NYT/2000-Subways/7510-Electronics Mtc/7540-Data Svcs.
I need to pull out the string after the second / and before the third / in this case the value is 7510-Electronics Mtc but it does change.
I have this function that returns the first value (NYT):
USE [Data_Warehouse] GO /****** Object: UserDefinedFunction [dbo].[Get_Dept] Script Date: 04/22/2008 09:22:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
-- Provide the phrase before (ex. 'Worklog Related to'), the field searched (ex. UPDATE_ACTION), and -- the phrase after (ex. ']'). -- This function returns the string found after any phrase and before any phrase, within any field searched.
ALTER FUNCTION [dbo].[Get_Dept](@in_Phrase_Before varchar(250),@in_Search_Field varchar(250)) RETURNS varchar(250) AS BEGIN Declare @str_String As varchar(250) Declare @tmp_String as varchar(250) Set @str_String = null -- to clear the variable buffer
Set @in_Phrase_Before=char(37)+(LTrim(Rtrim(@in_Phrase_Before)))+char(37) -- if 'Null', all strings will be returned
If Left(@in_Search_Field,4) = 'NYT/' BEGIN Set @tmp_String = Right(@in_Search_Field,(len(@in_Search_Field) - 4 ) ) Set @in_Search_Field = @tmp_String END
If (PatIndex(@in_Phrase_Before, @in_Search_Field)) > 0 --Checks to see if the phrase before is in the search field Set @str_String=SubString(@in_Search_Field,1, (PatIndex(@in_Phrase_Before, @in_Search_Field)-1) )
I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?
I am looking forward to hearing from you and thanks a lot in advance for your advices and help.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
I need to change my usernames in a column from JSmith to ABCSmith. What would be my update statement to make that change? I need something that would basically start at position 1, for a length of 1, then use that to replace with ABC...
Here is what I have been trying:
UPDATE tblusers, SET userLoginID = replace(userloginID, 1, 'ABC')
In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.
I would really apprecite if anyone could make a comment on this...
what i wanna know is.... there are two tables, called 'A' and 'B' with identical structures... is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2 * Records of table 'A' which not in table 'B' and * Records of table 'B' which not in table 'A'
I am new to SSIS and everything in it. I have been reading up on it but I am stumped. I need to do the following: Automatically open a .csv file that will have a different name each day Extract the data Convert it to SQL format Append it to it to an existing SQL table in my DB Delete or move the .csv file to a different directory
hi All, I have a string varaible passed to the SP something like : @var = 'v1#1@v2#1,2@v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that : select * from var_data shud return like this : ID Role v1 1 v2 1 v2 2 v3 1 v3 3 v3 4 v3 5
Plz guide me how to achieve this result from the variable. Thanks in advance :-)
heyI have a problem string index;Command.CommandText = "Select * from employees where LastName = ' " Index " ' ";but the problem is iam not able to pass the index value properly like it should be ( ' Index ' ) for SqlServer2000.but its either giving me an error or its not taking the value properly.Can anyone help me out with that.Thanks
Can someone point me in the right direction. I would like to grab some data from an email (not as an attachment)and load into a table, but I have no idea where to begin. Also, can this data also be retreived from a website?
Has anyone been able to solve the issue with setting up a package in EM, which connects to an Exchange(.edb) database and extracts the data into a SQL Server db. Previous message posts don't offer much insight. So far, all I have is using Access 2000, to connect & move the data, then extract from here. Thanks in advance for any help. BV
I recall something about Analysis Services (SQL server tool) that is able to extract data from the DB tables (select multiple tables if one wishes (and to link those tables up ??!?)) then extracted to MS Excel. It has been a while since the course was last taught, I'm not at all sure how the steps were, or how Excel were able to display them. Would someone advice or refer to a tutorial if possible?
I have a description that is dynamically built for a part, with variables for this part split by line feeds. For example
100" widget Color: White Holes: Many Material: Wood
This makes everything nice an neat on reports, etc, and the world is good. There are a lot of better ways to do this I am sure, but due to the limitations of the software we are using, this fits the bill best.
In SQL view, it LOOKS like the lines are all run together, but when you actually pull the data, it formats properly.
Enter a new need to identify just the first "line" of info. I would like to be able to query all characters left of the CHAR(10). In the example above, the results would be just 100" Widget. I have tried a couple ways using Left(field,charindex(char(10))-1 but nothing seems to get me data back. Is this even possible within a View?
I have an Excel 97 file that has a lot of data in it and I need to pull out pieces of that data. Here is one example of the data I have. It is in colum C in Excel.
What I want to get out of this to put into a SQL table in one record is (I've included a header row here):
StartDate EndDate Week Value 11/22/07 12/1/07 1 183751
What is the best way to attach this? I have several other items I need to pull out of this Excel File, but if I can get this figured out I can adapt it to get the rest of what I want.
hope someone can help with this problem. I've created a package which copies data from an oracle 10g database into excel. the package works fine when executed in BIDS, at the command line or using dtexec but fails whenever i try and schedule it as a sql agent job. The package was created as the same user that sql agent runs as. I'm getting the following error message....
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2008-02-15 14:28:03.85 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OPRD.epsenq" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. The package execution fa... The step failed.
does oracle client tools need to be installed in a certain locaion? the server is 32 bit.
I have a form in Notes that is huge. I need to extract a part/field from that form into SQL SERVER. If I use NotesSQL Thru SQL DTSPackages, I am afraid it will run very long. Is there any other option on that?
I am using data flow task.And data flow source uses ole db for olap 9.0 to connect my ssas. sql comment is my access mode. A mdx query extracts data. Data flow destination is sql server table. Error said Data Flow Task: OLE DB Source [579]: The output "OLE DB Source Output" (589) references an external data type that cannot be mapped to a Data Flow task data type. I guess it is a implicit data type convertion problem. But how to solve it???
I'm trying to pull 1.2 million rows down from an Oracle database into SS2005. It has been painfully slow (about 20 - 30 minutes). Is there a way that I could speed this up?
While I am connecting to Oracle 9, I tried downloading the latest drivers (10g) from Oracle because I thought I could use the ODP.NET drivers. Unfortunately it did a number on my system and I could no longer even ping one of the databases.
I've seen this question sort of answered, but the only answer that I've found seems to be to use a custom connector developed by http://www.persistentsys.com. That is great that they have developed something, but I would expect that I should be able to use Oracle and/or Microsoft's drivers to get similair performance. (OK, I just don't want to pay for it )
I was thinking I could do a bulk dump from Oracle, into a flat file and then bulk load it into SQL Server, but I don't have (or know about) tools that can bulk dump from Oracle.
Hey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server. We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server. Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table. So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles. MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]); BinaryReader bReader = new BinaryReader(mStream); int intFileSize = (int)mStream.Length; Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location. BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create)); int count2 = bReader2.Read(byteFile, 0, intFileSize); bReader2.Close();i've also tried this but when the file gets created in the folder there is no content. i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works string strContentTpe = WValue.WStr(dtrResults["contenttype"]); int intFileSize = VValue.VInt(dtrResults["filesize"]); / Byte[] byteFile = ((Byte[])dtrResults["file"]); //Downloads the data correctly Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """); Response.AddHeader("Content-Length", WValue.WStr(intFileSize)); Response.ContentType = strContentTpe; Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal
I have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.
My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.
Hi @ll, I'm trying to extract data from a database where columns have names with spaces inside, I show an example:
select "total amount" from sales;
This query shows me all data stored in the "total amount" column in table sales, ok no problem.
Well, the problem comes when I try to execute this query with osql because the output text file where I put the data only have the text "total amount".
I use the query as I wrote before stored in a text file.
I'm doing something like this:
osql -U user -P passwd -i query.sql -o textfile.txt
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
I am running an hotel application for all the bookings.
I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.
The application has an SQL database locally on my computer.
Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?
