Questions tagged [sql-server-2005]

SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
10
votes
3answers
2k views

Hard limitation for merge replication article count?

A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
10
votes
3answers
15k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
0
votes
1answer
28 views

SQL 2005 SP2 Database on SQL 2012 Instance

I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005. Those databases are on physical server that don't have any space to backup ...
18
votes
2answers
7k views

SQL Server how to get around the transaction log filling up when updating a column to an int

I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns: MarrigeId tinyint, HusbandName varchar(500), MarrigeLength int Now I have another table ...
0
votes
2answers
598 views

UPDATE with JOIN: Incorrect syntax near the keyword 'RIGHT'

I'm using SQL Server Management Studio 2005. I want to look up a column value in another table, using a shared ID tblOrder_ordernr in both tables, but only update one table. To me, the query is ...
2
votes
3answers
379 views

SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database

Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue. Scenario: Routine index rebuild happens, once a week, 2 AM Saturday morning. Log file grows about 15x ...
0
votes
0answers
2k views

pros and cons of partition or not-partition this sql server table that is highly hit?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
4
votes
2answers
431 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub stored procedure based on passed ...
1
vote
3answers
34k views

Joining two tables on multiple criteria

I have two tables products and positions. products: id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100) positions: id BIGINT PRIMARY KEY, position ...
86
votes
2answers
266k views

How to get the current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
28
votes
2answers
33k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An article ...
3
votes
2answers
361 views

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2008?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2008 (compatibility 2005) and the only copy of deadlocks have ...
5
votes
3answers
3k views

One of the Database Ownerhas been Removed and corresponding property changes in sql server

A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?
0
votes
1answer
121 views

Inherited Permissions: SQL Server 2005 vs 2016

Question: I have a SQL Server 2005 server and a SQL Server 2016 server. On each server is a database (A) with a stored procedure that selects data from a view in Database (A) The view in database (A) ...
7
votes
2answers
2k views

What characters are word breakers in English for SQL Server 2005 and 2008 R2?

I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ....
4
votes
2answers
598 views

How do I store spatial coordinates in SQL Server 2005

I'm looking to store GPS co-ordinates in an SQL Server 2005 for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...
5
votes
2answers
1k views

Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued ...
4
votes
2answers
3k views

Find the days difference between two dates per month

I have a table 'data' in which there are two columns i.e. Startdate and Enddate. Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to ...
3
votes
1answer
463 views

SQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627)

We are new to using clustered shared volumes and SQL Server 2016 Enterprise Edition. When restoring SQL Server 2005 databases with full text search catalogs onto our new 2016 instance, we get the ...
-2
votes
1answer
653 views

I want to prioritize which record to select from multiple records which have same ranking

