Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

15
votes
5answers
6k views

What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I'm in a situation where I want to get the minimum value from of 6 columns. I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
11
votes
3answers
653 views

Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

We have a large table [MyTable] which currently has both a PK, and a UNIQUE Non Clustered Index on [KeyColumn] (which has additional covering columns), which seems redundant, and I think I can delete ...
2
votes
0answers
96 views

Are there any best practices before moving SQL Server to another node (controlled failover)?

Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there ...
1
vote
1answer
2k views

Restoring an SQL Server 2012 MDF to SQL Server 2008 [duplicate]

Possible Duplicate: Restore SQL Server 2012 backup to a SQL Server 2008 database? I have created a database on SQL server 2012 with compatibility mode for SQL server 2008 (100). When I do a ...
0
votes
1answer
372 views

Where is sp_MSrepl_getpublisherinfo in SQL Server?

In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find ...
2
votes
3answers
885 views

Transactional Replication snapshot locking Publisher db from beginning

I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we ...
2
votes
1answer
74 views

Need a solution on dataset as given below

Data Set: col1,col2,col3,col4.col5 (A,b,c,d,e) (A,z,p,q,r) (F,s,t,y,v) (F,w,x,y,z) (F,p,x,r,t) (G,m,n,o,p) (G,m,i,q,s) Actually I need result set that has all the columns and only single row for ...
1
vote
1answer
135 views

Follow up to certificate sharing question

Follow up to the post Sharing certificates encryped by password between DBs and instances I created a certificate with a password CREATE CERTIFICATE testcert ENCRYPTION BY PASSWORD = 'test123' ...
2
votes
6answers
1k views

Taking backup of all the databases in SQL Server

I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I ...
3
votes
2answers
518 views

Sharing certificates encryped by password between DBs and instances

Question: I am trying to create a single (self signed) certificate and deploy across many DB's and instances. I create and backup the cert with: USE MASTER CREATE CERTIFICATE DavesCert ENCRYPTION ...
20
votes
4answers
3k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
3
votes
1answer
3k views

Disable autocommit for SQL Server connection in Oracle SQL Developer

I am using Oracle's SQL Developer to execute queries against my SQL Server 2008 database as I simply can't work with SSMS as long as it doesn't support Ctrl + Return to execute statements... Anyway, ...
7
votes
2answers
6k views

Downgrading SQL Server 2012 to Lower Edition

I have a Win2008 R2 server with an enterprise edition of SQL Server 2012 installed. Because my production server is going to be standard edition, is there a way to downgrade editions from enterprise ...
2
votes
1answer
397 views

Relocate all files to folder checkbox unavailable sql server 2012

Using SQL 2012 Enterprise edition Following instructions here for restoring a backup: http://msdn.microsoft.com/en-us/library/ms186390.aspx#Restrictions I need to be able to rename the database ...
6
votes
1answer
853 views

Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?

My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application updates on an ...
1
vote
2answers
3k views

Error Authenticating Proxy Account when Executing SSIS job

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages. These packages have been running for years and the associated steps run via a Proxy Account ...
2
votes
0answers
494 views

I/O is frozen on database databasename

On SQL Server 2005 log report, everyday I receive the following message: I/O is frozen on database databasename. Is it serious ? How to resolve it ?
7
votes
1answer
470 views

List Prepared Statements

Anybody know how I can list the prepared statements that are in a SQL Server instance?
1
vote
1answer
124 views

Default Schema Problems on server

When i connect to SSMS on my server through remote desktop and query my database using: SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID() I am getting the correct results ...
1
vote
1answer
400 views

2 similar queries in large sql server table - 1 takes too long

I have a huge table in my database that contains distances between cities. This enables my application to find nearby cities around the world when a starting city is selected. It contains 4 columns ...
3
votes
1answer
749 views

Data Collection reports fail because login is from an untrusted domain

I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management ...
1
vote
2answers
406 views

SQL Server 2012 Data Warehousing Overall Flow [closed]

I am studying Data Warehousing in SQL Server 2012 I am attempting to learn the various tools included by practicing on the different tools separately. I have practiced with: AdventureWorks ...
1
vote
2answers
801 views

Can't create indexes on really large table!

I have a table with over 300 Million rows in it. When I try to create an index on the table I get the error message below. Msg 1105, Level 17, State 2, Line 3 Could not allocate space for ...
1
vote
0answers
261 views

How To Speed up Merge Replication [closed]

I am running SQL2012 merge replication on a netbook using SQL Express as a subscriber. The netbook has 1GB of RAM. The replication process works quite well, but it is when it comes to applying the ...
4
votes
1answer
216 views

SQL Server Workspace Memory

Does Workspace memory (part of buffer pool) is limited for only Hash Join, Sort etc. only? If I have a query that does use a nested loop join (wihtout a sort or hash), would it not use workspace ...
3
votes
1answer
142 views

Merge replication with PDAs AND P2P replication between servers?

We're currently building a setup to scan tickets at an event. We plan on having 2 servers at each entrance but can't have a local link between the two. We have scanners (sqlCE) and half of them are ...
4
votes
1answer
98 views

repeatable read and nolock

Does anyone know what the net effect of having a block of t-sql with a transaction isolation level of repeatble read and then inside having a select with nolock. Does the nolock become invalid ...
5
votes
2answers
891 views

BUILTIN\Administrators accidently removed

Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. ...
9
votes
3answers
1k views

Used Memory not freed up after a SQL BULK Insert / BCP Export

I have created a very basic SQL table as following CREATE TABLE [dbo].[TickData]( [Date] [varchar](12) NULL, [Time] [varchar](12) NOT NULL, [Symbol] [varchar](12) NOT NULL, [Side] [varchar](2) ...
-1
votes
1answer
302 views

Multi-Tenant Database Schema Creation [closed]

I need to create a Multi-tenant schema. I have searched a lot, but I haven't got any concrete solution to create one. Any example would be highly appreciated. I'm using SQL-SERVER 2008. I need to ...
5
votes
1answer
2k views

SQL Server has encountered 1 occurrence(s) of cachestore flush

On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue? SQL Server has encountered 1 occurrence(s) of ...
0
votes
2answers
138 views

Import data problem in SQL Server 2008

I want to import data from one database to another. On the destination database, I don't have permission to restore a backup file to it, so I have only one option which is to import data. I used the ...
0
votes
3answers
205 views

Is adding rows better then adding columns in case their value is not consistent?

In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
1
vote
1answer
319 views

Error in SQL syntax

SELECT CONCAT_WS(' ',child_fname,child_sname) AS 'Child Name' , child_gender AS 'Gender' , child_dob AS 'DoB' , COUNT activity_name AS 'Activities name' , CONCAT_WS(' ', ...
2
votes
1answer
519 views

Good articles for SQL Server Health Check specifically for Web Applications

I am searching for Database Health check articles specifically for a web application back-end server and SQL Server 2005/2008. My current checklist includes: - Index Fragmentation - SQL Server ...
3
votes
2answers
169 views

Databases entering “In recovery” mode on restore

I am facing an issue while restoring the database. I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore ...
2
votes
1answer
115 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
12
votes
2answers
1k views

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT(B.foo) FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table ...
6
votes
2answers
708 views

When do I need to backup the Service Master Key?

I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the ...
4
votes
1answer
148 views

Always being logged in as SA and not Windows account

First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server. I have recently started a project working with SQL Server 2008 R2. I have access to the database ...
6
votes
1answer
215 views

What would be a good name for a job schedule?

I have encountered many different names for job schedules in SQL Server, ranging from the job name where it is used to a short recap of the schedule itself. I would like to give a clear, descriptive ...
2
votes
0answers
303 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
2
votes
1answer
892 views

SQL Server 2005 Tools makes SQL Server 2008 Express install fail

When I tried installing SQL Server 2008 Express, the install failed due to this line. How can I circumvent this, or fix the issue?
6
votes
1answer
574 views

Service Broker: Queue Monitor is dropped after poison message

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event ...
5
votes
2answers
2k views

Column definition for a SQL Server equivalent to Access BOOLEAN type

The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for ...
3
votes
1answer
68 views

Is it possible to enlist a TSX to an MSX in a different domain?

In Domain1, I have an MSX with 75+ TSXes registered. Now I've been given some SQL Server instances in another domain. Is it possible to enlist Domain2 instances to an MSX in Domain1 ?
2
votes
3answers
732 views

How can you save all user-defined functions to txt files on your local hard drive?

I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved ...
5
votes
1answer
6k views

How can I strip non-numeric characters out of a string?

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One ...
3
votes
1answer
207 views

Connecting Linked Server

Hi I want to fetch data from MS SQL DB of a Remote server using Linked Servers. I have linked the servers. I am fetching data as follows. select * from [IP of server].[Database Name].[dbo].[Table ...
5
votes
1answer
2k views

Should I add SET NOCOUNT ON to all my triggers?

It is fairly common knowledge that you should have SET NOCOUNT ON by default when creating new stored procedures. Microsoft has changed the default template to include this in 2012. I thought ...