Tagged Questions
0
votes
1answer
22 views
File locations for Full Text Catalogs using SQL Server 2005 on VMware
I have an issue with one of our development SQL Server Servers. We have databases that use full text search catalogs. Just some background info, our servers are configured to use separate drives for ...
0
votes
3answers
65 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
6
votes
2answers
159 views
If I fail over one database, do the others that share the same mirror endpoint fail over as well?
We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.
If I go into ...
0
votes
0answers
63 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
3
votes
2answers
53 views
How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION ...
3
votes
3answers
84 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
2
votes
2answers
80 views
Restoring differential backup from SQL Server 2005 to SQL Server 2012
We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.
Please ...
0
votes
2answers
48 views
Can I copy the table structure of a #temp table to a new physical table?
I have a #tempTable that was created using
SELECT *
INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls',
'SELECT * FROM [Sheet1$]')
Is there an easy way ...
5
votes
1answer
81 views
How do I shrink the physical Transaction Log file when it's the principal in a mirror?
We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
1
vote
1answer
40 views
Limiting user access to tables based on a ROLE
I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:
Created a database role called NO_HR
Added the tables to this role under Securables that should be ...
-1
votes
2answers
65 views
SQL Server Transaction logs
Could someone tell me the role of transaction logs in SQL Server ?
I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
2
votes
1answer
73 views
Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box
We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
2
votes
3answers
62 views
How to take SQL Server database under this scenario?
I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
2
votes
1answer
55 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
3
votes
3answers
49 views
Which system databases should I checkdb and defrag indices?
I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best ...
4
votes
2answers
99 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 ...
2
votes
1answer
64 views
Restoring a database back to a point in time before full backup was made
A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set ...
1
vote
1answer
86 views
SQL Server taking a lot of memory
I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has ...
0
votes
0answers
42 views
Invalid Object SQL Server 2005 Express
I have 2 SQL Servers, let's call them A and B. I have a ColdFusion app that runs on server A. It connects to my database, MyDb. I needed to update the db content so I backed up the same db on server B ...
1
vote
1answer
73 views
SQL Server 2005 error while starting service
I am getting error while starting the service & errorlog is:
2013-04-04 14:50:49.14 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) ...
5
votes
1answer
103 views
Unexpected Table Scan with Parameterized LIKE
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
2
votes
3answers
100 views
Backups script completes, but doesn't back up all dbs
I'm running sql server 2005 and I've made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has ...
4
votes
1answer
75 views
Process attempted to unlock a resource it does not own
SQL Server 2005 SP4 32-Bit
I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean ...
2
votes
1answer
38 views
FOR XML is generating a empty first line
I'm parsing with flash a XML file generated by this code:
:XML ON
USE MyDatabaseName
GO
SET NOCOUNT ON
SELECT * FROM ProgramacionDia as programa order by hora
FOR XML AUTO,
ROOT ('toflash'),
...
3
votes
2answers
109 views
Identifying which values do NOT match a table row
I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.
To better explain, here's what I would do now, to check which IDs of the list ...
5
votes
5answers
170 views
Run Multiple Remote Jobs
I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job ...
5
votes
3answers
157 views
What type of SQL Server Replication fits our scenario? Merge or Transactional?
Background
In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server. We additionally have another (non-MSSQL) database which we use for our POS system, which ...
1
vote
1answer
421 views
IF NOT NULL then UPDATE else keep the value of the field
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN ...
0
votes
3answers
77 views
How to check if database is in mirrored mode?
I have two database servers A and B where I have databases set up in mirroring namely X. There is another database on server A named Y which is only present on database server A. Y database has a ...
1
vote
3answers
163 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
