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
11 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
0answers
3 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 ...
0
votes
0answers
5 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,...
0
votes
1answer
16 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 ...
1
vote
2answers
112 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
0answers
11 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
22 views

Default Value Of Table

In my example syntax below I want the field delayed to be the difference in days between gotitdate and dunitdate I know that this syntax will get me the result, and I was going to set this up as an ...
0
votes
0answers
22 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 ...
0
votes
1answer
26 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
1answer
36 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
28 views

Attaching / creating database on network drive - Access is denied

I have two computers, computer A is with SQL Server but has no space left, the other is computer B which has free space. I'd like to create a new database on computer B. This is what I've done so far:...
5
votes
2answers
402 views

Brent Ozar First Responder Kit filter

About once a week the query sp_Blitz @IgnorePrioritiesAbove = 50, @CheckUserDatabaseObjects = 0 shows "errors" with DB backups that are very old. It's correct to show this, but is it possible to ...
1
vote
1answer
34 views

sys.dm_exec_query_stats - what causes total_worker_time to be reset? How to work around that?

I am monitoring stored procedure executions, mainly the execution times and the CPU usage, using sys.dm_exec_query_stats in the script below. SET NOCOUNT ON; declare @lastex datetime select @lastex =...
2
votes
1answer
40 views

What is BMK Operator in SQLServer

I was trying to answer this question stating from clause is optional..But i am stuck with an Operator in plan..below is screenshot of execution plan .. As you can see there is BMK operator in query ...
1
vote
1answer
21 views

Pass a parameter from SSRS to MySQL [on hold]

Here is the query for the dataset: SELECT DISTINCT CompanyId, Company FROM User_Hierarchy_Mapping WHERE UserId = @FieldManager FieldManager is the parameter name. It's not ...
0
votes
0answers
45 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 ...
1
vote
1answer
15 views

How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get: Msg 9514, Level 16, State 1, Line 3 Xml data ...
0
votes
1answer
31 views

Should I shrink the Log File

I am a developer but have to do DBA stuff now and then. We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB. ...
0
votes
1answer
28 views

SQL Server Performance hit after cache rebuild

After I rebuild my indexes and update my statistics, SQL Server will have to rebuild the execution plan cache to get up to speed with the current setup. So, will the SQL server do this immediately ...
0
votes
0answers
11 views

Is there a fast way to find out if there have been any changes to an individual table in SQL Server using change tracking

I am using SQL Server's change tracking on an Azure SQL database with a number of tables that update independently. I am keeping 5 days worth of changes which equates to approx 1 million change ...
0
votes
0answers
27 views

Compare Previous and Current Year Sales

I am seeking advice on how to modify my sales query that will compare 2 separate years (current and previous years) and do some calculations for the two years. Below you will find my current sales ...
0
votes
0answers
16 views

Help Interpreting a group by clause

I have the following Group By clause: Group By Grouping Sets (a, (b, Rollup (c, d)) ) My thought process is Grouping sets ( a ) Union All Grouping sets( (b, Rollup (c, d) ) ) Then expand the ...
0
votes
1answer
18 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
1answer
27 views

stored procedure logic,data flow visualization tool [on hold]

I have a very poorly written stored procedure with cursors, if else etc. Is there any toll available to visualise the logic and data flow? Thanks
0
votes
1answer
40 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: ...
1
vote
1answer
17 views

SQL Server 2014 (12.0.4439.1) to SP2 (12.5000) service start failure

In what seems to be eerily familiar to the oh-so-famous SSISDB SP1 fiasco, I've hit a snag while upgrading our DEV server to SQL 2014 SP2. The base version was SP1+CU5 (12.0.4439.1). We have SSISDB ...
0
votes
0answers
15 views

(SQL Server) How to Generate Schedule Based on Datetime Range and Duration

What I am trying to do is generate a schedule based on an input date. There are start and endtimes for each visitation period listed in MyTable as StartTime and EndTime. Data in MyTable would be: ...
0
votes
1answer
26 views

Trigger on replicated DB

If I have database db1 with schema sch1. I want to create another schema sch2 which will be used by different application and the tables in sch2 will be populated from tables in sch1. So my approach ...
0
votes
0answers
15 views

Lock Request timeout causing Orphaned spids

I have an issue where an application with a single connection to the database, got a lock request timeout. In my mind a lock request timeout can only happen if a query waits for a resource for over 5 ...
0
votes
2answers
25 views

when were the sys-dm_os_wait_stats last cleared? sql-2005!

I intend to monitor the execution of my stored procedures in a specific database, using the following script, which I got from How can I monitor the call count per stored procedure per timespan? ...
0
votes
0answers
22 views

how to find out the next time a job is scheduled to run in sql - in real time and even if the job has never been run before

I have a very similar question here but the problem with that question/ answer is that it does not work for all situations. for example, for the jobs that I have just created and have not been run as ...
0
votes
0answers
23 views

How knowing information between linked servers (MySQL & MS SQL)?

I am new to StackExchange Database Administrators and have just a little bit of experience with Databases (mostly MySQL). I have been asked for my job to link a MySQL Database to a MS SQL Database, ...
0
votes
3answers
55 views

Is GUID(uniqueidentifier) not suitable for a clustered index? [on hold]

When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index... So, will it slow down my queries or not ? I am explaining some description of my ...
0
votes
1answer
24 views

On a single table, how is an indexed view different from a non clustered index?

Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an ...
0
votes
0answers
15 views

Data Cleansing Using DQS

My question is regarding the installation of DQS server. I have a production server and a data warehouse server, both are synced. Is it the best practice to cleanse the data in production using the ...
0
votes
2answers
65 views

How to resolve Geospatial Data over linked server causing error?

Update of the issue. The exact error is Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later. The error is happening ...
1
vote
0answers
18 views

How can I get the actual SSAS error when processing a cube from a SQL Agent job?

I have a SASS cube on one server and a SSIS package on another server that triggers processing the cube. The SSIS package is executed via a scheduled SQL Agent job. I have enabled error reporting for ...
1
vote
1answer
18 views

Permissions for Cluster service account in SQL server

Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL ...
0
votes
1answer
19 views

How convert database from SQL Server 2012 TO SQL Server 2005 [duplicate]

I have SQL Server 2012 database which I need to use. But I can't install SQL Server 2012 on my PC. I use SQL Server 2005 only. How can I change the database SQL Server 2012 To SQL Server 2005 OR ...
0
votes
0answers
17 views

login failure for sa to unspecified DB

I have a pair of SQL Server 2008 R2 DB mirrored and I'm seeing a login failure approximately every one minute. [417593] MSSQLSERVER Type: FAILURE AUDIT Computer: EVS-DBSERVER-2 Time: ...
1
vote
1answer
30 views

Replication from Enterprise to Standard edition

Can I define transactional replication while the Publisher and Distributor are both Enterprise Edition and the Subscriber is Standard Edition? What do I need to consider in case it is possible (given ...
0
votes
1answer
30 views

AlwaysON AG synchronous commit mode disconnected transaction log effect and mitigation

I have an AlwaysON AG with one primary and one secondary replica in synchronous commit mode. The databases in the AG all have autogrowth disabled (standard policy within organisation). There is ...
3
votes
1answer
23 views

Can SSDT deploy SQL server service broker objects?

Can SQL Server Data Tools (SSDT) deploy sql server broker objects? (So contacts, message types, services, queues and so on.)
0
votes
1answer
50 views

Database design considerations for unused columns with every table has same schema

I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate ...
0
votes
0answers
10 views

Can a snapshot be “disconnected” from the app logon, to keep ONLY the immediate changes?

Can a snapshot be "disconnected" from the app logon, to keep ONLY the immediate changes before that disconnection, without changes subsequent to the disconnection being written to the snapshot? I want ...
0
votes
1answer
22 views

How to generate list of dates — ie every day for the past 2 years? (sql server)

We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value. I'm wondering what's the simplest way to ...
0
votes
0answers
30 views

Table optimization in SQL Server with huge data [on hold]

I have a table in one of my databases which has 20 billion rows and keeps on increasing daily. Even if I do a select *, it takes a lot of time to fetch all records. There s only one primary key in ...
0
votes
1answer
36 views

OVER and PARTITION BY for db size change

I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the ...
1
vote
1answer
26 views

Visual Studio Database Project

I work in a company that is deeply ingrained in .Net programming. I am responsible for managing a couple SQL Servers, with about a dozen databases in total. Our programming manager recently ...
0
votes
0answers
26 views

T-SQL for creating staging tables with indexes for partition switch operations

I have some partitioned tables in a SQL Server 2012 environment. Is there an easy way to make the create table statement along with its indexes and constraints so I can partition switch to them on ...