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

learn more… | top users | synonyms

1
vote
1answer
28 views

Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

My requirement is to create the automated email as ( A formatted HTML Email via Database Mail) The basic requirement has been completed where when the Job is triggered it inserts the data into a Table ...
2
votes
0answers
15 views

SELECTs in Database Change Log and Source Control

We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right, ...
0
votes
1answer
20 views

How to list the permissions on User-Defined Table Types?

I am using some user-defined table types. They are very useful. you can see information about them by running this simple script: SELECT o.* from sys.table_types o I have a procedure that shows me ...
1
vote
1answer
33 views

need to replace the blank value as 0

need to replace the blank column into 0 for example the table is i have tried this query to bring the output but it is taking more number of time because i have put this table in to procedure ...
0
votes
1answer
27 views

databases cross references baselines

We have on several databases on same instances, some stored procedures have references to tables on other databases. We are in the middle of a big project to take our database under source control. ...
-1
votes
1answer
83 views

EXCEPT operator vs NOT IN

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.
1
vote
0answers
45 views

SQL Server 2008 R2 Service pack 3 won't recognize all instances

I have two instances on one of my servers, and when installing service pack 3 the GUI installer does not recognize one of the instances. Server is Windows Server 2008R2 Enterprise x64 SP1 Both ...
0
votes
1answer
25 views

Forget login from connection drop down menu [duplicate]

Recently I logged myself as a different user (testUser) but now that user seems to be persisted in the drop-down menu of connection dialogue box and I couldn't find a way of getting rid of it. Even ...
1
vote
2answers
55 views

PAGEIOLATCH_SH and LCK_M_IX wait type

I'm seeking for a resolution into wait types I observe on one of my servers. I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX ...
4
votes
1answer
115 views

DBCC DBINFO showing wrong information

Though DBCC DBINFO() is undocumented, but we all know that dbi_crdate: Database creation datatime dbi_dbccLastKnownGood: the completion time of the last 'clean' run of DBCC CHECKDB For one ...
0
votes
0answers
28 views

SQL Query to get the Sales Qty of the Item and the its Returned Quantity for past 6 Months

I want retrieve the Sales Qty and return qty of the Items in the following format for the past six months. From two different tables,I TxnInvoice Table :Invoice header TxnInvoiceLine Table :Invoice ...
0
votes
0answers
14 views

SQL Server Mirroring and automatic failover - why don't all DBs failover to the mirror?

On SQL Server 2008 R2, I'm mirroring 20 databases on my SharePoint SQL Server with a witness, high-safety. All mirrored DBs have the default mirroring_connection_timeout = 10. And, occasionally, a ...
0
votes
0answers
12 views

SSIS - This error is thrown by Connections collection when the specific connection element is not found

I am writing a package to upload result of query to FTP as CSV file. Package is working fine on local machine, but after transferring .dtsConfig and .dtsx on another environment it throws following ...
-3
votes
0answers
22 views

Clearing log files

How to clear/Shrink the Log file of a database when the database is in stand by mode. How to make a database from recovery mode(normal mode/with recovery) to standby mode.
2
votes
1answer
130 views

Checkpoints and Logging

I'm working on getting full grasp of logging architecture. Please correct me if I'm wrong here: I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the ...
0
votes
0answers
42 views

Size of Table Now vs 2 Years ago [closed]

I am trying to Analyze the growth of the data/tables within a database. I would like to analyze how much Data is currently present in a table and how much there was 2 years ago. There are date fields ...
1
vote
0answers
31 views

SQL Server spending a lot of time creating sp_dbmmonitorupdate

I have an SQL Server 2008 R2 Standard with around 80 databases, 18 of which are a synchronous mirror primary. I'm having a lot of complaints about database speed lately, so I've started digging with ...
4
votes
0answers
142 views

There is insufficient system memory in resource pool 'internal' to run this query

One of our production servers is reporting an error in the log Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'internal' to run this query. I ...
0
votes
1answer
19 views

Reviewing Stored Procedure template for handling transaction?

I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction. I have a stored procedure to log error in catch block in a table. How can I improve my ...
1
vote
2answers
62 views

Find out when your index was last rebuilt / reorganized

I've recently come across a database, which has very high fragmentation on large tables. Page count > 1'000'000 with fragmentation 99,... I would like to know how long this table has gone without a ...
2
votes
1answer
290 views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
-1
votes
0answers
42 views

SQL Server is very slow [closed]

In company with 30 clients(computers), after 3-4 hours working with financial software, SQL Server is very slow SQL Server can not free used memory and the query execution time is very slow but after ...
0
votes
1answer
42 views

Upgrading SQL Server 2008 R2 Standard edition to SQL Server 2008 R2 Enterprise edition what is the process and the impact?

We are using SQL Server 2008 R2 Standard edition with Visual Studio 2010 (Web API). Also we have created SSIS packages using business intelligence. If we decide later on to upgrade to SQL Server 2008 ...
0
votes
1answer
40 views

Columns into rows and rows into columns

I need to do this table transformation, but its little hard for me. I have original table wich looks like this and i need to transform the table into this How can i do that ? Here i prepared ...
1
vote
1answer
45 views

Combining log shipping and regular log file backups

Following on from my earlier question, I'm trying to set up a combination of Log Shipping and database backup. What I have in place is: Log Shipping set up between my two servers, to run every 5 ...
1
vote
1answer
42 views

