Tuesday, April 23, 2013

[SQL Server 2008 issues] sql partiton view

[SQL Server 2008 issues] sql partiton view

sql partiton view

Posted: 22 Apr 2013 07:37 PM PDT

hi,i build view that contain lat say 10 table, each table contain check constraint on column name origtimetype datetime , after create the partiton view i build quary to test it so:SELECT * FROM partition_viewWHERE A=5 AND origTIME = '2012-01-01'in this case sql engine work perfectly :it look of data i looking for of a specific one table from all tables in view.the probloem start here :DECLARE @NEW_DATA DATETIMESET @NEW_DATA='2012-01-01'SELECT * FROM partition_viewWHERE A=5 AND origTIME = @NEW_DATA DATETIMEthis is the way i plan to search in this table in my procedure in db (it very logic no ? :) )after i check it in sql plan the behavior of sql wes strange:sql loop over all tables in the partiton view the quaesition is why ? (every table have index so still was fast but this is not the point )and how can avoid this?thank alot sharon

Script to find specific schema_owner for all databases

Posted: 22 Apr 2013 06:48 PM PDT

Hello,Do you know a script to find schema_owner for all databases ?I have this script but I can use it only to get information for only one database select * from information_schema.schematawhere schema_owner <> 'dbo'Thanks,Eric

Trouble with removing a defunct file

Posted: 22 Apr 2013 06:39 PM PDT

Hello gentsSyptoms are as follows:[code]select file_id, name, physical_name, state_desc from dbname.sys.database_filesgo[/code]Returns:file_id name physical_name state_desc1 dbname D:\Data\dbname.mdf ONLINE2 dbname_log D:\Log\dbname.ldf ONLINE3 dbname2_ARCHIVE D:\Data\dbname.ndf DEFUNCT4 dbname_ARCHIVE D:\Data\dbname.ndf ONLINE[code]alter database dbname remove file dbname2_ARCHIVEgo[/code]Returns:Msg 5009, Level 16, State 2, Line 1One or more files listed in the statement could not be found or could not be initialized.If I trie to run:[code]backup database dbname to disk = 'D:\backups\userdb\dbname\dbname.bak' with stats = 1go[/code]Returns:Msg 3636, Level 16, State 2, Line 1An error occurred while processing 'BackupMetadata' metadata for database id 7 file id 3.Msg 3046, Level 16, State 2, Line 1Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Then I tried to backup the tail log:[code]backup log dbname to disk = 'D:\backups\userdb\dbname\dbname.trn' with stats = 1, no_truncate, CONTINUE_AFTER_ERRORgo[/code]Returns:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current database backup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.Looking like I do not have a full backup for the t-log backup to begin with.Plus, DBCC checkdb reported no error.The defunct file is obstructing my daily full backup job for several days in a row now and how can I get ride of it?Thanks in advance for you help or advice!

Different in count of records.

Posted: 21 Apr 2013 09:15 PM PDT

Hi All,when i am trying to find the count of records, i am getting two different counts.example:select * from table--1500select count(*) from table--1500select count(*),sum(total) from table--1450 5681665can any help on this, why we are getting difference?thanks in advance,Kumar

jobs execution monitoring

Posted: 22 Apr 2013 07:35 AM PDT

Hi I wonder if there is an article about monitoring jobs, something similar to backups recent article script, we have several jobs running on each sql server, need o get the status of all jobs in a single monitoring server. Regards.

Invalid colum Name

Posted: 22 Apr 2013 02:10 PM PDT

Hi,I am following query in any database, the table is exists, but the column my be exists in some database.But am keep on getting the error 'Invalid column name mark2..Invalid column name mark3..Invalid column name mark4..Invalid column name mark5..Invalid column name mark6)this is just example.IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark2')IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark3')IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark4')IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark5')IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark6') BEGIN select mark2 from studentselect mark3 from studentselect mark4 from studentselect mark5 from studentselect mark6 from studentendIf am using IF exists, if the statement succeed it should go to select statement. But it directly goes to select statement and throughing an error.Please guide me this ASAP.what may be solution. I should not use dynamic query.. this is final time error we are getting.. this should go live today!!Regards,antony

Name Parser

Posted: 22 Apr 2013 08:04 AM PDT

