SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

1
vote
1answer
59 views

Efficient way to store multi choice in a column with few options

I am looking for advices on how to handle multiple choices in a column? By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options ...
9
votes
2answers
180 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
0
votes
0answers
11 views

Why is Echo statement in SQL query not writing in text file? [duplicate]

Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
1
vote
2answers
41 views

My Ola Hallengren nightly backup job failed but It works fine when I execute the job manually by right clicking

I have been using Ola Hallengreen maintenance plan . It has been working great for longtime . Recently one of my nightly backup job failed repeatedly with an error: Sqlcmd: Error: Microsoft SQL ...
3
votes
0answers
64 views

Query with sp_executesql returns nothing while query without it returns the result [closed]

I'm using MS SQL Server 2008 R2 through ORM in .NET application. This ORM uses ADO.NET, so any query will be wrapped in exec sp_executesql. One of the generated queries returns nothing, but it ...
1
vote
4answers
62 views

UNION xp_cmdshell

I'm trying and failing to do something a follows select 1 union xp_cmdshell 'whoami' Is there anyway to get this work? The simpler the better i.e. one statement.
2
votes
3answers
33 views

Suddenly cannot access SQL Server database

My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below: It cannot be detached and attached. I need to ...
1
vote
0answers
29 views

Move extended property comments to create table comment

I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database ...
2
votes
2answers
51 views

RIGHT() works in a SELECT but not in an UPDATE? [closed]

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
5
votes
1answer
38 views

SQL Query not throwing error even if the entered scale value is higher than specified

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'. Column 'A' datatype - numeric(5,2) Case 1: Entered value: ...
3
votes
1answer
68 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
-2
votes
1answer
27 views

Calculate the fee with conditions

I have two tables 1 is fee-structure and 2 is student-info, in fee-structure I have define all classes fee like.. Class tuition-fee swimming-fee cricket-fee etc. Class wise and in student-info I ...
1
vote
0answers
68 views

Performance measuring on complex query

Now that I have overworked a very very complex sql statement by going over all those little parts and investigating on their missing indexes, index design, queries column order, join vs. exists... I ...
4
votes
2answers
103 views

Replace special characters in a column with space

I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid): SELECT columnA FROM tableA ...
2
votes
0answers
22 views

Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is ...
3
votes
2answers
67 views

How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

Problem We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year. The SQL-Server instance in question has very high CPU usage ...
4
votes
1answer
38 views

How long does it take for powershell to restore a SQL Backup?

I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name: $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)") ...
1
vote
0answers
42 views

SQL Server 2008 R2 service pack steps …on cluster

I have a sp3 to apply on SQL Server 2008 R2 on windows 2008 R2 cluster with four nodes and 6 instances. Node 4 do not have any resources but is possible owner for all the instances. Steps I am ...
3
votes
2answers
313 views

Is there a Maintenance Plan that checks my database for corruption?

Is there any way to make a maintenance plan check all my databases on a single instance? Is the maintenance plan Check Database Integrity Task enough ?
1
vote
1answer
54 views

SQL Server Database backup regressed to historical date

I have the following code snippet that takes backup of my production database every night - use My_database go checkpoint go use [My_database] go DBCC SHRINKFILE (N'my_db_log', 500) go backup ...
1
vote
1answer
44 views

Phantom comparison data in SQL Server Reporting Services - How do I remove it? [closed]

I'm trying to write a query that will allow me to include or exclude a filter in my where statement based on whether a parameter passed in is set to true or false. So far my statement is IF ...
7
votes
2answers
154 views

Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?)

We have a table with spatial data and some attributes and need to aggregate it so it could be used in various queries. We have the mechanism to do the aggregation on-the-fly via CLR stored procedures ...
4
votes
2answers
100 views

SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck?

I am looking for an indication (if any) that would either disregard the memory bottleneck option, or accept it, or make me investigate further. for instance: page life expectancy: SELECT ...
7
votes
3answers
150 views

what AD groups logins my user belongs to?

I am not sure if I have chosen the right title for this question. what I am really after is, given a individual windows AD user, I would like to find out the list of the windows AD groups ...
2
votes
0answers
39 views

Database Corruption - object in sys.parameters does not have a matching row in sys.objects

I'm using SQL Server 2008 R2 and I have a strange db corruption. I ran dbcc check db and the output is: Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters ...
7
votes
2answers
157 views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
-4
votes
1answer
37 views

