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
0answers
35 views

Is there ANY risk in granting a superuser CREATE and ALTER procedure permissions in a specific schema?

I have a developer who has created a set of procedures/functions/views he uses to perform configuration analysis in a large application (provides much higher productivity than using the formal ...
0
votes
1answer
12 views

SQL Server 2016 Install ERROR (Pictures and logs provided)

Can't install, updated Windows Server 2012 R2 fully with all Optional and Important Updates. I enabled a proxy to gain outside access which works (since Windows updates work). There is no anti-virus ...
12
votes
3answers
139 views

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
0
votes
1answer
22 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
1
vote
2answers
88 views

sys.dm_sql_referencing_entities not returning results

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM ...
0
votes
0answers
16 views

Backup and restore specific filegroups [duplicate]

I've seen this Stack Overflow question: Backup and restore SQL Server database filegroup. However, it didn't do what I needed. I have a database with three filegroups: primary, reporting, and ...
-1
votes
0answers
34 views

normalize table data

Maybe my question is duplicate or asked many times, but it's rare, and I really need this. I have a table, which contents looks like: Id points 3 20 3 42 4 40 4 60 6 20 6 30 5 10 ...
2
votes
1answer
117 views

What is the best way to import this non-standard text file into SQL Server?

I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest ...
0
votes
1answer
66 views

Identity column jump in sql server restart

I had many research on identity column jump in sql server restart. I know the reason why this happen as it explained here. I know the solution how to prevent this using either manually perform ...
3
votes
3answers
124 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
0
votes
0answers
25 views

Upgrade SQL Server 2008 R2 [closed]

I have SQL Server 2008 R2 with SSMS version 10.50.1600.1. Can I upgrade this version to SQL Sql server 2008 R2 with SSMS version 10.50.4000.0? While restoring the database there comes an error. So ...
0
votes
0answers
10 views

End user configurable mailers based on queries [closed]

We are required to send out a variety of mailers containing database rows (basically work items assigned to users) Ideally I would want to buy a solution that gives as much control as possible to ...
0
votes
0answers
31 views

Sql Server Stored Procedure calling table-valued function - Performance

I'm using Sql Server 2012 and I have a stored procedure that does about 15 left joins and one of those joins is a CROSS APPLY to a table-valued function. The first 14 left joins are not gonna restrict ...
4
votes
2answers
92 views

The index columns in sys.index_columns different from what are in the index

When I run the following I get 3 indexed columns: SELECT b.name, c.name FROM sys.index_columns a INNER JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id INNER JOIN ...
0
votes
1answer
48 views

Why can't I update or delete certain records in SQL Server?

I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table. I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout ...
0
votes
1answer
49 views

Duplicates when checking for two different column values based on a key that is not unique in table

I want to check to see if two orders were placed for one person within an hour of one another. Example data PERSONID | proc_code | TIME 123 | 1 | 4/25/2016 00:01:00 123 | 2 | 4/25/2016 00:01:00 123 ...
0
votes
0answers
12 views

SQL Server - DENY new server role to view any logins, server roles, or system databases

Wondering if the following is possible, to satisfy a requirement for login to be able to connect to an instance via SSMS but only be able to select from a view in a particular user database, without ...
0
votes
1answer
32 views

Sql service account password change for environment on Availability groups 2012

My current environment is on Availability groups sql 2012 with 1 Synchronous and 1 Asynchronous replica. Soon we need to change the sql service account passwords. Can any one suggest to me do I need ...
7
votes
2answers
266 views

SQL Server not using all memory

I have SQL Server 2014 with max memory set to 6GB (physical memory is 8GB). The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB). I ...
0
votes
0answers
24 views

Missing database on a Linked Server

I have a MS SQL 2014 (web ed) that's on the Azure cloud (Server A). I have linked this server to an onsite SQL 2014 server (server B). On server A, I can see MOST of server B's databases but not all. ...
0
votes
0answers
28 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...
1
vote
2answers
39 views

I'm confused about High Availability/Always On

is High Availability something to describe Log Shipping and Always On ( replica )? is Always on something to describe Log Shipping and High Availability? I'm studying to get my certification 40-762 ...
0
votes
0answers
15 views

Connect MSSQL DB with SSAS

I am trying to build a BI system using SQL Server 2008. I have installed and created the database. I have also installed SSAS but I can't seam to find a way to connect my database as a Data Source ...
1
vote
0answers
42 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
3
votes
4answers
105 views

Is it enough to have only full backup and transaction log backup only for a good backup plan?

