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)

5
votes
1answer
2k views

Turning off auto shrink on all SQL Server databases. Why doesn't this work?

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message. sp_MSforeachdb ' BEGIN USE [?] DECLARE @dbid INT SET @dbid = DB_ID() IF(@dbid > 4) BEGIN --PRINT ''[?]'' ...
1
vote
2answers
2k views

Use domain username on SQL server reporting services installed in a server which is part of a Workgroup

I have installed SQL Server 2012 on Windows Server 2008. This system is configured to be part of a Workgroup and all the users in our organisation are part of a domain. I am trying to use the ...
5
votes
1answer
350 views

Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one?

For T-SQL stored procedures with the prefix sp_, SQL Server will search for and user a procedure in the master database before the current one. This does not appear to be the case with a SQLCLR stored ...
5
votes
3answers
1k views

Disabling logging on certain tables

I'm using SQL Server 2005. I have two tables that contain aggregate information. The information is constantly being updated, generating almost 5GB of log data a day. (That's larger than the entire ...
6
votes
4answers
4k views

What can speed up a SQL count query?

When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so ...
15
votes
2answers
2k views

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
6
votes
1answer
399 views

Backup to three drives suddenly slow

The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
0
votes
1answer
770 views

Length of LOB data (%1) to be replicated exceeds configured maximum %2

We recently hit this error shortly after enabling replication on a table. Length of LOB data (%1) to be replicated exceeds configured maximum %2 I've found solutions to this problem already: ...
3
votes
2answers
5k views

Is there a more concise way to convert a UTC datetime into a local date only?

I am trying to write a query that groups records based on the local date part only of a UTC datetime field. For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as ...
2
votes
2answers
3k views

Fast XML, slow XML

Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data. The ...
3
votes
3answers
6k views

Altering nullability of column in SQL Server

Say I have the following table: CREATE TABLE test(test_id int not null identity primary key, field1 int not null, field2 int ); CREATE INDEX IDX_test_field1 ON test(field1); CREATE INDEX ...
5
votes
3answers
2k views

When was a table trigger disabled and re-enabled?

I need to know if the enable/disable history of a table trigger is natively tracked by SQL Server. I've reviewed the system views: • [sys].[triggers] contains a modify_date field • ...
4
votes
1answer
2k views

How can I create a stored procedure using xp_fixeddrives?

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008 ALTER PROCEDURE freespace AS ...
3
votes
1answer
261 views

Do my metrics justify changing the MAXDOP settings?

I recently took over a dual instance virtualized SQL Server with 8 x vCPUs and 8 x physical cores. This is the only VM on the host. MAXDOP is at the default setting. The server has 100 high ...
3
votes
1answer
3k views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
0
votes
1answer
2k views

How do I connect to a database which is a linked server via ODBC?

