Wednesday, August 14, 2013

[SQL Server 2008 issues] SQL Native Client Configuration

[SQL Server 2008 issues] SQL Native Client Configuration

SQL Native Client Configuration

Posted: 13 Aug 2013 06:57 PM PDT

Hi All,I would like to know in SQL ...If we change the order of the Protocols in the SQL Native Client Configuration.. Do we need to restart the services for sql server. Please can some body help me out.Thanks in advance !!!

LIKE Operator

Posted: 13 Aug 2013 04:49 PM PDT

Hi,I want to display list of tables , which having "_" in the part of table name?Thanks.RR

After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.

Posted: 13 Aug 2013 05:46 PM PDT

Hi All,Today I got a request to kill SSIS process which SID is XXX.After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.Please reply ASAP

Same query on a partitioned table with different date range has different execution plan

Posted: 13 Aug 2013 10:11 AM PDT

Query running slow .. Table has partitions based on date , some dates it return in 3 mins and some days it takes more than 3 hrs I have a partitioned table (partitioned by day) and have filtered stats configured. I am trying to run a query something like this ... select * from Part_tbl ptleft join t1 join_condleft join t2 join_condleft outer join t3 join_condwhere day_no = 09This is completed in 3 min for day_no = 11, 09, 08 and 07. But when I ran the same query with day_no = 10 and 12 it took 3 hours each (Data wad is not the issue as the amount of data we will get every day is almost identical). I compared the execution plans and they are different for 10/12 compared to 07/08/09/11. We enabled filtered stats on 01/2012 and never deleted any of the stats. Also, we have about 1250 partitions. I thought these might be the reason, but why the run time is different for only some of the partitions.Any help or direction would be appreciated :)

SHRINKDATABASE killed but rollback appears to be stuck

Posted: 13 Aug 2013 04:52 PM PDT

Hello...I've found several useful tips/tricks on this site over the years, and am hoping someone can help me with this one as well.A shrinkdatabase was executed and killed 8-10 hours later. The problem is that:select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requestssuggest that there has been no progress in the last 2-3 hours, and the estimated_completion_time increases. How can I proceed?Thanks,

I/O requests taking longer than 15 seconds to complete

Posted: 13 Aug 2013 04:38 PM PDT

Getting below message in error log when DBCC CHECKDB is running. SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [Drive:\MSSQL\MSSQL.1\MSSQL\Data\xyz.mdf] in database [database]. The OS file handle is 0x00000000. The offset of the latest long I/O is: 0x00000000000000I never see this message other than DBCC CHECKDB. Is this indicating any IO bottleneck or its normal to see this message while running DBCC CHECKDB ?

Querying AD thru an ASDI linked server

Posted: 13 Aug 2013 06:46 AM PDT

I'm having trouble diagnosing what the issue is here, i'm not sure the error message that i'm getting is useful in helping find it out. Am using SSMS.I've successfully created the linked server per:http://msdn.microsoft.com/en-us/library/aa746379.aspxand i know i'm using a correct LDAP connection string, as we use the same one in several web apps that work well.I have also had my network admin check my string and he indicated that it looked correct to him.My select syntax has been tested against both forms: a) LDAP://DC=Fabrikam,DC=comb) LDAP://Fabrikam.com/DC=Fabrikam,DC=com(note my querystring is different than that above, but same form)and either query parses just fine.However, all i get in returnMsg 7321, Level 16, State 2, Line 1An error occurred while preparing the query "SELECT name FROM '<LDAP STRING>' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".replace <LDAP STRING> with a or b forms.I know it's not a permissions issue as I'm using the current security context, and I can open up in ADMINISTRATIVE TOOLS and see what I"m looking at. (using ASDI Edit)But i'm stuck as to what to check next.I've checked at a bunch of places and most are a regurgitation of the original article above, and indicate i'm headed the correct way.... Thoughts on what i can try to get over this 'speed bump' i'm facing? Heck even getting a different error message would excite me at this point... (current problem related of course....)

QUERY and SP

Posted: 13 Aug 2013 02:34 AM PDT

Hey, I am confused with these files and what is asked of them. I need help creating a query with these results as well as a stored procedure help. I apologize with the lack of the DDL. So look at the XLS files please and the DOCX files. Attached below.

Email Alert when there is no entry in the table for more than one hour

Posted: 13 Aug 2013 08:57 AM PDT