If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes. My problem is what is ...
5
votes
3answers
144 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
1
vote
1answer
16 views

Transactional replication using articles filters and delete operations

The problem with transactional replication using articles filters and delete operations. I have a problem with my transactional replication when I use articles filters and delete operation. When I ...
0
votes
2answers
54 views

How can I return top 10 rows from each state [duplicate]

How can I return the top 10 rows for each category? Say I have a table named xyz. It has a column called state, in the political unit sense of the word. I want the top 10 rows from each state. Do I ...
1
vote
0answers
21 views

Solr with SQL Server or NoSQL

I am working on a POC of using Solr with SQL Server. I have a very complex data model with SQL server which required a bunch of joining and scalar functions to strip some mark up and other stuff. ...
0
votes
0answers
19 views

my log files are 65gig with 1% free space [duplicate]

my log files are 65gig with 1% free space so shrinking the file can't shrink any smaller. what do I do? should I delete it to have it create a new one?
1
vote
1answer
89 views

Most efficient way of finding rows in a table that don't exist in another — SQL Server

What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the ...
1
vote
0answers
27 views

Problem inserting into table from trace file consumes all space in tempdb

I'm working with SQL Server 2008 SP3 Enterprise Edition (64-bit) I'm trying to import a trace file of about 3.5 MB (4667 rows) into a table as following: INSERT INTO DBTRACE with (rowlock) SELECT * ...
3
votes
2answers
98 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
11
votes
1answer
301 views

CASE expression returns wrong value when using CEILING

I've run into an issue where a CASE expression does not return what I expect. As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
1
vote
1answer
44 views

Limit the number of records depends on the part of primary key

I created database for football matches. I have 2 tables with a many-to-many relationship, so I also created a junction table: Referee CREATE TABLE referee ( id_referee INT IDENTITY PRIMARY KEY, ...
0
votes
1answer
27 views

Powershell invoke sqlcmd

How is it possible to execute a query as a applicationintent=readonly using powershell? I have an cluster alwayson environment. I want to call a procedure with invoke sqlcmd to the readonly database ...
3
votes
1answer
61 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
0
votes
1answer
27 views

Sql server covering index and join

I understand that covering index will speed up read operation if all the columns in select list are covered in the index. But what if I select and filter on columns in other tables as well? Would ...
1
vote
2answers
17 views

Setting up a listener for sql server alwayson availability groups without computer object rights in the AD

Is there a way to create a sql server alwayson listener in an active directory (AD) environment where I don’t have create computer object rights? The AD admin created a computername for me. Is there a ...
0
votes
1answer
39 views

Copying data from two tables into one using INSERT in SQL 2012

I've been trying to copy data from two tables into one using the INSERT statement. The tables are from the same database and have the same numeric types and I only need to copy one column from each ...
0
votes
1answer
41 views

How is powershell commonly used in SQL server? [closed]

I am wondering how is powershell used in SQL Server? I heard people saying that powershell makes a DBA's life easier. But I dont know how is powershell commonly used in SQL server. Can anyone provide ...
3
votes
1answer
29 views

Case Sensitive Ordering within the CMS Server List

I've got to believe there's a reason for this, but for the life of me I cannot figure it out. It looks like ordering of Server Names within any Central Management Server is Case SENSITIVE, regardless ...
0
votes
1answer
42 views

Increasing the number of tempdb files question about initial size

I am planning on increasing the number of tempdbs we have on our sql cluster. I do have one question that I can't seem to find a clear cut answer to... I have one tempdb that is over 50GB and I want ...
8
votes
2answers
122 views

Is the hierarchyid CLR open-source?

The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality. Unfortunately, there is limit to how deep the represented paths can be, and ...
1
vote
1answer
59 views

how to uniformly select rows in sql

I am new to here and please let me know if I didn't make the question clearly. If I have a m rows in a table and I want to select n rows uniformly. In other words, the interval between first row and ...
6
votes
3answers
217 views

Execution Plan is NOT using INDEX and uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
1
vote
0answers
33 views

2 SQL Clusters & 2 instances

Can anyone tell me why i wouldn't want to the architect Option 1 for SQL Server clustering? Option 1 is having 2 different clusters, and 2 different named instances on 2 VMs. One's Active is the ...
0
votes
2answers
40 views

Grant select on tables with a specific naming pattern

How to grant select on all tables starting with VVC_? grant select on VVC_* to user1
17
votes
7answers
406 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
3
votes
1answer
73 views

TempDB uses a lot of space in buffer pool

I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used: SELECT COUNT(*) AS ...