This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (3)

10
votes
5answers
27k views

What is the fastest way to export a table to a text file

I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell ...
6
votes
4answers
3k views

How does SQL Server determine precision / scale?

I'm running SQL Server 2012 SELECT 0.15 * 30 / 360, 0.15 / 360 * 30 Results: 0.012500, 0.012480 This one is even mor confusing to me: DECLARE @N INT = 360 DECLARE @I ...
2
votes
0answers
176 views

Identifying if spid on SQL Server is in rollback after low disk space

On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows. The process ran for about 10 hours and we noticed it was getting ...
4
votes
1answer
176 views

How can “HierarchyID” type work when “CLR” is disabled?

The following statement sp_configure 'clr enabled' returns: name minimum maximum config_value run_value clr enabled 0 1 0 0 I assume this means there are no CLR ...
8
votes
3answers
6k views

Truncated 200GB table but disk space not released

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
3
votes
1answer
185 views

Possible reasons for really long database recovery

This is cross-posted at SSC, but restore is now going on 6 hours. Hello, Overnight I ran restores with NORECOVERY for a 5tb database, along with several other 250GB - 1.3tb databases. I did that ...
0
votes
1answer
90 views

Select complete next row based on the current row

Hi i have a complicated condition. I have a table lets say 'Test' ID Partner Type Amount 143854 CSTC Purchase -0.81 144029 CSTC Purchase -0.69 144030 CSTC Purchase ...
1
vote
1answer
32 views

SMSS will start for one user, but not another?

We have a MS SQL Server 2012 and SMSS 2012 installed. The C:\ recently ran out of space due to a misconfiguration, and now we have 1 user who cannot start SMSS they receive the error: "Cannot find ...
1
vote
0answers
82 views

Is there any way to set stored procedure options like ANSI NULLs to true or false outside of creation?

I'd like to set ANSI NULLs and quoted identifier properties on a stored procedure after it has been created. Is this possible? For some context, I'm using Visual Studio Database Projects / SSDT, and ...
0
votes
1answer
75 views

Access not reading extended parameters

We have an ADP Access 2010 project, linked to a MS SQL 2012 server. I am trying to set up a table that has a combo box to select appropriate values, which are supplied by another table. A table ...
0
votes
1answer
180 views

Cannot connect with OLE DB

OLE DB connection... Can anyone suggest me on this. Authentication with windows security works in 2012-2005 "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; ...
2
votes
2answers
501 views

Is SQL Server Thread Safe?

If I need to update same row with two concurrent thread then is it possible in SQL Server? Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with ...
2
votes
1answer
59 views

Is tlog backup on replica clearing VLFs on the primary?

Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)? I was not able to find ...
1
vote
3answers
107 views

How to force SQL Serve *not* to maintain a log file

I have a situation that I need some advice to overcome. I have a SQL Server 2012 database that has been set to Simple recovery model. At times, my SQL Server transaction log file grows out of ...
0
votes
2answers
1k views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...
0
votes
1answer
351 views

Eliminate duplicates in different order from all combinations, can I order returned columns alphatically?

this should actually be not a too hard task, but it seems to be a little too early for me to see the solution: I have 4 different string values. I want to have all combinations of those 4 values ...
1
vote
1answer
342 views

How to obtain SQL feature list from the command line

Is there a command at the CLI or in PowerShell that will list the components installed for SQL? I'm looking for something like the Feature List you can get from running the discovery report from ...
1
vote
1answer
63 views

Trouble backing up remote database from local server

I have 2 servers LOCAL and REMOTE. REMOTE has a database 'remotedb'. From REMOTE the following statement backs up the database just fine. BACKUP DATABASE [remotedb] TO DISK = ...
10
votes
2answers
675 views

What is the logic behind ISNUMERIC for certain special characters?

The ISNUMERIC function has some unexpected behavior. The MSDN documentation says: ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ...
1
vote
1answer
338 views

What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? [closed]

The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank. They had ...
18
votes
2answers
10k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
0
votes
2answers
3k views

Can I use a SQL Server 2014 license on a SQL Server 2012 install? [closed]

We are going to purchase a SQL Server 2014 Standard Edition license, but one of the products we use is not compatible with SQL 2014 yet. Can we install SQL Server 2012 using a 2014 license ?
2
votes
2answers
469 views

Performance/Health checking queries for a user with Read permissions

I am a software developer that only had read access to our prod database (of course). Occasionally we have what looks like performance issues on our server when my DBAs are not available. Is there ...
2
votes
1answer
243 views

SQL Server 2012 Transparent Data Encryption and indexes

Does SQL Server TDE allow a query like this: select * from Patient where lastname = 'Smith' to occur without a full-table scan if the lastname column is indexed?
8
votes
0answers
328 views

Troubleshooting THREADPOOL Waits [closed]

