Problems Extracting Data From Tables With Osql.

May 3, 2007

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

Can anybody give a hand? What I'm doing wrong?

Thank you in advance.

Bye.

View 2 Replies


ADVERTISEMENT

Extracting Data From 2 Tables.....

May 19, 2004

Cheers

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'

Thanks

View 5 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

Transact SQL :: Extracting From 3 Related Tables And Including MAX Function

Oct 5, 2015

I have these 3 tables in a database for printed chamber music works:

Table INSTRUMENTS: List of musical instruments
    Column ID          -- int, primary key
    Column sInstrument -- string

Table PTRS_INSTRUMENTATION: 
    Column ID          -- int, primary key
    Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
    Column lMainItem   -- int, points to a row in table MAINITEMS via ID
    Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
    
Table MAINITEMS: List of chamber music works
    Column ID          -- int, primary key
    Column sItem       -- string
    
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).

I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),

And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).

View 7 Replies View Related

T-SQL (SS2K8) :: Extracting Large Tables From Offsite Linked Server

Oct 1, 2014

I work in Healthcare IS for Company A, but Company B is hosting one of our EMR programs for us. This was done on purpose, so that whether a patient is seen at one or the other, their medical history is more complete. However, this puts all of the data that I need to get to on a server across town that I can only access via Sql Server Management Studio as a linked server.

Now, in some ways, the performance has been better than I expected, but sometimes it behaves very erratically. I am using OPENQUERY to handle all of the pulls, and am not joining to any local tables, in order to maximize efficiency.

Here is some of the code I run, and what happens:

SELECT *
FROM
OPENQUERY([linkedservername],
'
SELECT *
FROM Encounter_ItemChild

[Code] ....

***The above query was programmatically generated by taking the IDs from the second query, and packing as many into the IN condition as possible. Each statement could hold only about 900 IDs, so around 70 queries get built...however, each one returns the records in question in 1-2 seconds.

My main question is...if the second query pulls all IDs from Encounter in a few seconds, and that query is used in the first query's WHERE clause, why does it spin and spin, while manually throwing the IDs in instead runs almost instantly?

View 3 Replies View Related

Extracting Data From A Device

Feb 6, 2001

hi all,

i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?

thanx.

:-)

hemant

View 2 Replies View Related

Extracting A Portion Of PK Data

Mar 27, 2008

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.

View 1 Replies View Related

How To Extracting Data Into Three Table

Mar 22, 2007

This is my sample table Data

table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............

TagID--Primary Key

table2

TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key

table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................

DetID---Primary key
TagID and TopicID----Foreign key


i need Topic based on TagName.


This xyz(TagName) have a 3 Topic. i need the 3 Topic

Once i select 123abc(TagName) that particular Topic I need..

any Query in Single Line.......................

View 2 Replies View Related

Extracting Data From SQL CE SDF File

Dec 9, 2005

Hi,

View 8 Replies View Related

Extracting Data From Sql Server 6.5

Jan 22, 2008

Hi!

I'm analyzing the problem of extracting data from sql server 6.5 in order to load it into oracle.

I already know about the bcp tool, but I'd like to know if you would consider any other approach based on SSIS, or any other tool like SQL Load.

regards, and thanks in advance for your help.

View 1 Replies View Related

Extracting Data From Csv With A Different Name Everyday

Jan 30, 2008

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

Thanks for the guidance!

Matt

View 4 Replies View Related

Extracting Data From A Variable

Jul 30, 2007

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 :-)

View 1 Replies View Related

Extracting Data From A Varchar Variable?

May 19, 2004

Guys.

I have some data like this one:

'UploadfilesCompanyIDProjectCodeFilesFile1.doc'

P.S: the values may vary each time.

I need to programmatically extract from that kind of string only file1.doc

Ho can I dynamically do it? any function? statement? etc? please help.

The idea is getting only the string after the last ""

Thanks
Fr

View 1 Replies View Related

Sql Query Problem While Extracting Data

Oct 18, 2005

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 

View 3 Replies View Related

Extracting Data From An Email Or Website

Feb 1, 2002

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?

