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

learn more… | top users | synonyms (2)

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
57 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
71 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
85 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
84 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
1answer
46 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 ...
3
votes
2answers
57 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
40 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
73 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
33 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
69 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
557 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 ...
3
votes
1answer
36 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 ...
4
votes
2answers
580 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
49 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
48 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
320 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
47 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
172 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
224 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
340 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
57 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
213 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
44 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
649 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
209 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
89 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
109 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
855 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
148 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
59 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 ...
10
votes
4answers
459 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
26 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
117 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
99 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
40 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
76 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
433 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
63 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
97 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
33 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
47 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
146 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
37 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
85 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 ...
3
votes
1answer
44 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
154 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 ...
2
votes
1answer
86 views

Script file execution through Power shell

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell. ...
7
votes
1answer
158 views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...