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
1answer
160 views

SQL Server 2008 search joining query

I have two table as one is the message table and another one is messageUser table. Now i need to check before insert a new row. for example, M_MessageMessageId========= ...
3
votes
3answers
229 views

why would command text not execute at all?

in my report i have:: <CommandText>SELECT column1, column2, 'poop' from mytable </CommandText> i am using sql server profiler to see exactly what statement is ...
8
votes
2answers
1k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
1
vote
0answers
490 views

SSRS Timeline Chart for each day / minute

I'm asked to make a Timeline Bar Chart with SSRS 2012. There should be 1 chart per day/employee, and this chart should have a x-axis of 1440 minutes (or 24 hours). The bar should have 2 different ...
1
vote
1answer
2k views

How to connect Powershell to the SQL Server named instance?

When I run # Load the SMO assembly [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"); $ServerName = "[local\s12]"; #Get a server object which corresponds to the ...
1
vote
0answers
540 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
3
votes
1answer
555 views

Unable to run union in parallel on SQL Server

I am unable to get this fairly simple query to parallelize the union operation: select va.ObjectId, 0 as IsFlag from Oav.ValueArray va where va.PropertyId = @pPropertyId ...
6
votes
2answers
857 views

Generate script to automate renaming of default constraints

Background: Some of our default column constraints were generated without explicit names, so we get fun names that vary from server to server like: DF__User__TimeZoneIn__5C4D869D I would prefer to ...
7
votes
2answers
2k views

Limit connection rights on SQL Server

I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part ...
2
votes
2answers
229 views

Can the OUTPUT clause create a table?

I'm doing an update like this: UPDATE dbo.Table1 SET BirthDate = b.BirthDate FROM Table1 a JOIN Table2 b ON a.ID = b.ID And I want to use the OUTPUT clause to back up my changes. UPDATE ...
7
votes
3answers
662 views

SQL Server Sandbox

I'm attempting to set up a sandbox for our report developers to their work in. My current plan is to "reset" the database every evening but I'm not sure how to go about doing so. What I mean by reset ...
1
vote
1answer
1k views

SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?

I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help. -------Edit - Answered------- This ...
1
vote
1answer
885 views

Error creating scalar function “Invalid Object Name dbo.fnMyFunctionName'”

I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions. When I try to modify function, function name is ...
1
vote
1answer
114 views

Does the SQL Server Backup Database Command also backup views from a Database?

I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
0
votes
1answer
123 views

Database is read-only

I got this error. How can I resolve it? Failed to update database "msdb" because the database is read-only. Could not insert a backup or restore history/detail record in the msdb database. This ...
6
votes
3answers
198 views

Primary Key efficiency

If I need a table to hold Point of Sales transactions, and am told I need to store: Country ID Store Number POS Terminal Number Transaction Date Item Code Teller ID Another Field More Fields Now, ...
3
votes
6answers
4k views

SQL Server split mdf into multiple files

I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and ...
2
votes
1answer
607 views

Even when user has 'bulkadmin' role, query says user does not have role - SQL Server 2012

I am facing a weird issue with SQL Server 2012. I have a user (say RS\sqluser) which has bulkadmin role. Now when i run this query Select IS_SRVROLEMEMBER('bulkadmin', 'RS\sqluser') output is 0. ...
14
votes
4answers
3k views

Best solution to fixing database design with GUID as primary key

