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
1answer
27 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
104 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
35 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 ...
-5
votes
0answers
35 views

Data file size again increased [closed]

I have database of size 500 GB. Among that 500 GB free space available around 110 GB. So I shrinked my database file to 450 GB. I need that 50 GB free space in my drive for other purpose. it worked ...
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
28 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
115 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
77 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
70 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
43 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
46 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
59 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
51 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
14 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
39 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 ...
0
votes
1answer
20 views

Create a view showing total minutes per customer per month and change from previous month

I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report. The issue I am having is with restricting the ...
0
votes
0answers
94 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
33 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
27 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
28 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
57 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
939 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
71 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
56 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
30 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
115 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 ...