Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

0
votes
2answers
28 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
87 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 ...
3
votes
1answer
40 views

Is there any way to add a witness without restarting the witness afterwards?

I want to add a third instance of SQL server to my mirroring pairs to act as a Witness. When I set the witness, the mirror remains disconnected from the witness and the mirror's log accumulates errors....
3
votes
2answers
165 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 ...
0
votes
1answer
26 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) ...
3
votes
1answer
86 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
46 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....
1
vote
2answers
235 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
28 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 ...
1
vote
1answer
118 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 + ',' , '') + ...
0
votes
0answers
65 views

MS SQL Server 2005, Using AD authentications only. LDAP Server is about to enable tls 1.2 only

Currently I maintain a SQL Server 2005 that uses mainly MS authentication Most of the security logins are through MS Authentication, So database owners are through LDAP users I was notified that ...
2
votes
1answer
56 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 ...
0
votes
1answer
170 views

Error -2146825284 / 0x800A0BBC when trying to write file

I have a query which uses sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2 to write from an image column to disk. It is failing with an error which I think relates to file permissions. -2146825284 / ...
2
votes
1answer
153 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 ...
0
votes
1answer
74 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 ...
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 ...
0
votes
2answers
243 views

How to Backup Remote Database to FTP server

I can connect to a remote SQL Server via Management Studio. But, when I create backups, they get created on the server's disk. How can I transfer them to an FTP server? I only have a login to the MS ...
1
vote
1answer
386 views

how to find number of empty tables in any schema?

I want to query how to find all the empty tables in my schema HR i already tried with select * from dba_tables where owner='HR' and num-rows is null; but i dont know if this is the correct logic or ...
0
votes
0answers
21 views

How to reduce space on db after having deleted large amount of datas [duplicate]

How can we reduce space on a sql server db, after having deleted a bunch of datas? Shrinking the mdf file doesn't produce any result. But a backup/restore does but takes more than 14 hours. It's a 2 ...
-2
votes
3answers
77 views

How to calculate sum of employees late mins and Insert into another table using stored procedure

Employees daily late minutes stored in one table(Attndate) and I want to calculate sum of Individual employees late mins then insert into another table(LateMuster) using sql server stored procedure ...
6
votes
2answers
314 views

Why is datalength of decimal 5 regardless of precision?

I'm trying to better understand numeric types in SQL and have read that the decimal type will always require 17 bytes. However, the MS Docs list a table indicating the amount of space used depends on ...
5
votes
2answers
733 views

Can send test database mail but job emails failing

I stopped receiving sql agent job failure notifications in the recent past. Upon investigation the error reported is: The mail could not be sent to the recipients because of the mail server failure. ...
2
votes
1answer
739 views

How long will a truncate table take on 252M Rows (170GB data)?

I understand that there are many variables in this and am not looking for an exact number of min:sec :) I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and ...
3
votes
1answer
383 views

Will DB backup time and size be decreased after truncate large tables

Two tables in our SQL Server DB equate to 500M rows and 350GB of data. Disk space is an issue where we keep backups and the time to execute backups is as well. We plan to truncate these 2 tables. I ...
1
vote
0answers
45 views

Deadlock During Insert into One Table

I have a multi-threaded application that does an insert into a table using a stored procedure, and it is causing deadlocks in the database. I ran a trace on my database, and the deadlock processes are ...
-3
votes
1answer
509 views

Find employees who have worked in all departments [closed]

My tables look like this: departments dept_id,dept_name employee emp_id,emp_name,dept_id I am trying to find all employee names who have worked in all departments.
-3
votes
1answer
47 views

SQL Server detected a logical consistency-based I/O error [closed]

Please provide the solution for the error message below Msg 824, Level 16, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:87552775; ...
0
votes
1answer
38 views

Advice on deleting all rows and reclaiming space of very large table

