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)

0
votes
3answers
3k views

SQL agent job step's connection managers

Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?
2
votes
2answers
19k views

Database running out of space

My database has 16MB of space left. I used to just truncate as I was taught but I found these links that advise against truncating: ...
2
votes
2answers
5k views

How do you create a composite foreign key when key being referenced is also a foreign key

If you have table a (column x) which is 1 to many with table b (columns x, y) which is 1 to many with table c (columns y, z), it's pretty straight forward to define the foreign keys. If for ...
7
votes
3answers
406 views

Should we periodically 'refresh' the mirrored copy of a SQL Server database?

When you have SQL Server mirroring set up, do you trust that your mirror database will stay the same as the primary, even after months and months of mirroring?  I know we all check the status, ...
1
vote
3answers
1k views

How to create new users for restored databases?

I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa. Then a couple of databases were restored from earlier ...
7
votes
0answers
52 views

Is Logical Fragmentation Meaningful in the age of huge SANs? [duplicate]

So, every time I read that a data file shrink will cause fragmentation or a developer rave on that the 95% frag he sees is the cause of all life's misery, I wonder what the problem really is. We no ...
5
votes
1answer
297 views

Clear schema from database without dropping it

I'm working on a school project where I have a SQL Server with a database for my team. I already imported a local database created with Entity Framework. Now the model has changed, table properties ...
0
votes
1answer
2k views

Database management tool for compact edition (.sdf) database [closed]

What is the best database management tool for remotely managing a compact edition (.sdf) database created in WebMatrix?
4
votes
2answers
4k views

Change drive letter (which contains system dbs)

Is it possible to safely change the drive letter for a volume which holds only system databases? What precautions should be taken and how should it be done? I know I can just go to computer ...
2
votes
1answer
5k views

Data Migration from Oracle to SQL Server [duplicate]

If you need to do a data migration from an Oracle database to SQL server, what approaches and technical solutions are the best practice? Our database has about 100 million rows and a total of 52 ...
1
vote
1answer
323 views

Database size not reduced after compressing tables

Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?
4
votes
2answers
5k views

Constraints based on other columns

Is it possible to limit what values are allowed in a coulumn based off of other values in the row? For example, my table: ID Test_mode Active -- --------- ------ 1 1 Null 2 0 ...
2
votes
0answers
158 views

Re enable Windows Authentication [closed]

My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.
6
votes
4answers
6k views

Re enable Windows Authetication in SQL Server [duplicate]

My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password. I ...
1
vote
2answers
1k views

Can't use SQL Server database on a networkpath

I am working on a project for a very long time now. It's a .NET application which works with a SQL Server database (.mdf, LINQ to SQL). Now I am almost finished and wanted to run some tests. But ...
0
votes
0answers
69 views

Copy tables from a stand-by database

I want to explain the following. I have a database that is a recovery db from our main environment. This database is kept up-to-date through log shipping. However, I want to copy all tables from this ...
2
votes
1answer
1k 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: ...
2
votes
1answer
38k 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 ...
4
votes
2answers
41k 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
2k 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
368 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
3k 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
54 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
983 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
50 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
398 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 ...
15
votes
2answers
16k 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 ...
0
votes
1answer
74 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 ...
0
votes
3answers
11k 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
276 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
106 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?
2
votes
2answers
4k 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
899 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 ...
3
votes
2answers
6k 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
1k 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 ...
6
votes
1answer
933 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
2k 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
3k 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 ...
4
votes
1answer
30k 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
96 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
231 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
751 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
2k 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
216 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
6k 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
1k 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
3k 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
212 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 ...
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 ...