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

learn more… | top users | synonyms (3)

2
votes
2answers
19k views

Conversion failed when converting date and/or time from character string

I have a problem, because I have been using this query without problem... until now: UPDATE T1 SET ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.Date) FROM FactTransactions T1 INNER JOIN DimStore T2 ON ...
3
votes
1answer
199 views

SQLCMD stuck on “set textsize 4096”

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012). The script is around 500mb and I added a GO every 100 lines. When I start the script with: ...
2
votes
1answer
402 views

Estimated row size and estimated data size is incorrect

I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns As a simple example, if I dump the table into a temp table, the ...
2
votes
1answer
103 views

Why take full backups when you can do COPY_ONLY?

With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG. As it's possible to restore logs backups after a database restoration ...
6
votes
1answer
113 views

RESTORE VERIFYONLY locks file in Azure Blob Storage

I'm using SQL Server 2012 SP2 to backup my transaction logs directly into Azure Blob Storage using the BACKUP TO URL statement. I am then attemtpting to verify my transaction log as follows: RESTORE ...
1
vote
0answers
29 views

Best event to track the DML operations being performed on Server along with the hostname performing those operations?

Currently I'm using the XE event sql_statement_completed to track the DML statements that are being executed and the hostname that is performing the same. However this event gets fired for each and ...
4
votes
1answer
195 views

Are there any reasons to avoid using sp_rename for renaming objects?

In the documentation it is clearly said: Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, ...
3
votes
2answers
1k views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number ...
0
votes
1answer
82 views

Upgade from SQL Server 2012 cluster Evaluation to enterprise

What I understand is that there is a restriction of upgrading a SQL Server 2012 cluster from evaluation edition to licensed standard edition. Is there a similar limitation for upgrade from cluster ...
2
votes
1answer
84 views

Seeking help with Extended events behaviour

I'm using Extended Events to track the procedures that are being executed on the server, along with the Hostname of its executor. I've used the following XE script for the same: CREATE EVENT SESSION ...
1
vote
1answer
86 views

How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]?

I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure. I want to log some statistics about how long the execution of the generated ...
1
vote
1answer
65 views

How can I find out what stats objects were used during a SQL Server 2014 query?

I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012. They ...
1
vote
0answers
142 views

Does “CREATE INDEX WITH DROP_EXISTING” use the bufferpool for the recreated index?

We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message: "Msg 802, Level 17, State 2, Line 30 There is ...
2
votes
1answer
84 views

Analysis Services Project: Data Source per Enviroment

I'm trying to configure a multi dimensional analysis service project, so that each developer of a team can configure his/her own data source (oracle). Changing the deployment target is easy, but how ...
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?
7
votes
1answer
2k views

Why do we use FILE = 1 in scripts for restoring a database

Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
2
votes
1answer
243 views

SQL Server backup status report

We have 30-40 SQL server instances in different servers (2012 with AlwaysOn). Is there any kind of report that can be made which captures the daily backup status and sends a mail, perhaps, or at least ...
1
vote
0answers
80 views

Securing High-Availability on Partitioned Database

I have a 90GB SQL 2012 database I am sharding over 3 database and I plan on growing as more clients are coming. A new customer came to us with a demand to know on we secure high-availability in our ...
4
votes
1answer
653 views

Determine the origin IP address of queries against a table?

I have a table that is being updated by a service being run at an unknown location. I've checked all of the servers that normally communicate with my database server and none of them appear to have ...
1
vote
1answer
86 views

SSIS Derived Column Conditional Time Modification

I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to 24:00:00,...
1
vote
1answer
306 views

SSIS Execute Sql Task not executed on server

I have a package that has an Execute Sql Task. When I run it in SSDT it works fine, the query is executed. When I deploy it on the server and run the package, the query is not executed and there is ...
-1
votes
1answer
258 views

Job successfully execute Stored Procedure but no results [closed]

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job ...
0
votes
1answer
38 views

One time database shrink catch-22 solution?

I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of ...
0
votes
0answers
58 views

SQL Server DBCC OUTPUTBUFFER: is the output buffer a circular buffer?

While viewing the results from a long-running SQL process with a large amount of output from "print" statements, it appears that the output buffer is a circular buffer. So midway through the process, ...
0
votes
1answer
139 views

Is it possible to know the remaining 'expiration of sql login password date' & ‘maximum password age’ through T-SQL

