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)

3
votes
3answers
2k views

Restore one table from SQL Server data file (.mdf)

I stupidly deleted most of the contents of a table in my database. I have a backup of the server which has the .mdf on it. I assume I can make a new db and attach the backup .mdf. Then I can select ...
2
votes
1answer
1k views

User in multiple windows groups mapped to sql logins

I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2. For every app: SQL login based on Windows Group is created SQL server login is mapped to SQL database user user is member of a ...
2
votes
1answer
2k views

UPDATE Statistics with full scan

Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior. a) takes any locks during the entire ...
1
vote
2answers
597 views

SQL Server 2008 R2 recognizes a domain account as a SQL Server account

After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database ...
5
votes
1answer
1k views

Cross Database Chaining vs. Trustworthy Option

Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account ...
7
votes
1answer
175 views

Disallow new UDFs that contain a cursor

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
3
votes
3answers
3k views

SELECT every nth value by Date from large table

Rewrote this question 2012-06-20 for clarity, check edits for older version Background and Question I collect data for a couple of sensors on some devices. This data I want to display for the ...
1
vote
1answer
2k views

Why is a Login losing a User mapping?

Is there any way to determine why is a Login is losing a User mapping? I've created them with the following SQL ... CREATE LOGIN [qauser] WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = [app_tst], ...
2
votes
0answers
131 views

Lock timeout on user tables traced back to system processes [closed]

I have a problem where users are reporting intermittent errors in an application that is using a SQL Server 2005 database. Nothing jumped out at me at first glance so I took a look at ...
0
votes
1answer
397 views

Can You Replicate Sequences

In SQL 2012 can you replicate sequence objects, and if so do changes to the sequence (including consumption of values) get replicated? If not is there a good reason why this was not included?
5
votes
2answers
596 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
2
votes
3answers
2k views

Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a?

I have a table with two columns, A and B. Each pairing (a, b) should only occur once in the table. When I query this table, I will only be interested in those rows where A has a certain value. Is a ...
6
votes
1answer
3k views

What are my locking options for a Merge statement?

I have a stored procedure that performs a MERGE statement. It seems like it locks the whole table by default when performing the merge. I'm calling this stored procedure inside of a transaction ...
1
vote
1answer
59 views

What steps are required to move data from a custom schema in one database to the default schema in new database

Lets assume for the sake of argument that I have the following tables in a single SQL Server database. mySchema.Users mySchema.Products -- secondSchema.Users secondSchema.Contracts ...
5
votes
1answer
149 views

Show data point values in the legend section

Is it possible to show the values in the legend section in a chart component of SSRS? I'm using SQLserver 2008. Something similar to the image below
7
votes
3answers
3k views

SQL Server 2008 R2 High CPU

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3. Our database works very hard (we run around 600,000 queries in 1 minute) and ...
0
votes
1answer
221 views

strange situation with very large partitioning table

I have a very strange situation here... On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last ...
1
vote
0answers
423 views

Format 1,2,3,4 to roman letters for display purpose [closed]

Alli have a problem with serial number format. i have the serial numbers as 1,2,3 upto 'n'. and i need to show this as roman letters(i,ii,iii,iv....). i need to display this in crystal report. thanks ...
1
vote
1answer
2k views

Determine sql server installation (server or client tools only)

Think about the situation that you have a software audit. The company use a software facility management tool to determine installed software on a machine. This software that seek for installed ...
4
votes
1answer
564 views

Methodology & Architecture to Create ETL Process

What book or webpage can I read the architecture and methodology for creating ETL process? In other words, I'm looking for "how to do it" to create a ETL process when you have many source system to ...
2
votes
3answers
33k views

SQL Server 2008 R2 High memory usage

I have SQL Server 2008 R2 that runs multiple jobs over night, the problem is SQL Server will eat up all RAM and CPU, then it will not release the RAM resources after the job finishes, as a workaround ...
1
vote
1answer
152 views

Thought CTEs were syntactic sugar

I was asked to test a change to a query, moving a Table Valued Function (TVF) into a CTE (non-recursive). My understanding was that each CTE reference is executed separately, so there was a ...
3
votes
4answers
263 views

Special Formatting of Numbers

I need help formatting numbers in a specific way. If a number has 3 digits or less, I would like it to remain the same. If a number has more than three digits, I would like it place a decimal ...
3
votes
2answers
1k views

Restore a Database into a Current Database and merge

I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using. Both Databases will ...
5
votes
1answer
2k views

Storage order vs Result order

This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT. @Catcall says this on the subject of storage order (clustered index) and the output order ...
3
votes
1answer
705 views

SQL Server 2005 - Map login to pre-existing user

