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)

5
votes
2answers
852 views

Insert custom header row in BCP output

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an ...
5
votes
2answers
336 views

Copy data from multiple databases to a central database for reporting and analysis

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting ...
4
votes
2answers
3k views

Can I optimize this MERGE statement?

I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows. In my development environment, the query ...
4
votes
1answer
404 views

How do I create a partition using a bit field in SQL Server 2014

I have a table where data is archived. I need to keep this data as it is rarely viewed. Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When ...
4
votes
2answers
2k views

TdsInputBufferError not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers

I've got an issue with SQL Server. The value of TdsInputBufferError is not 0 in LoginTimers errors of RING_BUFFER_CONNECTIVITY of sys.dm_os_ring_buffers. This happens randomly, once or twice a week, ...
4
votes
1answer
4k views

Storing Japanese characters in a table

I am using SQL Server 2008 R2 and I want to store Japanese characters in one of the columns of my table. Say I want to store Japanese names, how can I achieve that? Is there an easy way?
4
votes
1answer
1k views

Running functions in parallel

This is for SQL Server 2012. We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
4
votes
1answer
364 views

Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?

(With apologies in advance since I've been asked to pick up a project written by someone with more skills than me but who I can no longer ping for questions. If it sounds like I may be using ...
4
votes
1answer
832 views

Understanding SQL Server encryption (by password)?

After asking this I still have a question. A company need to store sensitive data. Please let's not talk about certificate right now. A DBA does the following : CREATE SYMMETRIC KEY ...
4
votes
3answers
137 views

Collect data with central management servers and keep them in local table

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository. I could open a query and get result for each one at the same time. But ...
4
votes
2answers
633 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
4
votes
1answer
3k views

Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering

We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the ...
4
votes
3answers
8k views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
4
votes
2answers
238 views

BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands: ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL; or BACKUP LOG ...
3
votes
2answers
904 views

How to identify unused databases on my sql server

I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server. I went through many articles, which say possibly to go for ...
3
votes
2answers
1k views

Is it possible to get schema ID in another database without using dynamic sql?

I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic ...
3
votes
2answers
177 views

Copy large table to archive table SQL Server 2012

I have a table with logs of my application that have 161.626.718 rows (and growing) and it's very slow to make a insert on it. My idea is to keep only 6 months of log in the table and all older ...
3
votes
1answer
2k views

Biggest wait WriteLog

I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where ...
3
votes
2answers
1k views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
3
votes
4answers
2k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
3
votes
2answers
6k views

App domain is marked for unload due to memory pressure

In my sql server log (2005 SP4) I have this message every couple of seconds : "AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure" Our server : Windows Server ...
3
votes
2answers
745 views

Help with a multi-language medical database

My project is to design a database for medicines in many countries. Medicines have various criteria like composition, price, manufacturer. Database will be multi-language; related to each country. ...
3
votes
2answers
6k views

What are the limitations of Toad for SQL Server freeware? [closed]

I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial ...
3
votes
2answers
2k views

Export as csv or xsl file from SQL Server 2008 express R2

I am basically LAMP developer got some assignment where SQL Server is required. I have installed SQL Server 2008 R2 Express and imported backup file in it. Now, I want to export user table in excel ...
3
votes
1answer
3k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
3
votes
2answers
4k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
3
votes
4answers
339 views

PRIMARY KEY CONSTRAINT fundamentals

Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ? The RDBMS that is used is SQL Server. In fact, ...
3
votes
2answers
4k views

What's the owner of SqlServerAgent job for?

What is the possible use of owner of an SQL Server Agent job, I wonder? Removed, and left it blank and the job still succeeds
3
votes
1answer
3k views

CREATE DATABASE permission denied for sysadmin

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User ...
2
votes
2answers
7k views

Restore Database script

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
2
votes
1answer
2k 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
62 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
2
votes
1answer
8k views

SQL query to convert columns into rows

I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005. my input Data ONAY_ID sip_RECno KULLANICI ONAY 1 1 ...
2
votes
3answers
857 views

How to merge data sets without including redundant rows?

I think this must be a fairly common thing to do, but I haven't been able to find an example. It involves merging data that involves dates/times (or more generally any sequential values) without ...
1
vote
3answers
8k views

Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired

I am creating Clustered Column Store Index in SQL Server 2014. I am getting error as "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding....
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, '...
1
vote
2answers
8k 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 ...
1
vote
2answers
1k views

“In Use” memory on a SQL Server box

I have a SQL Server 2012 database server with 64GB of RAM. A process called DataPumpService runs on the same box and loads around 400 million records from 2700 flat files. I want to understand what ...
1
vote
1answer
624 views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...
0
votes
3answers
618 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
-6
votes
3answers
3k views

SQL Server Express with 20000 databases won't start [closed]

I have a SQL Server Express instance with 20,000 databases (twenty thousand databases).. and the service won't start. I get error message 1053: The service did not respond to the start or ...
18
votes
1answer
443 views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
18
votes
2answers
81k views

How do you grant execute permission for a single stored procedure?

Normally when I create a stored procedure I use the following as a template of sort Create procedure <procedurename> <@param1 <type>, @param2 <type>, etc..> as begin <...
17
votes
3answers
36k views

Can I use SQL Server Management Studio 2012 on a 2008 server?

My school is using SQL Server 2008. Last time I used it I only installed (at home) SQL Server Management Studio 2008 part, and used VPN to connect to school's server. Now I want to install SQL Server ...
17
votes
4answers
17k views

What is the best query to use to monitor a SQL Server database's status?

I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state. This is the ...
16
votes
1answer
704 views

Restore Database using GUI - Wrong file to restore

I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task. One thing that I noticed is when I click on "generate script", the first line of the query is: ...
15
votes
4answers
34k views

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
14
votes
3answers
171 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question isn'...
14
votes
2answers
406 views

LOB_DATA, slow table scans, and some I/O questions

I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete ...
13
votes
5answers
7k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...