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
3answers
936 views

Copy database to local from server [duplicate]

As I show , I have server database version-10.50.1600 and local database version-10.0.1600 . How can I copy database (with all tables and data(s)) from server to local which has different version ? I ...
6
votes
3answers
280 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
3
votes
2answers
872 views

Can I open a SQL Server 2008 R2 database in SQL Server 2008 R2 Express?

This question has probably been asked a dozen times but I couldn't find them here or at SO. Can I attach/restore a SQL Server 2008 R2 database to my local SQL 2008 R2 Express instance?
-1
votes
1answer
71 views

validate where each parent intermediary is also a parent to itself [duplicate]

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any. I need to do is first of all get all parents ...
1
vote
3answers
1k views

Table clean up taking too long

I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more ...
1
vote
1answer
108 views

Statistical Analysis of Data that has to be done in an order?

Bear with me - that is the first time try that in SQL Server, normally I have been doing that on the front end ;) I a implementing some analysis on time coded data series. This is not super ...
0
votes
2answers
2k views

Cannot see Green Button with white arrow in object explorer; DTC config problems?

I have SQL Servers across locations setup for replication. One of the SQL Server instances that I installed, running locally I can see the green button with the white arrow in the object explorer when ...
2
votes
1answer
563 views

SQL Server Memory for Backup Buffer

In SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012? EDIT: Does MAX Server Memory ...
6
votes
1answer
968 views

Optimum configuration for 4 SSD drives

I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single ...
2
votes
0answers
89 views

Why does GO affect my SELECT? [closed]

I am experiencing a very weird behaviour from the Management Studio GO. I am following the full-Text Search tutorial and I run the following query over adventure works SELECT FT_TBL....
2
votes
2answers
15k views

How to restore SQL Server database with mirroring

