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

learn more… | top users | synonyms (1)

2
votes
1answer
40 views

Agent service account shows as not clustered

One of the SQL Server 2008 R2 instances I administer is running on a two node Windows Cluster (Windows Server 2008 R2 Enterprise) When I run the DMV sys.dm_server_services, SQL Server Service Account ...
0
votes
0answers
52 views

ComputerNamePhysicalNetBIOS and “Current Host Server” shows different machines

I have a SQL Server 2008 R2 instance on a two node (say names X and Y) windows server cluster (Windows Server 2008 R2 Enterprise). Server Manager on both nodes shows 'X' as the 'Current Host ...
2
votes
2answers
1k views

How to log successful login attempts to a database?

Is there a way to log database name when auditing logins in SQL Server (2005 and above)? I basically want to log the username and the database name the user successfully connected to. I have tried ...
0
votes
1answer
40 views

skip the rows that already imported in sql server 2008 r2

I used the sql server 2008 r2 wizard to import excel file to my DB, now I'm trying to import more excel sheets may have the same data that I imported before. how can I import these excel sheets to the ...
8
votes
5answers
23k views

How to query last restore date in SQL Server?

Is there a TSQL query that shows the last restore datetime for a certain database?
5
votes
2answers
612 views

When was a Database taken Offline

I am trying to find a way to figure out when was couple of my sql databases were taken offline. I checked the the logs but could not find any such info and moreover there is no default trace enabled. ...
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 ...
0
votes
0answers
86 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
0
votes
0answers
46 views

Get list of rows in an hour bracket, grouped by 5 minute increments

Im beating my head the wall with this one. Using data from an attendance tracking table, I need to basically count how many people are inside the building at a given time, assuming that they are still ...
1
vote
1answer
44 views

Validate many SQL statements for correctness

I have about 150 reports generated from SQL statements. These reports are saved independently from the server. Some generated from sprocs, some from table-valued functions, but mostly just SQL ...
2
votes
0answers
31 views

Is there an equivalent to sys.dm_exec_query_memory_grants for non-waiting queries?

Basically I have a proc that executes and requires massive amounts of memory. We can sometimes get into a situation where the proc executes multiple times, in which case the memory grants are visible ...
3
votes
2answers
981 views

TempDB will not shrink. No Open Transactions

I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down. I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio. I ...
2
votes
0answers
44 views

Table queries locked during long insert SQL server

Everyday runs a sql job that loads data from bulk files and load them in auxiliary tables, to adapt and then copy to the destination table where applications read (and only read) the data. I am ...
0
votes
1answer
47 views

How to split a multivalued column, do a lookup and update another column?

This is a part of my previous question. How can I achieve the following for all the records in my table using a single query? SET @TeamMembers = TeamMembers from tblProjects where ID = '125' ...
1
vote
0answers
65 views

How does SQL Server know it was abnormally shutdown last time [closed]

I am doing background reading on this. Would like to know how does SQL Server know it was abnormally shutdown last time?
6
votes
1answer
200 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 | ...
1
vote
2answers
7k views

sql server management studio backup error [closed]

hello, i want a back up for my database in sql server management studio. i am using ms sql. but when i try to back up , i take the following error: =================================== Backup failed ...
4
votes
1answer
2k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
4
votes
3answers
8k views

Users cannot view tables in non-default schema in SSMS

I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has ...
0
votes
2answers
65 views

AVG function returns wrong average

I have a simple table with a column of type REAL. There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following: SELECT AVG(MyColumn) FROM dbo.MyTable I would expect to ...
0
votes
1answer
320 views

Single query approach to split and copy string values in a column

This is a part of my previous question. I have the below logic to split a string value from a column in table tblProjects as below: SELECT @Docs = Documents FROM tblProjects WHERE ID = @ID SET ...
2
votes
2answers
536 views

Log shipping - switchover and failback

We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance. Please help me with the following 1. Is it possible to switch back ...
0
votes
1answer
175 views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
0
votes
1answer
243 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
3
votes
1answer
1k views

SQL Server backup devices

I'm trying to find out how to detect if a backup device exists using T-SQL. This is how I create my backup device: EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = ...
1
vote
0answers
181 views

sqlcmd returning NUL instead of NULL for tinyint column

sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL. I ...
1
vote
0answers
97 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
0
votes
0answers
66 views

how to restrict user to run sql server database only on partiular ip only?

How to restrict user to run a SQL server database on in particular IP only? I have one database I just want to give to clients. But I wont Restrict that the database should run only on one ...
8
votes
1answer
43k 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 ...
6
votes
1answer
252 views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
5
votes
2answers
2k views

Find out what setting were used for a given stored procedure

As a follow-up to my previous question: is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ? It seems my ...
1
vote
2answers
69 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 ...
0
votes
1answer
21 views

Run SQL requests generated by some script

I'm using this script to delete data from all the tables in my database. It generates DELETE statements in proper order, each request in one row : DECLARE @schema_name sysname; DECLARE @table_name ...
3
votes
1answer
61 views

Full to Simple recovery mode during massive data update?

I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode. My plan is: 0 - take a backup for safety ...
1
vote
0answers
63 views

Too big database file 90GB [closed]

I just found that one of database growth to size of 90 GB (one file) The biggest table is about 23 GB , mostly containing integer data. Till now I dont have performance problems, but I think that ...
3
votes
4answers
12k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
0
votes
2answers
35 views

Problems with Insert( simple query - can't think something )

I have 2 tables Tblservicedesk and Tblservicedesk_temp Collumns: CodServiceDesk ID_Solicitacao Solicitante Departamento Tecnico Grupo Status Prioridade ...
-1
votes
1answer
19 views

Run Sp on linked server 2008 [closed]

How could i run my sp on a linked server ? Using sql-server-2008 Exec my_sp [db-srv_name].[db-name].[dbo] Please help me i am new in sql
3
votes
2answers
6k views

SQL Server 2008 user, group, or role already exists in the current database

I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008. Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on ...
1
vote
2answers
131 views

How to split url in sql server like this

Here is the url www.abc.com/a/b/c/d/e/f/g/h/i I want result like this www.abc.com/a/b/c/d/e/f/g/h/i www.abc.com/a/b/c/d/e/f/g/h www.abc.com/a/b/c/d/e/f/g www.abc.com/a/b/c/d/e/f ...
6
votes
1answer
3k views

Database Mirroring with TDE

I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'. I have setup TDE on both the principal and ...
7
votes
4answers
42k views

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
2
votes
1answer
114 views

T-SQL query to find backup folder for specific maintenance plan

I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012. Is there any way to query the maintenance plans for databases, ...
3
votes
1answer
8k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
1
vote
0answers
80 views

Rebuild/Re-organize index and Update statistics in SQL server

For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan. I want to figure out if auto update ...
3
votes
0answers
356 views

How unsafe is it to change the data source of repl_distributor on a publisher

I have a long story that I will condense to a few bullet points that leads up to my question Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication ...
0
votes
1answer
3k views

Conversion failed when converting the nvarchar value '0.000000' to data type int

I have a code select CONVERT(NUMERIC, CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0 ELSE FLEXFIELD4 END ) FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003' which is ...
0
votes
3answers
84 views

Keeping an up to date copy of a SQL Server database on another server [closed]

We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers ...
1
vote
1answer
99 views

how to group data against more than one conditions

I have a table named Employees with below two columns. ================== Employees 1- EmpId (auto incremental id) 2- EmpName (varchar) There is another table named 'LateDays' with below five ...
0
votes
1answer
96 views

Insert/Update SQL Server Stored Procedure

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and ...