2022 Developer Survey is open! Take survey.

Questions tagged [sql-server-2008]

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

Filter by
Sorted by
Tagged with
5 votes
3 answers
545 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...
44 votes
7 answers
260k 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 ...
3 votes
3 answers
1k views

Why does 'sysfiles1' show up in the sys.objects catalog view in SQL Server 2008?

We were recently using this query to find tables in our database that didn't have clustered indexes and found that one of the results it reported back was the 'sys.sysfiles1' table. We're running SQL ...
1 vote
2 answers
134 views

Upgrade BI server to 2014 and stay on 2008r2 with the database engine server

We would be happy to upgrade our SQL Server environments to 2014, but we have a tight budget for next year. Our BI server (SQL Server 2008 Standard Edition, that is used only for BI purposes) is ...
-1 votes
0 answers
29 views

Is it possible to capture the connection string used to connect to SQL Server

I have recently taken over a number of SQL Server 2008 databases that are configured with mirroring. I understand there are some applications that connect using the "Failover Partner" option ...
-1 votes
0 answers
24 views

Columnstore or page compression

We've got a lot of code and data in Sql Server 2008 dbs and a lot of our design choices have been frozen at 2008 available functionality. We've recently gotten a tentative green light to upgrade to ...
0 votes
1 answer
115 views

SQL Server - moved index behind PK constraint to different filegroup, but space not freed

I have a large table Foo. There are 4 filegroups (FG1, FG2, FG3, FG4) FG2 is on a disk that is nearly full. It has 2 indexes: a) clustered index (on Date) b) a unique non-clustered index (on ID) ...
0 votes
1 answer
460 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
1 vote
1 answer
118 views

How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server?

I want to recreate the table below in SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it. ...
5 votes
3 answers
6k views

Splitting data into two tables, in one go

I am using SQL Server 2008 and have a problem which I don't know how to solve without using many temporary tables and an unreliable join. Table 1 contains 6 columns of data, which is then split into ...
3 votes
1 answer
3k views

Does existing msrepl_tran_version column need to be removed when re-creating replication?

What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it ...
2 votes
3 answers
3k views

SQL Server data extract with limited performance impact

Background: I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively. Problem: I need to do a data ...
1 vote
3 answers
7k views

Protect SQL Server from hackers

Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me. EXEC sp_readerrorlog 0, 1, '...
0 votes
1 answer
52 views

Best What's new book about just changes and new features in SQL Server 2012 [closed]

I want to read about new changes and new features in SQL Server 2012.I found some E-Book in the internet but they have basic topics (like create Tables,Views,...) I want a book that just cover new ...
-2 votes
1 answer
25 views

Select rows with a duplicate ID but different value in another column

I have a table like this I would like to select the Itemid that occurs more than once with a different Rate with group by Masterid The output should be something like:
4 votes
1 answer
115 views

reading content of master database at a different server

I have a query for example select * from sys.databases select * from sys.configurations I believe this information is stored at master database. I would like to have a look at configuration settings ...
0 votes
2 answers
1k views

SQL Server Setup failure - Cached MSI files are missing

Trying to apply SQL Server 2008 SP4 to an old instance I now have to look after, but the installer is failing with TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error:...
16 votes
3 answers
16k 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/...
7 votes
2 answers
15k views

How to find memory usage by table? [duplicate]

I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?
8 votes
1 answer
568 views

SQL Server Profiler: why the RPC Starting event does use an different decimal separator from SP Starting?

Trying to diagnose an problem, I've ran into that inconsistency. In SQL Server Profiler, the RPC Starting event uses the regional settings to write real numbers (example: in Portuguese(Brazil) 14.5 ...
40 votes
4 answers
76k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
0 votes
1 answer
667 views

Missing Encryption Key when migrating Reporting Services from SQL Server 2008 to SQL Server 2016

Owing to a ransomware attack on our production server Reporting Services went down. This server was installed few years ago and its been in production for long. We have quite a few reports on this ...
8 votes
2 answers
4k views

Query plan showing a missing index even though the index is present in the table

In one of my production SQL server instance, I am facing an issue. A query is taking long time to run. While checking SQL query plan, I found that it was giving suggestion for creating a missing index....
42 votes
3 answers
18k 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
1 answer
220 views

move blob data to new filegroup

