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
3answers
6k views

Is replication possible on two different domains?

I am implementing a database replication between different geograhical locations where each one is on a different domain. After going through all the process, when I start the agent it runs and stops. ...
1
vote
1answer
4k views

SQL Server 2012 Service Master Key Regenerate

I have a new SQL Server 2012 instance and when I run the following command I get an error. ALTER SERVICE MASTER KEY REGENERATE Error: Msg 15329, Level 16, State 1, Line 1 The current master ...
0
votes
3answers
221 views

SQL Server Facets: “Linked Server” exist but “Registered Server” doesn't - why?

I'd like to monitor multiple SQL Server instances. Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using ...
2
votes
1answer
415 views

Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service?

Reading and re-reading MSDN "Select an Account for the SQL Server Agent Service". NT Service\SQLSERVERAGENT is not listed in the "Supported Service Account Types" section. And, if not, what is it ...
4
votes
1answer
1k views

Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

SQL Server 2012 R2 on Windows Server 2008R2 Is there any way to send notifications to different Emails: on success of an Sql Server Agent job to one Email address and on failure - to another ? ...
0
votes
1answer
645 views

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

As an example, let's say I have a fact table with two dimensions and one measure FactMoney table ProjectKey int PersonKey int CashAmount money The two dimensions are defined like this: ...
1
vote
2answers
588 views

SP or query to dump “password policy” checkbox for all logins

sp_helplogins doesn't show a column for this. I need a listing of all logins, with the status of their "Enforce password policy" checkbox. This is for an auditor, and the standard practice is for ...
0
votes
1answer
76 views

Compiled version of stored procedure

If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view? Is there a visual representation available?
2
votes
2answers
171 views

Best way to explain the importance of Raid 10 to SysAdmins [closed]

Hi I'm trying to impress upon a group of administrators as to why the extra money should be spent on raid 10 vs say raid 5. Other then just pointing at MS Best Practices does anyone know of any good ...
7
votes
3answers
7k views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
1
vote
0answers
223 views

remote query bloats tempdb [closed]

I have someone who retrieves data from a linked server to a new table as following: * newly edited - turns out there's multiple queries executed * select * into dbDestination.dbo.tableDestination ...
1
vote
0answers
647 views

SQL Server FileTables (File Stream) file Corruption

We're running an implementation of SQL Server 2012 FileTables with several scientific instruments with PCs that write data directly to a mapped drive of a FileTables share. The size of this share is ...
0
votes
1answer
679 views

How i can create backup script from diagram in SQL Server?

I use SQL Server 2012 and SQL Server 2008 R2. I create a script from all object (tables / trigger / stored procedure / function ...) in my database. I generated this script from SQL Server ...
0
votes
0answers
74 views

All databases restoring in Suspect Mode in SQL Server 2012

I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in ...
1
vote
1answer
715 views

Approaches for Real time copy of database on same SQL Server instance

I have a draft of requirements that look like this: Production database should be copied in real-time to "Pre-prod" database The Pre-prod database should be available to read and update It should be ...
0
votes
1answer
597 views

Setting Audit Events in SQL Server 2012

We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries: select ...
3
votes
2answers
2k views

What's the point in SQL Server Central Management Server?

Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states: "Note Before you execute the following procedures, ...
4
votes
1answer
2k views

How to specify a task in a SQL Server maintenance plan conditionally?

I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp. These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans ...
1
vote
1answer
2k views

How to launch SQL Server Agent job on completion of another one?

An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs. Is there any way to start SSA jobs on completion (or success, or failure) of another one? Currently, I do this on ...
0
votes
2answers
16k views

To Join two tables without using Primary key

Table1: Order; Column Name: Id (PK), SecurutyId, UserId. ** But no foreign key in this table. Table2: Security; Column Name: Id (PK), SecurutyId (FK), Symbol. Can I do That? Select o.SecurityId, ...
0
votes
1answer
213 views

Not exists/Where NOT in

Doing a pie chart in crystal. Items are Inquiries Referrals Followups A referral has to have an inquiry, A followup has to have a referral. I want to show pie chart of referrals to ...
1
vote
2answers
2k views

Foreign key constraint based on conditional column

I'm trying to figure out the best way to handle this situation: I have a table set up as follows: LEARNING CONTEXT ================ LearningContextID - INT - Surrogate Primary Key EntityID ...
3
votes
2answers
2k views

