All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

3
votes
1answer
524 views

SQL Server DOUBLE keyword

There is a reserved keyword DOUBLE in SQL Server, but I know that it's not a data type (other languages' double is float or real in SQL Server); does anyone know why this is a keyword? I've tried to ...
1
vote
1answer
161 views

Data type and Operator type Errors

i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, ...
1
vote
1answer
273 views

fn_dblog not showing up deleted table data

I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored procedure....
5
votes
1answer
254 views

Server roles in SQL Server 2016

There are 3 new server roles in SQL Server 2016: mediumrc largerc xlargerc Any idea what these are for? Google doesn't give any information about these.
1
vote
1answer
351 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM C:\Windows\Microsoft.NET\Framework\v4....
1
vote
1answer
38 views

Problem passing rows results to columns names

I have a table with following data: ID IDALUMNO ALUMNO IDTIPOEVALUACION TIPOEVALUACION NOTA 1 682 ALVITEZ MIRANDA, KAREN LIZET 1 PR1 ...
0
votes
0answers
344 views

Can't connect to an Active Node in an Active/Passive cluster

While being logged into an active node SQL Server 2012 node, I tried connecting to it from within the SQL Server Management Studio but was unable to do so. I tried the fully qualified name and the IP, ...
9
votes
1answer
399 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
2
votes
0answers
129 views

SQL User Entitlements - Combining Multiple IDs

New to SQL, first time poster. I searched a bit for what I was looking for. Couldn't locate. I appreciate your help...I'm still learning terms and commands. Consider this: User XYZ has access to ...
3
votes
2answers
147 views

Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. My question is where does the snapshot index ...
-1
votes
2answers
77 views

Getting a total value for items obtained in the current quarter

I've been working with MS SQL Server Management Studio and have a problem I can't fix. I am currently able to get the total for a set of values (Volume and Activity) from my SQL Server database. ...
-1
votes
1answer
361 views

Unable to start SQL Server Agent

We are unable to start SQL server Agent and it fails with below error: SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)...
0
votes
1answer
103 views

How to organize long Query

I have a massive query string that I am trying to pick apart for a project I am working on. I am looking for some guidance on how to take it apart(format it into human readable). Also, what are the ...
4
votes
1answer
1k views

Truncate log file mirrored database

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it. ...
0
votes
0answers
12 views

Truncate log file mirrored database [duplicate]

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it. ...
2
votes
1answer
506 views

Exclusively access a table and prevent other queries, even read queries

I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application: Get unprocessed messages (using the IsProcessing flag) Set their IsProcessing to true (in RAM) ...
1
vote
0answers
128 views

SQL Server losts and shows error 26 after random time

I have a Windows Server 2012 as server, which have SQL Server 2008 R2 where our database is stored. We have another 5 PC's installed with Windows 7 64-bit, which are run under domain of above server, ...
1
vote
1answer
190 views

SQL Server 2008 R2 Express Connection

I installed SQL Server 2008 R2 Express Edition. I opened the command prompt and typed sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword to connect to SQL Server. However, it ...
0
votes
1answer
1k views

TSQL Copy an existing database

I am learning how to administrate MS Sql server 2012, come cross a question and its suggested solution for copying an existing database for different name. Administer a windows azure sql database ...
0
votes
0answers
281 views

How to find cause of SQL-Server Full-Text Index Auto-Population Error?

I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected. Sample Table Definition CREATE TABLE [wrk].[WorkOrder] ( [id] ...
1
vote
2answers
315 views

How to prevent a trigger from running on certain statements?

I'm looking for an answer that satisfies my need on both SQL Server and MySQL. I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations ...
2
votes
1answer
91 views

Why do database servers have different time_source values

I have two SQL 2008 R2 Enterprise SP1 servers that have different values for sys.dm_os_sys_info.time_source. The time_source column of sys.dm_os_sys_info indicates the API that SQL Server is using to ...
2
votes
1answer
135 views

Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

I'm seeing the following tempdb I/O behavior over a one hour period: There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ~...
5
votes
4answers
508 views

How to uniquely identify SQL instance

I need to find a way how to identify SQL Instance no matter what changes were done to SQL Server and\or Windows Server where it is running, because we're collecting remotely information about ...
0
votes
1answer
841 views

The statement has been terminated

I have a table for which a update stored procedure has been created. For this table I created an update trigger. But when I either execute the stored procedure or execute an update query I am getting ...
2
votes
3answers
168 views

LEFT OUTER JOIN-ing the same table, grouping, summing and finding 0 or NULL

I have a table where I record searches performed on my site, tbl_search. An id is automatically assigned to entries. If the searched phrase is more than one word, my system splits the words and ...
1
vote
1answer
2k views

Permanent count of waiting tasks (2) in Activity monitor

Now I'm observing in Activity Monitor permanent count of waiting tasks: and at "Resource waits" tab I see next: so, my question is: How to find and resolve this wait tasks? I suspect that it ...
3
votes
1answer
730 views

Intellisense is not working since upgrade to SQL Server 2014

We recently upgraded our production server to SQL Server 2014. This server hosts ~2200 databases. When I connect to it as sa with SQL Server Management Studio 2014 and I type queries, Intellisense ...
2
votes
2answers
140 views

Why Does Partition Range Right Skip Deletes/Inserts?

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, ...
0
votes
1answer
784 views

how to extract current year and combine with specific month and date as a date

I have to get the current year with June the 16th as a date. For example, if I run the query now I want to get 2015-06-16. I tried the following: CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS ...
2
votes
1answer
76 views

How to check what type of lock an UPDATE statement uses?

How can I determine if an update statement puts a table lock, page lock, or row lock on a table?
1
vote
1answer
4k views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
1
vote
2answers
655 views

Collation conflict when running dynamic sql FOR XML PATH

I am getting the following error message when running the query below: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation ...
0
votes
1answer
46 views

Tables shared information

I would like to have a table pattern on my db. I.e. every column should have Record_Insert_Date Record_LastChange_Date Record_User_Insert Record_User_Update The obvious approach is to add these ...
-2
votes
2answers
232 views

Deny database access to all users but sa/admin [closed]

How can I deny the access to a database for all the users except for the sa BY CODE? I know how to do it by using the interface in management studio, but I need to have it on the script.
2
votes
1answer
103 views

Queries that are usually fast running, sometimes take minutes?

Updating my Entity Framework edmx file takes about 10 seconds. However recently it's been taking 3 minutes. sqlsevr.exe is responsible, thrashing the CPU during that time. Running sp_whoisactive shows ...
0
votes
2answers
585 views

Why does my SQL Server Express (2014) connection break after restarting my computer?

For some reason when I install SQL Server Express (2014), it works fine for a while (usually a day or two), and then quits allowing me to sign in. I'm not just talking about remotely here, either. ...
5
votes
1answer
524 views

Is there any way to monitor execution of xp_cmdshell in SQL Server 2012?

I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin ...
1
vote
0answers
100 views

SQL server actual execution plan: finding bottlenecks

I have a batch (7 queries) which executes in a couple of seconds locally by takes more than an hour in production. I've have a .sqlplan XML file from that long running query on production and trying ...
0
votes
0answers
51 views

clarification of ALTER DATABASE error message

I took a SQL Server 2008 database offline: alter database FOO set offline; Then I copied its MDF and LDF physical files to a new folder on another hard drive. Then I ALTERed the database: ALTER ...
4
votes
1answer
294 views

NUMA Nodes - MAXDOP - PLE

We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article: https://support....
1
vote
1answer
728 views

Different execution plans during INSERT at @table variable and #temporary table

I faced with strange situation: inserting of ~800 000 rows into table variable is much more faster than inserting (with the same select) into temporary table. INSERT is under stored procedure. SELECT ...
-1
votes
1answer
98 views

Using different aggregate functions in a pivot

I have a table of data collected in the time as shown below. I would pivot them using different aggregate functions: average for C1 and sum for the others. How could I achieve it ? DECLARE @T AS ...
1
vote
1answer
91 views

How do I use a SP in SQL Server 2014 to call a external XML source?

I am attempting to retrieve Currency Exchange rates from http://www.floatrates.com/daily/usd.xml using a SP in SQL Server 2014. The result is in XML. So I created the following SProc: --DROP ...
3
votes
1answer
555 views

Foreign keys become untrusted after bulk insert

In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet) with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are ...
-2
votes
2answers
2k views

SQL Server failed stored procedures log [closed]

How to find errors that was occurred in stored procedures in some timespan in Sql server management studio 2008 ?
0
votes
1answer
173 views

Loading subscription slow in SSRS

I have a SQL Server 2014 test box with SSRS installed. The site loads ok and reports are quick. When I try to go to Subscribe the page takes over a minute to load. From there if I pick a schedule it ...
0
votes
1answer
108 views

How to modify the precision of a datetimeoffset column?

In a sql-server database with data I have several columns with type datetimeoffset(7). Now I would like to change the precision part of the type to datetimeoffset(0). Is this possible without ...
0
votes
4answers
95 views

The multi-part identifier > “a.surveyid” could not be bound [closed]

I am getting the error : Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "a.surveyid" could not be bound. Below is the query ,what could be the issue? SELECT a.surveyid,* ...
2
votes
1answer
1k views

Indexed VIEW in SQL Server with PIVOT

A simplified version of my problem is as follows: I have three tables Records, Inputs, and Outputs. Records Id DTS   1  2015-01-01 00:00 ... Inputs Id RecordId ...