All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

1
vote
3answers
162 views

Improve a query using cursor to SET Based approach

I am still new to Query optimisation, and I have a Stored Procedure which uses a Cursor to go through each row within the Table, and performs the following operations: Calculate the Time Difference ...
0
votes
1answer
64 views

How to write function in sql which accept table as input and return result back as table?

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in ...
3
votes
1answer
42 views

Allocation Unit ID calculation is incorrect?

I am reading the article http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/ on how to calculate the allocation unit ID based on m_indexId and ...
0
votes
1answer
30 views

Fail to execute SQL command asynchronously

I want to invoke a SQL command “INSERT INTO …” for 3, 000, 000 times to insert 3, 000, 000 records via ADO. Other than the BULK INSERT command, I also want to utilize the ADO.Command object to ...
4
votes
2answers
26 views

How do I get a graph of request traffic history in MS SQL Server 2008 R2

We're having difficulty with an application. One of the potential causes on the table is a regular spike in database traffic overloading the database, causing DB-dependent services to back up. I ...
1
vote
2answers
61 views

SQL Server: Query Efficiency Having vs Subquery

I have the following query where I used a sub query since I can't use aggregates in the where clause. SSMS suggested I use the HAVING clause but then I would need to group by all the other columns ...
0
votes
0answers
36 views

Connecting to SQL Server 2014 with sqlcmd

I am using the version of sqlcmd that comes with the Microsoft ODBC Driver 11 for Sql Server to connect to a remote 2014 instance. I can create a DSN connection using the SQL Server Native Client ...
2
votes
1answer
30 views

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various Stored Procedures, User-Defined Functions ...
0
votes
1answer
46 views

SQL Server 2012 Transactional Replication - twin subscriptions on same machine

Server A publishes Pub1, Server B subscribes to Pub1. Server A and Server B are both SQL Server 2012 Standard Edition, default instances. When I use Replication Monitor to drop-in a Tracer Token, ...
0
votes
1answer
30 views

SQL to XML with multiple values

Hello I'm new to converting SQL to XML. I have been trying to get this to work for the past few days. SELECT Item.foldername AS "foldername", Item.status, Item.vhrid, Item.firstname, ...
0
votes
1answer
49 views

Restored database from .bkp file in SQL Server 2014 generates some strange errors [closed]

I have restored the well-known Northwind database and executed a trivial query: Notice the strange error "Invalid object name..." although the query has been executed successfully and both tables ...
0
votes
1answer
25 views

SQL Server 2014 All Administrator Accounts Disabled

I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) ...
1
vote
2answers
103 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
4
votes
2answers
456 views

Alias of COUNT is not being “recognized” by SQL Server

