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
2answers
37 views

SQL Table Design Schema - To Normalize or Not?

I am considering a table schema design for a project. The design involves a Many-to-Many Relationship. The 2 related tables are: a) Class b) Subclass A Class can have many Subclasses, and a ...
0
votes
1answer
57 views

AG (SQLServer 2014) - Suspend Data Movement

I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site. For DR testing, is it possible to failover to the secondary replica, make (test) ...
3
votes
1answer
233 views

How to avoid implicit conversion for an Integer column

SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode] FROM [AdventureWorks2014].[HumanResources].[Employee] where BusinessEntityID = 5 I get convert_implicit() function ...
3
votes
1answer
84 views

how to find out what process(es) caused the autogrowth of a database or a database file?

I have a process(es) hungry for tempdb, but I am struggling to identify this process. any ways I could achieve this? We have been alerted as Tempdb has now grown into the space you reclaimed ...
0
votes
0answers
9 views

Provide Access to view when access on table is denied

I have a table T1 in database which contains columns C1,C2,C3 AND C4. Of which C1 and C2 contains sensitive information. Access to this table is denied to a group G1. But i need to provide access to ...
5
votes
6answers
132 views

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible. I know that xp_cmdshell itself poses risks, even with a proxy ...
1
vote
1answer
82 views

Where is “UNDO” in SQL Server?

Seems like a simple question but I've found conflicting information out there. Can anyone enlighten me (ideally with a rock-solid source)? Is it: Part of the transaction log, per database? Part of ...
1
vote
2answers
33 views

Performance issues with partial join query

I have two tables: dbo.allowedURLs (a unique list of all permitted domains) *** DomainName *** google.com facebook.com etc... dbo.loggedURLs (a list of URLs processed by a proxy server) *** ...
3
votes
1answer
34 views

how to change the file directory of your log and data files and what are the effects

I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is ...
0
votes
0answers
35 views

What is the VERSIONING_COMMITTING wait type in SQL Server 2012?

