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

learn more… | top users | synonyms (2)

2
votes
1answer
24 views

Detail of all transactions on a specific row

I've a User table and It's data can be added/ updated from a User Screen. I want detail of all transactions on any row. For example, I have data in table like this Name - Age - Salary ...
-2
votes
0answers
221 views

SQL Server Management Studio Has Stopped Working [closed]

I was executing a large script and suddenly the Sql Server had crashed, after that I tried to run the SQL Server Management Studio, but was getting the mentioned error. I even tried to repair the SQL ...
5
votes
2answers
157 views

Can sp_executesql be configured / used by default?

I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I ...
1
vote
0answers
32 views

Why am I getting this network error when setting up database mirroring?

I have two servers, one in the Portsmouth, NH datacenter, one in the Kansas City datacenter, same domain. Trying to set up mirroring. First, does it matter that one server has Windows 2008R2 server ...
6
votes
2answers
1k views

Is ELSE 0 implied in my COUNT CASE WHEN statement?

What's the difference between COUNT(CASE WHEN [Column A] = ____ THEN 1 END and COUNT(CASE WHEN [Column A] = ____ THEN 1 ELSE 0 END? I've been using the former and I haven't seen the difference thus ...
3
votes
1answer
41 views

Clarification on Database Replication

SQL Server 2008 I have to do database replication which is a new for me. Goal is have mirror database on servers 2 and 3. When we set up database replication in the Server 2, 3 from Server # 1, ...
0
votes
1answer
54 views

SQL Dependent Percentage

Sample data Excepted output Math Operation Sum all Period time for each EmpName as Total Employee Time Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100% ...
4
votes
2answers
282 views

MSDB log file size

We have a SQL Server 2008 cluster with 60 databases. Full backup runs every night, log backup runs every 15 minutes. The MSDB database recovery model is set to simple (default). To be able to see ...
2
votes
2answers
47 views

simpler way to write if today or tomorrow is between ColA or ColB

SELECT * FROM my_table WHERE getdate() between SPRFRDTE and SPRTODTE or dateadd(DD, 1, getdate()) between SPRFRDTE and SPRTODTE is there a way to write this as if TODAY or TOMORROW is between ...
1
vote
1answer
52 views

SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4. I tried adjusting my SQL code in the ...
-8
votes
1answer
122 views

FULL OUTER JOIN much faster than UNION ALL in SSMS, but slower in Excel [closed]

I am trying to optimise a view pulled from SQL Server 2008 to Excel, and I've managed to drastically improve the performance on SSMS. Using FULL OUTER JOIN instead of UNION ALL, I've managed to ...
0
votes
2answers
310 views

SQL Job Fails - Why?

