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

learn more… | top users | synonyms (3)

2
votes
1answer
36 views

SQL Server 2012 In-Place Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...
6
votes
1answer
245 views

Partition by ascending and descending performance

I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is... ...
0
votes
1answer
79 views

Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a ...
2
votes
2answers
117 views

How to fix a messed up replication on MS SQL Server

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-...
4
votes
2answers
98 views

Is there a way in SQL Server to make a table only able to insert by trigger?

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't ...
1
vote
1answer
71 views

Queries not Running in Prod [duplicate]

I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod ...
4
votes
2answers
219 views

Why does removing these LOWER calls change the execution plan like this?

While troubleshooting a slow query I was misled for a while by the actual execution plan telling me a sort late in the query where there should be few rows was taking >80% of the query time. At the ...
2
votes
1answer
51 views

SQL Merge Statement Spending Large Amount of Time Sorting on Primary Key

We are looking to use the SQL MERGE statement in SQL Server 2012 to handle data replication for a data warehouse that we have access to from the manufacturer of a 3rd party application we use for data ...
3
votes
1answer
92 views

MEMORYCLERK_XE Memory Usage Will not Clear

We've been having an issue where memory usage from Extended Events grows to over 1 GB and then will not clear - even when we stop all XEvent session on the instance. This hasn't been an issue yet on ...
2
votes
2answers
55 views

Cannot see new drive in SSMS maintenance wizard

I'm trying to setup a Backup Maintenance Plan in SSMS for Sql Server 2012, running in a failover cluster on Windows Server 2012. The current plan backs up to the same cluster volume where the ...
2
votes
1answer
64 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
4
votes
1answer
134 views

Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
1
vote
1answer
59 views

Stored procedure running for a long time

We have a stored procedure which is running for ever, whereas when it is run as script it takes just couple of minutes to complete. When I just declare the params and assign values to them and run the ...
4
votes
4answers
306 views

Easy way to change Column ID (primary key) of a table to Auto increment

I started to use SQL Server recently and I still don't know the best way to do some things. I created all the tables with a column ID as the primary key. Now when I try to insert values I get the ...
1
vote
2answers
63 views

SQL Join problem [closed]

I have a problem trying to join two tables TableA +----------+--------------+ | PersonID | customerId | +----------+--------------+ | 123 | 456 | | 258 | 145 | | ...
1
vote
1answer
33 views

Monitoring decimal values on Perfmon using User Settables

I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must ...
3
votes
2answers
137 views

What is considered for output from an sql server job step?

In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output? Background: Just executing a statement like UPDATE product SET ... will ...
3
votes
1answer
43 views

Changing database name based on project environment

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM) DEV environment: Source = EDB_DEV Target = EDM_DEV QA environment: ...
0
votes
0answers
21 views

Is there a way to get alerted for stats going stale?

It came to our notice that applications running slow when there is bulk update/insert operation on few tables, randomly on AD-HOC basis. NO fixed date when data will be updated. Thus we see the ...
0
votes
1answer
54 views

Generate backup script of SQL Agent Jobs using c#

I am using C# code to read all the SQL Agent Jobs: static readonly string SqlAgentJobName = "SqlAgentJob_123"; static ServerConnection conn; static Server server; static Job job; try { conn = ...
-4
votes
1answer
33 views

calculate a column based on existing columns [closed]

Simply I want to calculate a formula on specific columns in my table to populate an empty column in the same table using SQL Server.
2
votes
3answers
163 views

How can I create 3 tables with references On SQL Server

I started using SQL Server recently and I think this is simple to resolve. I need to create one table with a country list, and two tables that will use the country list in a column. Here is the code:...
1
vote
2answers
65 views

Can't create a table with the same name I deleted before

I started to use SQL Server recently and I think that its simple to resolve I did: 1 - Create table table_nome 2 - Drop table table_nome 3 - I'm trying to create a table with the same name, and ...
1
vote
1answer
32 views

linked servers between availability group to a server

I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables ...
2
votes
1answer
44 views

SQL Server spatial geometry properties on index

Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012. I have mixed SRID table and I was wondering if it ...
5
votes
3answers
237 views

Shrinking database which is not having any insert/update

