0
votes
1answer
26 views

What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)?

I have a corrupt database. The last known good backup was over 2 weeks ago. I've run a CHECKDB and have the page numbers where SQL Server located the corruption. I've looked at the data on the ...
1
vote
1answer
22 views

Avoiding entitized characters when using FOR XML PATH for string concatenation

I have this query: SELECT DISTINCT f1.CourseEventKey, STUFF ( ( SELECT '; ' + Title FROM ( SELECT DISTINCT ...
1
vote
0answers
26 views

Profiling parameters

I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able ...
0
votes
2answers
12 views

New SQL Server 2008 instance, load old instance DB's

BACK STORY: I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers ...
1
vote
1answer
32 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
0
votes
4answers
29 views

SQL Server 2005 database log size increasing drastically?

My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased ...
0
votes
0answers
16 views

Adding a new partition to the partitioned table is skipping the last filegroup

I have created one partitioned table and trying to add a new partition to that. I ran the following scripts to create a new filegroup and add a new partition to the table. But when I run the alter ...
1
vote
2answers
39 views

Loop through all databases and remove all users

We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user ...
1
vote
1answer
27 views

Translating a query from MS Access to SQL Server

I have a legacy query from years ago that was written for an MS Access backend: DELETE category.* FROM category LEFT JOIN product_category ON category.id = product_category.categoryid ...
-3
votes
0answers
39 views

SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb) [on hold]

Now SQL Server 2014 is out and there is lot of excitement regarding SQL Server 2014. How can we compare SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb)?
0
votes
0answers
22 views

Can't connect SQL Server through firewall [duplicate]

I know this has been asked before however I've tried all the solutions presented and it still does not work. I am attempting to connect to a SQL Server 2008 running on a Windows8.1 machine through ...
1
vote
2answers
31 views

Can I get OSQL if I install SQL Server Client Tools?

We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network. We've tried running the .bat scripts from our app server (Win Server 2008) but get the ...
2
votes
1answer
46 views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
-3
votes
0answers
55 views

Fastest way to Restore 50TB DB [closed]

I am trying to find out if there other ideas as what the fastest way is to restore a 50TB DB.
2
votes
2answers
577 views

“Cannot insert the value NULL”, but there is no NULL values!

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
7
votes
1answer
158 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
0
votes
0answers
32 views

SQL query to return a column per date in date range with sum of contract utilisations (value/duration)

My question is similar but more elaborate than another already in stackexchange.com "SQL query to return a column per date in date range?" I have the following data: Customer Start_date ...
2
votes
1answer
26 views

How to search double quotes in fulltext search in sql

I have double quotes in my Keyword. How can I search this in my fulltext search query. I have this query SELECT top 10 K.[KEY], 10, K.[RANK] FROM CONTAINSTABLE(ProductKeywords, Keywords, '("19*") ...
1
vote
1answer
54 views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
-3
votes
0answers
41 views

Querying for commentator repo and poster repo for different values of the comment ID (stored in a table) in SEDE [migrated]

How can I modify the following query SELECT CASE WHEN U.Id = P.OwnerUserId THEN 'Poster' ELSE 'Commenter' END AS [User], U.Id AS [User Link], U.Reputation AS [User Rep] FROM Comments AS C INNER ...
1
vote
1answer
31 views

SQL Server 2008 R2 - Limit viewing on database list

In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS. I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see ...
2
votes
0answers
62 views

Querying for reputation of the commenter and its addressee using SEDE [closed]

How can I modify this Data Explorer query for the case when user of the comment is same as the poster, then the query should look for the tagged user in the comment and print the tagged user ...
0
votes
3answers
73 views

SQL Server: CONVERT From ISO8601 To DateTime Within Query

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a ...
0
votes
2answers
33 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
0
votes
0answers
57 views

How control 27 locations and 27 databases? [closed]

We have 27 locations and 27 databases (all are SQL Server 2012 R2) All of them are the same (same tables,views,...), with different data for each location and separated from each other (in each town) ...
-1
votes
0answers
56 views

CTE Hanging with Larger Data Set

I have a CTE that creates a hierarchy and looks for circular references. The table it searches lookes like this: Parent_IndicatorID | Child_IndicatorID | SortOrder My CTE is the following: ;with ...
1
vote
1answer
196 views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
5
votes
3answers
197 views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
1
vote
0answers
62 views

Phantom insert query

I have a log table which I have to clean up a bit because it is growing very fast. While deleting some historical data I reached count zero for that condition on which I have deleted the entries. 5 ...
0
votes
1answer
32 views

Login failed for user '' reason:The account is disabled sql error

i got the error after change my SQL windows authentication security login set enabled to Disabled . But after that i dont able to reset windows authentication security login please help me ...
2
votes
1answer
53 views

Saving passport information [closed]

For starters: Our company is based in Germany and thus is based on German law. Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored. Now we're ...
1
vote
1answer
58 views

How to solve the error Msg 547, Level 16, State 0, Line 1 [closed]

Please suggest me how to solve the error How to solve the error Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The ...
0
votes
1answer
36 views

How to query transfers for single source to single destination?

This should probably be easier than I'm making it, but I'm scratching my head coming up with an efficient way to query the results I want. I'm working with a table that has a source field and a ...
1
vote
1answer
278 views

Difference between COUNT(ColName) vs COUNT(ALL(ColName))

Both query return the same output: select COUNT(id) from Employee select COUNT(All(id)) from Employee So what is the difference in SQL-Server 2008R2?
0
votes
1answer
60 views

How to insert default values to a table with not null columns?

I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required). I would like to write a query that inserts a row with ...
1
vote
2answers
61 views

SQL Server hardware configuration: Best Practices [closed]

I inherited a relatively new database server that is light on hard drives. Currently, everything - OS, SQL Server, hosted databases, etc. - is running on a single partition in a two-drive RAID 1 ...
2
votes
1answer
40 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
1
vote
1answer
51 views

Find the SQL query that caused an event in the past

I have a database in which I noticed that one of my tables is missing. I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it ...
1
vote
1answer
72 views

How to aggregate pairs of id values into groups

I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example ...
0
votes
1answer
26 views

How to retrieve the Capture Instance for a given table?

Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled. I am trying ...
2
votes
3answers
35 views

Steps after restoring to a different server

We have a 30 GB Database on SQL Server 2008 R2 Standard Edition. Yesterday we backed up the database and restored it to a different Virtual Machine which has exactly the same specs as the previous ...
0
votes
3answers
45 views

Can we restrict the number of connections to a database in SQL Server?

I have a database on SQL Server. My web application contacts this database. Is there a way in which I can restrict the number of connections to this database through some settings on the SQL Server?
0
votes
0answers
21 views

Database migrated to SQL Server 2005 with 80 compatibiltiy from SQL Server 2000 experiencing locks

I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists. The code, while not great to maintain, ...
3
votes
2answers
141 views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
0
votes
1answer
36 views

Nightly reload of tables from a linked server

I have a number of tables that need to be reloaded nightly from a linked server. My current method of doing this is to create a nightly job to pull the data using something like the following: ...
0
votes
1answer
33 views

How to store chains of information?

I am currently trying to design a table structure that can capture report data (from various sources) in an ordered fashion. Let me give you an example: Each day I receive three separate reports ...
3
votes
2answers
112 views

Enhance performance of update query

I have a query that is running against a table with 50 million rows. The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values. We are seeing ...
1
vote
1answer
73 views

Take database backup of a SQL server 2012 from local SQL Server 2008 R2

I have a SQL Server 2012 installation that has some issues with SQL Server Agent because of which I cannot schedule a job or a maintenance plan to take backup. What I am trying to do is to create a ...
1
vote
2answers
74 views

Assign maximum memory limit for a particular database in sql server

In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like ...
1
vote
0answers
37 views

1 SQL Instance on 1 box with multiple databases or multiple SQL instances on 1 box [closed]

I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organisation. I have identified the main databases that are mission critical to the ...