I am working with a number of large data extracts (10 - 50 million records) and need to parse full name fields into their component parts (e.g., first name, middle name, last name, suffix). The format of the full name fields may vary across extracts, but rarely vary within an extract. Here are some example formats:Dr. Mary Ann A. Van Ness Jr.Van Ness Jr., Dr. Mary Ann A.Van Ness Jr. Dr. Mary Ann A.I am generally able to code around most variations (e.g., combine compound first names and last names, account for common honorifics, etc.), but this is a time-consuming process, and I was wondering if there is a software tool or SQL function available (commercial or free) that is able to parse full names with a high degree of accuracy. I understand that there are situations where the name parser will fail (e.g., sometimes Mary Ann will be a compound first name, and sometimes Ann will be the middle name), but would like to know if there is a good solution out there that is reasonably priced. I've looked into IBM's name parser (http://publib.boulder.ibm.com/infocenter/gnrgna/v3r1m0/topic/com.ibm.gnr.gna.ic.doc/topics/gnr_np_con_parsingnamesusingnameparser.html).

Having real trouble...although it's probably easy!

Posted: 22 Apr 2013 06:18 AM PDT

I asked for some ideas about this last week, but nothing quite suited my situation, which is kinda tricky...maybe if I explain it differently...I need to run a query that gets each dealer_id in my database, looking at some criteria, in order to determine some sales numbers.I have a list of about 5000 customers. Some of them have "linked" customers. I have a table that maps dealer_ids to any "child" dealer_ids they may have. Only about 25 dealers have "child" accounts.I need to do the calculations mentioned about to get totals, percentages, etc for each dealer...(Basically I total up a couple of sales numbers for each customer, and divide by a total inventory to show each dealer's percent of their inventory they've sold)BUT...If they have "child" accounts I have to add up the totals of the "parent" and all "children", base a percent calculation on that, and then apply that same percentage across the "parent" and each of the parent's "children". (Actually they're kinda treated like "siblings.") And then for dealers that have no children I just need to do the percentage calculations on them individually.One way, I guess, is to do all the accounts NOT in the parent-child mapping table, and then come up with a second routine that handles the parent-with-children accounts.This seems really clunky and convoluted though. Has anybody ever seen something like this before? Is this a known type of routine? Has anyone seen any example? Or is this easy to write SQL for, but I'm not seeing it?Any help VERY MUCH appreciated!

A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Posted: 22 Apr 2013 07:25 AM PDT

Quick question experts!A backup is made on SQL Server 2005, can it be restored to SQL Server 2008 R2 engine?

Make a select from three columns (with case expression)

Posted: 22 Apr 2013 01:48 AM PDT

HI,I have a table (named empresa) which has several fields.Three of the fields are:perc_cs_publicoperc_cs_privadoperc_cs_estrangeiroI want to make a select that returns PUBLICO if perc_cs_publico is different from zero and perc_cs_privado and perc_cs_estangeiro are equal to zero.It should Return MISTO if perc_cs_publico is different from zero and perc_cs_estrangeiro or perc_cs_privado are different from zeroIT should return PRIVADO if perc_cs_publico is equal to zero and perc_estangeiro or perc_cs_privado is different from zero.How can I do this select?Thank you

T-SQL HELP NEEDED

Posted: 22 Apr 2013 12:48 AM PDT

[code="sql"]Create table Mytemp(Id int ,value nvarchar(100),name varchar(50))insert into [rbamouser\parAPatg].MYTEMP VALUES(1,'B,C,D','XYZ')[/code]mY O/P SHOULD BE LILKE1 B XYZ1 C XYZ1 D XYZKKINDLY REQUEST YOU TO HELP ME IN THIS REGARD

MS-SQL instance failing due tempdb space issues

Posted: 22 Apr 2013 02:49 AM PDT

Hi,I should clarify my instance is Cluster aware and running SQL2012, but posting here because I believe this is more a generic SQL issue than version specific.Having said that, I patched my SQL2012 failover instance few days ago and when failing over the other node, I noticed that the SQL resource did not come online on its own. As a matter of fact, I struggle over a 5 to 10 min period in order to bring it online.I tested taking the whole SQL group in the Cluster offline, move to other node and bring online one by one, starting by disk resources. Such test went fine.I checked the SQL logs and this is what I found:[code="plain"]"Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized."[/code]Could be that lack of tempdb space was not allowing the SQL instance to come online? And if that's the case, how to troubleshoot this. I know tempdb internal usage goes up and down. I temporary scheduled a job that keep track of tempdb internal usage; it runs every minute and saves data to a table, but I barely see anything above 800MB total, which is extremely low. I will leave it for several days though, just to get a better picture and more accurate results.Total allocated space for tempdb is 150GB, spreaded over six files (not set to auto-growth). For Tlog, it was 10GB (looks it was too small) It is now 50GB, limited to 80GB. Total data (all databases hosted there) is about 1.3TB and 100GB for Tlog files only.I started to believe the issue was temdb, but the Tlog file, but I am not so sure about it. I need to be sure I am using the right size before do another planned failover. If 50GB is still too small, I may have to ask our SAN admin to expand that LUN.Any ideas, suggestions or opinions?

