SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

8
votes
1answer
140 views

How can I prevent SSMS from re-writing my code when saving a view?

I'm creating a view that uses a statement with a WHERE clause similar to this: WHERE ( col1 IS NOT NULL OR col2 IS NOT NULL ) AND NOT EXISTS (SELECT ...) ...
2
votes
0answers
381 views

Fastest way to determine size of groups of FILESTREAM objects

We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects. To date, we have been using ...
0
votes
1answer
77 views

How many connections reads queue

Is there a way to get to know how many external tasks (console applications) is reading queue? I am using Service Broker external activator. What i want is something similar to information i get ...
5
votes
1answer
1k views

Calling a .NET method in a SQL Server script

In the data migration process, I need to convert some columns values form the source system before writing to the target table. I've been provided with a Csharp code which reads a string and do the ...
1
vote
2answers
6k views

SQL Server : Is it possible to have a linked server link to another linked server?

Hello to all and thanks ahead of time for taking the time to look at my question! I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2. ...
3
votes
1answer
607 views

Applying user-defined fields to arbitrary entities

Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
4
votes
2answers
680 views

Execution plan results for sp_executesql suggests adding missing index for already existing index

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
0
votes
2answers
3k views

Save data to variable and use it in stored procedure

I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this: SELECT Text FROM MyTable WHERE Date>= @userDate; This works, ...
1
vote
2answers
609 views

How to connect to SQL Server Analysis Server via Management Studio command line

I can connect SQL Server Management Studio to a SQL DB by passing command line parameters like this: "C:\Program Files (x86)\....\Ssms.exe" -E -S SERVERNAME -d DB_NAME -nosplash (Obviously -E is ...
3
votes
2answers
3k views

Merge two records in the same table, keeping foreign key relationships intact for both

I have a table, called SITES, that has three columns, lets say it looks like this: ID Name Path 1 Google http://www.google.com/ 2 Microsoft http://www.microsoft.com/ I also have ...
4
votes
1answer
873 views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
0
votes
1answer
766 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
2
votes
1answer
69 views

Is it possible to determine what part of a stored procedure is currently executing?

Say I have a stored procedure like this (assume that the table schemas are fine): CREATE PROCEDURE p_MyProc AS INSERT MyTable SELECT Col1 FROM Table1 INSERT MyTable SELECT Col2 FROM Table2 INSERT ...
2
votes
1answer
269 views

tempdb thrashing and mirroring permissions (SQL Server) [closed]

We have a client site that uses SQL Server. The site this morning was slow, and we realised that the primary database server was responsible for a lot of disk activity on tempdb (Server 2008, SQL ...
4
votes
3answers
2k views

Cannot create login on cluster

I have a SQL Server 2008 R2 cluster on Win2008 R2. I am unable to add a login for the service account. Any other login I add to this instance works fine and I have been able to add the same service ...
2
votes
3answers
23k views

Unable to start SQL Server Instance 2008 R2 - DB file corrupt

I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is " The log scan number passed to log scan in database ‘master’ is not valid. This ...
5
votes
2answers
4k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
2
votes
1answer
947 views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
3answers
1k views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
4
votes
1answer
2k views

SQL column goes from first to last position in output

I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order. This is ...
1
vote
1answer
4k views

Full-text search in SQL Server on multiple tables fails

tl;dr version: A full-text search using FREETEXTABLE that worked fine on one column in one table produces 100% false postitives and 100% false negatives when the same steps are followed but the ...
3
votes
2answers
5k 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 ...
4
votes
3answers
11k 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 ...
1
vote
1answer
902 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
1
vote
2answers
1k 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 ...
2
votes
1answer
857 views

Error 18456 trying to connect

Trying to connect to SQL Server (SQL Server 2008 R2) installed on Windows Server 2008 R2 results in the following error message (according to ERROLOG): Error: 18456, Severity: 14, State: 38. ...
1
vote
3answers
4k 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 ...
0
votes
0answers
401 views

Real time replication issue - can't use transactional replication

We have an existing Microsoft SQL Server 2008 R2 database that we need to real time replicate to an identical off-site live web database. Transactional replication and merge replication will not be ...
1
vote
1answer
2k 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 ...
5
votes
1answer
869 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. ...
3
votes
1answer
144 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
1answer
76 views

System test Database user management

We are in development stage and to be able to give the developers an integration test environment, we need to give them access to the database, they can share and test their code in the system ...
4
votes
2answers
5k views

The best way to rename the tables after development completed

What is the easiest and most reliable approach to rename the database tables and columns in SQL Server 2008 r2? We have finished development and for some reasons, we are required to rename some ...
6
votes
2answers
1k views

What characters are word breakers in English for SQL Server 2005 and 2008 R2?

I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ...
0
votes
2answers
215 views

Auto Script DB Objects for SVN

My Boss requires that we keep up to date scripts of all database objects in svn. This results in constantly trying to find the object in the current script and copy and pasting the changes. Is there ...
5
votes
1answer
840 views

Raising an error with a system message_id in SQLCLR

If call CREATE DATABASE foo twice I get the error message: Msg 1801, Level 16, State 3, Line 1 Database 'dropme' already exists. Choose a different database name. The error message is listed in ...
3
votes
1answer
1k views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
2
votes
1answer
120 views

Is it worth to update to an x64 environment?

Situation We have 10 to 20 Tomcat web applications with hundreds of concurrent users per application on a server. Each Tomcat application connects using its own user to a database on Microsoft SQL ...
4
votes
1answer
204 views

Security Question involving Sys tables

We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this ...
6
votes
2answers
8k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on ...
4
votes
2answers
140 views

index maintenance strategy if few insertion points relative to # of rows

In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
14
votes
3answers
5k views

SQL Server database synchronization

Problem definition Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
2
votes
2answers
1k views

SSRS Report dependencies

Is there an automatic way to track reports and their dependencies for SSRS? What I want is a map/tree where I can see whether a report depends on a other report or subreport. So far we did this by ...
1
vote
3answers
919 views

SQL Server 2008 R2 Remote Connection issue

I have installed SQL Server 2008 R2 in mixed mode, but currently issue is I can't use SQL through ip nor ip with port, even I have enabled TCP/IP and open port in firewall too. I tried to telnet ...
3
votes
2answers
1k views

User can alter procedures without permission

I have a sql server user that our application is connecting as. The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use. ...
28
votes
4answers
4k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
2
votes
1answer
88 views

Differential backup successfully completes without taking the backup of few databases

I have differential backups for multiple databases configured to run through a stored procedure. Sometimes one or more databases have not been backed up, even though the job was "successful." I'm ...
2
votes
0answers
273 views

Stored procedure doesn't return results or any messages when executed as other privileged user [closed]

I have many stored procedures with... EXECUTE permission given to role XXXUser. Login XXXLogin is created and assigned to the role of XXXUser There is one particular stored procedure that I have ...
8
votes
1answer
2k views

Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

I am looking at snapshot replication between two servers. Here is what I have: 500GB database ~500MB nightly bcp loads ~50MB daily transactions I was asking other DBAs in the company about which ...
5
votes
2answers
4k views

Automate daily backup of database

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I ...