SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

2
votes
1answer
57 views

Query Each Database in A Table [closed]

We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the ...
-1
votes
1answer
48 views

SQL command or trigger needed to capture changes in tables [duplicate]

I want a query or trigger that will identify what changes done in the database tables from the Third Party Software such CMS etc. I want to check that which data is modifying with time. There is no ...
6
votes
4answers
6k views

“TASK MANAGER” process is taking over a single-user mode database. What is it?

This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening. I have a sporadic problem, when upgrading SQL Server in ...
0
votes
0answers
32 views

Case Statement to Identify Multiple Errors

I'm trying to write a statement that identifies any of 200 potential errors / illogical combinations in a data set for each line item. My first thought was to write a case statement for each error ...
1
vote
1answer
76 views

Can I bring a warm standby SQL Server database online and then return to standby mode?

What I'm trying to do falls outside of the normal SQL Server work flow so might be impossible. I'm implementing a system that serves out copies of a large production database to multiple test / dev / ...
1
vote
1answer
93 views

Issues installing patches on SQLserver 2008

There are multiple issues with a server I manage, we cannot start management studio on it and when we try to install patches to SQL Server the setup never shows up. I run the patch as an ...
3
votes
2answers
1k views

Can I get notification when event occur?

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example, If I decide ...
0
votes
1answer
34 views

Sql Server Security Scenario

I need some advice/help... I need to implement database security in some Databases but I don't know how to do that with this given scenario... Using LDAP Auth We have Table1 In Table1 one we have ...
3
votes
1answer
1k views

Replicating Triggers

I am doing some analysis on merge replication and triggers for a project. I have two tables, Product and Audit that I am replicating. I have an update trigger on the Product table which is ...
0
votes
1answer
59 views

Error from Program Compatibility Assistant when installing SQL Server 2008

I am new to this so please do not mind if this is dumb question. I am receiving an error while installing SQL Server 2008 on Windows 8.1 I have .net 4. I have windows installer 4.5
0
votes
1answer
52 views

SQL Server Backup - move to new server - Media Failure

I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise. We want a local duplicate of the DB to test and build a new report. Backing up the relevant DB, and then ...
0
votes
1answer
37 views

A login is a member of db_datareader and public roles only, but it is not read-only

I'm an accidental DBA in a small company that doesn't have a dedicated DBA. I need to set up a read-only login/user for SQL Server 2008 database using SQL Server authentication that could be used by ...
0
votes
1answer
13 views

Unable to backup after recovery model change [duplicate]

Previously I had the recovery model as SIMPLE. I have altered it to FULL. While trying to perform BACKUP LOG, I am getting an error saying BACKUP LOG cannot be performed because there is no ...
1
vote
2answers
308 views

Clear missing index DMVs

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details? I don't have the option to restart the server or taking it ...
1
vote
1answer
33 views

Back-end with encription and user-control for MS Access2003-VBA

My client has an Access 2003 "multi-user" (<6) "database" (<3k records) with "encryption" located in a LAN folder and an easy Form I polished with VBA(DAO library). Access 2003 encryption can be ...
1
vote
1answer
26 views

Retire a SQL instance without impacting the primary server?

I have a SQL Server 2008 instance (TST01) that has some databases on it that are being migrated, still a few weeks of work to finish up, before the server is retired, this is a Virtual Server. There ...
10
votes
2answers
8k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific date/...
0
votes
1answer
30 views

Automatically replicate table data into another table

Is there a way to automatically replicate the table into another table where in if table A has changes, then it will also change the data in the 2nd table.
16
votes
3answers
13k views

How do I identify the column(s) responsible for “String or binary data would be truncated.”

