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)

1
vote
3answers
7k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
819 views

Altering Multiple Tables

I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to ...
1
vote
1answer
87 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
1
vote
1answer
74 views

Connecting to Active Active Cluster Services

I have a two node active active cluster setup as below: NODE 1 - SQLINST1 ( with DTC1 ) NODE 2 - SQLINST2 ( with DTC2 ) I wish to now have the application connect to SQLINST2 whenever ...
1
vote
1answer
399 views

Difference between COUNT(ColName) vs COUNT(ALL(ColName))

Both query return the same output: select COUNT(id) from Employee select COUNT(All(id)) from Employee So what is the difference in SQL-Server 2008R2?
0
votes
1answer
5k views

How to insert default values to a table with not null columns?

I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required). I would like to write a query that inserts a row with ...
0
votes
1answer
381 views

understanding (and modelling) grocery shopping lists

I am having a hard time understanding how multiple product orders are mapped out. What is the best way of modelling a shopping list for multiple customers? I am developing an C# application that ...
1
vote
2answers
1k views

Numerous failed authentication attempts in error log

I'm having a hellacious time trying to figure out what is attempting to authenticate against a particular database server, running SQL Server 2008 R2 Standard Edition. The following is the error ...
1
vote
2answers
228 views

Changing two publications at the same time causes deadlock in distribution database

I have transactional replication setup. Let's say three databases on server A replicate to corresponding three databases on Server B. The databases are identical (same structure for different ...
1
vote
2answers
2k views

SQL Server hardware configuration: Best Practices [closed]

I inherited a relatively new database server that is light on hard drives. Currently, everything - OS, SQL Server, hosted databases, etc. - is running on a single partition in a two-drive RAID 1 ...
3
votes
4answers
10k views

How To change the column order?

How To change the column order in sql server 2008 R2? Eg: My table order is Eid------Ename-----Esalary Know i want this format like Eid---Salary----Ename Thanks.
2
votes
1answer
399 views

How SQL determine page exists in Buffer pool (internal)

When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data. If data page isn't in memory, then SQL pull those pages ...
1
vote
1answer
207 views

Transaction Log usage by transaction

Is there a way to find out how much of the transaction log a specific transaction uses? I have a database with simple recovery model and a log file with max size 500 MB. During our tests we ran into ...
0
votes
1answer
655 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
1
vote
2answers
143 views

Three databases : one instance or three?

I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization. I have identified the main databases that are mission critical to the ...
2
votes
2answers
1k views

Maintenance Plan Job running at additional unscheduled times?

I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is ...
2
votes
2answers
4k views

SQL service does *not* start, port 1433 in use

Sorry to raise if this has been asked before. The SQL Server service is defined as manual, whenever I start it I get (event viewer): > Server TCP provider failed to listen on [ 'any' 1433]. Tcp ...
1
vote
0answers
347 views

ODBC Connection Not Refreshing

I am trying to connect FedEx Shipping Manager Integration Assistant to a SQL 2008 R2 database. I have a view created that has all the necessary fields to populate in the Integration Assistant. The ...
4
votes
1answer
1k views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
3
votes
1answer
700 views

Inserting a 30 million row table from a linked server

I have a large table that needs to be updated on a nightly basis from a linked server. Initially I had simply truncated the table and inserted all of the records but this was taking too long. I'm ...
3
votes
1answer
463 views

Three Node Cluster that Allows For Two Failures

We are creating a three-node three-instance cluster with SQL 2008 R2 (not cutting edge, but that is what the application supports) and think it should be able to maintain quorum with two physical ...
4
votes
2answers
4k views

How to Log Debug Information About a Stored Procedure

We have a group of fairly complex stored procedures that process a transaction queue on a pretty much round the clock schedule. Currently these scripts use a number of print statements to document ...
2
votes
1answer
960 views

Database Backup from 2008 R2, Restore on 2012 Failover Cluster Instance

I have a production database server having sql server 2008r2. This is an old server and considering our load, we are moving to a better hardware configuration, Now the new environment is a cluster ...
1
vote
1answer
661 views

GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of ...
1
vote
0answers
2k views

Cannot read property Collation.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo) [closed]

I am new to databases and Microsoft SQL. I just installed SSMS today and am getting this error while trying to add new login in security or rightclick-> view properties on user logins already there ...
5
votes
4answers
24k views

Displaying the list of all tables in all database

How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?
2
votes
1answer
57 views

Find the SQL query that caused an event in the past

I have a database in which I noticed that one of my tables is missing. I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it ...
0
votes
0answers
209 views

Comparing CLR Functions in Visual Studio SSDT

