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)

1
vote
0answers
1k 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 ...
4
votes
1answer
471 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
411 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> ...
0
votes
2answers
1k 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 ...
3
votes
4answers
4k 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
139 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
459 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
78 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
240 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 ...
1
vote
0answers
114 views

How can I generate a usage log? [closed]

I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum ...
2
votes
3answers
187 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
3answers
3k 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
152 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
443 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
129 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
202 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
193 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
166 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 ...
5
votes
3answers
2k 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
56 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
457 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 ...
5
votes
1answer
2k 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
121 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 ...
2
votes
2answers
189 views

Performance effect of indexing foreign keys

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key. There are two tables involved. The first is a ratings table with 2 columns: ...
0
votes
1answer
1k views

How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express

I would like to upgrade my SQL Server 2005 Express instance which is installed using this command: SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine ...
2
votes
2answers
237 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
4
votes
2answers
409 views

How do I turn off ALTER DATABASE script output in VS 2012?

I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project. When I publish my VS project and generate the script to run against my database, VS includes the ...
1
vote
0answers
96 views

Is there a way to generate an execution plan for a stored procedure before executing it?

We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns. A stored procedure exists to correct the invalid records but it ...
1
vote
1answer
123 views

How to achieve this sql server replication scenario?

I want to achieve below illustrated scenario via sql server replication. Client A and Client B are client databases containing same article Table A. This article needs to be published by Client A ...
1
vote
1answer
93 views

Migration of create statement from SQL Server to MySQL

I have a table that have the following table structure CREATE TABLE [dbo].[ASAT]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [ASATID] AS ('ASAT'+right('00000000000000000000' ...
3
votes
3answers
277 views

Restore of logfiles in SQL Server [closed]

I'm trying to write a script which will read a folder containing a full backup and X number of log files. My script fails when it starts the loop to read the log files. The error is cannot convert ...
0
votes
0answers
257 views

How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]

The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
-1
votes
1answer
97 views

data warehouse construction from mdf and ldf file via etl

I have sample data warehouse stored in the form of mdf and ldf format. I want to use any ETL tool to extract data and then load datawarehouse on the specified database with username, password and ...
1
vote
2answers
2k views

How to attach an .mdf file that was not properly detached?

One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to ...
1
vote
0answers
156 views

Job accessing linked server yields error 7391 [closed]

I created a stored procedure which queries data from a linked server to insert into a local table. I tested this stored procedure by directly executing it; it works fine. I have to run this stored ...
3
votes
2answers
378 views

checksum(newid()) as primary key - what happens if a collision occurs?

Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
7
votes
2answers
184 views

Is there any way to prevent the memo structure from being pruned?

We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
1
vote
2answers
213 views

Update my local db based on scripts executed in my server database

I need to create an solution that update my local db version based on changes that was made on my server database. First, I need to check the differences that has exists between these databases and ...
1
vote
2answers
65 views

Tracking down program from Event Viewer

The event log has hundreds of login failed errors for SQL Server. How do you find out the application generating them? There are lots of potential candidates on the server and since it's logging in ...
2
votes
2answers
1k views

Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2

I have a .bak file created by some other user in (I think) SQL Server 2008 R2 (10.50) and I need to restore it in SQL Server R2 SP2 (10.52). I used a hex viewer to look into the .bak file and found ...
1
vote
1answer
445 views

PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster

We a provisioning a new SQL Failover Cluster. We have successfully configured the SQL instance, and have moved on to configuration of the PowerPivot for SharePoint instance. We run the configuration ...
5
votes
1answer
4k views

Start service error code 3417 with SQL Server 2012

I cannot start the SQL Server 2012 service. The event log says the service-specific error code is 3417. In the Event Viewer: The SQL Server (MSSQLSERVER) service terminated with the following ...
3
votes
1answer
47 views

SP with Encryption Read/Writes Stats

If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
0
votes
1answer
206 views

SQL Server TDE - add master key and certificate [closed]

I want to add multiple master database encryption keys and certificates. Can I do that?
4
votes
2answers
1k views

One filegroup, multiple datafiles, how to get list of tables in each file

In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup. How do I identify the location of each of the 50 ...
3
votes
0answers
107 views

custom population of full text index

Is there any way to populate a full text search index without storing the backing information in the database? I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
2answers
216 views

Server permissions of an activation stored procedure of a Server Broker queue

I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on ...
-1
votes
1answer
316 views

Why does fn_my_permissions return empty rowset? [closed]

If I run this query: select * From fn_my_permissions(NULL, 'SERVER') This is what I'm getting: entity_name subentity_name permission_name ------------- --------------- ...
3
votes
2answers
607 views

Is this a good strategy for importing a large amount of data and decomposing as an ETL?

I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
4
votes
1answer
185 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting ...