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
37 views

why to use isnull(field,'') =2?

I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it? for exaample : field = 'E' It says conversion failed converting varchar value ...
0
votes
0answers
20 views

How to return a user defined error code through SQL based on the error

I would like to return a user defined error code in my SQL Server stored procedures based on the outcome. For example, if no error takes place, RETURN 0. If a validation error takes place, return ...
3
votes
2answers
140 views

Copy large table to archive table SQL Server 2012

I have a table with logs of my application that have 161.626.718 rows (and growing) and it's very slow to make a insert on it. My idea is to keep only 6 months of log in the table and all older ...
1
vote
2answers
43 views

Use SQL Script Parameter to Map Drive

I am trying to map a drive to a UNC path where the UNC Path is a variable, but I keep getting an error saying: Incorrect syntax near '+' I execute this script: DECLARE @UNCSourcePath ...
0
votes
1answer
47 views

Corrupt Transaction Log on database

I have reason to believe the transaction log file on my database is corrupt. This database is involved in transaction based replication (both subscription and publishing), and I'm unable to start ...
2
votes
3answers
47 views

How to send SQL Server database backups to OneDrive for Business?

I have five databases in SQL Server Express, and now I need to find out what is the easiest way to make regular backups and send all these backups to OneDrive for Business?
2
votes
1answer
51 views

Calculate path to node in a tree

The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the ...
0
votes
1answer
35 views

Modify Backup Directory Path Ola Maintenance Script

I have configured backup using SQL Server Maintenance Solution by Ola (https://ola.hallengren.com). In the backup job, I specified BackupDirectory as a shared folder (eg: \comp1\path). It worked ...
0
votes
1answer
28 views

How to know current logins and what are they do in SQL server? [closed]

How to know current logins and what are they do in SQL server?
0
votes
0answers
16 views

Software to manage ERD [on hold]

We have a small database (Around 50 tables) under development, and I am being asked to produce an ERD for the development team when ever a change is made. Using Management Studio is tricky, because we ...
0
votes
0answers
43 views

How to avoid an Index Spool [closed]

I have a scenario wherein my SQL Server 2012 production server is generating an execution plan using Index Spool to read a billion rows and to filter 40. This is a complex query being generated ...
1
vote
1answer
18 views

FreeTDS TSQL connect string

I am unable to connect to a MSSQL 2012 Server using FreeTDS on OSX in the terminal when I pass the -P . Example: V-MBP:etc vashavoc$ tsql -S SERVER_IP -p PORT -U USER -P PASSWORD locale is ...
3
votes
0answers
43 views

how do i create an automated sql restore script for multiple backups?

Hello to all SQL gurus out there. I basically have 6 daily sql backups (.bak) that need a restore everyday. I want to automate this process by having the sql agent run the script daily. I have the ...
0
votes
1answer
23 views

SQL Server 2012 Patching Service Pack and CU Question

I am tasked with updating a SQL Server 2012 from a version(11.0.5623.0) before SP3 all the way to SP3 CU2. Can I install both patches without a reboot until afterwards? All the documents I have found ...
5
votes
2answers
165 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
10
votes
2answers
130 views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
0
votes
1answer
23 views

HADR procedures

We are thinking of a SQL Server 2014 + Windows Server 2012 configuration for a HADR scenario. My questions are: on a HA scenario, how does the primary server get updated once it is again available ...
1
vote
0answers
14 views

SSRS: UserName value in Subscriptions table, ExtensionSettings column

SSRS subscription to a Windows File Share. Is there a way to query for all the "credentials used to access the file share" in plain text? It appears that the UserName value in the ExtensionSettings ...
1
vote
2answers
63 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | ...
0
votes
0answers
30 views

Distributed transaction debug

I have the classic error: Msg 7391, Level 16, State 2 When I try to execute a transaction over a linked server. I have the same identical configuration in 2 environment, production and test. It ...
0
votes
1answer
34 views

Row of database is unreacheble

Here is the problem: I have a table called "flags" with 54 columns: PVKEY PEOPLEKEY ADDRESSKEY FLAGTYPE FLG1 FLG2 FLG3 FLG4 FLG5 FLG6 FLG7 FLG8 FLG9 FLG10 FLG11 ...
2
votes
1answer
51 views

Select a table's current identity from another database

How can I get the current identity of a table using OPENQUERY in SQL Server? How to select the current identity on the table from another database?
1
vote
0answers
19 views

Views dependent on other views [duplicate]

Is it bad practice to have views dependent on other views? If one dependency doesn't affect performance, at what level of dependency will it start showing negative effects? In the picture is an ...
1
vote
1answer
12 views

How to find the 'Instance ID' for PowerShell Invoke-TroubleshootingPack?

I was doing a PowerShell training, that said use the PowerShell help to run a "Networking troubleshooting pack" I found, ran and read Get-Help Get-TroubleshootingPack -full Get-Help ...
3
votes
1answer
153 views

How to lower HEAP Fragmentation in SQL Server?

i recently found out that one heap table had more than 70% fragmentation. So i decided to do a ALTER TABLE dbo.myTable REBUILD Funny enough, afterwards i had 20% fragmentation. There was no write ...
1
vote
0answers
38 views

Speed Up Garbage collection in FileStream process

In my current process, we have two production site SQL Servers 2014 (Sync), and one DR site SQL Server 2014 (ASync) in one WSFC. I will be hosting 5-6 databases for AOAG set up, out of which one of ...
-1
votes
0answers
32 views

MongoDB Pricing [closed]

I can't find a good article with MongoDB pricings and I am trying to estimate how much it would cost for my company if we would use it in production environment. Could you help me to estimate/provide ...
-1
votes
0answers
22 views

How to see if i excluded databases from backup with TDP for MSSQL?

I have this in my dsm.opt exclude "\...\*test*\...\*" so I want to see if it's really excluded before the schedule kicks off. I know there is a command for regular tsm client to see this but I'm ...
0
votes
0answers
26 views

Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error

We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy ...
1
vote
1answer
36 views

SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without ...
0
votes
1answer
46 views

solve high stolen memory

I've a SQL Server 2012 SP2 with max memory set to 13GB but there is 8GB of stolen memory (SQLServer:Memory Manager - Stolen Server Memory (KB)). I know stolen memory is memory taken from the ...
0
votes
0answers
14 views

Better way to design self-referencing tables with undefined number of recursions to use with linq

I am about to create a self-referencing table (two columns: ID and ParentID) to use with C# and linq, and this question makes me think. Is there a better way to design self-referencing tables with ...
0
votes
0answers
27 views

Ola's IndexOptimize fails with log_shipping_primary_databases select permission error

I am using Ola Hallengren's IndexOptimize maintenance solution for online defragmenting a database on a SQL 2014 Enterprise edition server. Many tables get updated through file loading which results ...
2
votes
1answer
40 views

Is there a way to determine locks that a given query might apply on SQL objects?

Consider a simple query, as simple as a select statement: select * from Teachers where Name like N'%John%' As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find ...
5
votes
1answer
63 views

Index creation leads to large transaction log growth

I have an approximately 1 billion row table. I then create an index as follows: CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable]( [CustomerID] ASC ) -- CustomerID is uniqueidentifier WITH ...
0
votes
0answers
10 views

