This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

0
votes
0answers
19 views

Sql agent job fails with access denied error

We are trying to execute a SQL agent job which executes a step to shut down a service on DEV ENV using CMDexec, but it fails with error below: Executed as user: ABC\mssql. System error 5 has ...
4
votes
1answer
408 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
0
votes
0answers
15 views

Restore databases from prod to test server with data over say X days in SQL Server 2005

We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis. Moreover, they require it in a way that ...
4
votes
1answer
525 views

How to restore symmetric key on the new database?

I have an issue with symmetric key restoring on the another database. I have table in the first(old) database with encrypted column.(The table contains encrypted data) The key in the old database has ...
33
votes
2answers
80k views

How to the get 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
0
votes
0answers
28 views

How can I run a SQL Server 2000 database on SQL Server 2005 Express with a different location for files

I am trying to help a client out after a server crash. The temporary replacement server has different drive letters and data file locations. Here are the system details as I understand them: ...
0
votes
1answer
11 views

Field Prerequisite on Another

I would like to know if it's possible to have two fields (in this case bits) in the same table where one is prerequisite on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
9
votes
2answers
3k views

Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005). The subscription databases are also SQL Server 2008 R2, ...
1
vote
1answer
100 views

Powershell and SQL Script Execution in selected schemas

Any one please help me on this one also..I have got many schemas under one database. And in that database I have some SQL Scripts to be executed. My issue is that I should be able to execute those sql ...
0
votes
2answers
360 views

Insert data in relation table and into data table with less query cost

I have a similar table layout: create table data ( id int identity primary key , value int not null); create table relation (id int identity primary key , ...
0
votes
0answers
36 views

A transition table, is it a good idea?

I'm writing an application that stores a project's C++ API representation. For example, I have tables like Class, Function, FunctionParameter, DerivedClass, BaseClass, and so on. Every once in a ...
5
votes
1answer
440 views

What is this upside-down silver key icon

I inherited a SQL Server 2005 3rd party server and database to look into to be able to generate reports and dashboards on. On one table, I found a new icon: a blue-silver upside-down key, alongside ...
-4
votes
1answer
138 views

Trying to attach a .mdf with missing .ldf [closed]

I am trying to attach a .mdf database file to a SQL Servre 2005 however we do NOT have the .ldf and have been unsuccessful in all our attempts to date. I should also mention there is no back-up to ...
0
votes
0answers
19 views

Receiving Error: 18456, Severity: 14, State: 16 when trying to deploy SSRS Report

Our SSRS reports have stopped working. I tried re-deploying it but got a message stating that Error: 18456, Severity: 14, State: 16 an internal error occurred on the report server If checked ...
3
votes
3answers
2k views

SQL Server Concise List Of Trace Flags

I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking. Looking at this query which uses 8691, I was wondering ...
6
votes
1answer
2k views

How to improve Procedure Cache Hit Ratio?

From what I can tell Procedure Cache Hit Ratio below 95% is a problem. On my box, the values hover from 85 to 95%. How do I fix this issue? The server seems to have plenty of RAM so that ...
2
votes
2answers
136 views

PowerShell to get database schemas

I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell ...
2
votes
1answer
145 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
20
votes
4answers
49k views

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem ...
1
vote
1answer
61 views

SQL Server 2012 - Dropping Snapshots takes hours

We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL ...
6
votes
1answer
190 views

Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | ...
0
votes
1answer
28 views

SQL Server : how to disable connections to a linked server?

SQL Server Linked Server to default instance - service is disabled but connection is still possible I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed. The SQL ...
3
votes
5answers
2k views

Can I restore a database to a point of time without backup?

I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?
0
votes
2answers
177 views

Bidireccional synchronization between a local Sql Server 2005 database and a SQL Azure database using Sql Data Sync

I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the ...
0
votes
1answer
419 views

SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?

I have a SQL Server 2005 instance that has worked fine for years. There have been no changes that I am aware of. Recently, I found that if I stop and then start the instance in SQL Server ...
7
votes
1answer
36k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
0
votes
2answers
205 views

SQL Server 2005 to 2012 Replication, cannot access replicated data

I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes ...
2
votes
2answers
114 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
1
vote
2answers
61 views

How do I prevent my clients viewing my database definition when it is installed on their server?

I want to give my database to the client to install on their server. However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run ...
1
vote
1answer
36 views

SQL logins Audit for SQL Server 2005/2008

For the Security Audit of SQL servers 2005/8 R2, We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via ...
4
votes
3answers
591 views

Table structure for many custom profiles

I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers. For example, I have a basic profile that is ...
7
votes
2answers
1k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
1
vote
1answer
739 views

Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop

We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble. Specifically, we created a publication ...
6
votes
1answer
87 views

Why and how sometimes I get the “absent table error” while there are definitely Sch-M lock?

I have a synthetic test, which reproduces some error we have on the production environment. Here are the 2 scripts to reproduce it: 1st DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS; SET NOCOUNT ON; ...
1
vote
2answers
58 views

Detecting and fixing database corruption on SQL Server mirrored database

I’m new to my current company and we have a System Administrator but no DBA. In the past I’ve taken care of DBA related issues at other companies. I’ve run into and corrected database corruption in ...
1
vote
1answer
60 views

Manage multiple short-term tables with same schema or use single table for short-term data?

My team has proposed to store session state data for each open client session in a table in the database. The application server creates a new table for each client session as the user logs in via ...
1
vote
1answer
82 views

Need to query records based on Date only (Sample Query given)

sSQL = " SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, ordHandling, ordTotal, ordDiscount, ordPrivateStatus FROM orders WHERE (ordStatus = 7) " I want to display orders that ...
4
votes
3answers
529 views

sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

I have an issue SQL Server 2005. sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment). No error message is given / reported, the query just keeps executing but ...
0
votes
3answers
44 views

