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)

0
votes
0answers
96 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
0
votes
1answer
34 views

Installing SQL Server TDE on a Windows Cluster

i'm going to be upgrading two Instances to Enterprise and thenI'm going to be installing TDE on two sides of a Windows Cluster. All of the databases are on one storage device and whichever Instance is ...
0
votes
1answer
37 views

Data Modeling constraint issue

Can someone help me with this school question? I have this data model shown below: here is the question: You are modeling an emergency communication system used to contact teachers in case ...
0
votes
2answers
47 views

SQL Script to Exclude Weekends and Holidays

I have a query where I need to add 2 days to the date variable based on a condition. However, this date variable needs to skip weekends and holidays. I have a table that stores holidays. How do I ...
1
vote
1answer
28 views

Nonclustered index Seek

Question about Index usage: If I create a nonclustered index like IX_LastName_Age Where it is created by LastName ASC and Age ASC, and there is clustered index on ID field. Now if I run a query ...
0
votes
2answers
34 views

AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]

I have what I believe to be a correctly written sql query that calculates the average using an over clause to window the results. However, I'm getting an error when I include the over clause. SELECT ...
0
votes
1answer
48 views

When were OLEDB based linked servers added to SQL Server?

I guess what I'm asking is in which version of SQL Server were linked servers added to SQL Server?
0
votes
1answer
25 views

How to change “Initial File Size” before restoring

I have backup of size 10GB. When I restore DB on server it will take around 100GB due to initial file size set to 100GB. I could change it and shrink to around 14GB. Now I want to restore db on my ...
1
vote
0answers
29 views

Error when trying to install SQL Server 2012 Ent in to a Server Core 2008 VM

I have a problem that I hope you can help with. I am studying for my 70-462 exam and in order to do so, must setup virtual labs as follows. DC: Windows Server 2008 R2 Enterprise (Full Installation) ...
1
vote
1answer
58 views

Move database to another drive in same server

I have installed MSSQL server 2008 in my server in C: drive and I want to move it to the D: drive completely. That means, database locations, installed location to move another drive with data. Is it ...
1
vote
1answer
37 views

Integration Services License requirements [closed]

Not sure this is something which people will know on here are not. We currently have a BI setup where we have Integration services on one SQL instance and the data warehouse database on a separate ...
12
votes
2answers
942 views

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
0
votes
1answer
30 views

Can't login to sql server custom db