I am generating some queries automagically with code I wrote to SELECT from a remote Pg database, and insert into a local SQL Server database. However, one of them is generating this error: [...
1
vote
3answers
45 views

Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110

I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year ...
2
votes
1answer
56 views

SQL - Best way to limit access to small number of tables

I have a login I've created on SQL Server 2008, it's a SQL Server login not using AD. I want to give them access to every table in two databases except the tables that I store login information in for ...
8
votes
2answers
37k views

How to add a user with access to a single view?

I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
5
votes
3answers
18k views

How to turn off identity for a database column of a table

I have mistakenly deleted a table in one one my database server. I have same set of data in the other server. What I have done is clicked on edit top 100 rows for the table, then copied it and pasted ...
1
vote
1answer
41 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
6
votes
3answers
345 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
1
vote
1answer
84 views

Foreign key not working

This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me. I have two tables, Report and Request. The Report table has a primary key of [RP_ID], ...
8
votes
1answer
1k views

Very high transactions per second

Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec. Looking at ...
0
votes
1answer
23 views

drawback of including primary key creation along in the table creation script

Sample script: CREATE TABLE [dbo].[Countries]( [Id] [nvarchar](2) NOT NULL, [Name] [nvarchar](100) NULL CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = ...
1
vote
1answer
58 views

Inserting rows into a truncated table will increase the size?

Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the ...
7
votes
3answers
2k views

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
0
votes
0answers
18 views

How to find difference in usage date based on results

I have a ticket details table that stores the information of transactions. Here is an example of the table data: Ticket_Number Detail_type_ID Description Date_Created TotalAmount Barcode ...
36
votes
10answers
100k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
3
votes
1answer
76 views

VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have ...
11
votes
3answers
969 views

Executing same request from C# VS SSMS give different execution time

I have a request like this one SELECT [EstimateId], [CreationUserId], [EstimateStatusValueId], [LanguageId], [LocationId], [EstimatorUserId], [FilterUnitSystemTypeId], [EstimateNumber], [...
8
votes
3answers
9k views

Executing Stored Procedure that accesses another SQL instance

I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation. Desired Outcome A user using SQL authentication has execute ...
5
votes
7answers
18k views

Can't Login to SQL Server 2008 when running single user mode

I'm following the MS documentation here to migrate logins from one instance of SQL Server to another as part of the process described here. This involves putting the server into single user mode (...
0
votes
0answers
68 views

Install SP4 on SQL Server 2008 SP3 - named clustered instance MQ and PQ in A/A configuration on two Nodes A and B

Current config - We have two named instances called MQ and PQ which are clustered in A/A configuration on two nodes A (for PQ) and B (for MQ). MQ and PQ instances/features on both nodes A and B are ...
1
vote
2answers
51 views

how do i create Periods and weeks (think business reports) using 2 date ranges

Basically Fromdate is every Sunday and ToDate is every saturday, i need to programatically create the PERIOD column on the fly through a stored procedure where period is the month and week is the week ...
12
votes
5answers
39k views

Restoring a backup to an older version of SQL Server

When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient....
1
vote
1answer
19k views

Trying to install SQL Server 2008 Management Studio Express

On a Wndows Server 2008 R2 64 bits, I installed Visual Studio 2010 Express, which installed a SQL Server 2008 Express: Trying to install a SQL Server Management Studio, first I'm required to ...
3
votes
1answer
102 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON a....
0
votes
2answers
80 views

SUM() In SQL Server

Running SQL Server 2008 I can not wrap my mind around why these two queries, which are identical except for the first one has COALESCE() wrapped around the entire statement and the second one has ...
14
votes
3answers
13k views
0
votes
0answers
33 views

What is the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for handling Select ISNULL(MIN(StartDate, '1/1/1900 10:00'))?

I would like to know the difference between Microsoft SQL Server 2008 R2 and MySql Server 5.6 for parsing the following SQL statements. Microsoft SQL Server 2008 R2 SELECT ISNULL(MIN(StartDate),'1/1/...
3
votes
2answers
531 views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
33
votes
3answers
12k views

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
1
vote
1answer
143 views

Business Intelligence Development Studio installation for Visual studio 2010 problem

I want to install Business Intelligence Development Studio for doing report development in Visual Studio 2010, however in my installation process, I cannot find BIDS in the feature selection. What ...
0
votes
1answer
39 views

Determining CPU Usage of last week

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in ...
1
vote
1answer
69 views

Store values in select as a temp variable and reuse them

I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically ...
11
votes
5answers
2k views

Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state ...