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

learn more… | top users | synonyms

0
votes
1answer
30 views

The message “Running the Merge Agent process would exceed the maximum number of processes allowed” appears, but it doesn't seem likely

The full text of the message is "Running the Merge Agent process at this time would exceed the maximum number of processes allowed by this publication. The process will run when resources become ...
2
votes
2answers
72 views

SELECT permission denied trying to import data into Excel

We recently migrated to a newer version of a program and migrated its database from SQL Server 2000 to SQL Server 2008 R2. We had some Excel files which ran a few custom queries to pull data. I ...
1
vote
0answers
37 views

Database backup hangs both in SSMS and XMLA

I am using SQL Server Analysis Services 2008 R2 Service Pack 3. My database was recently upgraded from Analysis Services 2005 and since that time I have been unable to do a backup either from SSMS or ...
1
vote
0answers
56 views

File group space problem

I'm having some problems with my database usage space. I have a database with autogrowth off and limited size to 150GB. When users try to import files, large databases or other massive transaction ...
3
votes
1answer
111 views

Get minimal logging when loading data into temporary tables

After reading The Data Loading Performance Guide I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. ...
0
votes
1answer
34 views

Stop agent job after executing for a certain number of hours

I have a scheduled job which runs nightly. I want it to stop itself at 07:00AM in the morning whether or not it is completed. I have seen some duplicates in the site but they don't exactly address the ...
1
vote
1answer
76 views

Possible to use a Trigger as failsafe for insert in two databases?

(This is for a project on MSSQL 2008R2 Server) I have been tasked with splitting a table into an internal (same db as before) and an external one (different server). Currently the idea is to read ...
3
votes
2answers
199 views

SET DATEFORMAT problems

I have some doubts about SET DATEFORMAT command. 1)Does this command is only used for inserting and updating date format and it does not effect the date format of that column? OR does this effect my ...
0
votes
2answers
39 views

Log Sequence Numbers in Full BackUp and Log Backup

My full back-up starts daily at 02:00 AM. It takes about 3 hours. I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours). I can verify the LSN sequences of T-log backups when I ...
0
votes
1answer
51 views

Cannot log in to SQL Server 2008 R2

I have problem with my SQL Server 2008 R2 instance. I want to log in using windows authentication and server name "local", but error 2 is returned. I've tried to login with a server name of "IP ...
1
vote
1answer
26 views

SQL Server configuring both UCP and MDW on a single server

I'm trying to configure both UCP and MDW on a sql server. I have already setup UCP and it created [sysutility_mdw] database. Now when i try to setup MDW it is trying to use sysutility_mdw database ...
1
vote
1answer
103 views

Query to convert the count to million or thousands

I am running a query on a database to return count(*) for number of rows. If the count is greater than million say 2,250,304, I want the query to return 2.25M. If the count is greater than 1000 but ...
1
vote
0answers
22 views

CHKDB is marked RESTORING

I am seeing this in my Microsoft SQL Server 2008 R2 (SP1) database log: The database 'CHKDB_dbname' is marked RESTORING and is in a state that does not allow recovery to be run. This occurred ...
1
vote
2answers
68 views

How do I return data from a table where the data is the max date of the previous month's data submissions (-1 of current date)?

I have a stored procedure where I select the latest/max submit_date data for a tracker_date and client from a source table called Tracker_Live, and insert it into a new table called ...
2
votes
3answers
218 views

Finding Database Default Locations

I am working on creating a script to restore database from a backup file to default location of an instance, which is set away from system databases installation folder. Currently, I'm using to find ...
-2
votes
2answers
72 views

Install SQL Server 2008 R2 with one click [closed]

How can I install SQL Server 2008 R2 without wizard, just install and voila, install on PC?
0
votes
2answers
62 views

Setting for default column type

Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead ...
3
votes
2answers
68 views

Is there a way to grant SELECT for every database (including new ones) at a server level?

I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically. Ignoring the obvious issue with that, I need to be able to ...
1
vote
2answers
82 views

Reuse a complex non-deterministic expression in SELECT and WHERE clauses

I have a query like this: -- This is inefficient, and may even be wrong if expression is nondeterministic SELECT TOP 10 team_name,score_points=<big expression> FROM teams WHERE <big ...
1
vote
2answers
102 views

Manipulate SQL statement before execution on server

Our databases are on SQL Server 2000. We are migrating to SQL Server 2008 R2. We want to update the compatibility level to 100. But the join operator \*= and =\* are no more supported with that ...
0
votes
3answers
45 views

Reset Key Names to Defaults?

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names. In SSMS is there any (preferably automatic) way ...
2
votes
1answer
116 views

SA Account has not been disabled

There has been a security audit for many of our sql servers and per script there are many findings. One of them says "SA Account has not been disabled" I know it can be disabled, but can it be ...
0
votes
1answer
73 views

How to get data from multiple tables in one row

I am looking for a way to get the table name and description field from all tables in my database that start with T_. What I have up till now is this: DECLARE @tables TABLE(table_name VARCHAR(100), ...
9
votes
3answers
202 views

What is a deterministic method for evaluating a sensible buffer pool size?

I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory ...
2
votes
1answer
47 views

How to programatically change the login audit settings?

Is it possible to change the login auditing settings of a SQL Server database by script, rather than through the Server Properties Security setting in the SSMS UI? I need to programatically change ...
3
votes
1answer
73 views

Controlled shutdown/startup of a cluster

My three node SQL Server 2008 R2 physical server cluster needs to be shutdown, moved along with its storage to a new location, and restarted once in the new location. My desire is to have a clean, ...
1
vote
1answer
82 views

Rebuild index in table with many values

I'm junior DBA ,have table with 11 million values ,one of the indexes is like : CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] ( [my_id] DESC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
186 views

SQL Server 2008R2 XML Column Index strategy for large tables?

I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
1
vote
2answers
232 views

Unable to connect to FTP server using “FTP Connection Manager”

I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log ...
1
vote
0answers
111 views

SQL SERVER 2005 slow Performance on Windows Server 2008

Recently i have upgraded my server to new one. But because of compatibility issues with my old Windows Server 2003 R2 32 bit, I have also upgraded it to Windows Server 2008 R2 64 bit. (i have ...
1
vote
1answer
71 views

Create nonclustered index on uniqueidentifier type

CREATE TABLE [dbo].[session] ( [id] [int] IDENTITY(1,1) NOT NULL, [user_id] [int] NOT NULL, [session_id] [uniqueidentifier] NOT NULL, [supervisor_id] [int] NULL, some columns .. ...
0
votes
1answer
54 views

why is my trigger called even when the underlying table didn't change?

I have a quite basic trigger defined as such: CREATE TRIGGER dbo.foo ON dbo.bar FOR INSERT, UPDATE, DELETE AS ... I also have a stored procedure which at some point does something like this: ...
0
votes
1answer
17 views

I couldn't see Conflict tables in replication monitor SQL Server 2008 R2 in Merge Replication

I work with merge replication in SQL Server 2008 R2. In the replication monitor I can't see conflict table, I must to see conflict tables in "MSmerge_conflicts_info", please help me. How to see ...
0
votes
1answer
45 views

SQL Server FILESTREAM access denied

I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain. From my machine, I am able to perform any and all operations using ...
1
vote
0answers
83 views

Application running slow when querying against SQL Server 2008R2

I'm having an application that is starting to run really slow when inserting and querying data from a SQL Server. The database is on a dedicated virtual box with 10 GB ram, AMD Operon 6134 processor ...
1
vote
1answer
151 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
18 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, ...
1
vote
1answer
74 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
45 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
39 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
107 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
1answer
676 views

Setup error :Attempted to perform an unauthorized operation [closed]

Below is the detailed error message. I can't proceed with installation for the last one week. I've given full rights to registry keys that are associated to SQL Server but nothing has worked out. For ...
1
vote
0answers
320 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
30 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
125 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
151 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
53 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
1answer
43 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
218 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 ...
2
votes
1answer
146 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 ...