(All the searches for this sort of thing tell me about where the linked server is connecting to the original database via ODBC, but I'm connecting to the intermediate server via ODBC.) I have a ...
0
votes
2answers
173 views

SQL Server transaction log [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? Is it possible to have SQL Server 2008 R2 reset the transaction log for certain databases when the ...
0
votes
2answers
957 views

Can you set up a SQL server maintenance plan to restore a database?

I am looking to setup a maintenance plan that makes a backup of a database (Database1) and then restores to another database (Database2) to confirm that it is able to restore successfully. Can I do ...
1
vote
1answer
93 views

Software/Hardware Upgrade

I've been working on a software/hardware upgrade from SQL2005 on win 2003 to SQL2008 R2 SP2 on win2008. The new hardware has 48 cores with 128 gig of ram whereas the older server 16 cores with 32Gig ...
1
vote
0answers
138 views

What permissions are required to view the contents of a catalog on a remote server?

I defined a linked server from a SQL Server box to a MySQL box. I went through a very detailed set of instructions and steps, everything works like a charm when I am logged in. The linked server ...
1
vote
2answers
886 views

Consistency errors for a SharePoint database

When I check my database (SQL Server 2008 R2, Windows Server 2008 R2, SharePoint 2007 DB) with the command: DBCC CheckDB I get 12 consistency errors (see below). Is the only way to fix it with ...
1
vote
3answers
2k views

Putting SQL Server data files in a different partition

I have a SQL Server and I put the data files (the .mdf) on a different partition. Windows runs from C: and the data files and logs are on D: Is it advantageous or disadvantageous to separate the ...
14
votes
1answer
1k views

Where are Statistics physically stored in SQL Server?

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool? More specifically, is there a way to figure out the pages used by ...
1
vote
1answer
34 views

sql server session revert

Sql server log file stores the every log in information.is it possible to revert the query operation which we doing through query editor or by application based on the session we are logged in? ...
5
votes
2answers
1k views

Optimisation of complex query - 100% cost index seek?

I'm a c# developer, however I'm quite experienced with SQL. I have had a bit of experience with optimising queries but I've come up across one that has me stumped. The query takes about 3 minutes to ...
0
votes
1answer
4k views

How many IP numbers are there for a SQL server active active cluster setup also a active passive setup?

How many IP numbers are required for a SQL server active/ active cluster setup also what in the case of an active/ passive setup? Correct me if I am wrong 2 for private network 2 for public network ...
1
vote
2answers
259 views

correct security and user setup for SQL Server database

I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database. What is the best approach for setting up users ...
3
votes
2answers
708 views

Alternate ways to increase column size

I have a database that is in production, and in some instances will have millions of records. It has been requested that we increase the sizes of several columns, which is for the most part pretty ...
1
vote
1answer
5k views

Table schema column data type equivalents for Netezza 6 to SQL Server 2008

I'm trying to find a data type equivalents for data types in Netezza when migrating table schema to SQL Server. Where can I find a complete list? When getting down to brass tacks, I am specifically ...
2
votes
0answers
42 views

List of changes required to upgrade scripts after starting to use replication in SQL Server

We have recently setup transactional replication on our test and production databases. As part of that change, we've had to make changes to how we create upgrade scripts as well. The goal is to make ...
9
votes
3answers
443 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
2
votes
1answer
186 views

How can I modify a trigger at subscribers SQL Server 2005?

I have a SQL Server 2005 publisher with SQL Server 2005 subscribers and one SQL Server 2000 subscriber running merge replication. Is there any way to modify triggers at a subscriber with the ...
2
votes
2answers
214 views

T-SQL Naming Standard

Is there a recommended (official or not) style from Microsoft regarding naming and casing in T-SQL? If there isn't one from Microsoft then is there one with broad acceptance?
1
vote
1answer
1k views

Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

I have the following table [dbo].[myObj] ( [ID] [int] IDENTITY(1,1) NOT NULL, [fk_ParentID] [int] NULL ) and I have a relationship from fk_ParentID to ID. I want to change the Delete Rule ...
2
votes
2answers
66 views

How can I get a copy of the SQL generated by Management Studio?

If I save a table in Management Studio's Table Designer for example, I believe it runs DDL SQL in the background. Is it possible for me to grab this SQL so that I can save it to a file?
0
votes
1answer
881 views

TSQL query for viewing start/end of all SQL Agent jobs yesterday

See this related query: What's a good tool for visualizing SQL Agent jobs? I'm not after a visual tool however, since I am looking to build my own visualisation. This is my starting point, but ...
1
vote
3answers
2k views

Encrypt/Decrypt output & input for SSIS task

I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system. I have a new requirement to encrypt the CSV text files that are ...
1
vote
1answer
670 views

bulk insert from file

I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db. I would like to use BULK INSERT, but I have no access to the db server file system. Questions is there a ...
1
vote
2answers
238 views

SQL Server Monitoring Tools [closed]

Can anyone recommend a good SQL server Monitoring Tool? One which will provide info on, server space usage, CPU usage , expensive queries run etc.
4
votes
1answer
519 views

Preventing users who are dbo from dropping their own databases

We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this. ...
1
vote
1answer
783 views

Logshipping alert emails are being sent out while there is no error

I have logshipping setup with a Primary and a Secondary, and this has been working fine for a long time. Yesterday I started reveiving these emails: The log shipping primary database ...
3
votes
1answer
129 views

Strange SQL Statement Behavior

I have a simple SQL statement (executing on a SQL Server upgraded from 2000 to 2008 R2): SELECT TOP (1) DocNox = CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1) FROM Tbl1 WHERE Fld1 = 19 ...
1
vote
0answers
169 views

Multi-Table Join to Single Destination

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
4
votes
1answer
103 views

How can I copy specific tables into one SQL Server Table into a separate table automatically?

I'm not sure if this is possible. Looking for some help with a specific issue. I am building an iPhone application with a data layer written in Ruby that will connect to SQL Server 2008. For various ...
2
votes
2answers
1k views

SQL Server 2012 CPU core usage

Having some trouble figuring out how to alter the core usage on SQL Server 2012 Enterprise. On a server with 16 cores, is there any way to alter which ones will actively use the SQL database, and not ...
2
votes
1answer
1k views

In Change Data Capture (CDC), how to configure retention with success?

We have CDC enabled on SQL Server 2008 with retention re-configured to a number other than the default 3 days (or something similar). When we first configured it (with default options), it worked ...
7
votes
1answer
1k views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
0
votes
2answers
231 views

Regarding create index and lock word in SQL statement used for SQL Server

This way someone creates an index. CREATE TABLE [dbo].[QueueSlow]( [QueueID] [int] IDENTITY(1,1) NOT NULL, [QueueDateTime] [datetime] NOT NULL, [Title] [nvarchar](255) NOT NULL, [Status] [int] NOT ...
5
votes
3answers
242 views

How do I find I.O. statistics (by index) for queries in progress

By running SET STATISTICS IO ON before running a SQL query, I can get an idea of the number of logical reads performed on each index after the query completes. By looking at the view ...
2
votes
2answers
730 views

Creating CDC system table on SQL Server 2008

I am facing problem with CDC tables in my database. I think few of these tables are damaged and are not capturing any audit information. I tried to disable CDC on these tables, but that did not work ...