SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

2
votes
2answers
46 views

Show Query Results Vertical

I am wanting to show these results vertical as opposed to the default horizontal. My desired output is like this Passed w/ notes 26 Passed w/o notes 28 Passed w/ notes 52 ...
1
vote
0answers
23 views

Connection to database causes SSPI context error

I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted ...
0
votes
1answer
14 views

Catalog of LinkedServer2 from Server1 shows databases on Server1

I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1! I am sure I've done something bone-headed or am overlooking something horrendously obvious, ...
4
votes
1answer
130 views

Query All Databases Listed In Table

I have been using a cursor to get my results, but it is taking an extended period of time to return my result set. I am hoping to find a way to QUICKLY get the results returned (if at all possible). ...
3
votes
0answers
103 views

Read/Write Deadlock on Ms-Sql Server

We are facing deadlocks when store procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of sp. I have ...
0
votes
1answer
60 views

Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?

One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of ...
1
vote
0answers
25 views

Compare Strings After Character

I need to check email addresses against an internal known bad domain database. My internal database has domains in the form of myfreepaysite.com just no @ sign. I have been using this syntax to ...
1
vote
1answer
59 views

Hide Schemas and Tables

I have the following structure: Views dbo.vSales Sec.vSales Tables dbo.Security Sec.Products Sec.Countries Sec.Users I have 2 groups accessing my Database: Administrators Standard_User ...
1
vote
1answer
54 views

Why is deadlock alert not working?

I created a deadlock alert including notification to an operator like this: EXEC msdb.dbo.sp_add_alert @name=N'1205 Deadlock', @message_id=1205, @severity=0, @enabled=1, ...
0
votes
0answers
15 views

Can connect to the local server default instance with the servername but not via other means

I have noticed that I cannot connect to the default (and the only) instance of a production server (SQL Server 2008 R2) by specifying [single dot]., (local) or 127.0.0.1 but can by specifying the name ...
0
votes
1answer
31 views

Sql Server Security Scenario

I need some advice/help... I need to implement database security in some Databases but I don't know how to do that with this given scenario... Using LDAP Auth We have Table1 In Table1 one we have ...
0
votes
0answers
40 views

Search All Views On A Database

We have about 800 views on a SQL Server 2000 database, about once a week I run a procedure to give me information so I can see which stores have LIKE '%Camo%' in there inventory so I know who to send ...
1
vote
0answers
50 views

Why is SQL Profiler showing empty for database name and database id?

I have some code that uses Entity Framework to call a stored procedure in SQL Server. If I run SQL Profiler and filter by the database it is calling, I can see that the profiler correctly shows the ...
1
vote
0answers
23 views

“The schema script could not be propagated to the subscriber” error has SQL code instead of file name

This type of error is unfortunately not uncommon. One report of many is this one on Stack Overflow: ...
0
votes
2answers
68 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
6
votes
1answer
215 views

SQL Server 2008R2 Alias not working

I have an interesting situation here. We're basically trying to migrate some SQL Server infrastructure to new hardware, and one of the boxes (single-instance, 2-node cluster) is running 2008R2. The ...
0
votes
3answers
25 views

How to insert/combine values from multiple tables?

I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value") Example: January code | test_name | value 0001 name1 17 0002 name2 45 0005 ...
1
vote
1answer
59 views

TSQL Script Execution Through Power shell, Special Characters Rendering Problem

We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ). ...
1
vote
2answers
43 views

Can a table column be written to treat two different values as the same value?

The title is a bit generic, but the specific issue is that the United Kingdom of Great Britain and Northern Ireland has two commonly used 2-character codes. ISO 3166-1 specifies the code GB as the ...
3
votes
1answer
107 views

Export/import data from Excel into a table with queries

I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it. I've come across some scenario's where I would have an Excel ...
2
votes
1answer
77 views

Secondary log shipped DB becoming suspect after restore of a transaction log

The Configuration Primary DB SQL Server 2008 R2 SP3 Web Edition running in windows Server 2012 R2 x64 on a local RAID 10 SAS 10k disk array co-located in an enterprise grade datacentre Secondary DB ...
4
votes
2answers
52 views

Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
1
vote
2answers
65 views

Why could query performance improve enormous after db restore on new server with worse hardware?

A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from ...
0
votes
2answers
43 views

What is the minimum privilege required to create a full text index?

I just created a full text catalog on my database. I now have a developer who needs the ability to create full text indexes on the tables in this database. The suggestion given in a previous ...
0
votes
0answers
89 views

Unable to login to SQL Server using SSMS and SQL agent failing

I have SQL server 2008 R2 Ent insalled on Windows server 2008 R2 Ent. I am unable to login to SQL server locally after the installation. Getting below error: Connection handshake failed. An OS call ...
3
votes
3answers
349 views

How to select distinct TOP but group by column

I have a result set of 500 rows, but this are for 100 invoice. I need only the top 10 of the last invoices. I found something like similar: How to select top 10 records from each category Sample ...
0
votes
1answer
43 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
1
vote
2answers
39 views

