Thursday, July 25, 2013

[SQL Server 2008 issues] Fuzzy searching

[SQL Server 2008 issues] Fuzzy searching

Fuzzy searching

Posted: 23 Jul 2013 11:55 PM PDT

Hello everyone, I'm having trouble finding a way to use fuzzy searching (specifically Levenstein) in my database. For now, I've been using an optimized T-SQL implementation of the Levenstein algorithm that I found on another forum. However, it is way too slow for me to use. I'm unable to use CLR functions on my server due to "memory pressure" issues (My dba is nervous about using the -g start up parameter to allocate additional memory). In addition, I'm unable to use Master Data Services' Similarity function. I'm using SQL 2008 R2 Standard, which doesn't support MDS.Can anyone think of another way that I can use fuzzy searching to compare entries in my database?Thanks in advance.

SSIS list file names in directory

Posted: 09 Apr 2013 04:12 AM PDT

Hello All,I am trying to create an SSIS package that will look at a directory, get a list of all the file names in the directory, and enter them into a table. Any advice to accomplish this?Thanks!Robert

MSDTC errors - SQL server stopped responding to any request

Posted: 07 Jul 2013 10:20 PM PDT

HiRather a strange issue with multiple SQL server instances - SQL server abruptly not responding to client applications for a brief period (5-15 minutes), but it's started responding as soon as these errors raised on SQL error log. SQL version: 2008 R2 enterprise RTMOS version: Windows server 2008 R2 Enterprise, Service pack1Client applications (IIS7) displayed with error [b]8004e024 [/b]- COM+ unable to create the instance when this issue occurring. [b]MessageError: 8510, Severity: 20, State: 1.MessageEnlist operation failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15100). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.[/b][b]MessageError: 8509, Severity: 16, State: 1.MessageImport of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15105).[/b]Any idea what's causing this issue? Any help on this highly appreciated. ThanksPS

Unknown Traces - Keep restarting when stopped

Posted: 24 Jul 2013 05:14 PM PDT

We have an application that detects when tracing is enabled on our SQL server and it has been requested that the tracing is disabled to attempt to troubleshoot an issue with the application.I've run: SELECT *FROM fn_trace_getinfo(default);GOand getting: traceid property value1 1 11 2 NULL1 3 NULL1 4 2013-07-26 16:00:01.1071 5 13 1 13 2 NULL3 3 NULL3 4 2013-07-26 06:12:01.2773 5 1and:Select * from sys.traceswhich gets:id status path max_size stop_time max_files is_rowset is_rollover is_shutdown is_default buffer_count buffer_size file_position reader_spid start_time last_event_time event_count dropped_event_count2 1 NULL NULL 2013-07-26 06:12:01.057 NULL 1 0 0 0 1088 1 NULL 221 2013-07-25 06:12:01.120 NULL 0 03 1 NULL NULL 2013-07-26 06:12:01.277 NULL 1 0 0 0 1088 1 NULL 223 2013-07-25 06:12:01.207 2013-07-25 15:49:45.433 1004 0When I run:Exec sp_trace_setstatus @traceId=2,@Enabled=0 and Exec sp_trace_setstatus @traceId=2,@Enabled=2to stop and remove the trace, it just starts up again automatically.I've checked if there are any jobs running under the SQL server agent but there is only the syspolicy_purge_history job and its history is not showing it running.Is there anything else to check to try and find what is running these traces??

DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing

Posted: 24 Jul 2013 05:45 AM PDT

We have a table, and just noticed if we try to delete a row from it (for example the first row) it runs for over 5 minutes without deleting (finally just killed the process).Restarted the server, just in case.Created a copy of the table, and I can delete just fine from that.Other tables work fine in that database, it is just this particular table.Only has 500 or so rows.It has a PK clustered index on it and that is it. (two constraints for default values of 0 for a bit field and getdate for a datetime field)I've never encountered this before.Additionally, it works fine on our other database servers, just our dev environment is having this issue.DBCC CHECKTABLE didn't find anything wrong with that given table.Any thoughts?Running the DELETE statement begins to show wait types for CXPacket after a while.... CPU jumps to 40% while it runs... and moves around (normally it is at 2%).PAGEIOLATCH_SH and CXPacket come up in wait type However, they don't stay it just suspends the task and juggles between suspended and running.

TDS DONE Message

Posted: 24 Jul 2013 05:34 AM PDT

Hello,I am facing a "problem" with the TDS network protocol. When I send a batch like the one below I receive back from SQL Server a lot of packets with the DONE message saying that a statement was executed. I understand that for each while iteration this message is generated. The problem is when I execute this command over a WAN link I can allocate all the network bandwidth of my WAN link for these messages.[i]declare @i intset @i = 0while @i < 10000begin set @i = @i + 1end[/i]From what I could find I can not disable this DONE message. Does anyone know if I can disable this message?I can execute this batch directly in the server, but I wish to know if I could disable it.Thanks,Thanks,

Indexing for Pivot Performance?

Posted: 24 Jul 2013 08:31 AM PDT

Hi SSC,I've got a table which is wasting a lot of space in it's current narrow format and I'd like to pivot it out. The table contains about 44 million rows. The example below is a scaled down version of what I'm dealing with. The idea being to pivot out the [Measure] values into their own columns. In the real data there are about 12 different measures which will become their own columns.My question is, is there a way to index the underlying table, or perhaps an analogous method to Pivot which would get index seeks or better performance over a data set of this size?[code="sql"]--Setup raw tableif object_id('tempdb.dbo.#Raw') is not null drop table #Rawcreate table #Raw( Id varchar(25), TradeDate int, Measure varchar(100), Value float primary key clustered (Id, TradeDate, Measure))--Populate sample data;with nums as --numbers table( select num = row_number() over (order by [object_id]) from sys.objects with (nolock) ), measures as --measures( select Measure = 'Alpha' union all select 'Beta' union all select 'Gamma' union all select 'Delta' union all select 'Epsilon')insert into #Raw( Id, TradeDate, Measure, Value)select Id = ids.num, TradeDate = 40000 + dates.num, Measure = m.Measure, Value = checksum(newid()) % 10000from nums idscross join nums datescross join measures mwhere ids.Num between 1 and 100/*********************** Actual Statement ***********************/select Id, TradeDate, Alpha = max(Alpha), Beta = max(Beta), Gamma = max(Gamma), Delta = max(Delta), Epsilon = max(Epsilon)from (select Id, TradeDate, Measure, Value from #Raw) srcpivot (max(Value) for Measure in (Alpha, Beta, Gamma, Delta, Epsilon)) pvtgroup by Id, TradeDate[/code]

Executing Reporting Services reports from SQL Server

Posted: 24 Jul 2013 08:01 AM PDT

Hi,I hope someone can help me. I work in SQL Server (T-SQL), and I develop reports in SQL Server Reporting Services. I am looking for a way run SSRS reports from a T-SQL Stored Procedure. I have researched it enough to know its possible by writing a CLR procedure that uses the SSRS WebServices API. However, I don't program in either VB.NET or C#.NET and don't have Visual Studio (other than the small portion that comes with BIDS).I am running SQL Server 2008R2 x64. Does anyone have a CLR procedure already compiled to do this, that they can share?Thanks,David R.

Database hosting solutions

Posted: 24 Jul 2013 04:03 AM PDT

All right folks, I'm looking for various popular database hosting solutions. Preferably places that use their own images with fully functional database engines (unlike SQL Azure that seems to strip nearly everything off an image..)Does anyone have any recommendations?Erin

Changing the system date on a server running SQL Server instance

Posted: 24 Jul 2013 06:53 AM PDT

I am recently supporting an application that can be classified a 'rules engine' and the developers and business rely on testing scenarios whereby it is required to move the date to the future. Once that occurs, anyone is pretty much prevented from accessing the server remotely, as kerberos authentication blocks due to out of sychronization with system time(I think 5 minutes off).Has anyone been exposed to this scenario (rolling dates forward, and back on server), and what are some of the negatives in doing so in regard to SQL Server ? It makes job history difficult to dicipher, but I also wonder what negatives from an engine it may have. i.e., if windows patches applied, group policy, txn log history...Thanks

Edit multiple stored procedures

Posted: 24 Jul 2013 02:34 AM PDT

I need to create 5 test databases by copying the live db's. There are many stored procedures in each db. Each sp is told which db to use, Use [NEHEN_prod]. I've been told that I have to go into each sp, about 50 per db, and change the NEHEN_prod to NEHEN_test. Is this necessary? If so, is there an easy way of doing it?

SSRS Newbie -- Question on combining data from different instances in 1 report

Posted: 24 Jul 2013 05:54 AM PDT

Hi All, I'm just learning on SSRS and needed clarification. If I want to create a simple report that outputs a list with the names of my current sql server instances and the version information, how do i set up the report to pull the information from different servers? Most tutorials show beginners how to create a simple data source/dataset but nothing on combining the data of multiple instances into 1 report.Thanks for any input.

Running SSRS report from SSIS package

Posted: 27 Feb 2009 04:28 AM PST

I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that presents my final table and I run it and export it into Excel.Is it any way after SQL Query task and Store procedures are being finished in my SSIS package, I add another object to run my SSRS report and export it to Excel format?

How get some informations about MSSQL (options , memory...)?

