This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

5
votes
2answers
6k views

How to refresh AD security group on Sql Server permissions

I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP. I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
1
vote
1answer
61 views

Should I update to sql server 2008 sp2 before restoring the db's or do the update after?

I'm making a move from SQL Server 2008 to SQL Server 2008 R2 (Azure VM) as part of a hosting provider switch (from MyHosting to Azure). Should I update the VM to R2 SP2 before restoring the db's or do ...
3
votes
2answers
46 views

will table partitioning and compression help heavily used OLTP database?

We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to ...
0
votes
0answers
16 views

Migrating from SQL Server 2005 (Win 2003R2) to SQL Server 2008R2 (Win 2008R2) new Server [duplicate]

I am pretty new to the DBA field and i have been assigned to migrate all of my Production database from SQL server 2005( server A) to 2008R2 (Server B) and Decommission all of those database from 2005 ...
0
votes
1answer
145 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
0
votes
1answer
1k views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
1
vote
1answer
35 views

SQL Server restore database file access error

I am trying to restore a database from a file on a remote machine. The script I am using is as follows: RESTORE FILELISTONLY FROM disk = '\\server\path\to\file.dmp' RESTORE DATABASE MyDB FROM ...
7
votes
2answers
1k views

SQL Server 2012 Availability Group is it “AlwaysON”?

In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to ...
1
vote
2answers
294 views

SQL 2008 Server - performance loss possibly connected with a very large table

We have SQL Server 2008 with 3 active databases running on it. DB1 - cca 400 MB size DB2 - cca 8 GB size DB3 - cca 42 GB size - but the majority of records are not used at all In DB2 we have a ...
2
votes
2answers
62 views

SQL Server : Tracking a slow running query on completion

Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, ...
2
votes
4answers
6k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
3
votes
2answers
214 views

Only show results greater than a certain number when summing two values together

I have a query that sums up two values in the same table and then calculates one as a percentage of the other select account_no, 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) from accounts etc ...
1
vote
3answers
218 views

How to get the first or last row in ordered result set, depending by column value?

I have the follwoing result set from the table PUNCHREPORT EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE .................................................................. 70 ...
0
votes
0answers
30 views

Combine multiple same rows into one row by using Id

This is my table: ID Contact Id Ad-type Ad-Title Images_Path 1 2 Houses For Sale lands/im1.jpg 2 2 Houses For Sale lands/im2.jpg I need output like this: ...
6
votes
1answer
8k views

Add DEFAULT to existing column

In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
3
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...
2
votes
1answer
31 views

Hosting SQL Server and SSIS/SSRS in same VM

I have SSIS / SSRS and large number of databases currently being hosted on the same prod server (SQL1, SQL Server 2008R2). Since SQL1 already has SSRS/SSIS installed, would it be a good option to ...
1
vote
2answers
219 views

Why does FETCH NEXT return second row after opening cursor?

I have a cursor declaration: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempRemaining OPEN idCursor And the statement: SELECT DISTINCT [id], [data] ...
9
votes
3answers
45k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
6
votes
4answers
2k views

How can I detect broken stored procedures after a schema change?

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change. ...
12
votes
1answer
443 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
21
votes
4answers
89k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
4
votes
1answer
14k views

How can I get my linked server working using Windows authentication?

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs ...
0
votes
1answer
16 views

Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008?

Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008 ????
4
votes
2answers
19k views

SQL Server 2008: The database [dbName] is not accessible

I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc. The ...
1
vote
1answer
46 views

microsoft sql server schema [closed]

From blog.sqlauthority.com: Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
13
votes
1answer
1k views

Best way to write SQL Query that checks a column for non-NULL value or NULL

I have a SP with a parameter that has NULL as default value and then I want to do a query like this: SELECT ... FROM ... WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND ...
0
votes
0answers
23 views

What is the syntax to sp_recompile logon trigger?

I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax. E.g. these don't work: sp_recompile '[Tr_Logon]' sp_recompile '[Tr_Logon]' ON ALL SERVER ;)
2
votes
0answers
42 views

Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a ...
2
votes
3answers
67 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to ...
1
vote
1answer
46 views