The short of it is that we have several databases that are synchronized between two failover instances. The primary contains logins that are mapped to the users in the databases, and everything's ...
0
votes
1answer
1k views

SQL Server Replication Stored Procedures sp_MSdel_db, sp_MSupd_db, sp_MSins_db

I have 2 production servers and 2 development servers. All SQL Server 2008 r2. In production, I had one of our DBAs create a replication publisher and distributor on ProdServer1 and Subscriber on ...
1
vote
1answer
439 views

Sql Server Measuring Query Memory Consumption

I am optimizing a stored procedure in sql server 2005, and while I know of several ways to measure timing differences, I am not quite sure of a good way to measure memory consumption differences. How ...
4
votes
5answers
17k views

Tempdb is getting full very quickly in microsoft sql server 2008

I am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly . Is there any solution to clean the tempdb file in microsoft sql server 2008 on ...
2
votes
1answer
1k views

Is there a way to use a SQL Server 2000 database in SSIS 2012?

I just got my first chance to install SQL Server 2012 on a test machine. I also installed all of the management and Visual Studios components. I created a copy of one of my SSIS 2008 packages and ...
2
votes
1answer
16k views

How to Delete Restoring Database

I am running log shipping with SQL server 2008 R2. I have a situation where the secondary database drive ran out of space and was not applying log shipping transaction logs. The way I want to fix ...
3
votes
2answers
2k views

How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive

a while ago I asked a question about how to make begin dialog and send more flexible so that it can be embedded in a procedure that takes the to, from, parameters as sysname variables. Service Broker ...
2
votes
1answer
134 views

How to tell what user has synced

I have set up merge replication web sync to sync a publication from a SQL Server 2012 database. I am trying to make some parameterised filters using SUSER_SNAME() or HOST_NAME(). When I do a sync is ...
2
votes
1answer
2k views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
16
votes
2answers
21k views

When To Update Statistics?

I've inherited a Maintenance Plans that does the following: Cleanup old data Checks DB integrity Performs Database and Transaction Log Backups Reorganizes Our indexes Updates Statistics Delete old ...
2
votes
2answers
2k views

How to log successful login attempts to a database?

Is there a way to log database name when auditing logins in SQL Server (2005 and above)? I basically want to log the username and the database name the user successfully connected to. I have tried ...
5
votes
1answer
410 views

SQL Server Linked server query problem (group by)

I have been using an old SQL Server 2000 installation for a long time that is linked to several Oracle servers, and it all works very well. I am trying to migrate to SQL Server 2012 via SQL Server ...
11
votes
4answers
4k views

Sort order specified in primary key, yet sorting is executed on SELECT

I'm storing sensor data in a table SensorValues. The table and primary key is as follows: CREATE TABLE [dbo].[SensorValues]( [DeviceId] [int] NOT NULL, [SensorId] [int] NOT NULL, [SensorValue] ...
2
votes
2answers
473 views

How can we automatically replace a table with a view for a specific user?

We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a ...
0
votes
3answers
538 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
5
votes
1answer
493 views

SQL Server 2008 Multi Page Allocation

When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer ...
3
votes
1answer
7k views

Create a view with dynamic column headers based on original table row data? [closed]

Given the two sample tables here: Tickets Table ID User Description ------------------------------------ 0 James This is a support ticket 1 Fred This is a ticket too Properties ...
1
vote
1answer
2k views

Why do I only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008

I was finally able to create my ODBC connection but now when I go to External Data > ODBC > LINK TABLES using my ODBC connection in Access 2007 I get a huge list of tables such as ...
6
votes
2answers
949 views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds). After suspecting ...
3
votes
2answers
473 views

SQL Server 2008 ODBC

My SQL Server 2008 is located on a remote computer so I am trying to create an ODBC connection using SQL Server Authentication. My problem is that the way i normally connect to my SQL Server is by ...
5
votes
1answer
604 views

Are SQL Server's Date values stored as a single int?

SQL Server stores DATETIME internally as two INT values, as SQLDenis explains. Is it correct (by logical extension) that DATE types (SQL Server 2008+) are stored as a single INT?
3
votes
1answer
151 views

How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008

We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the ...
1
vote
2answers
23k views

Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type

I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are ...
2
votes
1answer
563 views

SQL 2012: Import DB changes original data scheme

I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines. As a test I copied the data files of one of the DBs, did an "Attach ...
2
votes
2answers
624 views

How To Set Up Web Synchronisation With IIS 7.5

I set up merge replication web sync using a pull subscription. When I try to start the merge agent at the client side I get this error, The Merge Agent could not connect to the URL ...