My DTS package for importing data from oversea's database fail becasue it use many time ( more 2 hours ) . It will be complete When the run duration was used less than 2 hours and fail when the run duration was used more than 2 hours . If we eleminate to improve network , there is any way to do ?
I try to import data from other database ( that data base is in other country ) and insert into my table .It's succeeded normally but a few day ago it sometime fail .It's seem the data were selected but can't insert into my table . I have already checked filed name and field properties of both source and destination table .It''s same .The error message is below.
Executed as user: SCTINET1sqlservice. ...t: Create Table TBL_ASCP_PLAN Step DTSRun OnFinish: Create Table TBL_ASCP_PLAN Step DTSRun OnStart: Copy Data from Results to TBL_ASCP_PLAN Step DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: Copy Data from Results to TBL_ASCP_PLAN Step; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProg... Process Exit Code 1. The step failed.
Hi all, need help before i break this pc! trying to get an import job to read from an excel file. Normally this works fine, no issues but have a certain excel file that is just not importing correctly. one row is importing nulls for some values but without any visible reason. I have a file with over 15000 rows. 9 columns. last column stores a year in the format yyyy. this is the problem column. in the import job it shows up as a float. have checked the format of the cell and it says General in the excel file. when i execute the job over 5000 row come throught with null Years. cant see a reason for this. anyone able to shed some light please..
The import from Flat File Source fails: Error 0xc02020a1: Data Flow Task 1: Data conversion failed.
The data conversion for column "ArticleName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
I have changed the size of the column "ArticleName" (varchar) to max but the error comes up again.
The data i want to import came with multiple flat files. They all could import properly but this one is a problem.
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification.
Could you please look into this and guide me Thanks in advance venkatesh imtesh@gmail.com
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Public function get_field() as string Dim myConnection As System.Data.SqlClient.SqlConnection Dim myCommand As System.Data.SqlClient.SqlCommand Dim data_reader As System.Data.SqlClient.SqlDataReader Dim field(100) as string Dim i as integer Dim j as integer Dim sql_field as string Dim nbr_field as integer Dim rtn_string as string
i = 0 sql_field ="Select field from mytable" myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;") myConnection.Open() myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection) data_reader = myCommand.ExecuteReader() While data_reader.Read() if data_reader.HasRows then field(i)= data_reader(0).ToString() end if nbr_field = nbr_field + 1 i= i+1 End While data_reader.Close() myConnection.Close()
for j = 0 to nbr_field -1 rtn_string = rtn_string + field(j) + "," Next j
rtn_string = left(rtn_string,rtn_string.length-1) return rtn_string 'return sql_cmd 'return yes_no 'return lkupfield end function
I am likely to work on moving our ETL system from SQL server 2000 to SQL server 2005. Code is being re written. I wanted to know from anyones previous experience like what kind of activity/data can cause a package to fail. I need to check that a certain package fails and logs error to the server error log and stops. And also check that a certain package fails, logs an error to the server error logs but continues to run.
Any thoughts in this regard would be very helpful for me.
I've got a question about failover with the database mirroring.
I have 3 servers, 2 with SQL 2005 and 1 with SQL Express as witness.
After getting some problems to install database mirroring with Windows authentification, i had to create an authentification by certificate.
All goes well but my problem is about my primary instance SQL alias.
The alias on the primary doesn't follow on the secondary when the primary is down.
I've searched on forums and in the internet SQL web sites, but i didn't find anything.
I create another alias on the secondary but i don't know how to modify it in T-SQL...
I would like to know if there's any solution to modify an SQL instance alias by transact-SQL? or if anyone has already install on its servers, a fail over automatic with one instance alias with data mirroring in T-SQL?
The following statement fails when using SQL Analyzer under sa but works on all of our development and staging server. All are SQL Server 2005 SP1. We upgraded production over the weekend from SQL Server 2000, creating a new instance machinenameSQL2005.
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:data est.xls',
'SELECT * FROM [Sheet1$]')
The error we are getting only in prodcution is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source. I select the SQL Command in the Data Access Mode, then use:
USE [SecurityMaster] EXEC [dbo].[smf_ListEquity]
It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:
Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider. Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.
to create a code. Hi everyone, I have SQL SERVER 2005 on my desktop and i need to run queries from remote machines on my SQL SERVER. I thought it would be a good i idea to create a web page on my machine to where remote users can contact and activate SQL QUERIES. My question is very simple and i wonder i a simple answer is available: How do i run SQL queries whereby ASP code ? How do i make a connection between a web page (.asp code) and a SQL sever ? If the answer is complicated as i'm afraid it is, could any one provide me a link for a tutorial dealing with the above issues ? Thanks a lot !
Hello, I have an urgent need for a DBA who knows SQL server 6.5 and 7 very well. Also if you have any ASP experience this would be music to my ears. Please give me a call to find out more 02 9221 6601 Australia.
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
These two queries when executed seperately give results in under 10 secs A union between these two does not give results even after 20 minutes ... Any idea why this is happening SELECT T001W.NAME1, t25a5.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a5.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT= MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3) AND MARA.PRDHA <> '' AND MKPF.VGART IN ('WR','WF') AND MKPF.MJAHR=YEAR(@BUDAT1) AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP SELECT T001W.NAME1, t25a2.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a2.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT=MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3) AND MARA.PRDHA <> '' AND MKPF.MJAHR=YEAR(@BUDAT1) AND MKPF.VGART IN ('WR','WF') AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRP
Hello Friends, 1) I have a table and column names are Executiveid companyname positiontitle start date enddate I want to see all executives who worked for more than 1000 days in one job. I am not sure but may be i need to use datediff function.
i have 3 tables and i have to use joins table 1 executive executiveid firstname lastname companyid
table2 company companyid companyname sales currencycode
table3 currency currencycode description
how am i supposed to join 3 tables. i know how to give joins on 2 tables.
hi all , i am working on a migration project of database from access 2003 to sql server 2000 but facing a problem in converting queries from access to sql server , is there any way or tool through which i can convert the queries to sql server format Thanks in advance
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
i have created my sql mdf file now i was tring to create sql script for the same how do i do that? script to create databse and tables in object explorer i right clicked on my databse file name
selecdted tasks and then generate script after that i clicked on next button
Hi, My site (in VB.NET) has developed a intermediate timeout problem. System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Can someone tell me what could cause this intermediate problem. Is it something wrong with the query? It is definitely the problem from the query as I have 2 queries using same design and both of them develop similar problem. Pleaee help as our site will have major advertising next month so need to fix it by then. My server is running Win2003 on SQL 2000. I have shown the cut-down version of the original query: ***************************************************************CREATE PROCEDURE dbo.sp_getmyitems(@CurrentPage tinyint,@PageSize int,@TotalRecords int OUTPUT)AsSet NoCount ON Declare @FirstRec intDeclare @LastRec intSet @FirstRec = (@CurrentPage - 1) * @PageSizeSet @LastRec = (@CurrentPage * @PageSize + 1) Create Table #TempTable(LocalItemID int IDENTITY PRIMARY KEY,ItemID int,Title varchar(250),) Insert Into #TempTable(ItemID,Title)SELECT ItemID, TitleFROM tbl_Items Select ItemID,TitleFrom #TempTableWhere LocalItemID > @FirstRec AND LocalItemID < @LastRec Select @TotalRecords = Count(*) FROM #TempTableGO **************************************************************Thank you
4 days ago we had a Server crash luckily we have the daily full database backups...Since the server which was crashed is unusable I installed SQL 7.0 on another machine...I tried to restore databases from our backup tapes...Here is the T-SQL I am running:
restore database pentatel from tape='.ape0' with file=2,move 'PentaTel_dat' TO 'D:mssql7datapentatel.mdf', move 'pentatel_log' to 'D:mssql7datapentatel.ldf'
The error message I am getting is:
Processed 8992 pages for database 'pentatel', file 'PentaTel_dat' on file 2. Processed 1 pages for database 'pentatel', file 'PentaTel_log' on file 2. Server: Msg 9004, Level 21, State 1, Line 1 The log for database 'pentatel' is corrupt.
Connection Broken
After these when I look at the SQL Enterprise manager I see that the database is in loading status...I left it in that status for hours but there was no reaction...So I tried to detach and attach a single file.Here are the TSQLs and Error messages...
Server: Msg 945, Level 14, State 2, Line 1 Database 'pentatel' cannot be opened because some of the files could not be activated. Server: Msg 1813, Level 16, State 2, Line 1 Could not open new database 'pentatel'. CREATE DATABASE is aborted.
We have 7 backups and I tried all of them...Same error messages...
When I try to change the status from loading to emergency or normal the database is always inaccessible
I are running these statements
exec sp_configure 'allow',1 reconfigure with override update sysdatabases set status=32768 where db_id=6
and I stop and start the Mssql7 service but still inaccessible
Any idea???
I despretaly need help...Any recomendation is greatly apreciated...
Hi,I urgently need help with this. I am trying to implement custom paging with Sql 2000. However I have ran into a few problems.I really hope that someone would be able to help me out. I need to get this done by tomorrow!! (I know I screwed up big time, but I didn;t relize that built in paging fetches all the records, and the displays it page by page!!) 1) Currently the paging seems to be working. However the primary key always resets itself. For example, lets say I have 4 records ID 1,2,3,4When I delete ID 3, instead of displaying ID 1,2,4 it shows ID 1,2,3. Is there any way to solve this problem?2) The database that I am currently creating will have about 40,000 records, is it advisible for me to use your control (I hope so)?Thank you and do have a nice day.http://www.codeproject.com/aspnet/ASPNETPagerControl.asp CREATE PROCEDURE [dbo].[GetPagedProducts11] ( @PageSize int, @CurrentPage int, @ItemCount int output ) AS Declare @UpperBand int, @LowerBand int
-- Get The Count Of The Rows That They Meet the Criteria SET @ItemCount = (SELECT COUNT(*) FROM aduan)
-- Calculate the @LowerCount and @UpperCount SET @LowerBand = (@CurrentPage - 1) * @PageSize SET @UpperBand = (@CurrentPage * @PageSize) + 1
-- INSERT ALL THE Rows that meets the Criteria INSERT INTO #AllRows SELECT ad_nama,ad_tarikh, ad_status,ad_title FROM aduan
-- AND finally select and return desired -Paged- Rows SELECT ad_id, ad_nama, ad_tarikh, ad_status,ad_title FROM #AllRows WHERE ad_id > @LowerBand AND ad_id < @UpperBand
On one of our servers the sqlseragent was running fine. But now it is not running. We se the option to start the server agent each time the OS starts. Even when I tried to start the agent manually from service manager it is not starting. Would any one help on this urgently.
Also how can we identify under what account any job runs. The reason is when we deleted some NT user accounts one of the job failed as this job runs under that userid. But in the properties it shows that sa is the owner of that job. What is best way to create a job so that these jobs run under one userid so that it won't fail when we delete users.
Hi, I'm building a SSAS Integration project, it consist of extracting data from a Microsoft Access 2003 database and consolidate data into a SQL Server 2005 database for datawarehousing. I need to make several operations for integration, but i'm struck in the following:
- I added an Ole DB Origin that connects to my access database - I made an sql query with multiple joins that extract all data i need for first instance. - in data i extracted, i got one column named: FECHA (and many other colums), it contains data of the date of the actual sale; but in previous steps i fill a table named DimTiempo from my sql server database that contanins all the dates (with no repeating) of the sales and added an unique primary key for them; so, now i need to get that primary key for each column FECHA i gotted in my sql query in OLEDB ORIGIN, for example:
I have: 20/20/2004 (column FECHA on my OLEDB ORIGIN gotted with an sql query with multiple joins) I need: 001 (extracted from DimTiempo, its the primary key of the registry 001 - 20/20/2004)
I need that code or primary key to insert into my new table on my sql server database because its related directly with my DimTiempo table filled previously.
For more directions, i don't have that primary key column on the original access data base; i reestructure completely the database on sql server to make the datawarehouse work fine.
Please, i hope you help me, tell me what do i need to do to get all the data to fill my sql server 2005 database.
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
hi Friends... I have got a ordermaster table in which the orderid field is autoincremental.Now I also have an Ordercode field which is a combination of [current (orderid)-LICON)] eg 1-LICON .. I achieve this using the following query ... insert into ordermaster(ordercode,orderamt,orderdate) select cast(max(orderid)+1 as varchar)+'-LICON' as ordercode,1000,'3/6/2004' from ordermaster
But I think the above query could lead to an inconsistent state ....If say following occurs :
1) USER1 reads max orderid as 1 2) User2 also reads max orderid as 1 3)USER 1 inserts record with ordercode as 2-LICON. 4)USER 2 will also insert record for ordercode as 2-LICON since max orderid read by USER2 is also the same as USER1.
How do I avoid this....I know I there are LOcktypes. But aren't locktypes used when we are updating same records....
Please help me out .I really need help in ths context
We have a BIG problem that has been occurring for quite sometime. We have a RAGGED-RIGHT FFS (FlatFileSource) component which receives a FF and then transforms to XML. We define 10 FIELDS with the last field as {CRLF} per ragged right style. Simple right? The source file is sent to us from multiple separate groups, some of which don€™t use the last two fields and have early line termination, so they basically are not there. The problem is that when we define X amount of fields in SSIS, it expects X amount of fields to be there. So when we receive the source file that doesn€™t contain last two fields, SSIS tries to read in the next line, and in most cases successfully places the fields from the next line into the missing fields. Now what we end up with is a source file with 10 rows, but only half successfully transformed, with every other row fitted into the last two XML fields. SSIS completely ignores the {CRLF} until those specific numbers of spaces/fields have been met €“ even if it has to go onto the next line to get them. This seems horribly wrong to me. I would think that at the very least there should be some type of validation that prevents it from grabbing the next line. I thought that is what fixed-length SSIS style is for, and ragged right for allowing to parse multiple rows. We could enforce all clients to produce X char spaces (and we do), but there can be cases in any aspect where a file could get accidentally sent over with a shortened row. I have heard from certain individuals that SSIS adapter wasn€™t designed with this in mind. Is this true, I am to believe that this would still get parsed without throwing any errors and potentially damage the ERP system it is going into? If this is the case then I feel extremely disheartened about SSIS €“ especially with all of the great advancements it has made.
We desperately need a workaround or at the very least some way to validate that a new row {CRLF} isn€™t getting picked up in the first row of the flat file connection. Please can someone help ASAP!?
i think i have serious problem. i have almost 20,000 rows with the following conflict on a table (generated by merge replication over a period of around 3 weeks) :
'The row was updated at 'SubscriberServer3.DatabaseA' but could not be updated 'at 'PublicationServer1.DatabaseA'. Metadata mismatch'
i have 20 remote subscribers & 1 publisher. The above message originates from practically everywhere.
Besides dropping(not practical at the moment) & recreating replication to get rid of them, what else can i do to resolve this problem?? Do i have to resolve this problem row by row?? i'm at my wit's end. Please help!!