Need to sum a field in a row with same it and result needs to be cumulative in a field

This is my SQL Server 2008 R2 table: ID Amount date_time 001 9.00 01/01/2015 01:01:00 001 20.00 01/02/2015 01:02:00 001 20.00 01/02/2015 01:03:00 002 20.00 01/01/2015 01:01:00 002 20.00 ...
0
votes
0answers
17 views

How to find difference in usage date based on results

I have a ticket details table that stores the information of transactions. Here is an example of the table data: Ticket_Number Detail_type_ID Description Date_Created TotalAmount Barcode ...
3
votes
3answers
92 views

Improve Restore Speed

I have a ~3GB backup file from our QA database, and a daily diff (~500MB). For data complexity reasons, some of our developers like to use regular backups from this database, and so we have a process ...
0
votes
1answer
89 views

Intermittent errors sending email from SQL Server Database Mail

I've been seeing some SMTP errors in my database mail logs. So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email ...
-1
votes
1answer
90 views

How to fix invalid length in all tables of MS SQL Server 2008 (R2) database

Task is simple: I want to export all SQL Server database tables using SQL Server Management Studio 2008 R2 to MySQL (or any other format that MySQL can take, e.g Excel, CSV). I have tried the ...
1
vote
1answer
30 views

Permissions to execute a procedure on database a that reads a table on database b

A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following ...
3
votes
1answer
74 views

Merge Replication Validation Failure

We are getting a bizarre error with some of our users while syncing with SQL Server 2008 R2 Service Pack 3. Validation fails even though the row count and expected row count actually match! Error ...
3
votes
1answer
46 views

False-failures when mirrored in high-safety mode without a witness

A number of DBs are mirrored in high-safety mode without a witness. I'm trying to understand what might happen in the case that a DB (or DBs) experiences a false-failure while the primary server ...
3
votes
1answer
50 views

Msg 8905: DBCC CHECKALLOC Error Messages

We recently experienced a database corruption and run integrity checks. DBCC CHECKDB returned a lot of consistency errors and 2 allocation errors. We have fixed consistency errors by moving data, ...
0
votes
1answer
32 views

Determining CPU Usage of last week

We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in ...
0
votes
1answer
29 views

Multiple Replication Type Publications on Single DB

I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as ...
3
votes
1answer
31 views

Select Permissions Multiple Schema

I have an interesting one that I have not had the pleasure of solving before. I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, ...
0
votes
1answer
34 views

Error 823 and no connection

One of our servers tells us it has Error 823 issues: TITLE: Connect to Server Cannot connect to ServerName. ADDITIONAL INFORMATION: Warning: Fatal error 823 occurred at Mar 1 2016 > ...
1
vote
0answers
42 views

Why does the 9th Sql Connection in a row fail with a transaction manager error? [closed]

I am debugging an asp.net application on local machine that is connecting to a remote SQL Server database and run into an unhandled execption saying: System.Runtime.InteropServices.COMException: ...
2
votes
1answer
210 views

VMWare VMotion causing performance to increase?

We've been experiencing strange issues for awhile now with our virtual environment running SQL Servers. We randomly get calls from users about very poor performance on the SQL boxes. Sure enough when ...
0
votes
0answers
20 views

Operator Disable enable in sql server via JOB when fail over occured

I want to disable and enable the the operator in sql server when fail over is occurred either manual or automatic. I have created below alert on both principal as well as mirrored server and in ...
1
vote
0answers
53 views

Include failed step's error details in database mail for failed agent jobs

I receive database notification mail whenever a job fails. Error message of the failed job (Step Id=0 in the Job History) is included in the mail; MESSAGES: The job failed. The Job was invoked by ...
3
votes
1answer
153 views

Howto find out details and reasons for idle transactions?

I have started to use the "Idera SQL check" tool. It shows floating bubbles for each process active on the Sql Server system: Those bubbles are surrounded by a red border in case the transaction is ...
7
votes
1answer
107 views

Index SEEK is not used unless OPTION (RECOMPILE)?

(Question moved from SO) I have a table (dummy data) with clustered index contains 2 columns : Now I run those two queries : declare @productid int =1 , @priceid int = 1 SELECT ...
3
votes
1answer
75 views

Login issue in Logshipping

I have configured log shipping in my primary serverA and secondary serverB. Everything works fine until I bring the standby database online. Users are not able to login to the secondary serverB ...
1
vote
2answers
48 views

How to estimate REDO phase time during RESTORE

I am restoring database now and get it hang on 100% but still going (doing redo phase). I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it, ...
0
votes
2answers
51 views

Inline SQL To Set Fully Qualified Name [closed]

Why does my variable @FQN not set accordingly? Is this a problem with my syntax? A Problem with using a cursor? What should I do so that my variable @FQN sets appropriately? This is what my print ...
4
votes
1answer
100 views

How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

My server is having a high compilation/sec compared to batches/sec (more than 10%). How do I know that this is because of memory pressure that forces removing cached plans from memory, or because of ...