disk usage by top tables

Posted: 22 Apr 2013 12:20 AM PDT

script to find disk usage by the top tables on a database in sql 2005\2008

Partitioning a table with 1.5 Billion Rows

Posted: 22 Apr 2013 06:35 AM PDT

Hi,I have a table in our data warehouse with 1.5 Billion rows that we need to partition it based on the Create Date.I have tried to create a new partitioned table and load the data into that but the existing table changes so fast that keeping them in sync before the changeover has proved very difficult.So just wondering if anyone has any better ideas. I can take the existing table offline for about 6 hours and am running SQL Server 2012 SP1 Enterprise Edition (I know this is the 2008 forum but seems to be a lot busier in here!)Anyways as always help is appreciated!If I tried to rebuild the existing table with partitions I would expect it to take a lot longer than 6 hours and I think SSIS would take some time also.I have the new and old table side by side at the moment but trying to compare their data is taking an age. Have tired toSELECT * FROM A EXCEPT SELECT * FROM B to get back any difference but on a table of this size it is taking an age.A bit stumped! :crazy:

Communication Link Failures

Posted: 22 Apr 2013 03:25 AM PDT

Hello,I am receiving Communication link failures in SQL which are causing my backups to fail. This has been occurring off and on throughout the week. I have attached the SQL log below. Has anyone had this issue before? What could be causing this? I run a two-node cluster with 3 instances on each node.SQL Log04/20/2013 20:07:04,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:04,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:04,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:07:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 20:05:58,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:04:52,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:04:52,,Error,[298] SQLServer Error: 10054<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:04:52,,Error,[298] SQLServer Error: 10054<c/> TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:03:02,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect04/20/2013 16:03:02,,Warning,[408] SQL Server I_PDBMSZD is clustered - AutoRestart has been disabled04/20/2013 16:03:01,,Information,[129] SQLAgent$I_PDBMSZD starting under Windows NT service control04/20/2013 16:03:01,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent04/20/2013 16:03:01,,Information,[432] There are 12 subsystems in the subsystems cache04/20/2013 16:03:00,,Information,[339] Local computer is PDBMSZD running Windows NT 6.1 (7600)04/20/2013 16:03:00,,Information,[310] 8 processor(s) and 64510 MB RAM detected04/20/2013 16:03:00,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is PDBMSZD\I_PDBMSZD04/20/2013 16:03:00,,Information,[102] SQL Server ODBC driver version 10.50.250004/20/2013 16:03:00,,Information,[101] SQL Server PDBMSZD\I_PDBMSZD version 10.50.2500 (0 connection limit)04/20/2013 16:03:00,,Information,[100] Microsoft SQLServerAgent version 10.50.2500.0 ((Unknown) unicode retail build) : Process ID 1948404/20/2013 16:02:49,,Information,[393] Waiting for SQL Server to recover databases...04/20/2013 16:02:15,,Warning,[098] SQLServerAgent terminated (forcefully)04/20/2013 16:02:14,,Error,[264] An attempt was made to send an email when no email session has been established04/20/2013 16:02:03,,Warning,[188] Scheduler engine timed out (after 15 seconds) waiting for 5 jobs(s) to stop04/20/2013 16:01:48,,Error,[165] ODBC Error: 0<c/> SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]04/20/2013 16:01:47,,Error,[165] ODBC Error: 0<c/> SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]04/20/2013 16:01:47,,Error,[382] Logon to server 'PDBMSZD\I_PDBMSZD' failed (ConnSetJobCompletionState)04/20/2013 16:01:47,,Error,[298] SQLServer Error: 65535<c/> A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]04/20/2013 16:01:47,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]04/20/2013 16:01:47,,Error,[298] SQLServer Error: 65535<c/> SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]04/20/2013 16:01:47,,Error,[382] Logon to server 'PDBMSZD\I_PDBMSZD' failed (ConnLogJobHistory)04/20/2013 16:01:47,,Error,[298] SQLServer Error: 65535<c/> A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]04/20/2013 16:01:47,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]04/20/2013 16:01:47,,Error,[298] SQLServer Error: 65535<c/> SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]04/20/2013 16:01:47,,Information,[131] SQLAgent$I_PDBMSZD service stopping due to a stop request from a user<c/> process<c/> or the OS...04/20/2013 16:01:36,,Error,[382] Logon to server 'PDBMSZD\I_PDBMSZD' failed (ConnUpdateRunRequestedJobActivity)04/20/2013 16:01:36,,Error,[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]04/20/2013 16:01:36,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]04/20/2013 16:01:30,,Error,[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]04/20/2013 16:01:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:01:03,,Error,[298] SQLServer Error: 10004<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:01:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:01:03,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:01:03,,Error,[298] SQLServer Error: 10054<c/> Communication link failure [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:01:03,,Error,[298] SQLServer Error: 10054<c/> TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 08S01] (LogToTableWrite)04/20/2013 16:00:55,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01]04/20/2013 16:00:55,,Error,[298] SQLServer Error: 121<c/> Communication link failure [SQLSTATE 08S01]04/20/2013 16:00:55,,Error,[298] SQLServer Error: 121<c/> TCP Provider: The semaphore timeout period has expired. [SQLSTATE 08S01]

