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
votes
1answer
24 views

How to list all the views and its base object which has unresolved table or other object referece in a paricular database

I want to list all the views which are made up of base objects. That base object is no longer available.Thanks
0
votes
0answers
37 views

DBMS comparison [on hold]

I am interested in getting to know differences in some of the DBM systems. I am mainly thinking of MS SQL, PostgreSQL and mySQL, but am also willing to dig into some other DBM systems, like Oracle. ...
3
votes
1answer
71 views

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with ...
0
votes
1answer
31 views

sqlserver 2008r2 service pack incompletely applied on shared features

I have installed sqlserver 2008R2 service pack 3 on my instance. The installation went through successfully but the instance still shows version number of SP1 in management studio. When I start the ...
1
vote
1answer
35 views

When you drop or truncate tables in SQL Server do old data pages get overwritten with new data?

When you drop tables, does the empty space get automatically reused, or do you need to do some sort of maintenance to allow that space to get reused? Consider a situation where you drop and recreate ...
1
vote
0answers
30 views

Inserting rows into a temporary variable causes plan cache pollution in SQL Server

In my database I use stored procedures to insert multiple rows from a temporary table variable. Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each ...
4
votes
1answer
101 views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
1
vote
1answer
38 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...
1
vote
2answers
30 views

Access to folder is denied (log shipping)

