| update stats Posted: 01 Apr 2013 06:37 PM PDT How update stats works in sql ? |
| oracle 9i to Sql server 2008 R2 migration Posted: 01 Apr 2013 07:09 PM PDT Hi,We are having oracle 9i as database and migrating to sql server 2008.Our oracle 9i database connects to another oracle databases and these databases are having different oracle version(9.2.0.8.0, 9.2.0.7.0,10.2.0.5.0,11.2.0.2.0,9.2.0.6.0,10.2.0.4.0,8.1.7.4.0 )I do have two queries :1) how to connect from Sql server 2008 R2 to different Oracle databases2) How will other oracle databases connect to Sql server 2008 R2 database |
| Data Compression in SQL Server: Shubham Saxena Posted: 01 Apr 2013 06:31 PM PDT Dear Friends,Please help!!I need to build up a function or stored procedure to compress/decompress a large object.like image or blob please provide me script to compress and decompress image in SQL.Thanks !!:-) |
| How to write custom query which shows last two records added only. Posted: 01 Apr 2013 06:04 PM PDT For Insurance where logic is something like after renewing a policy that renewed policy is current policy and previous policy will not be current anymore. Now want to show customer this last two records i.e. renewed policy as well as original policy which was renewed. And while renewing current policy of original policy will become previous policy for renewed policy. query i am using to show current policy only or renewed policy is something like:select * from table_name where is_current='Y'Modified to select * from table_name where person_id=somevalue OR Prev_PolNO=Curr_PolNobut still it doesn't give correct result.So can same be achieved....... |
| Suspended process in msdb database Posted: 05 Dec 2012 04:00 PM PST Hello gentsI have right now a suspended spid 53 at msdb which is in suspended status for over 2 hours. Seen from sp_who2, the 'cmd' it is doing is 'delete' and the login behind this process is "NT AUTHORITY\SYSTEM" from program name "Microsoft Windows Operating System".In addition, lastwaittype is BROKER_RECEIVE_WAITFOR and I ran dbcc opentran in msdb and it returns:Oldest active transaction:spid: 53UID: -1Name: receiving msgStart time: <the time i ran "dbcc opentran">Any ideas where else I should look at?Thanks in advance! |
| Cannot convert between Unicode & Non Unicode Strings Error Posted: 01 Apr 2013 03:41 PM PDT Hi,I have a SSIS package that imports an ADO.Net source file & inserts into an OLEDB Destination.There are about 25 fields but one field errors when tryingto run this task.The field represents an Account number which is a varchar (15) in both the source & destination tables.I tried fixing this by adding a Data Conversion node to change the Account number from DT_WSTR to DT_STR as I found an answer on a forum about this problem but no luck, same error.Can anyone advise how I can fix this & why does it happen?Thanks |
| Can I move the older ERRORLOG files? Posted: 01 Apr 2013 01:22 PM PDT Hi all,Long story short, we had a process that was filling up our log file at an impressive rate (impressive in a bad way). We got everything under control and that particular error log file is no longer the "current" one. However, it's taking up quite a bit of space on one of our drives. So, I'd like to move it to another drive to free up some extra room.I know I can cycle the log files until the log file in question just gets auto-deleted, but I'd like to keep it around a bit longer so we can look through it and troubleshoot.Can I manually move the files (such as ERRORLOG.2, ERRORLOG.3, etc)? Is there a super secret sp for doing this? How does SSMS like having those files manually moved without its consent?Thanks! |
| sql query to be accessed online Posted: 01 Apr 2013 07:41 AM PDT HiI just want to figure how to have a sql query that I generated in excel form and I want to put it online and I want it to update it self. is that possible?Thank you |
| A severe error occurred on the current command. The results, if any, should be discarded. Posted: 01 Apr 2013 12:17 AM PDT Hi While running the query , I am getting the following error :- Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.The query is :- ;with cte as ( select 'Bldgclass' as BldgClass,RuleId,7 As CurVerNum , 'State' As State1 from ERM..RuleConstructionCS where ruleid between 40500001 and 40599999 and SourceCode=29 ) , CTE1 AS ( select GrpAddressID,SourceCode,RuleID,UID FROM ES_Archive..RuletrackerArch where SourceCode=29 and VersionNum=6) ,CTE2 as ( SELECT GrpAddressId,SourceCode,UID FROM CTE A INNER JOIN CTE1 B ON A.RULEID = B.RULEID),CTE3 AS ( SELECT DISTINCT StateCode,UID FROM ES_Archive..EmporisMRSTGMappedArch A INNER JOIN ES_Archive.dbo.tmpCurrMinusPrevGrpAddressId B ON A.GRPAddressID = B.grpAddressID ) SELECT GrpAddressID into abc FROM CTE2 INNER JOIN CTE3 ON CTE2.UID = CTE3.UID Please help me out !!!Regards,Ankit |
| Discrepancy between sys.master_files and sys.database_files Posted: 01 Apr 2013 05:19 AM PDT Hi all,I have discrepancy beetween sys.master_files and sys.database_files in size column. Couple of days ago I changed the size of a log file for one database, however it's not reflected in sys.master_files. Views sys.database_files and sys.sysfiles are OK, they show right value. Is sys.master_files outdated? Should I run some DBCC to update it ?Thanks |
| Issue with Float datatype while using EXCEPT Posted: 01 Apr 2013 02:25 AM PDT I have two tables with float data types and am using EXCEPT to check for the new rows in the source tables.Their is data in the source table but it is returning nulls.I am unable to figure it out , why this is happening.select colmn1, colmn2, -----from table aExcept select colmn1, colmn2from table bnow although talbe A has data but it is giving nullsAny help will be appreciatedThanks |
| Cardinality Issue Posted: 01 Apr 2013 02:15 AM PDT I have a 100,000,000 row table with a GUID primary key. (This is an IBM FileNet/ECM database, and utility query.)The following query:[code="sql"]SELECT TOP 9999 * -- Edited for brevityFROM DocVersion T0WHERE (T0.home_id IS NULL AND ( ( storage_area_id = N'{18CDA09D-00B2-427F-9016-C78844AA92AE}' OR storage_location =N'FNFS:/{18CDA09D-00B2-427F-9016-C78844AA92AE}')) AND (( object_id > N'{42D1BB3B-53F2-462D-A277-1AFF39548364}'))) ORDER BY object_id ASC option (recompile)[/code]is making a cardinality error on object_id. It estimates it will have 10,000 - 20,000 rows on the greater-than comparison, but the actual row count is 46M.Based on this error, it goes for a seek of the primary key, and as such, it takes 4-6 hours to seek 46M times. The scan would be 13 minutes.What I don't understand is 1) Why it is reaching this error, and 2) How to resolve it.The primary key is on the object_id, which is a GUID.This historgram details on the object_id are:[code="plain"]RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ff4c80d6-98b7-46ef-97f0-[b]0000003c6313[/b] 0 1 0 1 9da7159c-a90e-48e3-84f1-[b]01ef6ded77e4[/b] 319998 1 319998 140b75dcf-55c1-4371-83e5-[b]04b0ea3cea3f[/b] 267887 1 267887 1 e2aed122-0e41-40a2-9296-[b]0ea384fd1a1d[/b] 1608631 1 1608190 1.000275e0fbf953-9a39-4d49-bbc6-[b]132a32003da4[/b] 536035.9 1 536036 1 e9b6e490-233e-40ba-a4e4-[b]19179b42e6c[/b] 5536035.9 1 536036 1 --------------------------------------------------0b8fc800-047f-5eb2-bfb9-[b]1b3e9999571f[/b] 536035.9 1 536036 1 0aa19a05-047f-5eb2-bfb9-[b]1b3e9999571f[/b] 804184.8 1 804094 1.000112(continued...169 steps total)[/code]I believe GUIDs are ordered by the last segment of the guid, and that belief is reflected in this histo. I put a dotted line in the histo: Everything below the line (including the remainding 161 steps) would match the query predicate on object_id.Why in the world does the PK seek think there are only 20k rows? Each step has over 100,000 rows.Thanks for your consideration,@SixStringSQL |
| Query - Please Help! Posted: 26 Mar 2013 03:21 AM PDT Someone please tell me what I'm doing wrong..... :( All I want to do, is get a list of databases which don't have extended properties. Query below:exec ('create table #t_nodb(dbname varchar(120)) insert into #t_nodb (dbname) exec sp_MSforeachdb ''if ''?'' in (''tempDB'') returnuse ? select ''?'' where ''?'' not in (select ''?'' from sys.extended_properties)''select * from #t_nodbdrop table #t_nodb') Thanks! |
| Duplicating rows based on another table Posted: 01 Apr 2013 01:51 AM PDT Hi,I have some data in table a which has a unique identifier called column nd. I have another table, table b, which has that unique identifier, again called nd and another column called j which is a number between 10-20.How can I return the results on table a, but I want a row for each nd times the result in column j where nd is the same in both tables.For example, if nd is equal to small, and j is equal 10 the results from that query would be 10 rows of the word small.Hope that makes sense.Thanks |
| Why duplicate indexes Posted: 01 Apr 2013 01:58 AM PDT It is query logically error, post deleted. Thanks for looking at. |
| Requirement of .net framework for sql server 2008 r2 in Windows 8 Posted: 01 Apr 2013 12:47 AM PDT What is the actual version requirement of .net framework for installing sql server 2008 r2 in a windows 8 PC?According to [url=http://www.microsoft.com/en-us/download/details.aspx?id=3743]link [/url]Download and install Microsoft .Net Framework 3.5 SP1. or Download and install Microsoft .Net Framework 4.0.According to [url=http://www.microsoft.com/en-in/download/details.aspx?id=26729]link [/url]Install Microsoft .NET Framework 2.0 or laterIn some article i saw that i should enable .net 3.5 features in control panelSo what is the actual requirement? |
| Maintenance Plan issue Posted: 01 Apr 2013 12:21 AM PDT Hi all, I got a strange issue today. When i am trying to run a maintenance plan manually from GUI. I am not able to execute because the option itself is greyed out.I am interested to know why that execute option is not highlighting. |
