Tagged Questions
2
votes
4answers
76 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
0
votes
3answers
81 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 ...
1
vote
2answers
69 views
How to backup databases to ensure data recovery
I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had ...
2
votes
3answers
64 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 ...
5
votes
1answer
110 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 ...
0
votes
2answers
75 views
Missing data after attaching .mdf file
Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that).
...
3
votes
2answers
115 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 ...
1
vote
1answer
692 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
88 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 ...
3
votes
2answers
70 views
SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.
I inherited this database and it came to ...
0
votes
1answer
136 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
0
votes
1answer
144 views
FETCH API_CURSOR causing open transaction in tempDB
A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when ...
1
vote
1answer
47 views
Scheduling a backup in SQL Server 2005
I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected ...
7
votes
2answers
178 views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
4
votes
1answer
66 views
Index Evaluation
I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index.
The non ...
1
vote
0answers
46 views
SQL Server 2005 Relationship [closed]
I have SQL Server 2005. Tables have relations which each others.
Some tables are libraby. 1 table has 10 foreign relations. I dont like the Database performance, a little slowly and I dont know what ...
0
votes
1answer
68 views
Getting the exact edited data from a column in SQL Server
I have two Tables:
Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).
ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, ...
2
votes
2answers
53 views
2 databases in sql server 2005 “Production Server”
Can 2 databases decrease the performance in a sql 2005 production server? Server specs:
Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB sata hard drive hot plug, windows server 2003 R2 ...
1
vote
1answer
63 views
Possible to query all SQL Server Indexes which have page locks enabled?
Is it possible to run a SQL Server Query in order to view all SQL Server Indexes which have page locks enabled?
0
votes
0answers
24 views
SQL services wont start due to incorrect drive letter [duplicate]
Possible Duplicate:
SQL services wont start due to incorrect drive letter after windows restore
SQL services wont start due to incorrect drive letter after windows restore
Had a server ...
0
votes
0answers
26 views
Max connections sql server express could have? [duplicate]
Possible Duplicate:
Max connections SQL Server Express could have?
I have sql server 2005 express edition installed on one of my client's machine, and lots of other users are also connected ...
5
votes
2answers
134 views
Deleting duplicate records when using text datatype
I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.
Normally ...
4
votes
1answer
120 views
Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?
I kick off the following command in my app:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE
Note: ...
12
votes
2answers
165 views
Does firing off an indefinite WAITFOR increase the log file size?
In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
The ...
2
votes
2answers
89 views
Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
1
vote
3answers
120 views
Check of SQL Server upgrade success
I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished ...
4
votes
2answers
112 views
SQL Server update and database transfer
In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What ...
3
votes
2answers
450 views
command or query to ping SQL server
I have 2 windows 7 machines on a very high security domain one acting as a server, the other as a client.
Each box has a SQL Server Express 2005 database and application "A", however I'm only using ...
5
votes
3answers
287 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
5
votes
2answers
2k views
SQL Server backup failing. Error: 3041, Severity: 16, State: 1
First of all, I'm very new at stackexchange, so please bear with me.
I'm running a SQL Server 9.0.4060.
My problem is: my SQL Server backup keeps failing on a lot of the databases.
The Maintenance ...
0
votes
2answers
76 views
Grouping data checking against top values
I've got data in this form:
CallNum Value Accepted
--------------------------------------
971374 81.482204444473609 True
971374 83.783551111089764 False
971374 97.936875555547886 ...
1
vote
9answers
259 views
Is there any way to run the definition of multiple stored procedure at a time?
I have created the definition of more than 200 stored procedures. My problem is that I have to execute them on a different server and on a different database.
The process which I am following is that ...
0
votes
0answers
102 views
How do I get the date that each user last logged onto the server / database in SQL Server 2005
Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.
ALTER ...
0
votes
3answers
387 views
How to attach .mdf file to SQL Server 2005 without an .ldf file?
I want to attach a database to SQL Server 2005 but I have only its .mdf file
How can I attach it?
1
vote
1answer
207 views
How do I query based on percentages of a subset of values?
I have a table with a column named Type that can have some set of values. For instance, A, B, C, D, etc. Behind the scenes this is mapped to an Enum.
I need to query this table to get a percentage ...
2
votes
3answers
181 views
selecting multiple instances of a record based on record lifespan over years
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
1
vote
1answer
97 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
2answers
186 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
1
vote
1answer
89 views
group by and having query need help?
I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:
select
...
3
votes
3answers
10k views
SELECT DISTINCT on one column, while returning the other columns?
I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.
My SQL ...
2
votes
4answers
302 views
SQl Server Password Change after installation
I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in window authentication mode, and I want to access the database in ...
3
votes
1answer
246 views
Send data from SQL to a serial port or IP address
I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a ...
5
votes
2answers
103 views
New Cluster Testing - Best Practice
We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.
We are looking for suggestions on a set of tests we could perform to test failover of the ...
4
votes
2answers
523 views
SQL Server Memory Paging Issue
We have a dedicated SQL server running Windows Server 2003 R2 Standard x64 SP2 and SQL Server 2005 (64-bit) Standard Edition with 16GB RAM. Every night we're experiencing severe performance issues ...
2
votes
1answer
3k views
CASE statement with IS NULL and NOT NULL
Is there any better way to write the lines below in SQL Server 2005?
CASE
WHEN (ID IS NULL)
THEN 'YES'
WHEN (ID IS NOT NULL)
THEN 'NO'
END AS ID_Value,
7
votes
3answers
718 views
Table partitioning for archiving data
Scenario:
two databases: DB_A and DB_Archive with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A and moved
to DB_Archive mainly to leave thing ...
1
vote
1answer
312 views
How to avoid duplicating information in a series of nested SELECT statements?
I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
1
vote
2answers
122 views
Unable to connect SQL Server 2008 from SQL Server 2005 DB
I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error:
...
1
vote
0answers
111 views
Database model problem from MS Access to SQL Server 2005
Good Day,
I am a C# programmer but I have a problem with a database model on SQL Server 2005. I have a client who told me to rebuild his database from Access to SQL Server 2005 and actually I did but ...
2
votes
2answers
77 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 ...