We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/...
0
votes
1answer
23 views

Point in time restore fails - backup file from before the restore point not available (why does this matter)

I have tried to do a point-in-time restore of a database through the management studio gui, the point-in-time being yesterday morning (2016-04-12). This fails with the error "...cannot open ...
0
votes
0answers
26 views

installed 2012 SP3 after fixing missing cached installer files, @@ version shows up as sp1

I was to upgrade stg server running SP1 2012 standard, upon getting downtime on the first try I received multiple errors about missing installer cached files (.msp), which was fixed using Microsoft ...
1
vote
0answers
42 views

SQL Server dynamic inserts based on another table's rows

I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert ...
0
votes
1answer
85 views

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time. I am trying to ...
4
votes
1answer
72 views

Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from ...
1
vote
1answer
50 views

Rebuilding partitioned table clustered indexes

I need to know how can we rebuild the partition table clustered index with the table size being around 270 GB with 126 Partitions on it. Also, I want to execute it in the production environment, so ...
9
votes
2answers
230 views

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates. We have a large table partitioned by date with a couple of ...
1
vote
2answers
65 views

Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups

If I were to backup 50 databases in SQL server, I want to make sure that backup is taking place when the server is not that busy. Is there any way or script/tool to use to check when the server is not ...
0
votes
1answer
82 views

Automate backup using Windows Task Scheduler

I want to run an automatic backup of SQL Server 2012 Express using Windows Task Scheduler. I have 3 user databases. I want to backup all 3 user databases through a single stored procedure and a ...
7
votes
3answers
152 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
0
votes
2answers
57 views

Trying to query AD on a linked server error message is 7321

Trying to query AD on a linked server error message is 7321 error occurred while preparing the query. Any suggestions? Select Name, EmployeeID, Mail, SamAccountName FROM OPENQUERY(ADSI, 'SELECT Name,...
2
votes
2answers
36 views

How do I detect execute permission granted to a role (when no ON clause was used)

I just spent hours trying to figure out how an app had privilege to execute stored procedures. TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted ...
10
votes
3answers
347 views

Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs. However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the ...
2
votes
1answer
63 views

Unable to set user/group as principal on a proxy

I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to ...
0
votes
0answers
11 views

Send SQL Server Snapshot to External FTP Server

I am trying to set up Snapshot-Replication for SQL Server 2014. I need Server 1, the publisher and distributor, to create a Snapshot and FTP to an external FTP server i.e. FTP server is not hosted on ...
1
vote
1answer
81 views

Create Linked Server using Active Directory account

I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my ...
0
votes
1answer
32 views

is there a max number of rows in a SQL Server 2008/2012 and MySQL table? [closed]

Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O. Max number of concurrent ...
2
votes
1answer
132 views

Is it possible to disable use of fast forward-only cursor on server side?

I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr to set the SQL_SOPT_SS_CURSOR_OPTIONS option to SQL_CO_FFO. This means that its reads from the DB are ...
1
vote
2answers
72 views

Can I access the file stream shared folder using Listeners in case of AlwaysOn cluster environment?

One of the most effective ways to extend your SQL Server Integration Services (SSIS) control flow is to use a Script task to write custom code that perform tasks you cannot perform with the built-in ...
2
votes
1answer
90 views

Joining 2 queries to match Months side by side

I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts. Query 1 Actuals ...
2
votes
2answers
67 views

SQL Server 2012 Replication - MSrepl_commands growing all set to in progress

I'm stuck looking at this problem that we've had since our last restart almost a week ago. The distribution database is growing and no longer pruning; it appears that everything in the ...
1
vote
1answer
78 views

Deny users permission to create tables in master

I have noticed that I can create tables, schemas, stored procedures etc. the master database. I don't know what is granting this permission. It's annoying, because when we open a *.sql file in SSMS, ...
4
votes
2answers
276 views

How can I find the Database ID of a deleted database?

Where can I view/find the database ID after a database was deleted? I work in software/hardware environment and we have locking unit (prototype) synced in our S/W with unique DB ID. In order to reset ...
3
votes
2answers
126 views

Grant Admin to an Active Directory account in SQL Server

I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that ...