From everything I have read, THREADPOOL waits occur when a task is waiting for a worker thread. A high amount of THREADPOOL waits typically means you are running out of available worker threads. ...
0
votes
1answer
71 views

Reporting Services DBs not appearing in Management Studio, but can be queried?

I've set up a server with native reporting services, connected to a database server which hosts its two databases. I'm trying to run a backup of the two databases so I can add them to my availability ...
0
votes
0answers
110 views

Imported SSIS Package Wont Schedule

I have a simple package that backs up the transaction log. I scheduled it and it runs fine. I script the job and export the package. I import the package ran the script to create the job and it gives ...
0
votes
0answers
42 views

SQL Server 2012 FTS and weird behavior with numbers

I stumbled on this weird behavior in SQL Server's Full text search. First run the following SQL-code in a database: CREATE TABLE test( id int IDENTITY NOT NULL, value VARCHAR(255), CONSTRAINT ...
0
votes
1answer
127 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
2answers
2k views

SSIS Package truncation and possible data loss (error code: 0xC020901C)

Good morning all, It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out. Here is the thing. I have created an SSIS package to import data from ...
2
votes
1answer
446 views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
0
votes
0answers
104 views

Mirroring failed over - created 2 principal database and will not allow me to drop one of them

For some reason my mirrored servers triggered an automatic failover. I have 2 databases that both think they are the principal server, one is "Principal, Synchronized/In Recovery" and the other is ...
0
votes
1answer
132 views

Best practice for testing database disaster recovery [closed]

This is regarding database disaster recovery testing. The production server has 20+ databases. I'm developing procedures for regular testing of disaster recovery and am wondering what is the best ...
2
votes
2answers
285 views

Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements

Suppose I have the following XML data, where <root> can have an arbitrary number of <child> children <child> can have an arbitrary number of <grandchild> children ...
4
votes
1answer
4k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
0
votes
1answer
70 views

sys.dm_db_index_physical_stats is extremly slow

I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. ...
0
votes
1answer
182 views

Cube Processing vs Database Processing

We have a SSAS database (SQL Server 2012 Enterprise) with 4 cubes in it. 1 of the cubes is significantly bigger than the other 3. We want to process the 3 smaller cubes at regular intervals during the ...
2
votes
1answer
11k views

What is different between SQL-Server versions 2008R2 and 2012

Dear SQL Server experts, Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft ...
0
votes
2answers
151 views

SQL Server Core - Installed SQL Server features

When using a traditional installation of SQL Server one has the Installed SQL Server features discovery report. Googled and found no info if some equivalent was available for the core installation. ...
1
vote
1answer
709 views

SQL Server 2008 mirroring migration to SQL Server 2012 always on

Quick question, request your input. I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on. Should I : Restore the SQL Server 2008 R2 backup to SQL ...
2
votes
2answers
17k views

Why can't we access SQL Server when a long query is running in Management Studio?

We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load. This work fine ...
1
vote
1answer
193 views

Finding the Table and Column Based on a Known Value

I've inherited a rather large SQL Server 2012 database -- about 2TB, tens of millions of rows in some tables, and about 350 different tables. I have basically no references nor documentation. There's ...
0
votes
1answer
494 views

Throw and RaiseError of level 16 do not cause alert to occur

The next alert has been created: USE [msdb] GO /****** Object: Alert [Notify] Script Date: 09/09/2014 11:38:31 ******/ EXEC msdb.dbo.sp_add_alert @name=N'Notify', @message_id=0, ...
2
votes
3answers
402 views

How to check history of primary node in AlwaysOn HA environment?

How do I see history of failovers of the primary node in cluster? I'm trying to figure out at what point secondary node started to act as primary. We are running Windows Server 2012 with SQL Server ...
1
vote
1answer
56 views

PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column

PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks
3
votes
2answers
235 views

Restore database into new DB without replacing the file structure

Our Network Admin has setup a MS SQL 2012 server and wants the DBs setup so the database files are located on 4 different drives. My problem here is when I try to restore a DB from a different system ...
4
votes
1answer
150 views

Combining full-text and scalar index

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around ...
1
vote
2answers
63 views

SQL logins access

I am trying to set up a set of SQL logins (one for each developer) on a server who can have access to a given set of databases, currently the setup is as follows: Login 1: db_owner of databases 1, 2, ...
0
votes
1answer
197 views

Attempt to add DQS Cleansing Connection Manager to SSIS results in error

I reopened an SSIS project in SSDT-BI (with VS2013 Shell) which had been dormant for ~3 months to see the dreaded red "x" had appeared on my DQS Data Flow Component: I attempted to replace the ...
0
votes
1answer
120 views

SQL query to return columns per value in value range?

I have a table like this: Date Value1 Value2 2009-12-30 23334 93 2009-12-31 21989 97 2010-01-01 18638 62 2010-01-02 ...