How To Extract Data From One Database To Another.
Jul 20, 2005
Hi,
Anybody have an idea of copying data from tables of a database to another
database. It should be a choice to select all tables, single table or
several tables.
For them knowing Oracle it is possible to do it with 'exp', where you can
choose to script the database with or without data. So I am trying to get
alike to MSSQL. The job is to unload data from a database with one
structure to another database with another structure.
Thanks in advance
Best regards
Tom Frank
View 4 Replies
ADVERTISEMENT
Sep 29, 2014
I'm trying to extract data from our database for the number of phone calls our reps are doing.
In counting the calls I only want to include up to 3 calls to the same customer (field name is CompanyID) per day - anything more than this is ignored.
The query at the moment is something like:
SELECT COUNT(CallID) AS CallCount FROM Sales_Calls WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' AND RepID = 1
Using MSSQL 2012.
View 13 Replies
View Related
Sep 25, 2007
I have to extract data from 5 different oracle databasee with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies
View Related
Sep 25, 2007
I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies
View Related
Feb 27, 2007
Hi,
My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.
I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select count(FingerPrintID) as FingerPrint
from Employee
Result Set is set up like this:
Result Name: FingerPrint ; Variable Name: User:: varDataset
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".
My variable is set up as a Int16.
Please help!!!!!
If you could provide step by step example's that would really make my day.
Thanks,
Lawrence
View 7 Replies
View Related
Sep 25, 2007
Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.
There is a table which list all the 10 databases.
View 2 Replies
View Related
Oct 31, 2006
Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)
View 2 Replies
View Related
Jul 24, 2015
Why does it take me 4 hours to set up an SSIS package that I can run from a SQL job to extract data from a SQL database to and Excel workbook. Shouldn't this be easy to do with 2 Microsoft products? Writing the query to extract the data takes 10 minutes, the rest of this process should take less than that.
I should be able to create a new job that runs my query (I can actually do that) and saves the data to an Excel workbook. Why can't I do that?
View 3 Replies
View Related
Apr 21, 2008
Hi Everyone,
Am a third year student doing work placement.
Could anyone please give me clues on how to go about extracting data from a LDAP and then into an SQL database?
1 A defined subset of data is to be extracted from GDS on a nightly basis,
2 Then imported into a SQL database for quick & easy retrieval.
3 A web interface is required to present data retrieved from the SQL database.
I will appreciate every assistance.
Regards
Lidiolo
View 6 Replies
View Related
May 7, 2008
hi lads,
what i need to do for my project are as following:
a mobile user send data via GPRS to SQL server Database. then i need to have a method to detect while a particular table is being inserted. and then extract data from table construct a report dynamiclly. what should i do to achieve this goal? e.g. window application, store procedure or trigger ?
PS : client side is a mobile application developed using MCL. i don't in which way he will send all data to SQL server Database, so what i need to do is to monitor new data inserting.
2) how to auto generate and print report directly after record been inserted into the table ? Do i need to import report web service API ? if yes, which one? or i can use other methods e.g. predefine report control view in my window application, turn off pop-up menu while printing a report(I guess)
thanks
View 2 Replies
View Related
Jun 23, 2015
Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.
View 5 Replies
View Related
Oct 2, 2014
I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,
I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..
Is there a simple tweak that would speed it up?
USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT
[Code]....
View 4 Replies
View Related
Jun 13, 2006
Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
View 3 Replies
View Related
Mar 28, 2008
Hi All,
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
thanks
Sanra
View 4 Replies
View Related
Jan 22, 2008
I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV
SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM
View 2 Replies
View Related
Jul 23, 2005
I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 2 Replies
View Related
Jan 10, 2008
I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd
I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd
I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1
But i get the following result which is no good!
Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba
What am I doing wrong in the Street & StreetSuffix Functions above?
I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.
In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))
SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))
SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))
View 6 Replies
View Related
Feb 18, 2008
My web hosting provider allows me to access database via Management studio, but to restore a database, a ticket have to be submited.
I intent to extract MS SQL 2005 database into sql script, then I can execute this script to easily restore my database.
I tried EMS SQL Manager 2008 in my development server with login user sa, but when I executed the extracted script, I got an error message:
The procedure 'sys.sp_droprolemember' cannot be executed within a transaction.
Is there any tools that can achieve this task?
Thanks
View 4 Replies
View Related
May 21, 2008
Hi,
How can I extract an XML from my data base and display it on reporting services?
I want to make a list of team (it's already do) and a link to a report wich display the XML
But I don't know how can I display this XML into a table.
Database Design:
[Table=FootTeam]
_ id : bigint
_ nameTeam : nvarchar
_ playerInfo : xml
DataBase Table
1 | "Mancherter" | "<team><player><name>Mick All</name><postion>Defense</postion></player><player><name>Pete Kolo</name><postion>Atack</postion></player></team>"
2 | "Liverpool" | "<team><player><name>John Back</name><postion>Defense</postion></player><player><name>Alex Pol</name><postion>Atack</postion></player></team>"
Example of a XML:
<team>
<player>
<name>Mick All</name>
<postion>Defense</postion>
</player>
<player>
<name>Pete Kolo</name>
<postion>Atack</postion>
</player>
</team>
View 2 Replies
View Related
Aug 5, 2005
Hi, I'm hoping someone has an idea or two on this topic.
Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
-----------
ContactID
Email
Name
tQuestion
------------
QuestionID
Question
tAnswer
------------
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to see
the data with one line per contact, but showing every answer to every
question... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc........
Obviously to get the data I cansimply do an outerjoin to get all
contact data then all questions, and answers that exist... but that
will obviously return tabular data with one row per each
answer... Does anyone have any ideas on how to do this using just
SQL? I can pull the data and write a function that spits it out
to text using the Stringbuilder class and some logic, but I'm thinking
this must be possible in SQL natively... any help would be more than
appreciated. Thanks in advance.
-e
View 1 Replies
View Related
Oct 5, 2005
hi there
i have a field name(fil_srt_cond) with the values of
cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3
i used the char index:-
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str
to display them in their specific fields like
NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC
however for cmpnt_stuff,DESC,2,3 i'm getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES
therefore i'm not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance
View 4 Replies
View Related
Mar 15, 2007
I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key
i want top 3 Name(field) in first table(Form1).
Result
------
xyzx
23wa
abcd
View 7 Replies
View Related
Jul 23, 2005
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
View 7 Replies
View Related
Jan 30, 2007
Hello,
I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.
I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?
Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3
Please help. Just can't figure this out!
Meera
View 7 Replies
View Related
Apr 4, 2008
Hi,
I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??
Any idea will be appreciated..
Thanks
Karen
View 4 Replies
View Related
May 14, 2008
Hi.
I want to compare the database structure (tables, indexes, sp...) of a huge running database with some old backups of it, without restoring them, to detect some object's changes. Data is not required at this time.
Is there a tool to extract the database structure into a SQL file directly from the .bak file?
Thanks... ++Vitoco
PS: I wrote such a tool for Oracle 7 many years ago. I hope someone did the same for SQL Server ;-)
View 3 Replies
View Related
Mar 7, 2008
I have a repository that is currently stored on our SQL 2005 database. I would like to pull this repository from the database and place it on our files server. Is there anyway for me to extract this from our database?
View 1 Replies
View Related
Nov 12, 2001
Hi all,
I would like some information on extracting data from an ODBC connected database. (oracle is the current master database).
I can connect to the oracle database, and view tables.
But I would like to create a job (in SQL server) that runs every hour to retrieve current data from the oracle database.
Has anyone accomplished this, or is there a tutorial about this procedure?
Any help would be appreciated....
thanks
tony
View 1 Replies
View Related
Sep 28, 2006
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance.
FIELD
----------
1234/4567
2345/6754
1234
I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3.
FIELD 1 FIELD 2
------------------
1234 4567
2345 6754
1234
View 3 Replies
View Related
Jun 30, 2015
I need to extract the information from the following Formula:
prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)
What would be the simplest and fastest way to do this...
I did write a fetch statement but i want to do something simpler as it is a bit hectic...
Basically i require the information from prj(10517) etc.
View 2 Replies
View Related
Jun 12, 2014
I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.
For Example
If user keys in ASOFDATE as 10-May-2014, I would have
ASOFDATE Data1 Data2
10-MAY-2014 123 443
30-APR-2014 222 234
31-MAR-2014 544 875
28-FEB-2014 546 908
31-JAN-2014 957 896
How do I do that?
View 2 Replies
View Related
Mar 11, 2008
I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob
View 1 Replies
View Related
Apr 28, 2008
Hi,
I have just started learning SSIS. Could someone please tell me if where can I find step by step instructions on how to simply extract data from two excel files and populate the relevant table. What I simply want to do is:
Excel File 1 (With Columns FirstName, DateJoined)
+
Excel File 2 (with column Summary)
|
|
->Add these three columns to a new table called CustSummary
Any thoughts and suggestions will be really appreciated.
Thanks
View 1 Replies
View Related
