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

learn more… | top users | synonyms

0
votes
0answers
28 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 ...
1
vote
1answer
200 views

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

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
104 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 ...
4
votes
1answer
146 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
65 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 ...
0
votes
1answer
75 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
0answers
41 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 ...
10
votes
1answer
444 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, ...
0
votes
1answer
49 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
57 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. ...
2
votes
1answer
140 views

Duplicate login details per user in 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 ...
0
votes
1answer
83 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 ...
0
votes
1answer
51 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 ...
1
vote
2answers
67 views

Merge Table Row Values to Header of Another Table

Table 1 id | Field | Values ---------------------------------------- 1 | 'Field01' |Name 2 | 'Field02' |Age 3 | 'Field03' |Gender 4 ...
0
votes
0answers
63 views

Why would call to scalar function inside a Table Value Function be slower than outside the TVF?

I am writing a Table Value Function, calling the function takes 10x as long as directly running the code. I traced this to a call to a multi-line scalar function inside the TVF. The call to the ...
1
vote
1answer
47 views

Print, transaction and cursor performance in Transact-sql

I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact. The algorithm is O(n^2). The ...
1
vote
2answers
74 views

How can I generate an .mdf file containing a subset of my tables and rows?

I need to provide some data to an organisation (lets call them DataCo) that we're working with. Since we're both SQL server shops, my counterpart at DataCo requested an .mdf file. However, our ...
2
votes
2answers
177 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 ...
1
vote
0answers
88 views

MYOB ODBC crashes SQL Server

A sister company of ours uses MYOB AccountRight Enterprise and has the read-only ODBC license available. The thing that is annoying them the most is what apparently annoys many a MYOB user: its ...
4
votes
1answer
417 views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
0
votes
0answers
43 views

Find all possible combinations for each group [duplicate]

I have a table like as: ________________________ |ID| Table1ID| Table2ID| |1 | 1 | 1 | |2 | 2 | 1 | |3 | 2 | 2 | |4 | 3 | 1 | |5 | 3 | 2 | ...
0
votes
1answer
61 views

how to restore?

I am not sure if this has been addressed already. I have been a long time reader but this is my fifth post. I need assistance with a database restore solution. I receive a full BAK and the transaction ...
0
votes
1answer
50 views

Does SQL server 2008 log instance level security permission changes

I just recently logged onto one of our Test environments to find i did not have permissions, turns out my instance security Login had been disabled I have a feeling towards whom may have done this ...
2
votes
3answers
54 views

Monitor SQLAgent

I detected that SQLAgent was not running on one of our servers due to a unexpected memory leak error that caused SQL Agent to shutdown. There were not alerts configured so one main job that was ...
0
votes
1answer
92 views

Attempt to add DQS Cleansing Connection Manager to SSIS results in error

I reopened an SSIS project in SSDT-BI (with VS2013 Shell) which had been dormant for ~3 months to see the dreaded red "x" had appeared on my DQS Data Flow Component: I attempted to replace the ...
0
votes
2answers
50 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
1
vote
2answers
669 views

How do you create a job in SQL server 2008 R2 that will clear transaction logs?

I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has ...
-1
votes
1answer
35 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
7
votes
1answer
130 views

Lower performance on newest servers

We have several db servers on production, 4 of them with very similar hardware configuration. Dell PowerEdge R620, only difference is that 2 newest ones (bought and configured 3 months ago) have RAID ...
0
votes
1answer
58 views

Accessing Master database data into my database table

my database name LCMC i put a trigger on one table on update. and i want to insert the last 15 executed queries into a table. last executed queries are in the MASTER database as SELECT top(15) ...
1
vote
1answer
92 views

SQL Server 2008 R2 on production — is it worth to upgrade to SP1/SP2

One of my company's mission-critical database is running SQL Server 2008 R2. SELECT serverproperty('ProductVersion') returns 10.50.1600.1. So, yeah, RTM. Now, long story short, this database is not ...
0
votes
0answers
667 views

SQL Server 2008 R2 backup fails to restore on SQL Server 2008 R2 SP2

My DR SQL Server got automatically updated by Windows update from 2008 R2 to 2008 R2 SP2 and since then I can't restore backups from my production machine which is still on 2008 R2. 10 percent ...
2
votes
1answer
925 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
1
vote
0answers
42 views

Low batch Batchrequests/sec

I am running this query to do massive deletion in my database. I am looking at Batchrequests/sec and it is quite low (like 15% of what my server can handle) Is there any way to rewrite this query to ...
0
votes
0answers
721 views

Login failed for user 'x'. Reason: Could not find a login matching the name provided

I get this error Login failed for user '****'. Reason: Could not find a login matching the name provided. [CLIENT: 10.4.3.5] Right around 3 AM ish but the application connecting to sql server ...
0
votes
1answer
340 views

Pivoting some row data into columns (SQL Server 2008 R2)

Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another. Full-Disclosure: I did ...
1
vote
1answer
98 views

Am I using the correct data types on SQL Server side to call an Oracle 10g function?

When calling an Oracle function with the following in parameter types NUMBER(7,0) VARCHAR2(8 BYTE) VARCHAR2(4 BYTE) DATE and return type NUMBER(14,5) from SQL Server 2008R2 via a linked Oracle ...
0
votes
2answers
1k views

Reducing database size to restore to SQL Server Express

I have a SQL Server 2008 R2 database, about 15 GB. I want to copy it for a partner, who is using SQL Server 2008 R2 Express. I deleted many tables and rows, and now I am sure that the data is ...
0
votes
0answers
51 views

table check constraint, greater than > not accepting

CONSTRAINT CK_BABY_YOUTH_CHILD CHECK (case when Passenger_Type='Baby' then Reference_ID >=0) This is my table check constraint, i have no idea why sql server is not accepting ">". Please ...
2
votes
1answer
154 views

How to find number of days since beginning of fiscal month?

This relates to my question How to find XXth day of previous month in SQL server? The fiscal month starts on 26th of each month. I need to find out from any date: The date of the start of the ...
2
votes
3answers
252 views

Using expressions in the SELECT list to thin down a CASE statement

I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this: select Column1 = case when (Something = 2) and ...
0
votes
0answers
55 views

Installer Error while Installing SQL Server Reporting Services

I have a Windows 2008 R2 Server 64bit, I have already installed SQL Server 2008 R2 SP2 64bit Enterprise Edition, Which is working fine, without any issues. Now when I am trying to install Reporting ...
0
votes
1answer
149 views

Lost DMK encryption password for master database

Back story: My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, ...
3
votes
1answer
260 views

How to find XXth day of previous month in SQL server?

I have 2 parameters, an input date (DATETIME) input day of month (TINYINT) If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the ...
2
votes
0answers
82 views

Avoiding Functions() in the Where clause

Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query: (@p__linq__0 nvarchar(4000)) SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1] ...
2
votes
2answers
65 views

Specific user can't view database contents

I have a particular user, let's call it TheUser. This user is able to connect to my SQL Server instance and view a list of the databases. However, upon expanding a database to view tables, stored ...
0
votes
1answer
67 views

How to identify unused tables

We are planning to merge 2 or 3 region databases who are using almost the same version of SQL Server 2008 R2, have exact tables and most stored procedures for our end users reports.. in the process, ...
1
vote
2answers
100 views

Is there a more efficient way to run this query for first/max transaction?

For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only ...
-1
votes
1answer
63 views

Does changing the collation of a column change TIMESTAMP (rowversion) values?

I need to run ALTER TABLE ... ALTER COLUMN ... COLLATE Czech_CI_AS ... for multiple columns in a database. All those tables have TIMESTAMP column which is used for database synchronization. Will ...
1
vote
2answers
130 views

Reduce space used on C drive

I have installed SQL Server 2008 R2 on my laptop, under 32-bit Windows 7. The SQL Server installation is using approximately 30GB of space on my C: drive. Why is it using so much space, and how can I ...