Hi Experts,Requirement: check for entries in a table for every one hour and Send an email to x,y,z when there are no entries in to that table for more than 1 hour, this table has Entrytime column. For: If the programme checked table at 1:00PM and if there are no entries in table after 12:00(on EntryTime Column)it should email to the users saying there are no entries for the duration 12:00PM to 1:00PM. Next check should be done at 2:PM and it should check for the values for 1:00 pm to 2:00 pm...Any other way we can implement this without TRIGGERS?Any input is appreciatedThanks in advance

Converting Non-Spatial Data to Spatial Data

Posted: 13 Aug 2013 09:19 AM PDT

I have been looking in my SSRS book and online and can not find a clear answer to this question. I have a list of 30 dummy CPT codes in our EHR that our MD's use while on Outreaches to different shelters or Motels each dummy code represents the location they are doing their Dr. Visit at. For instance code (OR001)CPT Code = Label (Albuquerque Rescue Mission). I would like to be able to convert each code to a spatial Long and Lat that i can then use in Report Builders mapping tool. I do not mind looking up each of the 30 codes actual Long/Lat using bing. If I could get the spacial conversion, we could create a Heat Map over our city where we have the most outreach encounters. What is the best process or where can I find good information on accomplishing such a feat.Any help is appreciatedThanks Thomas

Shrink does not release space

Posted: 13 Aug 2013 07:58 AM PDT

Hi =)Im trying to shrink my production database, because we archived about 200GB of data, but - I don't know why - none space is released. I tried rebuild some indexes, shrink via file and even suspended the mirror (this environment has mirroring) but 0 KB is released rs.Recently I created an environment for testing and restore one of my lastest backups from the production database. I shrinked and ..... works fine. Released the unused space... I really don't know what to do. Pls, Heeelp rsps.: I searched in others topics, but didn't find any solutionps2: Sorry for my english...

Strange Job Slowdown 30 secs to 30 minutes

Posted: 08 Aug 2013 03:19 AM PDT

I have a job that runs 22 times a day.Four of those times, it is kicked off by another job.We recently moved to a SAN (EMC SAN is a VNX5300). That seemed great, all jobs sped up.Then we virtualized the machine to VMWare 5.1Now all jobs are fine except the one that runs 22 times a day. That one runs fine too except when it is kicked off by another job at 3:30 AM. It runs fine the other three times it is kicked off by the other job, it only slows down at 3:30 AM.Normally, the job takes between 30 seconds and 5 minutes depending on the volume of data.Before virtualization, the run at 3:30 AM was taking between 30 seconds and 1 minute.Now it is taking 28 minutes.Nothing else is running on the server at this time. Everything else has finished around 3:00.Backups (EMC, third party solution) start at 12:30 AM, finish at 3:28 AM, and don't slow down any of the other jobs that run between 12:30 and 3:30.The step in the job that is taking longer is an SSIS package.The job moved data from one database to another on the same server, then does a query. The data from the query is fed to an SSIS loop which creates files.All the files that are created have a timestamp within a minute (i.e. all are 3:55)This leads me to believe the slowdown is in one of the first two queries.The first, larger query, that inserts data to another table, typically has 500-600 records at this hour. The indexes all are under 5% fragmentation.I plan on enabling logging on the SSIS package tomorrow (today is a business-critical day, no changes allowed)Any ideas?

Create multi column view from single column data

Posted: 13 Aug 2013 05:24 AM PDT

I have the following result from my SQL query: EventID P_Num PN_NameCount1 PN_Name ABC-I-10942683 1089213 1 Company 1 ABC-I-10942683 1326624 8 Company 2 ABC-I-10942683 1565423 1 Compnay 3 ABC-I-10942683 1659874 2 Company 4I am still learning this capability in SQL and need some assistance, Pivot's are NOT working in this scenario. I have tried several different ways in attempting to do this, but was not able to create the desired results: EventID P_Num1 PNC1 PN_Name PNC_Num2 PNC2 PN_Name ABC-I-10942683 1089213 1 Company 11326624 8 Company 2The `EventID` will change based on the different events from the companies, as the `EventID` is based on a particular date the event occurred with the company.This is just a sample of the 500K+ rows of data I am working with. This will go into a temp table to be joined with the other various pieces of data needed.I have tried this without success: SELECT Key, MAX(Col1) AS Col1, MAX(Col2) AS Col2, MAX(Col3) AS Col3 FROM table GROUP BY Key

Modifying a stored procedure to point from PROD to TEST

Posted: 12 Aug 2013 11:20 PM PDT

Hi , Im looking for suggestions that would enable me to create a TEST environment.AT this point in time in my PRODUCTION system , I have stored procedures that use Linked Servers to point to the Production Environment.I am looking for ways to easily create a test environemnt from Prod.At this point in time I have 22 stored procedures that have hardcoded the database name, so when I want to create a TEST environment, I would manually need to change these 22 occurrences.I can think of using dynamic SQL to overcome this problem, however any other suggestions would be appreciatedThanks

Red gate backup issues

Posted: 13 Aug 2013 08:29 AM PDT

Hi I am using sql backup 7 from Redgate and i am having an issue whereby the sql backup process 'sqbcoreservice'is consuming way too much cpu. Way to much means, right now, when the server cpu should be near 1 - 10 % (as it now not being used) it is currently at 50% cpu. When business hours start cpu will go to around 90 - 100%.Ive just ran sp_whoisactive and i have this:dd:hh:ss.... waitinfo..........................................................CPU.......... STATUS00:11:56.....(43006055ms)ASYNC_NETWORK_IO...............32,953.........RunableI have omitted some of the other info for berevity !!When i examine the sql server processes that is running its always sqbcoreservice. We have transaction log backups throughout the day every 10 mins. There are currently no backups being performed by sql backup and there won't be for another 8 hours untill business starts again.Why am i seeing sqbcoreservice as the cpu bottleneck if no backups are currently happening?I have posted a question on the RedGate web site...i am still waiting for an answer on that one.

Select statement can't modify

Posted: 13 Aug 2013 07:35 AM PDT

I ran a select statement like below in query window but it can't be modified.select * from OrderI have to right click Order table from object explore, select "edit 200 rows".How to make select statement be editable?

SSRS: Catch/Replace #ERROR value in cell

Posted: 13 Aug 2013 06:58 AM PDT

Hi, allI have tricky tablix, and when I'm doing final TOTAL = sum all cells, it's failes because "SSRS can not deal with multiple types of values".SO in result I have on my cell value <#ERROR>, how I can erase/replace with spaces, or zeros. [code="other"]The original formula inside this cell is:=Sum(Fields!AmtX.Value * 12)I tried something like:=IIF(IsNumeric(Sum(Fields!AmtX.Value * 12)),Sum(Fields!AmtX.Value * 12),nothing)[/code]Hopind that1. It will run =Sum(Fields!AmtX.Value * 12) first2. Then check if #ERROR but it doesn't work, I think I doesn't execute original command first to evaluate status of execution.Thanks allMario

Restoring database by specifying network path for a local pc

Posted: 12 Aug 2013 09:09 PM PDT

I am using sql server 2008 r2. When i try to restore the database by using restore command[code="sql"]RESTORE DATABASE [dbname] FROM DISK = N'\\PC91\D\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'\\PC91\D\dbname.MDF', MOVE N'test_log' TO N'\\PC91\D\dbname_log.LDF', NOUNLOAD, STATS = 10[/code]I get error like[code="sql"]Msg 3634, Level 16, State 1, Line 1The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\\PC91\D\dbname.MDF'.Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.[/code]Here in above code i use network path,But this network path is network path of my pc where sql server exists.If i use below restore command then it works[code="sql"]RESTORE DATABASE [dbname] FROM DISK = N'D:\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'D:\dbname.MDF', MOVE N'test_log' TO N'D:\dbname_log.LDF', NOUNLOAD, STATS = 10[/code]This command also restores the database to same folder.So if i specify the network path why does error comes?

Increase in MAX memory causing DBCC CHECKTABLE to run longer?

Posted: 13 Aug 2013 02:55 AM PDT

A number of weeks ago we upgraded the RAM in 2 of our servers. After allowing about a week burn-in time, I changed the max memory of SQL to leave the same amount of RAM to the OS as was before, which allowed us to increase the memory for SQL about 300GB. Ever since then, our weekly maintenance significantly increased in duration. Specifically, I have traced it to DBCC CHECKTABLE tableName WITH PHYSICAL_ONLY increasing for each of our tables. From about 3 sec to 15 on avg. Anyone have any thoughts on this?

Reindexing causing strange SQL to be run

Posted: 13 Aug 2013 07:19 AM PDT

I have a customer that is reindexing our database and the reindex job that is causing the following SQL to be seen:INSERT INTO [schema_a].[table_a] SELECT * FROM [schema_a].[table_a]schema_a is our schema nametable_a is our table namethe command being run is ALTER INDEX with no options except (online = on)I'm wondering if this is related to enterprise edition and how it handles online reindexing. I'm just looking for confirmation if anyone else out there has seen this.Thanks in advance for taking any time to read and assist if you do. Thanks, Even if you don't!

Improving the efficiency of a resource-intensive job

Posted: 13 Aug 2013 06:49 AM PDT

Hello,I am looking into ways to improve a resource-consuming job (that tends to cause deadlocks sometimes), and was hoping to get advice on the right approach.The job compares 50+ tables from an OLTP and a reporting DB, logs the count of rows that are different , and triggers CDC. It currently contains 50+ statements - 1 for each of the table pairs, with the following pattern:--1—- Get the delta between the DB and the DM records in each tblSELECT a.col1 INTO #Diff FROM Tbl_DB (nolock) a LEFT JOIN Tbl_DM (nolock) b –- optional additional joinsON b.Col1 = a.Col1WHERE b.Col1 IS NULL --optional other filters--2-- A 'fake' update to the source DB table to trigger CDCIF @@ROWCOUNT > 0UPDATE aSET Col2 = a.Col2 FROM #Diff b JOIN Tbl_DB aON a.Col1 = b.Col1--3—-Insert a row in a log tbl--4—Drop table #DiffIn the majority of cases the columns in the join condition are PK's (some of which are composite). The additional conditions (when applicable) in the Where clause are either ColumnN=Constant (the ColumnN's are indexed in most cases), or ColumnN<>Constant. When more than 3 tables are joined there are multiple equality conditions in the where clause – I checked 10 of the cases and there are either clustered or non-clustered indexes on those columns. The row count for about 10 these tables is in the range of 1-80 million, about a third have 100k-1million.Is there a more efficient way of achieving the same, assuming that the set of tables would changes very rarely?For example: have a ref table with the names of tables that need to be compared, find a way to detect differences between the DB and DM version without going through all the joins (calculate checksum; or another method from available sys tbl info?), and compare only the data sets with actual changes.If not, what is a good approach find areas for improvement in the existing process?Any words of wisdom from your own experience will be much appreciated.Thank you!Vilyana

Transaction Log

Posted: 09 Aug 2013 07:47 AM PDT

One of our databases transaction log file growing larger month by month.Before I increase the disk space I would like to understand is there way we can keep the transaction log file minimal other than shrinking.However, data is increasing I belive cause log file to grow.Could you please elobrate what other things can impact the trnasaction log file gowth other than maintenance plans etc.How should I do analysis for the disk space recommendations?Thanks and appreciated!

SSIS process log

Posted: 13 Aug 2013 05:25 AM PDT

I have the following SQL that I run for DTS packages to be able to determine how long individual steps in the package took.[code="sql"]USE msdb;declare @packagename varchar(255)set @packagename = 'Month_End_Step_2_Primary_Update'select stepname, starttime, endtime, convert(char(12),(endtime - starttime), 108) as elasped_time, elapsedtimefrom msdb..sysdtssteplogwhere lineagefull = (select lineagefull from msdb..sysdtspackagelog where [name] = @packagename and logdate = (select max(logdate) from msdb..sysdtspackagelog where [name] = @packagename --and starttime < '2012-08-01 00:00:00.000' ) ) and stepname not like '%e-mail%' and stepname not like 'Success email%' and stepname not like '%Parameter%'order by stepexecutionid[/code]Here is the results I get:stepname starttime endtime elasped_time elapsedtimeAgency Cross reference SQL 7/1/2013 8:51:49.000 7/1/2013 8:58:32.000 0:06:43 403.328DTSStep_DTSExecuteSQLTask_3 7/1/2013 8:58:32.000 7/1/2013 8:59:14.000 0:00:42 41.641DTSStep_DTSExecuteSQLTask_4 7/1/2013 8:59:14.000 7/1/2013 9:00:03.000 0:00:49 48.734DTSStep_DTSActiveScriptTask_5 7/1/2013 8:59:14.000 7/1/2013 8:59:14.000 0:00:00 0.031DTSStep_DTSActiveScriptTask_6 7/1/2013 9:00:03.000 7/1/2013 9:00:03.000 0:00:00 0.031UpdateRegionCodeRegionTable 7/1/2013 9:00:03.000 7/1/2013 9:12:35.000 0:12:32 752.093DTSStep_DTSActiveScriptTask_12 7/1/2013 9:12:35.000 7/1/2013 9:12:35.000 0:00:00 0.047Balancing_by_Region 7/1/2013 9:12:35.000 7/1/2013 9:12:50.000 0:00:15 15.641My question is does anyone have something similar I could run to get the same type of results for SSIS packages?I have SQL to look at the log file created from the SSIS package, it just doesn't put it in the nice format that this is with start and stop times on one row.

SSMS query connections

Posted: 13 Aug 2013 04:46 AM PDT

Hey gang, is there a way to tie the two connections of the query tabs together so that when I change one, the other changes as well?Regards,Erin

Log User Access to Server and Database

Posted: 13 Aug 2013 02:15 AM PDT

Hello,is there way, to find out which client is connecting via Linked Server to a specific database and with specific UserID?(UserID?...ClientID?...Date?)Background:The user no longer exists (Real Employee !!:w00t:, and therefore it must be deleted on the SQL server. However, there are applications in our Companie that have deposited in the connection string with the user password (also odbc-connect and Views).We have a lot of tables in this Database, and we cannot put a Trigger on each table. And if the User sends a "SELECT"-Statement, the Trigger doesn't fire.Thank youBEST REGARDSNicole ;-)

SSAS - Multiple Instances

Posted: 13 Aug 2013 01:48 AM PDT

sorry for my ignorant question: Is it possible to install multiple SSAS instances in a clustered/nonclustered environment with the same IP address with different port numbers.

Starting SSIS jobs from BMC Control-M

Posted: 11 Aug 2013 05:05 PM PDT

Our outsourcing partner is trying to setup a way to start SSIS jobs from BMC Control-M. There seams to be a problem doing that because the SSIS job returns control before the SSIS job has finnished. Is there a way to work around that problem?Control-M is a central scheduler, its installed on the SQL Server batch server so Connection between Control-M and the server is no problem.

Execute permission

Posted: 13 Aug 2013 12:19 AM PDT

Hi ,Can someone please explain what is the purpose of execute permission? What is the drawback of granting execute? Does it cause lot of performance overhead or does it make the database/ table insecure ?I added a trigger on one of the tables in sql server (trigger on insert). But the insert fails when i enter the details on the webpage and hit enter.. The error that i get is - execute permission denied.I can get the execute permission but I want to access all the pitfalls before i go ahead with that.

copy job

Posted: 13 Aug 2013 01:24 AM PDT

i need to copy the backup file from one server to another server ?can any one send me the script to achieve this

List of fixes in SQL server 2000 SP4

Posted: 13 Aug 2013 12:53 AM PDT

Hi,I need list of only fixes that were done in SQL server 2000 SP4. I got the link belowhttp://support.microsoft.com/kb/888799Please let me know if it contains the fixes of SP3 or other service pack also.

MDW - SQL 2008

Posted: 13 Aug 2013 02:04 AM PDT

I am having a problem with the disk usage reports after creating a centralised MDW on SQL Server 2008, this is the report displayed when you drill down onto a specific database. When I drill down on a database on the local server to the MDW database the disk usage report is shown correctly. However, when I drill down onto another servers database I receive the following error: A data source instance has not been supplied for the data source 'DS_TraceEvents'. Any help will be appreciated.

Index rebuild

Posted: 13 Aug 2013 12:51 AM PDT

Hi Team,After Rebuilding index, Total fragmentation is still showing 75.please suggest what to do.

Installing multiple instances of SQL 2008 R2 SSRS

Posted: 13 Aug 2013 12:14 AM PDT

Hello all,The subject says pretty much everything.I was looking for a way to install multiple instances of SSRS on Windows Server 2008 R2 either using PowerShell or any scripting language.Firstly is it possible ? If yes, I would appreciate if someone can put me in the right direction.Many thanks in advance.

SQL Server 2008R2 Evaluation edition download link

Posted: 27 May 2013 11:01 PM PDT

Hi, I am trying to find the free trail version of SQL Server 2008 R2 Evaluation edition but no success.I tried this linkhttp://www.microsoft.com/en-us/download/details.aspx?id=8158[u][/u] but the page is empty.Could someone help me out to find the location.Thanks....
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog