This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

3
votes
1answer
65 views

SQL Server cluster resource won't come online unless its on port 1433

The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 ...
0
votes
1answer
21 views

SQL Server: compare names ignoring non-alpha

I have a database table with a text column "title". New rows are inserted all the time, and every time I would like to check for potential duplicates on this column. I define duplicate by "having the ...
2
votes
2answers
166 views

Table with 4 million records - different size and performance in development and production

At my company we use SQL Express 2008 R2, which supports databases up to 10GB. We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in ...
1
vote
1answer
27 views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...
1
vote
1answer
176 views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...
2
votes
2answers
71 views

Shared Memory with Microsoft Hekaton

Does the Microsoft SQL Server 2014 In-Memory Engine supports Shared Memory access like Oracle TimesTen? I only found that there was a protocol with this name in SqlServer2008 but i found nothing for ...
4
votes
1answer
67 views

Find reason long running stored proc SQL Server

I have a stored proc which is called every 2.5 seconds by a program, 24-7. This proc normally runs under the 10 milliseconds. A few times a day (3 -5 times) it takes around 42953 milliseconds to run ...
2
votes
1answer
2k views

How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

i am executing query like this on sql server 2008 R2 DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"' ...
2
votes
1answer
2k views

How to schedule a weekly backup of a database only if it has been changed since last backup

I´m new to SQL Server administration. I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary). Is there ...
1
vote
1answer
64 views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
3
votes
1answer
73 views

SQL Server SSL connection

Is it possible if I wanted to set up SQL Server 2008 R2 instance so that it only accepts SSL Connection? I am getting different answers and based on my testing, it will accept non-SSL connection even ...
2
votes
0answers
25 views

Unable to Update on SQL Server 2008 R2

This is a bit strange so bear with me please. After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication ...
1
vote
1answer
110 views

Database Mirroring and Replication in SQL Server 2008 R2

I have configured the mirroring and replication between 4 servers (A,B,C,D).i.e, Mirroring between A to B and C to D, Replication between A and C. The configuration was a success and I am able test ...
2
votes
0answers
80 views

Why does a simple select query produce a deadlock?

I am trying to analyze randomly occurring deadlock problems on an SQL Server 2008 R2 database. The deadlocks occur in very rare cases when SSRS tries to access one of 5 different stored procedures. ...
1
vote
1answer
103 views

Why dropping all foreign keys from a script is not working? [on hold]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
0
votes
1answer
20 views

Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database. I have two Options. Option 1: One Physical Harddisk and then Multiple Virtual hard ...
0
votes
1answer
1k views

SQL Server 2008 R2 Drops Connections when Anti-Virus Scan is running

I have a SQL Server 2008 R2 Enterprise (SP2) cluster (2 nodes Windows Server 2008 R2 SP1, NetApp Shared storage). There are 5 instances all running on this cluster in an active passive configuration ...
2
votes
2answers
88 views

CHECKDB fails on MSDB and Master system databases

I get the following error within my maintenance plans: Executing the query "DBCC CHECKDB(N'master') WITH NO_INFOMSGS " failed with the following error: "The database could not be exclusively ...
0
votes
1answer
60 views

Configuring Reporting Services - ExecutionLogDaysKept

Is it possible to set Reporting Services options when Reporting Services is not installed on a server? Is this how a SQL Server instance used by SharePoint works? I'm a new DBA (very junior...), and ...
0
votes
0answers
13 views

Run a script before UnitTesting

The Test Initialize script in the SQL Server Unit Tests runs before each test methods and Test Clean up runs after each test methods. So for example I have this structure UnitTests -- Main ...
2
votes
1answer
269 views

How to get SQLCMD to output errors and warnings only?

How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters? I essentially dont want information based messages to be output.
0
votes
1answer
39 views

Find Without primary key Table in SQL Server 2008 R2? [duplicate]

This is my query to find out primary key, foreign key in SQL Server 2008 R2: select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' Know I need to select without ...
1
vote
1answer
152 views

What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? [on hold]

The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank. They had ...
2
votes
0answers
37 views

Implementation of SSL ON SQL SERVER DEFAULT INSTANCE

We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS. We imported the same certificate on the SQL Server machine. However, when trying to ...
3
votes
3answers
4k views

How to get last identity row Inserted when using instead of trigger

When I inserted on tables that exists instead of triggers on those. @@Identity , IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get last identity of inserted row
9
votes
1answer
121 views

Can you use COUNT DISTINCT with an OVER clause?