I have a fairly large SQL Server 2012 database in an availability group that experienced performance problems for about an hour, with the bulk of the wait in HADR_SYNC_COMMIT (waiting for synchronous ...
0
votes
1answer
39 views

Cannot restore SQL database backup to new database because new database is in use

I am attempting to create a new database from a backup file of an old database that is still in use. At one point I was able to successfully restore the database, but had to delete it later after ...
0
votes
1answer
44 views

“CREATE FILE encountered operating system error 32” when attempting to attach database

I am having trouble connecting to a database I created in SSMS. Keep in mind this is my first time working with databases. All I've done in this database is create some tables and add a few records. ...
3
votes
2answers
157 views

SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
0
votes
1answer
21 views

Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

I'm using survey monkey's API to dump survey data into a SQL data warehouse. So I'm dumping response data into a table 'resonses' thusly: survey_id, Q1, Q2, Q3, Q4, Q5 survey3944, 848948344, ...
1
vote
0answers
45 views

SQL Server: How to select a snapshot for a transaction? [closed]

PostgreSQL has a feature to select a snapshot for a transaction using: SET TRANSACTION SNAPSHOT snapshot_id Does SQL Server have a similar feature? Edit: Answer to: What is the use case? In an ...
1
vote
0answers
32 views

Query Statistics in management data warehouse empty

i am using management data warehouse form SQL Server 2012. I used it before on a 2008 instance a few years ago and everything worked good. However this time i cannot see any Query Stastics data ...
15
votes
3answers
1k views

Was Management Studio removed from SQL Server 2016 installation media?

I just tried installing SQL Server 2016 Enterprise but I noticed that Management Studio was not installed by default. I also noticed there is a separate link to SSMS in the installation guide which ...
0
votes
2answers
30 views

Always On Listener Behavior - DBs removed from group

We have multiple multi-site always on failover cluster instances in our env. We have an enforced internet outage being imposed on us and at the time the link between the data centers will be down for ...
1
vote
1answer
33 views

Why does Publisher need to be able to connect to Remote Distributor?

I have Transactional Replication set up with a Remote Distributor and a Pull Subscription. So as I understand it, at one end I have the Publisher, and at the other end I have the Distributor, Log ...
0
votes
0answers
33 views

Help on Database Table Design

I am trying to create a website with multiple Q&A. The main features I need to support are as below There will be multiple types of questions. (a. Single option Answers, b.MultiOption, c. True ...
1
vote
3answers
245 views

How to create a Unique (Index) constraint across multiple tables?

Is there a way to create a unique index across tables in a SQL Server database? I have two tables, Table A and Table B. In that I have a Column named ID, I want to make these Id columns as unique (...
0
votes
1answer
21 views

Live Database Sync. MySQL - SQL Server

I have a Rarer scenario, Is there any possibility to achieve the target? Here is the Situation: I have Developed a Responsive site using php with foundation framework and the back-end is MySQL for a ...
6
votes
2answers
130 views

Estimated Operator Cost Calculation

Everywhere I have read says that the Estimated Operator Cost is the sum of the Estimated CPU Cost and the and the Estimated I/O Cost. However, in many operators I see, this is not the case. Here is an ...
0
votes
0answers
25 views

MAXDOP Recommendations - Is my understanding correct? [duplicate]

I have a VM SQL 2014 Server with the following CPU setup. If I follow MS recommendations for MAXDOP settings (https://support.microsoft.com/en-us/kb/2806535) I believe that MAXDOP should be 2 as the ...
0
votes
1answer
28 views

sql server 2012 developers edition license and 3rd party applications [closed]

Our company is using an ERP system for our product inventory management and customers orders. The newest version uses MS SQL 2012. if we are not developing software and the only users accessing the MS ...
1
vote
0answers
17 views

Report Builder does not connect for a remote reporting Server

Current Environment I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server. The users are connecting to ...
0
votes
0answers
18 views

Hidden Index on a table [duplicate]

I am running into an issues with locating index In SSMS, if I expand Users>Indexes, I do not see an index named "_dta_index_Users_5_1466188719_88410000_K1_K6_K2_7_10_11" But I can see a statistics ...
0
votes
2answers
29 views

SQL 14 AG go into read only when cluster is down, how?

Looking at SQL 14 AG and trying to get this to work https://blogs.technet.microsoft.com/dataplatforminsider/2013/06/27/alwayson-in-sql-server-2014-ctp1/ Increased availability of readable ...
0
votes
1answer
39 views

Better way of re-arranging the indexes for better performance

I ran a script to find overlapped and duplicate indexes for a database. Below is the sample screen for the above said. Please advise how can I arrange them or what should be the guidelines to make ...
1
vote
2answers
55 views

Why not create ColumnStore index on every single table?

As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table? Edit: In SQL 2014 we ...
0
votes
1answer
25 views

sp_recompile does not recompile inline TVF. What alternatives are there other than using an ALTER statement?

In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work? I have ...
2
votes
2answers
107 views

XML Parsing Illegal XML character error in SQL 2008

I was running this query in SQL Server 2008 (10.0.5890), Collation: SQL_Latin1_General_CP1_CI_AS SELECT CAST(event_data.value('(event/data/value)[1]', 'NVARCHAR(max)') AS XML) AS DeadlockGraph ...
0
votes
1answer
51 views

how to make table names dynamic in SQL? [closed]

I am trying to make the table name dynamic declare @varString as nvarchar(10) set @varString = '06232016' declare @testString as nvarchar(500) set @testString = 'SELECT * from ' + 'Cases_' + @...
0
votes
0answers
12 views

Cluster shared volumes storage configuration

When setting up and configuring Cluster Shared Volumes (CSV) for a SQL Server 2014 build out, is it recommended to create separate csv volumes for tempDB, SystemDB's, UserDb's, and UserLog's?
0
votes
1answer
35 views

Is there a way remove SQL Server listener IP on maintenance

Is there a any way to stop listening listener on the SQL Server on production server. I want to be sure nobody is trying to connect my production server when it is maintenance mode. I disabled all ...
0
votes
1answer
38 views

Microsoft ODBC Driver 11 for SQL Server: Named Pipes Provider: Could not open a connection to SQL Server

I am aware this question has been asked before. I have been following the extensive troubleshooting steps at MSQL Tips as well as every answer I can find on Google. I am really stumped. The error is ...
0
votes
1answer
93 views

Delete missing database

Event Viewer, I can fins this event logged : 2016-07-10 11:45:01.83 Logon Error: 18456, Severity: 14, State: 38. 2016-07-10 11:45:01.83 Logon Login failed for user 'Domain\Username'....
3
votes
1answer
102 views

Object_name(object_id) returning incorrect results

We are trying to exclude stored procedures which have name like '%backup%' or %WI%'.. Here is the total query i used.. ;WITH CTE AS (select object_name(object_id) as procname from sys.sql_modules ...
0
votes
0answers
30 views

Query runs 10 times slower on one node compared to another using the same database

I have got an AlwaysOn database on two nodes and have discovered that the same query takes less than half a second to run on one node but around 7 seconds on the other one. It doesn’t matter whether ...
3
votes
1answer
120 views

SQL, SQLCLR objects & effective memory utilization

We've recently converted our ERP system from IBM Universe to SQL Server. Application performance is generally tolerable but occasionally degrades to terrible. We're running the database on Win ...
1
vote
1answer
30 views

set default datatype = char (instead of varchar)

I'm setting up a database in SQL-2014 that is exchanging data with a mainframe. All the fields are char, so there are trailing spaces. In table designer, my default field type is varchar. Is there a ...
3
votes
1answer
80 views

SQL Server 2014 accounts for Daylight Saving Time now?

My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference. CREATE TABLE datetimeoffsetfunction( ...
1
vote
1answer
71 views

What is the best way to save a large array in database?

Currently, I'm working on a simple CBIR (Content-based Image Retrieval) and I have a question. What is the best way to save a large array on the database? For example, I want to save the results of ...
0
votes
1answer
45 views

Improve SQL query performance, need help

I don't see any missing indexes on the execution plan apart from Hash Match, Parallelism and Index Scan. I get 218,673 rows in 5 minutes. Planning the improve the performance. Here is the sql query, ...
2
votes
2answers
50 views

How to find all views with invalid column names

I am trying to refresh all my views using code I found on the web: DECLARE @ViewName VARCHAR(256) DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views OPEN cViews FETCH NEXT FROM cViews ...
1
vote
1answer
63 views

BCP utility:“Copy direction must be either 'in', 'out' or 'format'”

I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error: Copy ...
0
votes
0answers
53 views

T-SQL OPENROWSET Permission Issue

I've developed a custom OLEDB provider and I was able to use it without problems on many SqlServer installations but now I'm facing a problem I can't figure out: This query works just fine if I run it ...
0
votes
0answers
15 views

Regenerate a data model in report builder

Is there any way to automatically regenerate a data model in report builder? I am using SQL server 2012 and Visual studio 2013. Please help.
1
vote
1answer
57 views

How to set rank per group with a SQL UPDATE statement?

Please first refer to the table structure: Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year. I need to rank the managers for every ...
-1
votes
1answer
41 views

Line number in SQL Server Messages

this is probably a silly question, but I am just not able to find a answer for it... When there is an error in T-SQL statement, SQL Server would generate an error message, sometimes with a line ...