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

learn more… | top users | synonyms (1)

1
vote
1answer
16 views

Best way to document Dwh views

I works in a company that provide Dwh views as an API for external Customers (We use MS-SQL 2008 & 2012). every once in a while we release new views and updates the existing views. We have ...
2
votes
2answers
299 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
2
votes
3answers
786 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 ...
1
vote
3answers
15 views

SQL Query in suspended state causing high CPU usage

Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver. Issue: My online DB server was experiencing high CPU usage. Using the following ...
2
votes
2answers
376 views

Table with 4 million records - different size and performance in development and production

At my company we use SQL Express 2008 R2, which supports databases up to 10GB. We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in ...
1
vote
2answers
88 views

Auditing the View Server State permissions on Server

We noticed that one of our servers has permissions granted to "all" users for "view server state". I am wondering if there is a way to audit the DMV's used by the user(s) on a regular basis as this ...
5
votes
2answers
91 views

Keeping iterator in consistent state

I was reading this post to understand how things are done in Sql Server: http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/ If I understand it correctly, data is being sent ...
1
vote
1answer
220 views

SQL Server replication conflicts after migration from 2000 to 2008

I got a suggestion over at Stackoverflow to post here....greatful for any and all help. Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
-1
votes
1answer
24 views

SQL Server 2008 create database problem [on hold]

When I create a database in SQL Server 2008 an error message is shown: Create database permission in master denied in database master. What can I do?
2
votes
1answer
88 views

Identify unattached File Size in SQL Server

From a similar post on this forum, I have modified the below code as per comments and able to get the orphaned files in the drives. The code is : EXEC sp_configure 'show advanced options', 1; ...
2
votes
2answers
5k views

sql server management studio backup error

hello, i want a back up for my database in sql server management studio. i am using ms sql. but when i try to back up , i take the following error: =================================== Backup failed ...
0
votes
2answers
191 views

can we transfer sql database backup from one server to another server directly?

I want to transfer sql database from one server to another server directly, instead taking back up of that database and copy that back up in another server and then restore it. Please tell me, Is ...
1
vote
1answer
66 views

how to included concatenated values in Dynamic Pivot Column Value

I am extending a dynamic pivot query. The original query works fine. In the second query Instead of display the date as a value for the pivot column I would like to display a concatenated column ...
1
vote
1answer
95 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
0
votes
2answers
38 views

How can i know the relationship in the database in sql server 2008? [closed]

I want to know how can I determine relationships between data which is not determined by the primary key in the design. For example, I have three tables in the database like this :- table 1: fields ...
0
votes
1answer
41 views

Run CDC Cleanup job at specified time

I have enabled cdc and using it , it works fine now the problem is that we have many tables in a database and also we have many databases so how to configure cdc cleanup job to run again and again ...
0
votes
0answers
23 views

Upgrade from 2008 RTM to 2008 R2 RTM failed due to perf-ReportServer-rsctr.dll is being used by another process

I was upgrading SQL Server 2008 RTM on windows Server 2008 R2 Sp1 to SQL Server 2008 R2 RTM.I got this error " The process cannot access the file 'C:\Windows\SysWOW64\perf-ReportServer-rsctr.dll' ...
2
votes
2answers
10k views

The value could not be converted because of a potential loss of data [on hold]

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of ...
15
votes
2answers
20k views

How to find the SQL statements that caused tempdb growth?

The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create ...
1
vote
1answer
70 views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
5
votes
2answers
2k views

SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so: Fired up Profiler Created ...
8
votes
2answers
1k views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
6
votes
2answers
370 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
1
vote
2answers
27 views

Copy related records from 3 tables to another 3 tables and maintain the relationship between them

I have 3 tables that sore orders, order items and order item comments . I need to copy the data for one order to 3 new tables that have the same structure and maintain the relationship. the order will ...
6
votes
6answers
2k views

attach/detach v.s. backup/restore

