Data Errors Querying To .xls
Jun 29, 2007
Hi all, I've just started playing with data importing from Excel.
Since I know how to do linked servers, that's where I started. I created a link, querried the spreadsheet and got some strange results where data appearing in the spreadsheet returns as NULL in QA.
So, I noticed that I could use OPENDATASOURCE and OPENROWSET and tried each of them. They're returning the same data (in different column orders) also with strangely intermittant NULL values instead of the data that I see in the source spreadsheet.
I bet that this is something easy once you know what's going on. Anyone know what's going on?
Not that I think it'll matter, but the queries I'm using for these three cases are:
select * from EXCEL_A...EPIQ202$ where SUBSCRIBER_ID = '50664582103'
select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:EPIQ202.xls;Extended Properties=Excel 8.0')...EPIQ202$
select*
from
openrowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:EPIQ202.xls',
'SELECT * FROM [EPIQ202$]'
)
Thanks a bunch for your time,
Chris
View 2 Replies
ADVERTISEMENT
Jan 24, 2004
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
parent | child
1 | 2
2 | 3
2 | 4
3 | 6
3 | 7
4 | 8
5 | 9
8 | 10
8 | 11
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Thanks.
Jon
View 20 Replies
View Related
Aug 20, 2007
Is there a way to query the name of the physical data file and/or log file associated with a given database. For example if I were to do an alter database:
ALTER DATABASE X
MODIFY FILE
(NAME = xxxxx,
size=10MB)
Is there something I can type in to extract the file name associated with 'X' or do I have to know it implicitly?
View 3 Replies
View Related
May 10, 2007
Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax for retrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this:
'Create a New Connection to our daabase
Dim test As SqlDataSource = New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ToString
'This is the SQL Insert Command
test.InsertCommand = "Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@Initial,@Correspondent_Name,@Correspondent_No,@Payment_Type)"
'Each of this insert a value into the appropriate command
test.InsertParameters.Add("Initial", DropDownList4.SelectedValue) 'This is the selected "Initial" DropDownList value
test.InsertParameters.Add("Correspondent_Name", TextBox10.Text) 'This is the Correspondent_Name value
test.InsertParameters.Add("Correspondent_No", TextBox3.Text) 'This is correspondent_no value
test.InsertParameters.Add("Payment_Type", "Pay upon delivery") 'This indicates that the payment is made with credit card
test.Insert()
But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!
Regards,
Ivan
View 2 Replies
View Related
Oct 5, 2013
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
View 2 Replies
View Related
Jul 23, 2005
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
View 1 Replies
View Related
Sep 19, 2006
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
View 1 Replies
View Related
Feb 26, 2015
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
View 1 Replies
View Related
Aug 2, 2007
I am working on a project, which involves displaying trends of certain aggregate values over time. For example, suppose we want to display how the number of active and inactive users changed over time.
One issue is how to store historical data. First of all, should I create a separate database for each historical snapshot or should I use one database for all snapshots? Second, our database size is a couple of gigabytes and replicating the entire database on a daily basis is not feasible. An alternative solution is to back up aggregate values, but how do I back up results of aggregate queries, where the user can specify a date range in the WHERE-clause? Another solution is to create fact tables from our relational schema and back those up.
Another issue is how to query historical data. Using multiple databases to store historical snapshots makes it harder to query.
As you can see there are several design alternatives and I would like to know how this sort of problem is generally solved in the industry. Does SQL Server provide any support for solving this problem?
Thanks.
View 5 Replies
View Related
Jun 24, 2015
Below is the table information. I want the Code information with latest EDate only.
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Expected output :
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22
View 13 Replies
View Related
May 2, 2015
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
View 2 Replies
View Related
May 2, 2007
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error:
Msg 7372, Level 16, State 4, Line 1
Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723
Linked Server Script:
/****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120'
GO
EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true'
I've tried searching the Internet for solution but yielded no results. Can anyone help?
View 3 Replies
View Related
Aug 3, 2015
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
View 9 Replies
View Related
Oct 12, 2015
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML
SET @XMLVAR = '<?xml version="1.0"?>
<POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME
,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER
FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
View 1 Replies
View Related
Nov 8, 2006
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
View 2 Replies
View Related
Oct 12, 2015
I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
View 6 Replies
View Related
Aug 3, 2007
HI,
Periodically, I receive these errors in my package:
The "component "Set date to NULL or format" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SettlementDate - derived" (544)" specifies failure on error. An error occurred on the specified object of the specified component.
It has to do with the fact that I have a derived column that uses an expression to format a date field. The expression is as follows:
ISNULL(TRIM(SettlementDate)) || TRIM(SettlementDate) == "" || LEN(TRIM(SettlementDate)) < 8 || TRIM(SettlementDate) == "89999999" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(SettlementDate,5,2) + "/" + SUBSTRING(SettlementDate,7,2) + "/" + SUBSTRING(SettlementDate,1,4))
It looks complicated, but it isn't. Basically, I'm just checking to see if the date value is NULL, a blank, less than 8 chars, or if it equals 899999999. If it is, set the date to null.
Anyways, the problem is, that the date field value changes sometimes. That is, I thought I covered the problem by checking for 89999999, but apparently other values are showing up as well, and when this happens it breaks the package.
How can I account for varying values, without having to know in advance what they are? Apparently, this problem is going to continue.
Thanks much
View 5 Replies
View Related
Oct 13, 2015
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
View 5 Replies
View Related
Aug 30, 2002
I get the following error when I run a job created by a maintenance plan that
runs a database backup.
[2] Database OurData: Database Backup...
The backup was not performed since data verification errors were found.
Any input on how to correct this is appreciated.
Thanks
View 1 Replies
View Related
Jul 6, 2005
Vimal Kanth writes "I am using SQL Server 2000 and one of my databases has loads of consistency errors. When i try to query a particular record from a table it displays an error message similar to this
Could not find the index entry for RID '168927001232500300' in index page (1:95510), index ID 0, database 'Tristar'.
Then, i executed the DBCC CHECKDB command after going through some documentation. The results of which were something like this
Table error: Database 'Tristar', index 'DMS_VOUCHER.index_1739153241' (ID 1739153241) (index ID 2). Extra or invalid key for the keys:
Index row (1:11229:144) with values (ROWGUID = 22814645-5B2B-4B4E-A1D8-9B49DF7C8CEF`¢0; and DEALER_ID = 10121 and BRANCH_ID = 1.242260474E-307 and VOUCHER_ID = 9507) points to the data row identified by ().
Then, i executed the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command. I lost around 3000 records when i excuted this
I dont want any data loss as this is very critical.Can you please help me overcome this problem? Thanks in advance.
Regards
Vimal Kanth"
View 20 Replies
View Related
Apr 27, 2006
Can anyone please tell me why I recieved these errors when running dbcc check db. Thank god it was a user created "test" table in a production database and I was able to just drop the table and rerun dbcc with no errors, but this is the second time this has happened in the last month(I just started this job the week of the first error)- and we ended up having to restore to the last good backup.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652192), slot 3, text ID 225434796032 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652192), slot 4, text ID 225434861568 is not referenced.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1543676547, index ID 255: Page (1:652193) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1543676547, index ID 255, page (1:652193). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 0, text ID 225434730496 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 1, text ID 225434927104 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 2, text ID 225434992640 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 3, text ID 225435058176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 4, text ID 225435123712 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 5, text ID 225435189248 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 6, text ID 225435254784 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652194), slot 7, text ID 225435320320 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652195), slot 0, text ID 225435385856 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652195), slot 1, text ID 225435451392 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652195), slot 2, text ID 225435516928 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652195), slot 3, text ID 225435582464 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1543676547. The text, ntext, or image node at page (1:652195), slot 4, text ID 225435648000 is not referenced.
DBCC results for 'BILL_PRT_DETAIL'.
There are 909154 rows in 101409 pages for object 'BILL_PRT_DETAIL'.
DBCC results for 'BILLG_ERR_MSGS'.
There are 971 rows in 52 pages for object 'BILLG_ERR_MSGS'.
DBCC results for 'WORK_COMPCY_LEVEL'.
There are 0 rows in 0 pages for object 'WORK_COMPCY_LEVEL'.
DBCC results for 'BILL_PRT_HEADER'.
There are 196396 rows in 24550 pages for object 'BILL_PRT_HEADER'.
DBCC results for 'WORK_COMPCY_TYPE'.
There are 0 rows in 0 pages for object 'WORK_COMPCY_TYPE'.
DBCC results for 'BILL_PRT_MSGS'.
There are 0 rows in 0 pages for object 'BILL_PRT_MSGS'.
DBCC results for 'MTR_TMP_DTA_SEL'.
There are 0 rows in 0 pages for object 'MTR_TMP_DTA_SEL'.
DBCC results for 'WORK_IN_PROGRESS'.
There are 1 rows in 1 pages for object 'WORK_IN_PROGRESS'.
DBCC results for 'BILL_PYMT'.
There are 750053 rows in 19951 pages for object 'BILL_PYMT'.
DBCC results for 'NOTES'.
There are 26812 rows in 457 pages for object 'NOTES'.
DBCC results for 'WORK_ORDER_MASTER'.
There are 0 rows in 0 pages for object 'WORK_ORDER_MASTER'.
DBCC results for 'BUD_BILLG_AMT'.
There are 0 rows in 0 pages for object 'BUD_BILLG_AMT'.
DBCC results for 'NOTES_RELSHP'.
There are 22428 rows in 252 pages for object 'NOTES_RELSHP'.
DBCC results for 'WO_ACT_STAT'.
There are 0 rows in 0 pages for object 'WO_ACT_STAT'.
DBCC results for 'BUD_BILLG_CNSM_HST'.
There are 0 rows in 0 pages for object 'BUD_BILLG_CNSM_HST'.
DBCC results for 'NXT_NBR'.
There are 7 rows in 1 pages for object 'NXT_NBR'.
DBCC results for 'WO_ADDTNL_INFO'.
There are 0 rows in 0 pages for object 'WO_ADDTNL_INFO'.
DBCC results for 'BUD_BILLG_HST'.
There are 0 rows in 0 pages for object 'BUD_BILLG_HST'.
DBCC results for 'PARENT_HST'.
There are 0 rows in 0 pages for object 'PARENT_HST'.
DBCC results for 'WO_APPVL_ROUTING'.
There are 0 rows in 0 pages for object 'WO_APPVL_ROUTING'.
DBCC results for 'BUD_BILLG_WTHR_HST'.
There are 0 rows in 0 pages for object 'BUD_BILLG_WTHR_HST'.
DBCC results for 'PHY_ADDR'.
There are 70776 rows in 7905 pages for object 'PHY_ADDR'.
DBCC results for 'WO_BEHAVIOR_RULE'.
There are 0 rows in 0 pages for object 'WO_BEHAVIOR_RULE'.
DBCC results for 'BUD_BILLG_WTHR_SEV'.
There are 0 rows in 0 pages for object 'BUD_BILLG_WTHR_SEV'.
DBCC results for 'POSTAL_TOWN_CD'.
There are 29539 rows in 569 pages for object 'POSTAL_TOWN_CD'.
DBCC results for 'WO_COMPONENTS'.
There are 0 rows in 0 pages for object 'WO_COMPONENTS'.
DBCC results for 'BUS_UNT'.
There are 1310 rows in 25 pages for object 'BUS_UNT'.
DBCC results for 'cash match'.
There are 738871 rows in 6720 pages for object 'cash match'.
DBCC results for 'POSTED_HISTORY'.
There are 1230436 rows in 26768 pages for object 'POSTED_HISTORY'.
DBCC results for 'WO_CONSTANTS'.
There are 0 rows in 0 pages for object 'WO_CONSTANTS'.
DBCC results for 'BUS_UNT_MSTR'.
There are 0 rows in 0 pages for object 'BUS_UNT_MSTR'.
DBCC results for 'Cashmatch7_30'.
There are 727509 rows in 6619 pages for object 'Cashmatch7_30'.
DBCC results for 'PROFILE_CAT'.
There are 0 rows in 0 pages for object 'PROFILE_CAT'.
DBCC results for 'WO_EDIT_RULE'.
There are 0 rows in 0 pages for object 'WO_EDIT_RULE'.
DBCC results for 'CNSMPTN_SVC_CONTN'.
There are 36776 rows in 928 pages for object 'CNSMPTN_SVC_CONTN'.
DBCC results for 'Trace_cash_match'.
There are 815011 rows in 7412 pages for object 'Trace_cash_match'.
DBCC results for 'PROOF_BILL_PRT_DTL'.
There are 0 rows in 0 pages for object 'PROOF_BILL_PRT_DTL'.
DBCC results for 'WO_MASTER_FILE'.
There are 0 rows in 0 pages for object 'WO_MASTER_FILE'.
DBCC results for 'cash match 8-24'.
There are 815011 rows in 7414 pages for object 'cash match 8-24'.
CHECKDB found 0 allocation errors and 17 consistency errors in table 'cash match 8-24' (object ID 1543676547).
DBCC results for 'PROOF_BILL_PRT_HDR'.
There are 0 rows in 0 pages for object 'PROOF_BILL_PRT_HDR'.
DBCC results for 'CNVRGT_BILLG_DTL'.
There are 0 rows in 0 pages for object 'CNVRGT_BILLG_DTL'.
DBCC results for 'WO_RTG_APPVL_MSTR'.
There are 0 rows in 0 pages for object 'WO_RTG_APPVL_MSTR'.
DBCC results for 'PYMT_TERMS'.
There are 2 rows in 1 pages for object 'PYMT_TERMS'.
DBCC results for 'CNVRGT_BILLG_HDR'.
There are 0 rows in 0 pages for object 'CNVRGT_BILLG_HDR'.
DBCC results for 'WO_STAT'.
There are 0 rows in 0 pages for object 'WO_STAT'.
DBCC results for 'Cash Matching Process'.
There are 836838 rows in 7612 pages for object 'Cash Matching Process'.
DBCC results for 'RD_CYCLE'.
There are 55 rows in 1 pages for object 'RD_CYCLE'.
DBCC results for 'WO_STAT_RULES'.
There are 0 rows in 0 pages for object 'WO_STAT_RULES'.
DBCC results for 'REASON_CD'.
There are 0 rows in 0 pages for object 'REASON_CD'.
DBCC results for 'WO_TYP'.
There are 0 rows in 0 pages for object 'WO_TYP'.
DBCC results for 'COLCT_RULES'.
There are 30 rows in 1 pages for object 'COLCT_RULES'.
DBCC results for 'RECEIPTS_BTCH'.
There are 2109 rows in 44 pages for object 'RECEIPTS_BTCH'.
DBCC results for 'nightly107'.
There are 344086 rows in 3225 pages for object 'nightly107'.
DBCC results for 'WO_TYP_ACT_STAT'.
There are 0 rows in 0 pages for object 'WO_TYP_ACT_STAT'.
DBCC results for 'COMPONENT_MASTER'.
There are 0 rows in 0 pages for object 'COMPONENT_MASTER'.
DBCC results for 'RECEIPTS_I_FILE'.
There are 51698 rows in 3244 pages for object 'RECEIPTS_I_FILE'.
DBCC results for 'NightCash10-12'.
There are 64657 rows in 592 pages for object 'NightCash10-12'.
DBCC results for 'WRK_CENTER_WRK_SCH'.
There are 0 rows in 0 pages for object 'WRK_CENTER_WRK_SCH'.
DBCC results for 'CONS_SVC_CONN_CD'.
There are 1524 rows in 20 pages for object 'CONS_SVC_CONN_CD'.
DBCC results for 'RECEIPT_ITM'.
There are 151581 rows in 6334 pages for object 'RECEIPT_ITM'.
DBCC results for 'Utiligy Cash Match Trace'.
There are 321627 rows in 2928 pages for object 'Utiligy Cash Match Trace'.
DBCC results for 'WRTOFF_GL_CLASS_CD'.
There are 0 rows in 0 pages for object 'WRTOFF_GL_CLASS_CD'.
DBCC results for 'CONTACT_INFO'.
There are 1 rows in 1 pages for object 'CONTACT_INFO'.
DBCC results for 'REFUND_HISTORY'.
There are 906 rows in 24 pages for object 'REFUND_HISTORY'.
DBCC results for 'RECEIPTS_I_FILE2'.
There are 48343 rows in 3022 pages for object 'RECEIPTS_I_FILE2'.
DBCC results for 'CUST_ADDR_REL'.
There are 70611 rows in 2258 pages for object 'CUST_ADDR_REL'.
DBCC results for 'REPORT_TYPE'.
There are 42 rows in 1 pages for object 'REPORT_TYPE'.
DBCC results for 'CUST_CNTRCT_RELSHP'.
There are 0 rows in 0 pages for object 'CUST_CNTRCT_RELSHP'.
DBCC results for 'REQ_READ_SCH'.
There are 1 rows in 1 pages for object 'REQ_READ_SCH'.
DBCC results for 'RESOURCE_ASGNDS'.
There are 0 rows in 0 pages for object 'RESOURCE_ASGNDS'.
DBCC results for 'CUST_INFO'.
There are 36779 rows in 2480 pages for object 'CUST_INFO'.
DBCC results for 'RESOURCE_MASTER'.
There are 0 rows in 0 pages for object 'RESOURCE_MASTER'.
DBCC results for 'RESOURCE_WORK_SCH'.
There are 0 rows in 0 pages for object 'RESOURCE_WORK_SCH'.
DBCC results for 'CUST_PYMT_ACCT'.
There are 0 rows in 0 pages for object 'CUST_PYMT_ACCT'.
DBCC results for 'ROUTE'.
There are 95 rows in 2 pages for object 'ROUTE'.
DBCC results for 'ADDR'.
There are 70767 rows in 861 pages for object 'ADDR'.
DBCC results for 'CUST_STMT_HST'.
There are 0 rows in 0 pages for object 'CUST_STMT_HST'.
DBCC results for 'ROUTE_RD_CYCLE'.
There are 0 rows in 0 pages for object 'ROUTE_RD_CYCLE'.
DBCC results for 'AGING_DAYS_TBL'.
There are 5 rows in 1 pages for object 'AGING_DAYS_TBL'.
DBCC results for 'CUST_SVC_RELSHP'.
There are 73551 rows in 1415 pages for object 'CUST_SVC_RELSHP'.
DBCC results for 'RPT_TYPE_CRITERIA'.
There are 116 rows in 2 pages for object 'RPT_TYPE_CRITERIA'.
DBCC results for 'AMX_MSG_LG'.
There are 0 rows in 0 pages for object 'AMX_MSG_LG'.
DBCC results for 'RPT_VERSION'.
There are 32 rows in 1 pages for object 'RPT_VERSION'.
DBCC results for 'DED_MTR_CONSUM'.
There are 0 rows in 0 pages for object 'DED_MTR_CONSUM'.
DBCC results for 'ASGND_BILL_CAT_CD'.
There are 0 rows in 0 pages for object 'ASGND_BILL_CAT_CD'.
DBCC results for 'RPT_VRSN_CRITERIA'.
There are 15 rows in 1 pages for object 'RPT_VRSN_CRITERIA'.
DBCC results for 'DED_MTR_POS'.
There are 0 rows in 0 pages for object 'DED_MTR_POS'.
DBCC results for 'ASGND_CUST_CAT_CD'.
There are 0 rows in 0 pages for object 'ASGND_CUST_CAT_CD'.
DBCC results for 'SCH_POOL'.
There are 0 rows in 0 pages for object 'SCH_POOL'.
DBCC results for 'DED_MTR_RDING_HIST'.
There are 0 rows in 0 pages for object 'DED_MTR_RDING_HIST'.
CHECKDB found 0 allocation errors and 17 consistency errors in database 'UtiligyPD'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (UtiligyPD ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thanks!
Anita
View 5 Replies
View Related
Jul 21, 2006
I have a package that archives some old data. It ran fine last month, but today it's failing with the following error messages:
DTS_E_BUFFERGETTEMPFILENAME
The buffer manager could not get a temporary file name. The call to GetTempFileName failed.
DTS_E_UNUSABLETEMPORARYPATH
The buffer manager could not create a temporary file on the path "__". The path will not be considered for temporary storage again.
DTS_E_CANTCREATEBLOBFILE
The buffer manager cannot create a file to spool a long object on the directories named in the BLOBTempStoragePath property. Either an incorrect file name was provided, or there are no permissions.
Any ideas on how to fix this?
Thanks!!!
MarkA
View 2 Replies
View Related
May 3, 2008
I had been frequently encountering these errors when I run my page: "Error converting data type nvarchar to int.Cannot insert the value NULL into column 'COMPANY_CODE', table 'ZSWAREHOUSE.dbo.ZS_COMPANY_TBL'; column does not allow nulls. INSERT fails.The statement has been terminated.". How do I solve this, so that I can insert data in my company table?
View 6 Replies
View Related
Jan 5, 2001
Has anyone figured out how to test if a string is a valid date without risking a sql conversion error?
I'd really like to avoid parsing the strings myself in an import procedure I am writing.
I can run an insert/select with the text being converted to datetime data, but if it hits text that won't convert correctly (such as '13-1-1999') it fails with a sql error. This could mess me up quite a bit.
Thanks in advance
View 2 Replies
View Related
Jun 26, 2007
Do you have to set the Error Output on DF components to "Fail Component" in order to get the errors?
What I would LIKE to do is a combination of "Ignore Failure" and "Fail Component". You see, I am using the Logging feature in my package that creates the sysdtslog90 table in the SQL database. The errors that I am logging make sense and have enough information for my purposes.
The problem is that I would like to continue processing the data and not have it stop when a data error occurs. I REALLY do not want to Redirect Rows unless it is necessary for me to do what I am asking.
Using Ignore Failure on both the source text file and destination SQL table allows the "good" data to be inserted, but I cannot get any info on the columns in error. Conversely, if I choose to Fail component, I get the info on the columns in error, but only the data that was inserted before the error was encountered is inserted into the table.
Suggestions?
View 14 Replies
View Related
Aug 7, 2006
hi heres a new one.....
ive created a dts that is using an odbc source to connect to an oracle server, the conexion works just fine and i have no problems with it, then, i run this package from mi computer and the data transfer ends succesfully, when i upload it to my server in SQL 2005 and set it into a job.. i get errors like these:
Event Name: OnError
Message: Thread "WorkThread0" has exited with error code 0xC0047039.
Event Name: OnError
Message: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Event Name: OnError
Message: The PrimeOutput method on component "table" (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.
Event Name: OnError
Message: The component "faccomitecedidos" (1) was unable to process the data.
Event Name: OnError
Message: The "component "table" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "diasperm" (1707)" specifies failure on error. An error occurred on the specified object of the specified component.
why is this happening , any solutions or ideas? the data i want to extract comes from an sql command not a table
this is the query:
SELECT
FCNSS ,
FITIPOSOLICITUD ,
FIFOLIO ,
RTRIM(FCNOMBRE)||' '||RTRIM(FCAPPATERNO)||' '||RTRIM(FCAPMATERNO) AS NOMBREAFILIADO,
fdfinicta as FAFIL ,
fdFecCedido as FCED ,
(fdFecCedido-fdfinicta) AS diasperm ,
FNSALARIOACTUAL AS SALAFIL ,
(SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED ,
(SELECT DISTINCT (FISDI/48.60) FROM gentec_own.AFILCEDIDOS WHERE
FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS CalSalCED ,
FCNUMPROMOTOR AS cod_promotor,
(SELECT RTRIM(FCNOMBRES)||' '||RTRIM(FCAPEPATERNO)||' '||RTRIM(FCAPEMATERNO)
FROM gentec_own.prommaestro where FCNUMPROMOTOR = gentec_own.faccomite.FCNUMPROMOTOR AND ROWNUM = 1) AS NOMPROMOTOR,
FICVEENTCED as aforeorig ,
FCAFORECEDIDO as aforeced ,
FNINGCOMT ,
FNCTOPROMOCION ,
FNCTOADMON ,
FNCONTRIBUCION ,
fcCanal as Canal ,
FCDIVISION as Division ,
FCREGION as Gerencia
FROM gentec_own.FACCOMITE
WHERE FCCEDIDO = 1
and (to_char(fdFecCedido,'yyyymmdd')>=to_char(sysdate-8,'yyyymmdd') and to_char(fdFecCedido,'yyyymmdd')< to_char(sysdate,'yyyymmdd'))
order by fdFecCedido;
please!!!1 somebody
View 4 Replies
View Related
Feb 17, 2008
Hi experts,
i am asked to check data driven errors while importing data from an excel workbook to a sql server table.
what are the data driven errors expected and how to introduce that in this sql code.
SELECT a.* FROM OPENROWSET(BULK 'Microsoft.Jet.OLEDB.4.0',
'Excel8.0;Database = C:DataExTestVista_Logo_Product_List_20080204.xls',
'SELECT * FROM [RAW Data$]'
, FORMATFILE = 'D:format.fmt') as a
Thanks in advance.
View 3 Replies
View Related
Jul 24, 2006
I'm trying to copy a data base from one server to another. Both servers are running 2005 MSDE, on Win 2k3, the target server is 64 bit.
I'm using the Copy Database function, and ultimately it fails with the error in the event viewer in the targe machine:
Faulting application dtexec.exe, version 2005.90.1399.0, stamp 43500d1a, faulting module kernel32.dll, version 5.2.3790.1830, stamp 42438b79, debug? 0, fault address 0x0000000000027d0d.
I have tried both the Attach/Detach & the SMO method, and they both give me the same error. I have 'Repaired' the .NET framework on the target machine, with the same result.
Does anyone have any Suggestions? I really only need at this time to make one copy of the database.
Thanks
Phil
View 5 Replies
View Related
May 4, 2006
Hello,
In my SSIS package I am trying to import a .csv flat file with about 170,000 rows of data with 75 columns (I know this is rather large). I would like to create a SSIS package that loads ALL of the rows of data into a table. This table has it's fields defined (such as money, float, varchar, datetime, etc). I want the data to load to this table even if there is a conversion error converting any field. When the data is loaded to this table, any fields that couldn't be converted should be set to null. Of the 75 columns of data, there are MANY columns that could be invalid.
For example, if the flat file contains the value "00/00/00" for my "PaidDate" field, I would want all of the other fields to be populated and the value for this particular record's "PaidDate" field to be null.
It would also be nice if I could trap any of the fields that caused a problem and log them along with the row.
I know that SSIS supports the "Redirect Row" method for handling data, but in my case, I don't want to redirect it. I want to continue to load it, just with a null value.
Is there an easy way of doing this (i.e. perhaps by using the Advanced Editor for the Flat File Source and setting something in the Input/Output columns)? I really don't want to create a Derived Column or Data Conversion transformation for every field that needs to be converted (b/c there are 75 columns to maintain this for).
I know if I import this file to an Access 2003 database, it imports the data fine and logs any conversion errors to an "_ConversionErrors" table. I am basically looking for this same behavior in SSIS, without having to maintain 75 columns.
TIA
View 3 Replies
View Related
Aug 29, 2007
We have a customer that has a large report that they want to export to Excel. If I try to export the entire report, it just hangs. If I run the report in 2 batches, it exports fine.
Can you tell me if there is a patch for SSRS that is going to address this or if there is a work around that I can put in my report to handle it?
The customer is quite upset at the work around so I'd love to find a solution soon.
Thanks in advance for any help!
Sherry
View 2 Replies
View Related
Jul 25, 2006
I have a client who we moved to our new web server and sql 2005 server from a sql 2000 server. I detached the database from sql 2000 and attached it to 2005. I also just set the compatibility to 2005 also.
My client used enterprise manager to import data into the tables on the sql 2000 just fine. Now using the SQL Management Studio, importing the same table produces all kinds of error, truncation errors, etc.
I have played with a bunch of the settings, did the "Suggest Types" options, but I still just get a bunch of errors. It seem to get it to work I have to go in on the columns of the flat file i am importing and change EVERY COLUMN field to match the table i'm importing too. That is just too much work.
I basically have a 2 record text file i easily imported to sql 2000. but importing into sql 2005 proves to be a *** load of work! Aren't products supposed to get better with future releases? What am I doing wrong?
I've tried the sql native client and the oledb sqlserver client and get the same results.
Any ideas?
View 1 Replies
View Related
Jul 1, 2015
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
View 1 Replies
View Related
Nov 8, 2007
I thought this should be easy... but, so far it has not been. I want to export data from SQL Server using a query to an Excel spreadsheet. I'm using SQL Server ODBC for the source connection and a Connection To Excel as my destination source. The spreadsheet exists and has the first row with column names. My mappings and query work fine. I don't have any warnings before trying to execute. BUT it will not insert the data into the spreadsheet. Here are the errors I'm getting:
[Destination - TEST$ [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
[Destination - TEST$ [28]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "Destination - TEST$" (28) failed the pre-execute phase and returned error code 0xC0202025.
TEST$ is the sheet that I am trying to add the data to and I'm using Excel 2003.
My query is simple:
SELECT OrderDate AS Date, VendorName AS Vendor, Item AS Product, TotalCost AS Amount
FROM osv_Ordercaldwecs319
All fields have been converted to varchar although I started with not trying to convert them so I have tried both ways.
What is causing the errors? Where can I look to find the problem? I'm guessing it's a data conversion problem but I made everything varchar and no formating on the spreadsheet (although I've tried that as well)
HELP please!!!
Thanks,
Linda
View 11 Replies
View Related
