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)

16
votes
7answers
386 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
1
vote
1answer
30 views

Capitalize only the first letter of a name

I have a table Vendors that contains a column VendorContactFname, which is written in lowercase. I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first ...
8
votes
1answer
192 views

Can I Improve Performance on Bloated System Tables?

Background: I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's. For example, one db has more than 10k VIEW's and 2+ million SYNONYM's. General ...
0
votes
1answer
16 views

TSQL Solution to backup ALL databases on ALL instances of a server?

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
2
votes
2answers
21 views

Can we commit the transaction when the sql server trigger fails?

I am trying to avoid data loss when the insert trigger on a sql table fails. I am testing it out using the following sql. I am trying this with the following scenario and my code is failing. When an ...
1
vote
0answers
15 views

What happens when two separate processes perform a full backup against the same database?

I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly". AppFull does a full ...
3
votes
1answer
166 views

SQLCMD stuck on “set textsize 4096”

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012). The script is around 500mb and I added a GO every 100 lines. When I start the script with: ...
1
vote
1answer
62 views

AlwaysEncrypted Conversion Fails

I'm trying to encrypt a column with AlwaysEncrypted and I get the following error. Could not deploy package. Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it ...
1
vote
0answers
23 views

Slow Delete's of LOB data in SQL Server

I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE ...
0
votes
3answers
40 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
17
votes
5answers
581 views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup: USE [tempdb] SET NOCOUNT ON ...
5
votes
3answers
153 views

Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this: id Avenger Type Power 1 Captain America foo 2 2 Captain America bar 3 3 Me foo 5 4 You ...
0
votes
0answers
4 views

Database migration from SQL2005 to SQL 2012. SQL groups Creation with Securables

I moved 7 databases to MS SQL server 2005 to MS SQLserver 2012. Both SQl are on different servers. I copied window level user group and created SQL server level groups in SQL server 2012. Now my ...
3
votes
1answer
25 views

Do temporal tables log changes when there are none?

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema: create table blah ( foo int identity(1,1), baa int ) and I ...
3
votes
3answers
817 views

Can the OUTPUT clause create a table?

I'm doing an update like this: UPDATE dbo.Table1 SET BirthDate = b.BirthDate FROM Table1 a JOIN Table2 b ON a.ID = b.ID And I want to use the OUTPUT clause to back up my changes. UPDATE ...
3
votes
3answers
116 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
1
vote
1answer
40 views

Slow-running query returns incomplete resultset

So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over ...
4
votes
1answer
43 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
1
vote
2answers
24 views

Using Variables to contain column names for SQL Server Merge statemtns

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to ...
0
votes
3answers
33 views

how can database snapshot to be used to recover data?

I know the logic behind database snapshot and how it works. But my problem is how can database snapshot to be used to recover data? can we take a backup of database snapshot and restore it in a new ...
11
votes
3answers
173 views
+250

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
0
votes
0answers
18 views

BallPark Upgrade Cost SQLServer 2014 standard with SA to Enterprise Edition with SA [on hold]

Anybody know a link or has a ballpark way of calculating upgrading from 2014 Standard Edition SA to 2014 Enterprise edition with SA? Failed when searching google land in this endeavor.
1
vote
1answer
21 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is ...
0
votes
2answers
41 views

Migrate SQL server data to new schema

I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent ...
-1
votes
1answer
31 views

How can I do a SQL Server 2000 bak?

I have 2 databases .mdf SQL Server 2000, and I need to restore them on SQL Server 2014. After some research, I saw that the best approach is making a .bak of this 2 files. Well, I need to install SQL ...
0
votes
1answer
30 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
1answer
12 views

Granting only SELECT VIEW permissions to specific Views to a specifc user on a MSSQL database

I have landed in a scenario were I have to let a user inside a database which is not his. Hence I want to make sure that he is only able to SELECT from the views that I deem he can read from. I have ...
0
votes
1answer
12 views

Generically get all related records from SQL Server Database based off of Primary Key of one table

In Sql Server it is possible to visually see all relations a table has by creating a diagram of one table and right clicking and selecting 'Add Related Tables.' Is this also possible to do for one ...
82
votes
4answers
11k views

Why are numbers tables “invaluable”?

Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table: USE Model GO CREATE TABLE Numbers ( Number INT NOT NULL, ...
1
vote
1answer
16 views

Looking for a better way with Reporting Db server

I have a "reporting" db server which is basically a clone of production using backup and restore in a stored proc to handle restore , user , permission etc. Backups/restores happen 1x per day at a ...
0
votes
1answer
20 views

Can mirror and witness have same port number?

When configuring database mirroring, the mirror and the witness have generated the same port number. When I hit next, it errors out with: End points for instances for the same server must use ...
3
votes
1answer
249 views

Can see other user's databases in shared hosting

This looks like a security concern but I do not know enough about SQL Server to know. Using a shared hosting service, if I go to restore a database I'm able to see other user's databases. I'm not ...
0
votes
1answer
19 views

data validation Datawarehouse ETL

Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following ...
0
votes
2answers
41 views

Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc. There are a ...
0
votes
1answer
624 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
28
votes
4answers
3k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
1
vote
1answer
174 views

After Insert Trigger never fires using Import Wizard

Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY. These 2 table structures already exist in SQL Server and they are related with ProjectID columns. PROJECT_IDENTITY has ...
5
votes
2answers
837 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
2
votes
1answer
523 views

Command fails as CmdExec job but succeeds from console

I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command: powershell -ExecutionPolicy Bypass ...
-2
votes
0answers
10 views

show invalid object name [on hold]

when this code runs on mysql database then fine but when this code run on sql server that showing error com.microsoft sql server exception invalid object name... private void ...
2
votes
1answer
38 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` ...
5
votes
2answers
8k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
0
votes
1answer
24 views

Powershell invoke sqlcmd

How is it possible to execute a query as a applicationintent=readonly using powershell? I have an cluster alwayson environment. I want to call a procedure with invoke sqlcmd to the readonly database ...
3
votes
1answer
28 views

Security concerns on SMTP in SQL Server

In my company, while I try to get the ports opened to connect to my SMTP server from my SQL Server running on Windows Server 2012, I get a reply saying that it is a security concern to open ports ...
2
votes
1answer
22 views

Relating 2 tables with possible wildcards?

I am having trouble getting the desired results when trying to relate the following tables. NULL in the following table essentially means that if it is not an exact match with another row then choose ...
0
votes
1answer
108 views

Error message is causing Error Log to grow out of control

The error log is growing like crazy - in one day it has reached 32GB. There is one error that keeps being logged. I have searched online for this error, however I couldn't find any helpful ...
0
votes
0answers
14 views

Create update Trigger

I'm trying to create a trigger that whenever a software package is installed on a PC, the person who installed the software needs to provide data for pack, tagnum, and instdate, and the softcost field ...
1
vote
1answer
28 views

Attach corrupted database

In a little C# tool, I use an a mdf file to save my data. The file became inconsistent, which isn't really a problem as there only a few changes since my last backup. Anyway, I would still like to ...
5
votes
2answers
231 views

Are cursors/While loops the only way to make administrative changes to multiple databases?

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in ...
3
votes
2answers
46 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...