Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,476
questions
-5
votes
0answers
44 views
How to write dynamic query with pagination in SQL [closed]
Query -
SET NOCOUNT ON;
WITH cte AS
(
SELECT *, COUNT(*) OVER() as TotalCount FROM tblOwnerDetails
where PhoneNo1 is null and PhoneNo2 is null
ORDER BY OwnerId
OFFSET @...
0
votes
1answer
33 views
I want to pull a report from multiple sql servers, where the Database owner and job owner is not SA
I want to pull a report from multiple sql servers,
where the Database owner and job owner is not SA.
I need a report from a Central monitoring server, if the database owner and job owner is not SA, I ...
0
votes
1answer
39 views
If Primary Data Centre DC1 goes down how to activate secondary DC2
We are planning to activate our secondary DC2 site and bring down our primary Data Centre DC1.
We have three node cluster with AG's ON.
Two nodes SQL07P & SQL08P which are in automatic failover (...
0
votes
0answers
14 views
3 select and collate the result of the query or organize (order by) by fields of the tables
I have 3 tables and each one with different structure and number of different columns, but the first 4 columns are the same in all tables. For instance
Table 1
Type
id1
id2
Period
FirstName
LastName
...
-1
votes
1answer
35 views
How to rewrite slow proc with deletes
How should this be re-written/optimized to speed up? On avg deletes 80 million rows. No FK references. Cannot use move data to another table and truncate methods.
SQL Server 2012 Stnd
CPU 8 Cores
...
0
votes
1answer
42 views
Use trigger on DELETE to retrieve original SQL command and user who did it
I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012.
What I need is the username that executed it and the command executed, then I can insert them on a log ...
0
votes
1answer
46 views
How to filter grouped data based on a single column values?
I have large patient dataset with diagnosis information (It has more columns than shown below, which are unnecessary for this). I have generated a sample datset like below:
ID Name Gender ...
0
votes
0answers
17 views
Full Text Search Launcher service and process
I am puzzled by the full text search in sql server. I am trying to break down the issue in multiple sub questions, maybe that would help to bring some light into the situation.
I created an ...
0
votes
2answers
41 views
Which way is a better structure for users table in a shopping project - SQL Server
I think it will be faster, but if its a better database design and structure I'm not sure,
Should I create a field in Users table which indicates if the user has a shopping cart or not, and only if ...
0
votes
1answer
38 views
Get data between nth occurrence of two characters
I'm trying to extract a certain data from below two examples and can't seems to get it.
From first string I need this value 99068001 and from second string I need this value
42135001
I think it would ...
0
votes
0answers
33 views
Error 18456 Login failed for User
We are observing these below errors in our logs frequently and it gets fixed automatically -
Error: 18456, Severity: 14, State: 46. Login failed for user 'sqllogin'. Reason: Failed to open the ...
0
votes
0answers
39 views
MSSQL 2012 Update (or create new certificate with same name) that uses Symmetric Key encryption
Super short version of my question:
For a MSSQL 2012 DB is it possible to update the current certificate with a new expiry date and new PW (or create new certificate with same name)? Or some other ...
0
votes
1answer
44 views
Looking for help understanding what is happening in this SQL Script that shows key info about SQL Server Autogrowth Events
Here is the code below:
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;...
0
votes
1answer
20 views
Looking for authoritative Microsoft reference about db_owner automatically having db_datareader role and how to check specific role is check marked?
SELECT IS_MEMBER("DB_DATAREADER");
The above return 1 for user who has db_datareader.
Also, it returns 1 for user who doesn't have this role but has got db_owner.
I am looking for ...
0
votes
0answers
25 views
Linked server to Oracle 19c table not showing
We have a linked server setup on our production, test, and development Windows SQL Server 2012 machines that all connect to our production Oracle 19c database.
All three linked servers in each ...
1
vote
2answers
123 views
How to lock table in SQL Server to prevent other transactions from INSERT and avoid deadlocking?
I'm writing a database trigger on Table A. In said trigger, I insert a new record into Table B by doing:
Look up the PK ID of Table B
Increment the PK ID and insert new row into Table B
(Note: Table ...
0
votes
0answers
41 views
How can you read MySQL text and longtext fields using EXEC AT on SQL Server
(A similar question already exists, but it relates to Oracle rather than MySQL.)
I have a MySQL table which I've connected to SQL Server as a linked server via ODBC (MySQL ODBC 8.0 Unicode Driver). ...
0
votes
0answers
61 views
Invalid Urn filter on server level SQL Server error
I am trying to alter permissions to some of my users using GUI. When I press Script Out or OK,
I am receiving this error attached. Has anyone experienced this error and what did you to resolve this ...
0
votes
0answers
13 views
Getting database in suspected mode while running DBCC checkdb command
On production we had setup maintenance job for every weekend. In this maintenance job we use dbcc checkdb command in one of step. sometime while dbcc checkdb command run it gives database consistency ...
-4
votes
1answer
35 views
Case statement syntax error [closed]
I am trying the code below but the SQL throws an error
case
when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))='RD')
then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4))
when (b....
0
votes
0answers
51 views
SQL server IN Clause usage with Pivot table
I have a problem with pivoting a table on MSSQL.
The main problem is, when I want to fetch row values in an inner query (with IN clause) I get syntax errors but when I type the values statically, it ...
0
votes
1answer
44 views
SQL server: Any user can access database instance without any grants [closed]
User created from Active directory can access all instances of databases in SQL Server without any grants.
Was shocked to see this behaviour on our environment.
Example.
We have a database named MyDB
...
0
votes
0answers
57 views
transaction log grows despite recovery=simple. SHRINKFILE does not release space
I have this script that restores a DB, sets recovery=simple, trims some tables & shrinks files:
RESTORE DATABASE [ABC] FROM DISK (....)
ALTER DATABASE [ABC] SET RECOVERY simple
go
exec [ABC]..trim
...
1
vote
1answer
50 views
Connect to local instance after leaving domain account [duplicate]
Yesterday, I switched from a domain user account to a local user account in Windows. Before the change, I could connect to my local database instance, but after leaving the domain, I cannot connect ...
0
votes
2answers
43 views
Track deleted user in SQL Server?
Is it possible to track which user was deleted from SQL Server and when was it deleted?
3
votes
2answers
64 views
Question Regarding ISDATE Function and DATENAME Functions
We have had an issue this morning with a report failing due to the following error:
"Conversion failed when converting date and/or time from character string"
The issue is that in the ...
5
votes
1answer
341 views
SQL Server 2012 equivalent of AT TIME ZONE capability
Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that's not available in 2012. I have a ...
0
votes
2answers
67 views
Solution for syncing a database between two server and secondary can be used for all DML operation
I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
1
vote
0answers
19 views
How to Enable TLS 1.2 on MS SQL Server 2012 SP1? [duplicate]
im having trouble connecting to MS SQL Server 2012 SP1 with regards to TLS Handshake. as stated by microsoft, SP1 only supports TLSv1 to TLSv1.1, if i'm not mistaken. I have been developing a spring/...
0
votes
1answer
44 views
SQL Server 2012 memory Issue
We are currently running SQL servers 2012 on machine having 64 GB memory. We have a SQL server that is currently using up all the memory allocated (45GB)to it, after consuming all the allocated memory ...
0
votes
1answer
72 views
Return unique records based on shared column in 3 tables
This is the database schema that I'm working with and I've included some sample data as an example of what is currently in the database.
CREATE TABLE Visits ([ADATE] date, [AKEYTIME] varchar(10), [...
-1
votes
1answer
81 views
How to execute join statement dynamically without using static group id? [closed]
I work on SQL server 2014 I need to run this statement below dynamically without
using group id static
because I don't know maximum group id using may be 10,15,20
so How to run query below dynamically ...
0
votes
1answer
17 views
EXECUTE AS LOGIN failed with local windows group login
I'm troubleshooting a permission issue that I tried to perform the query below
EXECUTE AS LOGIN = 'ComputerName\Windows Group Name';
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
SELECT * FROM ...
0
votes
3answers
190 views
Sum date between two date and group by month
I have a table as below:
Name Start Date End Date
Joe 20/04/2021 20/05/2021
John 01/05/2021 28/05/2021
I am using a SQL table-valued function to return a table that has 2 columns: Month and ...
0
votes
2answers
42 views
Get the Record where only Two Records exists in SQL
I want to get the parentID where user1 and user2 are using and only the two of them are using.
for example,
ParentTbl
| ID | Remarks |
|------|--------------------------------|...
3
votes
1answer
56 views
Database Unallocated Space - Correct way to measure
I am confused over the web regrading few scripts on calculating the database size and free space within same.
Most of the scripts over the web uses sys.databases_files dmv to calculate free space like ...
0
votes
1answer
44 views
Run SQL Job monthly at day 16. If it's a weekend, run it in the next business day ( dont care if is a holiday)
I need to run a job every day 16. But if it is "saturday" or "sunday" it must run in the next business day.
I don't care if it is holidays or not. I just need to run it.
I could do ...
1
vote
0answers
39 views
Can I use SQL Server Developer edition (free license) for Non-Production environmen that is used for training [closed]
Client uses SQL Server 2012 Dev environment for Training, is this regarded as a Prod or Non-Prod activity? Please point me in the direction of a Microsoft documentation for this scenario. Thank you.
0
votes
2answers
150 views
Get data between date range
I have a range of tabular data with two columns for dates Start Date and End Date and I want to pick up everything either Starts/Ends on coming Monday 03/05/21 or Starts/Ends on Sunday 09/05/21 and ...
0
votes
1answer
40 views
need the rows with a unique value on certain columns but date column doesn't help?
I have a table with multiple records by each employee. I need to find all the records by employee where any of the columns values changes. For example, I have the following data for George:
CREATE ...
1
vote
1answer
39 views
Significant performance differences executing alter and grant statements between users with same permissions
I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.
I have an automated build system that runs a series of SQL ...
0
votes
1answer
30 views
SELECT only the rows with a unique value on certain columns [closed]
I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George:
HOME
...
0
votes
1answer
29 views
How to find if the application uses the database snapshots in Management Studio [duplicate]
I'm taking over dba responsibilities for the migration of an environment that the application owners are unable to give enough information on.
I need to comment on possibility of optimizing licensing ...
1
vote
1answer
39 views
Thousands of After Insert and Update Triggers Causing Performance Issues
We have a After Insert and Update trigger on one of our tables. The trigger basically makes a json payload and enqueues it in a RabbitMQ system.
Today a large insert script was run on the table (over ...
0
votes
1answer
62 views
How to get SQL Server Developer edition for installation in non-production for the organisation
Kindly shed some light on this for me please.
Through my free membership of Visual Studio Dev Essentials, I have a free copy of SQL Server Developer edition installed on my personal machine.
How can ...
0
votes
1answer
49 views
I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table
I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number....
0
votes
2answers
74 views
Total logical reads per database on SQL Server
Is there a way to get the total running logical reads\writes per database from last restart? Trying to monitor what dbs are most used and if that changes over time.
0
votes
1answer
80 views
Index Reorganize fails
I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error.
Executing the query "ALTER INDEX [PK_abc] ON " failed with the
...
1
vote
1answer
59 views
How Restore backup as overwrite on a database in use in SQL Server? [closed]
I Want to restore a backup with overwriting on a database in use
for this problem I want to create a query base on these steps :
1- Remove All Connections [MyDB]
2- Create a backup from [MyDB]
3- ...
0
votes
1answer
33 views
an element with the same SQL SERVER key has already been added
Good morning community, I am having problems when I run this query from SQL 2018 and Visual basic 2010 shows me the following error "an element with the same key has already been added"
...
