Extract IBM UniData (Pick Database) Data Into SQL Server
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
ADVERTISEMENT
Mar 5, 2007
Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
View 7 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
Dec 29, 2006
Hi -
I am using linked server to insert data to a table. When I do select, it does show me results but when I do insert, it does not work. My source/destination has exact same data types defined. Any idea?
insert into dbo.tb_PERSONNEL
select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN
FROM PERSONNEL_1_NF')
I get:
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
View 1 Replies
View Related
Jul 20, 2005
Hi,Anybody have an idea of copying data from tables of a database to anotherdatabase. It should be a choice to select all tables, single table orseveral tables.For them knowing Oracle it is possible to do it with 'exp', where you canchoose to script the database with or without data. So I am trying to getalike to MSSQL. The job is to unload data from a database with onestructure to another database with another structure.Thanks in advanceBest regardsTom Frank
View 4 Replies
View Related
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
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
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
Nov 17, 2011
I have a case where I need to pick the right data based on two dates and month.
Here's the sample of data:
Code:
cash_box_id | balance | validfrom | validto
-----------+---------+-----------------+------------------
cb003 | 950 | 30june2010 | 24may2011
cb003 | 900 | 25may2011 | 01Jan5999
cb005 | 800 | 11jan2011 | 01Jan5999
cb007 | 300 | 24Feb2011 | 10may2011
cb007 | 385 | 11may2011 | 22may2011
cb007 | 590 | 23may2011 | 28jun2011
cb007 | 700 | 29june2011 | 01Jan5999
...
I wanted to pick data that valid on 31 May 2011. So, expected output should be only two records as blue highlight.
Code:
cash_box_id | balance | validfrom | validto
-----------+---------+-----------------+------------------
cb003 | 950 | 30june2010 | 24may2011
cb003 | 900 | 25may2011 | 01Jan5999
cb005 | 800 | 11jan2011 | 01Jan5999
cb007 | 300 | 24Feb2011 | 10may2011
cb007 | 385 | 11may2011 | 22may2011
cb007 | 590 | 23may2011 | 28jun2011
cb007 | 700 | 29june2011 | 01Jan5999
...
They are thousand of records like this.
View 2 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
Apr 1, 2008
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
Thanks!
View 1 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 8, 2001
Hi,
I am interested in writing a stored procedure that will compute and select data from SQL Server 2000 database and store it into a .XLS or flat file.
how can i do that ?
need help.
Thanks.
-- Parag
View 3 Replies
View Related
Jan 11, 2008
I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_
I want to select the 8 characters after the first comma in the field, and then convert it to a date format.
I am a novice, so any help with the correct function and syntax would be greatly appreciated.
thanks,
Dave
View 8 Replies
View Related
Jul 16, 2015
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
View 3 Replies
View Related
Apr 28, 2006
Greetings MSDN Experts! I am working for a company that has a large payroll database in UniData format in a program called "ScorPEO." The information from that database needs to be imported into a newer payroll SQL database program called "Millennium." Any ideas on either how to go about doing it or if there are any companies that are good at UniData to SQL conversions?
(Note: If you really want to learn more about the ScorPEO/HRPyramid program go to http://www.fwdco.com/ For more on Millennium, go to: http://www.mpay.com/ )
Thanks!
View 3 Replies
View Related
Dec 17, 2014
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
View 5 Replies
View Related
Jan 14, 2006
I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance
string strCatalog = "TestCatalog", strsearchstrings=""; string strQuery=""; strsearchstrings = txtsearch.Text;
strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')"; // TextBox1.Text is the word that you type in the text box to query by using Indexing Service. string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring); conn.Open(); System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn); System.Data.OleDb.OleDbDataReader objRdr;
objRdr = objcmd.ExecuteReader(); DataGrid1.DataSource=objRdr; DataGrid1.DataBind(); objRdr.Close(); conn.Close();
View 1 Replies
View Related
Dec 8, 2006
I have installed the MS SQL Server (Developer Edition) that comes with Visual Studuo 2005 Professional Edition into my computer. Seems like install is OK and I can see both SQLEXPRESS and MSSQLSERVER in configuration manager. I can start/stop both of the servers OK. However, when I try to make a connection from Visual Studuo 2005, I can only see SQLEXPRESS in the pick list in "Add Connection" window. SQLSERVER is not in the list.
The very first task I wanted to do is to convert and Access database to SQL. For that reason, I also installed "Microsoft SQL Server Migration Assistant for Access". In that application I cannot even see any server name when the wizard asks which server to choose. I also tried to start/stop "Sql Server Browser" service. When I stop the service, the pick list becomes empty and when I start the service the pick list has only SQLEXPRESS.
So, how can I see or pick MSSQLSERVER to make a connection in either applications? Please note I am VERY NEW to SQL Server and I appreciate your answers with that consideration.
Thans in advance
View 2 Replies
View Related
Apr 4, 2007
I am trying to import unidata into SQL Server 2005 Standard Edition.
I can successfully import data into MS Access. It is simple. I just tell Access to look at the appropriate system DSN, put in the server, database name and password, and there are all the tables to choose from.
The ODBC data source is not one of my choices in the Import/Export Wizard in SSMS.
How do I use the Import/Export Wizard to import unidata?
Thank you.
Joel
View 17 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
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
