This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

1
vote
1answer
144 views

SQL Server 2014 .bak file error - The media family on device 'D:\..' is incorrectly formed

Worst case scenario - the server crashed and the 2014 SQL Server .bak file had to be recovered from the failed disk. The .bak file I have is over 20gb in size but is unusable when trying to restore ...
3
votes
2answers
68 views

What happens with statistics when reverting to a database snapshot

Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics ...
5
votes
2answers
3k views

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
32
votes
2answers
111 views

SQL Server unpredictable select results (dbms error?)

Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error? After some investigation ...
0
votes
1answer
83 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
0
votes
1answer
32 views

Analyse and report disk usage growth historically

Is it possible to use historic information to show disk usage growth? I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in ...
2
votes
1answer
336 views

What means Command column in sp_who2 and text column in sys.dm_exec_sql_text?

I have a script that shows commands being fired in SQL Server 2008 database as follows: --shows who is active and executed commands declare @SPID int=null, @Status varchar(max) = null, @LOGIN ...
1
vote
0answers
18 views

Dynamic Paramterized Replication to a PUSH subscription

I have a publication that I want to filter by suser_sname() filter. This works perfectly fine when I'm using a pull subscription. However, I need to use a push. How do reflect this suser_sname() as ...
1
vote
2answers
60 views

Running a T-SQL job under another user

I cant find my proxy user to "Run as" that user !? I am confused which kind of proxy user is appropriate to run a job with a simple T-SQL command. (I have created a credential and proxy but cant find ...
0
votes
2answers
50 views

Query to get data in one status not in another status

I have a table with status column with different status values like 'A','C','E','I'. My data is like this: ---------------------------------------------- TagNo TruckNo VerifiedDateTime ...
7
votes
4answers
24k views

"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
0
votes
0answers
39 views

SQL Server consuming large amount of RAM [duplicate]

I have Server 2012 with two 2.10 Ghz processors and 64GB of RAM. I have SQL server 2008 installed on this server. but this SQL server is consuming 70-80% of memory. I have limited SQL memory to 24GB ...
0
votes
0answers
18 views

Is it possible to retain access to table that will be dropped and recreated?

I have a GIS table which is small and is dropped and recreated from MAPINFO. Each time I drop and recreate that table I grant access to roles created. Is it possible to sustain access during drop and ...
2
votes
2answers
1k views

Datawarehouse - Dimension, Fact, or Degenerative dimension

I am building a warehouse for a billing system that bills a number of services/utilities on the same invoice, as such I am having real trouble conforming to a recognised design method for the ...
-4
votes
0answers
28 views

Not able to attach database backup file .bak into SQL Server 2008 [closed]

I have a database backup file named iyym.bak. How can I attach the database to my SQL Server 2008? I tried inside SQL Server Management Studio the following: right click on data base folder in ...
2
votes
1answer
35 views

SQL Server : GRANT REFERENCES Fails With Msg 15151 as Windows User with sysadmin role and also as 'sa' [closed]

Okay, what am I missing? Someone please hit me with the proverbial clue-by-four. On SQL Server 2008 R2, I execute this command: GRANT REFERENCES ON ASYMMETRIC KEY ::IRESKey to XYZZYEXT; I get this ...
1
vote
2answers
48 views

Get time elapsed between log entries

At a company, fluids are prepared and held in tanks, following a preparation/recipe. The preparations move from tank to tank depending on needs. The tasks are logged in sequence into a table. What I ...
0
votes
0answers
14 views

Informatica is causing Convert_implicit in SQL Server 2008

In our project a datafile is loaded into SQLServer using Informatica. The source column is defined in Informatica as UniChar.60 The target column in SQL Server is defined as varchar(60) But when the ...
2
votes
1answer
109 views

SET CONTEXT_INFO for tracking specific SQL requests

I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state. I can see the machine name and ...
7
votes
5answers
4k views

What's a good tool for visualizing SQL Agent jobs? [closed]

I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful? Offhand, there are three things I'd like to see in such a ...
0
votes
1answer
46 views

DB Permission: User can drop only their created db's?

I want to set permission against a user, which can create database and drop on their created databases neither any database. Any idea/suggestion ?
2
votes
3answers
11k views

Generate create script for all indexes

I am working on documenting my databases, and I would like to create a list of all of the indexes in my database. The reason I want to do this is so that I can track changes to my indexes overtime. ...
0
votes
2answers
35 views

Mapping a Login to a User

On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors. We use Windows authentication on our DBs. On my test system I ...
0
votes
1answer
56 views

SQL Server Deadlock on delete

I have a query like this: with xx as ( SELECT [FilePath], ROW_NUMBER() over (partition by [FilePath] order by [FilePath]) as RowNumber FROM [dbo].[foo] ...
1
vote
2answers
3k views

unable to bring database online from recovery pending

I am trying to bring a SQL Server database online and accessible that is in Recovery Pending state. The SQL Server service uses an Active directory account that is a domain administrator and a ...
0
votes
2answers
57 views

Find tables with modified data after a specified datetime [duplicate]

I am performing few inserts and updates on my database using tool provided by app supplier. I want to compare the updated database and backup after running the tool. But my database size is around ...
1
vote
1answer
53 views

Full text index does not honour deleted words - They are still indexed

I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but ...
1
vote
1answer
33 views

Proper way to store a password within a SQL Server agent job

I know this is a stupid idea, but I'm having a hard time finding a better solution right now. I need to execute several sql stored procedures on a daily basis, then execute a powershell script to ...
1
vote
0answers
38 views

tempdb contention issue and excessive pagewrites/s

Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there... Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM. ...
1
vote
2answers
57 views

Different table size on sp_spaceused and standard report “Disk Usage by Top Tables”

I ran sp_spaceused and Disk Usage by Top Tables standard report for a table. The results for sp_spaceused are: name rows reserved data index_size unused SomeTable <1301755> ...
0
votes
0answers
8 views

Find unpublished columns via RMO

The stored procedure sp_helpmergearticlecolumn will return all columns for a table and have a bit set if the column is published or not. The MergeArticle RMO object has the function ...
1
vote
1answer
51 views

Converting VARBINARY HEX to VARCHAR

I have a fun problem, I have data stored in SQL Server 2008 VARDATA column as Hex. I need to see what this translated to string is. I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a ...
0
votes
1answer
25 views

Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation

I am trying to evaluate SQL Server 2014 by using the 2014 evaluation edition to upgrade an in place SQL Server 2008 Standard sp4 instance. At the end of the process I expect to have a SQL Server ...
6
votes
2answers
9k views

Quick look at how much RAM is allocated to SQL Server?

With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server. With SQL Server 2008, the Working Set or Commit Size never ...
0
votes
1answer
29 views

SQL problem with checking next and previous rows

I have a list and the returned table looks like this. I took the preview of only one car but there are many more. What I need to do now is check that the current KM value is larger then the ...
12
votes
2answers
1k views

Why does SELECT 1/2 return 0?

As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0. I am using SQL Server 2008.
0
votes
1answer
44 views

How can I choose the best autogrowth option for my new database? [duplicate]

I want to create a new database that I can use for testing, restoring it from a full backup from a production database. These backup file sizes are around 45,486KB, and the production database size is ...
4
votes
2answers
438 views

How do I query only for committed data while avoid a deadlock

I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables At the same time there are ...
0
votes
1answer
10 views

Building test site, update dates (back date records)

I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting. I was thinking about how to do this and not sure if I am on the correct path. The big problem I have is I have ...
1
vote
1answer
81 views

Delete files from disk, with a job, respecting some rules ( SQL Server 2008 )

We have An application that our customers use to download and upload files. I need to delete these files from disk after 30 days, of if it was downloded, in 7 days. How can i Use the script below, ...
0
votes
1answer
40 views

SQL update next row in select

I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data. Each row has a field for KM (mileage in KM metric unit), ...
8
votes
3answers
36k views

How to Handle TimeZone Properly in SQL SERVER?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
3
votes
4answers
16k views

How to drop multiple tables with common prefix in one query?

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query? something like that table names: LG_001_01_STLINE, LG_001_02_STFICHE
0
votes
1answer
40 views

Merging two Select queries with different where statements

I have this query below in which I am trying to get results for the month and year to date (account balance). SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance' FROM OJDT T0 ...
3
votes
3answers
4k views

bcp in of datetime type data results in an “Invalid date / time format”

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited. I then bcp in to a truncated table ...
0
votes
1answer
76 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
1answer
28 views

Update incoming rows based on % calculation

Below is the business requirement which I have to achieve... As a Sales Data Operations process/product owner, I want to be able to identify share class conversions (aka "wash trades") coded as ...
0
votes
1answer
40 views

copy table with all constraint

how I would copy a table with all constraints? I run select * into myNewTable from baseTable where 1=0 This copy a table but no with constraints
2
votes
1answer
93 views

Microsoft SQL Server 2008 encryption over multiple DB versions

I have about 5 clients deployed running different versions of SQL Server 2008 (Some standard, some enterprise, etc.) And I am being tasked with encrypting the data so: The software we are using can ...
6
votes
1answer
4k views

How to clone a user in SQL Server 2008 R2?

Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?