Problem to access the SQL Server

I have SQL Server 2005 which is installed on server machine. I was working fine on SQL server before I change the server machine IP. I changed server machine IP to host the Web Service on it. But ...
0
votes
1answer
21 views

Cleaning disabled SQL agent jobs in SQL Server 2005 and above

For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working ...
0
votes
1answer
33 views

Add linked server from SQL-Server to MySQL: “Data source name not found” (Error:7303)

I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide: ...
0
votes
1answer
19 views

How do I load this SAS text file to SQL Server 2005

Every 3 months, I have to load several fixed-width text files into our SQL Server 2005 database. They can have anywhere from 5 to 798 columns (seriously) and the columns change pretty regularly. ...
3
votes
1answer
57 views

Impact of changing char(10) to varchar(12) on massive table

I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows. It has a column called 'balance_forward' that is char(10) (Unusual choice for a ...
0
votes
1answer
38 views

What are the implications of upgrading statistics SQL Server [closed]

I Have a SQL Server 2005 DB, with 500Gb of Data. The Statics do not upgrade from 2010 and this past saturday were updated. Yesterday the querys on DB were very slow and caused bad response times. ...
0
votes
0answers
9 views

I want to make stored procedure for the TABLE1 that have another table2 column which is not primary key in sql server

I have two table Maker(maker_id(PK), maker_code, maker_desp) Model(model_id(PK), model_code, model_desp, maker_id(FK)) Now I have a gridview 2 in asp.net c# MAKER TABLE (GRIDVIEW 1): Maker_id, ...
2
votes
1answer
42 views

Can I throttle individual databases instances on one SQL Server 2005 box

I am running a very old server that has a database that is being permanently retired within 14 months. My company will not invest anymore in hardware or software for this system as we are moving to a ...
1
vote
1answer
35 views

maximum allocate RAM for MS SQL Server

I have MS SQL Server with different size of RAM. So I would like to control the values which are set for the RAM. Somewhere I read a 'rule' to calculate the maximum of RAM. These SQL Server are only ...
2
votes
2answers
2k 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, ...
9
votes
3answers
9k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
0
votes
1answer
52 views

Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G?

I recently deleted some data from a table that was not needed and growing to too big a size with this query which provided to me by a support tech... declare @mdate datetime set ...