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)

2
votes
1answer
140 views

Change SQL Server 2008 name

I want to change my SQL Server's name. Currently I am connecting to SERVER1\SQLSERVER. SERVER1 is the machine name. I want to change it so I could connect to SERVER2\SQLSERVER and keep SERVER1 as the ...
2
votes
1answer
62 views

Changing subscription expire days

When I go to publication properties and General Tab in that. I see an option Subscription expire and may be dropped if not synchronized in the following number of days. Replication meta data is kept ...
17
votes
4answers
34k views

How export a sql server 2008 diagram to PDF filetype?

I want to have an export from my database diagram to PDF or image types. How can I do this? I worked with SQL Server 2008 R2.
3
votes
1answer
597 views

SSD for production database

I am looking for a solution to [dramatically] improve an enterprise database performance on a system that needs to support thousands of transactions (inserts & large selects) per second. Besides ...
4
votes
3answers
1k views

Identify applications with locks

In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2. Many users access this database via some applications, that sometimes "forget" to ...
0
votes
1answer
126 views

Assign most elevated permission to Sql Server DB

I am using SQL server 2008 R2, on my development machine. I had to delete the old windows user account, with which I had installed SQL Server. I have created a new admin account. When doing any DB ...
3
votes
1answer
467 views

Rolling Back DB Changes After Deployment Edge Cases

I manage a mixed DB environment and our programmers could use some good rollback options. I was thinking of using SQL Compare, Red Gate Source Control, and a few other tools but I can't figure out ...
2
votes
2answers
545 views

Inserting data to a table which is being cleaned up by SQL job at the same time

Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table. DELETE FROM MyTable ...
3
votes
1answer
773 views

Isolation and atomiticy of SQL Server UPDATE

Is there any difference in atomicity and isolation between the two following SQL statements? Batch A: UPDATE Orders SET InProgress = 1 WHERE Completed = 0 and Batch B: UPDATE Orders SET ...
4
votes
2answers
2k views

Optimistic concurrency check failure when updating linked server table with timestamp column

I’m trying to update a table in a database on a linked server using code like this: declare @FirstName nvarchar(30); declare @LastName nvarchar(50); declare @AccountNumber nvarchar(30); set ...
0
votes
1answer
677 views

Get ticket sales and reservations grouped by time slot

Ok, I am definitely no guru when it comes to SQL, I will preface this by saying that. Also, hopefully the info I post will be enough for those out there to figure out what I need to do. That being ...
3
votes
2answers
494 views

Is there a SQL Server 2008 R2 specific target for SSDT projects?

I am making a SQLCLR stored procedure in Visual Studio 2012. I have these options for target platform: Is there not a SQL Server 2008 R2 specific option? Is this because SQL 2008 R2 shares the same ...
5
votes
1answer
1k views

Query in procedure occasionally does not finish

I have a question about how to debug very strange behavior (nested query getting stuck) in SQL Server 2008 R2 (Standard Edition) running a query. Sometimes (unfortunately not always) when I run a ...
7
votes
2answers
3k views

SSRS appears to be ignoring Permissions set using Report Manager

I have setup SSRS on SQL Server 2008 in native mode. As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports. I have also ...
2
votes
2answers
631 views

Using data from other database to populate a new database

I have a SQL Server database DB1 and want to move data from DB1 to DB2. But only want some of the data to be moved. I want all the rows from DB1 that have a catParent that is the same as I Id from ...
27
votes
1answer
2k views

Cast to date is sargable but is it a good idea?

In SQL Server 2008 the date datatype was added. In this Connect item you can see that casting a datetime column to date is sargable and may use an index on the datetime column. select * from T where ...
3
votes
1answer
1k views

Cannot delete publication

When attempting to delete a publication, I am getting the error cannot drop publication 'XX' cannot drop article 'XX'. The subscription has been deleted at the subscriber. Any ideas on how I can ...
2
votes
1answer
1k views

SQL Server - storing ulong as primary key

I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as ...
2
votes
2answers
189 views

SQL Server: reset all database tables

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to ...
3
votes
1answer
117 views

Dissecting Joins

Recently I asked a question in dba.stackexchange the question was about data hierarchy in sql and a guy named chris allen answered the question and I really loved it but when I try to understand that ...
1
vote
2answers
321 views

Select Into a table on server1 from server2 without using 'Linked Servers'

I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers. Any help would be appreciated. Thanks
2
votes
0answers
1k views

How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
4
votes
4answers
20k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
2
votes
0answers
66 views

What is correct process to physically move an availability replica to another location

Current situation is we have 3 SQL Servers with one availablity group and we plan on moving one of the secondary replicas out of HQ to another location. There will be the span of a few hours between ...
1
vote
1answer
92 views

SQL Server 2008 Mirroring

How would one start the secondary mirror instance if the principal SQL server instance went off line?
4
votes
1answer
223 views

What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this?

I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following ...
0
votes
1answer
480 views

Installation of SQL Server 2012 does not contain Virtual Interface Adapter?

I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter. What is the cause of this?
-1
votes
1answer
74 views

Total of Same Customer ID from a Single DB Table [closed]

I have a table with multiple customers. Each customer has a unique ID. Example, only one customer has ID as CustomerID = 100. The customer can be entered into the table numerous times but the ...
2
votes
2answers
315 views

Database Is In InRecovery Mode.

I have a database that has entered "Recovery Mode". It was working fine, but ended up in this status a few days ago. I have been told that a cause can be the partition holding the datafiles running ...
1
vote
0answers
234 views

How to model Week Commencing / Week Ending scenario

I’m reasonably new to data warehousing and I’m struggling to get a feature of my time dimension working. I’d like to model a Week Commencing and Week Ending type of scenario. I’ve created a DimTime ...
1
vote
2answers
127 views

Who changed the size of the transaction log?

Can I know, who and when changed size of database transation log?
3
votes
1answer
163 views

SQL Database automatic back up

I have an SQL database - I need to back it up everyday (at a minimum) - preferably multiple times a day - automatically so it definitely gets done. I'm running Windows which the SQL database is ...
7
votes
2answers
11k views

Restore database from backup file of different version / edition

I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons. Does anyone know off hand if ...
5
votes
1answer
1k views

Slow SQL Server query using a DB2 linked server in virtual environment

I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue: Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 ...
0
votes
1answer
1k views

Downloading image data type contain to desktop SQL Server 2008

I am very new to SQL Server 2008 and question may look very basic to gurus. Following in the problem universe. I have a huge (600 GB size) SQL Server 2008 table that stores pdf files in a column ...
2
votes
3answers
22k views

Non-Clustered-Index on a temporary table

I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is ...
3
votes
1answer
1k views

Index Key Column Order

I have created a joining table for many-to-many relationship. The table only has 2 cols in it, ticketid and groupid typical data would be groupid ticketid 20 56 20 87 20 ...
12
votes
3answers
7k views

Is there a way to determine if SQL Server queries are running in memory or going to disk?

I came across a set of stored procedures in an application today that are called repeatedly within a long running process. Within each procedure I found multiple different select statements, some ...
2
votes
2answers
228 views

One database per company for “security” reasons [duplicate]

Possible Duplicate: Is that a good idea to use one database for 50.000+ shops? We are developing an internet application in C# with MVC 4. It's connected to a database which will contain a ...
4
votes
2answers
6k views

SQL Server replication subscriptions marked as inactive

Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever? It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription ...
6
votes
1answer
1k views

Is it ALWAYS possible to avoid cursor?

I'm looking for any example where using a cursor is unavoidable. I'm pretty sure there are some rare cases like that, but I'm unable to come up with any scenario. Any help would be wonderful. Thanks!
1
vote
1answer
57 views

when can AG primary replica be a different host to WFC primary?

To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster. I've seen cases after certain failover modes where the primary IP address in the cluster ...
-1
votes
1answer
614 views

Is it possible to create a trigger on a table synonym in SQL Server? [closed]

The title says (asks) it all. Is it possible to create a trigger on a table synonym in SQL Server (2005 or higher)?
2
votes
1answer
1k views

Disadvantages of reorganizing all tables nightly

I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too ...
2
votes
1answer
2k views

Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed

I've got this sequence of error messages showing up regularly in my server error log: Error: 9245, Severity: 16, State: 1. During the last time interval 257 query notification errors were ...
3
votes
2answers
689 views

Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related. E.g. Query 1 runs for a few seconds Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and ...
8
votes
3answers
13k views

SQL update satement taking a very long time / high disk usage for hours

Yes it sounds like a very generic issue, but I have not been able to narrow it down very much yet. So I have an UPDATE statement in a sql batch file: UPDATE A SET A.X = B.X FROM A JOIN B ON A.B_ID = ...
8
votes
2answers
179 views

What is the effect of replacing indexes with filtered (non-null value) indexes?

Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I ...
1
vote
2answers
6k views

Getting SQL Server query history

I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ...
7
votes
4answers
1k views

Returning rows where all non-NULL values are equal

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" - effective I want to say: WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL this ...