The table structure to solve | **Email** | **Product name** | **price** | **Creatdt** | |R@gmail.com| ABC | 9.99 |05/07/19 12:30:21 | |R@gmail.com| efg | 12....
13
votes
3answers
18k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on [sys].[...
1
vote
2answers
1k views

UPDATE in combination with nested SELECT queries and LEFT OUTER JOIN

I have a problem with an UPDATE statement that contains a nested SELECT statement in combination with LEFT OUTER JOIN statements. Situation: In our database there is a new column added to all people ...
0
votes
1answer
142 views

Check connection details to SQL Server 2000/2005

I want to migrate some databases hosted in SQL Server 2000/2005 to SQL Server 2014. I don't have direct access to the databases and I want to know all applications connecting to it and connection ...
26
votes
2answers
5k views

Optimization issue with user defined function

I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I ...
5
votes
3answers
3k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
4
votes
3answers
2k views

Objects in Tempdb that are not associated with a session

I have a SQL Server 2005 instance where my tempdb i apparently holding on to objects that are not in use anymore. The database has 24gb total and is apparently unable to go below 8gb in use. For ...
67
votes
4answers
165k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
1
vote
1answer
121 views

How to know if that execution plan is suitable for this query (Sql server 2005)?

This is my query. Declare @Pivv NVarchar(Max), @Query NVarchar(Max), @Pivv1 NVarchar(Max), @Columns NVarchar(Max) Select @Pivv = Coalesce(@Pivv + ',' , '') + ...
22
votes
2answers
23k views

How badly do SQL Compilations impact the performance of SQL Server?

I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so. I whipped out SQL Profiler ...
2
votes
1answer
65 views

SQL DBA data type is changed after upgrade

We have recently upgraded our SQL Server Database from SQL 2005 to SQL 2014. We are experiencing slowness in both SSMS and application as well while executing a SP in SQL 2014. After checking through ...
3
votes
1answer
1k views

SQL Server service permissions on network share depending on IntegratedSecurity=true/false

Our company receives a daily file from one of our customers. It is an EDIFACT file translated to XML and downloaded from an SFTP server. We have developed one (c# console) application that: Download ...
43
votes
10answers
50k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
8
votes
3answers
6k views

SQLCMD Mode in SSMS and @@variable expansion

When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL ...
0
votes
1answer
198 views

Fetch data from SQL Server table of the ERP DB

I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of ...
6
votes
1answer
20k views

No SA password. No SQL Server Management Studio. No OS authentication [duplicate]

I have this problem. I need to do some administrative tasks on a MS SQL Database using SA account. OS authentication is not set. SQL Server Manager Manager Studio is not installed. I have no other ...
0
votes
1answer
51 views

Disaster recovery/Server Migration for databases that are permanently in Standby

I've got an old 2005 server where 8/12 of the databases are in Standby/Read-Only. This server has 81 jobs, many of them with unique id's for names. One of these jobs is called Replicate, and it calls ...
3
votes
2answers
8k views

Backup name in Maintenance plan

I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak. Now I am using SQL Server 2012 but I am ...
3
votes
2answers
4k views

Why can the SA account no longer access the master database?

I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it. Their ...
2
votes
2answers
131 views

Performing a “Spread Negitive” over a dataset

I have a situation where a table that represents outstanding balances and credits are both contained in one table. What I need to do is apply all outstanding credits (preferably in order of oldest ...
1
vote
1answer
1k views

Mysterious merge replication problems creating a subscription

We have a merge publication which had been mostly working OK for a long time (years). Recently an article was added, we were informed the snapshot was out of date, so we regenerated it. Then we got ...
4
votes
1answer
1k views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys....
6
votes
1answer
5k views

SHRINKFILE best practices and experience [duplicate]

Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
5
votes
1answer
2k views

SQL Server 2005 - Best way to constraint a Datetime field as Date

Whereas: date field type starting with SQL Server 2008 Given a table: CREATE TABLE dbo.MyTable ( Code int NOT NULL, DateCode datetime NOT NULL ) CONSTRAINT [PK_MyTable] PRIMARY KEY ...
1
vote
1answer
2k views

how to get last date of today in datetime datatype?

select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0)) when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody ...
11
votes
1answer
3k views

How does IDENTITY_INSERT affect concurrency?

I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support. Under certain circumstances, it archives and incomplete post from the posting queue ...
1
vote
1answer
87 views

SQL multiple select with where conditions performs really poorly

Data structure is something like this: Number| ID | DateTime | Status 50000001, 101, 1/1/09 0:10, PO 50000001, 110, 1/1/09 0:11, PO 50000001, 102, 1/1/09 0:15, PO 50000001, 101, 1/1/09 0:10, PCK ...
2
votes
1answer
5k views

DatabaseMail didn't start when an email is queued

This is about SQL Server 2005. I have configured SQL agent jobs to send an email when it failed. So far I have received email reliably. However, I have an weird problem where an email would get ...
6
votes
3answers
3k views

Performance impact of waiting on a service broker message

I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
5
votes
3answers
5k views

Job Schedule view

I'm trying to create a view for a report so that I can easily keep track of all of the backups that I have scheduled on a server. All of these backups are scheduled in subplans of a maintenance plan, ...
7
votes
3answers
220 views

How to handle reporting of an extreme amount of databases in SQL Server 2005?

I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these ...

1 2 3 4 5 27