I have SQL Server with mirroring. I am trying to restore the database using the command: USE [master] ALTER DATABASE [CentralStorage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [...
1
vote
1answer
80 views

Is there any way to Restore a Database from newer version to an Older Version of SQL Server? [duplicate]

I am having a database backup file from SQL SERVER 2012 and i wanted to restore it on SQL SERVER 2008R2 . If we can’t restore a SQL Server 2012 database to earlier version what is the next best option?...
0
votes
0answers
345 views

Data deleted from replicated table

I have two servers (one publisher and other is subscriber). The replication is succesfully running for a long time. I am using transactional replication with updateable subscription. Couple of days ...
6
votes
1answer
4k views

Stopping SQL Server Agent: Effect on runnings jobs

On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day. Now and then, the server needs to ...
1
vote
0answers
1k views

Should xp_logevent and RAISERROR … WITH LOG work on LocalDB?

I need to run a script to configure a LocalDB (v11.0.3000) instance and database (from a .NET 4.5 program) and I want to record results at various stages. Someone suggested xp_logevent but it doesn't ...
1
vote
1answer
321 views

How to repair Microsoft.SqlServer.Types assembly

When I run a checkdb('mydb') this is the only error message printed. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3857, State 1: The attribute (clr_name=NULL) is required but is missing for ...
1
vote
2answers
6k views

Linked Servers pointing to same server (localhost) causing “Transaction context in use by another session” error

We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers. Now I ...
5
votes
6answers
16k views

How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY ...
11
votes
2answers
4k views

More CPU cores vs faster disks

I'm part of a small company so as usual covering a number of different roles. The latest of which is procuring a dedicated SQL Server box for our .NET web app. We've been quoted on a dual Xeon E5-2620 ...
5
votes
2answers
543 views

When should tables NOT be compressed

We have two servers. One we just compressed, but I took over this little project and this guy says some tables are not good candidates. As with many others, performance is -critical-. Is it accurate ...
4
votes
2answers
5k views

Combining data from two databases with same structure into one database

I have five SQL Server databases with the same schema in five different geographical locations. These locations send periodic backups to the central server which I restore in the five respective ...
1
vote
2answers
204 views

Migrating a database from SQL Server 2012 to the cloud [closed]

I have a sample SQL Server database which is running on my computer. I have a cloud database account on AWS (free account). I want to migrate the database, with all tables, to the cloud. How can I do ...
-1
votes
0answers
424 views

All users missing after a detach/reattach [closed]

After detaching and reattaching a database to do a copy operation, all of the user objects are now missing from the database (I should add that the copy failed; according to the logs, there was an ...
3
votes
1answer
2k views

Can you grant an Active Directory Group rights on a linked server?

I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to ...
2
votes
1answer
226 views

Backup on an empty SSAS database hangs

I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database. Most databases ...
2
votes
1answer
119 views

Debugging SSAS Timeouts

I have a drill-through problem apparently related to security. Users in one role are seeing timeout failures when trying to invoke a drill-through action. They have permission on the action through ...
1
vote
1answer
4k views

Remove specific duplicates (all but latest)

When removing duplicate rows, using the below query from this tutorial, how would I go about forcing which of the found duplicates to remove? DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (...
11
votes
3answers
2k views

Updating a table with millions of records, its been 4 days

I am currently updating a table with millions of records, its been 4 days and query is still executing. I checked the activity monitor its shows that query is running. In event log there is no ...
1
vote
0answers
5k views

Troubleshooting a SQL Server instance that won't start

I've had a problem with one instance of SQL Server 2008 (the free version) not starting automatically. I always have to right click on the icon in the task bar and start it manually. It is set to "...
7
votes
1answer
7k views

Running total to the previous row

I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the ...
1
vote
2answers
1k views

Remove scientific notation from an XML column

I have a XML column as shown below: <Root> <Row> <Rowid>1</Rowid> <date>2013-05-06</date> <Balance>1.0002E7</Balance> </Row> </...
1
vote
2answers
4k views

Backup SQL Server To NAS

I have seen that there are similar questions like this but I tried all of them and my problem still exist. I want to backup a db. I created a shared folder on NAS I can reach this shared folder ...
6
votes
4answers
23k views

Query without having to specify table's schema

I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename. In the table properties it lists 'erpadmin' as the ...
2
votes
2answers
188 views

Record versioning and promotion

Let's say we have this hierarchy: -World --USA ---WA ----Seattle -----Downtown ------1st Ave -------945 1st ave ------3rd Ave ---...
2
votes
2answers
1k views

Partial rollback doesn't decrement trancount

Suppose I have an open SQL Server session, and do the following: begin tran insert into People (Id) values (1) select @@TRANCOUNT -- Prints 1 save transaction tt begin tran select @@TRANCOUNT --...
2
votes
2answers
128 views

Delete SQL Server logins on replicated server

We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database. However, the SQL logins are not ...
1
vote
2answers
2k views

Where should I look for details about 'access denied' errors?

This question derives from Deploying a new database on a new server, an msbuild ssdt rookie issue. Since I have a permission denied error, I expect to find which user is failing to access so I can ...
2
votes
3answers
525 views

Which is better: one database per application, or just one database? [duplicate]

I ask this specifically for SQL Server, the advantages that I can see for multiple databases are: Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall) A page ...
4
votes
4answers
15k views

SQL Server 2012 installation silently fails

I am trying to install SQL Server 2012 running setup.exe on 64-bit windows 8. After the "please wait" dialog, the installation center comes up. I chose "New SQL Server stand-alone installation", ...
2
votes
4answers
167 views

When should I use the shrink option

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
3
votes
2answers
2k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
-1
votes
4answers
201 views

Dropping and recreating indexes [closed]

I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness). I somehow manage to rebuild ...
6
votes
2answers
261 views

Does an index with multiple columns make a similar index redundant?

Let's say I have a table that looks like this: CREATE TABLE Activity ( ActivityID int primary key identity(1,1) , ActivityName nvarchar(10), InactiveFlag bit ) with an index that looks ...
4
votes
1answer
341 views

How to get a row_number to have the behavior of dense_rank

I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
4
votes
1answer
374 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
12
votes
5answers
7k views

Why do linked servers have a limitation of 10 branches in a CASE expression?

Why does this CASE expression: SELECT CASE column WHEN 'a' THEN '1' WHEN 'b' THEN '2' ... c -> i WHEN 'j' THEN '10' WHEN 'k' THEN '11' END [col] ...
1
vote
1answer
57 views

Generate List of Missing Relationships

I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from ...
2
votes
2answers
934 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
9
votes
1answer
8k views

Troubleshooting SOS_SCHEDULER_YIELD wait

Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems. After performing the same activities in both the development and ...
1
vote
2answers
249 views

Is it possible to remove a database server from a cluster resource, but keep it running on the node

I was wondering if anyone knew whether it was possible to delete the cluster resource, but keep all of the disks and sql instance running on the node instead? So if I was to delete the cluster ...