SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

0
votes
1answer
135 views

SQL Server : how to decide if FULL backup or DIFFERENTIAL

The following scenario (on SQL Server 2008 R2 and 2012 servers) A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only ...
0
votes
0answers
49 views

SQL Query to calculate total time worked and total distance travelled

I am trying to write an SQL Query which calculates the Total Time worked and Total Distance Traveled by a Driver for each date. The StartOfDay is the MIN(LogDateTime) WHERE EventCode = 1 The EndOfDay ...
1
vote
1answer
15 views

How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get: Msg 9514, Level 16, State 1, Line 3 Xml data ...
0
votes
1answer
26 views

Trigger on replicated DB

If I have database db1 with schema sch1. I want to create another schema sch2 which will be used by different application and the tables in sch2 will be populated from tables in sch1. So my approach ...
0
votes
1answer
35 views

Should I shrink the Log File

I am a developer but have to do DBA stuff now and then. We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB. ...
6
votes
1answer
168 views

SQL Server Agent initiating self-termination

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly: Sql Agent Output file [097] Memory leak detected [...
0
votes
2answers
21 views

SQL Server - Select last value given a date

To make it easier, I have data that looks like this as an example: What I am trying to return is the Amount of the Balance Update as of a specified Date for each Investor_ID. For example, if my ...
0
votes
1answer
111 views

on every row on SELECT Query must lookup to other table

I'm working on Login/Logout System and I'm facing this problem regarding on SELECT Query. I have 2 tables namely dtr_tbl and shift_tbl. In dtr_tbl are the logs of the user(login/logout time details) ...
6
votes
1answer
2k views

Alternatives to AdeptSQL Diff (for MS SQL Server 2012)

It seems AdeptSQL Diff is not compatible with SQL Server 2012 (at this time). And it is my preferred tool for deploying changes. I realize this is a shopping list but I hope SE won't mind. I am ...
3
votes
2answers
2k views

Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio

When right-clicking on a table and selecting "select top 1000 rows", I get this error: Is there any workaround, other than the obvious upgrade to SQL Server 2014?
4
votes
0answers
51 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
1
vote
1answer
18 views

Permissions for Cluster service account in SQL server

Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL ...
0
votes
1answer
50 views

Database design considerations for unused columns with every table has same schema

I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate ...
0
votes
2answers
1k views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
1
vote
2answers
37 views

Execute stored procedure remotely using linked server

We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally. I'd like to ...
0
votes
0answers
13 views

Issue with Linked Server query

I have a job which fetches data from an Oracle 12c database and inserts into a SQL Server 2012 database, via a linked server from the SQL side. It's a simple Select * statement and just pulls in any ...
0
votes
1answer
1k views

DB2 Linked Server on SQL Server 2012 - Grant Access to Users

I have a linked server on SQL Server 2012 instance pointing to a DB2 database and I have Sys Admin access to that instance. I am unable to test connection to that DB2OLEDB database as I get below ...
0
votes
0answers
104 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
4
votes
1answer
177 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
1
vote
2answers
172 views

Get first and last record for each date and calculate their difference

I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2. I have posted a sample data set for 2 days ...
0
votes
0answers
24 views

Finding records only when all ids present in a group

Having these two tables id | val TableA -------- 1 | A 2 | B 3 | A 4 | B 5 | A 6 | C 7 | D id_A | data TableB ----------- 1 | a 2 | b 3 | c 5 | d 7 | e First I ...
0
votes
1answer
42 views

Update empty values with null

I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed ...
2
votes
2answers
19 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 ...
5
votes
1answer
302 views

Move content between filestream containers

I have a server running SQL Server 2012 with Multiple Filestream containers on a single table. I need to split one of the existing filestream containers into two parts by creating a new filestream ...
9
votes
1answer
437 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
1
vote
2answers
62 views

SQL Server 2012 database in suspect mode after upgrade from Developer to Standard Edition

SQL Server database is in suspect mode after an upgrade from developer to standard edition 2012 version. In the log file there are two errors: 933 and error 905. Following error occurred when we tried ...
7
votes
2answers
4k views

AlwaysOn Availability Group Automatic Failover does not work

Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server ...
16
votes
1answer
16k views

Why does SQL Server 2012 Express use 9.5GB of RAM on my server?

I am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore. When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr....
1
vote
0answers
63 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
0
votes
1answer
325 views

delete WSFC 2012 cluster role/group fails

PS C:\Windows\system32> get-cluster Name ---- sql2012poc_cl1 PS C:\Windows\system32> get-clustergroup Name OwnerNode State ---...
-1
votes
1answer
36 views
1
vote
1answer
51 views

Matching empty string is not working

I have the following query SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength, DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, CAST(Box.ice AS ...
0
votes
2answers
84 views

Tiny query used to be sub-second speed, now takes 60+ seconds

Ok, I've got a monitoring process that runs the following query to make sure the databases are backing up regularly. This process used to take 1-2 seconds for connection, query execution, and ...
0
votes
0answers
18 views

Querying Table from another Database using Synonyms

I have two databases(A,B). on database "A" i created synonyms CREATE SYNONYM [dbo].[Names] FOR [server1].[B].[dbo].[Names] I should now be able to query the table "Names" from within database "A" ...
0
votes
1answer
25 views

Call to sysmail_delete_mailitems_sp keeps deadlocking

I run SQL Server 2012. I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this: Begin try Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '...
1
vote
1answer
161 views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
3
votes
0answers
35 views

Concurrent Merge Issues

A (simplified) model of my application has several processes dumping data into the table below: CREATE TABLE [dbo].[InputData] ( [InputId] [int] NOT NULL, [Dts] [datetime] NOT NULL, [...
1
vote
1answer
238 views

SQL Server Management Studio 2012 Application Cannot Start

I need someone's help. I'm trying to install SQL Server Management Studio on my Laptop Operating System: Windows 7 Service Pack 1 But I got an error message saying: The application cannot start. ...
0
votes
0answers
9 views

Multi Purpose Witness/Master Data Services SQL Server

I currently have a Master Data Services SQL Server instance that I use to execute SSIS packages, moving and consolidating data from a few OLTP servers to a Data Warehouse. It currently just runs its ...
7
votes
1answer
238 views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
0
votes
1answer
29 views

Select 1 Row Per Day For Each Database

I have a table with over 60 million rows. Snapshots taken at multiple times a day for all databases across all instances. I am trying to reduce the rows in my select by only returning 1 snapshot per ...
3
votes
2answers
34 views

ALTER TABLE SWITCH

I have two columns that I want to convert from computed to non computed. I have the code for that. However, it was suggested to me to use the ALTER TABLE SWITCH. I believe the individual who ...
0
votes
1answer
178 views

How to specify localhost for SSIS package source server?

I have a job running on SQL Server 2012, where one of the steps is of type "SQL Server Integration Services Package". For the "Package source" I choose SSIS Catalog. For the "Server", if I type in the ...
3
votes
1answer
120 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
0
votes
0answers
25 views

SQL Server 2012 sp3 error and SNAC

It is a client server with only the Management Tools basic/complete and Client Tools connectivity. While it appears that some the of SP3 took based on items in the Add/Remove Programs having a .6020 ...
5
votes
1answer
5k views

AlwaysOn Availability Groups go to Resolving status

We have a two-node cluster set up running SQL Server 2012 on Windows Server 2012 R2. The base cluster consists of only those two nodes. The quorum is set up as node + disk majority, with a shared ...
3
votes
2answers
2k views

Removing secondary replica from SQL Availability Group causes large log file

Due to an unforeseen hardware error, I had to take our secondary replicas down for maintenance. This was really due to a storage issue and, it was decided at a higher level, to purchase a new storage ...
0
votes
0answers
8 views

How to move SSAS tabular instance databases?

We have a SQL Server 2012 SSAS tabular instance in our environment that is sitting in domain1. We need to move these SSAS databases in a different SQL server 2012 that is on domain2 which has no trust ...
2
votes
2answers
3k views

How to allow connections to SQL Server with 2 differents ip

I have a small issue here: I have a SQL Server instance 2012 (let's call it SQLINSTANCE) on a cluster. I also have 2 network interfaces for this instance: - SQLINSTANCE.my.network - SQLINSTANCE.mgt....
5
votes
1answer
229 views

ORDER BY and comparison of mixed strings of letters and numbers

We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be ...