I would like to say that today incident whatever i have faced related to Microosft SQL Server 2012. I had logged in Microsoft SQL Server 2012 at starting working hour of office time ,but at the mid of ...
0
votes
1answer
446 views

Reporting Services service restart fixes slow reports

SQL 2012, the reports are displayed on a WEB page or rendered to PDF I have an ongoing problem with reports suddenly slowing down. The only way to "fix" this problem is to restart the service. This ...
0
votes
1answer
39 views

Need help with a query for a helpdesk application

I have a helpdesk application I'm modifying. I need to pull data into an ASP.Net DataGrid. In the problems table, there is a field for employeeID, which is the employee id of the person entering ...
3
votes
1answer
144 views

Grant access on Secondary but not on primary in SQL Server logshipping [duplicate]

I have a strange situation wherein users need permission on log shipped DB on secondary server but not on primary. The problem is, the login has access on many other DBs on primary server apart from ...
0
votes
0answers
210 views

PREEMPTIVE_OS_FILEOPS when querying Filestream

I've have a table which contains a Filestream column, when I query the table as follows: SELECT ID, FileContent --, * FROM dbo.Files AS f WHERE FileContent IS NOT NULL AND ID = '...
4
votes
1answer
872 views

Scope_Identity() returns null after inserting first row in table

I have a stored procedure that is used for inserting values in two tables. The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the ...
1
vote
2answers
1k views

Getting error “Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.” But a database with Id 7 DOES NOT exist

I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to: 1) copy database back up from the prod. 2) DROP all existing databases except ...
5
votes
2answers
196 views

Always On Availability Group, Always redirect user to read only instance

We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are ...
5
votes
2answers
177 views

How can I efficiently downsize some Unicode fields?

We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all ...
1
vote
0answers
352 views

iSeries Linked-Server SQL 2012 performance issue

I have a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries. I have set the linked server on the SQL2012 server as a Microsoft OLE DB Provider for ODBC ...
0
votes
2answers
46 views

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I ...
2
votes
1answer
292 views

SQL Server 2012 Always On Setup with two nodes

I have AlwaysOn setup with two data nodes and one witness. I faced an issue where my PRIMARY Server restarted abruptly. Here're some issues I faced during this timeframe. Due to abrupt restart of ...
0
votes
0answers
22 views

Same query takes even 100 times more ran from IIS app vs Management Studio

Sometimes (after a new deploy but which doesn't have any db updates) a specific query takes even 100 times more time to be executed than standard (or Management Studio). A few details: The query is ...
2
votes
3answers
231 views

Excluding clustered key columns from non-clustered indexes definitions

As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said ...
7
votes
1answer
274 views

Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest?

In this support article: https://support.microsoft.com/en-us/kb/956893 The answer to Q7 says: Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL Server versions and editions ...
3
votes
1answer
902 views

The multi-part identifier “d.dept_name” could not be bound

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp ...
1
vote
2answers
711 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
0
votes
0answers
58 views

SQL Server 2012 - Query Governor Not Working Properly

Our production instance has been experiencing long running queries, in an attempt to keep them from running long, we decided to implement the Query Governor. I started by testing in our development ...
1
vote
1answer
37 views

Batch process to delete table from SQL Server backup files

I have a set of full backups from a same SQL Server database saved in different files. For example: 01.my-database.bak 02.my-database.bak 03.my-database.bak I want to delete a specific table from ...
5
votes
2answers
113 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
1
vote
2answers
118 views

SQL Server compatibility level

I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level. How do I justify updating them to 110 ...
1
vote
1answer
59 views

Using RowLock to enforce Once-And-Only-Once

I have a Web Service that can fire off a chain of events for an order. It does this when it gets a message telling it to (max of about 2 per second). The problem I have is that I have more than one ...
5
votes
2answers
418 views

Import XML files into SQL Server 2012

Every Friday I have to import a couple (sometimes more than 300) of XML files into 2 tables. The structure of one of the tables, R000000, looks like this: R00000010 | R00000020 | R00000030 | ...
4
votes
1answer
1k views

DB Engine Stolen Server Memory is too high

I get error from System Center Operations Manager (SCOM). How to solve this error? SQL DB 2012 Engine Stolen Server Memory is too high. I run query and get this counters: ╔════════════════════...
0
votes
0answers
58 views

FreeTds with 2 sql server instances

I have python scripts on a Linux machine that connect to Microsoft sql server to get data. The server was initially 2008 version. Now it has both 2008 and 2012. How can I point the tds to connect ...
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 ...