Issue in Datetime nodes in Importing data from XML files into SQL Server tables [closed]

I use this code as a part of my TSQL code to convert some XML file into SQL tables BEGIN declare @filepath NVARCHAR (1000) set @filepath = 'C:\Media\All\'+ CAST(@fname as varchar(300)) ...
2
votes
0answers
38 views

Loads of parallel inserts (OLTP) into heap table - Operation stops every now and then for 2-3 seconds

We got an analytics database and as new events are coming in we are adding them into our tempdb. From there we have backend processors that pick up the events and merge them into the right tables, ...
0
votes
0answers
24 views

sql server cpu usage

First off all thanks for your time. I am trying to understand the CPU utilization of our server box. This is a sql 2008 r2 running Hyper-V my question is why only CPU's 0-6 are hitting hard. How can ...
0
votes
0answers
60 views

SQL Server Performance: SELECT…INTO vs INSERT…INTO

I read couple of articles on comparing performance of SELECT...INTO vs INSERT...INTO. Some people even exaggerate that the first one is 10X or 100X faster! :) I tested it by inserting 25 million ...
5
votes
1answer
77 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
4
votes
4answers
141 views

Weird WHERE Clause Behavior. Why does this return a row?

In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example? WITH SRC AS ...
4
votes
2answers
646 views

Is it possible to rebuild a dropped index?

I'm studying for the 70-462 exam and one of the training resources I'm using gives a scenario where an ETL process is taking a long time to complete an insert into a heap table due to non clustered ...
1
vote
1answer
19 views

Change database login default schema [duplicate]

I have a problem. I have a Windows user [domain\user] on my instance that has access to some database. I want to change the default schema for that login. I can do this when I create some test login ...
0
votes
1answer
20 views

SQL SERVER Verifying mirror status from 2 weeks ago

How we can verify if the mirror was working fine two weeks ago ?? I was checking the sqlerror log, I'm not sure whether the mirroring sync status will be logged, if so, I don't have that log. because ...
0
votes
0answers
32 views

Dynamically create a rule inside a procedure [closed]

I'm new with SQL Server. I just wanted to know how can I create a Dynamic Rule inside a procedure. This is my approach: CREATE PROCEDURE DynamicRule @startMonth smallint = NULL, @finalMonth ...
5
votes
1answer
98 views

How do I move or redistribute FILESTREAM files between containers?

We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts). According to this blog about FILESTREAM best ...
0
votes
0answers
9 views

My company changed windows domains and now I cannot access SQL Server Management Studio

I'm able to connect to the object but cannot access the databases (except for master) and cannot change permissions. This is just a local instance and I have backups of the data so I'm not concerned ...
1
vote
2answers
48 views

Trigger performance - UPDATE statement joining on INSERTED

I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify): CREATE TRIGGER ...
0
votes
0answers
40 views

how to find overlapping or duplicated indexes? when is it O.K. to keep them?

this is a script that does the following: create a database called "radha" for testing purposes, create the table ProductItemDetailsDenorm create the clustered primary key create ...
2
votes
0answers
97 views

Advice on how to improve this stored procedure

Edited: Added table column datatype I have 2 tables ReportItem id (int, primary key clustered index) report_item (nvarchar(100)) flow_name (nvarchar(50)) source (nvarchar(50)) Tech_Details id ...