This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails. (For security I have changed the server name and ...
0
votes
0answers
12 views

List All Stored Procedures that Query Specific Table [duplicate]

I am using this syntax, and it returns no stored procedure names (however I know their are 2 stored procedures that query the table). This is SQL Server 2008 and this is my syntax: SELECT DISTINCT ...
4
votes
4answers
97 views

How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database

Forgive my greenness.. but would appreciate some help on this... We are doing development work on an instance of SQL Server 2008 and have multiple databases on that server that are core to our ...
0
votes
1answer
32 views

SQL Server authentication failed after server promote

we recently promoted a Windows 2008R2 server to domain controller. We then found that it hosted a SQL Server 2008R2 Express database instance. If we try to login to the DB with the domain admin ...
1
vote
1answer
86 views

Sum of time spans

I have table like that Code Time Value RowNum -------- ----------------------- ----------- ------- I370902P 2015-12-04 09:00:00.000 5.0 1 I370902P 2015-12-04 09:...
1
vote
1answer
33 views

SQL Server 2008 running schema query without identifiable cause

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two ...
1
vote
0answers
134 views

SqlException (0x80131904): Cannot open database

We keep getting the error SqlException (0x80131904): Cannot open database in our environment. We are sure the connectionstring used is correct. The user has the necessary permissions in the database ...
-7
votes
1answer
49 views

Need Immediate Help To Develope T-SQL

Hi DBAs, I want to Develop a T-SQL For Fetch data from following Table. But Condition is : I want to select only those data which status has all three status. suppose for id 1 : It has Only two ...
0
votes
0answers
15 views

How to move SQL Server to a different domain [duplicate]

I am new to data base usage and I want to know a simple steps on how to move an application server to a different domain without losing any data. Right now I'm not very good with SQL but I want to ...
3
votes
0answers
33 views

Network Traffic increase with Small Result Set

I am currently executing a stored procedure that is using up a ton of network traffic when it executes. The stored proc executes multiple stored procs within itself as well as functions and ...
7
votes
1answer
377 views

Can I recover a SQL database from Standby/Read-Only without a standby file?

Is there any way to recover a SQL database from Standby/Read-Only mode if the "standby file" is missing? (I see some references calling this a TUF file in a log-shipping scenario, but in my case, it ...
1
vote
2answers
46 views

SQL Query to find Records companywise

I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes See the Sample Data below : addon status amt1 amt2 company ...
3
votes
1answer
55 views

Are database connections from an application expensive from a performance point of view?

A colleague tells me that connections to a database are expensive and could harm performance of the application. They ask me to create a procedure that calls say another 8 procedures, that way they ...
0
votes
0answers
29 views

Broken partition function/schemes

The dilemma SQL Server 2008 Enterprise 2 partition schemes using the same partition function partition function splits on date column, monthly basis maintenance script to split partition function ...
0
votes
1answer
63 views

MSSQL how do I query sql for a latest state record after latest date

I have a table with openingdays, location, state (Open, Day Closed, Shift Closed) and the actual date. I would like to know when the day is closed for the last time (state = D) and everything what ...
3
votes
1answer
183 views

SQL to Find Partial Duplicates of a Field

I have a table with a field for a member number. The table can contain records with duplicate values for this field and does. I have a SQL query that will obtain any duplicate values for this field, ...
0
votes
0answers
44 views

Rowlock hint is doing Lock escalation at table level. i dont know why.? [closed]

I want to first do select to read rows and then do some calculations and then finally update the read row. So my transaction will be in 3 steps: select the records (In this session only allow other ...
1
vote
0answers
45 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , tj....
0
votes
0answers
32 views

Do both the sessions need to have UPDLOCK to avoid deadlocks

The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time - The table has clustered index on ID column ----session 1 -------- begin transaction ...
0
votes
0answers
32 views

SQL Server: Update linked table times out

I have 4 linked SQL Server 2008 R2 servers in the same network, and distributed selection queries works fine. However when I try to run something like that: UPDATE [LINKED SERVER NAME].[DB NAME].[...
1
vote
1answer
61 views

Setting up SQL Server to handle queries from external domains

I am not a SQL Server admin. I have tried to google this but can't seem to use the right words to get the right info and they all talk about remote connections to a SQL Server in terms of admin and ...
3
votes
1answer
123 views

Determine Which Parameters Passed Into Stored Procedure

Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?
15
votes
2answers
2k views

Why does SQL Injection not happen on this query inside a stored procedure?

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' ...
1
vote
1answer
132 views

How to get SQL Server CPU and Memory read and write usage within two weeks?

Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems ...
4
votes
1answer
106 views

Viewing Temp Tables

I am using SQL Server 2008 and my stored proceduce does inserts into #temptable. My procedure has been running for 32 hours (which isn't unheard of considering the amount of data that it has to ...
-1
votes
2answers
27 views

how do i take the end value of a colum, convert to date and insert into a new column

I have a column [week] in a table [wkd] with a value of 11/08 - 11/14. They are strings with 2 date ranges. (nov 8 - nov 14). what I need to do is get 11/14 into a new date column [weekending] so ...
2
votes
1answer
71 views

looking for a cleaner way to select from multiple tables

I'm currently using this code to select from 2 tables using a stored procedure. DECLARE @now as varchar(26) set @now = CONVERT(VARCHAR(26), SYSDATETIME(), 9) --Select 'SMD_' + replace(convert(...
1
vote
1answer
93 views

Aggregate SQL Rows into single column

I would like to aggregate results of a column into a single row using MS SQL. I have create a sql fiddle which showcases the issue: http://sqlfiddle.com/#!3/384d5/2 Basically I would like all 'Tag' ...
1
vote
1answer
25 views

SQL Server 2008 job failure

I have a problem on my servers sitting on different domains. I created a linked server that executes successfully and I can list the catalogue but when I schedule a job to execute I get this error: ...
0
votes
2answers
759 views

How to restore a database using mdf and ldf files in SQL Server 2008?

Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent ...
0
votes
0answers
55 views

Issue with multiple execution of sys.sp_addextendedproperty

I'm using SQL Server 2008 R2. I need to store a text as a description of each table of my DB. But I don't want to do this with wizard. I handled to build some code for this purpose. Here's a part of ...
0
votes
1answer
188 views

How to deny drop, delete permission for a table?

I want to give Create , alter , read permission on object to user but not insert , update ,delete,drop permission to user , How can i do it in sql server 2008?
1
vote
0answers
19 views

What is the most effective way to manage a community table with one customizable flag?

Fair warning I do not have dba training or anything of the like but I am managing a rather large web app database. I know the basics but I'm not sure how to handle this latest scenario. Setup I ...
1
vote
3answers
110 views

dynamically get last x months and output as a format

Using today's date, I need to be able to go back x months and output in the format of YYYY_MM At the moment, I am using: select right('0000' + cast(datepart(year, getdate()) as varchar(4)), 4) + '...
0
votes
3answers
64 views

Connecting to a SQL server whose IP changes

I want to connect to a server whose IP is dynamic via a VB.net application. I have configured the connection string in my VB application to a static IP. I also gave a static IP to the database. ...
0
votes
0answers
73 views

Help diagnosing a single-table, single row deadlock

We're currently using an ORM-controlled, table-based, identity generation scheme to generate the IDs for our entities. The table is as follows: create table id_seq ( tbl varchar(255) not null, id ...
2
votes
0answers
96 views

Are there undocumented Linked Server limitations?

I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server. There are a few stored procedures that execute a pass-through against the linked server. Once in a while, a ...
0
votes
1answer
55 views

Finding Deleted / Updated record statement from SQL Server transaction Log backup file

One of my production server's data was updated / deleted by someone. I want to find out when the data was deleted or updated, and if it's updated/ deleted then through which statement it was updated (...
1
vote
0answers
111 views

Backing up / restoring a 20GB database with images stored as binary code

Please excuse me as I am a beginner. I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues: The developers ...