Thanks
BV

View 1 Replies View Related

Extracting Data From Exchange Into SQL Server Db

Mar 22, 2002

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

View 1 Replies View Related

Extracting Data From MSSQL To MSExcel

Jul 13, 2004

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?

Much thanks!

View 1 Replies View Related

Extracting From A DATETIME Data Type

Mar 16, 2004

Hi,

I can't figure out how to extract just the date or just the time from a DATETIME data type? Any suggestions?

View 2 Replies View Related

Extracting Data Based On Two Fields

May 27, 2014

I have a table having multiple fields but I need to extract data based on the two fields.

Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758

I want the result with 2 cases

1.) distinct in Field 1 but multiple in field 2

Expected result:

Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758

2.) multiple in field 1 but distinct in field 2

Expected Result :

Field 2 | Column1 | Column2
123 | 1 | 2

There may be more columns added according to the data available.

View 7 Replies View Related

Extracting Data To The Left Of Char(10)

Oct 21, 2014

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?

View 1 Replies View Related

Extracting Data From An Excel File

Dec 4, 2007

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.

Row Column C
1 11/25/07
2 12/01/07
3 week 1
4
5
6 9,708
7 128,353
8 -
9
10 42,691
11 3,000
12 183,751
13 $183,751


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.

View 4 Replies View Related

Problem Extracting Data From Oracle

Feb 15, 2008



hello



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.



thanks
iain


View 1 Replies View Related

Extracting Data From LotusNotes Into SQL SERVER

Feb 15, 2007

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?

Any help is appreciated.

View 2 Replies View Related

Extracting Olap Data Into SQL Using SSIS

Apr 2, 2008

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 appreciate for all suggestion. Thanks.

View 6 Replies View Related

How Can I Speed Up Extracting Data From Oracle?

Apr 27, 2007

Hello,



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.



Thank you for the help.



-Gumbatman

View 7 Replies View Related

Extracting Binary Data From SQL DB To A Location On The Web Server

Mar 15, 2007

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     

View 2 Replies View Related

Extracting Data From Oracle Into SQL Server 6.5/Sybase

Feb 7, 2001

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?

Tony

View 1 Replies View Related

Extracting Data From A Table For Archiving Purposes

Sep 22, 1999

Hello,

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.

Sorry this is so long...

Thanks,

Brian R
WESNet Systems, NOC

View 1 Replies View Related

SQL Server 2012 :: Extracting Data From XML Query

Sep 28, 2015

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.

create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">

[Code] ....

View 6 Replies View Related

SQL Server 2014 :: Extracting Data From Database?

Nov 5, 2015

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?

View 8 Replies View Related

Extracting The Oracle Data Into SQL Server 2005.

Apr 28, 2008

Hi All,

We are extracting the data from ORACLE databse into SQL Server 2005 database. Both are hosted on different servers.

For this we have created linked server on SQL Server and we using the OPENQUERY to extract the data. Its taking too much time to extract the data.

Is there any other alternate thing for OPENQUERY clause.

Please help me on this.

Thanks in advance.

Thanks,
Ramesh.

View 3 Replies View Related

Creating An Index Whilst Extracting Data

Nov 28, 2007



Anyone know the syntax and function to create a self incrementing index whilst extracting data? I'm using the Derived Column tool. The extract will be a full extract everytime, not incremental.
Thanks.

View 4 Replies View Related

BUG? Extracting Data Stored In Multiple Languages

Feb 27, 2006

Hi,

I have an access database that is storing text in english and chinese characters. I am importing the data using SSIS and it is putting a 'square' character in place of all the chinese characters. Can I associate multiple code pages to a control? If this is not possible, I would just like to Extract the data and Load it into a table, and i will just handle the errors and write the key fields to an error log. The problem is that the error handler is not redirecting the row. It just simply fails. I have changed all the error actions to Redirect Row too. I currently have a script checking if the 'square' character exists but i feel this should be handled a better way? Has anyone run into this issue or have any suggestions??

This is the error code i get

Data conversion failed. The data conversion for column "FDCDOPS0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved