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 ...