Check String for some chacters

Posted: 22 Apr 2013 02:50 AM PDT

I need to check to see if string contains (,-, / or . and pull out only the numbers from the string....212/2222255 should be 212-222-2255212.222.5555 should be 212-2225-555(212).222.5555 should be 212-222-5555212-222-5555 should be 212-222-5555212-222-5555 EXT 511 should be 212-222-5555 511what would be the best way to code this?

Slow performing query!

Posted: 22 Apr 2013 03:38 AM PDT

The following SQL query isn't scaling very well! Works great on record sets of 10-20 million. However, now that I'm trying it on a table of 90 million records I'nm getting really slow performance :([code="sql"]INSERT INTO ##BaseField_PotentialMatches_(Master_Id, Duplicate_Id, MatchKeyType, OrgName_Score, Address_Score, Postcode_Score) SELECT TOP 100 a.id, b.id, 1, ONS.*, ADS.*, PS.*FROM dbo.BaseField_keys_ AS aINNER JOIN dbo.BaseField_keys_ AS b ON a.mkMatchKeyType1 = b.mkMatchKeyType1 AND a.ID > b.ID AND NOT EXISTS (SELECT 1 from ##BaseField_PotentialMatches_ as c WHERE c.duplicate_ID IN (a.id,b.id)) CROSS APPLY dbo.OrgNameScoring (a.mkNormalizedOrganization, a.mkOrgName1, a.mkOrgName2, a.mkOrgName3, b.mkNormalizedOrganization, b.mkOrgName1, b.mkOrgName2, b.mkOrgName3) as ONSCROSS APPLY dbo.AddressScoring (a.mkPhoneticStreet, a.mkPremise, a.mkPhoneticTown, a.mkAddressKey, b.mkPhoneticStreet, b.mkPremise, b.mkPhoneticTown, b.mkAddressKey) as ADSCROSS APPLY dbo.PostcodeScoring(a.mkPOstOut, a.mkPostIn, b.mkPostOut, b.mkPOstIn) as PS[/code]the table:[img]http://i.imgur.com/KOcHXu4.jpg[/img]Index definition:[code="sql"]CREATE NONCLUSTERED INDEX [idx_mkMatchKeyType1] ON [dbo].[BaseField_keys_]( [mkMatchKeyType1] ASC, [ID] DESC)INCLUDE ( [GUID], [mkNormalizedName], [mkName1], [mkName2], [mkName3], [mkNormalizedOrganization], [mkOrgName1], [mkOrgName2], [mkOrgName3], [mkPhoneticStreet], [mkPremise], [mkPhoneticTown], [mkAddressKey], [mkPostOut], [mkPostIn], [mkTelephoneNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO[/code]Destination table:[code="sql"]##BaseField_PotentialMatches_PotentialMatches_( Master_Id INT ,Duplicate_Id INT ,MatchKeyType TINYINT ,OrgName_Score VARCHAR(10) ,Address_Score TINYINT ,Postcode_Score VARCHAR(10)) CREATE CLUSTERED INDEX idx_MasterDuplicateIDs ON ##BaseField_PotentialMatches_PotentialMatches_(Duplicate_Id)[/code]Exection plan attached belowAny suggestions are welcome!

Urgent help!

Posted: 22 Apr 2013 01:41 AM PDT

HiI ran a xp_cmdshell 'copy con.....' command on my live server to create a file. It didn't work for some reason and I killed the SPID that issued this command. Now I see that if I run:select * from sys.dm_exec_requestswhere database_id=77, I see that SPID in KILLED/ROllback status. It simple is not going since last 6-7 hours. And I believe it will stay there indefinitely.Now I have to bring my database in SINGLE USER Mode but this UnKilled SPID blocks me and I am stuck. I don't see anything in task manager either.Is there a way to get out of it without recycling the server?Thanks

Is there a better way to insert data than Access?

Posted: 14 Apr 2013 10:41 PM PDT

Is there a better tool to insert data into SQLserver than Access ?For managers. Small number of people. Very limited amount of data. For small administrations. To replace spreadsheets used by the managers.(Insert, read at and modify data).Ben BrugmanLast week, I tried to ask the same question but bumbled.

Can full text search on a column in Multi Languages like En&Fr?

Posted: 18 Apr 2013 12:15 AM PDT

I have a table column contains English and French. My FTS always returns something unexpected. I am not sure because of the multi lingual or not. Can someone tell? Thanks

Is it possible to create tables Dynamically using Cursors from another table with csv values

Posted: 18 Apr 2013 08:42 AM PDT

Have a Table with the CSV Values in the columns as belowID Name text 1 SID,DOB 123,12/01/1990 2 City,State,Zip NewYork,NewYork,01234 3 SID,DOB 456,12/21/1990 What is need to get is 2 tables in this scenario as out put with the corresponding valuesID SID DOB 1 123 12/01/1990 3 456 12/21/1990ID City State Zip2 NewYork NewYork 01234Is there any way of achieving it using a Cursor or any other method in SQL server?

Arabic text is displayed ? reading xml

Posted: 21 Apr 2013 10:18 PM PDT

Hi all,Please help me as it is very urgent !!When you paste the below query, it should return arabic text as 3rd column instead it displays "?".. Please help!!Code:declare @ds as xml = '<odds> <FIDS> <English>واص</English> <Location_x002F_Explanation>Operational </Location_x002F_Explanation> <Arabic>نعتذر عن هذا التأخير. سيبلغكم موظفونا قريباً عن توقيت الاقلاع الجديد </Arabic> </FIDS> </odds>' select x.r.query('English').value('.','nvarchar(max)') as English, x.r.query(N'Location_x002F_Explanation').value(N'.','nvarchar(max)') as Location, x.r.value(N'Arabic[1]','nvarchar(max)') from @ds.nodes('/odds/FIDS') as x(r)---------Output ----English Location (No column name)??? Operational ????? ?? ??? ???????. ??????? ??????? ?????? ?? ????? ??????? ??????

CMDExec Proxy account not working with Domain Service Account...

Posted: 17 Apr 2013 12:17 AM PDT

I'm working on migrating an existing Server 2003 / SQL 2005 system to Server 2008 R2 / SQL 2008 R2, and am having some problems getting a proxy account to work. The account is needed to execute some OS operations, NOT to affect DBs on the server. Things like unzipping files that are recieved to a particular folder and deleting the files when done.The SQL Agent is running using a domain account for this purpose. There's also a domain-level account for the proxy account.I've added the Proxy account to the SQL Logins, created a Credential for it, then created the Proxy in Agent. I've granted the appropriate users access to the Proxy (the "Principals" section), and assigned the proxy to execute a step in a test job (dir e:\)Now, the problem.It fails.If I run the step using the Agent account, it works. If I set the Agent to use the "Network Service" account and the proxy, it works. If I set the Agent to use the domain account and the job to use the proxy account, it fails with:"Executed as user: domain\svcaccount. The process could not be created for step 1 of job 0x2E0030DE6B7C444E8C0E4759A405B8E5 (reason: A required privilege is not held by the client). The step failed"I have verified the proxy account does have access to the E:\ drive by running a command prompt as the account. I also "cloned" the local group membership for the proxy from the Server 2003 system, so it belongs to the local Admins account.I've looked through the Windows Security log, and it shows it to be logging in OK. I see a logon event with a subject account name of the Agent service account, and a new logon security ID of the Proxy account. So it seems the impersonation is working...Any thoughts?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog