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

learn more… | top users | synonyms (2)

0
votes
1answer
151 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 ...
6
votes
4answers
5k views

Re enable Windows Authetication in SQL Server [duplicate]

My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password. I ...
0
votes
2answers
237 views

Error with making backup in sql Server

When I try to backup a SQL Server 2008 database, I have an error. Is anybody know what is the error? TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for ...
2
votes
3answers
1k views

Changing a column's data type fills the transaction log

Running this code: ALTER TABLE npidata ALTER COLUMN npi varchar(20) Gives this error: Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why ...
7
votes
2answers
25k views

IntelliSense not working but it is enabled

This problem is really proving to be a tricky one (and quite annoying). In SQL Server Management Studio 2008, up until a few days ago my IntelliSense was working great. Then all of a sudden it ...
2
votes
1answer
12k views

How to find which tables and views a user has access to?

We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need. We ...
0
votes
0answers
23 views

SQL TDE Key Format

I'm trying to find the Key Format that SQL TDE uses. Is it PKCS? If so, what #? I haven't been able to identify this based on research done on MS website. And, there is no mention of key format during ...
-4
votes
0answers
15 views

SQL Server 2008 on Windows XP [on hold]

We have SQL Server 2008 installed on Windows Server 2008 Standard Edition. We have ladescape-like client connected to the front-end application with ODBC configured with SQL Server. The ODBC test is ...
3
votes
2answers
66 views

Using sp_getapplock to implement a queue. Is it correct? Is there a better way?

I've been reading a series of posts by Paul White about SQL Server Isolation Levels and came across a phrase: To emphasise the point, pseudo-constraints written in T-SQL have to perform ...
2
votes
1answer
71 views

Using a Linked Server to import data from Excel

I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins. What if I am just using a linked server to import data into SQL Server from an ...
6
votes
1answer
20k views

Finding the last time a table was updated

The query: SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate; ...will tell me the last time a table was created ...
0
votes
1answer
1k views

syspolicy_purge_history job failed at step 3

In one of my SQL Server 2008 sp1 standalone instances, syspolicy_purge_history job is fails at step 3 Erase Phantom System Health Records. Below is the command - (Get-Item ...
9
votes
1answer
149 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
3
votes
1answer
599 views

SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm. When setting up the subscription ...
4
votes
1answer
539 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
8
votes
1answer
54k 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 ...
3
votes
2answers
211 views

Error: Cannot Generate SSPI context

When someone tries to connect to a SQL Server instance, the error shows up: It's not possible do generate SSPI context. Yesterday we had a blackout (don't know how to say this expression in ...
0
votes
0answers
39 views

Event 17058 - initerrlog: Could not open error log file (SQL Server)

I'm new to SQL Server and encountering an error when running sqlservr.exe (Microsoft® SQL Server® 2008 R2 SP2 - Express Edition) Error 17058, Severity: 16, State: 1. initerrlog: Could not open ...
2
votes
2answers
64 views

Add a flag when certain conditions are met

I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are ...
2
votes
3answers
2k views

Copying distinct rows from one table to another

In SQL Server, I can copy one table into another by doing: Select * into desttable from sourcetable However, there are duplicate rows in sourcetable and I would like to just copy distinct rows. How ...
10
votes
5answers
18k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
0
votes
0answers
15 views

Is there a way to set the data source in Report Manager for whole folder rather than each report?

I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports ...
8
votes
1answer
5k views

How to trace SQL queries which are crashing SQL Server

We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here). Our application runs Hibernate for DB access, and since we ...
9
votes
5answers
2k views

SQL Server command line usage? Is it possible?

I've been trying to use the SQL Server Management Studio 2008's built in "Solution Explorer" to manage a project and after 2 weeks of struggling with it, I've decided I don't really care for it. It's ...
4
votes
2answers
4k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
2
votes
1answer
64 views

Scramble the data output of a stored procedure

I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day ...
7
votes
3answers
23k views

Query to report disk space allocation and used space

We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage). I'd like to write a query (report) for a single database ...
0
votes
1answer
295 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
0
votes
1answer
35 views

DTS Type Mismatch at Join

I am new to ETL and VB in general and need some help resolving this issue. I have looked online and found some interesting answers but none seem to be working for me. varamt = Lookup ( ...
0
votes
1answer
217 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
5
votes
1answer
262 views

What units is Page Life Expectancy in?

I'm currently using the following query to get the Page Life Expectancy from SQL Server: declare @PLE int select @PLE=cntr_value from psql1.master.sys.dm_os_performance_counters where ...
2
votes
1answer
313 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
6
votes
1answer
116 views

How to find the stored procedure which executed an event?

I have a problem where a certain stored procedure disappears occasionally and I need to find out which script deletes it. I found this piece of code which gives the events related to the deletion of ...
2
votes
1answer
27 views

Propper way to monitor SQL Server mirror status from service or job?

We have a production DB which is in a mirror configuration. It is in synchronous high safety with automatic failover configuration. Right now we monitor it using windows service which pings it. Is ...
2
votes
0answers
43 views

SQL Server restore differential after restoring full backup

Here is the situation: I had a full backup, and a differential backup. I have restored the full backup into the database. But accidentally lost that backup file. Now I would like to apply the ...
4
votes
1answer
33 views

Should Service Broker use SQL or Windows Authentication?

I was curious what the best practice would be, for properly setting up Service Broker in SQL Server 2008. Should I use a SQL authenticated user, or should I use a Windows NT Authenticated user based ...
1
vote
2answers
482 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
5
votes
2answers
2k views

Linked Server Risks

I'm implementing a new feature which requires data from databases on multiple servers. I just need to union data from all these servers and sort it. The two options that come to mind are: Use linked ...
5
votes
4answers
14k views

Query without having to specify table's schema

I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename. In the table properties it lists 'erpadmin' as the ...
2
votes
1answer
83 views

SQL Server 2008 Edition Upgrade Enterprise Evaluation to Standard problem

On my Windows Server 2012 machine SQL Server 2008 evaluation time has expired. After Edition Upgrade nothing happens, SQL Service doesn't start. In service log still is information about expired ...
3
votes
2answers
431 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
3
votes
1answer
1k views

SQL Server stored procedure working when run manually, not running from SQL Server Agent

I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error. line 9, character 9, unexpected end of input The code ...
4
votes
3answers
5k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
3
votes
1answer
116 views

VARCHAR NULL storage

I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of ...
2
votes
1answer
372 views

Setting up simple question & answer database

I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it ...
1
vote
1answer
26 views

Index design question

This query runs for 60 seconds: SELECT mychecklis0, .... FROM dbo.MyChecklistStatus mychecklis0_ WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1 There is an index created ...
2
votes
1answer
84 views

How to find out if anything is running as or relies upon a user account in anyway before removing it?

Running SQL Server 2008. So I need to get around to removing an old users account, but I'm worried that just disabling or deleting it right away might cause some things to stop working properly if ...
4
votes
1answer
706 views

SQL Server Troubleshooting Replication and Long-Running fn_dblog query

I am trying to get (pull) Transactional Replication working again for a subscriber. Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
5
votes
5answers
7k views

A query that lists all mapped users for a given login

When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
1
vote
1answer
64 views

Identity seed increased when using IDENTITY_INSERT

Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result. My scenario is that I am writing unit tests (using tsqlt) and this ...