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)

7
votes
1answer
1k views

When performing a cross-database transaction, in which transaction log(s) is the information stored?

Given the following snippet: -- error checking omitted for brevity begin tran exec database1..my_stored_procedure exec database2..my_other_stored_procedure if (@@error <> 0) rollback ...
5
votes
4answers
99 views

How do I check if a certain SQL statement is being run?

Using SQL Server Management Studio, how can check or see who is running a certain SQL statement?
1
vote
1answer
718 views

How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)

I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh ...
2
votes
1answer
145 views

WorldWide site - should I save UTC or in fixed time?

Let's say I have a world wide website ( I mean it has uses from all around the world). I have 1 server which is in germany ( at some city). it will never be moved from germany - ever. My question ...
1
vote
1answer
88 views

Inhibit Sql Server Log Shipping Alerts during certain times

There are batch jobs that occur in the middle of the night, which cause log shipping backups to be delayed such that they exceed the alert threshold, and alerts are then emailed. How can I disable ...
4
votes
2answers
2k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
0
votes
1answer
274 views

Tool to view intermediate query results

I am using SQL Server 2008 Express edition and I've worked two days on a problem where seems to be a cross join in running a query. I would want to know the set returned per join operation visually. ...
0
votes
2answers
385 views

Can I recover CDC tables from transaction logs?

Is there anyway to restore CDC tables from transaction logs? My basic understanding is that CDC reads these logs, and entries that are specially marked, are grabbed by CDC. Once this has happened ...
3
votes
3answers
189 views

pros/cons of different ways to store whether a record is one of two options?

I am trying to store whether an address is a Work address or a Home address. There will never be another type of address. I'm wondering what the pros/cons are of the different ways to store this, ...
2
votes
4answers
16k views

How can I query data from a linked server, and pass it parameters to filter by?

I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned. The basic syntax looks something like this: INSERT INTO #tmpTable (Id, ...
3
votes
2answers
1k views

Can I get notification when event occur?

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide ...
4
votes
1answer
2k views

Is replication from SQL Server 2008 to PostgreSql possible?

Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
3
votes
2answers
11k views

Total Memory used by SQL Server (64 bit)

My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named ...
2
votes
1answer
111 views

Backing up SQL Server 2008 database in different recovery model

We currently have a production environment with 10 SQL Server 2008 databases in Full recovery mode (for obvious reasons). We back these databases up fully at a frequent interval, but due to the Full ...
3
votes
1answer
130 views

SQL Server database schema (and likely, some data changes) - how to auto-distribute over many database instances

Our development involves a SQL Server database (also might be Oracle or Postgres later) and we would sometimes make database schema changes or some other interventions in database. What solutions ...
0
votes
1answer
1k views

Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?

We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records? ...
0
votes
1answer
6k views

How to connect a SQL Server 2012 to the internet [closed]

As you know you can create a database with SQL Server using the SQL Server configuration manager and SQL Server Management Studio. You know this - just typing that to tell that I checked the whole ...
10
votes
2answers
18k views

Maximum memory setting in SQL Server

I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available. As is noted elsewhere, SQL Server regularly takes up to 98% of physical ...
2
votes
2answers
1k views

Modifying replication distributor & publisher

I have a SQL Server 2008 transactional replication setup as follows: 192.168.100.1 <-- Distributor 192.168.100.2 <-- Publisher 192.168.100.3 <-- Subscriber Our network team want to change ...
9
votes
1answer
6k views

Why disabling a clustered index makes the table inaccessible?

When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
0
votes
1answer
569 views

DR - Moving databases from Active/Active cluster to Standalone server with LUN sync

We have an active/active cluster on LUNs (with one named instance on each node and a total of an 100 DBs) For DR purpose, LUNs will be replicated (with same drive letters) and attached to a ...
1
vote
3answers
338 views

What is the optimal response time for file growth

We normally set our file growth in percent (10%,20%) or fixed size (10 MB, 20 MB etc). If the disk response time is too high for the requested size to grow, then users will be affected and have to ...
1
vote
1answer
198 views

Can I use Try_Cast to give me a query result column with different datatypes?

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need). I am trying to make a view for this table that allows the value to display ...
4
votes
1answer
232 views

Selective Xml Indexing