Replication consistently fails to bring new rows to subscriber

Short description of the problem: Initial synchronisation to the subscriber works fine. Subsequent synchronisations fail to download new rows. Re-initialising the subscription "fixes" the issue and ...
0
votes
0answers
10 views

SQL Server 2008 R2 - Replication - Multiple Publications on a single database

I have hit a problem and I need an idea of where to look or how to resolve. I have setup a publication and 2 subscriptions to it, 1 running on a schedule and the other running continuously. I need ...
-1
votes
1answer
84 views

SQL Server: performance of IDENTITY vs INT as primary key

The most important table of my database is about 300,000 records, and growing. More than 20 tables have a FK to it. Its primary key is a number, but for historical reasons is defined as nvarchar(6) ...
0
votes
0answers
18 views

Huge Analysis Services log file: msmdsrv.log

I've just discovered our prod analysis services is writing to a log that is 1.6 GB in size. I was able to get a copy of the log file saved off to an alternate location and open it in WordPad ...
0
votes
0answers
17 views

Problem when querying audit file when audit is enabled

I'm trying to audit DML operations for a specific principle on a database. For this, I configured Audit under Server -> Security and Database -> Database Audit Specifications and enabled both of them. ...
2
votes
2answers
132 views

Performance Order By large table with Join

Hello I have a table with 206,902 records and I am struggling to improve my query: SELECT TOP (50) [ID] ,[Title] ,[Modified] ,[Created] FROM [Data] Inner Join [Permission] On ...
2
votes
1answer
54 views

How to get all logged in users to SQL Server database for this week?

I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of ...
0
votes
0answers
45 views

SQL Server : error 15208 Level 16, State 1, Line 1

Trying to Backup a certificate from SQL Server causes this error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format. ...
0
votes
2answers
54 views

SQL Server 2012 Very Slow to Consume Memory

I have a bit of a conundrum. I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, ...
0
votes
2answers
33 views

Error after removing sql server 2008 R2 Express edition and instaling Entreprise edition: Unable to connect to MyHost\MSSQLSERVER

So I'm having this issue after installing MS SQL Server 2008 R2 Enterprise edition, I'm getting this message error: Unable to connect to WIN7 \ MSSQLSERVER A network-related error or ...
1
vote
1answer
41 views

Getting Time Duration

I have a table like below: I want to get time duration for each record which SPEED is greater than 100. I written a function for that, it’s working fine , but my problem was i have millions of ...
2
votes
0answers
66 views

Impact of Service Broker on Server Performance [closed]

For DDL auditing we are thinking to use service broker, but I want to check few parameters before deploying it to a production server, where there are about 200 DBs and Standard Edition of SQL Server. ...
1
vote
2answers
210 views

SQL Server Grant execute on all stored procedures without ddl permissions

I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored ...
2
votes
4answers
293 views

Saving a variable globally in SQL server it self and use it when required

We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE. So I planned to create a stored ...
0
votes
1answer
40 views

Breaking changes between 2008R2 RTM going to 2008R2 SP3?

I have a few A/P FCIs running 2008R2 RTM, and I'd like to upgrade the passive node to SP3, fail over, and then upgrade the formerly passive node. Has anyone else attempted this? Are there breaking ...
0
votes
1answer
31 views

Which of these build numbers will tell me what version of SQL Server is installed on a server?

The main Microsoft SQL Server 2008 R2 (64-bit) doesn't contain a version number in Programs and Features in Microsoft Windows Server 2008 R2: To me it almost looks like I have SQL Server 2008 R2 ...
0
votes
1answer
41 views

View permission to SQL Server logs on SSMS GUI

I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. I was just wondering if this was possible? Thank you.
2
votes
1answer
29 views

Copy database wizard-generated job blows up log file; alternative?

I've recently upgraded our deployment environment to include a staging server. Said server has a database on the same server as our production database. I am trying to set it up to copy data from the ...
1
vote
1answer
36 views

Cannot remove char 0x00 from NVARCHAR column

So , I know all about the Replace function and a bug with char(0). I have a column (NVARCHAR(128)) that has some CHAR(0x00) from a bad import. I'm using SQL Server 2008 R2 The collation for the ...
0
votes
0answers
18 views

Problem with remote login for SQL user

We have a SQL Server SQL and a web server WEB. In SQL, we have a SQL user named test with password test with correct permissions set up. If we run SSMS on SQL and login locally with the test user it ...
0
votes
1answer
45 views

SQL Server Developer Edition Downgrading to Older Developer Edition

I have seen answers for questions regarding downgrading from different editions (e.g. - Enterprise to Standard), but is anyone familiar with downgrade policies for SQL Server Developer editions to ...
0
votes
0answers
45 views

SQL Server : how to decide if FULL backup or DIFFERENTIAL

The following scenario (on SQL Server 2008 R2 and 2012 servers) A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only ...
1
vote
0answers
20 views

SQL Server 2008 user and group permissions behavior weirdness

I have a strange situation: An AD user account has a login to a SQL Server 2008 database without any roles assigned to that user. That same database has a login for an AD group with ...
1
vote
1answer
40 views

What happens if an index build is cut half way through?

Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 ...
2
votes
2answers
73 views

What is difference between is_unique column and is_unique_constraint columns in sys.indexes

We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns Create statement for both types of ...