How to transfer database from SQL Server to DB2?

I have a database file on disk called project.mdf. I want to import this into IBM DB2. How do I import this database?
0
votes
0answers
140 views

SQL Server JOIN with COUNT [closed]

I have two tables I'm trying to query across. One contains an entry for every time a user tunes to a particular channel in our middleware. The second is normalized table containing station ...
1
vote
1answer
285 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
21
votes
2answers
5k views

Index not making execution faster, and in some cases is slowing down the query. Why is it so?

I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down. The query to create test ...
2
votes
1answer
546 views

Need to understand output of sqlcmd.exe

I was advised to make another question, from here - http://stackoverflow.com/questions/19674045/need-help-to-make-sqlcmd-run-as-per-requirements My sqlcmd.exe is invoked with a command of the form - ...
2
votes
1answer
932 views

SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component

I have a huge 200MB .SQL file. When I try to open this in SQL server management studio 2008, I get the error -Error HRESULT E_FAIL has been returned from a call to a COM component. What could the ...
3
votes
1answer
126 views

Restoring with replace going incredibly quickly [duplicate]

So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database ...
1
vote
2answers
8k views

Best Possible Solution for Reporting Mirroring or Replication?

Hi I have SQL SERVER 2008R2 Data Centre Edition and I use SSRS and a Vendor application for reporting. I have a Reporting Server where we backup and restore everyday from Live Server to have Data ...
3
votes
1answer
129 views

DBCC show_statistics: density doesn't match what I calculate

I've read in several articles that the density of a column is (1.0 / count(distinct columnname)). I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column ...
7
votes
1answer
19k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
1
vote
1answer
347 views

Product quantity bound to colors and sizes table structures

I've been thinking about how I'll be able to create products with sizes and colors which will match the max quantity of a product (or their total will be the max.quantity ). Let's say I got a product ...
1
vote
0answers
47 views

SQL Server log Size increasing while in Simple recovery mode [duplicate]

Is it possible to locate the active transaction, it was not listed in DBCC opentran, the log file keeps getting bigger. Database is in Simple recovery tried shrinking the database - no luck Also ...
0
votes
1answer
595 views

Multibyte replace not working in SQL Server

Why does one work and the other doesn't? SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI') SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI') My guess would be that in the ...
4
votes
1answer
5k views

What's the impact of running sp_updatestats in production server?

Is it safe to run sp_updatestats on an SQL Server in the production environment? Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs ...
4
votes
1answer
464 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
3
votes
1answer
229 views

join syntax / style performance consideration

Edit: I've added a follow up question based on comments by @ypercube about semantic equivalence and my findings of the query behavior changes when you have a mix of outer/inner nested joins. We ...
2
votes
4answers
367 views

Benefits of droping a temporary table

What are the benefits (if any) of dropping a temporary table in SQL Server? eg: DROP TABLE #temp; Since temporary tables are automatically dropped by the server this statement would seem to be ...
1
vote
1answer
5k views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
1
vote
2answers
2k views

Why would a long running query remain sleeping and with null wait_info

The following query was run on SQL Server 2008 R2 and took over 1 hour to run: select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id It produced 0 rows ...
1
vote
2answers
4k views

Protect SQL Server from hackers

Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me. EXEC sp_readerrorlog 0, 1, ...
6
votes
1answer
434 views

Minimal Logging Conditions in SQL

I have written a script to test out the claims made on this page http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx in the table titled Summarizing Minimal Logging Conditions about ...
1
vote
3answers
701 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
1
vote
1answer
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 ...
8
votes
5answers
13k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | ...
6
votes
2answers
5k views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...
6
votes
3answers
3k views

Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

There is a project for lab. The requirement to save the lab data (raw data and related infomation ) for research for years. So the data must be saved for years, but the data is so big, every raw ...
1
vote
1answer
1k views

DB Mirroring in async/High Performance mode: Available in SQL Server 2008 Standard Edition?

This should be easy, but the MS docs are not clear. The docs: http://msdn.microsoft.com/en-us/library/cc645993%28v=SQL.100%29.aspx Indicate that in Standard edition, the only supported mirroring is ...
2
votes
2answers
673 views

Sql server - Rebuilding Indexes - Does it affect foreign keys?

i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the ...