I have wamp installed in my system and sql server 2014, and configure so i can access sql server db through php.. with this code: <?php $serverName = "MY-PC\sqlexpress"; $connectionInfo = array(...
1
vote
1answer
73 views

Group by's in materialized views and query planning [duplicate]

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table. The query is something like SELECT COUNT(*) FROM BaseTable WHERE Slot ...
1
vote
1answer
46 views

SQL Server 2016 Standard AlwaysOn group

In my SQL Server, I can’t see that options (Basic Availability Group, Database level health Detection, Per DatabaseDTC Suport) at New Availability Group – Specifiyname. I just see to write the [...
2
votes
1answer
58 views

How can I connect to SQL Server from All IPs?

I have problem with access to SQL server with TCP/IP. I would like to SQL instance accept client from all network adapter that machine has. I go to configuration manager console and enable TCP/IP, ...
4
votes
1answer
57 views

using Change Tracking functions from another database

If I turn on change tracking on a database called Production, can I use the functions to get the change data from another database? For example: CHANGE_TRACKING_CURRENT_VERSION(); I have tried the ...
3
votes
0answers
44 views

ODBC Connections locking tables in SQL Server

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
1
vote
2answers
58 views

SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners?

I need help to deal with a problem about permissions in my database. This is the scenario: 1 Database 4 Schemas: schemaA; owner dbo schemaB; owner ownerX schemaC; owner ownerX schemaD; owner ...
2
votes
2answers
31 views

SQL Server agent job dependency

We have a 4h datawarehouse job that runs every 4h on 4h schedule. We want to create a new 'daily' schedule and have some processes run out of hours. However, if when the daily job comes to run and ...
0
votes
1answer
124 views

How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so ...
3
votes
0answers
40 views

How much disk space do I need to add a primary key to a very large table?

I'm using Microsoft SQL Server 2014 - 12.0.4100.1 Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) I have a 491 GB table with 3.6 billion rows that does not have a primary key. The ...
3
votes
2answers
67 views

How to escape XML characters in TSQL before converting to XML?

I have XML structure which is input param for my stored procedure. It contains element with & (which is escaped in XML). When I extract that element to VARCHAR I receive & which is not valid ...
3
votes
2answers
78 views

If view A is built atop other views and tables, does a user need permission to only view A, or everything down?

I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view ...
-1
votes
0answers
27 views

View NULLABLE Column Issue

I have a View that contains a column from a base table. The column is set to NOT NULL in the base table. However, in the view it is set as NULL. Even if I mouse over the CREATE VIEW script it shows ...
0
votes
0answers
11 views

Extended Events Live Data Not Reporting All Events [duplicate]

I'm fairly new to Extended Events and am currently working with how to view the data that gets captured. One of the things that I really liked was the "Watch Live Data" view; you can see everything ...
1
vote
1answer
46 views

sp_help with different object types with the same name

I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown. Is there a way to use sp_help or an equivalent to show the ...
0
votes
0answers
28 views

NULL value in query_plan column from the sys.dm_exec_query_plan DMV [duplicate]

I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL. ...
4
votes
1answer
347 views

Why does my execution plan (sometimes) include a left join?

I found a similar question and am aware that in order for a query of the form SELECT COUNT(1) FROM foo f LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2 to perform without ...
0
votes
0answers
20 views

SQL Server authentication through AD FS

Is it possible to set up SQL Server as a service provider of an AD FS server? I would like to be able to log in to SQL server like with a normal Active Directory password login but have it be ...
0
votes
2answers
32 views

Display list of SSRS reports in a dropdown list, and allow users to select which report to display

Our company has about 10 different SSRS reports. We would like to allow users to navigate from one report to another simply by selecting the report from a dropdown list. Is this possible?
1
vote
1answer
31 views

Change Name of Extended Event Session

I've just recently started working with Extended Events and am loving it. However, I ran into a small annoyance and am wondering if there's a way around it. I had a very simple ExEvent session that ...
1
vote
0answers
42 views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
1
vote
0answers
56 views

SQL Server tempdb high IO

Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2. The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. ...
1
vote
1answer
46 views

Get a list of SQL scripts that ran on my server

The schema change history report tells me when the schema of a DB was last changed. I would have run an x.sql file on that same DB for those changes to have happened. Does SQL Server log that I ran x....
2
votes
0answers
26 views

Link SQL Server Databases

I have 2 MS SQL Server Databases in two different offices (different countries), both working with the same in-house program I developed. I want to share the information in the databases between the ...
0
votes
1answer
33 views

SQL Server IOPS

I'm trying to evaluate the IOPS of TPCH queries on SQL Server. Specifically, for TPCH query 13, using "set statistics io on" and "set statistics time on", I got: Table 'orders'. Scan count 9, logical ...
1
vote
1answer
56 views

SUM (CASE WHEN) AS.. query

I'm very new to SQL and I'm trying and failing to get the right syntax on what I presumed would be a pretty easy query. I have a table (CustomerSales) with four columns: Period: a combination of ...
1
vote
1answer
65 views

SQL Server 2014 Triggers and where are they created

I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source ...
0
votes
1answer
48 views

Group COUNT by month less than variable

In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus: CREATE TABLE [dbo].[Employee] ( [employeeID] [int] IDENTITY(9500,1) NOT NULL, [firstName] [nvarchar](50) ...
1
vote
0answers
40 views

SQL Server 2012 AvailabilityGroups - Preferred Backup Replica

I currently have a 2 node SQL Server 2012 HA Availability Group setup. Currently we have a number of SharePoint databases that exist in this availability group. I'm attempting to ensure our backups ...
0
votes
2answers
91 views

Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

is there any effective way to tune the query below? I have a filtered index on OrganisationID <> 0 Primary key is on another field, not relevant for this query ItemCode varchar(20). I have tagged ...
2
votes
0answers
33 views

SSISDB Retention Period

I have looked into articles to get the size of SSISDB down. One good way to do this was set the retention window for logging lower, currently down to 150 from the default of 365 days. I would like ...
0
votes
1answer
41 views

Automatic recovery on a 3 node AlwaysOn Multisite Cluster?

We are looking to build a 3-node AlwaysOn Windows 2012 R2 failover cluster with 2 nodes in our primary datacenter for HA, and a 3rd node replica in our DR datacenter for reporting. Given this is a ...
3
votes
1answer
155 views

Access SQL Server Express LocalDB from local area network

I am developing a software which requires sql server. But for 50 MB application it is not good idea to tell customers to install SQL Server. So I think it is better to use SQL Server Express LocalDB. ...
4
votes
2answers
509 views

Why is the schema prefix (dbo) mandatory when we call a function?

When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema. We can also execute stored procedures without prefix if it's under the default ...
3
votes
1answer
155 views

Ordering of table is based on Clustered index or non clustered primary key?

I have created a table with primary key on name column, the ordering of the table is based on name column create table TestPrimaryKey (Name varchar(20) primary key,id int identity) insert into ...
3
votes
2answers
82 views

Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end?

I have a MS Access frontend that I would like to install on 5-7 computers, so they can access a SQL Server stored on a shared network drive: If possible, is it safe and can my data be corrupted? ...
0
votes
2answers
114 views

Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize?

I have a SQL Server 2008 machine that is running backed by a SAN (not entirely certain of the SAN configuration). I've been noticing that some queries are slow to respond, so have been running some ...
0
votes
0answers
44 views

Simplifying this NULL check [duplicate]

Is there a way to simplify this check? I would like to compare if two columns are equal and that includes if one is NULL and the other has a value (which would mean they're considered different). (A.[...