DTS Works, Job Fails!(data Source Foxpro And Destination SQL Server 2000
The DTS works perfectly when I run it manually. However, when I run it
as a job it fails. Before you ask if i'm running it under different
security context. I have already made sure of that. I was logged into
the server through remote viewer, when I created and ran the package,
as well as scheduling the job. So the accounts they're running under
are consistent. They're the same accounts as the SQL Agent is running
under and it's the sys admin account.
The data source is a Fox pro database with a pull of two tables. I am
using Microsoft OLE DB Foxpro driver as my source connection and OLE DB
Connection for SQL Server as my destination. I am doing a simple table
to table transformation. The path of my connection is a mapped Drive:
E:Main. There are other packages and jobs within my job queue that are
pointing to the same database and they seem to run fine using the above
mapped drive. The ONLY difference between this package and other
packages are that, they're few months old and this one was created last
night. I have also enabled logging on this package and here is the
below error when the job fails:
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft OLE DB Provider for Visual FoxPro
Step Error Description:Invalid path or file name.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 9/23/2006 11:39:17 AM
Step Execution Completed: 9/23/2006 11:39:17 AM
Total Step Execution Time: 0.031 seconds
Progress count in Step: 0
View Complete Forum Thread with Replies
Related Forum Messages:
Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out. On SQL Server 200 instances the job ran in minutes in the old 2000 package. Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be? Any inputs will be much appreciated. Thanks, MShah
View Replies !
DTS Fails When The Columns Between Source And Destination Are Different
Hi Everbody, I would like to transfer couple of text files into SQL7 using DTS. The destination table has 17 columns and some records in the text file does not have the same number of columns. Therefore, DTS package will fail when it gets to the record that has less columns. Can anyone please give me some tips here. I can either use ActiveX or anyother mothods. Thanks
View Replies !
Source And Destination Databases On Same Server While Data Transfer Using SSIS
Hi, I am having one query regarding data transfer using SSIS. If we use DTS packages for the data transfer between two databases then the source database and destination database must be on different db servers or instances.Here, I am talking about the DTS Packages with Distributed Transactions enabled. I need to know that whether this constraint has been rectified with SSIS Packages or it still persists. One more thing is that while transferring the data, can we view/insert/update source database or it is locked if the transfer is in process. Please reply.......... Thanks and Regards, Rajesh
View Replies !
SQL Triggers: Transfer Data From SQL Server 2000 To Visual FoxPro DBase
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration. There error generated is: Server: Msg 16957, Level 16, State 4, Line 3 FOR UPDATE cannot be specified on a READ ONLY cursor Here is the cursor declaration: declare some_cursor CURSOR for select * from part_view FOR UPDATE Any ideas, guys? Thanks in advance for knocking your head against this one. PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View Replies !
How To Use The Same DTS For Different Data Source And Destination
My question is similar to Savita's question: I am importing data from text files into tables with the same structure.Therefore, I have hundreds of DTS packages with the only difference in source text files and destination tables... Obviously, a nightmare for maintenance, isn't it? Is there any help, like using of some parameters, or scripts, or what? : ))) Thanks in advance, Suat.
View Replies !
Create Date Source View And Data Model That Works Right
I created a data model for report builder. And since it wont let me use views, i tried to put all the tables im using in one of my already made views, to create a data source view like my sql view. But when i connect everything the same, my report model still doesnt narrow my search. I only want to see Breed information, but since i have a Breeder table,and Customer table, its showing me all customers, and not limiting it to the ones that are breeder customers. heres the tables: Code Snippet FROM dbo.Tbl_Customer INNER JOIN dbo.Tbl_Customer_Breeder ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Breeder.Customer_Code INNER JOIN dbo.Tbl_Customer_Detail ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Detail.Customer_Code INNER JOIN dbo.Tbl_Customer_Category ON dbo.Tbl_Customer.Customer_Category_Id = dbo.Tbl_Customer_Category.Customer_Category_Id INNER JOIN dbo.Tbl_Customer_Classification ON dbo.Tbl_Customer.Customer_Classification_Id = dbo.Tbl_Customer_Classification.Customer_Classification_Id INNER JOIN dbo.Tbl_Customer_In_Breed ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_In_Breed.Customer_Code INNER JOIN dbo.Tbl_Breed ON dbo.Tbl_Customer_In_Breed.Breed_Id = dbo.Tbl_Breed.Breed_Id What am i doing wrong, and are there any suggestions.
View Replies !
Move Data From Source Database To Destination
I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB. I was thinking using dynamic sql, cursors, and linked servers. Any ideas? Jim
View Replies !
Transferring Data From Read-only Source To Destination.
Hi, I have read only permission in the source (OLTP) database. The source database is running in SQL Server 2000 and the size is more than 200 GB. I need to pull data from source and load target which is running in SQL server 2005. Following are the objectives I want to achieve. Data should be loaded on incremental basis. Whatever changes take place (Update/Delete) in source, that should be replicated to already uploaded data. Here I want to mention that, the source database does not have any identification key or timestamp column like Updated_Date by which I can filter the data which are recently inserted or updated into the source and upload the same. The source does not maintain any history data also. So I do not have any track of deleted record also. I don€™t have any scope to change the schema in the source. In this scenario can anybody suggest me the best approach to achieve the above mentioned objectives? Can I retrieve only the recent updated or inserted date form transaction log back up. Can log shipping solve the give the solution? One more question. Say I have a table and I am exporting/importing all the data from/to my target table using SSIS or DTS. In this scenario does using query or using directly the table affects the performance? Regards Sudripta Rakshit
View Replies !
Lookup For Compare The Source And Destination Data
Hi, I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1 being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates. I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it. I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP. Thanks
View Replies !
Lookup For Compare The Source And Destination Data
Hi i want to compare the source and destination data my source is Oledb source and destination is sql server destination I want to Compare the Data in destination with source and also want to add validation like iF Any row is there is destination but not in source delete that row if any row in source but not in destination Insert that row nd compare source with destination respect to Primary key if There is change in any column of that row Update that if not change than move to next record. Please tell me what is the sutable object and what to do Please Help me Out Thanks Sandeep
View Replies !
XML Source To Data Conversion To OLE DB Destination - Loss Of CDATA
I'm trying to build a DTSX package that FTP's an XML file to the local file system and then imports it into an existing table. My "Data Flow" for the package starts with an XML Source component and then goes to Data Conversion component and then to an OLE DB Destination component. It all executes with out error and seems to work fine, but when I look at the data in the table after I've run the package it seems to have inserted the appropriate number of rows from the XML file but all of the column values are NULL. All of the data in the XML file is surrounded by <![CDATA[ ]]> and I discovered that if I remove the CDATA wrapper by hand then it inserts the data properly. The only problem is that I'm not in a position to have the data provider remove the CDATA tags and some of the data in the XML file needs the CDATA wrapper or else it will not validate. Anyone know of anything I can do to get the CDATA to import properly?
View Replies !
Data Flow Task - OLEDB Source / Destination
Hi Inside a data flow task, i have a oledb source and destination. In my situation, I need to pull data from a table in the source, but also hard code some columns myself, which means my source is a blend of data from table, hard coded data, which will then have to be mapped to columns in oledb destination. Does anyone which option to choose in the oledb source dropdown for the data access mode. Keep in mind, i do need to run a a select query, as well as get data from a table. Is it possible to use multiple oledb sources and connect to one destination, because that is really what intend to do here. I am not sure how it will work, or even if its possible. Basically my source access mode needs to be a blend of sql command and table columns, how would that be implemented? Any help or advice is appreciated. MA
View Replies !
Importing Data From One Source In Two Destination Tables Linked By A Foreign Key
Hi, I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example : table MOTHER (MOTHER_ID, MOTHER_NAME) table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME) After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table. My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table". The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times... Is there a solution to chain two insertions with a foreign key constraint in a data flow? Thanks Regards, Arnaud Gervais.
View Replies !
Login Fails For Network SQL Server But Works For Localhost
I have an ASP.NET webform:This connection works: "Server=localhost;uid=sa;pwd=;database=pubs"but this connection DOES NOT work: "Server=dnrsqlt1;uid=sa;pwd=;database=pubs"dnrsqlt1 is a sql server my network.Do I have to do something to users ASPNET or IUSER_Machinename on the remote machine
View Replies !
Error When Using Configuration File For Source And Destination Connections In A Data Flow Task
Hi all, I have a package that does simple exporting from an excel sheet to a table. I used a Dataflow task with Excel Source and OLEDB Destination Components. And i created Package configurations for Source and Destination Components. After than when i execute the package i get the following error. Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLLPL_Config2.dtsConfig". Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLDBCon2.dtsConfig". SSIS package "ProductDetails_Import.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at ProductDetails_Import, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation. SSIS package "ProductDetails_Import.dtsx" finished: Failure. The program '[2416] ProductDetails_Import.dtsx: DTS' has exited with code 0 (0x0). I have been trying to troubleshoot the error message given below from last evening. I have been trying to troubleshoot the error from last morning. Counld not figure out what is causing this error to occur. Please help!!!! Any pointersSuggestions would be highly appreciated. Thanks & Regards
View Replies !
SQL Server Agent And X64 Using OraOLEDB.Oracle.1: Dtexec From Cmd Works, But Job Fails
I am extracting data from an Oracle database and have installed the latest x64 ODAC. When I run the 64 bit dtexec in cmd the package runs fine with no errors, but when creating and executing a SSIS job in the agent it fails. I've tried creating the job using CmdExec as well and I get the same errors: Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:12:43 AM Error: 2007-08-15 11:12:45.63 Code: 0xC0202009 Source: Load Dimension Data Connection manager "ora" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800703E6. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x800703E6 Description: "Invalid access to memory location.". End Error Error: 2007-08-15 11:12:45.65 Code: 0xC020801C Source: CopyCustomers CustomerSource [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ora" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-08-15 11:12:45.65 The CmdExec job step uses the _exact_ same command as the one I execute successfully in cmd: "c:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW I have managed to find two work arounds, but they are quite ugly: 1) Schedule the package execution using Windows Task Scheduler, basically create a bat file which runs the package. 2) Schedule the package in SQL Server agent, but as T-SQL script and use xp_cmdshell, i.e. EXEC xp_cmdshell 'dtexec /FILE "C:PackagesLoad Dimension Data.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW' Both work and use the 64 bit dtexec. Not that elegant though.. CmdExec and the 32 bit version of dtexec works, but is not good enough for me since we bought new hardware and 64 bit software just to be able to address more memory. Has anyone managed to execute a SSIS package successfully using the agent and the 64 bit OraOLEDB.Oracle.1? Any help would be greatly appreciated. Thanks! Btw, I am using Windows Server 2003 x64 and SQL Server with SP2.
View Replies !
How To Retrieve Connections Collection Inside Custom Data Flow Tasks (source/destination)
Hi, How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task. I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task. Any help appreciated. Thanks Naveen
View Replies !
SQL Server 2000 As Data Source For SSRS 2005 Express
Hi, We are in the process of upgrading a SQL Server 2000 database to 2005. For the mean time, we're trying it out on SQL Server Express 2005. My question is, can reports in SSRS 2005 pull data from SQL Server 2000 in case both are installed side by side in the same machine and the 2000 version is the default instance while the 2005 express edition is the named instance? Thanks a lot!
View Replies !
Cant Use ODBC Data Source On SQL SERver 2000 Sp3a Named Instance
Here's my situation:I have an ODBC DSN setup for Timberline Data (An accounting packagethat uses pervasive.sql) on my sql box. I set up a linked server usingthe supplied timberline odbc driver. I have two sql instances setup,the default instance and a named instance. On the default instance,the linked server works great no matter who is logged in using it (allauthentication is NT integrated). However on the named instance onlythe NT account that the MSSqlserver$NAMED service is logged in undercan utilize the linked server. All others get a ODBC error:"error 7399: OLE DB Provider 'MSDASQL' reported an error. AccessDenied. OLE DB error trace [OLE/DB Provider 'MSDASQL'IUnknown::QueryInterface returned 0x80070005: Access Denied.]."Far as i can tell, both instances are setup the same, except that oneis the default and one is a named instance.Why the different results for the default instance vs a named instance.Any ideas?Thanks
View Replies !
Import Text File Source Into SQL Server Destination
Hi all, I got a unicode file source with this fields: -DT_WSTR (100) originally is DT_STR(100) -DT_WSTR (100) originally is DT_STR(100) -DT_NTEXT -DT_WSTR (20) originally is DT_DBTIMESTAMP -DT_WSTR (5) originally is DT_BOOLEAN I export a Query result to a File (see above) ...as unicode TXT destination. OK, now I must to re-import into another DB and here is my difficult...'cause the DT_NTEXT is HTML code and I got always this error: [Flat File Source [1050]] Error: The column delimiter for column "scheda" was not found. Scheda field is the DT_NTEXT. Into connection manager area I modify the advanced tab for the set-up of my fields setting all to: Unicode string [DT_WSTR] with a variable of the len, but Try also to define everyone to the rigth type of the SQL destination like: - DT_STR(100) - DT_STR(100) - DT_DTNTEXT - DT_DBTIMESTAMP - DT_BOOLEAN In every type of action I see no message alert and all seem to be good, but when I try to execute got always same error... So hope someone can help me... ----------- here first line of my UNICODE TXT source file ---------- "codven" "manufacturer" "scheda" "last_modified" "modificata" "CDGI2120" "Altri" "<datasheet><section ncellmax="1" id="1"><row order="1"><cell><![CDATA[Combat possiede mitragliatrici per intraprendere battaglie testa a ~testa del genere "spara o sei finito" in mezzo a territori ~butterati di crateri su carri armati del 23esimo secolo.~Caratteristiche:~* Cinque modalita' di gioco~* Tre tipi di carri armati~* Partita singola o in multiplayer~* Grafica in 2D, 3D]]></cell></row></section></datasheet>" "2007-12-11 13:02:26.290000000" "1" "CDGI2586" "Disney Interactive" "<datasheet><section ncellmax="1" id="1"><row order="1"><cell><![CDATA[Entra con Tigro ed i suoi amici nel meraviglioso Bosco dei 100 Acri aiutalo a cercare il miele nella natura incantata di questo fantastico mondo! ~~Il giocatore vestirÓ i panni di Tigro, il simpatico e buffo amico di Winnie The Pooh, il quale dovrÓ raccogliere quanto pi¨ miele possibile, per rendere la festa di Winnie qualcosa di veramente speciale!!!]]></cell></row></section></datasheet>" "2007-12-11 13:02:26.290000000" "1"
View Replies !
SSIS Web Service To XML Source To SQL Server Destination Problem
Hi, I have an SSIS package which calls a web service and returns a Dataset object in the form of an xml file (it does this successfully - and stores it successfully). The Data Flow then picks up the file using an xml source task (in which use inline schema is specified) , and passes it to an SQL Server Destination task - for bulk load. I've checked the mappings and everything seems to be ok, the package gives no warnings or errors when run. The destination table is created on the SQL Server without problem, but at that stage the task finishes "successfully", without actually loading any table rows into the destination table. Also in the SQL Server Destination task - when I select "preview" in the connection manager section - I can see the column definitions, but again - no rows of data. Thanks to Simon Sabin for pointing me here - I did post on the managed msdn newsgroups in sql server programming but have had no replies as yet. Below is the beginning of the xml Dataset object which is output as an xml file for information. If you need any more information just let me know. Not quite sure where i'm going wrong here - thanks in advance Crispin. Code Snippet <?xml version="1.0" encoding="utf-16"?> <DataSet> <xs:schema id="callStatisticsDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchemahttp://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</A< A>>" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="callStatisticsDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Table"> <xs:complexType> <xs:sequence> <xs:element name="date" type="xs:dateTime" minOccurs="0" /> <xs:element name="callID" type="xs:int" minOccurs="0" /> <xs:element name="cli" type="xs:string" minOccurs="0" /> <xs:element name="itemClosed" type="xs:string" minOccurs="0" /> <xs:element name="custProdId" type="xs:string" minOccurs="0" /> <xs:element name="itemSaleId" type="xs:string" minOccurs="0" /> <xs:element name="phoneNumber" type="xs:string" minOccurs="0" /> <xs:element name="lastXml" type="xs:string" minOccurs="0" /> <xs:element name="followedPath" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"> <callStatisticsDataSet> <Table diffgr:id="Table1" msdata:rowOrder="0"> <date>2007-06-04T08:03:10.02+01:00</date> <callID>85560695</callID> <cli>648477292</cli> <itemClosed /> <custProdId>-1</custProdId> <itemSaleId /> <phoneNumber /> <lastXml /> <followedPath>Initialize #waitList;|tvShop.CallInitiated;|Is not closed?;|No item on sale;|Store Items;</followedPath> </Table>
View Replies !
DTS Import To SQL Server 2000 From Visual FoxPro
Greetings, I am unsure if this is the correct forum to send this question, but I can't seem to find any information regarding this problem. If this is the wrong place, please direct me to the correct spot. I am attempting to import data from a free tables FoxPro database to SQL 2000 using a DTS Package which has worked correctly every day for the past 2 years. Yesterday, I got an error. The package has around 10 tables that it deletes, re-creates, and populates with data from the Foxpro. All of the tables except one work correctly. When I try to do an explicit import using the ODBC connection to populate that one table, I get the following error: Context: Error calling Openrowset on the provider. I created an access database on my local computer and setup an ODBC connection and link tables to the database to see if it would work, and it did. So I thought there might be something wrong with the ODBC data source on the SQL Server, so I deleted it and created a new one, used it and I receive the same error. I thank you in advance for any assistance or direction you can provide me for finding an answer.
View Replies !
Inserting Image Data Fails With Connection Broken (SQL Server 2000 And SQL Native Client)
Dear all, we have tables with many image columns. We fill these image columns via ODBC and SQLPutData as described in MSDN etc (using SQL_LEN_DATA_AT_EXEC(...), calling SQLParamData and sending the data in chunks of 4096 bytes when receiving SQL_NEED_DATA). The SQLPutData call fails under the following conditions with sqlstate 08S01 - The database resides on SQL Server 2000 - The driver is SQL Native Client - The table consists e.g. of one Identity column (key column) and nine image columns - The data to be inserted are nine blocks of data with the following byte size: 1: 6781262 2: 119454 3: 269 4: 7611 5: 120054 6: 269 7: 8172 8: 120054 9: 269 The content of the data does not matter, (it happens also if only zero bytes are written), nor does the data origin (file or memory). All data blocks including no 7 are inserted. If the first chunk of data block 8 should be written with SQLPutData the function fails and the connection is broken. There are errors such as "broken pipe" or "I/O error" depending on the used network protocol. If data no 7 consists of 8173 bytes instead of 8172 all works again. (Changing the 4096 chunk size length does not help) Has anybody encountered this or a similar phenomenon? Thank you Eartha
View Replies !
SSIS Programmaing - How To Map Flatfile Source Columns To SQL Server Destination?
Hi, I am new to SSIS programming and trying to export data from a flatfile source to SQL server destination table dynamically. I need to get the table schema info (column length, data type etc.) from SQL server table and then map the source columns from flatfile to destination table columns. I am referring to one of the programming samples from Microsoft and another excellent article by Moim Hossain. Can someone help me understand how to map the Source columns to destination table columns depending on table schema? Please help. Thanks
View Replies !
Transfer Logins: The Source Server Can Not Be The Same As The Destination Server
I am replacing a server so I need to migrate everything. The old server is running SQL2000 and the new server is running SQL2005. I am trying to write an SSIS solution to migrate everything for me and I can't even get started because I get the error "The source server can not be the same as the destination server". At the same time I am changing the name of the Domain so the two machines arenot even members of the same Domain. I am doing this over the Internet so the machines are not even on the same subnet. The only thing I can think of is that the machine names are the same so even though the domains are different therefore the full names are different, the NetBIOS names are the same. Could that be the problem?
View Replies !
Transfer Login: Source Server Can Not Be Same As Destination Server
Hi! I have two servers. Server A and Server B Both the servers are in different domains and both have different names. Server A runs a clustered sql server (sql 2005 standard sp1) Server B also runs a clustered sql server (sql 2005 enterprise sp1) Both server A and Server B has same virtual server name X. So, when I try to copy all the logins from server A to Server B using Transfer Login task in ssis, I gert the following error- "Source Server Can not be same as destination server" I am using IP addresses to connect to both the servers. How do i solve this issue now? Thanks
View Replies !
SQL Server 2000 Linked Server To Foxpro (deleted Records)
Hello, I have a problem retrieving records from a foxpro data table through a linked server in sql 2000. When querying a table it returns not only live records but deleted records as well. Creating a local DSN and setting the Deleted option allows me to query the table through MS Query and return only live records but I cannot get it to work through linked server. Any ideas?
View Replies !
SQL 2000 To SQL 2005 Works For One 2000 Server But Not The Next
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is: SQL Server 2000 as Publisher/Distributor SQL Server 2005 Standard as Subscriber The connection is via the Internet with snapshots using FTP. I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5. II am setting up the same scenario for location B. Here is my problem: In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next HERE IS THE PROBLEM: On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one. I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine). WHAT'S DIFFERENT: Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window). The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result. ANY IDEAS?
View Replies !
Transfer Data From Foxpro To Sql Server
i have an old database in foxpro. The table in foxpro now has been broken into more than tables in sql server 6.5 . how do i append the data to sql server database to the respective tables from the foxpro database. vineet
View Replies !
Foxpro To Sql Server Data Conversion
Hi everyone, I have worked soley with Sybase for almost ten years! Now I have been tasked with converting a bunch of data currently in a FoxPro database to a Microsoft SQL Server database. Short of writing some routines myself (which I don't mind doing), is there a shortcut for doing this? Any specifics would be great as it seems this could be mind boggling as far as dates go and so many other things! Thanks so much! Rachel
View Replies !
DTS Works But Job Fails
Hi I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails. SQL Server agent is running with same account "xxx" DTS connects to SQL Server with sa authentication Job owner is same account "xxx" Job error log Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed. I copied the DTS to another one and scheduled it This time I got the error log Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: 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. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed. Please help! Thanks in advance
View Replies !
Sql Job Fails But When Run Outside Works Fine
Hi.. I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error Error: 2008-03-24 13:52:40.22 Code: 0xC0202009 Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR" 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.". I am able to run the package outside the sql job and also connect to the oracle. I have oracle 9i client installed on the server and sql server is 2005. Any help would really be appreciated..
View Replies !
Synchronizing Foxpro .DBFs Data With SQL Server Tables (realtime)????
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis? I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis. Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink). Thanks for any and all assistance..... Dave
View Replies !
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared. Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits. I will do a 1 time DTS from FP into SQL Server tables. I then create INSERT and UPDATE triggers within FoxPro. These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there. In the end - the tables are local to both apps. If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed. Here's the FoxPro and SQL Server code for reference for the Record Insert: FOXPRO employee.dbf InsertTrigger: employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone) FOXPRO corresponding Stored Procedure: FUNCTION EMPLOYEE_INSERT_TRIGGER PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE nhandle=SQLCONNECT('SS_PDITHP3','userid','password ') IF nhandle<0 m.errclose=.f. IF !USED("errorlog") USE tisdata!errorlog IN SELECT(1) m.errclose=.t. ENDIF SELECT errorlog INSERT INTO errorlog (date, time, program,source,user) ; values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username")) IF m.errclose USE IN errorlog ENDIF RETURN ENDIF nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0" nsucc=SQLEXEC(nhandle,nquery) SQLDISCONNECT(nhandle) IF nSucc<0 m.errclose=.f. IF !USED("errorlog") USE tisdata!errorlog IN SELECT(1) m.errclose=.t. ENDIF SELECT errorlog INSERT INTO errorlog (date, time, program,source,user) ; values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username")) IF m.errclose USE IN errorlog ENDIF ENDIF RETURN SQL SERVER Stored Procedure called from FOXPRO Stored Procedure CREATE procedure ewo_sp_insertNewEmployee ( @WEPK int, @WEFNAME char(20), @WELNAME char(20), @WEEMAIL char(50), @WEUSERID char(15), @WEPHONE char(25), @RETCODE int OUTPUT ) AS insert into WO_EMP ( WE_PK, WE_FNAME, WE_LNAME, WE_EMAIL, WE_USERID, WE_PHONE ) VALUES ( @WEPK, @WEFNAME, @WELNAME, @WEEMAIL, @WEUSERID, @WEPHONE ) IF @@ERROR <> 0 BEGIN SET @RETCODE=@@ERROR END ELSE BEGIN -- SUCCESS!! SET @RETCODE=0 END return @RETCODE GO
View Replies !
Query Works - Sproc Fails
I have a query that works fine but fails as a sproc. QUERY: SELECT UserName, ProfileId, FirstName, LastName FROM dbo.CustomProfile JOIN dbo.aspnet_Users ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId WHERE UserName = 'Brown' SPROC: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetProfileId] @UserName nvarchar AS SELECT UserName, ProfileId, FirstName, LastName FROM dbo.CustomProfile JOIN dbo.aspnet_Users ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId WHERE UserName = @UserName The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0
View Replies !
SELECT Works But UPDATE Fails. ?
This statement failsupdate ded_temp aset a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)With this error:Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'a'.But this statement:select * from ded_temp awhere a.balance = (select sum(b.ln_amt)from ded_temp bwhere a.cust_no = b.cust_noand a.ded_type_cd = b.ded_type_cdand a.chk_no = b.chk_nogroup by cust_no, ded_type_cd, chk_no)Runs without error:Why? and How should I change the first statement to run my update. Thisstatement of course works fine in Oracle. :)tksken.
View Replies !
SQL DTS Job Fails Where Execute Of Package Works?
I have a SQL DTS package that imports a text file as one wide column into a two column SQL table, one is an identity seed. So essentially I import then parse the data using the index key as it is 3 seperate lines of data. All works fine by running the DTS package using execute. When I schedule this as a job it fails. It indicates that I have additional white space after a column. Any thoughts on why this can ocurr?
View Replies !
Scheduled DTS Fails, Manual Works. Help!
Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results. My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info. I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!
View Replies !
DTS Execute Works, Schedule Fails
Hey all, I have created a DTS task that i can right click on and execute and it works fine on the server. However when i try to schedule the task and run the job from SQL Server Agent, i get the following error. Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31 the error occurs on the following line Set MySite = CreateObject("CuteFTPPro.TEConnection") How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server. thanks in advance, pete
View Replies !
DB Access Works In Debug But Fails When Hosted
I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails. What could I doing wrong could I be missing a setting in IIS? Any ideas??Here's my webconfig if that helps at all: <?xml version="1.0"?><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <connectionStrings> <add name="studentprofilesConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <roleManager defaultProvider="AspNetWindowsTokenRoleProvider" /> <compilation debug="true" defaultLanguage="c#" /></system.web></configuration>
View Replies !
|
TRACKER