How to update/insert/inject nodes into XML

I have an application which stores optional Site information in an XML field. Sample table: CREATE TABLE [dbo].[Sites]( [SiteID] [int] IDENTITY(1,1) NOT NULL, [SiteName] [nvarchar](80) ...
0
votes
2answers
43 views

How to add deleted user in sql server? [duplicate]

I have set up sql server 2008 in windows server 2008 r2 and it works fine. However, I have deleted the my user from logins in the sql server accidentally and now I cannot find a way to log in to the ...
0
votes
0answers
10 views

Are tables during a merge function updated dynamically?

If for example, I have a column age in both tables. My merge condition for two tables is when table1.age=table2.age. My WHEN MATCHEDinstruction is to increment table1.age by 1. So if a row with age=5 ...
22
votes
4answers
55k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
1
vote
1answer
37 views

Database compatibility level [duplicate]

We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005. What are differences in compatibility level across the versions ? Are there any advantages changing it to 2008 ...
0
votes
2answers
231 views

Error with making backup in sql Server

When I try to backup a SQL Server 2008 database, I have an error. Is anybody know what is the error? TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for ...
3
votes
2answers
693 views

dbcc shrinkfile not reducing size of log file in sql server 2008

I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code: ALTER DATABASE [database] SET RECOVERY SIMPLE DBCC SHRINKFILE(DATABASE_LOG, 20) ALTER DATABASE ...
2
votes
1answer
36 views

Select columns dynamically from sql server table

Lets say I have a table that actually store record which looks like Create Table dbo.Info ( SN int primary key identity(1,1), FirstName nvarchar(50) not null, LastName nvarchar(50) not null, ...
1
vote
2answers
52 views

JOINS - optimize cost of LIKE/OR criteria

The Active table has the Account Name in 1 column and the Master table has the Account Name in any one of 6 separate address columns and I'm trying to match them. Also, they don't necessarily exactly ...
2
votes
2answers
621 views

parallelism repartitions, ordering, and hash matches

I am just starting to delve into query plans and recently learned that "hash matches" are bad. I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash ...
2
votes
3answers
3k views

Pros and Cons of SQL Server Database Mail

I'm planning to use SQL Server Database Mail. I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new ...
8
votes
2answers
4k views

Turning off auto shrink on all SQL Server databases. Why doesn't this work?

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message. sp_MSforeachdb ' BEGIN USE [?] DECLARE @dbid INT SET @dbid = DB_ID() IF(@dbid > 4) BEGIN --PRINT ''[?]'' ...
2
votes
1answer
24 views

Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in ...
1
vote
2answers
75 views

Restore SQL Server 2008 database onto SQL Server 2014?

I have a backup file that I need to restore daily on schedule based. But the backup file is from SQL Server 2008 and restore destination is SQL Server 2014. And also the backup file is from cross ...
19
votes
8answers
6k views

Why should an application not use the sa account

My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc. I have an absolute belief that ...
1
vote
0answers
45 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
0
votes
1answer
27 views

Microsoft SQL Server partitioning with on premise server

I'm planning for a small hosted ASP.NET application which uses a Microsoft SQL Server 2008 database. One of the users which uses the application doesn't want his data to be stored in a remote database ...
0
votes
0answers
14 views

SQL - ignore new columns in Access

At my company we download an Acces DB daily, SQL pulls that file to update a Spotfire file. Sometimes the Access file has had columns added. This causes an error in SQL and stops the Spotfire ...
1
vote
1answer
278 views

syscacheobjects is cleared randomly

Microsoft SQL Server Web Edition (64-bit) Version 10.50.1600.1 SQL Server 2008 R2 I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, ...
3
votes
2answers
57 views

Historical dbcc checkdb results

Is there a better way of accessing historical dbcc checkdb result history (if output logging was not used at runtime) than xp_readErrorLog? EXEC xp_readErrorLog {0-20}, 1, N'CHECKDB'; SQL Server ...