SQL Server and msbi relation and difference [closed]

With SQL Server 2008R2, we have analysis services, reporting services, and data moving and manipulation services with SQL Server Management Studio. So why do we need SSIS, SSRS, and SSAS?
8
votes
3answers
625 views

Avoiding a divide by zero error in a computed column

How do you avoid divide by zero error in the below table? CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/ Denominator) ) ...
2
votes
2answers
183 views

SQL Query is getting progressively slower

We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can. We have a webserver and database server through ...
-1
votes
1answer
54 views

How to retrieve and replace Null values to another column value in inner join table

There are four tables. The below i have mentioned in screenshot. From the above DeliveryOrder_TBL, NotifyPrtyID NULL then ConsigneeID should replace for that NotifyPrtyID place. How to do?? I want ...
1
vote
0answers
73 views

SQL Server Transactional Replication Distribution Database not keeping up

We have following setup at the moment: 8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication. The 8 servers are replicating to a ...
5
votes
2answers
445 views

Best way to get last identity inserted in a table

Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance? SCOPE_IDENTITY() Aggregate function MAX() ...
1
vote
0answers
84 views

Permission Error when creating Linked Server from SQL Server to Access DB

I created a linked server from my SQL Server 2008 R2 64 bit Windows Server 2008 machine to an Access 2000 Database that resides on another server. The Linked server was created with no trouble using ...
1
vote
1answer
52 views

How can I get the whole execution log of stored procedure?

I know that how many times it was executed from execution_count in sys.dm_exec_procedure_stats.But how these values are Calculated? Is there any way to know how these values get calculated? I want ...
11
votes
4answers
300 views

Using third party tools to backup

Is there any advantages to use Third Party Tools to make Backups ? I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL ...
0
votes
1answer
24 views

How to delete/remove the orphan user's and its schema from server

I'm new to a role as SQL Server DBA. I need help how to find out the ORPHAN user's in SQL Server 2008 R2 and how to delete/remove the orphan user's and its schema (please give the reference where I ...
3
votes
3answers
76 views

Manually create index fragmentation

Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table? I need this for the purpose of a ...
3
votes
1answer
50 views

SQL Server - SQL Agent - Allow a Group to own a job

Scenario There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move ...
0
votes
1answer
31 views

Mixing Clustered Instance and Non-Clustered Instance

I'm currently managing a 2 node SQL Server Failover Cluster in Active/Passive setup. I plan on adding a new SQL Server instance which doesn't have to be on the Failover Cluster. The only documentation ...
4
votes
2answers
67 views

SQL server timeout after downgrade

After a downgrade from SQL 2008 R2 Enterprise to SQL 2008 R2 Standard. SQL Server after some time starts lagging and CPU use is around 95%-100% and I can't log in in "MS SQL server management studio", ...
9
votes
1answer
425 views

Where does an execution plan come from?

Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
4
votes
1answer
42 views

Nest SUM() Function Inside Case Statement?

I am trying to run this syntax, but I get an error of Msg 4145, Level 15, State 1, Line 9 An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'. ...
7
votes
3answers
74 views

SQL Server: Using Dual Databases for Performance?

We have a SQL database that stores application usage logs for about 3000 PCs. These PCs send their usage data to the SQL server around 10-20 times per day. We used to store only the most recent 60 ...
0
votes
1answer
15 views

Getting a logical consistency-based I/O error error in sql server

I am using SQL Server 2008 R2. My server got accidentally shutdown. After restarting it, I am not able to access my database. I am getting the following error for a few tables: SQL Server detected ...
2
votes
1answer
46 views

SQL Server 2008: Unused Clustered Index

I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy. It contains Tweets, and the id of each Tweet is one of the ...
-2
votes
0answers
64 views

SQL Server Management Studio Has Stopped Working [closed]

I was executing a large script and suddenly the Sql Server had crashed, after that I tried to run the SQL Server Management Studio, but was getting the mentioned error. I even tried to repair the SQL ...
3
votes
0answers
35 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
2
votes
1answer
64 views

Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database ...
4
votes
1answer
43 views

How to handle Schedules when moving Jobs to another server?

We are migrating our SQL Server 2008 R2 to another physical server. For this, I will script out all jobs and run it on the target server. My question is about handling the schedules: When a job is ...
9
votes
1answer
130 views

How to reproduce “Could not continue scan with NOLOCK due to data movement”

I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries. I understand this has something to do with ...