Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,535
questions
0
votes
1
answer
88
views
Index Fragmentation - log issues
I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a ...
- 10.9k
14
votes
2
answers
5k
views
How accurate is the sys.partition.rows column?
The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Is it possible to support multiple snapshots / store a snapshot / restore from a snapshot to a state with another snapshot?
So, I would like to have a snapshot structure like the following:
A snapshot 1 is taken. Some code is run, then a snapshot 2 is taken.
Then some more code is run, and the database is reverted to ...
- 84.1k
11
votes
3
answers
24k
views
Looping through values stripping 1 character at a time
I want to loop through values and strip 1 character at a time from the values and display the result.
So if I have a table with values:
ID
___
34679
13390
89906
I want the result to look like this
...
- 9,323
-1
votes
2
answers
997
views
Ignoring null values in columns. pivot and unpivot
I have below data format
I am looking for below data format
I try all options but unable to reach the below result.
Query :
create table dbo.FS_Daily_HSE_SAPS_Audit(
formId int default ...
- 101
1
vote
2
answers
1k
views
With LocalAdmin become sysadmin SQL Server
I am a local Admin on my machine. My account wasn't added when SQL Server was installed, and I don't want to have to reinstall SQL Server. It worked until I tried to log and run the commands via ...
- 6,228
7
votes
2
answers
5k
views
SQL Server 2012 compress column for whole table
I was wondering if SQL Server supports a kind of dictionary compression for a whole table, and not just a page.
The system I'm working on wasn't initially created for the large amounts of data it ...
- 3
1
vote
0
answers
1k
views
Fast Bulk insert - return ID and other items
I have a C# console app which is inserting single rows into a SensorLog table, and returning the insertedId
I then use this insertedId to insert/update other tables
The DB is now getting around 50,...
- 301
19
votes
4
answers
33k
views
How to changes or update local server connection in Maintenance plan job
Two days back our client changed one of our Dev Server name
After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch.
We are using sql server 2012 ...
- 183
5
votes
1
answer
2k
views
SQL Server - Very high disk activity after running query?
I'm running SQL Server Express 12.0.4100 on an AWS instance with 16 cores and 64 GB of RAM with a 3TB and 9000 IOPS EBS attached. This has been running for 2 years perfectly with no issues until this ...
- 183
26
votes
4
answers
37k
views
SQLCMD command is not able to insert accents
I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits.
Here's the command I use:
SQLCMD -S .\MSSQLSERVER08 -V 17 -...
- 101
0
votes
1
answer
161
views
SQL Server Licensing for training, small scale development
I am currently undertaking SQL SERVER 2012 Training for the exam 70-461. I currently have SQL SERVER 2008 running on my laptop but I'm looking to upgrade to 2012 and I'm having trouble trying to get ...
CommunityBot
- 1
1
vote
1
answer
66
views
Rebuild and Reorganize index while altering index
Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns.
due to some reason if i need to add one more column in the existing index .
Do i need to ...
- 84.1k
0
votes
1
answer
118
views
Weird case , how do I delete a cluster under these circumstances [closed]
I would like to know how to delete a cluster with this type of case.
To explain, I created a cluster for two nodes and there was a bug so I had to stop the service on the first node. However after ...
- 7,939
0
votes
1
answer
212
views
Installing SQL Server with Virtual Account / (managed local accounts)
I am installing SQL Server 2016 by using virtual accounts (which I suppose is managed local accounts) and I cannot see any limitation, drawback of using this simpler method vs. using MSAs.
Would a ...
CommunityBot
- 1
6
votes
3
answers
88k
views
Stored Procedure null parameter within where clause
I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no ...
- 151
13
votes
2
answers
1k
views
ALTER VIEW drops Index from View
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
- 46.5k
3
votes
1
answer
1k
views
Why do updates to separate sets of rows deadlock?
Update: I have resolved my problem by switching to optimistic locking (SNAPSHOT isolation). The question of why that was necessary in the first place remains unanswered, but I had to move on and thus ...
- 133
4
votes
1
answer
188
views
Why does querying for records that violate a foreign key return incorrect results?
While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant ...
- 143
4
votes
2
answers
34k
views
SQL Server: Cannot resolve collation conflict between "Latin1_General_CI_AS" and "[garbage]"
So I'm writing a large UDF in T-SQL to be used in reporting. The UDF contains quite a few common table expressions.
At one point I was adding another CTE:
cteCmtCauses AS (
SELECT ProductId = p....
- 68.8k
3
votes
0
answers
45
views
Is it possible to log whenever someone decrypts a credit card in MSSQL 2012?
All of our credit cards are encrypted. The process to decrypt them is to Open the symmetric key and decrypt the data by certificate.
Is it possible to add a trigger on something in the process to ...
- 133
0
votes
1
answer
248
views
How can I create a table to lookup values for combinations of varying sized sets of numbers?
I have a table of products, each with it's own id (int) as primary key:
product_id name
3 product1
4 product2
6 product3
10 product4
I have a table of ...
- 177
5
votes
2
answers
5k
views
How can I pass column to function in sql?
I'm calculating median as:
DECLARE @TEMP TABLE
(
ID INT
)
Select
(
(
Select Top 1 ID
From
(
Select Top 50 Percent ID
...
- 68.8k
1
vote
3
answers
508
views
Remote connection to the instance with window account and NTLM is failing
I have the windows server with 4 SQL server 2012 instances
This is the test server. SPNs are not registered for any of those instances.
I can successfully connect remotely to 3 of those instances ...
CommunityBot
- 1
2
votes
1
answer
3k
views
Import data from one Database to another script
As part of a data migration I would like to script a data transfer from the existing to a new database.
I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012.
Note the ...
- 68.8k
0
votes
0
answers
426
views
SQL Server Agent fails to start
I am replacing a SQL Server 2012 cluster with a new one (keeping the same version of SQL, changing OS from 2008R2 to 2012R2).
I have 1 node installed so far, but the agent won't start. The error ...
- 51
0
votes
1
answer
848
views
Backup and Restore of master KEY and Certificate are not working on same server but different DBs. I have SQL Server 2012
I am following these steps but still not able to DECRYPT the data at destination.
In Source DB I have created Following:
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'Analytics@2020';
CREATE ...
- 9,942
1
vote
3
answers
3k
views
How to Add A User To A Database In Standby / Read-Only Mode In Sql Server
I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?
- 26k
4
votes
2
answers
5k
views
Difference between clustered index and partitioning column
As I understand partitioning, the column that you partition on is how things are divided into partitions.
But does the partitioning column have anything to do with order on the disk? (Aside from ...
- 234
2
votes
0
answers
69
views
Default Schema not being applied even after setting default schema and default DB
Hello Stack Exchange!
I have an interesting problem! Let me set the stage for you:
1) Windows Authentication
2) Default Schema identityiq has been set for the user with login XCORP\USERA
3) ...
- 121
0
votes
1
answer
57
views
Cancel Rollback and accept data loss
I have a SQL instance (2012 Standard Edition) which consumes all CPU resources because of a rollback-process. The instance has been restarted, so the database is currently In Recovery. I cannot see ...
- 9
0
votes
0
answers
133
views
Always On Availability Groups and MSDTC for SQL Server 2012
I have been told that since MSDTC is not supported for SQL Server 2012 Always On Availability Group and should be turned off.
what specific settings do I need to change to turn it off?
This is what ...
- 16.2k
-1
votes
1
answer
73
views
Improve performance in Deletion Operation
I Am doing Import Process of half a million records.
Initially i will move the .csv file data to a sql table,then i will parse the records (Split the records to different tables and then parse) and ...
- 21
0
votes
2
answers
122
views
Does MSSQL Server need more space than the size of the data itself for importing? How much more?
Does MSSQL Server need more space than the size of the data itself for importing? How much more? How to tell?
Recently tried copying data into MSSQL server (2012)
Microsoft SQL Server Management ...
- 1,591
2
votes
4
answers
2k
views
Vendors with Most Invoices in State
I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:
SELECT Max(...
- 875
0
votes
3
answers
2k
views
Unusual error message - sql server the row object is inconsistent. please rerun the query
I'm reviewing the error logs for an instance of SQL Server 2012 and found the following error in the log (error 669):
The row object is inconsistent. please rerun the query
I googled and the only ...
- 11
0
votes
2
answers
51
views
Very large Transaction Log File - When is it normal?
I have a server that is running extremely low on disk space and I dug into to determine if something is not functioning properly or if it simply is a healthy database that just happens to be growing.
...
- 2,427
1
vote
1
answer
894
views
Deny All DDL & DML Commands In Sql Server
I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?
- 144
0
votes
1
answer
98
views
SQL Record Transfer from one db to another instance having same table structure
I want to move data from server db table to local db table but need to make sure that the Ids of records won't change while copying from main Server db table.
like
The issue I am having is when I ...
- 109
3
votes
0
answers
214
views
Optimize query without rewriting it - vendor app [closed]
Here is a query that uses a lot of CPU on my SQL Server (8 cores):
DELETE TOP(1) rd
FROM
(SELECT TOP 99.9999 PERCENT *
FROM APM_IisBb_Request_Details
ORDER BY RequestDate ASC) rd
JOIN ...
CommunityBot
- 1
0
votes
0
answers
55
views
How to stuff together earliest date and or text
I'm trying to write a query and output to an excel file which outputs all of a members mortgages into a cell, but grabbing their earliest maturity date first.
Table Creation
CREATE TABLE mortgages
(
...
- 23.7k
2
votes
1
answer
3k
views
SQL Server: Transaction log still very large after log backup
I have a database that is on Full recovery mode.
I previously made a full backup, and then on a daily basis add differential backups.
Now I have backed up the transaction log for the first time, ...
- 15.7k
24
votes
5
answers
103k
views
What permissions are necessary for truncating a table?
I have a SQL account with the following permissions on a database:
The db_executor role you see this account being a member of was created by this script:
CREATE ROLE [db_executor] AUTHORIZATION [...
- 17.7k
0
votes
1
answer
1k
views
SSIS OnError propagating when it shouldn't
I have an SSIS package with a data flow task that uses a script component as its data source. The script component retrieves its data from a web service. The call to the web service sometimes fails, ...
CommunityBot
- 1
2
votes
2
answers
514
views
Concurrent backups (full and transaction) and testing recovery model
My intent is to test my recovery model and setup log shipping to replicate a database on another server.
I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs ...
CommunityBot
- 1
0
votes
2
answers
53
views
How can I only UPDATE if the user was created?
I got A cursor with some ifs like this one:
IF @ROLE='RW'
BEGIN
SET @COMMAND ='
USE '+QUOTENAME(@DATABASE)+'
CREATE USER '+QUOTENAME(@...
- 16.4k
1
vote
1
answer
3k
views
Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL
I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format. I've stored them in a staging table in that same format, and now I would like to ...
- 381
0
votes
1
answer
687
views
How to Grant A User Under A Sql Domain Account To Insert and Update Records
I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under ...
- 16.4k
0
votes
1
answer
171
views
How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever
I am looking for how to set the value of @MaxItemSeqNum
to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement.
I need this in order to insert the next record ...
- 19.9k
1
vote
1
answer
79
views
Why only few of expected tasks are processed although many cores are available?
I push some data from one database to another on the same instance using Service Broker. 20 cores are online, 6 Service Broker tasks are activated, every taks is working on indepentent piece of data. ...
- 46.5k