I am using SQL Server 2008 Enterprise. I need to transfer the database (as a whole) to another server (to make a duplicate database to setup another test environment). I have two choices, (1) making ...
8
votes
5answers
1k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
34
votes
6answers
28k views

What are the main causes of deadlocks and can they be prevented?

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was ...
5
votes
1answer
134 views

Where to put a database in case of IP failover?

I understand that to achieve some level of availability of an online application it is possible to organize an environment with a redundancy using an IP failover mechanism and switching between ...
5
votes
1answer
2k views

Illegal XML character out of the extended event

I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up: Msg 9420, Level 16, State 1, Line 28 XML parsing: line 1, character ...
4
votes
1answer
12k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
4
votes
1answer
32 views

How do I determine the spid of the session that is making a query on a linked server?

I have a session running on a SQL Server instance that is blocking other sessions (holding on to a lock without releasing it). Querying sys.sysprocesses yielded the hostname of a different SQL Server ...
-4
votes
2answers
72 views

Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2 [closed]

MS on this link http://msdn.microsoft.com/en-us/library/ms182776.aspx mentioned that we should use rowversion data type over Timestamps. Now inside my sql server 2008 R2 I want to create a column with ...
0
votes
0answers
23 views

Cannot add “rowversion” data type in sql server 2008 r2 management studio, or even to modify existing column data type [duplicate]

I want to create a column with Rowversion data type, but inside my SQL Server 2008 R2 Management Studio, I cannot find such a data type: Also when I tried altering existing column named timestamp ...
2
votes
0answers
31 views

Can I convert SQL Server 2008 full db to SQL Server 2005?

Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts ...
8
votes
1answer
5k views

BULK INSERT permission on a single database

I work on a company that has straight IT security manners. I was hired as a Database Analyst in order to optimize some processes in our team. The problem is that I only have read and write permissions ...
0
votes
0answers
23 views

SQL Transaction Logs Overwrite

Server Detail: SQL Server 2008 R2 Production Set Up: In a production environment, I have a DBMR job scheduled to take transaction log backups for all the databases present in a DB server. This job ...
1
vote
1answer
5k views

Convert a date to yyyymmdd format

Which SQL command is recommended to convert a date to yyyymmdd format? convert(varchar(8), getdate(), 112); or convert(varchar, getdate(), 112) I notice that if I use the second one, it will ...
1
vote
2answers
175 views

Improving a search query [closed]

I have a query for search function. Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user. ...
1
vote
1answer
45 views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as ...
0
votes
1answer
54 views

Secondary File Group is not online in SQL Server

In my database there are two file groups PRIMARY and SECONDARY. The .ndf file is in SECONDARY file group. I just wanted to backup only the secondary file group. So ran following T-SQL... BACKUP ...
1
vote
1answer
85 views

Storing more than 8000 bytes in a column [closed]

I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the ...
15
votes
4answers
54k 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.
3
votes
1answer
176 views

Updateable Subscriptions: Rows do not match between Publisher and Subscriber

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database ...
-1
votes
1answer
49 views

How to know which columns value is different between two rows

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 ...
1
vote
1answer
79 views

Installation failed in installing SQL Server 2008 SP3 on windows 8.1

Overall summary: Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL ...
0
votes
1answer
93 views

Machine account causing a Token-based server access validation failed with an infrastructure error

As the error suggests the SQL log is filling with the error: Login failed for user 'xxx\xxx$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous ...
2
votes
2answers
476 views

70-432 : SQL Server 2008 Implementation and Maintenance [closed]

Has anybody taken this certification exam? If so, how many years of experience do you have as a SQL Server DBA and how hard did you find the exam?
1
vote
3answers
3k views

How to programmatically remove a push subscription?

I have 4 databases, a publisher and 3 subscribers (transactional replication). In case of an error in the publisher I want to: delete all subscriptions create a new publisher make the two remaining ...
0
votes
0answers
27 views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...
0
votes
1answer
46 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...