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)

6
votes
5answers
177 views

Does anybody know what this tool is called?

Productive work has crawled to a stop while we try and figure out what the red thing on the cover of Microsoft SQL Server 2012 Internals (2013, 0735658560) is. We think it's a surgical or electrical ...
1
vote
2answers
1k views

Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size. Now I am stuck using Diskpart to set ...
0
votes
0answers
6 views

What are options for super volatile tables?

I've inherited a very volatile table which is a map of who holds what resource in the system. At any given moment, there could be a dozen inserts/deletes/reads going against that table. However, ...
1
vote
1answer
32 views

How to use Case Statement with BCP

I am using BCP as follows, bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n This works 100%. I have omitted the rest of it because it is just server, user, password ...
0
votes
3answers
20 views

Table concatenation

I want to have a dynamic query that I can concatenate my table based on current month. I want to have a variable to my FROM and JOIN statements. DECLARE @month_number AS VARCHAR(2); DECLARE @...
1
vote
1answer
16 views

SQL Server Rollback nested Stored procedures and linked servers

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to ...
1
vote
1answer
29 views

SQL SERVER - “ApplicationIntent=ReadOnly”Listener redirect to fully qualified domain name

Hi all my DBA has setup a primary myNodeA and secondary node myNodeB and I'm trying to access the secondary node using "ApplicationIntent=ReadOnly" in JDBC connection string. The problem we are ...
-1
votes
1answer
29 views

Trying to find a percent of a categorical Column

I am trying to figure out the percentage of a categorical column. There are 5 Categories - Home, Home Health, SNF, Other, Unknown I want to find the percent of Home & Home Health divided by ...
1
vote
2answers
138 views

SQL Server restore database file access error

I am trying to restore a database from a file on a remote machine. The script I am using is as follows: RESTORE FILELISTONLY FROM disk = '\\server\path\to\file.dmp' RESTORE DATABASE MyDB FROM ...
0
votes
2answers
2k views

Restoring database backup file gives access error

I am running SQL server 2008 and trying to restore from a backup file. Note: the backup file and folder and read/write access to "everyone". Running the command as "master" Running RESTORE ...
0
votes
2answers
19 views

SSRS Query Timeout vs DatabaseQuery Timeout

In SSRS there are appear to be two separate query timeout options. The Query Timeout option can be found in the Dataset Properties dialog window on the Query tab. The DatabaseQuery Timeout option is ...
5
votes
2answers
4k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
2
votes
1answer
2k views

Inline edit SQL Server database rows from Visual Studio

I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 ...
1
vote
1answer
123 views

Distribution clean up job in transactional replication. Records removed but files didnt

Distribution clean up job runs without errors according to schedule, but I noticed that snapshot files do not remove even if they are created beyond max_disretention period. Records from ...
0
votes
1answer
16 views

Help needed in creating a SQL statement containing four joins

Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint ...
0
votes
1answer
29 views

AlwaysOn Failover - SQL Server 2014

I have two SQL Servers 2014 in AlwaysOn AG setup (OS - WSFC2012R2) with secondary acting as a synchronous replica. I'm facing an issue in the role switchover on daily basis. Every day morning around ...
1
vote
2answers
54 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 ...
6
votes
3answers
513 views

Does the order of columns in a columnstore index matter?

I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table. Will there be any change in performance based on the order of columns that I use ...
0
votes
1answer
23 views

MSSQL 2014 - Give user permission to run sp_refreshview

I need to give a user the required permission to run sp_refreshview for a specific database on our server. I've tried adding the user to the db_executor role but that only gave them access to certain ...
0
votes
0answers
21 views

Concurrency Issues while running large SQL Server Job Update

I have a SQL Server Job running nightly to clean up encoded XML data. During the window of the SQL Server job, the application that writes to the table being cleaned up is being halted for the length ...
1
vote
1answer
42 views

How expensive is a SELECT that returns an empty result set?

I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM ...
1
vote
0answers
51 views

Performance issue with larger resultsets SQL Server

I currently have a stored procedure in SQL Server where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records ...
3
votes
0answers
27 views

Consistency errors in database table after upgrade to SQL Server 2012

I keep getting a weird problem with some databases after upgrade to SQL Server 2012 (either from SQL Server 2005 or from SQL Server 2008 R2). The backup (.bak) is fine on older version. I do DBCC ...
6
votes
3answers
3k views

SQL Server job hanging and can't be stopped

I am using SQL Server 2008 R2 (10.5.2500.0) and we have a job that runs every weekend. It runs the ola.hallengren.com database maintenance tool to backup several databases, then FTPs the backups off ...
1
vote
2answers
37 views

What's the most efficient way to load a csv file into SQL Server 2016?

Alright, so this question seems simple up-front. Well, there's a bit more to it than just loading the file. The entire story goes like this: For some reason, our client sends us what can only be ...
0
votes
0answers
34 views

Generate a column from different rows in sql

I have a table that looks like this: SKU DateOut DateIn Status 123 1/2/2015 5/2/2016 1 123 10/2/2016 18/2/2016 1 123 ...
0
votes
0answers
19 views

what could be a high level good standard of error handling inside JOBS in SQL Server 2014?

I am after a good error handling for scheduled jobs in sql server 2014. for example - is it ok to use RAISERROR ? @@TRANCOUNT? So far I have been using the script below (it is an example as how to ...
0
votes
0answers
53 views

SQL Server Deadlock

I am running SQL Server 2012 Service Pack 1 + Non Security Update (KB2793634). I am frequently getting deadlocks occur, 99% of the time with the same 2 queries. How can i prevent this deadlock from ...
0
votes
1answer
11 views

Insert row wise different images in SSRS [on hold]

I am working on SSRS in witch i have data set with [First, Last, Email and Image] image contains name of image and when ever i use this image i have to put www.something.com/images/ at start of this ...
6
votes
3answers
1k views

Data warehouse server. How do you calculate RAM/CPU specifications?

I am trying to write a spec for a data warehouse server for our planned data warehouse upgrade. As we run virtual servers on VMWare hosts we have the ability to add or remove resources as necessary. ...
1
vote
2answers
37 views

Finding the row with the latest date for each row type

I've got an SQL database (SQLAzure V12 version running on Azure) that looks like the following: DECLARE @VehicleData TABLE ( id int PRIMARY KEY, Registration nvarchar(50) NOT NULL, [...
0
votes
0answers
8 views

How to maintain Schema changes with two Publisher into one Subscriber?

AlwaysOn Availability Groups configured with Replication Subscribers with two publisher on sql server 2014.its running successfully.... Problem - When I changes on schema at publisher 1 then I take ...
0
votes
1answer
22 views

Best way to get the latest Identity when inserting through View

I am using View and View trigger to insert data into multiple tables. In this case, how can I get the inserted ID of the specific table? I was using IDENT_CURRENT but it's not recommended. How can I ...
2
votes
2answers
51 views

SELECT * Returns 1 Record, But SELECT COUNT(*) Shows 3k+ Records

I imported a csv file that had more than 3k records into a database table in SQL Server using the Import Wizard. I do not receive any errors, and the import wizard shows success, with 3217 records ...
4
votes
1answer
3k views

SQL Server 2014 Replication - The process could not connect to Subscriber 'xxx'

I have just set up very basic transactional replication on SQL Server 2014. The Publisher and Distributor are one and the same. I have configured the Publisher to push to the Subscriber, so any and ...
2
votes
2answers
708 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error message:...
3
votes
2answers
154 views
+50

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...
0
votes
0answers
8 views

What is an effective method for populating a Jet and SQL database with a quantity of identical data for testing purposes

I'm testing an application that works with either a Jet or SQL Server back end. Part of that testing includes performance testing, but I don't want to manually populate both databases with identical ...
0
votes
0answers
7 views

How can I transfer a table with unknown columns from SQL to Jet

When supplied with a table name in a variable (e.g. "SHIPPING"), how can I use SSIS to pull data from that table in SQL Server and push it to a Jet database into a table with the same name and data ...
1
vote
1answer
28 views

Join Tables of Varying Criteria in Order of Priority

I am trying to join two tables based on varying criteria in order of priority. The situation I have inherited a clinical database which has inherently bad data quality from years of poor input and ...
0
votes
1answer
22 views

differences between SPID and session_id

I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and ...
0
votes
1answer
21 views

SSRS 2012 Report with Oracle database as datasource never finishes

I am investigating a SSRS 2012 Report which used to return data from an Oracle database. However, recently it stopped returning data. The report will sit there and run forever until something times ...
0
votes
0answers
7 views

regarding creating two colums using the CONCAT and CONCAT_WS function

Complete newbie here using SQL. Sorry if i don't make much sense here. Basically i'm trying to combine fields on a database that would keep customer's details. For example: fields: customer_title,...
1
vote
2answers
325 views

Cannot open backup device Error

I have a SQL Server Agent job that backs up a database on a remote server to a network drive. Linked Server, I believe is set up correctly but I get the following error: Msg 3013, Sev 16, State 1, ...
3
votes
2answers
430 views

SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN

I'm trying to join a few rows from a remote view to a local table. The view has about 300 million rows, so I want to use the REMOTE query hint so that all 3 million rows don't have to be transferred ...
0
votes
1answer
188 views

what is running VSS on my server

Client's SQL Server farm DBs are backed up by third-party tool that does not use VSS. About half of the instances show backups being made by some tool that is using VSS. I can disable SQL Server VSS ...
0
votes
0answers
13 views

NT SERVICE\MSSQL$SQLEXPRESS user cannot be found when added to network drive

For SQLEXPRESS 2016 I would like to backup/restore directly to/from network storage I have mapped to drive S:. SQLEXPRESS runs under the (virtual?) account NT SERVICE\MSSQL$SQLEXPRESS as it should, ...
0
votes
1answer
40 views

how to take output from second table by joining in SQL [on hold]

table 1 table 2 id Name value id status 1 a 1000 1 open 2 b 2000 2 closed 3 c 3000 ...
0
votes
1answer
43 views

How to split a single query into two?

I'm trying to figure out how to split a single query into two separate queries. The following two queries is what I want: Query #1: Label Value TOTALSTUFF 100 Query #2: ...
0
votes
0answers
52 views

SQL Query to calculate total time worked and total distance travelled

I am trying to write an SQL Query which calculates the Total Time worked and Total Distance Traveled by a Driver for each date. The StartOfDay is the MIN(LogDateTime) WHERE EventCode = 1 The EndOfDay ...