I'm trying to improve the performance of the following query: UPDATE [#TempTable] SET Received = r.Number FROM [#TempTable] INNER JOIN (SELECT AgentID, ...
5
votes
2answers
268 views

Is there a limit to how many credentials SSMS can remember?

Using SQL Server Management Studio that came with SQL Server 2008 R2, I make connections to the same server using a number of different sets of SQL Server Authentication credentials. On my development ...
4
votes
1answer
42 views

Encoding Debug UTF8 & Latin 1

We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2). For example we have strings like 'WilcoxonÃ'. We got this data into ...
0
votes
1answer
18 views

SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

I've been battling for hours to add a date to a CSV name: For brevity, Im just taking the first character of the date here. In SQL Command, this works: sqlcmd -S localhost -d master -E -W -w 999 -s ...
4
votes
1answer
519 views

SQL Server 2008 R2 Restore constantly hanging

I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the ...
0
votes
1answer
79 views

Duplicate login details per user in SQL Server 2008 R2 Error Log

Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However i have a scenario that i need help with - for some reason ...
1
vote
1answer
88 views

distrb.exe & Logread.exe using huge SQL.txt file in C:\Users\sqlsrvacct\AppData\Local\Temp folder

I have the following situation: SQL server 2008 R2 SP2 I have both the Distribution and Subscription database in this server. LogReader agent and Distribution agent are running in continuous mode the ...
1
vote
0answers
36 views

What will do After Press F5? [closed]

select * from Employee; Name id Col1 Col2 Row1 1 11 1 Row2 2 12 3 Row3 3 13 30 Row4 4 14 44 Above is my table After PRESS F5 What process is running in Background ...
2
votes
1answer
314 views

General access denied error while using import data in SSMS

I am using SQL Server Management Studio with the following version information below: Microsoft SQL Server Management Studio 10.50.4000.0 Microsoft Data Access Components (MDAC) ...
1
vote
1answer
306 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
1
vote
2answers
31 views

Garbage DB Objects Cleansing

How do I find in use db objects from all application pages is there any tool or trick I can use, just like we do search in Visual studio for an object in entire solution
0
votes
1answer
31 views

SSMS 2008 / 2014 showing different values for TempDB file sizes

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes ...
2
votes
1answer
40 views

SQL Server 2008 R2 - remove line below heading (which has ----,----) - dont have SSIS

I have found this line of code online sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv" This is used in a SQL job, to export to a CSV file. ...
0
votes
1answer
44 views

Slow SQL Server authentication, but fast SAN, fast querying?

I am asking you after days of research but I think I don't have required skills to understand what is happening. I have a slow authentication on a SQL Server 2008R2, which leads to a timeout for the ...
3
votes
1answer
137 views

ProcessID Owner with third party app

I am having problems trying to audit a third party app with a SQLServer backend. The app manages users and user access via a SQL table, using a single SQL login to access the databases. I am trying ...
0
votes
2answers
80 views

SSIS Package truncation and possible data loss (error code: 0xC020901C)

Good morning all, It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out. Here is the thing. I have created an SSIS package to import data from ...
1
vote
1answer
117 views

List of Expired Merge Subscribers

I have a setup of merge replication. Sometimes, subscribers will not be able to sync within the retention period set in the publication. How can I get a list of expired subscribers to a merge ...
0
votes
0answers
32 views

Logging and Error MS SQL Server 2008 R2 [closed]

Currently there are multiple SSIS packages, batch jobs etc. I'm looking for best practices that could be followed for normal Logging and Error Logging for SSIS packages . I understand it is a ...
2
votes
1answer
2k views

Reconcile users with logins using 'sp_change_users_login'

Sql Server 2008 (and 2005): I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, ...
4
votes
2answers
2k views

Extracting a field from RESTORE HEADERONLY in sql server

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '<path to .bak file>' works fine in query ...
1
vote
1answer
1k views

Creating Excel files in SSIS based with a predefined format

I need to create a statistical report in Excel and wondering if I can use SSIS to read data from the database (calculate the aggregated numbers) and load them into an Excel file with a specific ...
4
votes
1answer
154 views

Fulltext stoplist replication

In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to ...
2
votes
1answer
7k views

What is different between SQL-Server versions 2008R2 and 2012

Dear SQL Server experts, Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft ...
0
votes
1answer
44 views

I see a constraint in my table thats not in INFORMATION SCHEMA?

When I expand the database "Constraints" object, I see 2 of them named DF_DocumentD_Docum_3F466844 and df_documentdistqty, but when I select to view everything from the "Information Schema.TABLE ...
0
votes
1answer
47 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...