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
1answer
39 views

Splitting a table with large columns for performance?

I am working on a system that stores images in its database (SQL Server 2014) using the varbinary datatype. The table looks like this (extra columns removed for brevity): | ImageDataId | DateUploaded ...
11
votes
1answer
177 views

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the ...
0
votes
1answer
15 views

Catching two error messages/Throw into table

My goal is to catch errors from backup jobs into monitoring table. The problem is, that there are cases that backup statement returns more than one error, so ERROR_MESSAGE() is not enough: Msg ...
1
vote
1answer
95 views

How to find High CPU queries

CPU utilization is very high and the SQL Server process is consuming a lot of CPU. We have run few queries and notice that there are a few SPIDs taking a long time to complete and these queries may be ...
0
votes
2answers
30 views

SQL Server Standard License over written because of Report Builder Evaluation version

A colleague of mine rang me today to told me that on one of our servers the SQL Server has stopped working. If he tries to run the management studio it throws and error saying The evaluation period ...
1
vote
0answers
36 views

SQL Server Transactional Replication and Security Setup

I am new to SQL Server replication. I was able to setup transactional replication successfully, but I feel like I am missing some important concepts regarding security and accounts. I have a few basic ...
0
votes
0answers
46 views

Indexes and performance

I am wondering about the best way to design indexes. For Example i have this table CREATE TABLE [dbo].[CoursePrerequisiteAssignment]( [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, ...
7
votes
2answers
257 views

Same code in multiple stored procedures

I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of ...
0
votes
1answer
36 views

Date Conversion error

This is just an example: SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM'; I always get an error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date ...
0
votes
0answers
39 views

DBCC CheckDB - summary poses confusion

I have a .BAT script that runs the DBCC CheckDB and repairs the database if needed. And returns a message to the user based on the results using "errorlevel 1" The problem I'm seeing is that when ...
1
vote
1answer
44 views

MySQL syntax for order by not accepted in SQL Server

I'm working through the SQL snippets for nested sets in this article by Mike Hillyer. The article is using MySQL, but I'm using SQL Server. I get the following error reported when attempting to ...
2
votes
1answer
206 views

Plan cache behaviour

Can someone please explain me why the following is no getting cached use AdventureWorks2014 go DECLARE @id INT=43865 SELECT sp.[BusinessEntityID] , so.salesorderid FROM sales.salesorderheader so JOIN ...
0
votes
1answer
25 views

Running Excel Macro Code by SSIS package configured with SQL Server Job agent

I am trying to execute an Excel macro. It executes successfully on Visual Studio Data Tools 2012. But When I configured this package with SQL Server Job agent and try to execute this, it throws an ...
6
votes
2answers
208 views

Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation? For example: Create Table #Test ( Value VarChar(10) ) Insert Into ...
3
votes
0answers
52 views

Personal Finance database review

G'Day, I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
3
votes
1answer
112 views

.Net Application Using Inefficient Query Plan - caused by ARITHABORT setting?

One of our applications is causing a problem in that there is a stored procedure that returns fine in SSMS, in under 1 sec, but in the application its taking anything up to 10 minutes, depending on ...
4
votes
2answers
136 views

Why a SQL Server Table takes 1GB more after adding 55MB of data?

I've found that one customer database size exploded between yesterday and today. After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data ...
4
votes
1answer
46 views

Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Introduction In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face. Our data model consists of two entities, which shall be ...
3
votes
2answers
58 views

Copy one database to another (existing) database on the same server

I have a "Live" database and a "Development" database and occasionally, I want to replace the entire contents of the development database with the live data. Ideally, without taking the live database ...
0
votes
0answers
19 views

Why do I need local admin rights to access the database via ODBC system DSN?

We have a new SQL Server 2012 instance, and a database called say, testdbase. Logged on as admin, on my Win7x32 PC, I setup a system DSN to the database. The configuration test runs and completes ...
0
votes
1answer
26 views

Cannot read from secondary availability group

I have an issue with Always On Availability Groups In SQL Server 2012. I installed a replica secondary SQLNODE3. It was fine and everything retrievable form tables but suddenly I found I could not ...
1
vote
0answers
35 views

SQL Agent job step fails after upgrading to Powershell 5

I've not seen this issue discussed on the web, and have searched far and wide. I have a brand new HP DL380 running SQL Server 2008R2 standard. It's not yet in production, and I'm testing SQL Agent ...
2
votes
2answers
92 views

SQL Server memory usage alert script [closed]

We have a production server with 500GB RAM. max server memory is set to 450GB. Whenever we checked the memory usage it's showing 450GB, but maybe SQL Server reached 450GB for some time? The memory is ...
4
votes
3answers
207 views

Maintaining integrity between 3 tables when reference could be through or direct

Say we have the following 3 tables: tUsers (ID, name, countryID, regionID) ------ 1 | Joe | 1 | 1 2 | Sue | NULL | 2 3 | Bob | 2 | NULL 4 | Amy | NULL | NULL tCountries (ID, ...
0
votes
0answers
40 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
0
votes
0answers
15 views

Application Latency [closed]

I am not sure this is the correct group or not and I don't know which group to post this question.So apologies. We have an SQL Server client application, which take an average of 10 sec to login and ...
0
votes
0answers
29 views

How do I code Apriori Algorithm in SQL?

Can I get an implementation of apriori Algorithm in SQL ?
0
votes
1answer
44 views

How to do data migration between different schemas?

My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data ...
1
vote
1answer
24 views

Consume Rest Api with SSIS

I try to consume a Rest API (JSON export) with SSIS. Since it seems that a REST source is not supported by SSIS I was looking for a solution but could only find the SSIS JSON Source. Is there a non ...
2
votes
1answer
41 views

howto get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
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
21 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
51 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 [closed]

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
44 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
20 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
44 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 ...
11
votes
2answers
137 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
24 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
64 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 ...