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

learn more… | top users | synonyms

0
votes
1answer
25 views

Missing Database Mail Configuration Wizard in SQL Server 2008 R2

I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.
0
votes
0answers
19 views

How to delay replication start

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The ...
3
votes
1answer
55 views

Total Fragments 99% and I cant get it to stay down

I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the ...
1
vote
1answer
26 views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
0
votes
1answer
28 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 ...
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 ...
4
votes
2answers
82 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 ...
4
votes
1answer
72 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
0answers
28 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
109 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 ...
2
votes
0answers
82 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. ...
0
votes
1answer
22 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 ...
1
vote
1answer
69 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); ...
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 ...
1
vote
1answer
153 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
38 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
43 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
19 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
40 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
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 ...
9
votes
1answer
128 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
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
41 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
82 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 ...
0
votes
0answers
33 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 ...
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 ...
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 ...
1
vote
2answers
43 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
38 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
22 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
39 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
90 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
0answers
31 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
200 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
58 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
33 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
41 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
22 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
43 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
57 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
33 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
120 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
37 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
69 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
144 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
368 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
38 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
39 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
46 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 ...