Auto Data Extraction Through ODBC
Apr 12, 2007Hi,
Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).
Thanks
Amir
ADVERTISEMENT
Data Extraction From A Source Using SOLID ODBC Driver
May 12, 2008
Hi all,
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver.
When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below:
ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine.
Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
Thanks and Regards,
Nirmal
Data Extraction
Nov 12, 2007Hi guys!
How can I extract data from a SQL server 2000 to TEXT Files. On which the data length that I have is different from the new format.
sample: database1: employee_ID = 10
database2: ID_employee = 8
May I know what query can I execute to provide an 8 length for the incoming database?
Thanks! Hope u'll help me!
Data Extraction
Nov 13, 2007Guys can you help me to extract my database data to text files using different format:
sample
ClientId9ClientNo12
How can I extract ClientNo with a length of 12 to ClientID with a length of 9 to text files.
SQL Connection And Data Extraction Help
Jan 31, 2008Hi,
I am new to the whole concept of ASP.NET and codebehind (i am using c# and i am also new to that) i currently trying to get my codebehind to connect to a SQL Server express 2005 database on page load however i am getting this error thrown back at me when i run the aspx page on my localhost,
Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'.Source Error:
Line 18: {
Line 19: SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
Line 20: conn.Open();
Line 21:
Line 22: }
Source File: g:Level HRevivalDefault.aspx.cs Line: 20
i believe it has something to do with the conn.Open() funtion or the connection string it's self the acutal full code is here,
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
conn.Open();
}
secondly once i get the connection working i want t be able to check a user email and password against those in a table to allow the log in, through my experience with PHP i would of done this by putting what the user entered into a variable and then checking the variables against an sql query, how do i go about this with aspx and C# my textfield is called uname and my passsword is call pword. Any help of example code would be greatly appreciated.
Many Thanks
S_ainley87
Extraction Of Data On Conditions
Sep 24, 2014I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586
I want result on the basis of first 2 digit the repeatation of code is count as 1.
code1 code2 count
12 34 2
21 34 1
45 78 1
Simple Data Extraction From 2 Tables
Mar 22, 2007So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.
This first one returns no results at all when I should get about 7 rows or so.....
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS'
AND student.name = Attendance.studentid
This one is returning all of the names in the database 6 times each.
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS' order by name
I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!
Conditionally Extraction Of Data From A View
Mar 19, 2007Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorB
Transact SQL :: Delimited Data Extraction
Jul 29, 2015I have a database that has a table with a lot of delimited data. I need to extract some of this data, preferably into a sql view, that I can name the fields. The data looks like this:
|7700|6|0|work center|24|8|XLT
What I need is:
field name 1= 7700 field name 2= 6 field name 3= 0 field name 4= work center field name 5=24, ETC
I'm not sure how to count the delimiter | and get the data between | & |. Or maybe that isn't the way to do this.
Here is a sample of what I did in Crystal Reports but I need a view to make this usable.
Split ({usrc1.application_data}, "|") [22] This gives me the data between delimiters 22 & 23.
SQL Server 2012 :: Dynamically Hourly Data Extraction?
Dec 1, 2014I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
AS 2000 DTS Data Extraction From Cube To Flat File
Sep 6, 2006Currently we have a DTS that extracts data from a cube to a SQL Server table and then from SQL Server to a flat file.
This is taking a considerable time is there a faster way of doing this?
Thanks.
Mark.
Integration Services :: Data Extraction From Hyperion Financial Management Using SSIS
Jul 21, 2015I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
SQL 2012 :: SSMS Auto-recovery / Auto-save New (unsaved) Queries
Feb 16, 2014Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Dec 10, 2003Hi Everybody,
On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.
Thanks,
Arif
Server Error in '/' Application.
--------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.]
Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27
Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838
Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80
Calgary.venues.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootCalgarySitevenuesvenues.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
DDL Extraction
Sep 28, 2006Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects? I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse.
Thanks in advance
SAP Extraction
Jan 2, 2008Does SSIS support SAP extraction by out of box.
View 5 Replies View RelatedAuto Transfer Of Data From One DB To Another
Oct 6, 1998I`m wondering the best way to automate the transfering of data from
one SQL 6.5 DB to another SQl 6.5 DB, both are on the same server.
The idea is to move the data, then purge it from the orginal DB.
I was thinking of using bcp, is there a way to copy a selected group of records
instead of a table (I need to be able to select the data to transfer), or
do I need to select the data to table then use bcp, or is there an better
way to do this?
Thanks,
Nora
Truncation Of String Data With Data Reader Source Connecting To ODBC DSN
Mar 18, 2007A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Any assistance would be appreciated.
Thanks,
OLE Object Extraction
Mar 31, 2006Hi,
I have virtually no ms access skills as am an Oracle dba.
I need to extract OLE Object data which are images from an Access 2000 mdb file for an import I am writing, can anyone point me in the direction of either some software that will do this or any scripts I can write to do this.
When I open the table the field reads "Long Binary Data"
Many thanks
Robert
Term Extraction
May 12, 2006I've just started using the SSIS and i would like to know if it's possible to change or update the dictionary of the term extraction tool. That's important to me because i may have to look for words that don't exist in the defaut english dictionary of the tool.
Thanks.
Date: Auto Data Type
May 29, 2006I am really new to this. Is there a way to set up a data type in SQL Server to automatically write the current date to a column? Instead of sending it from the page, the DB just writes the date to the column ...
Any guidance would be great!
-L
Auto Increment Auto Non-identity Field
Jan 23, 2004I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
Excel Extraction Issue
Oct 26, 2006I have a spreadsheet that's designed to look like a form. Does anyone know how I would create a SSIS package to extract specific cell values?
TIA
Term Extraction -&&> Keep Order?
Nov 29, 2007Hello,
when I extract nouns from a text with the Term Extraction Transformation, the destination indeed keeps the correct nouns but they are in a random order.
Is it possible to keep the order of these nouns and maybe already keep double occurrences?
Kind regards, _Rodney_
Term Extraction Frequency Per Row
Sep 15, 2006Is there a way to perform term extraction on each row in a table, and have the term extraction denote the frequency of the term on a per row basis rather than a per table basis?
Otherwise, is there a way I can take extracted terms and apply a sql function that returns the occurance of that term in each row?
Example: Row 1 = 2 hits, row 3 = 5 hits, etc.
Requesting It(PK) After Saving Data With Auto Prim.Key
May 26, 2006Hi,
I have a page for saving a "home" data. As the page unloads I will
redirect the user to the page on which he can add the photos to this
"home".
Well I structured my photo adding page with first requesting the "homeID" which is an auto Integer (PK).
Because it's renaming the uploaded photos with the homeID and _01, _02...
Because of that i need to send the user to the photo adding page with "response.redirect......aspx?homeID=.....
homeID= ???
But I don't know how to get the homeID value, that the SQL Server automaticly selects for the data. As it is just saving it...
Thanks a lot.
Brute-Force Substring Extraction
Aug 28, 2007In my product description field, I need to extract the data that looks like
19-3-19 or 0-0-7. Sometimes that pattern is in the data, sometimes not.
I only want those that contain the pattern, of course, but I have no other method of determining whether or not the pattern will be in the description.
I thought I'd found a method that worked, until I encountered descriptions
that had a '-' in it prior to the pattern.
Here's what I have so far, it works for all but the last one.
CREATE TABLE #TMPPROD (
PROD_DESC VARCHAR(60)
)
INSERT INTO #TMPPROD
SELECT '22-4-12 15%SCU'
UNION ALL
SELECT '14-4-8 W/F'
UNION ALL
SELECT 'UFLEXX 17-3-17 2%FE'
UNION ALL
SELECT 'FERT-PEST 19-19-19'
SELECT PROD_DESC, SUBSTRING(PROD_DESC,CHARINDEX('-', PROD_DESC)-CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END, CHARINDEX('-', SUBSTRING(PROD_DESC,
CHARINDEX('-',PROD_DESC)+1,2))+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 2,1))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END+2+CASE WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) +1,2))<>0 THEN 2
WHEN PATINDEX('[0-9]', SUBSTRING(PROD_DESC, CHARINDEX('-', PROD_DESC) - 1,1))<>0 THEN 1
ELSE 0 END) AS 'ANALYSIS'
FROM #TMPPROD
WHERE PROD_DESC LIKE '%-[0-9]%-[0-9]%'
DROP TABLE #TMPPROD
Any pointers on what to try next, or have I severely over-complicated the process - as I am wont to do.
Thanks
Mark
Pattern Extraction Table Value Function
Aug 9, 2007
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p>
<p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows:
http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG
http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
Any idea?
Using An ODBC Data Source In A Data Flow
Oct 2, 2006Okay, this should be really simple but I don't get it. How do I use an ODBC data source in an SSIS data flow task? When I look at Data Flow Sources I see the following options:
Pointer
DataReader Source
Excel Source
Flat File Source
OLE DB Source
Raw File Source
XML Source
Which one do I use if I need to get the data from a connection manager that is ODBC based? The IBM OLEDB driver for the AS400 doesn't work correctly so I HAVE to use an ODBC driver to connect to an AS400 data source.
Thanks in advance for any info.
Schema Extraction For Multiple Interdependent Databases
Jul 23, 2005I'm looking for a tool that can extract only the schema from a databasein a form that can be used to generate that schema in another emptydatabase. This is to facilitate our disaster recovery processes wherewe need the objects only, not the data, and need to replicate this toour disaster recovery site over the WAN. There are plenty of toolsthat can handle a single database, but does anyone know of any toolsthat could handle multiple databases where many of the objects (storedprocs and views) are dependent on objects in other of the databases(tables). This is a home-grown ETL suite so making changes to the codeto remove these dependencies would take way too much effort. I amlooking for something that can either extract the schema for all 3databases and handle the object creation ordering to account for thedependencies (a simple method would be to extract by object type acrossall databases, e.g. tables for all dbs before views before procs), or abackup/restore tool that allows you to restore the objects only withoutdata. Worst case we could write something to generate the DDL or useSQL DMO, but ideally we would prefer to purchase a (relativelyinexpensive) tool to do it.Thanks,Simon
View 3 Replies View RelatedSQL Express Server Fails To Install During Extraction
Feb 17, 2008
I've tried downloading SQL Server Express several times and installing. Every time it fails during extraction. I have a Vista machine with .NET Framework 3.0 already installed. I'm using MS Expression Web, MS Access, and have also downloaded and installed MS Visual Web Developer 2008 Express Edition. Why can't I even get SQL Server Express to extract from the downloaded file?
Problem With Extraction Of A Report In Xml Format Using XSLT
Nov 1, 2007Hi guys,
I have a simple report created with rs2005. I want to get the output exported (using the export options of rs2005) in a specific xml format, so I am using xslt transformation to get it proper.
My problem is that, when I'm doing this using my own machine (SQL Server 2005 32bit installed) everything works OK. BUT when I want to try it on a server that we are supposed to use it gives me a real bad error. On the server, if I have the report without using xslt, it gets exported OK but not in the correct format. I have tested the report on a 32 and on a 64 bit server, I have used either SP1 and SP2 but still can't get through. The error message appearring while I try to export in xml is "Server Error in '/Reports' Application. The XSLT path is invalid. It refers to an external resource, uses invalid syntax, or the XSLT was not found in the catalog."
Has anyone seen something like this before ?
Your help would be much appreciated.
Thanx in advance
Problem Getting Select Data Set For XML Auto, Elements Into A Table Variable
May 26, 2004I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.
View 4 Replies View Related