Force To Complete Query, Ignore Errors
Nov 9, 2006
Hi,
I have a big table and want to make a plausibility check of it´s data.
Problem is, that my query stops, if there is an unexpected datatype in one of the rows. But that is it, what i want to filter out of my table with that query and save the result as new correct table.
How can i write a parameter to my query SQL Code, that if a error occurs, the querry resumes and the error line will not displayed in my final querry overview?
In my books and on the net, i don´t found something to this theme ;-(.
Thx in advance.
View 9 Replies
ADVERTISEMENT
Nov 11, 2004
Hello - the very nature of this question seems to make no sense I know - but we received a huge volume of data (29 tables) in flat file format. I first imported them into MS Access because of its portability and it seemed to be more forgiving on imports. Now I have a complete MS Access DB with all tables, so I figured importing to SQL server should be a snap. However, on the import, I had 14 tables import successfully, and 15 failed!
Here is an example of one of the error messages I received:
Insert Error, Column 3 - status 6; Data Overflow...this was on a date/time field in access, and here is the data contained in the referenced row/column: "8/19/4999"
the year "4999" is obviously the problem (at least i think), and I have no idea why this successfully imported to MS Access, but not to SQL Server....
what i'd like to be able to do (not the best practice, i know) for now is ignore these types of errors - and just force SQL server to take the data straight from MS Access and replicate it. We received this data from a 3rd party, and there's no telling how many data entry errors like this could be in each table - many of the tables have over 500,000 rows, and i don't want to have to go through fixing each of these errors by hand...anyone have any ideas?
View 1 Replies
View Related
Jun 7, 2006
I have a simply SSIS package with following data flow structure:
Flat File Source > Data Conversion > Aggregate > Dervied Column > Ole dB Destination
Basically, this package is executed on daily basis to import sales from a text file into sql server. Now there's a possibility that text file may contain previous sales (occasionally).
My sql table structure enforces data integrity through primary key and therefore my package errors out if there's a duplicate in text file which already exists in sql server.
I'm basically looking for a way to ignore these duplicates and continue to import rest of the file. I need a way to force execution (suppress errors if possible) and finish importing all text file.
I've tried making the maximumerrorcount more than # 50000 and failparent/failpackage on error = false.
Any help is greatly appreciated...thanks
Here's the errors I receive:
SSIS package "Package2.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Source - SALES_TXT [1]: The processing of file "Z:SALES.TXT" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Source - SALES_TXT [1]: The total number of data rows processed for file "Z:SALES.TXT" is 20450.
Information: 0x402090DF at Data Flow Task, Destination - SALES [37]: The final commit for the data insertion has started.
Error: 0xC0202009 at Data Flow Task, Destination - SALES [37]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Sale'. Cannot insert duplicate key in object 'dbo.Sale'.".
Information: 0x402090E0 at Data Flow Task, Destination - SALES [37]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Destination - SALES" (37) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0202009.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Source - SALES_TXT [1]: The processing of file "Z:SALES.TXT" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - SALES" (37)" wrote 18750 rows.
View 6 Replies
View Related
Jan 25, 2006
In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA
View 2 Replies
View Related
Dec 12, 2006
if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the table
View 4 Replies
View Related
Mar 5, 2004
Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:data.txt -b1 -m100 -c -t, -Sdatabase -Uuser -Ppassword
here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors
please help.
thanks
madhu
View 1 Replies
View Related
Apr 10, 2008
Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?
View 2 Replies
View Related
Jul 7, 2006
Is there any way to emulate the try/catch mechanism that SqlServer2005 provides using SqlServer2000?
Or more simply,
Is there any kind of IGNORE_ERROR or CONTINUE_ON_ERROR setting for SqlServer 2000?
Thanks
- John
View 2 Replies
View Related
Feb 6, 2008
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel source will fail.
I have it so that for each iteration if the transform was successful the file is moved to an archive directory, and if it fails the file is moved to a different directory.
But I don't want the package to be marked as failed. For the control flow tasks I have set the individual components to FailPackageonFailure = False, and for the Data Flow tasks I have set ValidateExternalMetadata = False.
It no use to set the MaxErrorCount higher because I can't guarantee how many files will be processed and how many might fail.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
Thanks
View 6 Replies
View Related
Apr 2, 2008
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine
My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:
Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.
In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option.
My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure?
Here's a code snippet stripped down to the bare minimum to generate the error:
CREATE PROCEDURE [My_FullTextSearch]
@Term VarChar(1000)
AS
BEGIN
SET NOCOUNT ON;
IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1
BEGIN
Select [DocumentID]
from [DocumentText]
where [Term] like '%' + LTRIM(@Term) + '%'
END
ELSE
BEGIN
Select [key] from FREETEXTTABLE(Documents, Contents, @Term)
END
END
View 5 Replies
View Related
Mar 23, 2006
I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.
I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)
I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.
I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.
One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.
View 2 Replies
View Related
Jan 11, 2008
I have added an email task to the ON Error Event of my SSIS package, so that I will always know when there are errors.
However I would like the SQL Server job executing the package to succeed even if the package fails.
What setting do I change in the SSIS packageto achieve this? MaximumErrorCount?
View 1 Replies
View Related
Feb 12, 2008
When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?
I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.
How do you get around this?
Thanks
View 13 Replies
View Related
Feb 25, 2008
Hi. i am updating a field on a table i have called tblNavproductId. when doing the insert, i get the error message that you know, it is is violation of a foreign key constraint on NavId. i see the foreign key, so i know it is there and i understand that. i read the books online and here is the article;
http://msdn2.microsoft.com/en-us/library/ms186247.aspx
does anyone have a good example of this type of query? i am putting 990k+ records in at once, so would this be a bulk insert? Thanks.
View 3 Replies
View Related
Aug 6, 2014
I am running an update query.It is taking long time. To find the estimated completion time i checked sys.dm_exec_request or sys.dm_exec_session or sp_who2 but there is no clue. It is showing as zero.
View 3 Replies
View Related
Jan 28, 2008
As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.
View 14 Replies
View Related
May 23, 2007
I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
View 16 Replies
View Related
Jun 8, 2001
We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
View 1 Replies
View Related
Jun 2, 2007
I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.
View 12 Replies
View Related
Aug 22, 2007
Hello--I do not know if this group can help me with SQL Server Reporting Services ... but here it goes. I have a report built on a query and I want to be able to send a parameter (@theWhere) that is a string which is a where clause "(MEGLOMART_TYP_CODE <> 'XYZ' AND MEGLOMART_STAFF_SHORTAGE > 25)" these strings can vary depending on what columns the user selects and what operators they want to use. Generation of proper SQL for the where clause has been verified, I just need to be able to pass these, is there any way to do this...see example query below and how I was planning on using the @theWhere variable...
Thanks!
SELECT DISTINCT SALES_REGION,
SALES_REGION_CODE,
PRODUCT_CLASS_DESC,
MEGLOMART_STORE_ID,
STATE_NAME,
MEGLOMART_TYP_CODE,
MEGLOMART_TYP_DESC,
MEGLOMART_COMMUNITY_POPULATION,
MEGLOMART_PENETRATION_PERCENT,
MEGLOMART_STAFFING_TARGET,
MEGLOMART_STORE_FT_STAFF,
MEGLOMART_STORE_PT_STAFF,
MEGLOMART_STAFF_SHORTAGE,
MEGLOMART_STAFF_TRAINEE_COUNT,
(CASE MEGLOMART_TYP_CODE
WHEN 'MEGA' THEN 'Full Service'
WHEN 'MID' THEN 'Traditional'
WHEN 'MIN' THEN 'Convenience'
ELSE 'Other' END) AS MEGLOMART_TYP_GRP,
(CASE WHEN MEGLOMART_TYP_CODE = 'MEGA' THEN 1
ELSE 0 END) AS MEGLOMART_MEGA_TYP_COUNT,
(CASE WHEN MEGLOMART_TYP_CODE = 'MID' THEN 1
ELSE 0 END) AS MEGLOMART_MID_TYP_COUNT,
(CASE WHEN MEGLOMART_TYP_CODE = 'MIN' THEN 1
ELSE 0 END) AS MEGLOMART_MIN_TYP_COUNT,
(CASE MEGLOMART_TYP_CODE
WHEN 'MEGA' THEN 0
WHEN 'MIN' THEN 0
WHEN 'MID' THEN 0
ELSE 1 END) AS MEGLOMART_OTH_TYP_COUNT
FROM
dbo.MEGLOMART_SNAPSHOT AS theView
WHERE
(PRODUCT_CLASS_DESC = 'Home Furnishings') AND
(NOT (SALES_REGION IS NULL))
AND @theWhere
ORDER BY
STATE_NAME,
PRODUCT_CLASS_DESC
View 26 Replies
View Related
Nov 16, 2007
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
View 5 Replies
View Related
Oct 30, 2015
Select A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?
View 7 Replies
View Related
Nov 14, 2007
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
View 4 Replies
View Related
Jul 17, 2006
Try this script to see what queries are taking over a second.To get some real output, you need a long-running query. Here's one(estimated to take over an hour):PRINT GETDATE()select count_big(*)from sys.objects s1, sys.objects s2, sys.objects s3,sys.objects s4, sys.objects s5PRINT GETDATE()Output is:session_id elapsed task_alloc task_dealloc runningSqlText FullSqlTextquery_plan51 32847 0 0 select count_big(*) from sys.objects s1, sys.objects s2,sys.objects s3, sys.objects s4, sys.objects s5 SQL PlanClicking on SQL opens the full SQL batch as a .txt file, including the PRINTstatementsClicking on Plan allows you to see the .sqlplan file in MSSMS========Title: Using a VB Script to show long-running queries, complete with queryplans.Today (July 14th), I found a query running for hours on a development box.Rather than kill it, I decided to use this opportunity to develop a scriptto show long-running queries, so I could see what was going on. (ReferenceRoy Carlson's article for the idea.)This script generates a web page which shows long-running queries with thecurrently-executing SQL command, full SQL text, and .sqlplan files. The fullSQL query text and the sqlplan file are output to files in your tempdirectory. If you have SQL Management Studio installed on the localcomputer, you should be able to open the .sqlplan to see the query plan ofthe whole batch for any statement.'LongestRunningQueries.vbs'By Aaron W. West, 7/14/2006'Idea from:'http://www.sqlservercentral.com/columnists/rcarlson/scriptedserversnapshot.asp'Reference: Troubleshooting Performance Problems in SQL Server 2005'http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspxSub Main()Const MinimumMilliseconds = 1000Dim srvnameIf WScript.Arguments.count 0 Thensrvname = WScript.Arguments(0)Elsesrvname = InputBox ( "Enter the server Name", "Server", ".", VbOk)If srvname = "" ThenMsgBox("Cancelled")Exit SubEnd IfEnd IfConst adOpenStatic = 3Const adLockOptimistic = 3Dim i' making the connection to your sql server' change yourservername to match your serverSet conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' this is using the trusted connection if you use sql logins' add username and password, but I would then encrypt this' using Windows Script Encoderconn.Open "Provider=SQLOLEDB;Data Source=" & _srvname & ";Trusted_Connection=Yes;Initial Catalog=Master;"' The query goes heresql = "select " & vbCrLf & _" t1.session_id, " & vbCrLf & _" t2.total_elapsed_time AS elapsed, " & vbCrLf & _" -- t1.request_id, " & vbCrLf & _" t1.task_alloc, " & vbCrLf & _" t1.task_dealloc, " & vbCrLf & _" -- t2.sql_handle, " & vbCrLf & _" -- t2.statement_start_offset, " & vbCrLf & _" -- t2.statement_end_offset, " & vbCrLf & _" -- t2.plan_handle," & vbCrLf & _" substring(sql.text, statement_start_offset/2, " & vbCrLf & _" CASE WHEN statement_end_offset<1 THEN 8000 " & vbCrLf & _" ELSE (statement_end_offset-statement_start_offset)/2 " & vbCrLf & _" END) AS runningSqlText," & vbCrLf & _" sql.text as FullSqlText," & vbCrLf & _" p.query_plan " & vbCrLf & _"from (Select session_id, " & vbCrLf & _" request_id, " & vbCrLf & _" sum(internal_objects_alloc_page_count) as task_alloc, " &vbCrLf & _" sum (internal_objects_dealloc_page_count) as task_dealloc " &vbCrLf & _" from sys.dm_db_task_space_usage " & vbCrLf & _" group by session_id, request_id) as t1, " & vbCrLf & _" sys.dm_exec_requests as t2 " & vbCrLf & _"cross apply sys.dm_exec_sql_text(t2.sql_handle) AS sql " & vbCrLf & _"cross apply sys.dm_exec_query_plan(t2.plan_handle) AS p " & vbCrLf & _"where t1.session_id = t2.session_id and " & vbCrLf & _" (t1.request_id = t2.request_id) " & vbCrLf & _" AND total_elapsed_time " & MinimumMilliseconds & vbCrLf & _"order by t1.task_alloc DESC"rs.Open sql, conn, adOpenStatic, adLockOptimistic'rs.MoveFirstpg = "<html><head><title>Top consuming queries</title></head>" & vbCrLfpg = pg & "<table border=1>" & vbCrLfIf Not rs.EOF Thenpg = pg & "<tr>"For Each col In rs.Fieldspg = pg & "<th>" & col.Name & "</th>"c = c + 1Nextpg = pg & "</tr>"Elsepg = pg & "Query returned no results"End Ifcols = cdim filenamedim WshShellset WshShell = WScript.CreateObject("WScript.Shell")Set WshSysEnv = WshShell.Environment("PROCESS")temp = WshShell.ExpandEnvironmentStrings(WshSysEnv("TEMP")) & ""filename = temp & filenameDim fso, fSet fso = CreateObject("Scripting.FileSystemObject")i = 0Dim cDo Until rs.EOFi = i + 1pg = pg & "<tr>"For c = 0 to cols-3pg = pg & "<td>" & RTrim(rs(c)) & "</td>"Next'Output FullSQL and Plan Text to files, provide links to themfilename = "topplan-sql" & i & ".txt"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-2)f.Closepg = pg & "<td><a href=""" & filename & """>SQL</a>"filename = "topplan" & i & ".sqlplan"Set f = fso.CreateTextFile(temp & filename, True, True)f.Write rs(cols-1)f.Closepg = pg & "<td><a href=""" & filename & """>Plan</a>"'We could open them immediately, eg:'WshShell.run temp & filenamers.MoveNextpg = pg & "</tr>"Looppg = pg & "</table>"filename = temp & "topplans.htm"Set f = fso.CreateTextFile(filename, True, True)f.Write pgf.CloseDim oIESET oIE = CreateObject("InternetExplorer.Application")oIE.Visible = TrueoIE.Navigate(filename)'Alternate method:'WshShell.run filename' cleaning uprs.Closeconn.CloseSet WshShell = NothingSet oIE = NothingSet f = NothingEnd SubMain
View 1 Replies
View Related
Aug 7, 2001
My SQL 7.0 server is currently querying the SAM database on the PDC for Windows NT authentication. How can I force it to use the SAM database on the server(BDC) that I specify?
View 1 Replies
View Related
Jul 25, 2007
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
View 1 Replies
View Related
Mar 1, 2012
I recently upgraded to SQL Server 2008 r2 from a 2k5 server. My SQL Reports Server generates a report with the following code.
SELECT SV00100.CPRCSTNM,
max(SOPHisIt.CUSTNMBR), MAX(RM00101.CUSTNAME) AS Expr1,
SUM(SOPHisIt.XTNDPRCE) AS TotalSales
FROM SOPHisIt INNER JOIN
[Code]...
This code still works on old report server no problem but I get a couple of errors on the new one.
*Msg 8120, Level 16, State 1, Line 1
Column 'SV00100.CPRCSTNM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
*Second is the multi part identifier could not be found
View 9 Replies
View Related
Nov 19, 2007
hello friends,
i am runing stored procedure which runs for around 20 min. as it is filtering data from lacks of record. after completion it shows above message i.e."query completed with errors" with 466814 records affected why this happining as it does not display any errror in message window . Does it is because of size of data . plz guide me how to debug it.
sp is containing 3 cursors
Thanx in advance
View 6 Replies
View Related
Aug 29, 2007
Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.
The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."
This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it?
The union query looks like this:
exec('select
RecordNumber,Sum(rank1) as HbA1CRank1,max(Operator1) as HbA1COperator1,sum(contentValue1) as HbA1CContentvalue1,max(dos1) as HbA1CDOS1,Sum(rank2) as HbA1CRank2,max(Operator2) as HbA1COperator2,sum(contentValue2) as HbA1CContentvalue2,max(dos2) as HbA1CDOS2,Sum(rank3) as HbA1CRank3,max(Operator3) as HbA1COperator3,sum(contentValue3) as HbA1CContentvalue3,max(dos3) as HbA1CDOS3
from
(SELECT DISTINCT TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2, 0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 0)ORDER BY RecordNumber
UNION
SELECT DISTINCT TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2, 0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 1)ORDER BY RecordNumber
UNION
SELECT DISTINCT TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3, contentvalue AS contentvalue3, DOS AS DOS3FROM (SELECT (SELECT COUNT(*) FROM tblDiabetic_HgbA1C_Total vw1 WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, * FROM tblDiabetic_HgbA1C_Total vw2) vw3WHERE (Rank = 2)ORDER BY RecordNumber )tblHgA1C
group by RecordNumber
')GO
Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up =
Thank you!
View 4 Replies
View Related
May 23, 2006
I've not gotten the If statement to work.
Before: http://www.webfound.net/sqlbefore.txt
After (with IF statement):
http://www.webfound.net/sqlafter.txt
I don't want to include the deletedpdcs if the month is today in my SUM for PDCs_IL basically is what I'm trying to accomplish here
View 3 Replies
View Related
Jan 14, 2005
Hello all,
Thought I would post here in case anybody can give some information.
Here is the background information:
I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.
Here are the update queries that cause the error:
UPDATE st
SET st.zip = 66668
FROM stores st
INNER JOIN sales sa ON st.stor_id = sa.stor_id
AND st.stor_id = 6380
Update stores SET stores.zip = 55555
FROM sales, stores
WHERE stores.stor_id = 6380
AND stores.stor_id = sales.stor_id
Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 1
Param. 2: 0
Param. 3: FROM
Param. 4:
Param. 5:
I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.
I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.
Any suggestions?
Thank you,
Aaron B
View 1 Replies
View Related
Aug 5, 2014
CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))
[Code] ....
I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.
What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.
My failed attempt:
Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL
[Code] ....
View 2 Replies
View Related
May 11, 2012
I execute a script that someone else wrote and I get Query Completed with Errors but there is not an error message.If I highlight and execute parts of the script, it completes successfully.
View 9 Replies
View Related
