Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
27,365
questions
0
votes
0answers
5 views
Create multiple logins with the same permissions
I have a server instance with 100 databases in it.
I need to create 20 new logins with access to 65 of the 100 databases. They all need to have the exact same permissions. The 20 logins should not ...
0
votes
1answer
12 views
2 logins with the same permissions
I currently have a login that has the server roles "sysadmin" and "public". I would like that user to only be able to have access and see 6 specific databases, but not the other 4 databases that are ...
0
votes
0answers
22 views
Setup certification for SQL Server running on Debian
I have successfully setup an instance of a SQL Server 2017 instance on the latest Debian release, and a domain that points to that server, and I have gotten the respective certificates by using ...
6
votes
2answers
166 views
Disable top (n) Sort Optimization for a specific view
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business ...
1
vote
3answers
37 views
How to unpack a DACPAC?
When I right-click on a DACPAC file in windows explorer, I don't have an option to "Unpack" the file, like this document indicates:
https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-...
0
votes
0answers
36 views
SQL Server 2019 License Not Found Issue
I am trying to install SQL Server 2019 Standard edition on a new professional Windows 10 64 bits (version 1809) virtualized installation with 6 GB of RAM memory. Setup fails just to start with the ...
-1
votes
0answers
30 views
SQL Server Table Partitioning [closed]
Looking for documentation about partitioning tables in SQL Server. The last white paper I found was for SQL Server 2008 (12 years old).
https://docs.microsoft.com/en-us/previous-versions/sql/sql-...
0
votes
1answer
45 views
How is it possible that some people are able to juggle several different SQL variations in their heads at once? [closed]
I have spent the last 20 years dealing with first MySQL (for about 6 years), then 14 years old PostgreSQL. In 2006, I naively thought that it was going to be painless, "because both use SQL"... man, I ...
-1
votes
0answers
39 views
SQL Server 2019 Performance Regression [closed]
We have recently upgraded our DEV environment from MSS 2016 (2012 Compatibility Level) to MSS 2019 (Native). We are seeing performance regressions across almost every query (approx. 12,000). Has ...
1
vote
0answers
28 views
How to prevent Dropping of tables but allow Creating of them, within a Schema
Is it possible to, within a specific Schema, allow a user to create a table, and select from it, but not allow them to Delete or update rows after initial creation, and also prevent them from dropping ...
0
votes
0answers
47 views
SQL Server 2019 Always On performance
We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution in SQL Server 2019 which will contain:
...
1
vote
1answer
50 views
Is it better to add a data file or better to increase the existing files (MAXSIZE)
I'm quite familiar with SQL-Server as a developer but not so much in the DBA role.
Currently I'm working on a rather large DB (~7TB, living in 7 files, all equal in size and all within one single ...
2
votes
0answers
59 views
SQL Server 2019: InitPersistentVersionStore failed after installing CU2
I have installed SQL Server 2019 CU 2 over a vanilla install of SQL Server 2019 RTM.
Now I am puzzled by the following messages in the error log at startup:
RecoveryUnit::...
-2
votes
1answer
24 views
insert one table automatically to other table reference column
How to make an insert in one table update a second table?
that already insert table......
List item
create table table
(
id int ,
student-name var-char(45)
)
create table table
(
id int ,
student-...
0
votes
0answers
23 views
Unable to create ssis db catalog
I am trying to create ssis db catalog using wizard but getting this error message:
The catalog backup file 'C:\Program Files\Microsoft SQL
Server\120\DTS\Binn\SSISDBBackup.bak' could not be ...
1
vote
1answer
38 views
minimum space is required on disk to restore a backup in SQl Server?
Suppose I am going to restore a backup (2 GB) in my sql server, I have sufficient space (20 GB) in my disk, but is there any algorithm that SQL server follows to calculate the minimum disk space ...
4
votes
0answers
45 views
how to remove all permissions in a database?
every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, ...
1
vote
1answer
29 views
how to list the SSRS permissions in each folder in the reporting services server?
I have the following powershell script that downloads all the RDL from a reporting services server.
I am not the author to whom I am very thankful. It has been very useful for downloading the RDLs.
...
3
votes
1answer
32 views
What happens when there's no tempdb space for version store operations?
If tempdb runs out of space, do transactions that need version store space fail, or do they wait for space to be freed?
2
votes
2answers
623 views
SQL Server Agent fails after installing SQL Server 2019 CU2
After installing CU2 our SQL server agent randomly stops working. If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error:
...
4
votes
2answers
125 views
sp_Blitz fails with collation error
SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: )
Installed sp_Blitz from Brent Ozar, stored procedure creates ...
0
votes
1answer
24 views
Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)
I know there are different variations of this question asked.
But what I am trying to do is that, there is a table with a numeric column (12,0)
that has data looking like
Units
-----
-1222222
0
-19
...
0
votes
0answers
20 views
Alter Filegroups to move from alternative FG back to Primary
I have a multitude of SQL Db's that have in excess of 80 individual file groups - I have been tasked with a project to try and get those FG's moved back to the primary - so I'd essentially have to ...
0
votes
0answers
23 views
sql server failover cluster setup [closed]
Someone use or know a tool to automate an Sql Server Failover cluster with multiple nodes?
We are try now with CHEF but it no provide this kind of setup.
Do we have found a possible workaround but we ...
2
votes
1answer
35 views
what does I/O affinity automatically do when cpu affinity is manually set
I have a 2016 Enterprise db server with 4 processors and 24 cores which is CAL licensed. So only the first 20 cores are used. Now I would like to set affinity so SQL Server will not use the first 20 ...
1
vote
5answers
114 views
Granting a user a permission to restore a database
In a database I created a user with the following roles:
Server Roles
public
Database roles
db_backupoperator
db_ddladmin
db_datareader
db_datawriter
The problem is I don't want to change the ...
0
votes
0answers
23 views
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0 [duplicate]
When I turn on the STATISTICS IO in SQL Server the provided message include the following:
Table 'Customers'. Scan count 9, logical reads 19500, physical reads 0, page server reads 0, read-ahead ...
0
votes
1answer
26 views
HIVE SQL [Error 10025]: Expression not in GROUP BY key
Here is my SQL statement:
my_table includes 10+ columns (e.g, day, ip_address, user, request, etc), including strings and numbers. I want to GROUP by & HAVING based on column 'ip_address', if ...
0
votes
1answer
52 views
Understanding about Execution plan and Index in SQL Server
I’m Christine and I have been studying for Data Analytics, I would like to know if you could help me clarify what I'm drilling down this execution plan as below.
About detail of execution plan, you ...
2
votes
2answers
71 views
How does the actual restoration works in SQL Server internally?
Suppose my Database DB1 lost some data, but I have a Full, Differential and then followed by Transaction log backups.
Till full\Differential backup I can understand it is like the snapshot of the ...
2
votes
1answer
87 views
Need help trying to run a batch file from a SQL Agent job
I am trying to run a batch file from a SQL Agent job. This batch file is a connection command and script file that will run. I can run the the actions straight from Command Prompt, but I need it to be ...
0
votes
0answers
19 views
SSIS Script transform package won't compile when deployed to dev [Visual Studio 2017]
I have visual studio 2017.
I have a script transform that I can run locally just fine, however when I deploy to the Integration Services Catalog on the dev server and run it, it fails with the ...
0
votes
0answers
23 views
Trace flag to stop update of dbts/rowversion column upon update [closed]
Could you please share if any trace flag exists which stops update of rowverion/dbts column when row/s are updated in same table.
Workarounds like storing values in mapping table/column ([key]-[Cast ...
2
votes
1answer
72 views
How to display rows containing special characters in a certain column
I have a table with the column f_Name. The column f_name sometimes contains special characters and numbers as in the examples below:
f_name
-----------
Pan23's
Ram'kumar
lawer"s()
I want to ...
6
votes
0answers
59 views
SQL 2019 install…what is AzureAttestService?
We started the installation and configuration of SQL Server 2019 on Windows 2019 and noticed after install a new service named 'AzureAttestService' was running and set to automatic. Services list the ...
0
votes
0answers
44 views
Unable to attach SQL Server database with shared folder in docker on Linux
The problem I'm having is that my SQL Server 2017 Linux container is not able to read my database files. I guess it has something to do with permissions and I'm sure I've overlooked something but I ...
0
votes
2answers
52 views
Single node availability group - is it possible?
Is it possible to have an availability group with just one node in it?
The reason I ask is because I am considering a distributed availability group spanning two single node always on availability ...
0
votes
1answer
22 views
Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'
I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get
Msg 208, Level 16, State 1, Procedure ...
0
votes
1answer
41 views
In SQL server during Log Shippping which portion (active\Inactive) of the Log file (.ldf) backup is taken and further restored in SQL Server [closed]
I am confused, like if we take the backup of the whole log file, wont it clear the transaction log though we have copy only backup.
In log file we have 2 parts, like active and inactive logs, in ...
0
votes
1answer
16 views
Select rows from table having specific values depending on another values in the same table
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:
Userpin TrDate Time1 Tr_type
4555 12-02-2020 ...
0
votes
1answer
35 views
Selecting preferred row in database based on condition
I'm new to SQL coding and I'm struggling to find a solution to my issue.
I have a database with contractors that have worked at our organisation on and off.
The database contains the contractors ...
0
votes
1answer
66 views
change stored procedure to accept a range of ids
So I'm looking at a stored procedure, which works with nopcommerce.
It's used when searching products.
There is one section which allows filtering of specification attributes, and it it seems to only ...
0
votes
1answer
33 views
Select Column data, from sys.column info
Either this is impossible, or I can't write a good internet search.
I have a query to get the table and column information for our database:
Select t.name [table], c.name [column]
From sys.tables t
...
4
votes
2answers
191 views
Execution Plan Warnings cardinality xml data type
I have the following statements:
-- 1st
DECLARE @AuditParameters XML = (
SELECT
1 AS AccountID,
2 AS CategoryID,
3 AS CategoryAttributeID,
'4' AS SyncBatchGUID
...
1
vote
1answer
16 views
Is it possible to Increase storage performance in azure vm using disk striping?
Given the following Azure VM:
E8s_v3 (8 cpu, 64 GB RAM, 12800 IOPS, 192 MBps)
I added 2 SSD Premium drives, one for data (1024 GBs / 200 MBps / Host Caching enabled read-only) and one for Logs (256 ...
0
votes
0answers
49 views
Why additional plans are created under simple parameterization?
I have a table which has 2163 rows and clustered index on id column.
I run the following queries:
select id
from ticket_attach
where id = 1
select id
from ticket_attach
where id = 2
select id
from ...
6
votes
1answer
51 views
Azure IaaS VM - Use D:\ Drive for tempdb - NTFS Formatting 64k
This was a question I was battling with earlier today and eventually figured out an answer. Wouldn't mind something better, but wanted to provide this to whomever also needed.
First, on an Azure VM ...
-2
votes
1answer
42 views
PowerShell script to output SQL query results [closed]
I have the SQL cmdlets loaded in PowerShell, need a script to execute a SQL statements and save results in pipe delimited file
0
votes
0answers
20 views
Complete and Accurate xEvents Resources [closed]
Does anyone have a good and COMPLETE resource for xEvents?? (And changes from SQL Version to Version)... There are a lot of bits and piece of information but I have not been able to find a definitive ...
0
votes
1answer
21 views
Select rows from two joined table that not in a third table
I have 3 table A,B,C,D with the same columns and I'm trying to retrieve the
user_id that are in table a and b but not c.
For instance:
Table A
user_id closed acct
10102345 ...