I'm comparing the CLR functions from a pair of SSDT projects to a database. The only difference shown in the comparison is on two properties: IsDeterministic and DataAccess The compare shows that the ...
18
votes
3answers
5k views

When to use CDC to track history?

SQL Server Change Data Capture is a feature that reads historical data from the SQL Server transaction logs and stores them in a special table. Through the use of special table value functions (TVF) ...
1
vote
1answer
747 views

How to aggregate pairs of id values into groups

I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example ...
3
votes
3answers
7k views

What are the risks of copying MDF/LDF files without detaching the DB first?

Where DB backups are not available, our support team has been trained to restore using the following steps: Get a copy of the MDF/LDF from a file system backup. Stop the instance. Overwrite the ...
1
vote
1answer
424 views

Delayed availability of historical data when using CDC

I am using the SQL Change Data Capture feature to retrieve historical data from my tables. During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my ...
0
votes
0answers
107 views

SQL Server consistency I/O base error

I have received this error while performing queries on a database (SQL Server 2012) msg 824, level 24, state 2, line 1 sql server detected a logical consistency-based i/o error. Incorrect page ID ...
1
vote
2answers
955 views

How to retrieve the Capture Instance for a given table?

Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled. I am trying ...
0
votes
0answers
2k views

SSIS and Script Component - WebReference problem

Within my SSIS package-dataflow I have a script component and within this component whenever I add a Web reference and close editor, script component gives me "Validation error. Data Flow Task ...
-1
votes
2answers
749 views

Create Table Script in SQL Server 2008 R2?

When I Generate a script for Particular table in SQL Server Extra line is added in my Script What is the purpose of that line in SQL Server 2008 R2? Thanks. PAD_INDEX = OFF, ...
2
votes
2answers
3k views

How to do partition on a non clustered index column?

I have table ORDERS .Where there are Million rows in it .I need to add Pationed Index on Order to store data in one of two files based on the new IsLocked field.But when i checked table there is a Non ...
3
votes
5answers
2k views

Steps after restoring to a different server

We have a 30 GB Database on SQL Server 2008 R2 Standard Edition. Yesterday we backed up the database and restored it to a different Virtual Machine which has exactly the same specs as the previous ...
0
votes
3answers
525 views

Can we restrict the number of connections to a database in SQL Server?

I have a database on SQL Server. My web application contacts this database. Is there a way in which I can restrict the number of connections to this database through some settings on the SQL Server?
2
votes
2answers
5k views

Help with SQL Server Error “ad Hoc access to OLE DB Provider 'Microsoft.ACE.OLEDB.12.0'”

So we have an interesting issue where users are receiving the following error when attempting to query a view in SQL Server 2008 R2: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB ...
1
vote
1answer
106 views

Purpose of Nonclustered indexes in SQL Server 2012

(New to SQL Server) I am struggling to understand purpose of Nonclustered indexes in SQL Server. Read though MS documentation (both for v2012 and v2008 that has pretty pictures) and some online ...
0
votes
1answer
4k views

Cannot access Properties page of a database

I'm in the process of decommissioning a database server and migrating the databases from one server to another. I was attempting to get the the properties of the database and received a SQL Error ...
0
votes
0answers
101 views

Database migrated to SQL Server 2005 with 80 compatibiltiy from SQL Server 2000 experiencing locks

I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists. The code, while not great to maintain, ...
2
votes
4answers
125 views

Effiiency of multiple OR predicates versus a single range

Say I have a SQL statement: SELECT * FROM MyTable WHERE KeyColumn1 = 1 OR KeyColumn1 = 2 OR KeyColumn1 =3 OR ... KeyColumn1 = 10 And another: SELECT * FROM MyTable ...
1
vote
1answer
660 views

SQL Server - Service User Accounts and Licensing? [closed]

Maybe this is the wrong place to post, since its not necessarily sql server related, but here goes. If I want to setup some service user accounts in the domain to use to run sql services like the sql ...
5
votes
2answers
8k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
0
votes
1answer
198 views

Nightly reload of tables from a linked server

I have a number of tables that need to be reloaded nightly from a linked server. My current method of doing this is to create a nightly job to pull the data using something like the following: ...
1
vote
1answer
2k views

Is there anyway to make this stored procedure thread safe?

I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each ...
1
vote
1answer
2k views

sqlsrvr.exe is not using all memory

I have no clue what to set up more. I tried a lot. My Problem is that on my test Server everything is runing perfectly see but on my production Server its not! see I found this on my ...
2
votes
1answer
86 views

Clustered index additional storage

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself. But, as clustered index is organized ...