short version: what options do i have to move a table that has blob data over to a new filegroup, without blowing out the txlog? details: we have an extremely over-allocated filegroup ("DA1"). the ...
20 votes
6 answers
54k views

Pros/Cons of using multiple databases vs using a single database

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented. While I don't agree, I'm having trouble ...
14 votes
3 answers
28k views

How to see what is cached in memory in SQL server 2008?

Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-...
2 votes
1 answer
463 views

Does DBMS (e.g. of SQL SERVER 2008) use virtual memory?

Or in other words ... . Does the DBMS somehow circumvent the OS Cache manager and acccess the memory directly OR it lets the OS do its job, behave as a normal process and use the concept of virtual ...
8 votes
1 answer
466 views

Strategies for organising SQL Server with large amount of RAM

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if ...
0 votes
1 answer
1k views

error 15466 An error during decryption

I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user....
7 votes
3 answers
596 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
5 votes
2 answers
16k views

Total Memory used by SQL Server (64 bit)

My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named ...
2 votes
1 answer
1k views

SQL Server Memory for Backup Buffer

In SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012? EDIT: Does MAX Server Memory ...
11 votes
3 answers
61k views

Automatic decimal rounding issue

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
2 votes
2 answers
5k views

Will SSIS 2012 work with SQL Server 2008R2 (not integrated)

Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software. We have a company policy where we cannot run ...
14 votes
3 answers
32k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
0 votes
2 answers
53 views

Is there any way to get current transaction id in SQL SERVER 2008?

I want to get current transaction id to log something in another process. The code is actually run in .net, but in SQL Server it will be similar to the following pseudo code: BEGIN TRAN <GET ...
0 votes
0 answers
302 views

SQL Server 2008: The target name, "package0.event_file", is invalid, or the object could not be found

What can cause the following error? Msg 25623, Level 15, State 2, Line 1 The target name, "package0.event_file", is invalid, or the object could not be found Given the following TSQL: CREATE EVENT ...
16 votes
6 answers
12k views

Accidentally turned down SQL memory too low to log in, how do I fix?

I turned down the Maximum Memory of the my SQL Server instance to 15 MB... Now I can't log in to increase it. How can I increase the maximum memory without logging in? Version is SQL 2008 R2
3 votes
1 answer
2k views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
0 votes
1 answer
196 views

Database Mail works in Production but not in Test Database

I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008): Msg 229, Level 14, State 5, Procedure ...
6 votes
3 answers
3k views

SQL Server parallelism for DML operations

I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold ...
7 votes
1 answer
2k views

Service Broker: Queue Monitor is dropped after poison message

I have searched everywhere and there isn't an answer online. There is one similar question on stackoverflow but it's not exactly the same and it has no accepted answer. When I setup event ...
10 votes
4 answers
46k views

Allocate space for object 'dbo.SORT temporary run storage

DBCC CHECKDB returns: Could not allocate space for object 'dbo.SORT temporary run storage: 140737951236096' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space ...
1 vote
1 answer
169 views

Able to connect with connection manager but SSIS package does not authenticate

I have an SSIS package that connects MSSQL2008 server to MSSQL2000 and pulls data off the 2000 server and transfers it to the 2008 server. The 2000 server was recently virtualized and since then the ...
3 votes
2 answers
2k views

Cleaning up transaction replication del/ins/upd stored procedures?

I'm referring to the ones that start with: dbo.sp_MSdel... dbo.sp_MSins... dbo.sp_MSupd... I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run ...
1 vote
0 answers
50 views

Migrate MSSQL 2008 database to MySQL 5.7 or 8.0

I am trying to migrate an old database runing in SQL Server 2008, to MySql 5.7 or 8.0 using MySql Workbench (Migration wizard). It gave me this errors Traceback (most recent call last): File "...
1 vote
0 answers
33 views

Create Calendar with Pivot or function in SQL SERVER

Is it possible to get a calendar with this structure from my AUT_CALENDAR table? I am applying PIVOT to obtain a calendar with the following query. DEMO SELECT IDCALENDARIO,NombreDia, [Lunes], [...
19 votes
2 answers
7k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255), when VARCHAR(30) will probably do. I ...
0 votes
0 answers
44 views

Can't connect to SQL Server using its actual server name

I have a SQL Server instance, its actual server name (@@servername) is actual_name, and its alias is alias. When using SSMS to connect, I can only connect using alias, but not using actual_name. ...

1
2 3 4 5
78