I have recently been researching Selective Xml Indexing and have been trying to figure out how to index a .query method If I have the below query WITH XMLNAMESPACES (Default ...
3
votes
2answers
2k views

What's the meaning of the Collation Code (blank, 90, 100, 110) in SQL Server 2012

During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS. What do they mean and which one is newer? When there's no Code, is it newer ...
0
votes
1answer
107 views

“Replicate” sql structure between testing and staging on same database instance

On my local development server I have a database set up which gets hooked into Entity-Framework and is then used for unit testing. Let's call it COMPANY\Database On the same server (and same database ...
3
votes
4answers
2k views

Working Linked Server Query fails in sp_send_dbmail

Take the following example: EXEC msdb.dbo.sp_send_dbmail @recipients = 'me@whatever.co.uk' , @query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' , @attach_query_result_as_file = N'True' ...
3
votes
1answer
318 views

Is there an effective way to create extended event data that includes stacktraces in SQL Server on a local db instance?

I read Paul Randal's article on getting the symbols for SQL Server. I have a theory that I could get the extended events to read the symbols from the symbol server catch directory by setting the ...
3
votes
0answers
415 views

SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows [closed]

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part: ALTER PROCEDURE ...
2
votes
1answer
2k views

The job failed. The owner (pc\user) of job FULL DB BACKUP does not have server access

SQL Server 2008R2 is failing to execute log backup hourly. Date 5/2/2013 10:22:19 PM Log Job History (LOG BACKUP) Step ID 0 Server pc Job Name LOG BACKUP Step ...
0
votes
1answer
2k views

Real-time merge of two databases on different servers

One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to ...
2
votes
2answers
3k views

Restoring differential backup from SQL Server 2005 to SQL Server 2012

We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken. Please ...
3
votes
1answer
4k views

SSMS 2012 is not showing the tables of the system databases

I have SQL Server Management Studio 2012 on my laptop connected to a SQL Server 2012. If I go in System Databases and then any of the 4 (master, model, msdb, tempdb) I don't have the Tables folder. ...
2
votes
2answers
6k views

Is it possible to backup a database that is not ONLINE?

We have a job which backs up all ONLINE databases nightly, but for some reason it didn't back up msdb. So the issue is at that time msdb was in some status was other then 0. So my question is : is it ...
5
votes
1answer
182 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
4
votes
1answer
75 views

Error statement is not logged, only *password-— with many dashes

I use extended events to store database errors, like this: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_hostname, ...
3
votes
1answer
71 views

CPU column 5x higher on one machine vs another?

We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we ...
3
votes
1answer
1k views

Service accounts for SQL Server 2012 only allow pre-Windows 2000 name?

I've just set up SQL Server 2012 with domain service accounts. I found that the account name would not validate unless I used the shorter user name. Is there any way of using the full names?
2
votes
2answers
12k views

The multi-part identifier “xxx” could not be bound

The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external); HAVING (SELECT COUNT(*) FROM ( SELECT * FROM ...
0
votes
1answer
100 views

Migration of SQL Server to Oracle Database

In Sql server, My Procedure will create temporary tables where column names are dynamically comes from every user, then inserts the data into the temporary tables and i use the data further based on ...
1
vote
3answers
315 views

DB comparer field by field for all tables

I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added ...
2
votes
2answers
3k views

Trying to write a function that doesn't require dbo. prefix

I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run EXEC SP_CONFIGURE ...
11
votes
5answers
2k views

Force sql server to run query conditions as written?

Im using Sql server 2008 R2 And I have this pseudo query (SP) : select ... from ... WHERE @LinkMode IS NULL AND (myColumn IN (...very long time exeted query...)) ... ... The ...
1
vote
2answers
191 views

Why does this 120 GB insert grow the log by over 780 GB?

A table [tbl_old] has about 3 billion rows and about 120 GB for disk space, and then created a [tbl_new] with the same schema (actually, just dumped the script of [tbl_old], then rename the table name ...
2
votes
1answer
175 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
1
vote
1answer
133 views

SSRS Deployment

I have developed and deploy my reports on report server in LAN. but I want to deploy my reports on report server which is not in LAN. means I need a setup or package which can run at client end ...
1
vote
4answers
6k views

Database Restore Failed Due to Incompatible Version of SQL Server [duplicate]

I am working on different two SQL Server, I need to transfer database from one server to another but problem is the servers have different versions. Below is image of error message. Using SELECT ...
0
votes
2answers
602 views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
4
votes
1answer
289 views

Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question). Our ...
5
votes
1answer
3k views

How does OPTION (FAST 1) actually interact with a client?

This is question 1 of 2 related to OPTION (FAST 1); We've just upgraded our ERP database from SQL 2000 EE to 2008 R2 EE and we've been noticing increased blocking in the database. I've narrowed it ...