Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
1
vote
1answer
41 views
nvarchar(max) in Included Columns of NC index
Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?
0
votes
2answers
27 views
Exec Stored Procedure on particular day of week
I have a procedure which should execute on specific day i.e. only on Monday of the week.
How to call it from another procedure passing variable or parametre ?
without using SQL server agent
...
1
vote
1answer
29 views
SQL Server Mirror: Witness Server(s)
Inherited a new SQL estate that has never been touched by a full time DBA. Mirroring has been setup and removed on multiple servers but not fully, old witness servers are still active.
Is there ...
0
votes
1answer
24 views
Difference between logical and physical reads/writes
First off, I hope DBA StackExchange is the right place to ask this question. If not, kindly let me know where I should go. I checked the canonical answers under meta, and could not find anything.
...
0
votes
0answers
17 views
DDLTrigger_Audit error “The user does not have permission to perform this action”
I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error.
database "XXX" failed on Jan 17 2019 ...
0
votes
2answers
34 views
Get data from both tables what join should I use
I have T1 as table 1
----------------
field1 | field2
---------------
val1 | val2
I have T2 as table 2
----------------
field1 | field2
---------------
val11 | val22
I want to write a query ...
0
votes
0answers
28 views
CHECKDB run times issue
When using the PHYSICAL_ONLY option and NOINDEX with CHECKDB, my run times are longer! But, on the weekend, it is 1/4 the time without those options. Yes, there are more data changes during the week ...
1
vote
2answers
33 views
Reverse SQL Server Listener?
The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.
We also have SSIS on the secondary replica of the HA ...
-1
votes
0answers
16 views
AlwaysOn Recovering Databases [on hold]
We rebooted Last Saturday, the AO groups failed over to DR except 1. When DR was rebooted, everything went into 'Recovering' mode on both nodes. Nothing is standing out to me to find the root cause. I ...
0
votes
1answer
33 views
Not able to execute xp_cmdshell from a proc using invokeSQL command
I have a powershell script that execute a SQL script remotely on a SQL server using invoke-SQL command but the problem is it is not able to execute a SQL script contains a stored proc that has a task ...
-3
votes
1answer
26 views
Hi all, I am not getting the proper result set as shown below [closed]
There are two tables tbl_sales_hdr and tbl_sales_ftr. I am struggling to get the results as shown below. Please help me to arrive at this kind of result set.
tbl_sales_hdr
date inv_no ...
1
vote
2answers
63 views
Insert missing dates within table
I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.
ID date Frequency
1 2014-05-18 5
1 2014-05-19 4
1 2014-05-20 ...
4
votes
0answers
55 views
Do I need a “downtime” to update a stored procedure? (Due to “definition changed” error)
I always thought that updating a stored procedure was something I could do while users were actively using the system.
But I am currently testing out an update to a stored procedure and while a long ...
3
votes
1answer
88 views
Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?
I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is ...
0
votes
1answer
39 views
RECOMPILE function
We had a production level event over the weekend where execution of a function suddenly went from MS to nearly a minute.
Unfortunately, SQL wasn't able to execute the ALTER Function and it kept '...
1
vote
1answer
33 views
Instead of Delete trigger
I want to pass one validation DML statement on the trigger. Condition is below DML statement
select distinct t1.cid
from table1 t1, table t2
where t1.cid=t2.cid
and
t1.status<>'...
0
votes
1answer
21 views
Missing data when trying to import large columns using powershell [closed]
Powershell command (whitespace added for readability):
Invoke-Sqlcmd -ServerInstance "$db" -Database "$dbname" -Username "xxxx" -Password "xxx"
-Query "select logmessages from log
...
2
votes
2answers
295 views
Restoring database with less data than the backup file
I'm not really experienced with DBA and SQL, so I need some recommendations from you.
I have a production database backup file, however it is very large, so when I'm executing restore, for example, ...
-2
votes
1answer
26 views
SQL query for import data [closed]
How can I write a single line SQL for importing 20k rows data from Excel to SQL server ?
Please don't tell me about import export wizard or to write 20 line repeatedly for do the same.
Please tell ...
0
votes
1answer
29 views
Server version error where restoring backup with SQL Server Management Studio
I have SSMS v17.9.1 freshly installed. I believe the only later versions as of this post's date and time are only preview versions.
I am attempting to restore a .bak file. I get the error that: The ...
0
votes
0answers
22 views
Maintenance plan changing from ALL USER DBs to SELECT DBs automatically
We came across an odd bug in Maintenance Plans on SSMS 2016 and 2017 for SQL Server versions 2012 and 2016 (at least). The setup is unusual, but I couldn't find anything about this or any similar ...
1
vote
1answer
97 views
corrupted data in filestream table
We started to observe a strange behaviour in out filestream table.
We have a couple of records that in field with data we have 0x or 0x000...
In audit trails data we have found that these broken/...
2
votes
1answer
86 views
Will deadlocks occur when deleting rows from a table that is being added to by another process?
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original table....
0
votes
0answers
22 views
Will deadlocks occur when deleting rows from a table when being added to by another process? [duplicate]
I have to archive data from a table into another table whilst the original table is in use. The archival process is to copy the rows into another table and then delete the rows from the original ...
1
vote
1answer
58 views
Exposing SQL Server database to internet
I have a POS application that is used by a business. The application has a SQL Server backend and there’s a local machine that acts as a server for the DB. The other computers within the office ...
0
votes
1answer
46 views
Can we restrict Maximum Job history rows for particular job/jobs in SQL Server 2012?
I am aware that there are 2 universal settings for Job history retention rows in SQL Agent. First one is default 1000 rows and per job max history row count is 100. Second one is deleting history ...
0
votes
0answers
38 views
sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals
When I query
SELECT principal_id, name, [type]
FROM sys.login_token
I found some local windows groups were not there.
However, when I check by
SELECT principal_id, name, [type]
FROM sys....
1
vote
1answer
30 views
How to update sys.login_token without restart SQL Server Service
I query sys.login_token to check if the current user is in a particular windows group.
SELECT principal_id, name, [type], usage
FROM sys.login_token
WHERE principal_id > 0
But after I added the ...
0
votes
0answers
19 views
SSRS version installation
I need to install two for two SSRS server system
The questions are:
Do I have to set up the servers according to the SQL version that the system works with?
Can I consolidate systems? as is?, there ...
1
vote
1answer
62 views
MSSQL - Query efficiency to many filters
I'm making an ASP.NET website which users can select more than 20 different filters at the same time. Something like 'Product Type', 'Year', 'State-of-Usage', 'Color', 'Size' and so forth.
Many of ...
1
vote
1answer
35 views
Query of sys.login_token doesn't include particular windows group while connect within workgroup
I had two SQL Server Logins which correspond to two windows groups:
MachineName\MyAppAmdin
MachineName\MyAppUser
And I use
select principal_id, name, [type], usage
from sys.login_token
where ...
1
vote
0answers
31 views
Openrowset is throwing error for file access as Access Denied
I am getting Access Denied error ..
Openrowset is throwing error for file access as Access Denied
I tried running these 2 queries as well..
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
...
-2
votes
0answers
46 views
Why variable is assigned NON NULL value in CASE expression
Usually, when I want to assign values to a variable from a lookup table I use MAX with a CASE expression. For instance the below snippet.
EDIT:1
The status_name is unique.
DECLARE @new_id INT,@...
0
votes
2answers
49 views
How to get the nearest date to a record date in sql?
I have the following data structure:
Table A:
ID | RequestNumber | Date
----+-----------------+-----------
1 | 1 |2017/09/27
2 | 1 |2018/06/02
Table B:
...
0
votes
1answer
49 views
Fastest way to read data from single SQL Server table into java under single transaction
I need to read a large result set from SQL Server into a java program. I need to read a consistent data state, so its running under a single transaction. I don't want dirty reads.
I can split the ...
0
votes
2answers
43 views
Linked server - connection fail from remote computer (using ssms)
I'm facing issue with linked server on Microsoft SQL Server 2012 (SP4).
When I remotely connect to SQL server (where linked server is created) with ssms and try to use/test linked server connection (e....
1
vote
0answers
41 views
Variable Speeds on CHECKDB
Does anyone know why I should experience variable CHECKDB completion times from day to day? I'm seeing times anywhere from 45 to 90min for the task to complete.
I am running CHECKDB on 8 SQL Server ...
1
vote
1answer
50 views
Ola Hallengren - Backupdatabase Task
When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error:
MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545
Invalid ...
1
vote
0answers
33 views
Archiving partition and removing the file group used for the same partition
I have created the below partition:
CREATE PARTITION FUNCTION pf_table (DATETIME) AS RANGE RIGHT FOR VALUES('2018-01-01')
CREATE PARTITION FUNCTION pf_table_Archive (DATETIME) AS RANGE RIGHT FOR ...
2
votes
1answer
30 views
'Ungroup' data based on column
I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has ...
1
vote
0answers
45 views
Getting attended, absent and no class days from database attendance
Problem
the problem is that I am able to get the days instructors record attendance and only that, I am unable to get the days they didn't make attendance and the days the instructors were not ...
5
votes
5answers
123 views
How to tell which windows group login I used when logging in via windows authentication
I created two SQL Server Logins which correspond to two windows groups:
MachineName\MyAppAmdin
MachineName\MyAppUser
Then in the database, I created two users with the same name and mapped them to ...
1
vote
1answer
32 views
SQL Server 11.0 and AD Groups
I have a user that has his Active Directory logon set to allow logging into a specific SQL Server, and that works fine. This user is also part of an AD Group that has access to a specific database on ...
0
votes
1answer
24 views
How to access objects created by a different user
User A created few objects with schema "UserAPriv", but now he is either leaving the company or changing the department.
Now we need to restrict his access to his previously created objects and make ...
-2
votes
1answer
33 views
SQL Server 2012 Enforce Password Policy [closed]
If i would like to change the password of my SQL login,
How much time i can't use with the same password?
2
votes
1answer
102 views
Correct way to group by month and year?
I need to group by month (and year) and I was thinking about:
GROUP BY CAST(YEAR(tDate) AS NVARCHAR(4)) + '-' + CAST(MONTH(tDate) AS NVARCHAR(2))
But I found in the web something like:
GROUP BY ...
0
votes
2answers
79 views
Get Headers from SQL Table
I am looking for a way to dynamically get the headers from a table without having to write them (select 'header1' as "header1"...., 'header50' as "header50" from MyTable)
It doesn't work above a ...
1
vote
1answer
35 views
Could anyone please help to understand MSSQL Compatibility Level
I have a task to upgrade database from MSSQL Server-2005 to MSSQL Server-2012.
So I took backup of old database and simply restored into SQL Server-2012(SP3).
Executed query on New database:
SELECT ...
2
votes
2answers
60 views
Measuring latency of a long-distance SQL connection
We have written a simple "client" app for a customer that connects to their SQLServer database, runs about 360 queries against a "property", and returns that data as a .Net object we call a "cProp".
...
-2
votes
1answer
26 views
sql permissions problem [closed]
I have a server 2012 r2 running SQL server, on the server I have a application that attaches to a data base on this server. Whats happening : I can access the database from the server and the ...