i am trying to log ship from server A to B.I have done everything to configure log shipping but i try to run the job then at Server B while coping backup from server A gives me an error (can not ...
1
vote
1answer
45 views

Can't “Script Database As -> CREATE TO” with SSMS 2008R2 and 2016

I created a SQL Login (Login01), mapped to DB user (User01), member of db_owner database role. When connected locally with this user in SSMS, i try to script out the database (Script Database As -> ...
1
vote
0answers
36 views

Modify data using trigger

I have the following requirements in SQL server. I want to get updated/inserted values, edit it save to one column using SQL trigger. Say I have column named customer_name, when someone updates/...
1
vote
1answer
33 views

Expanding AlwaysOn Availability Groups with Replication Subscribers

I have query regarding "AlwaysOn Availability Groups with Replication Subscribers"... Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)? In which ...
3
votes
2answers
60 views

Group by multiple columns with different members of the same hierarchy?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | ...
-3
votes
0answers
13 views

Collect statistics for whole SQL

I am quite new to SQL Server. I would like to collect some SQL statistics for the SQL Server, including Compilation/sec, Recompilations/sec and Batch Requests/sec. However, I have no idea how to ...
1
vote
0answers
25 views

Master-master replication, is it viable? [on hold]

I'm trying to think of an architecture for my game server. Here is how it would work for two regions: A server instance runs on one machine For each machine there is a slave database to speed up SQL ...
1
vote
1answer
50 views

Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

I have a Budget table which has the following columns: BudgetId, BudgetDate, BudgetAmount and a table Expense which has the following columns: ExpenseID, Date, ExDescrip, Category_fk, ExAMount ...
0
votes
0answers
37 views

Is there a way to prevent an key from ever being changed or dropped?

I worked with a dev once that loved to do this. Every so often he would de-fragment the primary key so that there was no gaps, I think he was a little too OCD. I need to map Microsoft SQL rows to ...
2
votes
4answers
253 views
+50

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 options (answers) and student ...
1
vote
1answer
21 views

SQL Server could not find a login matching the name provided

I have SQL Serer 2012 up and running. I created a new login (SQL Server authentication with password expiration, requires change, etc. turned off). I can start SQL Server Management Studio as the ...
0
votes
1answer
84 views

Update single table with mutually exclusive threads

I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. Thread id:...
5
votes
1answer
73 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
1
vote
0answers
25 views

SQL Server Express 2016 high CPU Usage as soon as I log into Windows

I have a Windows Server 2012 R2 configured for ASP.net applications that connect to SQL Server 2016 Express installed on the server. This server is a VPS with 3 cores and 1.5 GB RAM. The load on the ...
1
vote
0answers
13 views

Get missing index statistics in SQL Server 2014 [duplicate]

For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and ...
3
votes
0answers
82 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
1
vote
1answer
25 views

what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database?

i have 2 database files under a primary file group, one is set to restricted auto growth (35 GB), the second database file from a same file group but storing in different drive from first one and kept ...
1
vote
0answers
21 views

where to find TPC-C queries

I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer? As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www....
1
vote
0answers
15 views

Changing the Owner of Replication Jobs

Just wondering if there is any gotchas with changing the owner of a sql server replication job. The job owner is currently a domain account, but that person is leaving the company, so the account is ...
2
votes
1answer
39 views

How many Read IOPS are too many? (Should I upgrade my RDS instance memory?)

Amazon's best practices indicate you should have enough memory on your RDS instance to hold most of your dataset in memory, and that not having enough will result in more Read IOPS, as information is ...
1
vote
0answers
23 views

Error : failed to Retrieve data for this request when expanding subscriptions in SQL Server 2008

We are getting error when expanding subscriptions to delete them, we have already dropped distribution database. The error text is Failed to Retrieve data for this request. Microsoft.SqlServer....
1
vote
3answers
59 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
1
vote
0answers
31 views

SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of ...
2
votes
1answer
35 views

Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

As you all know, Microsoft SQL Server version 2012 and above are not enabled any more sysadmin role flag to Local System (NT AUTHORITY\SYSTEM) account. In my case it's becomes a problem: I have a WiX ...
0
votes
2answers
24 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
8
votes
2answers
382 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
0
votes
1answer
30 views

Linkserver update not working in Transaction

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand. if I run: update SERVER.testdb.[...
-5
votes
0answers
50 views

Is it possible to take MS SQL Server 2012 backup in particular date range? [closed]

Is it possible to take MS SQL Server 2012 backup in particular date range?
-1
votes
1answer
25 views

Copy tables from SQL Server on another PC to my SQL Server database [on hold]

I want to copy a table's data from a database on another PC to my local database. Can anyone help me with that? I have a different methods of doing that but I need another method. I want to automate ...
3
votes
1answer
92 views

Is it Ok to use GUID as the Foreign Key?

Here's my current situation: We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far. Now, ...
0
votes
0answers
13 views

read-ahead reads in 'workfile' cannot be disabled

In SQL Server, I used "DBCC TRACEON (652)" to disable read-ahead reads. However, the output of TPCH query 9 (http://www.qdpma.com/tpch/TPCH100_Query_plans.html), shows: Table 'supplier'. Scan count 9,...
15
votes
5answers
398 views

From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings. From the SQL Server via DMVs (or Extended Events or ...
2
votes
1answer
64 views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
2
votes
1answer
91 views

Querying True Physical Database File Sizes

On querying database file sizes... 1) Most if not all of these answers do not always match the physical file size shown in file explorer: http://stackoverflow.com/questions/5945360 The accepted ...
2
votes
1answer
78 views

Missing .msi files gives error while repairing SQL Server 2008R2

I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries. In the middle of the repair loading, the window suddenly shuts down and gives ...
4
votes
1answer
97 views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
1
vote
1answer
25 views

Documenting SSRS and SSIS jobs that are the Job Activity Monitor

We are running a SQL Server 2014 database using SSRS as a reporting tool. We have data coming in via SSIS jobs and linked servers. We have developers creating various SSRS jobs and scheduling them. ...
1
vote
0answers
62 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
1
vote
1answer
58 views

SQL Server TempDB size issue

SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx Determining the ...
1
vote
0answers
52 views

SQL Server x64 DBCC Shrinkfile Dies not release Unused space (never ends No errors) [duplicate]

I have a 1.8TB Production database in my test environment on a 2TB drive. Currently there is only 1 .MDF and 1 .LDF (on a separate drive). The Database has had some major cleanup of unused indexes, ...
1
vote
2answers
61 views

How to select sets of data from a table

I have a table containing data that represents a tree structure of data, using the nested set model. I'd like to store data for more than one tree in the table and have only one of the sets of data ...
1
vote
1answer
58 views

Aggregation using CASE

I have a table called country: CountryName | Sales A 200 B 100 C 300 D 500 E 400 Now I in my report I have hardcoded the parameter of ...