I have restored the Northwind database(https://northwinddatabase.codeplex.com/) from a backup file using SQL Server. Trying to execute the following query results in error "Invalid column name ...
6
votes
0answers
106 views

DBCC Check DB issue

I have database of 99GB on this database when I am running DBCC Checkdb('IssueDB') It gives error as: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while ...
9
votes
1answer
141 views

Why does SQL Server refuse to update these statistics with anything but fullscan?

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is CREATE TABLE [dbo].[factWebAnalytics]( [WebAnalyticsId] ...
0
votes
0answers
34 views

Characterset in SQL Server - Migrating data from Oracle 8i to SQL Server 2012

I'm migrating data from Oracle 8i to a new MS SQL Server instance (2012). My Oracle database has: NLS_CHARACTERSET = US7ASCII I'm completely new to SQL Server - how do I configure the SQL Server ...
3
votes
2answers
396 views

Why is WITH NOLOCK running as Serializable

I ran the following query: SELECT session_id,CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 ...
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 ...
-1
votes
1answer
49 views

What is the optimized way to fill table values based on it's previous columns values and from other table column values?

CREATE TABLE TEST2 ( RowID INT IDENTITY(1,1), Ratio INT ) CREATE TABLE TEST ( RowID INT IDENTITY(1,1), Col1 INT, Col2 INT, Col3 INT ) Now, I want to insert values in table ...
1
vote
0answers
49 views

What are the things that you will look at execution plan [closed]

I can write some good queries and I am trying to understand reading execution plans and see if there can be a better plan for this query. Below is the checklist I derived over time from different ...
0
votes
3answers
41 views

Incorrect syntax near ')' when executing multiple select statement

Incorrect syntax near ')' How do I execute only one select statement based on user input i.e @id. Here is my syntax. Create Procedure employee @id int, @year int AS SELECT CASE WHEN @id = 1 THEN ...
1
vote
5answers
42 views

Finding server and instance name

I want to know how to find the server and instance name on my computer to connect to the server. I am using the SQLCMD utility, and I am currently able to connect to the server with the line ...
6
votes
1answer
120 views

What is more appropriate sp_updatestats or UPDATE STATISTICS?

In a system there is a nightly data load process where all the foreign keys are dropped, all tables are truncated, repopulated from an external source, and the foreign keys are put back in place. At ...
2
votes
0answers
32 views

Unable to use select top N shortcut on Azure SQL Database via SSMS

Yesterday morning I was able to query my Azure databases via SSMS and the Select Top N menu shortcut. That afternoon I received a couple of alerts that my web applications couldn't connect to the ...
-1
votes
0answers
32 views

SQL server standard edition encryption [duplicate]

I'm trying to look into what kind of encryption I can do with SQL server standard edition from 2008 forth, so far I know that I can't use TDE as that's part of EKM. When I look up this topic I'm ...
1
vote
1answer
61 views

What encryption options are available in SQL server standard edition? [duplicate]

I'm trying to look into what kind of encryption I can do with SQL server standard edition from 2008 and later. So far, I know that I can't use Transparent Data Encryption (TDE) as that's part of ...
1
vote
0answers
58 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
1
vote
0answers
21 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
2
votes
2answers
39 views

Select ID from costumers that do not have contacts last 7 days and 2 times in the month

I have 2 tables: custumer, c_contact. c_contact is all the e-mails I send to my custumers. From now on I need to put a new rule that the costumer can't receive a new e-mail if: 1) It received an ...
9
votes
2answers
175 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
1answer
47 views

Why the backup file size is much larger than the restored database [closed]

I got a 18.3 GB .bak file, when restored, the database size is about 78 MB, including logs (54MB). restore filelistonly from DISK = N'E:\xx.bak' BackupSizeInBytes 23068672 0 I understand that the ...
0
votes
0answers
16 views

CDC Service for Oracle by Attunity Stops with Status Code ABORTED

We use Oracle 11gR2 as our OLTP layer behind our ERP product and we want to capture changes on SQL server for DWH use. In a new VM i make a clean install of SQL Server 2014 with SP1 (DB Engine and ...
1
vote
2answers
43 views

How to turn this query into a Pivot?

If theres something I can't learn, is PIVOT. People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic. How can I turn this query , into a pivot? The ...
0
votes
0answers
17 views

RDS Large SQL Server Instance high cpu low connections?

We are routinely getting high CPU usage with only 12 connections on our M3.large instance. I'm not particularly skilled with AWS RDS, please forgive any ignorance or obvious mistakes. But I don't ...
0
votes
1answer
41 views

Synchronous data transfer from SQL Server to Oracle

I have a requirement like: The table is present both in a SQL Server Database and an Oracle database with appropriate data types. Whenever a record is inserted into or updated in SQL server ...
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
3answers
355 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write 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
1answer
113 views

Conversion fails when converting date average to date in sql

I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string. sql query executed as: ...
2
votes
1answer
57 views

What does Error: 1204, Severity: 19, State: 4 mean?

I've found this error in our SQL log: "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask ...
0
votes
0answers
14 views

Enabling Sql Server / Azure Sql Database Query Store affects performance?

The feature Query Store https://msdn.microsoft.com/en-us/library/dn817826.aspx#Peformance collects data about queries for later use. I assume collecting data affects performance. Has anyone noticed ...
5
votes
0answers
50 views

SQL Server Agent initiating self-termination

From past 1 month one of my server is facing issues with SQL Server Agent. It stops automatically with below error and happens randomly. Sql Agent Output file [097] Memory leak detected [204 ...
6
votes
1answer
100 views

How can we have IIS talk to SQL Server with TLS1.0 disabled? [duplicate]

Background We need PCI-DSS compliance, and so must disable TLS1.0. In my test environment we have SQL Server 2014 SP1 with CU 1 (as per this post) and IIS 8 all on the same box. I used the Nartac ...
4
votes
1answer
255 views

CPU Schedulers Offline

I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me. Some CPU cores are not accessible to SQL ...
2
votes
0answers
36 views

Max Degree of Parallelism for SQL Server 2008

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and ...
1
vote
1answer
17 views

Permissions Error on Member that doesn't exist

My team built an SSAS cube a year or two ago; it was the second cube we've built. We rolled it out to a few teams who have been using it without any issues. Today, I sat down with a new user to set up ...
0
votes
1answer
32 views

Database role to allow .NET program to retrieve MSDB table and view rows

I'm currently writing a database management application that connects to all of my databases and returns metadata for each database. The user currently has db_datareader role for MSDB but the ...
-1
votes
1answer
34 views

AlwaysOn Multi-Subnet Setup

Without using SAN-to-SAN replication, how does data synchronization occur across subnets in a multi-subnet AlwaysOn (AVG) configuration? I want to setup a separate VLAN (replica lan) to synchronize ...
2
votes
1answer
52 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
1
vote
1answer
37 views

DB2 Database Conversion Workbench Issue

I am currently trying to convert a SQL Server database to DB2. I am using the Data Conversion Workbench (DCW) for IBM Data Studio. The issue I have is that DCW can not seem to correctly extract data ...