Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

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.

-1
votes
0answers
19 views

Best index strategy to use? [on hold]

What kind of index strategy would you use if there is something with a lot of writes and is quite expensive? I know there is no fixed index strategy but just wanted to know which one is the most ...
1
vote
0answers
22 views

Questions and advice for complying with Federal tax information (FTI) Security Guidelines for Federal, State and Local Agencies per Publication 1075

I've been able to find writings on compliance topics (e.g. HIPAA, SARBANES-OXLEY, PCI DSS) but I haven't found much on FTI (Federal tax information) and Publication 1075 outside of the IRS website ...
1
vote
1answer
32 views

Checking @@ROWCOUNT failing

I have a code similar to the one below in one of my SQL procedures, declare @rowcount int update table1 set value = @value where id = @id select @rowcount = @@ROWCOUNT if ( @rowcount = 0 ) begin ...
2
votes
1answer
36 views

Queries running parallel with default maxdop

I need to understand below behavior of sql server queries System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance. Currently MAXDOP is 0 and ...
0
votes
1answer
18 views

Sql selection of text file

I am new in SQL and need a small help in solving this. I have a column which has a file path name "C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt". And, I need to create a column (...
0
votes
1answer
21 views

What rights do you need for Ola's integrity check?

I'm configuring a new server and Ola's integrity check is working for user databases (I have restored a database of around 50 GB for tests) and is failing for system databases. If I look in the logs ...
2
votes
1answer
27 views

How to replace default on a table

I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table ...
1
vote
1answer
22 views

Remove specific character from string

I have a variable which is a VARCHAR and will contain a list of folders (1 or more). It may looks like that : @Folder = '/folder1/folder2/' or @Folder = 'folder1/folder2/' or @Folder = '/Folder1/...
0
votes
0answers
12 views

Data From ADP Needs To Be Ingested By SSIS, Data Has Variable Columns And Multi-line Headers

Our company's payroll admin is exporting data for us from the payroll software, ADP, which I am then tasked with inserting into SQL Server through SSIS. The problem I am running into is that the ...
-6
votes
0answers
23 views

Shared storage for MS-SQL databases [on hold]

I need a Shared storage solution for three-four different MS-SQL servers. I know there are some real good branded solutions, but I am being held back by budget. I started playing with FreeNAS, but ...
3
votes
2answers
225 views

Get top 1 row value from third table while joining 3 tables mssql

I am new to mssql .here I need to get some data using joins between three tables . Header join Lines join Images --> Result Header Table : Line Table : For each header record we can have multiple ...
2
votes
2answers
38 views

Add Constraint to check value exists in a non-unique composite key column

I have the following table, which has a composite primary key: CREATE TABLE dbo.Seqs( SequenceKey nvarchar(10) NOT NULL, UsageSequence smallint NOT NULL, col1 bit NOT NULL, col2 ...
-1
votes
1answer
52 views

SQL Server Logical IO Block Sizes and Physical Reads

I am trying to understand cache churn caused by SQL statements that are doing some table scans. Assuming LRU caches, if something is doing 400K logical IOs, how much memory is streamed across (IO ...
0
votes
1answer
27 views

Non-priviledge way to restrict UPD/DEL to user knowing certificate pass

I am assigned to researching column-level encryption, with the additional requirement of the sa not being able to read the encryted column(s). The idea as I've read in this DBSE post is to create a ...
0
votes
1answer
38 views

Msg 207, Invalid column name

alter procedure SP_AUTO @table_name varchar(200), @careof_check varchar(200), @ACCTG_careof_check varchar(200) AS begin declare @code_assgn varchar(2000) set @code_assgn=' if COL_LENGTH('+@...
2
votes
0answers
64 views

Transaction rollback hanging from past two months(MSSQL 2016)

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL ...
0
votes
0answers
32 views

Time Interval Partitioning in SQL Server

I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes ...
0
votes
1answer
120 views

SQL PRINT vs SQL EXEC

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error: Is there a way of ...
0
votes
1answer
25 views

Try_Convert Datetime showing '1/1/1900', want to show space

How do I get try_convert datetime to display NULL when the input is space? I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query. ...
1
vote
1answer
29 views

DB Schema for pricing [on hold]

I am designing a schema for pricing for an application. I am stuck on how to approach this. I have a product, it needs to get Price table to it and give me the options to set the price based on ...
1
vote
0answers
25 views

Record the duration of a stored procedure with multiple parameters

Is there a way to automatically record the duration and row count of the results of a stored procedure execution and save the plan? I have a procedure i'm troubleshooting for possible parameter ...
-1
votes
2answers
32 views

I need help with a SQL Query

I'm not so great with SQL Queries and would like to know if someone would know the correct Query to return the information below. I'm having a difficult time trying to figure this out. I'm using ...
0
votes
0answers
25 views

Help with Connecting to SQL Server using Oracle SQL Developer

I am trying to connect to SQL Server using Oracle SQL Developer. I was able to get the SQL Server JDBC installed on SQL Developer and am able to see the SQL server Tab on the Connections page. I have ...
1
vote
0answers
34 views

How do I parse Address into Street Number, Street Name, Type, Unit Number?

How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this? 2280 MEYERS AVE ALDERGROVE AVE & S ...
0
votes
0answers
13 views

Azure SQL Data Warehouse will always return zero (0)

I am studying Query Store, and while reading sys.query_store_runtime_stats I found these Average CPU time for the query plan within the aggregation interval (reported in microseconds). Note: ...
-4
votes
1answer
44 views

PRODUCTION DATABASE RUNNING OUT OF SPACE [on hold]

My database has 9556.97MB of space left. I do have about 2TB free disk space. The database is in full recovery mode and doing full backup everyday, also TLOG backup every 24 minutes. I used to just ...
0
votes
1answer
43 views

Getting data grouped by weekday and hour

I have a table: CREATE TABLE BolusData ( [BolusKey] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [BolusDate] [datetime2](7) NOT NULL, [BolusSource] [nvarchar](50) NULL, [BolusVolDelivered]...
0
votes
0answers
9 views

ZERO granted memory to all queries with AlwaysOn replication

we came across the following issue that is best described by the image below. To sum up, all (yes, ALL) of the queries this SQL Server was receiving were stuck at RESOURCE_SEMAPHORE and had 0 (NULL) ...
6
votes
1answer
205 views

TDE Master Key Rotation

Does changing the TDE Master Key (DB Master Key and/or the DB encryption key) always require decryption and re-encryption? If not, at what version did SQL Server begin to allow you to change the ...
0
votes
3answers
42 views

Simple way to use SP_EXECUTESQL for loop through all databases

I have a BIG problem running queries on all databases. I have a lot of scripts that works, but I would like to understand better how to use SP_EXECUTESQL instead of SP_MSFOREACHDB. Normally when I'm ...
0
votes
0answers
38 views

How do I add a directory, or bulk, of users to Microsoft SQL Server permissions

I have a front end program in visual basic that is using SQL as a back end. The only way I can get permission for login to use the SQL server is by adding each name or computer individually and this ...
2
votes
1answer
81 views

Stored Procedure: table-Value parameters

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and ...
2
votes
1answer
37 views

Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
0
votes
2answers
63 views

How to connect an MSSQL database engine to a local network

I need to let my public IP connect to a database that is located within the same local network. Ex: MY public IP is: 190.222.222.222 The local IP of that PC is: 1.1.1.4 And this server already has an ...
1
vote
1answer
43 views

SQL Server Query Store - Force Plan does not work

Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when ...
5
votes
1answer
181 views

Generic TVP tradeoffs?

Is there a best practice or strategy for table types used in TVPs? For instance, given the following: CREATE TABLE dbo.Colors ( Id int identity PRIMARY KEY, Name nvarchar(100), ); CREATE ...
3
votes
1answer
103 views

T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

Recently I attempt to search for a particular pattern by converting XML data into varchar(max) although I'm aware it's not the best practice and found out it's not working as expected:- Setup ...
2
votes
1answer
198 views

Fragmentation Level for Heaps

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
0
votes
1answer
54 views

Loading Data from CSV file or Staging Table to Multiple tables - SQL Server 2014

Hi i am trying to load data from CSV file to multiple sql server tables and some of the tables having Reference to parent tables so i can get the FK column value but trying to load via INSERT INTO ...
0
votes
1answer
40 views

Is it possible to log ship from on-premise SQL server to Azure SQL Managed Instance?

From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs? (the idea is to minimize downtime in a migration from on-prem to MI). ...
3
votes
0answers
28 views

Database Encryption scan for database <name> was aborted. Reissue ALTER DB to resume the scan

I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is ...
0
votes
1answer
34 views

Multiple Availability Groups on one instance

I want to add second availability groups to Always on group and I need to change the port to 5023 but it is read-only as shown below: The first availability group has an endpoint which is listening ...
0
votes
1answer
32 views

executing SQL query with temp tables using 1.6jdk

I created a batch to run a query that has temp tables as the below example IF OBJECT_ID ('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 select * into #TEMP1 from cutomer IF OBJECT_ID ('tempdb..#...
0
votes
1answer
52 views

Any way to represent simple AND/OR business logic in a database?

I'm designing an application to provide students access to modules once they have completed certain online courses. I have tables for: Students, Modules, Courses, and StudentCourseResults. Course ...
0
votes
0answers
25 views

Error when running SQL Server 2014 64-bit Import/Export Wizard

I have previously asked this question on Stack Overflow, but I am not sure if it should be migrated to DBA Stack Exchange. https://stackoverflow.com/questions/55186094/error-when-running-sql-server-...
2
votes
1answer
59 views

Does using a read committed isolation level and a NOLOCK query hint impact performance?

Can having both the SET TRANSACTION ISOLATION LEVEL READ COMMITTED and WITH (NOLOCK) on all tables in SP cause conflicts and downgrade performance on the SP? I just noticed some changes that a dev ...
0
votes
0answers
30 views

SQL Server 2016: Run xp_cmdshell as a different sysadmin user aside from the SQL Server service account

I been trying to research this for hours and can't seem to find anything. Basically what I am trying to do is run a xp_cmdshell command as a currently signed in sysadmin user To give some background, ...
1
vote
4answers
156 views

AG Cluster db upgrade by vendor

Vendor wants to put db into simple mode prior to upgrade then back into full recovery mode after upgrade. I do not want to do this because I am not sure what impact it will have on the db cluster. Won'...
1
vote
3answers
51 views

Long/complex INSERT and UPDATE query parser or visual editor/query builder [on hold]

I have long INSERT queries that insert values to table with over 50 columns. Is there any tool to parse this long query to key-value representation? like: INSERT INTO sometable(col1, col2, col3) ...
5
votes
4answers
191 views

Giving EXEC (@Variable) a Column name and Concatenation

I'm trying to writing a stored procedure that will check for orphan data before our customers attempt an upgrade, as orphaned records can cause issues. This is what I have thus far; IF EXISTS (SELECT ...