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

Service Broker stops working [closed]

I'm new to Service Broker. Our existing Service Broker suddenly stops working and I can't figure out why. When I try to enable Service Broker (which is already enabled) I get this in SQL error log: ...
1
vote
1answer
7k views

Convert a date to yyyymmdd format

Which SQL command is recommended to convert a date to yyyymmdd format? convert(varchar(8), getdate(), 112); or convert(varchar, getdate(), 112) I notice that if I use the second one, it will ...
3
votes
1answer
15k views

Get the 2nd or 3rd occurrence of a value in a delimited string

I have the following table: ========================================================== | Name_Level_Class_Section | Phone Num | ...
5
votes
3answers
1k views

How to reduce network latency during mirroring?

We are using SQL Server 2008 R2 Standard edition for mirroring. But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. ...
-3
votes
1answer
228 views

Microsoft Assessment and Planning Toolkit for Windows server 2003 [closed]

Can anyone please tell me where I can find Microsoft Assessment and Planning Toolkit for Windows server 2003? I tried using version 8.0 but it fails to install on Windows server 2003. thanks
3
votes
2answers
2k views

Is there a fast way to change column type without dropping the clustered index?

I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate ...
2
votes
0answers
45 views

How to remove leaf member values?

I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy. When you ...
4
votes
1answer
847 views

CTE memory space [closed]

How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query? I have a CTE that is ...
2
votes
1answer
44 views

Audit full text activity

I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.
5
votes
1answer
228 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
13
votes
2answers
10k views

How to avoid using variables in WHERE clause

Given a (simplified) stored procedure such as this: CREATE PROCEDURE WeeklyProc(@endDate DATE) AS BEGIN DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate) SELECT -- Stuff FROM Sale ...
-1
votes
1answer
71 views

Single quote issue

I have to update a column where I am giving query which is Update Table Set col = '[189] IS NOT NULL and [189] <> ''' Where colid= 198 But it's giving output:- [189] IS NOT NULL and ...
0
votes
1answer
1k views

Autogrowth of database and log file

I am confused what this autogrowth property does for any database or log file in SQL Server. According to my understanding: If we set autgrowth for database as restricted then its size will ...
-1
votes
3answers
6k views

How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]

I am unable to restore a backup from a 64-bit instance onto a 32-bit instance. Source Server: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Destination server: Microsoft SQL Server ...
4
votes
1answer
186 views

Table partitioning filegroup

Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
0
votes
1answer
100 views

Can I use the database and query it while full-text indexing?

I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
2answers
2k views

Running a query within a scheduled job

