Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
-1
votes
0answers
11 views
SQL Log File to Table Insert/Update
I have a emp table which contains three columns. Lets say EmpNo, EmpName and Age. Empno is a primary key column. I am getting data from logs or text file. If there is no particular Empno then row ...
1
vote
1answer
50 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 [on hold]
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 ...
2
votes
1answer
72 views
Finding gaps on intersection dates
This is a problem I've been trying to work through on and off for the past month, and haven't made any progress. Essentially, I am working with a HR system that stores two major types of position for ...
1
vote
0answers
24 views
Resynchronize transactional replication in SQL Server 2012 without doing a complete reinitialization after disk filled
We have a DB that is doing a transactional replication to one other server. There are three tables it is replicating in the DB. Yesterday the subscriber ran out of disk space. This caused the ...
0
votes
1answer
34 views
Remove punctuation marks, special chars and space and compare cols
I have a table with new and old long description columns and I want to compare them by removing space, punctuation marks, and special characters for differences.
Create table DESCR_COMP
(
HCPCS Char(...
2
votes
4answers
61 views
.bak file not visible for restoration
There is a database on a server of which I would like a copy on my local machine for testing purposes, so I created a backup in the default disk location (C:\Program Files (x86)\Microsoft SQL Server\...
0
votes
1answer
26 views
Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode
I am laying out a plan where there will be 3 Node on site with automatic fail over with synchronous mode and 1 remote replica with asynchronous mode. My question is if the primary goes down which node ...
0
votes
1answer
40 views
WHEN MATCHED clause fails if there's a NULL in the ON columns [duplicate]
I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns:
MERGE PEPS AS peps
USING (Lots of Cols..
...
0
votes
1answer
15 views
Difference between Client Access Name in SQL server Fail over Cluster and Always on availability group
We are migrating from SQL Server Fail over Instance to Always On Availability group. The current network name is ABC\ABCTEST where ABC is the SQL server Virtual Name and ABCTEST is the named instance ...
2
votes
1answer
60 views
Backend Table/Row Locking Structure using CTE Queries
This question is an extension of a previous question that I asked. Table Locking When using Unions
With the same query we are using that is causing blocking the various queries used in these UNIONS ...
-1
votes
0answers
20 views
Who is responsible for Windows clustering DBA or Sysadmin [closed]
Our SQL server runs on SQL Clustering now management has decided to move to Always on availability group. I would like to start from scratch but needed clarification regarding who is responsible to ...
2
votes
2answers
39 views
Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key
When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server:
CREATE ASSEMBLY for assembly 'System.ServiceModel....
0
votes
2answers
26 views
What happens when one of the filegroup is full within SQL server
Just a basic question due to my lack of understanding on how below works:
For one of the databases around 1 TB in size we have 8 file groups with multiple files added to them.
Now for one of the FG ...
1
vote
1answer
64 views
Table Locking When using Unions
When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
3
votes
1answer
110 views
Any way to tune / optimize this query?
Actual Execution Plan here:
https://www.brentozar.com/pastetheplan/?id=H1fgOQTRX
Non Clustered Indices did not seem to change much.
SELECT DISTINCT TOP ?
Object1.Column1 AS Column2, Object1....
0
votes
1answer
30 views
SQL command mode - unable to run command
I just answered this question but never tried to run it. So I tried it.. https://stackoverflow.com/questions/53521410/sql-command-mode-sql-server-unable-to-print/53521635#53521635
and I thought if ...
0
votes
1answer
36 views
Machines using the SQL Authentication
I have a requirement. The leadership wants an email list of all the users hitting the database and last access date. I have created the script that has the username and the time accessed. The issue is ...
0
votes
1answer
42 views
INSERT trigger not SELECTing rows
I'm working with a application and I have no control over the application source code, only SQL.
The entire process is this:
Row inserted via an application
Vendor trigger fires and calls an SP to ...
0
votes
0answers
40 views
When would exclusive lock escalate from row level to table level when I delete data from my table?
I have SQL Server 2012 (SP3-CU10) and a table, that has
- 10 million rows
- 11 Gb in size (6 GB data + 5 Gb index)
- 20 foreign keys from 20 other tables are referencing this target table's ID column
...
1
vote
0answers
43 views
Convert output of a query to JSON format
Given the following:
https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b72da5e9263d55490fa0809c52577083
I need to have the secondname in the results too, and the output should be in JSON format.
...
0
votes
0answers
13 views
MSSQL Server 2012 Service stopped to Initiate Unexpectedly
Please need your help!
I have SQL Server 2012 and stopped to start the service unexpectedly. I tried a lot to fix the problem by searching from online helps with all related errors I found from the ...
0
votes
0answers
20 views
Update stats as default or with a sample value
I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s
On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the ...
3
votes
2answers
34 views
SQL Server created a new errorlog file but instance and agent were not restarted
One of our jobs failed but the job history had already expired by the time we investigated. We could, however, see that a new log file was created around the time it failed.
There was no instance ...
10
votes
3answers
218 views
Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?
Given the next example:
IF OBJECT_ID('dbo.my_table') IS NOT NULL
DROP TABLE [dbo].[my_table];
GO
CREATE TABLE [dbo].[my_table]
(
[id] int IDENTITY (1,1) NOT NULL PRIMARY KEY,
[foo] ...
0
votes
0answers
18 views
Archiving SQL Server data on disk to be pottentially restored
Right now, we are backing up data per table into text/CSV files per month. This works fine for fairly small tables. For bigtables, the text files become so big that the restore process for a month ...
3
votes
1answer
44 views
SQL: Can we make query to compare data for 2 different years in same month, but looking at the same day as priority
FYI: I'm using SQL Server 2012.
Because scope of the function is a selling date which is different by the days of week (like comparison for customer visiting percentage).
So the main key to compare ...
0
votes
1answer
34 views
The value for the parameter @MirrorDirectory is not supported error with Ola Hallengren Backup script
I am using Ola Hallengren's Maintenance Solution, and it's been working great for our environment.
Quick question regarding the User Database backups s proc. Currently, when I'm taking backups, the ...
0
votes
0answers
49 views
Doing bulk load on database and monitoring the growth
I have a small doubt on how this works. We came to know last night one of our sql databases had bulk load operation done with vendor giving that data of approx 300 GB would be bulk loaded.
Before and ...
1
vote
1answer
33 views
Why does a query on a local instance with local database files cause NETWORK_IO waits?
I have followed this tutorial to get the wait stats of a particular query while it is 'in flight' using extended events. Whilst the tutorial refers to an index rebuild, I just did a select statement ...
-1
votes
2answers
41 views
sql server update single row taking very long time and not finishing
I am trying to update single record for my small database but it take for ever and not finishing but if I restart the SQL server it work fine for short time other tables work fine
SQL SERVER 2016
...
1
vote
0answers
34 views
Restoring from a backup having dynamic file names
I want to restore a backup from a set of multiple files. The file names are generated dynamically. I have to restore the UAT with that backup. I need to schedule a job for this restore.
This is SQL ...
0
votes
2answers
28 views
how to create custom id in sql server with sequence
How can I create a table with custom ID like BK-0001001 with a sequence. Sorry I'm a newbie with databases. All help will be appreciated thank you!
0
votes
0answers
24 views
Is the is_column in dm_sql_referenced_entities reliable?
In SQL Server 2012, I've got a stored procedure that includes
INSERT INTO my_table_name(columns...)
SELECT ... stuff ...
but the output from sys.dm_sql_referenced_entities('my_stored_procedure','...
3
votes
3answers
105 views
Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB
We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem.
When listed from sys.databases, these databases report that their ...
0
votes
0answers
27 views
Finding Product Categories Not Purchased in Last 12 Months
I have code that resembles layout below, and I'm struggling to find a way to identify product categories not purchased in previous 12 months by a customer. I am able to identify categories purchased ...
0
votes
0answers
11 views
Unable to select the table result in linked Server
As I have successfully created the Linked Server in SQL Server 2012 SP4 with MYSQL Server.
Some of the small tables record able to fetch from the MySQL Server. And some of the big tables records when ...
2
votes
1answer
394 views
Killed Backup in Rollback State for Days
I killed a backup job of an 80 MB database which has been rolling back for days...
I tried to take the database into single user mode, but that too hung in rollback state.
I tried killing all ...
0
votes
0answers
57 views
Optimizing sp by removing cursors
I have a cursor to compute the credits and debits column in my accounts table. The table structure is such that I have a column AccountLevel. Hence, my accounts are based on 4 levels (only) like this:
...
1
vote
0answers
19 views
Dynamic rows and columns with Pivot and calculated value
I'm trying to build a pivot request but i don't undestand how it's applicable for my database. The value I need has to be calculate (A percentage) for each column and row.
I'm using SQL Server 2012.
...
2
votes
2answers
52 views
Insert Missing Date row for each group With & W/O using CTE
This is my calendar table:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '9/24/2018';
SET @EndDate = '9/28/2018';
WITH theDates AS
(...
-1
votes
1answer
23 views
any way to delete part of database data file?
I would think that not but maybe I'm wrong.
I have large SQL Server 2012 database which I care only about records from last month. I know it's possible to split data file, but then can I throw away ...
1
vote
1answer
40 views
Transpose Query result in sql server
I have a query that returns several rows (one to many relationship), I need to be able to obtain as a result a single row that has concatenated these values.
This is my query:
SELECT I.[ID]
...
0
votes
0answers
33 views
Operand data type bit is invalid for max operator VB.Net and SQL Server 2012
hi im trying to make a tabulation in vb.net and sql server.
and always get the error Operand data type bit is invalid for max operator I use a Bit data type for the rows inside my pivot columns.
...
0
votes
1answer
27 views
Azure SQL Synonyms with On-Premise
I have an on premise database that I can't move to the cloud due to it not being our software, though I have a website that ties into it using synonyms and views. Is there any way to allow the Azure ...
1
vote
0answers
49 views
Another ANONYMOUS LOGIN Error
I have two servers/instances, both MSSQL 2012. I have a linked server set up on SERVER1 "using the login's current security context" (@useself='TRUE') and RPC set to TRUE. SERVER1 is using domain ...
1
vote
1answer
54 views
How to set the first row of CSV data as column name in SQL Server?
I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file?
If not, after reading the data from the csv (from second row),...
0
votes
1answer
25 views
What is the infile SAS equivalent in SQL Server?
I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm)
...
1
vote
0answers
13 views
Multiple queries to linked server (Access db) block Sql server
On a SQL server 2012 I've created a linked server pointing to Access database:
It all works fine, unless we have more than one query to this server - queries that are not even related to the same ...
0
votes
1answer
22 views
SQL Server 2012 - login mapping to user
I'm still struggling a bit in moving from Oracle to SQL Server, especially with the login-vs-user thing. I've restored a database from a client site so we can have a development copy. I want to set ...
