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)

-3
votes
2answers
76 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
1
vote
0answers
22 views

McAfee antivirus and File stream SQL Server 2008 R2 Issue

I am facing file stream issues in SQL Server 2008 R2 and I am not able to correlate things and how to fix them. Below are the event logs. Please let me know if they both the errors (One is Mccafee ...
1
vote
1answer
40 views

News Database Design for a Desktop Application

I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication ...
1
vote
1answer
59 views

Convert hierarchy in an ordered list of rows

I tried to make a simple list that represents hierarchy of items. Tags have simple structure like [Id] ,[Name] ,[ParentTagId] I try to make the follwing result table: ID, ParentID, Name, Lev,...
6
votes
1answer
76 views

Some SQL Server geospatial queries take much, much longer than others

We have a simple SQL Server table with geospatial data that looks like this: CREATE TABLE [dbo].[Factors]( [Id] [int] IDENTITY(1,1) NOT NULL, [StateCode] [nvarchar](2) NOT NULL, [...
1
vote
1answer
45 views

Restore database from script created by the “Generate and Publish Scripts” wizard

I have this database I need to move to a test server. The only way I can do this according to my searches is to create a script. The database is on SQL Server 2014, I am using 2012). I can get the ...
3
votes
1answer
29 views

Maintenance Plan on Replication Subscriber

I have seven subscribers to a replicated publication using transactional replication in SQL Server 2012. Do I treat the subscribers just like a normal database and perform maintenance via a ...
2
votes
1answer
41 views

Query 2 tables one with 1 row and the other with multiple rows

I have a part table: ID, Description ABC-123 ABC DEF-456 DEF I have a User Defined Fields table that stores information in a single row. ID, DOCUMENTID, STRING_VAL, DATE_VAL ...
1
vote
1answer
75 views

Are Database duplication and Mirroring one and the same? [closed]

I am going over the concepts of Distributed database management system. I read about duplication and replication. Then there is also something called Mirroring. I googled to find if duplication is the ...
-3
votes
1answer
53 views

Script out spaceused stored procedure to edit it

Is it possible to script out the sp_spaceused stored procedure so I can edit it to include log space information and sort of create my own custom stored procedure? I would like to put the results in ...
1
vote
1answer
29 views

Pre-checking execute privs for SPs

We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all. In the latter case, I understand that this is a blanket GRANT, ...
3
votes
2answers
42 views

Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication “distribution” database

I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article. These transactions are captured by the log reader and stored in the ...
4
votes
2answers
129 views

Will a DBCC CHECKDB with repair have to rescan?

I've run a DBCC CHECKDB and it found allocation errors. When I run it with REPAIR_ALLOW_DATA_LOSS - will it need to take the same amount of time to scan for errors, or will it attempt to use the last ...
4
votes
1answer
62 views

Log backup vs differential backup if latter is broken

Under full recovery mode, will a differential backup "break" the previous log backups? Let me give an example: Let's say we have the following backups: FULL backup 1 Log backup 1 LogBackup ...
1
vote
0answers
48 views

SQL Server 2016 - row level security by AD group membership

I'm wondering if it is possible to use row level security based on a users AD group membership. I would love to have something that could support users in different groups and some super users who ...
3
votes
1answer
55 views

Auto created Statistics vs Manually created Statistics

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement. Will the manually created statistics get ...
1
vote
0answers
13 views

What settings / statements are pushed to subscribers in transactional replication?

Version: SQL Server 2014 (Web edition) Replication: Transactional Are the following pushed to subscribers in transactional replication? SET_QUOTED_IDENTIFIER statement; SET ANSI_NULLS statement; ...
5
votes
3answers
562 views

use of coalesce to check for all variables being null

How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors. DECLARE @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @StateCode ...
15
votes
4answers
668 views

Why can't a differential backup specify its base?

This is my first DBA.SE post, so please inform me of any mistakes, thanks! I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I ...
2
votes
1answer
73 views

Microsoft SQL Server AG Synchronous Mirroring is not synchronous

We were upgrading to SQL Server 2012 and decided we wanted to divorce ourselves from replication we were using for Reporting services on Node 2. AG and mirroring looked like a good solution -- and we ...
4
votes
1answer
159 views

TDE - Encryption on a large database

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server. What kind of problems (if ...
17
votes
1answer
215 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
2
votes
3answers
86 views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
2
votes
0answers
50 views

“Access is denied.” for xp_cmdshell for network share

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied." The command is ...
11
votes
1answer
152 views

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large ...
0
votes
0answers
7 views

Transient Log Shipping Restore Error - Could not redo log record (172980:741440:22), for transaction ID (0:1889746485), on page (1:28106052)

I'm running SQL Server 2008 r2 sp3 and am taking transaction log backups every 5 minutes from my primary server in a log shipping scenario. I have multiple standby servers that are log shipped to. On ...
1
vote
1answer
51 views

SSIS package succesfully completes but does not import 100% of records from flat file into table [closed]

I have a fixed width text file. I have created an SSIS package that imports the file into a table in a SQL 2014 server. When I open the file (with notepad++) i see that it contains 259,735 records. ...
5
votes
0answers
34 views

Excessive Prelogin Packets

Running a client/server application where the client app is powered by MS Access and the database on the server is in SQL Server. We have two locations and have active users at both. We recently moved ...
0
votes
1answer
38 views

SQL Server 2014 - AlwaysOn HA - Always Synchronizing

I have a Windows Server 2012 R2 cluster with SQL Server 2014 configured in a HA configuration. The secondary databases always say Synchronizing when the data movement is happening. Is this normal? ...
0
votes
1answer
40 views

Organizing Stored Procs and Views

I have a database with 112 stored procs, 7 views and 88 tables. The problem is over time I have created tables and stored procs as I needed without taking the time to think about what I might be ...
1
vote
0answers
28 views

connection closes unexpectedly with different kind of errors

When I execute a query which returns more than 300 rows I’m getting random errors and the connection closes for example An error occurred while executing batch. Error message is: SqlDateTime ...
0
votes
1answer
61 views

Linking between SQL Server and Excel [closed]

I want to dump Excel data in SQL Server database. What is the best/easiest way to do this? I have SQL Server 2012 Management Studio. I have tried many techniques but failed miserably, there is no ...
4
votes
1answer
118 views

INSERT INTO inserting multiple times

I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called '...
1
vote
1answer
23 views

The following system error occurred: No mapping between account names and security IDs was done

While trying to Process a cube i go this error "Error 133 The following system error occurred: No mapping between account names and security IDs was done. 0 0" i know its some user which ...
2
votes
1answer
20 views

Distributed database implementation in sql server

Currently, We have central Database at Head Office and users from sub offices ,scattered around the country, connect to central database. Now we want to distribute the database in disconnected mode at ...
5
votes
1answer
43 views

Understanding of SQL Server active/active cluster

I have a vague understanding of SQL Server's active/active cluster. Can anyone help to confirm if my understanding is correct? As I understand, active/active cluster use two or more Windows servers. ...
16
votes
2answers
404 views

Using MAX text or more specific, smaller type

Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
1
vote
2answers
71 views

Large Database backup approx 2 TB and restore with limited disk space

I am working on DB migration from server A to server B. Server A:- DB size - 2 TB - Sql 2008R2 - Multiple Data files stored in diff drives. Server B:- Disk space:- 999 GB - Sql 2014 Enterprise ...
3
votes
1answer
63 views

Why more (and varying number of) logical reads with read ahead (prefetch)?

After creating tpch database in my SQL Server, I tried below query: set statistics io on DBCC DROPCLEANBUFFERS; select top 100 * from dbo.lineitem order by l_partkey; The table ...
4
votes
1answer
114 views

How to re-enable read ahead (prefetch) feature in SQL Server after disabling it?

In Microsoft SQL Server, I've disabled the read ahead (prefetch) feature using DBCC TRACEON(652,-1) But does anyone know how to enable the read ahead feature? Before I disabled the read ahead,...
2
votes
1answer
30 views

Tablediff utility output file encoding

The problem happens when sql server tablediff utility generate a file with differwnces between source and destination and when ssis execute the sceipt file which contains arabic letters are inserted ...
19
votes
3answers
281 views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
0
votes
0answers
23 views

How do I keep all roles, users and permission on a database being dropped then restored with a different database with different permissions [duplicate]

The DBA at my job every week drops my test database and restores it with a copy of the live environment. The permissions on the live database are different then the test database(and need to be). ...
2
votes
0answers
46 views

SQL Agent parsing actually executes instead of just validating

I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident. This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory. I ...
4
votes
5answers
208 views

How to select column names by data type

How would I get the names of all the columns in a table that are of a specific type such as datetime? Even better -- How can I do the same thing but with multiple tables joined together and then list ...
-1
votes
0answers
21 views

Table imports in sql server_ how to move foreign keys along with tables (sql server 2008 r2)

We received request from Application team to import table from one sql server to other sql server database, we have created one database in same server and imported tables and taken backup and ...
3
votes
1answer
37 views

Replication for Mirrored server

I have 3 SQL servers running SQL Server 2012 Standard Edition, called A , B and C. Server B is a mirror of server A. There is transactional replication between server A and Server C. How can I ...
2
votes
0answers
51 views

SSDT Syntax Error 80001 on every Select statement starting with “(”

Visual Studio / SSDT marks the following script line as wrong giving "SQL80001 Wrong Syntax next to SELECT" and also "SQL80001 Wrong syntax next to ')'" error. (select a from [tab1]) union (Select a ...
3
votes
1answer
80 views

Creating SQL Server triggers to copy a record from one table and populate another

I want to create a trigger that will copy a record from one table and populate another after an insert or update. Users will enter a date in an inspection table and I want that date inserted or ...