I'm not a dba...let's start with that :) I'm an app developer. But our database has an archive table which is 252 million rows and 170GB, and I've been tasked to fix it (actually, there are multiple ...
0
votes
1answer
969 views

What causes “lock request time out period exceeded” when droping a column?

I created some columns in our production database but now I need to drop one. However this seems impossible and I do not know why. I dropped the column in our development database just fine. I ...
2
votes
0answers
238 views

Unexplainable behavior of SQL Server under Windows Authentication

The versions affected are SQL Server 2005, 2008, 2008 R2. If we grant CONNECT SQL to public server role, any AD account can log in to server. So we'll have all the AD members on the server without ...
2
votes
1answer
69 views

Page Splits on an empty table

I have a monitoring tool that alerted me to high page splits, 50% of new allocations. After some Google Fu, I found the following query to look and see: SELECT IOS.index_id, O.NAME ...
-1
votes
1answer
188 views

How to raise an alert when a table reaches a certain size? [closed]

I would like to set up an alert when the disk space used by a table reaches a certain size. I am aware we can set up an alert for file group\log file\database reaching a certain threshold. It would ...
0
votes
2answers
60 views

How can I find out what my SqlServer 2005 CPU is mostly working on?

I apologize if this is somewhat of a rookie question. After looking online I found some articles that explain how to find the worker time per query using sys.dm_exec_query_stats. e.g. SELECT TOP(10) ...
3
votes
3answers
959 views

How can I get satisfactory random numbers from the RAND function (or elsewhere)?

I'm creating a pseudo-random data set for application users to train on. I'm surprised that if I seed the RAND() function with 1, 2, 3, etc., I get very nearly the same result from the seeded ...
2
votes
1answer
1k views

Sending email from SQL Server without using DBMail [duplicate]

Is there a way to send email from SQL server stored procedures without using DBMail? DBMail is hard coded by default to use base64 encoding for email messages. As far as I can tell there's no way to ...
4
votes
1answer
1k views

Querying XML nested nodes

I need to integrate a daily XML file into my system. The XML file has an schema similar to this: create table txml(id int identity, data xml); insert into txml (data) values (' <order> <...
0
votes
1answer
41 views

SQL Server restore database files [closed]

Say I'm using SQL Server and have set sa password in security. Can the database files be copied to another computer (running same SQL Server setup but with different sa password security) and restored ...
1
vote
2answers
52 views

Is it possible to remote connect to SQL Server 2008 database with SQL Server Management Studio 2005?

I have SQL Server Management Studio 2005 R2 in my administrator PC, and SQL Server Management Studio 2008 in the Server PC. Each time I tried to connect to the database server (SSMS 2008), it will ...
-2
votes
1answer
80 views

sp_BlitzFirst doesnt work on SQL Server 2005 [closed]

Msg 139, Level 15, State 1, Procedure sp_BlitzFirst, Line 0 [Batch Start Line 3] Cannot assign a default value to a local variable. Msg 195, Level 15, State 10, Procedure sp_BlitzFirst, Line 120 [...
1
vote
1answer
63 views

Read from large table without causing disk queue length issues

I have a report that reads from many tables, one of which is 48GB. To help with performance I planned to create a table which stored a subsection of values from this large table. However, whenever ...
4
votes
1answer
477 views

Loading SQL Server from SQLite with image data

I am trying to load a SQL Server database table from a SQLite database. I have been unable to transfer the data between the two databases since there are: Difficulties in accessing the data in the ...
4
votes
2answers
537 views

Extract street address from string with multiple delimiters and positions

Trying to pull just the Street addresses out of this data: CREATE TABLE foo(Places varchar(50)); INSERT foo(Places) VALUES ('MARKET @123 NORTH RD'), ('HARDWARE @600 Main RD ;W1'), ('MAIN AV / NORTH ...
0
votes
1answer
89 views

Two-way replication between SQL Server 2005 and SQL Server 2008

We currently have a SQL Server 2008 and SQL Server 2005 participating in one-way transactional replication of a single database. If possible we would like to set this up as a Two-way replication. ...
7
votes
2answers
141 views

Updating open orders from a price list within a range of dates

Setup I've set up an example on rextester and dbfiddle. Scenario Price list: Its the products price list, one Products can have more than one active price, even a future price. +---------+-------+...
6
votes
3answers
4k views

Storing NULL versus storing '' in a varchar column

I realize this may be marked as duplicate, but I'm specifically asking in relation to SQL Server 2005 I have read conflicting advice on the internet so I'm asking here. Specifically in SQL Server ...
5
votes
1answer
663 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 ...
3
votes
1answer
336 views

Microsoft SQL Server Error 1475 when Mirroring

Could anybody help me please. I am trying to mirror our SQL Server 2005 to our DR site. I have successfully done half of the databases without any issues. However, the remaining ones all have the ...
1
vote
1answer
78 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 ...
1
vote
3answers
78 views

SQL Server Express 2005 DB over size but data < 1GB

I have a SQL Express 2005 instance which is not working correctly due to it hitting the 4GB DB size limit. I have looked at the data in the DB and cannot see how this is possible - the data is less ...
1
vote
3answers
289 views

Moving data into an archive table and reducing the size of the original table

On a SQL Server I inherited, there is a 55GB database on a 70GB drive. There are many small tables, and one big table that is 50GB in size (approx 36GB data, 15GB index). The log file is on a ...