I am looking at running the following query within a scheduled job weekly: Select TOP 10 s.database_name, m.physical_device_name, CAST(DATEDIFF(second, ...
3
votes
1answer
492 views

BULK INSERT Data from file but ignore the first occurence of field terminator

I have a file that has data arranged in the following style: 1 1 0.5102 0.4898 0.0000 0.0000 0.0000 1 2 0.3114 0.6886 0.0000 0.0000 0.0000 I need to load ...
2
votes
2answers
3k views

SQL Server clustered index high fragmentation

I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index. Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
2
votes
2answers
861 views

How to rollback the identity seed after deadlock

Now that's an approximate sequence of operations Im performing: SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint ...
5
votes
1answer
543 views

Recommendations on how to organize Queues in Service Broker

I have a service broker application that currently has 5 or 6 queues on two servers. The general workflow is: Server A User populates some tables User fires stored proc which puts a message into ...
2
votes
1answer
1k views

Cannot connect to SQL Server after setting sa password to blank

I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically ...
4
votes
3answers
2k views

Auditing specific database events

For auditing purposes, we have a requirement in which we have to enable Auditing for theses events: DBA Logins Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.) Changes made by our ...
3
votes
1answer
16k views

CREATE DATABASE permission denied in database 'master'

I've developed a desktop application using WPF and Entity Framework code-first. In my application the user can create multiple databases at run-time. But Entity Framework can't create database if ...
-1
votes
2answers
84 views

SQL Server installation prerequisite fails

When I install SQL Server 2008 R2, I get this message: Previous release of Microsoft Visual Studio 2008 -- Failed (screen shot) I can't proceed to the next step. How can I fix this?
2
votes
1answer
162 views

excessive CPU utilization by SQL server

I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one ...
1
vote
3answers
324 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
1
vote
2answers
982 views

Missing or lost transaction and no errors to show what went wrong

I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue. New records are inserted using a stored procedure, which will return an error code to the ...
-2
votes
3answers
178 views

Left Join not giving desired result

I have two Datasets FEES PAYMENTS CrsCode InstNo FEE Regno CRSCODE Instno Payment CA1 -2 100 R1 CA1 -2 100 CA1 -1 200 ...
-1
votes
2answers
3k views

How to do division from select statements in sql server?

I am trying to create a select statement that can return a table of 1 column. Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
2
votes
1answer
862 views

Reorganize full text catalog is offline or online?

I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to ...
3
votes
3answers
2k views

Fixed length text file insert into SQL table

I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic. I have 100's of text files that ...
2
votes
1answer
175 views

Check programmatically if SQL Server needs restarting

I have to check programmatically if the SQL Server service needs restarting after series of changes in configuration. Is there any indicator (e.g. in the system registry) that allows to obtain this ...
1
vote
0answers
73 views

Coerce columnstore to operate in batch mode [closed]

I want to try the column store index in SQL Server and im trying to get my queries to run in batch mode, i rewrote my query this way( it had an outer join before) but the execution plan still show ...
0
votes
0answers
52 views

Transfer tables from multiple servers into one single server [duplicate]

I have to migrate similar tables present in multiple different servers (30+) into our new server using SSIS package. What would be the right approach towards this? I was successfully able to transfer ...
-1
votes
1answer
193 views

Validate each parent intermediary is also a parent to itself

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 ...
4
votes
1answer
299 views

Where and how to store my weird datamart

I could really use some help here. Here's my situation. I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
2
votes
1answer
744 views

If the users need INSERT/UPDATE/DELETE permissions, is Windows auth still more secure than SQL Server auth?

Some background first: The problem described below wouldn't exist at all if the database in question would have been built with a DBA's mindset: Data access only through views and stored procs --> ...
3
votes
3answers
5k views

How to check if I am hitting the Express Edition size limit?

I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused: How can I check if my DB is hitting the size limit? Is ...
4
votes
1answer
769 views

When SQL Server uses Multi Page Allocations

What are the consumers of MPA (multi page allocations) in SQL Server? I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans ...
-1
votes
1answer
234 views

How to merge data from 2 databases [duplicate]

I have a SQL-Server database that crashed while I was away on vacation. All info from 5/2/13 is missing from our current database because my replacement implemented a back-up of the database from ...
7
votes
3answers
2k views

SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc? SQL Server 2012 SP1. No replication, no mirroring, no log ...
3
votes
2answers
945 views

Execute a T-SQL job with a different connection string

Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string? The reason I want to ...
0
votes
2answers
185 views

Change SQL Server settings without restarting services

I can change SQL Server settings using Management Studio or programmatically as described e.g. here. I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any ...
3
votes
1answer
82 views

Scheduled stored procedure on all databases in my server

On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour. On the same server I have lot of databases with the same structure, and I'm constantly ...
3
votes
1answer
244 views

Create File Stream Enabled Database in Network File (NAS Server)

I want to create a filestream enabled SqlServer database using NAS server as the storage. I already passed the creation of DB step. Look at my previous question Creating SQL Server database with data ...
4
votes
1answer
108 views

How can I enforce data integrity in this model with a M:2 relationship?

This model describes a part of my database (somewhat simplified): A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 ...
0
votes
1answer
145 views

distribution.dbo.MSdistribution_history comments explanation

The comment column of distributor.dbo.MSdistribution_history contains comments such as <stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880"> ...
4
votes
1answer
111 views

One database or multiple referencing one?

Database design: one database or multiple databases, which is best? We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications ...
1
vote
4answers
373 views

Document database structure for auditors

I am DBA for a Microsoft SQL Server 2008r2. Due to an audition, I need to provide data structure of my database, to people without an explicit IT background. I guess they would like the traditional ...