Posted: 24 Jul 2013 01:41 AM PDT

Hi everybody , I need help :1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations are iin v$option table) An equivalence to MSSQL ?2) Information about the memory architecture of the DB ( In Oracle the equivalence is SGA : v$sga ....)I'm new in sql server administration , so sorry :/

Change Data Capture.

Posted: 24 Jul 2013 04:19 AM PDT

I am trying to enable Change Data Capture on SQL Server 2008 R2 DB.When I enable CDC on a table by using this SP sys.sp_cdc_enable_table the Capture Job is not getting created....did some search about it and found out thatCapture job wont work on DB 's where transactional replication is enabled..Is there a workaround for enabling CDC on Database where Transaction Replication is active ?

Can someone help me with a script please?

Posted: 24 Jul 2013 12:35 AM PDT

Hi,I got very nice assistance last time i had an issue on this site. Can anyone assist me? I would like to know, is there a script i can run the re indexes all the tables in a sql database? Reasons:I have clients running sql databases.Some of them start to complain about speed. The servers is fine, i cjhecked them out. Databases from 1.0 - 18 GB in size. After i re-index each table one by one, it seems a lot better. It takes forever though. Is there a script that can assist me?Thank You!

Currious thing about Count

Posted: 23 Jul 2013 10:48 PM PDT

I accidently had an unfinished piece of SQL code in my query window during execution. The code was[code="sql"]select count(*)[/code]Now to my supprise it returned 1. Anyone know why it returns 1? The only thing i can think of is that its counting itself./T

SQL dependency

Posted: 24 Jul 2013 02:42 AM PDT

Is there any free script or software for SQL dependycy?Red-gate has one but need to purchase.

Update table values if corresponding values change in another table

Posted: 23 Jul 2013 10:03 PM PDT

Hi,I am very new to SQL and really dont know how to phrase my question. There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?The values in the table will be changed through a webpage.Any ideas would be appreciated.Thanks.

SSIS to Excel export error

Posted: 24 Jul 2013 01:18 AM PDT

An Script task in SSIS fails with error saying excel cant access 'C:\....\Windows\Temporary Internet Files\Content.MSO\'. Any resolution to stop this error?

issue using the Konestan File Watcher Task in SSIS 2008 to move files between network folders

Posted: 24 Jul 2013 01:03 AM PDT

Hi,I am using the Konestan File Watcher task for SSIS 2008 within a For Loop Container to watch a specific folder and move any files that are "dropped" into the folder to another location. The For Loop Container is set to loop infinitely.This works perfectly on a local drive, files that are randomly dropped into the folder are moved to the destination.However when I move the path to a network folder, existing files within the network folder are processed but any new files dropped into the network folder subsequently are not.The network is an Active Directory domain Any advice greatly appreciated. Thanks

SSIS Package with a Send Mail Task works perfectly within BIDS but does not work in a SQL Agent Job.

Posted: 24 Jul 2013 12:03 AM PDT

Hi,I have a SSIS Package with a Send Mail Task in it. This works perfectly within BIDS...I can get the desired email. However when I put this SSIS package in a SQL Agent Job, the Send Mail Tasks does not work. I am getting the below error :An error occurred with the following error message: "Failure sending mail. System.Net.WebException: Unable to connect to the remote server System.Net.Sockets.SocketException: An attempt was made to access a socket in a way forbidden by its access permissions 10.38.58.184:25"What needs to be done to get my Send Mail Tasks to send the desired emails while executing as a SQL Agent Job?Any and All help will be greatly appreciated...Thanks

space check

Posted: 23 Jul 2013 10:40 PM PDT

how to check the free space on clustered drives which are having mountpoints?

reinitialize subscription cancel

Posted: 23 Jul 2013 10:44 PM PDT

One of our IT staff has accidently initiated a reinitialize subscription on a 150GB database via a VPN link. Anybody know if this can be cancelled in any way.:w00t:Thanks

split mirroring

Posted: 23 Jul 2013 09:55 PM PDT

is any technique split mirroring in sql ? if yes can any one explain how to do ?

Lock Wait

Posted: 23 Jul 2013 09:40 PM PDT

Hi,How to find out the total amount of time spent on lock wait event using T-SQL Query

sql server agent is not sending dbmail

Posted: 23 Jul 2013 08:27 PM PDT

Hi everyone, i've got a problem with the agent.I've create a procedure to check the memory and, in case is too low, to send an alert via sp_send_dbmail,this procedure works if I execute it manually, but when I start the job via agent, nothing happens, no error, no mail, nothing.it is my understanding that the only difference is the user that start the procedure, but both are sysadmin.The job success with no error, have u got any ideas about the solution?many thanks!!! :)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog