Wednesday, July 31, 2013

[SQL Server 2008 issues] Blocking Query

[SQL Server 2008 issues] Blocking Query

Blocking Query

Posted: 30 Jul 2013 07:02 PM PDT

Hi Team,I used to run 'sp_who2' to check the locks, i found that some sp_id are showing in BlkBy column, and in command column it is showing as 'Select'I to find the full query, which is blocked.

Convert Varchar to Date??

Posted: 30 Jul 2013 02:00 AM PDT

HiI have a date field stored in a varchar as "Jul 24 2013 8:05AM"I would like to convert to a date field to do calculations like this "2013-07-24 00:00:00.000"Time is not important..Thanks in AdvanceJoe

Tool for User Administration for Level 1

Posted: 30 Jul 2013 05:16 PM PDT

Can you suggest me a tool for User administration like create user, assgn roles, permmissions for sql server.My goal is to assign these activites to my level 1 team who knows nothing at sql server.SQL Managemenet Studio I donot want to provide.

How to add an analysis server into SQL server 2008 R2?

Posted: 30 Jul 2013 08:49 AM PDT

I am very new to data warehouse. I just installed Sql server 2008 R2, but when I accessed it, there was no analysis server which is for data warehouse. I also downloaded the Aventure Works DW 2008 database sample, but I did not know where to attach it?Does anyone know about it?thank in advance.

Identifying SQL Agent Job Name based on the job id.

Posted: 30 Jul 2013 11:33 AM PDT

I'm trying to figure out which Job is running, based on this the following name. Where can I find this information?SQLAgent - TSQL JobStep ([b]Job 0xFB668E27919DA3489E3DD97061F25B31[/b] : Step 1)

Need a help in MCSA SQL Server exam 2012 70-461, 462, 463

Posted: 30 Jul 2013 09:32 AM PDT

Hello friends,after getting good experience at current workplace, I am planing to get certified in MS SQL SERVER 2008/2012.I went through the webpage that provide the exam information.I am looking for any preparation material, or web site that can help me with the preparation.because, I can not afford the books, which are mentioned in the website for preparation of the exam.Please let me know where I can start reading or get some practice materials.thanks in advance.Pratik.

Getting Back Previous Database

Posted: 30 Jul 2013 02:55 PM PDT

Dear,I have restored a database with an existing database. But there was some important information in my previous database. I forgot to backup the previous database.Now I want to get previous database. But how to get it?I am using SQL 2008 R2. Please help me.Regards,Akbar

SSIS Lookup Transform Bulk insert error - stumped!

Posted: 30 Jul 2013 01:36 PM PDT

Ok - I'm stumped. I have an SSIS package that reads our CDR data and i need to do an incremental load nightly to only add new records. I can successfully run the package and insert directly in the table with out the look up transform, but as soon as i try to see if any of the records are in the destination table already the lookup transform fails with the following error:[Error Rows [169]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The Bulk Insert operation of SQL Server Destination has timed out. Please consider increasing the value of Timeout property on the SQL Server Destination in the dataflow.".I have sys admin rights on the server. I am running in admin mode with 32 bit enabled. Bulk Insert is allowed on the server and the package is ran locally on the server using the bulk insert operation.SSIS Data Flow Task:flat file source ->derive some columns->conditional split to find all non null values for insert ->lookup transform to see if the record exists->insert new records into OLE DB Destination TableI'm stumped on how to get the lookup transform to work. Is there another option to only insert new records via ssis efficiently?Thanks in advance for your help!Katie

Deploy multiple SSIS Packages in one batch file to SQL Server

Posted: 27 Mar 2011 07:08 PM PDT

Hi,I need to write a Batch file which can Deploy multiple SSIS packages kept at a location. The Batch file should iterate to check all the SSIS packages and Deploy them one by one.Server to deploy should be passed as parameter.

Output query result to excel sheet

Posted: 30 Jul 2013 12:29 AM PDT

Hi,I want to put results into excel from Sql script , In results it is having three different result sets returnedLike as below.Select * from table 1Select * from table 2Select * from table 3

Find out if index rebuilding/reorganizing is happening on a certain table

Posted: 30 Jul 2013 11:11 AM PDT

Dear Expert,I would like to know if there is a query to findout if index is current rebuilding/reorganizing for table X so that I can make the program wait for it to complete before issue certain command against the table for example SqlBulkCopy as SqlBulkCopy is fails consistently if re-index is happening at the same time. I know drop/recreate indexes would be a work around but with the way my application works, I cant afford to drop indexes or stop the re-indexing process either. Thanks for your help!

openquery single quote

Posted: 30 Jul 2013 09:44 AM PDT

Hi All, I have an application which automatically generates SQL - To connect my application to my SAAS database I am having to use a LINKED server. The problem I have is with single quotes as the SQL used has to be enclosed in single quotes. The string my application adds to the sql is: a.acctnum like 'ME4%' and e.entityid='100AAA' I need a way of taking this where clause and added an addtional set of single quotes.

Opening .dts package from SQL Server 2000 in Visual Studio 2008

Posted: 30 Jul 2013 07:39 AM PDT

I can't believe I'm having this much trouble with this, but I am stuck. I have a .dts package that was saved to a SQL Server 2000 database. Unlike what I'm used to, I cannot seem to access this file in Visual Studio 2008.I know that there will be a compatability issue with this, and the main thing I am trying to do is test out how to do this so that I will have a solution ready when I need to make these changes in the near future. Can anyone offer any advice? Can you tell me if what I'm seeing sounds correct (i.e. - I cannot track down an acctual .dts file. Instead I only see the package created on the server. I thought it was in SQL Server 2005 that first allowed that.)?Thank you,Jarid

CheckDB inconsistency error

Posted: 29 Jul 2013 11:36 PM PDT

hello there, I have an issue with CheckDB giving the error below.If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]checking xxxx [SQLSTATE 01000]Msg 8914, Sev 16, State 1, Line 1 : Incorrect PFS free space information for page (1:208) in object ID 60, index ID 1, partition ID 281474980642816,Is there anything i can do about that i have read that this is a bug.. Thank you in advance

Catch text before symbol

Posted: 30 Jul 2013 03:15 AM PDT

Hi geniuses,consider:[code="sql"]create table #mytable( Value varchar(50))INSERT INTO #mytable (Value) SELECT 'First.Second'[/code] [b]I need to isolate what's before the symbol: '.'[/b]I was able to catch whats after the symbol:[code="sql"] SELECT Value, (substring(Value,charindex('.',Value)+1,LEN(Value))) AS 'beforedot' FROM #mytable[/code]Thanks in advance!

SQL Native Client - How can you tell it's being utilised as a connection?

Posted: 29 Jul 2013 07:53 PM PDT

Hi All, Apart from turning it off with all of the associated protocols, how can you tell it's being used?Thanks in advance

ID and Exclude Top & Bottom 2.5 by Contract

Posted: 30 Jul 2013 12:04 AM PDT

Hi All, I'm trying to filter out my results the top and bottom 2.5 percent records by meeting lengths per contract.I currently have this query which ID's the top and bottom 2.5% but as a whole and not by Top 2.5 for Contract X and Top 2.5 for Contract Y etc.[code="sql"], x2 AS( SELECT caseref, [contract] FROM ( SELECT TOP 2.5 PERCENT caseref, [contract] FROM @avgep ORDER BY DATEDIFF(SECOND, casereceived, CaseEnd) ) AS lowest UNION ALL SELECT caseref, [contract] FROM ( SELECT TOP 2.5 PERCENT caseref , [contract] FROM @avgep ORDER BY DATEDIFF(SECOND, casereceived, CaseEnd) DESC ) AS highest)[/code]I imagine a CET is involved but I can't see the wood for the trees at the moment. Any help would be great.

multiple insert from one table depending on the number of rows in another table

Posted: 30 Jul 2013 01:17 AM PDT

Hi I have 2 tables as below which I import to sql server from supplied text files. I would like to repeatedly select / insert all the same rows from Table1 into a new table depending on the number of rows there are in Table2 adding the ID from Table2 each time per insert. As per the results table below. Both table1 and Table2 could have a variable number of rows. I'm not sure how to accomplish this at he moment, maybe to use a for each loop or a cursor. Any help would be appreciated. Table 1[b]chr start end[/b]chr1 14501 18250chr1 102501 103750Table 2 [b]ID[/b]100001100007result [b]chr start end ID[/b]chr1 14501 18250 100001chr1 102501 103750 100001chr1 14501 18250 100007chr1 102501 103750 100007

configure replication

Posted: 29 Jul 2013 09:13 PM PDT

How to configure replication in different domain ?

Non-Pageable Memory

Posted: 29 Jul 2013 10:17 PM PDT

Suddenly got struck in a confusion(I might be wrong too). I will really thank and appreciate any light sheds. AWE is non-pageable? right. It means it cant be hard-fault. Then why we need LOCK PAGE in MEMORY?

Migrating System Databases Between Clusters

Posted: 29 Jul 2013 10:26 PM PDT

Hi there,I have the need to migrate a 2008 R2 SQL Cluster onto a new site (unfortunately physically moving the kit is not an option). We will prepare a new cluster to be identical to the existing one in terms of SQL version, file structure etc.Once that is ready I would ideally like to backup and restore the system databases to the new cluster to make the process easier. Does anyone know if there are any special considerations when transferring system databases from one cluster to another, or is it exactly the same process as it would be if they were stand-alone servers?I'm aware moving system databases can sometimes be a bit of a minefield, but it would save a lot of work!Thanks,Matt

Find and update the Schema changes of a table?

Posted: 26 Jul 2013 12:02 AM PDT

Hi Friends, I have around 25 tables where i am frequently using to put the data from live server to my local . The schema's of the live database gets changed frequently. I mean we add one or more columns evertime. When i use my SSIS package , to pull the data from live server to my local, it is messing up. I have to drop the database and recreate it with new schemas and update the package as well. So what i am looking for isJust Curious..1. How do we create a script to update the schema ? I know there is Red Gate tool which does this comparision of the schemas. But is there a way to create some SQL statements to update the schema? (because I can't buy the red gate software. price is not affordable :-))2. How can we avoid the SSIS package getting this error? You know if we have added some fields in the table, then i have to update the package as well....and then using it.Any suggestions would be really appreciated. Sorry in case , if my questions are meaning less...

Table Partition

Posted: 29 Jul 2013 09:52 PM PDT

Hi All,I have partitioned VOICE_CALL table, size=130 GB(spill-ted into 10 ndf files) But before partition mdf files size 350 GB,After partition mdf file size 351 GB.10 Ndf files size 99 GB so total database size is 449 GB [b]Why mdf file size not decreased ? even-though I was moved data to ndf files ?[/b]Thanks,Ramana

SAN disk size expansion

Posted: 25 Jul 2013 04:04 PM PDT

Hi,We were trying to expand SAN disk size more than 2 TB as part our requirment. But it failed due to the limitation, NTFS can support maximum of 2 TB. Is it so? I am unaware such windows limitations! On the other way, how to work around and hot to expand a SAN disk more than 2 TB. Any suggesstion would be highly appreciated.My environment is: Windows Server 2008 R2, enterprise editionSQL Server 2008 R2, enterprise edition6 SAN disks are configured with >1 TB to < 1.5 TB. We want to expand those SAN disks to more than 3 TB each. Thanks!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog