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)

2
votes
3answers
1k 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
164 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
3k 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
725 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 ...
23
votes
5answers
5k 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
8k 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
2answers
664 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 ...
7
votes
1answer
1k 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 ...
2
votes
2answers
5k 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
570 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
597 views

List Prepared Statements

Anybody know how I can list the prepared statements that are in a SQL Server instance?
1
vote
1answer
136 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
580 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
924 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
504 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
1k 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
303 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
277 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
150 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
114 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
3answers
1k 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
2k 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
353 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
3k 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
242 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
437 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
875 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 ...
4
votes
2answers
188 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
121 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 ...
13
votes
2answers
2k 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 ...
7
votes
2answers
890 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
150 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
298 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
342 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
1k 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
760 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
74 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
1k 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
2answers
9k 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
328 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 ...
7
votes
1answer
3k 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 ...
-3
votes
1answer
1k views

Single query to copy data from 3 tables into a single empty table

In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table. I started saying "I will use a temp table or table variables", ...
2
votes
2answers
536 views

SQL Server 2008 R2 Dormant Users

I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is ...
8
votes
2answers
845 views

Sql server 2008 filestream maximum number of rows

We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 ...
2
votes
4answers
214 views

How does the database decide which Index to use

DDL create table t ( id int, id1 int ) create index Example_Index On t(id,id1) create index Example1_Index On t(id1,id) DML insert into t(id, id1)values(1, 100) insert into t(id, ...
1
vote
0answers
171 views

SQL Server Service seems stuck [closed]

I'm running a stored procedure and it just doesn't finish. Here are a couple of things I have noticed: Normally, the procedure takes 20mins but now it's running for over an hour by now. Normally, ...