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

Get attribute value of XML

I went through at least 5 question on SE, but I must be doing something very bad out of frustration already. I have a stored procedure that returns an XML like this: <pmsg:Messages> <...
1
vote
1answer
29 views

Can I use SNAPSHOT for large catalogs?

I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine. I liked the Idea and i will start to study about this, but can I use snapshot for large ...
0
votes
0answers
42 views

Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB

I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB. ...
8
votes
2answers
107 views

Increase a counter for each changed row

I'm using SQL Server 2008 Standard, which doesn't have a SEQUENCE feature. An external system reads data from several dedicated tables of the main database. External system keeps a copy of data and ...
1
vote
2answers
66 views

Change database loaction

I have installed new MS SQL Web Edition on C Drive and I have also another D drive on server. Now I want to change database location, like when we make new database it should be make on D drive. Can ...
3
votes
1answer
43 views

Any disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY?

I've been playing around with Ola's maintenance scripts recently, and they are absolutely great. However, I was considering one scenario and was surprised when I found it wasn't catered for. Maybe I'm ...
1
vote
2answers
21 views

issues with creating varchar returning functions [closed]

I have an issue while creating functions in sql server, am very new to sql server Below mentioned is the code which i used, please any issues with syntax. CREATE FUNCTION Fn_Check_Customer_Credit (@...
3
votes
1answer
58 views

Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

I have the following tables and content create table t(i int primary key, j int, k char(6000)) create index ix on t(j) insert into t values(1,1,1) insert into t values(2,1,1) insert into t values(3,...
1
vote
1answer
33 views

Remote Connection to SQL-Server for data extract [closed]

I have zero experience with MSSQL Server, but I'm trying to determine the most effective way to extract data from table into flat files from a remote location. Here's the usage case scenario: We are ...
1
vote
2answers
33 views

Maximum Attachment Size in database mail

Good Afternoon, I am trying to send an attachment via database mail. declare @mail_recipients VARCHAR(4000) = 'abc@123.com' , @mail_subject VARCHAR(500) = 'subject' , ...
1
vote
2answers
36 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
53 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
232 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
82 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
129 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
81 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
33 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
38 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
41 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
156 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
31 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
241 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
20 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
129 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
0answers
44 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
105 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
11 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
34 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
36 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
101 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
29 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
118 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 ...