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

learn more… | top users | synonyms (3)

3
votes
3answers
193 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in ...
1
vote
1answer
257 views

Execute Dynamic SQL Generated From Select

A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process. How does one execute ...
2
votes
2answers
13k 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
175 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
297 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 ...
6
votes
1answer
93 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
25 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
151 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, ...
2
votes
2answers
569 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
69 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
76 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
63 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
52 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
122 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
77 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 ...
1
vote
1answer
970 views

Error message - view server state permission was denied - when connecing 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?
6
votes
1answer
1k 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
203 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
77 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
493 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
70 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 ...
1
vote
1answer
203 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
185 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
34 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
50 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
116 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
295 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
107 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
169 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
658 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
663 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
168 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
172 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
248 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
223 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
198 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
207 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
688 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
419 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
48 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
36 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
109 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
100 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
55 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
326 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
50 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 ...