I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions. I have a very large database (20+ million ...
5
votes
1answer
241 views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
2
votes
4answers
177 views

Using Mirroring for incremental backups?

We have a machine tool application that runs on a SQL server backend. These tools run all day every day and I am looking for a way to incrementally backup the database without any machine downtime. ...
3
votes
2answers
427 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
0
votes
2answers
372 views

SQL Server sync databases between enterprise - web edition

We have an enterprise version of sql server 2012. we need to synchronize its data to a web edition database. what would be the best option t do this ? further to add on to this we need to provide ...
-1
votes
1answer
4k views

ALTER DATABASE command to set log filegrowth and size?

Is it possible to alter the LOG file through script when creating a database? I need to set the file size, growth etc, but NOT specify where the DB is to be created. e.g. CREATE DATABASE ...
1
vote
1answer
2k views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
1
vote
2answers
408 views

sql server-percentage calculation

Sample data : LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count 23053B 00000 00000 00000 S SUMMIT WER 43 23053B 00000 00000 00000 T ...
0
votes
2answers
165 views

When Creating a Foreign Key Relationship

When creating a primary/foreign key relationship there are the following options: Check Existing Data On Creation [Yes/No] Enforce for Replication [Yes/No] Enforce Foreign Key Constraint [Yes/No] ...
13
votes
6answers
8k views

Optimal drive configuration for SQL Server 2008R2

I have a fairly busy database server running SQL Server 2008 R2 that has the following setup: SATA RAID 1 (2 Drives) - OS / Programs SAS RAID 10 (4 Drives) - Sql Database Files (data and logs) SAS ...
1
vote
0answers
431 views

sp_testlinkedserver taking long time to time-out

I'm using the following code to check if a linked server is available before running queries on it: declare @srvr nvarchar(128), @retval int; set @srvr = 'my_linked_srvr'; begin try exec @retval ...
3
votes
1answer
672 views

SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning

I want to validate the optimal performance of our SQL Server and in doing so, I have gone through all the performance counters listed at the following link and create a trace log to analyze the data. ...
4
votes
1answer
157 views

How can I rename an auto-named constraint when I get this error 15248?

Method: Open SSMS. Find table. Open constraints. Find constraint name. Right click, rename. Give new name. EXPLODE. (well, it just fails, at least)
4
votes
1answer
226 views

Item Index for all items in nested XML query

I have a requirement to build an XML package with a parent record, and 1..N related child records. The child records are to be a nodes under the parent node, and there may be one child record or ...
5
votes
2answers
112 views

Retrieving cached plans fails due to cached objects of database Mirror

I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state. The below query, without any ...
3
votes
1answer
912 views

Deadlocks on same wait resource

I am experiencing a deadlock in SQL Server, but I do not understand why this is a deadlock. It looks like the two resources are actually the same. Meaning that they each have the lock and are asking ...
0
votes
1answer
635 views

SQL Server 2008 SP2 randomly slow down query

I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query. It is taking 4 sec to 4 min in execution. Please suggest the possibilities. There is no scope of query optimization. ...
2
votes
2answers
103 views

How do I write backup procedure

I've read that a decent backup strategy would be: 1) Full backup weekly 2) Differential backups daily 3) Log backups every 5 minutes. Now question is how do I write this stored procedure and create ...
2
votes
3answers
3k views

When to periodically RESTART SQL SERVER 2008 R2? [closed]

So I've read that it's really a bad practice to restart SQL Server on a production environment.. Does restarting SQL Server speed it up? Automatically restart SQL Server 2008 Why are periodic ...
0
votes
2answers
201 views

How to make a copy from a Emergency Database?

I've a database on a very old Computer (Windows xp, SQL server 8.0). Yesterday, the database has been marked as suspected, I've put it to the emergency state. So now the database is Read-Only. The ...
0
votes
1answer
702 views

Cant start SQL services

So I am having issues starting my SQL services. Currently it is failing the start due to: FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred ...
0
votes
2answers
972 views

SQL Server 2008 Restore

I'm trying to restore from a database backup to SQL Server 2008. The backup came from a different server. I'm executing the following query: RESTORE DATABASE Database_Name FROM DISK = ...
4
votes
3answers
1k views

Is it ever harmful to set a database owner in SQL Server?

There are a bunch of databases on one of our SQL servers that have no owner. Generally speaking, is it harmful to give them one? For example: USE dbName; GO EXEC sp_changedbowner 'sa'; GO I know ...
2
votes
1answer
562 views

sysadmin cannot drop login when ddl audit trigger enabled

I have a DDL trigger defined for database- and server-level events in SQL Server 2008R2 (Standard) which exists to log all DDL events into an audit table in an admin database. It's only function is ...
3
votes
1answer
2k views

Create Temp Table From Stored Procedure Result

Is it possible to create a temp table from a stored procedure result set without having to specify column datatypes? If not, is it possible to create a physical table at least from the SP? The reason ...
4
votes
2answers
353 views

Update strings for all cmdexec Jobs

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server? We'd like to change our logging location, and in principle I'd think this could be ...
0
votes
3answers
771 views

Merge Replication identity field issues

One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
2
votes
1answer
673 views

Execute sp_WhoIsActive as a query

I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?
0
votes
1answer
186 views

READ_COMMITTED_SNAPSHOT support in SQL Server 2005 Express

I wonder if SSE 2005 supports the READ_COMMITTED_SNAPSHOT database option. What I found is this article: Features Supported by the Editions of SQL Server 2005. It states that SSE 2005 supports ...
0
votes
0answers
121 views

SQL Server in a state of confusion: Does the database exist or doesn't it? [duplicate]

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
6
votes
5answers
3k views

SQL Server in a state of confusion: does the database exist or doesn't it?

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
2
votes
1answer
504 views

Best practice around sys.dm_db_missing_index_group_stats in SQL Server

I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which ...