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)

0
votes
1answer
445 views

SQL80001 - incorrect syntax near ')'

In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO ...
0
votes
0answers
6 views

SQL Server mirroring / failover alternative

I am looking at building at reliable database with Microsoft SQL Server, with mirroring and automatic failover. The SQL Server here is standard version, not enterprise one so High Availability ...
1
vote
2answers
84 views
+50

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 answers. Only one answer is ...
2
votes
0answers
18 views

Sql Server: Two identical queries, one times out, one is quickly done

I see a very strange behavior on Sql Server I need assistance for from people who know a bit more about it than I do. For the first time I used the Sql Server Profiler which confirmed that it is ...
0
votes
0answers
7 views

How to get the procedure names which has its underlying view missing in a database?

I need to identify procedures with broken dependency along with the list of view/ views or tables underlying under that procedure
16
votes
1answer
614 views

Restore Database using GUI - Wrong file to restore

I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task. One thing that I noticed is when I click on "generate script", the first line of the query is: ...
1
vote
0answers
23 views

How can I have SQL Server send Job Notification alerts to an API

I am trying to get the notifications for a job out of my email and into a chat client(HipChat). So far my googling has been less than successful so I will put the question to the community: Is there ...
3
votes
0answers
64 views

.Net CLR Assembly not working in SQL Server 2016

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .Net CLR assembly to parse strings. The error I get is: An ...
3
votes
0answers
78 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
-1
votes
1answer
21 views

Restoreation query execution continue after database restored

Today, I faced amazing problem which didn't cause any error or downtime. I executed a query to restore a database. RESTORE DATABASE Database_Name FROM DISK='PATH\FILE1.BAK', ...
2
votes
1answer
45 views

Concurrent reads and updates

Let's say I have a table with 50,000 transactions that need to be processed in the background in the same order they were received. I have a Windows service that polls for transactions, picks them up, ...
6
votes
4answers
3k views

How can I remove a bad execution plan from Azure SQL Database?

DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)...
13
votes
7answers
2k views

Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query: ...
1
vote
2answers
1k views

Can't restore Azure bacpac into localdb with SqlPackage

I have made a backup of my SQL Azure in a bacpac file and when I try to import the bacpac in localdb it always fail. Here is the command I use: PS C:\Program Files (x86)\Microsoft SQL Server\110\DAC\...
0
votes
0answers
36 views

Why can't a user in a group with the sysadmin role see any stored procs in SSMS?

A developer who is in a "Developers" group granted the server fixed role "sysadmin" on the development SQL Server installation is unable to see any stored procedures in a database - only the "System ...
4
votes
1answer
167 views

TDE - Encryption on a large database

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server. What kind of problems (if ...
1
vote
1answer
40 views

Can't “Script Database As -> CREATE TO” with SSMS 2008R2 and 2016

I created a SQL Login (Login01), mapped to DB user (User01), member of db_owner database role. When connected locally with this user in SSMS, i try to script out the database (Script Database As -> ...
41
votes
2answers
33k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...
1
vote
2answers
114 views

Choosing the right storage block size for sql server

There are many articles on what storage blocks size should be used for sql server e.g. Disk Partition Alignment Best Practices for SQL Server. The right blocks size should improve the performance of a ...
2
votes
1answer
65 views

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with ...
1
vote
1answer
35 views

SQL Server - Timestamp datatype

I need some information on SQL Server timestamp. I found all the required information here. The article says The timestamp syntax is deprecated. This feature will be removed in a future ...
0
votes
1answer
18 views

Hash Match in Query has high cost and high number of excecutions

I have the following query: declare @jobID NVARCHAR(20) declare @dataGroup BIGINT declare @lastSyncVer BIGINT set @jobid='P-0001' set @dataGroup= 5637144576 set @...
1
vote
1answer
233 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
3
votes
0answers
32 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
-2
votes
2answers
64 views

SQL Text data type deprecated?

I have read that Microsoft SQL Server has deprecated the text data type in favour of varchar(max). PostGreSQL already allows varchar (without a length) as an alternative for text. The question is, ...
0
votes
0answers
11 views

Retrieve Subscription Data on subscriber

I would like to retrieve the subscription data from a SQL server subscriber. The data that I am looking for is the same data that is displayed under the Local Subscriptions folder under Replication ...
1
vote
1answer
32 views

Log and Data Drive Configuration in VM environment

Anyone have any good feeling on how to arrange the underlying disk configuration for a VM (Hyper-V) implementation of SQL Server vs installing straight up on the hardware? For a native Install, best ...
3
votes
0answers
34 views

Horizontally scaling SQLSERVER, distributing the database with Sharding

I wanted to know if there is any way to distribute a SQLServer (I'm using 2012 version) database accross multiple nodes. I'm trying to compare READ queries performance between SQLServer and Mongodb. ...
2
votes
1answer
1k views

Fastest way to migrate database to AlwaysOn cluster

I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery. Some databases are ...
-1
votes
1answer
21 views

How to list all the views and its base object which has unresolved table or other object referece in a paricular database

I want to list all the views which are made up of base objects. That base object is no longer available.Thanks
2
votes
1answer
3k views

Could not open File Control Bank (FCB) for invalid file ID X when restoring from snapshot

We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we ...
0
votes
1answer
29 views

sqlserver 2008r2 service pack incompletely applied on shared features

I have installed sqlserver 2008R2 service pack 3 on my instance. The installation went through successfully but the instance still shows version number of SP1 in management studio. When I start the ...
0
votes
0answers
33 views

DBMS comparison [on hold]

I am interested in getting to know differences in some of the DBM systems. I am mainly thinking of MS SQL, PostgreSQL and mySQL, but am also willing to dig into some other DBM systems, like Oracle. ...
11
votes
1answer
258 views

stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the ...
3
votes
2answers
55 views

Group by multiple columns with different members of the same hierarchy?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | ...
4
votes
1answer
166 views

SSRS URL Changes while navigating

I am trying to track down the root cause of this issue I keep seeing. We have SSRS running with a custom URL like https://reporting.companyname.com/reports. When I connect to that address I can ...
4
votes
1answer
100 views

Reduce impact on SQL logs

Sharepoint and SQL Server issue as below When we change setting on SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30 we basically tell SharePoint to purge all ...
1
vote
1answer
34 views

When you drop or truncate tables in SQL Server do old data pages get overwritten with new data?

When you drop tables, does the empty space get automatically reused, or do you need to do some sort of maintenance to allow that space to get reused? Consider a situation where you drop and recreate ...
1
vote
0answers
25 views

Inserting rows into a temporary variable causes plan cache pollution in SQL Server

In my database I use stored procedures to insert multiple rows from a temporary table variable. Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each ...
11
votes
4answers
36k views

Query to report disk space allocation and used space

We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage). I'd like to write a query (report) for a single database ...
2
votes
2answers
49 views
+50

SQL Server Replication - Publication and Subscriber schema differ, need to sync

I have a SQL Server 2005 database where the publication and subscriber schema are different. This was intentionally done by turning the "Replicate Schema" option to False. However I now need to ...
2
votes
1answer
74 views

Missing .msi files gives error while repairing SQL Server 2008R2

I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries. In the middle of the repair loading, the window suddenly shuts down and gives ...
19
votes
2answers
11k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
1
vote
1answer
608 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
5
votes
5answers
367 views

SQL unused logins

is there a way to find out logins that have not been logged into the SQL Server or accessed the databases in the past 90 days ?
2
votes
1answer
56 views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
1
vote
1answer
29 views

Expanding AlwaysOn Availability Groups with Replication Subscribers

I have query regarding "AlwaysOn Availability Groups with Replication Subscribers"... Can I add multiple publishers to a subscriber(which is part of availability group as a primary server)? In which ...
1
vote
3answers
58 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
11
votes
7answers
45k views

How to determine the size of my tables in the SQL Server database

Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?
1
vote
0answers
34 views

Modify data using trigger

I have the following requirements in SQL server. I want to get updated/inserted values, edit it save to one column using SQL trigger. Say I have column named customer_name, when someone updates/...